Online Magazine

Mit DAX Studio Leistungsdaten aus Power BI ziehen

Du hast langsame Power-BI-Reports und willst herausfinden warum? In diesem Hack zeige ich dir, wie man mit DAX Studio Leistungsdaten aus Power BI sammelt und Verbesserungspotential sichtbar macht.

von Salvatore Cagliari

Langsame Ladezeiten von Reports in Power BI können ziemlich mühsam sein. Wer das Problem beheben will, muss allerdings zuerst einmal wissen, wo es liegt. Dafür gilt es, Performance-Metriken sammeln und analysieren zu können.

Um die Aussage der Metriken zu begreifen, müssen wir zunächst den internen Aufbau eines Power-BI- (oder Analysis-Services-)Datenmodell verstehen. Danach schauen wir uns anhand eines Beispiels an, wie man die Metriken sammelt und was sie bedeuten.

Jedes Tabulare Datenmodell hat zwei Engines:

Storage-Engine
Formula-Engine

Schauen wir uns also an, welche unterschiedliche Eigenschaften diese beiden haben und welche Aufgaben sie erfüllen.

Storage-Engine (SE)

Die Storage-Engine (Kurz: SE) ist die Schnittstelle zwischen der DAX-Abfrage und den im Tabellenmodell gespeicherten Daten. Sie nimmt jede beliebige DAX-Abfrage entgegen und sendet sie an die Vertipaq-Storage-Engine, welche die Daten im Datenmodell speichert.

Dafür verwendet die SE eine Sprache namens xmSQL, die auf der Standard SQL-Sprache basiert, allerdings nur einige wenige und einfache Operatoren unterstützt: +, -, /, *, =, <> und IN. Um Daten zu aggregieren, unterstützt xmSQL SUM, MIN, MAX, COUNT und DCOUNT (Distinct Count). Ausserdem unterstützt es GROUP BY, WHERE und JOINs. Entsprechend ist ein Grundverständnis in Sachen SQL-Abfragen sehr hilfreich, um SE-Abfragen zu verstehen.

Im Bezug auf die Leistungsmetriken, ist es wichtig zu wissen, dass die SE eine Multithreading-Komponente ist. Das heisst, sie verwendet mehrere CPU-Kerne, um die Ausführung einer Abfrage zu beschleunigen. Ausserdem kann die SE Abfragen und deren Ergebnisse im Cache zwischenspeichern, was sie insbesondere eine wiederholte Ausführung derselben Abfrage schneller ausführen lässt.

Formula-Engine (FE)

Die Formula-Engine (Kurz: FE) ist die DAX-Engine. Alle Funktionen, die von der SE nicht ausgeführt werden können, werden von der FE ausgeführt. Beispielsweise holt die SE die Daten aus dem Datenmodell und übergibt das Ergebnis an die FE. Dieser Vorgang wird «Materialization» genannt, da die Daten im Speicher abgelegt werden, um von der FE verarbeitet zu werden.

Die SE kann die FE auch aufrufen, wenn eine xmSQL-Abfrage Funktionen enthält, welche die SE nicht ausführen kann. Diese Operation wird «CallbackDataID» genannt.

Im Gegensatz zur SE ist die FE single-threaded und hat keinen Cache. Das bedeutet:

Keine Parallelität durch Verwendung mehrerer CPU-Kerne
Keine Wiederverwendung der wiederholten Ausführung der gleichen Abfrage

Aus diesem Grund sollte man so viele Operationen wie möglich an die SE auslagern und Materialization sowie CallbackDataID wenn möglich umgehen. Leider kann aber nicht direkt definiert werden, welcher Teil des DAX-Codes von welcher Engine ausgeführt wird. Es gilt entsprechend, bestimmte Muster zu vermeiden, um sicherzustellen, dass die richtige Engine die Arbeit in möglichst kurzer Zeit erledigt.

Schauen wir uns nun an, wie wir mit DAX Studio Leistungsdaten aus Power BI abrufen und so herausfinden können, wie viel Zeit von jeder Engine gebraucht wird.

Das sind die Top 3 Hacks von Salvatore Cagliari

1. Data Analysis mit Modulo

2. Aufträge zählen mit DAX

3. Datenvisualisierung mit "Small Multiples"

Wie kannst du die Leistungsdaten abrufen?

Als Grundvoraussetzung, um Leistungskennzahlen abrufen zu können, benötigst du DAX-Studio auf deinem Rechner. Der Download-Link ist bei Bedarf unter den Referenzen aufgeführt. Solltest du die Software nicht installieren können, ist es möglich, eine portable Version von der gleichen Website zu beziehen. Einfach die ZIP-Datei herunterladen, entpacken, in einem beliebigen lokalen Ordner speichern und dann DAXStudio.exe starten, um alle Funktionen ohne Einschränkungen nutzen zu können.

Nun musst du die DAX-Abfragen aus Power BI holen. Dafür gilt es zunächst, den Performance Analyzer in Power BI Desktop zu starten:

_{Abbildung 1: Starten des Performance Analyzers in Power BI Desktop}

Sobald das Performance-Analyzer-Fenster zu sehen ist, kann die Aufzeichnung der Leistungsdaten und der DAX-Abfrage für alle Visuals beginnen:

_{Abbildung 2: Starten des Performance Analyzers in Power BI Desktop}

Zuerst "Start recording", dann auf "Refresh visuals" klicken, um das Rendering aller Visuals der aktuellen Seite neu zu starten. Wenn du nun das Dropdown-Menü einer Zeile in der Liste anklickst, kannst du überprüfen, ob das entsprechende Visual aktiviert ist. Ausserdem befindet sich dort ein Link zum Kopieren der DAX-Abfrage in die Zwischenablage.

_{Abbildung 3: Visual auswählen und Abfrage in die Zwischenablage kopieren}

Wie du siehst, benötigte Power BI 80'606 Millisekunden, um das Rendering der Matrix-Visuals abzuschliessen. Allein die DAX-Abfrage benötigte 80'194 Millisekunden. Dies ist ein sehr schlechtes Zeichen für die Measure, die in diesem Visual verwendet wird.
Nun kann DAX Studio gestartet werden. Falls du es auf dem Rechner installiert hast, findest du es in der Multifunktionsleiste «External Tools»:

_{Abbildung 4: Starten von DAX Studio aus Power BI Desktop}

DAX-Studio wird gestartet und automatisch mit der aktuellen Power-BI-Desktop-Datei verbunden.

Falls du DAX-Studio separat starten musst, kannst du wie folgt mit dem offenen Power BI-Desktop eine Verbindung herstellen:

_{Abbildung 5: Manuelle Verbindung von DAX Studio mit Power BI Desktop}

Nachdem die Verbindung hergestellt ist, wird eine leere Abfrage in DAX Studio geöffnet. Im unteren Teil des DAX-Studio-Fensters befindet sich ein Log-Bereich, in dem man sieht, was passiert.

Bevor du jedoch die DAX-Abfrage aus Power BI Desktop einfügst, musst du die Server-Timings in DAX Studio starten (rechte obere Ecke des DAX-Studio-Fensters):

_{Abbildung 6: Starten der Server-Timings in DAX Studio}

Nach ein paar Sekunden wird im Log-Bereich gemeldet, dass das Trace gestartet wurde.
Nachdem du die Abfrage in den leeren Editor-Fenster eingefügt hast, musst du die Schaltfläche "Clear on Run" aktivieren und danach die Abfrage mit «Run» ausführen:

_{Abbildung 7: Aktivieren der Funktion "Clear on Run"}

"Clear on Run" stellt sicher, dass der Cache der Storage-Engine vor der Ausführung der Abfrage geleert wird. Das Löschen des Caches vor der Messung von Leistungsdaten ist das Best-Pratice-Vorgehen, um einen konsistenten Ausgangspunkt zu bekommen.
Nach der Ausführung der Abfrage wird am unteren Rand des DAX-Studio-Fensters die Seite "Server Timings" angezeigt:

_{Abbildung 8: Fenster "Server Timings" in DAX Studio}

Jetzt siehst du die Informationen, die wir als Nächstes untersuchen werden, um herauszufinden, warum der Power-BI-Report so langsam war.

Was kannst du aus diesen Daten herauslesen?

Auf der linken Seite von «Server Timings» hast du Einblick in die Ausführungszeiten:

_{Abbildung 9: Ausführungszeiten}

Hier siehst du die folgenden Zahlen:

Total: Die gesamte Ausführungszeit in Millisekunden (ms)
SE CPU: Die Summe der von der Storage-Engine (SE) für die Ausführung der Abfrage aufgewendeten CPU-Zeit (normalerweise ist diese Zahl grösser als die Gesamtzeit, da die parallele Ausführung mehrere CPU-Kerne nutzt)
FE: Die von der Formula-Engine (FE) aufgewendete Zeit und der prozentuale Anteil an der gesamten Ausführungszeit
SE: Die von der Storage-Engine (SE) aufgewendete Zeit und der prozentuale Anteil an der gesamten Ausführungszeit
SE Queries: Die Anzahl der für die DAX-Abfrage benötigten Storage-Engine-Abfragen
SE Cache: Die Nutzung des Storage-Engine-Cache, falls vorhanden

Als Faustregel gilt: Je grösser der prozentuale Anteil der Storage-Engine-Zeit im Vergleich zur Formula-Engine-Zeit ist, desto besser.

Der mittlere Abschnitt zeigt eine Liste der Storage-Engine-Abfragen:

_{Abbildung 10: Liste der Storage-Engine-Abfragen}

Diese Liste zeigt, wie viele SE-Abfragen für die DAX-Abfrage ausgeführt wurden und enthält einige statistische Spalten:

Line/Indexzeile: Normalerweise werden nicht alle Zeilen angezeigt. Die vollständigen Zeilen sind einsehbar, wenn du auf die Schaltflächen «Cache» und «Internal» in der oberen rechten Ecke des Zeitfensters des Servers klickst. Sie sind jedoch nicht sehr nützlich, da sie eine interne Darstellung der sichtbaren Abfragen sind. Manchmal kann es hilfreich sein, die Cache-Abfragen zu sehen, um zu erkennen, welcher Teil der Abfrage durch den SE-Cache beschleunigt wurde.
Subclass: Normalerweise "Scan"
Duration: Für jede SE-Abfrage aufgewendete Zeit
CPU: Für jede SE-Abfrage aufgewendete CPU-Zeit
Par.: Parallelität jeder SE-Abfrage
Rows und KB: Grösse der «Materialization» durch die SE-Abfrage
Waterfall: Zeitliche Abfolge der SE-Abfragen
Query: Der Anfang jeder SE-Abfrage

In diesem Fall hat die erste SE-Abfrage 12'527'422 Zeilen an die Formula-Engine zurückgegeben (die Anzahl der Zeilen in der gesamten Faktentabelle) und dabei 1 GB Speicher verbraucht. Das ist nicht gut, denn grosse «Materialization» wie diese sind Performance-Killer.

Es ist eindeutig, dass in diesem DAX-Code ein bedeutender Fehler gemacht wurde.

Zum Schluss lässt sich der eigentliche xmSQL-Code der ausgewählten Zeile lesen:

_{Abbildung 11: Abfragecode der ausgewählten Zeile}

Damit kannst du versuchen, das Problem der DAX-Abfrage zu verstehen. In diesem Fall siehst du, dass es eine hervorgehobene «CallbackDataID» gibt. DAX Studio macht alle Abfragen in der Abfrageliste fett, die eine CallbackDataID enthalten.

Wie du siehst, wird in diesem Fall eine IF()-Funktion an die Formula-Engine (FE) übergeben, da die SE diese Funktion nicht verarbeiten kann. Die SE weiss jedoch, dass die FE diese Funktion ausführen kann. Also ruft sie die FE für jede Zeile des Ergebnisses auf. In diesem Fall über 12 Millionen Mal. Wie du in der Messung sehen konntest, benötigt dies eine Menge Zeit.

Jetzt weisst du, dass der geschriebene DAX-Code fehlerhaft ist und die SE die FE viele Male aufruft, um eine DAX-Funktion auszuführen. Ausserdem benötigst du 1 GB RAM für die Ausführung der Abfrage und die Parallelität ist nur 1,9-fach, was viel besser sein könnte.

Was kannst du nun tun?

Die DAX-Abfrage enthält nur die von Power-BI-Desktop erstellte Abfrage. Aber in den meisten Fällen benötigen wir den Code der Measure um den Fehler zu finden und zu beheben.

DAX Studio bietet eine Funktion namens "Define Measures", um den DAX-Code der Kennzahl aus dem Datenmodell zu extrahieren:

Füge eine oder zwei Leerzeilen am Anfang der Abfrage ein
Setze den Cursor auf die erste (leere) Zeile
Suche die Kennzahl im Datenmodell
Klicke mit der rechten Maustaste auf die Kennzahl und dann auf «Define Measure» (oder: «Kennzahl definieren»)
Wenn deine Measure eine andere Measure aufruft, kannst du auf «Define Dependent Measures» klicken.

_{Abbildung 12: Definieren von Measures in DAX Studio}

Mit diesem Feature extrahiert DAX Studio den Code aller Measures, die von der ausgewählten Measure verwendet werden.

Das Ergebnis ist eine DEFINE-Anweisung gefolgt von einer oder mehreren MEASURE-Anweisungen, die den DAX-Code unserer verantwortlichen Measure enthalten.

Nun kann der Code der Measure angepasst werden, ohne die Measure im Datenmodell zu modifizieren.

Nachdem ich den Code in diesem Beispiel optimiert hatte, führte ich die neue Abfrage aus und verglich die Server-Zeiten mit den Originaldaten:

_{Abbildung 13: Vergleich zwischen langsamem und schnellem DAX-Code}

Jetzt dauerte die gesamte Abfrage nur 55 Millisekunden und die SE erstellte eine «Materialization» von nur 19 Zeilen. Die Parallelität liegt beim 2,6-fachen, was besser ist als 1,9-fach. Es sieht so aus, als ob die SE nicht so viel Rechenleistung benötigt, um die Parallelität zu erhöhen.

Dies ist ein sehr gutes Zeichen.

Der letzte Schritt ist nun der modifizierte Code der Measure in das Datenmodell zu übertragen, um damit den Report selber zu optimieren.

Fazit

Wenn du ein langsames Visual im Power-BI-Report hast und den Grund dafür herausfinden willst, brauchst du zunächst einige Informationen. Mit dem Performance Analyzer in Power BI Desktop kannst du herausfinden, wo die Zeit für das Rendern des Ergebnisses des Visuals verbraucht wird.

Wenn du siehst, dass die Ausführung der DAX-Abfrage viel Zeit in Anspruch nimmt, musst du mit DAX Studio – wie in diesem Hack beschrieben – das Problem herausfinden und versuchen, es zu beheben.

Aber die Behebung von Performance Probleme in Measures ist eine andere Geschichte – die ich euch in einem separaten Hack zeigen werde ...