Power Pivot-Daten in Access-Datenbank speichern

Hallo zusammen,

vor ungefähr 15 Jahren wurde ich stolzer Besitzer eines Mini-Disk-Recorders. Für diejenigen unter Euch, denen diese bahnbrechende Technologie nichts mehr sagt gehts hier zum entsprechenden Wikipedia-Artikel.

Was an der Mini-Disk so genial war, war die Tatsache, dass man digitale Kopien von CDs erstellen konnte, auch ohne einen PC mit CD-Brenner zu haben. Heute, im Zeitalter der MP3-Player und CD-/DVD-/Blu Ray-Brenner überhaupt kein Thema mehr. Aber damals war das wirklich klasse. Die Sache hatte nur einen Haken: Man konnte jedes digitale Medium auf eine Mini-Disk kopieren, aber man bekam nichts mehr von der Mini-Disk herunter. Was dort einmal drauf war, konnte man hören und löschen, aber auf kein anderes Medium (digital) übertragen.

Warum langweile ich Euch mit diesen steinzeitlichen Informationen?! Weil mich diese Situation an Power Pivot erinnert. Ich bekomme aus so ziemlich jeder Datenquelle dieser Welt meine Daten in die Power Pivot hinein, aber wie bekomme ich Sie wieder heraus, wenn ich beispielsweise nicht der Dateneigner bin, sondern nur die Power Piot zugesendet bekommen habe? Ich kann die Daten über eine Pivottabelle darstellen und über einen Doppelklick auf eben diese (Drill-Through) auch auf Teile der Originaldaten zugreifen, aber spätestens, wenn die zurückgegebene Datenmenge die 1 Mio. Zeilen überschreitet ist Schluß. Dasselbe gilt für DAX Queries. Auch diese werden innerhalb eines Excel-Sheets dargestellt und sind somit an die Zeilenbegrenzung gebunden. 

Ihr wollt wissen wie Ihr an die Daten herankommt? Dann viel Spaß beim aktuellen Post :)


Für diejenigen unter Euch, die Excel 2013 nutzen gibt es gute Neuigkeiten: Kasper de Jonge hat auf seinem Blog eine Möglichkeit beschrieben, wie man das Ergebnis eines DAX-Query an einen ADO-Recordset übergeben kann. Danach stehen einem alle erdenklichen Möglichkeiten offen, mit diesem Recordset umzugehen. Kasper speichert das Ergebnis anschließend in einer csv-Datei. Hier der Link zu Kaspers Post.

Da ich meine Daten immer gern in einer Datenbank vorliegen habe, habe ich das Makro von Kapser derart modifiziert, dass aus Excel heraus folgende Schritte geschehen:

1. Erstellen einer Access-Datenbank "Test-DB" in dem Ordner, in welchem die aktuelle Exceldatei liegt.
2. Je Tabelle des Power Pivot-Datenmodells wird in der Access-Datenbank eine gleichnamige Tabelle erstellt.
3. Nachdem die jeweilige Tabelle in Access erstellt wurde, wird für diese Tabelle im Power Pivot-Datenmodell ein Durchlauf der Spalten durchgeführt, bei welchem folgende Informationen ausgelesen werden:
• der Name einer jeden Spalte,
• der Datentyp einer jeden Spalte.
4. Nach der Ermittlung dieser beiden Informationen werden alle Spalte der jeweiligen Tabelle des Power Pivot-Datenmodells in Access angelegt und im Anschluss ein adäquater Datentyp für die Spalte in Access gewählt.
5. Nachdem die Tabellen des Power Pivot-Datenmodells in Access nachgebaut wurde, werden die Daten aus dem Power Pivot-Datenmodell in die Access-Tabellen kopiert. Dabei ist programmtechnisch folgendes zu beachten:
• Um die Struktur des Datenmodells in Power Pivot auszulesen habe ich vom VBA-Objekt Model Gebrauch gemacht. Über das Objekt Model kann ich auf die einzelnen Tabellen (ModelTable) zugreifen und von diesen wiederum auf die einzelnen Spalten (ModelTableColumn). Gefährlich wird es hierbei bei Spalten, die aus dem Clienttool ausgeblendet wurden.

  

  Spalten aus dem Clienttool ausblenden

  Diese Spalten werden über das VBA-Skript nicht erfasst. Das gleiche gilt für berechnete Spalten. Sie werden über das VBA-Skript ebenfalls nicht als ModelTableColumn erfasst. Dies gilt allerdings nicht für Spalten, die über die RELATED()-Funktion in die jeweilige Tabelle gezogen wurden. Derartige Spalten werden als ModelTableColumn durch VBA identifiziert.
• Im Gegensatz zu VBA mit seinem Objektmodell kann eine Tabelle aus dem Power Pivot-Modell jederzeit über ein entsprechendes DAX-Query-Statement komplett abgefragt werden. EVALUATE <TableName> liefert ein vollständiges Ergebnis mit allen enthaltenen Spalten, auch den berechneten und ausgeblendeten. (An dieser Stelle sei gesagt, dass das DAX-Query keine Umlaute verträgt. Daher verwendet bei den Tabellennamen bitte keine Umlaute)

 ==> Dies bedeutet für den Datenexport in die Access-Datenbank jedoch, dass durch das DAX-Query eine Tabelle zurückgegeben wird, die in der Access-Datenbank eventuell anders aussieht, also ggf. weniger Spalten besitzt (alle jene Spalten, die im Power Pivot-Datenmodell ausgeblendet, oder berechnet waren!). Dieser Problematik kann man sicherlich auf vielen Wegen beikommen und ich habe in meinem Beispiel auch nicht die performanteste Lösung gewählt. Darum soll es hierbei auch gar nicht gehen. Die gesamte Performance ist nicht besonders hoch, sobald man anfängt Daten über Recordsets auszutauschen.
Was dieses Beispiel jedoch zeigt ist ein Umgang mit den seit Excel 2013 neuen Objekten in VBA, mit denen in begrenztem Umfang auf das Datenmodell von Power Pivot zugegriffen werden kann und ich glaube, dass der ausührlich kommentierte Quellcode in meiner Exceldatei dafür sorgen wird, dass sich einige von Euch in Zukunft mehr mit den neuen Objekten in Excel beschäftigen werden :)

Die Exceldatei mit entsprechendem Datenmodell und kommentiertem VBA-Skript könnt ihr hier downloaden. Der Durchlauf des Programms kann durchaus einige Minuten dauern. Verliert also nicht die Geduld ;)

Ich freue mich über Eure Kommentare und wünsche Euch jetzt schon ein frohes Fest und einen guten Rutsch ins Jahr 2015.

Lars