Zeitlich befristet abrufbare Daten per Power BI Datamarts persistieren am Beispiel der Power BI Activity Logs

Vergangene Woche hatte ich eine interessante Diskussion mit meinem Freund und Co-Organisator der Power BI User Group Switzerland, Kristian Bubalo. Es ging darum, ob es möglich ist, nur mit Power BI Bordmitteln temporär verfügbare Daten zu persistieren und überraschenderweise haben wir eine extrem praktikable Lösung dabei entdeckt. Als konkretes Beispiel hatten wir die Diskussion über Daten, welche per REST-API abgerufen werden und nur einen gewissen Zeitraum zur Verfügung stehen. Für diesen Artikel verwende ich als Beispiel die Daten des Power BI Activity Log, welches jeweils nur die Werte der letzten 30 Tage rollierend zum Abruf bereitstellt.

Materialisierung der Daten benötigt Query Folding für den inkrementellen Refresh

Zur Materialisierung solcher Daten innerhalb der Power BI Toolbox besteht die einzige Möglichkeit darin, die Daten per incremental refresh zu persistieren. Der Knackpunkt ist allerdings, dass der incremental Refresh nur mit relationalen Quellen funktioniert, da QueryFoling unterstützt werden muss. Dies ist bei einer REST-API leider nicht der Fall.

Allerdings ist genau dies nun möglich durch die im Mai 2022 veröffentlichten Power BI Datamarts!

Kurzer Hinweis vorweg, meine gesamte Lösung stelle ich am Ende des Artikels zum Download bereit.

Architektur

Das Setup ist sehr einfach durchzuführen. Wir benötigen lediglich zwei Elemente:

1. Einen Power BI Datamart zum Laden der täglichen Daten per REST-API
2. Einen Power BI Dataflow, welcher die Werte per incremental Refresh persistiert

Die Daten werden aus der REST-API in den Power BI Datamart geladen. Daraufhin kann man auf die Azure SQL Datenbank des Datamarts einen incremental refresh anwenden, da die Azure SQL Datenbank des Datamarts QueryFolding unterstützt und somit incremental geladen werden kann.

Der Datamart dient somit als staging area, der Dataflow dient zum Persistieren der Daten. Die REST-API kann idealerweise täglich geladen werden. Die neuen Daten werden dann in per incremental refresh persistiert. Das Vorgehen ist folgendermaßen angedacht:

Implementierung

In diesem Artikel geht es hauptsächlich um die Persistierung von Daten, welche nicht direkt per incremental Refresh archiviert werden können und nicht um die Anbindung der REST API. Daher beschreibe ich die Schritte zum Anbinden des Power BI Activity Logs nur grob bzw. verlinke auf entsprechende Blogposts.

Die Implementation besteht aus 2 Schritten:

1. Laden der Daten per REST-API in den Power BI Datamart
2. Laden der Werte aus dem Datamart in den Dataflow

Laden des Power BI Activity Logs per Power BI Datamart

Das Laden der Activity Logs kann theoretisch auch mit einem persönlichen Account mit Admin Rechten erfolgen. Ich würde aber dringend empfehlen, dies mit einem Service Principal Account durchzuführen. Hierzu muss in einem ersten Step eine Azure App erstellt werden, welche nur lesenden Zugriff hat. Der folgende Link erklärt, wie ihr dies erstellen könnt:

How to use a service principal in Power BI Admin REST APIs in Power Automate – Forward Forever

Das Laden der Daten aus der REST-API kann nun wie gewohnt im Power Query innerhalb von Power BI Desktop entwickelt werden. Einzige Einschränkung in dem Datamart, sowie den Dataflows sind rekursive Funktionen zum aktuellen Zeitpunkt nicht möglich. Rekursive Funktionen sind Funktionen, die sich selbst aufrufen. Die API für das Activity Log liefert beim ersten Aufruf einen continuation Token zurück. Die API muss dann so oft aufgerufen werden, bist kein Token mehr zurückgeliefert wird. Aus diesem Grund wäre eine rekursive Funktion die sauberste Lösung. Dies funktioniert im Power BI Desktop, aber nicht in der Cloud. Falls jemand von Microsoft dies liest, dann bitte ermöglicht rekursive Power Query Funktionen!

Mein Lösungsansatz ruft die API einfach 75 mal auf und ignoriert die weiteren Aufrufe, sobald kein Token mehr zurückgeliefert wird.

Integration des REST-API Loads in den Datamart

Zuerst erstellen wir uns einen neuen Datamart. Diesen können wir unter “New” und dann “Datamart” erstellen:

Falls die Anbindung der API noch nicht entwickelt ist, kann das hier geschehen. Ich persönlich finde die Entwicklung in PowerQuery innerhalb von Power BI Desktop allerdings angenehmer. In diesem Szenario gehe ich davon aus, dass wir die Anbindung der API bereits in Power Query erstellt haben. Somit können wir einfach eine leere Query als Datenquelle wählen und dann die Abfragen aus unserem PowerQuery innerhalb von Power BI Desktop per Ctrl + C und Ctrl + V hier einfügen:

Im nächsten Schritt können wir die Query einfach leer lassen und auf “Next” klicken:

Danach kann die Query einfach aus der Power BI Datei in den Datamart kopiert werden. Wie bereits erwähnt, kann die Beispieldatei am Ende des Artikels heruntergeladen werden:

Nach einem Klick auf “Next” sind wir mit dem Datamart im Prinzip fertig. Wir müssen den Datamart noch umbenennen, den automatischen Refresh aktivieren und wir wären bereit für das Persistieren der Daten. Beim Erstellen des Datamarts wird immer ein Datamart, welcher auf Azure SQL basiert, sowie ein Dataset erstellt. In unserem Fall interessiert uns nur der Datamart, da wir kein Reporting auf dem Dataset erstellen. Beim Datamart müssen wir auch die automatische Aktualisierung aktivieren:

