Kürzlich habe ich an einem Hackerthon teilgenommen, bei dem das Ziel darin bestand, mit Microsoft PowerBI ein Dashboard für die Überwachung in Echtzeit zu erstellen. Die Daten wurden bereits in SQLServer generiert und persistiert und mussten effizient abgefragt werden. Da ich jetzt mit einem anderen Tech-Stack arbeite, war dieses Unterfangen völlig neu für mich und ich habe eine Menge gelernt, was ich hier dokumentieren möchte.
TLDR: Mit der API-Endpunktfunktionalität von PowerBI ist es möglich, Daten vom SQLServer nur einmal zum Dienst zu pushen, anstatt sie jedes Mal, wenn ein Benutzer die Datenbank aktualisiert, durch eine directquery zu ziehen, was die Leistung und die Nah-Echtzeitüberwachung erhöht.
Was sind datasets in PowerBI?
Die Visualisierung in PowerBI benötigt Zugriff auf eine Form von Datenquelle, die als Dataset dargestellt wird. Abgesehen von Streaming-Datensätzen, über die wir später noch sprechen werden, können Datasets auch ein komplexes Datenmodell darstellen. Datasets sind eine Form der Abstraktion und der zugrundeliegende Quelltyp kann beliebig sein, wie z.B. eine Excel-Tabelle oder eine Datenbank (powerbi-datasets).
Wie kommt man an die Daten
Daten aus einer Datenbank können entweder durch den Import oder directquery (powerbiconnect-data) gezogen werden. Die Verwendung von directquery war die erste Design-Idee. Der Nachteil dieser Methode ist jedoch, dass die zugrunde liegende Abfrage immer dann ausgeführt wird, wenn ein Benutzer mit der Visualisierung interagiert. Wenn viele Personen mit dem Dashboard arbeiten und gleichzeitig klicken und aktualisieren, würde dies eine Menge unnötiger Abfragen an die Datenbank erzeugen.
Streaming/Push-Datensätze und Überwachungin Nah-Echtzeit
With a push dataset, data is pushed into the Power BI service [..] and automatically creates a new database in the service to store the data. Once a report is created using the push dataset, any of its visuals [..] update in real-time whenever the data is updated (powerbi-pushdataset).
Eine weitere Methode, die PowerBI bietet, ist das Öffnen eines API-Endpunkts, über den Daten in ein Streaming/Push-Dataset gepusht werden können. Der Unterschied zwischen diesen Datensätzen ist, dass der Streaming-Datensatz nur die neuesten Daten enthält, während der Push-Datensatz auch historische Daten enthalten kann (powerbi-pushdataset). Mit einer einfachen Web Anfrage ist es möglich, die Daten als json-Objekte an den Endpunkt zu pushen. Für dieses Untergangen kann der SQLServer Agent verwendet werden, um die Datenbank abzufragen und die Daten alle 5 Sekunden in das Push-Dataset zu pushen. Eine Beispielvorgehen für das Skript kann auf auf thinkbi.de eingesehen werden.
Learnings
- Unserer Beobachtung nach enthält das pushdataset automatisch distinkte Daten, was bedeutet, dass dieselben Daten mehrmals gepusht werden können, ohne dass dies Auswirkungen auf nachgelagerte Ergebnisse hat.
- Der PowerBI Endpoint erlaubt anscheinend nur 120 Anfragen pro Sekunde (techtalkercorner)
- Das Pushdataset fasst nur bis zu 200.000 Zeilen und löscht Zeilen gemäß FIFO (powerbi retentionpolicy)
Herausforderungen
Endpoint request limit
Das oben erwähnte Skript fragt die Datenbank ab und erstellt für jede Zeile der Ergebnismenge eine Web Anfrage. Wenn eine Ergebnismenge mehr als 120 Zeilen enthält, führt dies zu einem Fehler aufgrund der Endpunktbegrenzung. Aus diesem Grund wurde eine geringfügige Änderung des Payload-Wrappings vorgenommen, um eine Liste von json-Objekten anstelle einzelner json-Objekte zu erstellen, die dann an die API übermittelt werden.
Optimierungsmöglichkeiten
Eines der Hauptprobleme der directquery besteht darin, dass jedes Mal, wenn ein Nutzer die Daten aktualisiert, eine vollständige Abfrage an die Datenbank ausgeführt wird, was zu einer unnötigen Arbeitsbelastung des Rechners führen kann, wenn mehrere Nutzer dasselbe Dashboard aktualisieren. Im Vergleich dazu fungiert der Push-Datensatz als zentrale Datendrehscheibe und die Abfragen werden nur einmal ausgeführt. Um Nah-Echtzeit zu erreichen, könnte der SQL-Agent so konfiguriert werden, dass er die Datenbank alle 5 Sekunden abfragt und nur die Daten der letzten 10 Sekunden herausfiltert („Delta“). Nichtsdestotrotz wird er die Datenbank auch dann abfragen, wenn keine Änderungen vorliegen. Hierin könnte eine mögliche Optimierung liegen, bei der Change Data Captures (CDC) genutzt werden, um nur neue Daten zu pushen.
Initialisierung
Wir konnten nicht in Erfahrung bringen, wo der Pushdatensatz persistiert wird, gehen aber davon aus, dass er nur im Speicher abgelegt ist. Wenn PowerBI aus irgendeinem Grund abstürzt, können die historischen Daten verloren gehen und müssen neu initialisiert werden.
Weitere Referenzen
Demo: https://www.youtube.com/watch?v=rjXJ0GSUMBg
Einfaches Beispiel der API:https://blogs.lobsterpot.com.au
