Time-Intelligence im DirectQuery-Mode von Microsoft Power BI

Die Analyse von Daten nach Zeiträumen und Vergleichsperioden ist eine wesentliche Anforderung an die Reporting- und BI-Lösungen vieler Unternehmen. Zum Glück bietet DAX mit den sog. Time-Intelligence Funktionen eine Vielzahl an Möglichkeiten, solche Analysen schnell und effizient durchzuführen.

Allerdings werden Time-Intelligence Funktionen, zumindest zum jetzigen Zeitpunkt, nicht in allen Einsatzszenarien von Power BI unterstützt. Eines dieser Szenarien ist der Datenzugriff via DirectQuery.

In diesem Artikel werfen wir einen Blick auf die Möglichkeiten Time-Intelligence unter Verwendung von DirectQuery in Power BI einzusetzen und auf die Vor- bzw. Nachteile der verschiedenen Optionen.

Wenn man von Time-Intelligence spricht, geht es im Wesentlichen, aber nicht ausschließlich, um Funktionen in DAX. In DAX existieren eine Reihe von Funktionen, die die Kalkulation und den Vergleich von Kennzahlen über Zeiträume bzw. mit Vergleichszeiträumen unterstützen. Zeiträume können dabei Tage, Monate, Quartale oder auch Jahre sein. Eine Übersicht aller relevanten Funktionen findest Du im DAX-Guide von Marco Russo und Alberto Ferrari unter der Rubrik "Time Intelligence functions".

Time-Intelligence Funktionen lassen sich in zwei Kategorien einteilen. Die Erste liefert einen skalaren Ergebniswert ohne die Verwendung der CALCULATE()-Funktion. Ein Beispiel hierfür ist die Funktion TOTALYTD(), die den kumulierten Wert für einen beliebigen Zeitpunkt innerhalb eines Jahres berechnet.

Bei der zweiten Gruppe handelt es sich um Funktionen, die eine Tabelle zurückgeben, die als Filter für CALCULATE() dient. Eine solche Funktion ist z.B. DATESBETWEEN(), die eine Datumstabelle mit allen Datumsangaben zwischen dem gewünschten Start-bzw. Enddatum zurückgibt.

Insbesondere die Funktionen der zweiten Gruppe machen deutlich, dass es bei Time-Intelligence in Power BI nicht nur um ein bestimmtes Set an DAX-Funktionen geht. Damit Funktionen wie DATESBETWEEN() eine Datumstabelle als Filter an CALCULATE() übergeben können, muss eine solche Datumstabelle zunächst einmal in Deinem Datenmodell existieren. Aufs einfachste heruntergebrochen ist eine Datumstabelle eine Tabelle, die für jeden Tag der betrachteten Jahre eine Zeile bzw. Datumsangabe enthält. Erweitert wird diese Tabelle dann z.B. um Spalten, die Informationen zum Jahr, Monat oder Quartal enthalten, zu dem das jeweiligen Datum gehört. Aus diesem Grund nenne ich eine solche Tabelle in meinen Datenmodellen auch gerne "Zeiträume" bzw. "Periods", weil der Nutzer aus ihr den Zeitraum auswählt, für den er seine Analyse durchführen möchte. Begriffe wie "Datumstabelle" oder auch "Kalendertabelle" sind aber wesentlich verbreiteter bzw. gebräuchlicher.

Als Quelle für eine solche Kalendertabelle kommen dabei Datenbanken, Excel- bzw. CSV.-Files, das Internet oder die Erstellung solcher Tabellen in Power Query oder mit DAX im Power BI-Datenmodell in Betracht.

(Eine Möglichkeit in Power Pivot für Excel zeige ich Dir z.B. auf meinem YouTube-Kanal: Kalendertabellen in Power Pivot für Excel erzeugen)

Je nach Situation können die Anforderungen an eine solche Kalendertabelle natürlich komplexer werden. Wenn Du Dich intensiver damit beschäftigen möchtest, empfehle ich Dir eine Artikelserie von Lars Schreiber. In mehreren Beiträgen hat Lars verschiedene inhaltliche und technische Anforderungen an eine Datums- oder Kalendertabelle zusammengetragen.