Wir setzen den täglichen Refresh beispielsweise auf 6 Uhr morgens:

Persistieren der Daten am Beispiel Power BI Dataflows

Nach dem ersten manuellen Refresh der Daten sind die ersten Tage nun in den Power BI Datamart geladen. Nun müssen wir die Persistierung der Daten anstoßen. Wir werden hier einen Power BI Dataflow anlegen, welcher die Daten per inkrementellem refresh persistiert. Aktuell benötigt ein incremental refresh mit Power BI Dataflows eine Premium oder Premium per User Lizenz. Es gibt aber auch Alternativen ohne Premium. Auf die komme ich im Kapitel “Alternative Lösungsansätze” zu sprechen.

Zuerst benötigen wir den Connection String zur Azure SQL Datenbank des Datamarts. Diesen erhalten wir in den Optionen des Datamarts:

Nun erstellen wir einen neuen Power BI Dataflow:

Wählen dann “Add new tables”:

Im darauffolgenden Dialog wählen wir die Azure SQL Datenbank:

Als Server fügen wir den kopierten Connectionstring von unserem Datamart ein, das Feld Datenbank können wir leer lassen und als Authentifizierung wählen wir “Organizational account”. Eventuell müssen wir uns dann noch einloggen und mit einem klick auf “next” sind wir auf unsere Datamart Datenbank verbunden:

Auf der linken Seite wählen wir unsere “ActivityManual” Tabelle, welche wir im Datamart angelegt haben. Unsere dort angelegten Tabellen erscheinen alle in dem Schema “model”. Danach gehen wir auf “Transform data”:

Danach öffnet sich PowerQuery im Browser. Änderungen müssen wir prinzipiell keine machen, einzig wir müssen sicherstellen, dass unsere Datum-Spalte vom Typ DateTime ist. Wie können die Tabelle ebenfalls umbenennen, falls uns der ursprüngliche Name nicht gefällt:

Nach der Bestätigung über “Save & close” wäre der Dataflow ebenfalls eingerichtet. Nun müssen wir noch den incremental refresh einrichten. Hierzu klicken wir auf das incremental refresh icon:

Wir müssen dann die Spalte wählen, welche unser Datum enthält. Ebenfalls müssen wir definieren, über welchen Zeitraum die Daten in der Tabelle behalten werden sollen. In meinem Beispiel habe ich 5 Jahre gewählt sowie welcher Zeitraum täglich gelöscht und wieder neu geladen werden soll. In meinem Beispiel habe ich gewählt, dass die letzten 4 Tage täglich gelöscht und wieder neu geladen werden sollen:

Generell würde ich immer ein paar Tage rollieren aktualisieren, da sich die Werte des Activity Logs rückwirkend für ein paar Tage nach der Erfassung noch ändern können.

Nach dem Erstellen des Dataflows mit incremental refresh muss dieser noch refreshed werden. Ich würde dies direkt manuell machen. Danach muss noch ein täglicher automatischer Refresh erstellt werden, zeitlich sollte dies geschehen, nachdem der Datamart refresh fertig ist:

Somit lädt der Power BI Datamart jeden Morgen automatisch die Logs der letzten Tage. Daraufhin werden die neuesten Daten automatisch per incremental refresh in den Power BI Dataflow geladen und somit materialisiert.

Abgrenzung und alternative Lösungsansätze

In diesem Artikel wurde die Möglichkeit aufgezeigt, eine zeitlich begrenzt verfügbare Datenquelle wie die Power BI Activity Log per Datamart und inkrementellem Refresh im Dataflow zu persistieren.
Für diesen Fall lag die Einschränkung darin, nur Power BI Technologien zu verwenden. Allerdings gibt es noch ein paar Punkte zu beachten

Archivierung in Cold Storage

Obwohl die Persistierung per Dataflow möglich ist, gibt es noch zu bedenken, dass die Daten zwar im Dataflow archiviert, aber auch nur dort vorhanden sind. Dies bedeutet, dass mit der Löschung des Dataflows die Daten ebenfalls verloren wären. Ein Backup der Dataflow Daten ist ohne weitere Ansätze nicht möglich.
Eine Möglichkeit wäre es hier den “Bring your own datalake” Ansatz zu verwenden und die Daten somit vom Dataflow zu trennen.

Ebenfalls wäre es möglich, die täglichen Daten im Datamart zu archivieren, beispielsweise mit einem PowerAutomate Flow, welcher die Daten in einem BLOB storage oder in Sharepoint persistiert. In solch einem Fall kann man auf das filebasierte Archiv zu jeder Zeit zugreifen und auch zukünftig die Historie wiederherstellen.

Alternativen ohne Premium oder Premium per User

Der oben beschriebene Weg benötigt für den inkrementellen Refresh für die Datamarts als auch für die Power BI Dataflows eine Premium oder Premium per User Lizenz. Falls diese nicht vorhanden ist, muss ein anderer Ansatz gewählt werden. Theoretisch könnte man die Daten auch in einen Power BI Dataflow laden und die Werte direkt in einem Power BI Dataset persistieren. Hier haben wir allerdings wieder das Problem, dass eine Archivierung somit nicht vollständig gegeben ist, da bei einem erneuten Processen des Datasets sämtliche Daten gelöscht werden. Die historischen Daten können dann nicht mehr wieder geladen werden.

Daher wäre auch hier mein Vorschlag, die Daten entweder per PowerAutomate oder Azure DataFactory vom Power BI Datamart anderweitig zu persistieren, beispielweise in einem Azure SQL, BLOB Storage oder einfach im Sharepoint.

Download Lösung: