OLAP-Datenexport im ETL-Prozess mit SSIS und Berichtsserver

Im Blogbeitrag „Export von OLAP-Daten in eine SQL-Tabelle“ ist beschrieben, wie mittels Linked-Server und T-SQL Befehlen (OpenQuery) die Abfrage von OLAP-Daten und Übernahme in eine SQL-Tabelle des Microsoft SQL-Servers aussehen kann. Nachfolgend wird betrachtet, wie ein solcher Datenabzug alternativ direkt mittels Microsoft SSIS erstellt wird.

Einen Blick wert sind auch die Exportmöglichkeiten des DeltaMaster Berichtsserver mit der interessanten Möglichkeit, diesen in einen SSIS basierenden ETL-Prozess einzubinden, um Würfeldaten zu exportieren. Das ist beispielsweise dann eine Alternative, wenn das manuelle Erstellen ausgefeilter MDX-Abfragen Schwierigkeiten bereitet und durch das einfache Erstellen von geeigneten DeltaMaster Berichten ersetzt werden kann. Zusammen mit den Iterations- und Voreinstellungsmöglichkeiten des Berichtsservers ergibt sich für den Datenexport eine hohe Flexibilität.

Ob man OLAP-Daten via SSIS oder OpenQuery in T-SQL exportieren möchte, hängt sicherlich von der konkreten Aufgabenstellung ab. Der Weg über SSIS kommt jedoch ohne das Erstellen von T-SQL-Prozeduren aus und erlaubt ein direktes Übertragen der exportierten Daten auch in ein fremdes Zielsystem (sofern dieses direkt anbindbar ist) – das Ganze dann auch als Bestandteil eines größeren ETL-Prozesses. Ein multidimensionaler Export erfordert jedoch praktisch immer ein Flachklopfen von Daten, da viele Zieldatenbanken nur tabellarische bzw. spaltenorientierte Datenformate akzeptieren. Formate bspw. in Form von Teilwürfeln sind üblicherweise proprietär und beim Datenaustausch heterogener Systemlandschaften ungeeignet. Die Alternative XML-Format wird in diesem Blogbeitrag nicht betrachtet, da eine direkte Verbindung von unterschiedlichen Datenbanksystemen hierbei oft nur über SOAP basierende Web-Dienste möglich ist.

Im Folgenden werden Würfeldaten aus einer Microsoft SSAS-Datenbank mittels MDX abgefragt. Die Vorgehensweise ist auch für andere MDX-fähige OLAP-Datenbanken vorstellbar. Abgerufen werden stark vereinfacht die Umsätze und Absätze aller Kunden der Region 3 für alle Vertretergruppen der Chair-Demodatenbank.

Datenexport mittels SSIS

1. Die exportierten Daten werden im Beispiel in einer SQL-Server Datenbanktabelle abgelegt. Ein vorangestellter SQL-Task sorgt dafür, dass die Zieltabelle, die die abzurufenden OLAP-Daten aufnehmen soll, gelöscht und neu angelegt wird. Es muss daher auch ein Connection Manager mit OLE-DB Datenquelle angelegt werden, der die Verbindung mit der Zieldatenbank herstellt. Die Zieltabelle sollte ggf. mit varchar(max) oder varchar(4000) Feldern definiert werden.
2. Die Verbindung zu einer OLAP-Datenbank wird mittels OLE-DB Treiber hergestellt. Entscheidend für den Erfolg ist also die Erstellung eines geeigneten Verbindungs-Managers zur OLAP-Quelle. Dabei gibt es zwei verschiedene Anbieter (bzw. technische Schnittstellen), die beide die Verbindung mit einem SSAS Service erlauben:

• „OLE DB systemeigen\Microsoft OLE DB Provider for Analysis Services 10.0“
• „.NET-Anbieter für OleDb\Microsoft OLE DB Provider für Analysis Services 10.0“

Die zweite (neuere) .NET basierte Variante erweist sich beim Aufbau eines DTS-Packages als weniger fehleranfällig bzgl. Datentypkonvertierungen, daher ist dieser Anbieter zu empfehlen, auch wenn die weiteren Schritte für beide Anbieter identisch sind.

Nach Auswahl dieses Treibers beim Erstellen einer neuen Datenquelle muss der Name oder die IP-Adresse des Rechners angegeben werden, auf dem der zu verbindende OLAP-Server läuft. Danach kann als ursprünglicher Katalog die MS OLAP-Datenbank ausgewählt werden, die per Default angebunden wird, auch wenn der Zugriff auf weitere verfügbare Datenbanken dennoch ebenfalls möglich ist.

• Anschließend wird ein neuer Datenflusstask angelegt und in diesem als OLE DB-Datenquelle der neu angelegte Verbindungsmanager ausgewählt.
• Für den Datenzugriffsmodus muss „SQL-Befehl” eingestellt werden, auch wenn danach in das Feld „SQL-Befehlstext“ stattdessen das MDX-Statement eingefügt wird, das für den Datenabruf aus der Chair-OLAP-Datenbank benötigt wird. Der Editor ist jedoch relativ empfindlich, d.h. es kann vorkommen, dass alleine durch das Formatieren des MDX-Statements der Abruf danach nicht mehr korrekt funktioniert.

Über die Schaltfläche „Vorschau“ kann das eingegebene MDX-Statement getestet werden. Davor erscheint jedoch eine Warnung, die darauf hinweist, dass der Datentyp der Ausgabespalten auf den Datentyp DT_WSTR zugeordnet wird, da der eigentliche Datentyp der Datenquelle nicht sauber erkannt werden kann. Es ist also nach Erstellen der Verbindung notwendig, eine Datenkonvertierung bspw. mittels nachfolgendem Datenkonvertierungs-Task vorzunehmen, sofern das Ergebnis, wenn dieses bspw. in eine SQL-Server Tabelle übernommen werden soll, noch nicht geeignet ist.

Danach sollte die Vorschau das Ergebnis der MDX-Abfrage zeigen.

Sollte die Vorschau keine Werte zeigen, ist die MDX-Abfrage fehlerhaft oder aber es werden tatsächlich keine Werte gefunden.

• Da die Eingabespalten aus dem Ergebnis der MDX-Abfrage generiert sind, werden diese befehlsweise entsprechend dem MDX-Abruf benannt. Es empfiehlt es sich, diese manuell umzubenennen. Dazu wird am besten das OLE-Quellobjekt im Erweiterten Editor geöffnet und im Register „Ein- und Ausgabespalten” die Umbenennung der Ausgabespalten vorgenommen.
• Es empfiehlt sich ggf. die von SSIS im Datenquellobjekt angenommenen Datentypen zu konvertieren oder aber die Zieltabelle für den Datenexport entsprechend mit varchar(max) oder varchar(4000) Feldern zu erzeugen. Die Konvertierung kann direkt im OLE-DB Quellobjekt über die Ausgabespalten vorgenommen werden, was jedoch nicht immer erfolgreich ist. SSIS setzt üblicherweise den Datentypen DT_NTEXT. Die Ausgabespalten können dann als DT_WSTR ausgegeben werden, was SSIS auch ggf. selbst annimmt (siehe zuvor beschriebene Warnmeldung). Die direkte Konvertierung bspw. in den Datentyp DT_STR (normaler string) scheitert, da hier SSIS warnt, dass ggf. Informationen abgeschnitten würden (auch wenn der Anwender sieht, dass SSIS hier falsch liegen mag). Ein nachfolgender Konvertierungstask hilft jedoch, diese SSIS eigenen Datentypen ggf. mit den Datentypen des Exportziels in Einklang zu bringen.
• Die Zuordnung zum Datenziel wird in der Komponente OLE DB Target vorgenommen.

• Als Ergebnis sieht unser Mini-Beispielpaket in SSIS wie folgt aus:

DeltaMaster Berichtsserver als Datenexportwerkzeug

Der DeltaMaster Berichtsserver erlaubt ebenfalls den Export von aggregierten Daten. In Frage kommen vornehmlich die Exportformate XLS und CSV.