DirectQuery ist einer der drei wesentlichen Datenzugriffsmodi von Power BI, also eine Form, in der in Deinen Reports auf die zugrundeliegenden Quelldaten zugegriffen wird. Die beiden weiteren Zugriffsmodi, die Power BI zur Verfügung stellt, sind der klassische Import, bei dem die Basisdaten aus den verschiedenen Datenquellen in das Datenmodell von Power BI geladen werden und dann im Arbeitsspeicher zur Analyse zur Verfügung stehen und sog. Streaming-Daten, bei dem die Daten in Echtzeit an den Power BI Service gepusht und ausgewertet werden. Je nach Modus werden die Streaming-Daten dabei nur wenige Sekunden bis wenige Minuten in Power BI gespeichert.

Im Gegensatz dazu handelt es sich bei DirectQuery um eine Live-Verbindung, die zu bestimmten Datenquellen, im Wesentlichen relationalen Datenbanksystemen wie dem Microsoft SQL Server, aufgebaut werden kann. Dabei werden die Daten nicht nach Power BI importiert, sondern quasi "on-demand" für die jeweilige Visualisierung von der Datenquelle abgefragt. Außer im Cash werden keine Daten in Power BI bzw. in der Cloud gespeichert. Damit ist DirectQuery eng mit den sog. Live-Connections verwandt. Von Live-Connections wird gesprochen, wenn die Live-Verbindung nicht zu einer relationalen Datenbankquelle, sondern zu einer multidimensionalen Quelle, wie z.B. einem Analysis Services Cube, besteht. Eine aktuelle Übersicht der von DirectQuery unterstützten Datenbanksysteme findest in der Dokumentation von Microsoft: Von DirectQuery in Power BI unterstützte Datenquellen

Zum Entstehungszeitpunkt dieses Beitrags sind dies folgende Datenbanken:

In welchen Situationen solltest Du den Datenzugriff über DirectQuery in Betracht ziehen? Zum einen natürlich, wenn Du Deine Reports mit Live-Daten versorgen möchtest. Verzögerungen in der Aktualität, wie beim Import mit zeitplangesteuerten Aktualisierungen, hast Du mit DirectQuery nicht.

Zudem hast Du bei großen Datenmengen mit DirectQuery die Möglichkeit einige Volumenrestriktionen von Power BI (1GB Maximalgröße der Power BI Desktop-Datei, 10GB Umfang eines Power BI Pro Accounts) zu überwinden, da bei DirectQuery kein Datenimport erfolgt. Für DirectQuery wäre lediglich zu beachten, dass das Ergebnis-Set einer einzelnen Abfrage den Umfang von 1 Million Datensätzen nicht überschreiten darf.

Des weiteren kommt DirectQuery für manche Unternehmen als hybride Lösung in Betracht, weil sie den Power BI Service als Reporting Front-End nutzen möchten, ihre Daten aber nicht in der Cloud speichern möchten.

Einer der Nachteile von DirectQuery liegt sicher in der Performance im Vergleich zum Import-Modus, weil DirectQuery nicht von der Geschwindigkeit der In-Memory Datenverarbeitung profitieren kann und zudem durch die vielen Abfragen auf den Server zu erhöhten Netzauslastungen führt.

Des weiteren werden eine Reihe von Funktionalitäten in Power BI bei Verwendung von DirectQuery nicht unterstützt. Darunter sind z.B. Einschränkungen bei der Datenaufbereitung mit Power Query, der Datenmodellierung im Power BI Desktop oder die Nutzung von Quick Insights im Power BI Service. Insbesondere die Nutzung der DAX-Language ist deutlichen Einschränkungen unterworfen.

Eine genauere Auseinandersetzung mit den Vor- und Nachteilen von DirectQuery findest auf Matt Allingtons Blog oder auch bei Reza Rad: Introduction to DirectQuery bzw. DirectQuery Connection in Power BI - How does it work? Limitations and Advantages

Beachte dabei jedoch, dass auch die Möglichkeiten mit DirectQuery durch die rasante Entwicklungsgeschwindigkeit von Power BI Änderungen unterworfen sein können.

Die Einschränkung von DAX-Funktionen im DirectQuery-Mode umfasst mehrere Abstufungen. Generell gibt es Funktionen, die unter Verwendung von DirectQuery genutzt werden können und solche, für die das nicht gilt. In der Gruppe der nutzbaren Funktionen ist dann wiederum nach Funktionen zu unterscheiden, die uneingeschränkt in allen Formeltypen (Measure-/Abfrageformeln, berechnete Spalten, Regeln für Row Level Security usw.) eingesetzt werden können und solchen, die nur in Measure- bzw. Abfrageformeln nutzbar sind. Die zum derzeitigen Zeitpunkt gültige Liste der Funktionen findest Du hier:

DAX formelkompatibilität im DirectQuery-Modus 

Hier die zum aktuellen Zeitpunkt gültige Liste:

Ein Blick auf die Liste zeigt, dass eine Vielzahl der Time-Intelligence Funktionen von DAX in der List nicht enthalten sind, Time-Intelligence im DirectQuery-Modus also nur stark eingeschränkt unterstützt wird. Zudem gibt diese Liste darüber Auskunft, welche Funktionen für die Verwendung unter DirectQuery hinsichtlich ihrer Performance optimiert sind. Funktionen, die nicht in der Liste geführt sind, können nur mit ungewisser Performance genutzt werden.

Wie kannst Du Time-Intelligence Funktionen nun trotz dieser Einschränkungen im DirectQuery-Modus verwenden? 

Danach kannst Du auch im DirectQuery-Modus sämtliche Time-Intelligence Funktionen nutzen. Die nachfolgende Tabelle zeigt einige Beispiele:

"Total Sales YTD" berechnet die kumulierten Sales des Jahres. "Total Sales PM" und "Total Sales PQ" berechnen, ausgehend vom aktuellen Monat, die Umsätze des Vormonats (z.B. für die Zeile Aug., die Umsätze Juli) bzw. die Gesamtumsätze des vorherigen Quartals, während "Total Sales Roll." ausgehend vom aktuellen Monat die rollierenden Umsätze der letzten 12 Monate berechnet. "Total Sales PY" zu guter Letzt, zeigt die Umsätze des jeweiligen Monats des Vorjahres. Hier die Übersicht der einzelnen Measure-Definitionen:

Diese Definitionen verdeutlichen, wie mächtig die Time-Intelligence Funktionen in DAX sind. Sie ermöglichen es auf einfache Weise, mit wenig Code, verschiedenste zeitbezogene Berechnungen durchzuführen. Gelb markiert sind dabei jeweils die Time-Intelligence Funktionen, die dies in unserem Beispiel möglich machen. Auch wenn wir Time-Intelligence Funktionen also grundsätzlich auch unter DirectQuery nutzen können, bleibt trotzdem zu beachten, dass sie weiterhin nicht zu den für DirectQuery optimierten Funktionen gemäß der obigen Liste zählen. 

Die zweite Möglichkeit Time-Intelligence mit DirectQuery umzusetzen ist nun, auf genau diese Funktionen zu verzichten. Dazu muss man wissen, dass Time-Intelligence Funktionen in DAX im Grunde nichts anderes als sog. "Syntax-Sugar", also eine vereinfachte Schreibweise für eine andere Measuredefinition, sind. Das obige Measure "Total Sales YTD" mit der Definition TOTALYTD(…) kann alternativ auch mit einer Kombination der Funktionen CALCULATE() und DATESYTD() abgebildet werden. Da DATESYTD() seinerseits wiederum zu den Time-Intelligence Funktionen gehört und somit nicht optimiert ist, kann man auch diesen Ausdruck weiter aufbrechen und in eine Kombination der Funktionen CALCULATE()/FILTER()/ALL() überführen, also Funktionen, die für die Nutzung mit DirectQuery optimiert sind. Mit dieser Kombination können im Grunde alle Time-Intelligence Funktionen nachgestellt werden. Nachfolgend siehst Du die obigen fünf Measure-Definition in ihrer überführten Fassung:

Das Grundprinzip dieser Measures ist dabei im Wesentlichen immer das Gleiche: In den gelb markierten Variablen werden zunächst die Start- und End-Zeitpunkte ermittelt, auf die man filtern möchte. Je nach Measure geschieht dies auf unterschiedliche Weise. Bei den Measures "Total Sales PM" und "Total Sales PQ" nutze ich dazu z.B. die Hilfsspalten [YearMonthNumber] and [YearQuarterNumber] aus der Datumstabelle "Period". Hierbei handelt es sich um Integer-Werte, gebildet aus dem Jahr und der Monats- bzw. Quartalsnummer (z.B. 201507 für den Juli 2015).

Stehen diese Daten fest, wird über CALCULATE() der Filterkontext des Measures "Total Sales" so verändert, dass er dem gewünschten Zeitraum entspricht. Dies geschieht, indem die gesamte Tabelle "Period", also ALL(Period), unter Verwendung von FILTER() auf die entsprechenden Zeiträume eingeschränkt wird.

Natürlich gibt es mehrere Möglichkeiten diese und andere Measures zu überführen. Auf der Seite daxpatterns.com findest Du andere Lösungen zur Überführung dieser und einer Reihe weiterer Time-Intelligence Measures: Time Patterns

Obwohl die Definitionen mit und ohne Verwendung der Time-Intelligence Funktionen zu den gleichen Ergebnissen führen, offenbart ein Blick auf die Performance der einzelnen Varianten Unterschiede:

Time-Intelligence in DAX kann mitunter ein komplexes Thema sein. Zwar ermöglichen die vorhandenen Funktionen eine schnelle und komfortable Analyse zeitbezogener Daten, allerdings sind sie in einer Reihe von Nutzungsszenarien mit Einschränkungen verbunden. Wie aber ist ihre Nutzung im Zusammenhang mit DirectQuery zu beurteilen?

Obwohl Time-Intelligence Funktionen (derzeit) nicht für die Nutzung mit DirectQuery optimiert sind, können Sie in bestimmten Situationen, durch ihre einfache Schreibweise, zu "Quick-Wins" bei der Umsetzung von DirectQuery-Lösungen führen. Dies kann immer dann der Fall sein, wenn die Performance-Nachteile für die Nutzer des Reports nicht spürbar ins Gewicht fallen. Dies ist aber im Einzelfall zu prüfen. Wenn man sich allerdings aufgrund der erheblichen Menge an Quelldaten für die Nutzung des DirectQuery-Mode entschieden hat, ist Performance schnell ein wichtiger Faktor, bei dem es um jedes Prozent gehen kann.

In diesem Fall ist es ratsam etwas mehr Aufwand in die Definition der Measures und etwaiger Hilfsspalten in der Kalendertabelle zu investieren, um durch die Nutzung von performance-optimierten Funktionen eine bessere User-Experience zu gewährleisten. Dies erhöht zwar den Aufwand, hat neben der verbesserten Performance aber auch den schönen Nebeneffekt, dass die Measures ebenfalls für andere Szenarien gerüstet sind, in denen Time-Intelligence Funktionen nicht verwendet werden können. Dies kann z.B. die Verwendung eines sog. "Custom Calendars" für zeitbezogene Analyse von Daten sein. Ein Kalender, der von der Struktur des klassischen gregorianischen Kalenders abweicht, um so Verzerrungen in der Analyse zu bereinigen. (z.B. ein 4-4-5 Kalender, in dem alle Quartale dem gleichen Wochenrhythmus folgen)

Ich hoffe ich konnte Dir das Thema Time-Intelligence mit DirectQuery etwas näher bringen. Wenn Dir der Artikel gefallen hat, würde ich mich freuen, wenn Du auch bei meinen nächsten Artikeln wieder reinschaust.

Vielleicht teilst Du den Artikel auch mit anderen und besuchst mich auf meinem YouTube-Kanal. Dort findest Du noch mehr Inhalte zu Power BI, Excel, DAX und M! Ich würde mich freuen!

Bis dahin!

Viele Grüße aus Hamburg

Uwe