This blog post has also been published in English.
In all unseren vorhergehenden Beiträgen haben wir unsere Daten stets parallel geladen, ohne dabei auf eventuelle Fremdschlüssel-Einschränkungen Rücksicht zu nehmen. Was aber, wenn es eben solche Einschränkungen in Ihrem Datawarehouse gibt, was bedeutet, dass die Reihenfolge, in welcher die Tabellen beladen werden von entscheidender Bedeutung sind?
In diesem Beitrag lösen wir gleich zwei Probleme: Basierend auf einer Liste von Tabellen, welche wir beladen wollen identifizieren wir in der Quelle alle Kinder (auch rekursiv), auf die diese Tabellen verweisen. Im nächsten Schritt legen wir diese dann in der korrekten Reihenfolge in unseren Datawarehouse an und beladen diese.
Wir verwenden hierzu das AdventureWorksDW2014 als unsere Quelle und übertragen von dort die Tabelle FactInternetSales inklusive aller referenzierten Tabellen unter Beibehaltung der Fremdschlüssel-Einschränkungen in eine neue Datenbank AdventureWorksDW2014_SalesOnly.
Biml stellt keine Standardfunktion für die Sortierung nach Fremdschlüsseln bereit, mit einer kleinen Extension Method, welche wir “TopoSort” nennen, ist das Thema aber leicht zu lösen. Letztendlich handelt es sich um eine Schleife, welche eine Liste von Tabellen durchläuft, zu jeder Tabelle prüft, ob es abhängige Tabellen gibt und, falls ja, diese in der Liste voranstellt:
Imports System.Collections.Generic
Imports System.Linq
Imports System.Linq.Expressions
Imports Varigence.Languages.Biml.Table
Imports System.Runtime.CompilerServices
Module TopologySort
Public OutputPath As String = "C:\SSIS\TopologySort"
Public LoadedTables As New List (Of String)
Public LoadableTables As New List (Of AstTableNode)
Public Level As Integer = 0
<Extension()>
Public Function TopoSort (tables As ICollection (Of AstTableNode)) As ICollection (Of AstTableNode)
Dim visitedList As New List (Of AstTableNode)
Dim outputList As New List (Of AstTableNode)
For Each tbl As asttablenode In tables
TopoVisit(tbl, outputList, visitedList)
Next
Return outputList
End Function
Private Function TopoVisit( node As AstTableNode, outputList As List (Of AstTableNode), visitedList As List (Of AstTableNode))
If Not visitedList.Contains(node) Then
visitedList.Add(node)
For Each dependentTable As AstTableNode In node.Columns.OfType(Of AstTableColumnTableReferenceNode).Select(Function(c) c.ForeignTable)
TopoVisit(dependentTable, outputList, visitedList)
Next
For Each dependentTable As AstTableNode In node.Columns.OfType(Of AstMultipleColumnTableReferenceNode).Select(Function(c) c.ForeignTable)
TopoVisit(dependentTable, outputList, visitedList)
Next
outputList.Add(node)
End If
End Function
End Module
To be able to actually access our data, we’ll need to define 2 connections, one for the source and one for our new target database. This will be done using a static Biml file:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="AW_DW" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2014" />
<Connection Name="AW_DW_SalesOnly" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2014_SalesOnly" />
</Connections>
<Databases>
<Database Name="AW_DW_SalesOnly" ConnectionName="AW_DW_SalesOnly"></Database>
</Databases>
<Schemas>
<Schema Name="dbo" DatabaseName="AW_DW_SalesOnly"></Schema>
</Schemas>
</Biml>
Damit wir auf unsere Daten zugreifen können benötigen wir natürlich für Ziel und Quelle eine Verbindung, welche wir beide in einer statischen Biml Datei definieren:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="AW_DW" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2014" />
<Connection Name="AW_DW_SalesOnly" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2014_SalesOnly" />
</Connections>
<Databases>
<Database Name="AW_DW_SalesOnly" ConnectionName="AW_DW_SalesOnly"></Database>
</Databases>
<Schemas>
<Schema Name="dbo" DatabaseName="AW_DW_SalesOnly"></Schema>
</Schemas>
</Biml>
Auf Basis dieser Tabellen können wir nun unsere Metadaten generieren. Wie bereits erwähnt basiert dieses Beispiel auf FactInternetSales – ändern, erweitern oder entfernen Sie diesen Filter gerne um verschiedene Ergebnisse zu sehen.
Die Logik funktioniert wie folgt:
– Wir stellen eine Liste (welche leer sein kann!) bereit, welche die Tabellen beinhaltet die wir übertragen wollen
– Wir holen das komplette Datenbankschema der Quelle ab
– Für jede Tabelle in unserer Liste prüfen wir, ob es referenzierte Tabellen gibt und, falls ja, fügen diese zur Liste hinzu
– Diesen Schritt wiederholen wir bis es keine unaufgelösten Referenzierungen mehr gibt
– Die finale Liste reichen wir erneut an GetDatabaseSchema weiter
– Mittels einer Schleife fügen wir für jede benötigte Tabelle, unter Anpassung des Schemas auf die Zieldatenbank, zur Biml Rootnode hinzu
<#@ template language="VB" tier="2"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Tables>
<# dim tablelist as new list (of string)
dim tablelist_checked as new list (of string)
tablelist.add ("FactInternetSales")
dim importResult as Importresults = rootnode.connections("AW_DW").GetDatabaseSchema()
do while importresult.tablenodes.where(function(e) tablelist.Contains(e.name) and not tablelist_checked.Contains(e.name)).where(function(r) r.columns.OfType(Of AstTableColumnTableReferenceNode)().Any).Count > 0
for each t as asttablenode in importresult.tablenodes.where(function(e) tablelist.Contains(e.name) and not tablelist_checked.Contains(e.name)).where(function(r) r.columns.OfType(Of AstTableColumnTableReferenceNode)().Any)
for each r as asttablecolumntablereferencenode in t.columns.ofType(of AstTableColumnTableReferenceNode)()
tablelist.add(r.foreigntable.name)
next
tablelist_checked.add(t.name)
next
loop
importresult = rootnode.connections("AW_DW").GetDatabaseSchema(nothing,tablelist,ImportOptions.ExcludeViews)
for each t as asttablenode in importresult.tablenodes
t.schema = rootnode.schemas(0)#>
<#= t.getbiml #>
<# next #>
</Tables>
</Biml>
Dank dieser Metadaten können wir die Tabellen nun in der Zieldatenbank anlegen. Um in keine Fremdschlüssel-Probleme zu laufen, werden wir diese zunächst (falls es sie schon gibt) per DROP entfernen: Hierzu sortieren wir mittels TopoSort.Reverse, wir löschen also die Tabellen zuerst, auf die nicht von anderen Tabellen verwiesen wird. Im Anschluss durchlaufen wir die Tabellen in genau umgekehrter Reihenfolge und legen sie mittels GetDropAndCreateDDL neu an:
<#@ template tier="3" language="VB"#>
<#@ code file="../Code/TopologySort.vb" #>
<#@ import namespace="TopologySort" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="01_Create" ConstraintMode="Linear" PackageSubpath="<#= OutputPath #>">
<Tasks>
<ExecuteSQL Name="DROP Tables" ConnectionName="AW_DW_SalesOnly">
<DirectInput>
<# for each table as asttablenode in RootNode.Tables.TopoSort.reverse #>
IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<#= table.name #>]') AND type IN (N'U'))
DROP TABLE [dbo].[<#= table.name #>]
GO
<# next #>
</DirectInput>
</ExecuteSQL>
<ExecuteSQL Name="CREATE Tables" ConnectionName="AW_DW_SalesOnly">
<DirectInput>
<# for each table as asttablenode in RootNode.Tables.TopoSort #>
<#= table.GetDropAndCreateDDL() #>
<# next #>
</DirectInput>
</ExecuteSQL>
</Tasks>
</Package>
</Packages>
</Biml>
Bei der Beladung selbst legen wir auch zunächst (durch Aufruf des vorhergehenden Pakets) der Einfachheit halber alle Tabellen neu an. Ein DELETE Statement oder ähnliches würde aber natürlich ebenfalls funktionieren. Nach der entsprechenden Bereinigung können wir in gleicher Reihenfolge je Tabelle einen Datenflusstask zur eigentlichen Befüllung anlegen:
<#@ template tier="4" language="VB"#>
<#@ code file="../Code/TopologySort.vb" #>
<#@ import namespace="TopologySort" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="02_Load_Linear" ConstraintMode="Linear" PackageSubpath="<#= OutputPath #>">
<Tasks>
<ExecutePackage Name="Recreate Tables">
<ExternalFile ExternalFilePath="<#= OutputPath #>\01_Create.dtsx"/>
</ExecutePackage>
<# for each t as asttablenode in rootnode.tables.TopoSort #>
<#= CallBimlScript("06_Dataflow.biml",t,nothing) #>
<# next #>
</Tasks>
</Package>
</Packages>
</Biml>
<#@ template language="VB" designerbimlpath="Biml/Packages/Package/Tasks" #>
<#@ property name="tbl" type="AstTableNode" #>
<#@ property name="LoadedTables" type="List (of String)" #>
<# if not loadedtables is nothing then LoadedTables.add (tbl.name) #>
<Dataflow Name="Load <#=tbl.Name #>">
<Transformations>
<OleDbSource Name="Get Rows" ConnectionName="AW_DW">
<DirectInput>Select <#= tbl.GetColumnList() #> from <#=tbl.SchemaQualifiedName#></DirectInput>
</OleDbSource>
<OleDbDestination Name="Set Rows" ConnectionName="AW_DW_SalesOnly" KeepIdentity="true">
<TableOutput TableName="<#=tbl.ScopedName#>" />
</OleDbDestination>
</Transformations>
</Dataflow>
Somit haben wir eine konfigurierbare Lösung zur Übertragung von Tabellen inkl. aller Abhängigkeiten zwischen zwei Datenbanken, was schon sehr praktisch ist.
Der Nachteil ist jedoch: Durch den sequentiellen Ablauf dauert dieser Prozess unter Umständen deutlich länger als er eigentlich müsste.
Haben Sie hierzu Fragen oder Anmerkungen? Wir freuen uns auf Ihren Input unter !
Weitere Informationen zu Biml, einschließlich Terminen und Blog Beiträgen finden Sie auch auf unserer Biml Seite.
Viel Spaß beim Biml’n!