Der reine Datenexport ist im Vergleich zum Weg über SSIS oder OpenQuery auch von einem Anwender durchführbar, der sich ausschließlich mit DeltaMaster und DeltaMaster Berichtsserver auskennt. Die Integration in einen ETL-Prozess mittels SSIS erfordert allerdings auch entsprechende Kenntnisse, es entfällt aber der Aufbau einer Verbindung mit dem Quellsystem, da dies von DeltaMaster übernommen wird sowie die MDX-Codeerstellung.
Der Berichtsserver wird als externes Programm eingesetzt. Der Export von Excel-Dateien im Berichtsserver ist zu bevorzugen, da diese den Export von zusätzlichen Attributen usw. erlauben, was vom einfachen CSV-Format derzeit nicht oder nicht in vollem Umfang unterstützt wird.

Zu Bedenken ist ggf., dass dieser Exportweg nicht für sehr große Datenexporte geeignet ist. Beim Einsatz von Iterationen müssen zudem viele einzelne Exportdateien eingesammelt werden.

Ein einfaches Beispiel kann wie folgt wiederum auf Basis der Chair-Demo aufgebaut werden. Dazu wird ein Cockpit angelegt, das die benötigten Daten zeigt.

• Dieses Cockpit wird als Bericht abgespeichert. Am einfachsten ist es hierfür in DeltaMaster einen neuen Ordner anzulegen, der den oder die Berichte enthält, die exportiert werden sollen.
• Die Berichtsserverjobs für den Export sind nun anzulegen, ein einfaches Beispiel ist entsprechend im nachfolgenden Bild dargestellt.

Das Ergebnis des Exports vom Typ CSV-Datei, wie im Screenshot zuvor zu sehen, übernimmt jedoch keine zusätzlichen Attribute etc. Im Beispiel wurden Kunden gemäß den Cockpiteinstellungen mit deren Bezeichnungen exportiert.

Wenn auch diese Bezeichnungen und weitere Attribute im Export benötigt werden, bietet sich somit der Export als XLS-Datei an, da mit diesem Format Cockpits nahezu ohne Abstriche exportiert werden können.

• Für eine Automatisierung des Exports kann bspw. die zu generierende Periode für ein aktuelles Datum im Berichtsserver ersetzt werden
  (bspw. strtomember(“[Periode].[Periode].[Monat].&[‘+format(now(),”yyyy”)+”
  “+format(now(),”MM”)+”]‘, CONSTRAINED)

Alternativ kann das Berichtsupdate-Element auch direkt per SQL-Task via SSIS vor Ausführung des Berichtsserver-Jobs in der Berichtsserver-Datenbank gesetzt werden, natürlich auch für andere Dimensionselemente.

• Ein passendes SSIS-Paket wird dann zusammengestellt aus einer Flatfile-Connection, die die zu importierenden Exportdateien, bspw. CSV-Dateien aufgreift, sowie bspw. einer OLE-DB Connection auf eine SQL-Server Datenbank. Im Packet selbst sorgt ein SQL-Task wiederum ggf. für Löschen und Anlegen der Zieltabelle, die den Datenexport aufnimmt.
• Weiterhin wird ein Task vom Typ “Prozess ausführen” benötigt, der den Reportserver startet. Wenn gleich mehrere Jobs ausgeführt werden sollen, kann man im Reportserver eine Jobgruppe bilden oder stattdessen den “Prozess-Ausführen” Task bspw. in einen For-Schleifencontainer legen.

„Task Prozess ausführen”:

Mit dem For-Schleifencontainer lassen sich die Jobnummern leicht über eine Integer-Variable definieren. Im folgenden Screenshot werden die Nummern für die Berichtsserverjobs 5-9 gebildet. Dies setzt eine geschlossene Nummerierung der Exportjobs im Berichtsserver voraus. Es ist daher ggf. besser, für alle Exportjobs im Berichtsserver eine Jobgruppe zu bilden, die dann die Definition eines Schleifencontainers überflüssig werden lässt (siehe auch die Aufrufparameter des Berichtsservers mittels Reportserver.exe /?).

• Die generierten Export-Dateien des Berichtsservers – im Beispiel CSV-Dateien – werden mittels ForEach-Schleifencontainer eingesammelt und nacheinander in das Zielsystem importiert.

• Der Ablauf des Packages sieht nun wie folgt aus: