Der gewöhnliche Weg von betriebswirtschaftlichen Daten in BI-Anwendungen ist weitgehend bekannt: Aus Vorsystemen werden Ist-Daten extrahiert, oftmals in relationalen Datenbanken zwischengespeichert, angereichert und verarbeitet. Danach werden die Daten in OLAP-Datenbanken importiert und den Anwendern für Analyse, Planung und Berichterstattung in einem Frontend (z.B. Bissantz DeltaMaster) bereitgestellt (vgl. Abbildung 1). In diesem Blogbeitrag wird nun beschrieben, wie, quasi entgegengesetzt, diese Daten wieder aus einer MS Analysis Services 2005/2008 (OLAP-)Datenbank über die OLE DB Schnittstelle direkt in eine MS SQL Server 2005/2008 Tabelle exportiert werden können (roter Pfeil in der Abbildung).
Abb. 1: Architektur von BI-Systemen
Wozu kann dieses “Sich-im-Kreise-drehen” sinnvoll sein? Im Blogbeitrag „Das Einmaleins der Planung mit DeltaMaster“ wurde der komplexe Prozess der integrierten Unternehmensplanung beschrieben. Aus Ist-Daten des Vorsystems können relational Vorschlagswerte für die aktuelle Planung generiert und in die OLAP-Datenbank eingespielt werden. Der Benutzer (Planer) bekommt so in seinem Frontend für ihn individuell gestaltete Eingabeberichte, in denen er die Vorschlagswerte anpassen und um weitere (Plan-)Werte ergänzen kann. Zudem können die Planwerte über Rechenformeln in ein Deckungsbeitragsschema überführt werden; neue, komplex kalkulierte Kennzahlen entstehen in der OLAP-Datenbank ohne direkten Bezug zu den Daten im Vorsystem. Die so neu erzeugten Daten (veränderte Vorgabewerte, neue Planwerte, berechnete Werte) sollen nun aus der OLAP-Datenbank in eine Tabelle exportiert werden – sei es zur Datensicherung, zur Datenweitergabe an andere Systeme oder um daraus neue Vorschlagswerte oder neue Szenarien zu generieren, die wiederum in die OLAP-Datenbank eingespielt werden können.
Schritt 1: Verknüpfung zur OLAP-Datenbank herstellen
Zuerst wird im Microsoft Management Studio ein Verbindungsserver zur OLAP-Datenbank angelegt.
use master EXEC sp_addlinkedserver @server='MyOlapLinkedServer', @srvproduct='', @provider='MSOLAP', @datasrc='bc-test0808', @catalog='Chair2008' go
- @server ist ein beliebiger Verbindungsname, hier “MyOlapLinkedServer”.
- @srvproduct bleibt leer.
- @provider muss “MSOLAP” heißen.
- @datasrc ist der Name des OLAP-Servers, hier “BC-Test0808″.
- @catalog ist der Name der OLAP-Datenbank, hier unsere Testdatenbank “Chair2008″
Nach Ausführen des Codes sollte im SQL-Managementstudio unter “Serverobjekte/Verbindungsserver” der Server mit allen Datenbanken sichtbar sein (vgl. Abbildung 2).
Abb. 2: Verbindungsserver im SQL-Managementstudio
Schritt 2: MDX-Abfrage mit dem Openquery-Befehl
Nun kann man über den T-SQL Befehl “Openquery” in einer Select-Anweisung auf den verknüpften OLAP-Server zugreifen. Für die Abfrage an den OLAP-Server wird eine Anweisung in der MDX-Abfragesprache benötigt. In einem ersten Beispiel soll ein einfacher Umsatzbericht in einer Tabelle angezeigt werden. Abbildung 3 zeigt diesen Bericht mit Umsätzen für das Jahr 2008 über alle Produkthauptgruppen zusammen mit dem dafür verwendeten MDX-Ausdruck.
Abb. 3: Einfacher Umsatzbericht mit MDX-Ausdruck
Um das Ergebnis dieser Abfrage in einer Tabelle im SQL-Managementstudio zu erhalten, muss folgende Openquery-Anweisung verwendet werden:
SELECT *
FROM OPENQUERY(
MyOlapLinkedServer,
'SELECT {[Periode].[Periode].[Jahr].&[2008]} ON AXIS(0),
{Descendants([Produkte].[Produkte].[Top].&[1],[Produkte].[Produkte].Levels(1),
SELF_AND_BEFORE)} ON AXIS(1)
FROM [Chair2008]
WHERE ([Measures].[Umsatz])')
Die Abfrage liefert folgendes Ergebnis (vgl. Abbildung 4).
Abb. 4: Openquery-Anweisung auf OLAP-DB mit MDX-Anweisung
Schritt 3: Export komplexer MDX-Abfragen in eine Tabelle
Nach diesem einfachen Beispiel soll nun eine komplexe OLAP-Abfrage in einer Tabelle gespeichert werden. Abbildung 5 zeigt einen Seitenriss von Planzahlen nach Kunde, Produkt und Stoffgruppen. In den Spalten ist ein DB-Schema angeordnet, das zum Teil berechnete Werte enthält (Nettoumsatz, DB 1). Dieses Cockpit soll nun in eine Tabelle exportiert werden.
Abb. 5: Komplexes Planwertecockpit
Tipp 1:
Die benötigte MDX-(Export-) Anweisung kann schnell in DeltaMaster generiert werden: Dazu hakt man unter Extras/Optionen/System “Abfrageanweisungen loggen” an, berechnet das Cockpit neu (F9-Taste) und kopiert den Code aus dem erzeugten mdx.log (Aufruf über Strg+D).
Im obigen Beispiel sieht der MDX-Code folgendermaßen aus:
WITH
MEMBER [Measures].[UserDef 2] AS '[Measures].[Umsatz]-[Measures].[Erlösschmälerungen]
-[Measures].[Rabatt]', SOLVE_ORDER=100
MEMBER [Measures].[UserDef] AS '[Measures].[Umsatz]-[Measures].[Erlösschmälerungen]
-[Measures].[Lohnkosten]-[Measures].[Materialkosten]-[Measures].[Rabatt]',
SOLVE_ORDER=100
SELECT {[Measures].[Umsatz], [Measures].[Erlösschmälerungen], [Measures].[Rabatt],
[Measures].[UserDef 2], [Measures].[Lohnkosten], [Measures].[Materialkosten], [Measures].[UserDef]} ON AXIS(0),
NON EMPTY NonEmpty(Crossjoin(Crossjoin({Descendants([Kunden].[Kunden].[Top].&[1],
[Kunden].[Kunden].Levels(5),LEAVES)},{Descendants([Produkte].[Produkte]
.[Top].&[1], [Produkte].[Produkte].Levels(3),LEAVES)}),
{Descendants([Stoffgruppen].[Stoffgruppen].[Top].&[1],
[Stoffgruppen].[Stoffgruppen].Levels(1),LEAVES)}),Axis(0)) ON AXIS(1)
FROM [Chair2008]
WHERE ([Periode].[Periode].[Jahr].&[2008])
Diese Zeilen setzt man nun in den bereits oben verwendeten Openquery-Befehl ein.
ACHTUNG:
Die im “with member”-Ausdruck verwendeten einfachen Anführungszeichen müssen durch ein weiters einfaches Anführungszeichen ergänzt werden. Der MDX-String wäre sonst falsch.
Da die Ausgabe in eine Tabelle geschrieben werden soll, erweitert man den SQL-Befehl um “Into Tabellennamen”. Im Beispiel soll die Tabelle “T_Export_Planzahlen” heißen (vgl. Abbildung 6).
Abb. 6: Anlegen einer SQL-Exporttabelle
Nach Ausführung der Abfrage wird die Tabelle angelegt und die Inhalte (687 Zeilen) eingefügt.
Tipp 2:
Nachdem die Tabelle einmalig erstellt wurde, enthält sie die von der Schnittstelle gelieferten Spaltennamen und Datentypen. Für die weitere Verarbeitung der Tabelle sollten die Spaltennamen vereinfacht werden. Die Dimensions- und Hierarchienamen sowie die eckigen Klammern können entfernt werden. Zudem können die Datentypen auf ein sinnvolles Maß gebracht werden. “Varchar(50)” sollte für alle Spalten ausreichen.
Tipp 3:
Auch die Wertspalten sollten in der Exporttabelle als Typ “varchar” definiert werden und nicht als Zahl. Über die Schnittstelle können Exponentialzahlen geliefert werden, die dann unter Umständen zu Konvertierungsfehlern führen. Zur weiteren Verarbeitung der Exportwerte z.B. in einer Datenbanksicht ist eine Konvertierung in den Datentyp “float” zu empfehlen.
Tipp 4:
Die so bereinigte Tabelle kann sehr einfach über ein “Insert Into Exporttabelle…” in automatische Prozessroutinen eingebunden werden (z.B. über gespeicherte Prozeduren).
