Über unsMediaKontaktImpressum
Benjamin Weissman 18. April 2017

Biml macht Datenbeladung zum Kinderspiel

Biml ist eine flexible Programmiersprache. © ave_mario / Fotolia.com
Biml macht Datenbeladung zum Kinderspiel. © ave_mario / Fotolia.com

Ein aktuelles Trendthema im Bereich Microsoft BI ist Biml – oder auch: Die Business Intelligence Markup Language. Eine von der Firma Varigence [1] geschaffene Auszeichnungssprache. Ziel dieses Beitrags ist es, Ihnen die Möglichkeiten mit Biml im Wesentlichen näher zu bringen.

Grundlagen von Biml

Lassen Sie uns zunächst einmal auf die Basics schauen. Wichtig ist hierbei stets: Biml ist ein Hilfsmittel, um bestehende Lösungen oder neue Anforderungen zu automatisieren. Dies erfordert jedoch zwingend entsprechende Vorkenntnisse in Microsoft BI-Technologien: Wenn ich nicht weiß, welche Lösung bzw. welchen Ansatz ich umsetzen möchte, so kann ich ihn auch nicht automatisieren!

Abb.1: Notwendige Definitionen für ein Integration Services-Projekt. © Benjamin Weissman
Abb.1: Notwendige Definitionen für ein Integration Services-Projekt. © Benjamin Weissman

Biml ist auch kein "Out of the box-Produkt", sondern eine flexible Programmiersprache. Man sollte somit nicht mit der Erwartungshaltung starten, dass man das Produkt heute installiert und damit sofort alle Anforderungen umgesetzt sind.

Sprache

Wie eingangs erwähnt, handelt es sich bei Biml um eine Auszeichnungssprache, also XML [2]. Der Aufbau sowie die Nomenklatur orientieren sich sehr stark an Microsoft BI-Tools. So sind für ein Integration Services-Projekt beispielsweise zunächst die Verbindungen, dann die Datenbanken und Schemata zu definieren, bevor wiederum auf Tabellen etc. zugegriffen werden kann (s.Abb.1).

Ein einfaches Beispiel für Biml sähe wie folgt aus:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
     <Packages>
        <Package Name="HelloBiml"/>
    </Packages>
</Biml>

Dieser Code würde ein leeres Paket namens HelloBiml.dtsx erstellen. Der konkrete Anwendungssinn ist hier natürlich nicht gegeben – vielmehr zeigt dies, wie logisch der Aufbau der Sprache ist.

Frontends

Im Wesentlichen gibt es 3 Frontends für Biml: BimlExpress, BimlOnline und BimlStudio.

BimlExpress

  • Vorteile: Kostenfrei, Voll in Visual Studio integriert
  • Nachteile: Unterstützt keine Pro-Features wie integrierte Metadaten, Frameworks und Dokumentation, erlaubt keine Build Automation, unterstützt kein SSAS, kompliziertes Debugging und kein Import bestehender DTSX-Dateien etc.

BimlOnline

  • Vorteile: Kostenfrei, Visualisierung von Metadaten, erlaubt Import von bestehenden DTSX-Dateien u.ä.
  • Nachteile: Unterstützt keine Pro-Features, Cloud-Lösung, erlaubt keine Build Automation, unterstützt kein SSAS

BimlStudio

  • Vorteile: Erlaubt Build Automation, stellt sämtliche Biml-Funktionen zur Verfügung, Code Preview, besseres Debugging, Visualisierung von Metadaten, erlaubt Import von bestehenden DTSX-Dateien u.ä.
  • Nachteile: Kostenpflichtig

Meine allgemeine Empfehlung wäre in der Regel, mit einer Testversion von BimlStudio zu starten, denn insbesondere die Visualisierung der Metadaten sowie die Code Preview-Funktion sind zum Lernen extrem hilfreich. Sobald diese abgelaufen ist und Sie sich mit den Kernelementen von Biml vertraut gemacht haben, übertragen Sie den Code nach BimlExpress um dort (dann kostenlos) weitere Erfahrungen zu sammeln und die Lösung weiter zu entwickeln. Danach können Sie sich immer noch überlegen, ob die erweiterten Funktionen von BimlStudio eventuell dauerhaft rechtfertigen, in entsprechende Lizenzen zu investieren [3].

BimlScript

Nun ist die Lösung aber natürlich nicht, Drag-and-drop durch Copy-and-Paste zu ersetzen, um eine gigantische manuelle XML-Datei zu erstellen. Hier kommt BimlScript ins Spiel. Mittels der gängigen Programmiersprachen C# und VB.NET lassen sich Muster und wiederkehrende Aufgaben stark automatisieren.

Um beispielsweise alle in den Metadaten definierten Tabellen auch in der Staging-Umgebung zu erstellen, bedarf es keines manuellen Codes je Tabelle. Egal ob es sich um eine Tabelle oder um 1.000 Tabellen handelt, dieses kleine Stück BimlScript würde die Generierung automatisieren:

<# for each table as AstTableNode in RootNode.Tables  #>
    <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target">
        <DirectInput>
            <#=table.GetDropAndCreateDdl()#>
        </DirectInput>
    </ExecuteSQL>
<# next #>

Es zeigt sich also schon hier: Je häufiger ein bestimmter Task in SSIS benötigt wird, desto höher ist der Zeitgewinn durch die Skalierung mit Biml und BimlScript!

Abb.2: Biml Engine verarbeitet den Code. © Benjamin Weissman
Abb.2: Biml Engine verarbeitet den Code. © Benjamin Weissman

Kompiliervorgang

Wie funktioniert das dann am Ende? Relativ einfach: der fertige Code, eine Kombination aus statischem XML sowie BimlScript, wird in 2 Stufen von der Biml Engine verarbeitet:

  1. Der BimlScript-Code wird ausgeführt, das Ergebnis ist zunächst nur noch XML.
  2. Der XML-Code wird von der Engine interpretiert und in Microsoft BI-Objekte übersetzt. Das Ergebnis hier sind wiederum – je nach Anwendungsfall und Projekt – SQL-Dateien, DTSX-Dateien oder ähnliches.

Dieser Vorgang erfolgt für den Anwender jedoch transparent. In BimlStudio könnte man den XML-Code, also den Zwischenschritt, noch vorab sehen. In BimlExpress wäre der gesamte Prozess gekapselt und nur das Endergebnis sichtbar.

Abb.3: Verbindungen definieren. © Benjamin Weissman
Abb.3: Verbindungen definieren. © Benjamin Weissman

Beispielansätze für SSIS

Einer der wohl offensichtlichsten Ansatzpunkte für Biml ist somit sicherlich SSIS, genauer der Staging-Vorgang. 

Wichtig ist es, hierbei stets die Wiederverwendbarkeit von Code und Methoden im Auge zu behalten, um nicht immer das Rad neu erfinden zu müssen!

Sehen wir uns also einen klassischen Staging-Prozess an.

Zunächst müssen wir unsere Verbindungen definieren. Konkret bedeutet das, mittels einer for each-Schleife alle benötigten Verbindungsdaten aus der Metadatenbank auszulesen und für jeden Eintrag eine entsprechende Verbindung in Biml anzulegen.

Der entsprechende Code könnte in etwa wie folgt aussehen:

<# for each row as datarow in ExternalDataAccess.GetDataTable(TargetConnString, "SELECT  ConnectionName, ServerName, DatabaseName  FROM [MyBimlMeta_Connections]").rows #>
 
<OleDbConnection Name="<#= row.item(0) #>" ConnectionString="Provider=SQLNCLI11;Server=<#= row.item(1) #>;Initial Catalog=<#= row.item(2) #>;Integrated Security=SSPI;"/>

<# next  #>
Abb.4: Durchlaufen aller Verbindungen und innerhalb der Verbindungen wiederum aller Tabellen. © Benjamin Weissman
Abb.4: Durchlaufen aller Verbindungen und innerhalb der Verbindungen wiederum aller Tabellen. © Benjamin Weissman

Im nächsten Schritt vervollständigen wir zunächst alle Metadaten bezüglich der zu ladenden Tabellen. Diesen Schritt würde man im klassischen Ansatz manuell je Tabelle ausführen, hier erfolgt er zentral und weitgehend automatisiert.

Das Vorgehen entspricht vom Grundprinzip dem, was schon bei den Verbindungen zum Einsatz kam, nur dass die Abfragen nun verschachtelt sind.

Das bedeutet: Wir durchlaufen alle Verbindungen und innerhalb der Verbindungen wiederum alle Tabellen. Je Tabelle müssen wir dann noch unterscheiden, ob wir alle Spalten benötigen (also ein SELECT * FROM) oder ob auch hier Einschränkungen greifen sollen.

Basierend darauf generieren wir somit wiederum dynamisch die Tabellen innerhalb des Biml-Objektmodells.

Abb.5: Erstellen der Staging-Tabellen. © Benjamin Weissman
Abb.5: Erstellen der Staging-Tabellen. © Benjamin Weissman

Auf Basis der gesammelten Daten lässt sich nun bereits die Staging-Umgebung selbst erstellen.

Biml bietet hierbei bereits eine große Vielfalt an vorgefertigten Funktionen. In diesem Fall übernimmt beispielsweise die Funktion GetDropAndCreateDdl das komplette Erstellen des T-SQL-Statements für unsere Staging-Tabellen.

Es bedarf somit nur weniger Zeilen Code, um für jede Tabelle einen SQL-Task in SSIS zu generieren, der diese Tabelle wiederum anlegt:

<# for each table as AstTableNode in RootNode.Tables  #>
<ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target">
<DirectInput>
<#=table.GetDropAndCreateDdl()#>
</DirectInput>
</ExecuteSQL>
<# next #>
Abb.6: Pakete für die Beladung der Umgebung erstellen. © Benjamin Weissman
Abb.6: Pakete für die Beladung der Umgebung erstellen. © Benjamin Weissman

Im letzten Schritt müssen nur noch die Pakete für die Beladung der Umgebung erstellt werden (s. Abb.6).

Die Grundlogik ist hier die gleiche wie beim Erstellen der leeren Staging-Tabellen. In diesem Fall ersetzen wir nur den SQL-Task durch einen Container, der die Vorbereitung (z. B. TRUNCATE) sowie die eigentliche Beladung nach verschiedensten Mustern abbildet.

Das Ergebnis wiederum sind "ganz normale" SSIS-Pakete, welche sich wie gewohnt über die Microsoft Bordmittel (SQL Agent, dtexec etc.) ausführen und planen lassen.

Theoretisch könnte man diese nun auch manuell weiter bearbeiten – dies sollte jedoch vermieden werden, da sonst natürlich keine Updates durch Biml mehr möglich sind bzw. die manuellen Änderungen hierbei verloren gehen würden.

Da sich diese Schritte beliebig oft aktualisieren lassen, stellen Änderungen im Vorsystem (beispielsweise ein Wechsel von non-unicode auf unicode) oder zentrale neue Anforderungen, wie eine derived-Column in jedem Datenfluss zur Speicherung der aktuellen Uhrzeit keinerlei Herausforderung mehr dar, da diese an einer zentralen Stelle in das Modell aufgenommen werden [4].

Kann ich Biml neben SSIS auch für weitere Themen nutzen?

Natürlich lassen sich mit Biml nicht nur Datenintegrationsthemen lösen! Neben den Integration Services wird auch die sonstige Palette des Microsoft BI-Stacks abgedeckt.

SSAS

So lassen sich sowohl tabular als auch multidimensionale Analysis Services-Projekte erstellen und designen. Dieses Feature ist jedoch nur in der kostenpflichten Version BimlStudio enthalten, weswegen wir hier nicht im Detail darauf eingehen.

Spannend ist jedoch in diesem Kontext eventuell – auch wenn dies wieder eher ein SSIS-Thema ist: Mittels Biml lässt sich zum Beispiel ein Task automatisieren, welcher dynamisch alle Dimensionen und/oder Cubes einer OLAP-Datenbank verarbeitet. Hierzu kommt erneut eine Kombination aus BimlScript – mit dem wir die Liste der zu verarbeitenden Objekte aus der Datenbank auslesen – sowie klassischem Biml zur Erstellung des Processing Tasks zum Einsatz.

T-SQL

Das Objektmodel von Biml eignet sich aber auch für klassische T-SQL-Themen. Durch das extrem mächtige und umfangreiche Objektmodell sind viele Aufgabenstellungen einfacher und schneller zu lösen, als mit dynamischem SQL – wenngleich sie sicherlich nicht nur durch Biml lösbar sind.

Einige Themen, bei denen man sicherlich anfangs nicht an Biml denkt:

  • Sample Data Generation – schnelle Generierung von Beispieldaten, um sie unter anderem für Performancetests zu nutzen
  • Index und View Clinic – Überprüfung einer Datenbank auf defekte Views und doppelte Indexe
  • Deployment – Ablösung von DACPACs durch Biml

Warum sollte ich Biml nutzen?

Was sind also die wesentlichen Vorteile von Biml? Sicherlich die teilweise massiv gesteigerte Produktivität durch das Finden und Automatisieren von Design Pattern. Auch die erhöhte Übersicht und hohe Flexibilität bei Änderungen sprechen für sich.

Durch den strukturierten Aufbau ist endlich auch ein sinnvolles Arbeiten mit Quellcodeverwaltungssystemen möglich, ohne diese als reines Backup-System zu nutzen. Und dies sind alles kostenfreie Features!

Wenn nun noch die erweiterten Funktionen wie automatisierte Builds, Metadaten-Integration etc. hinzukommen, so muss man sich schon fast fragen: Was habe ich all die Jahre ohne Biml gemacht?

nach Oben
Autor

Benjamin Weissman

Ben Weissman ist Inhaber und Senior Consultant bei Solisyon. Er ist MCSE Data Platform und Business Intelligence und arbeitet seit Version 6.5 mit dem SQL Server.
>> Weiterlesen
botMessage_toctoc_comments_929