Microsoft BI SSAS Tabular: über die Mächtigkeit von DAX-Measures

Data Analysis Expressions (DAX) ist eine Abfrage- und Modellierungssprache für SQL Server Analysis Services (SSAS) Tabular, Power-BI und Excel Power Pivot. Mit ihr können berechnete Spalten, Tabellen und benutzerdefinierte Measures auf sehr anspruchsvollem Niveau erstellt werden.

Wie nützlich DAX in der betriebswirtschaftlichen Praxis ist, zeigt eindrucksvoll eine Entwicklung im Umfeld des Entgelttransparenzgesetzes. Dieses relativ neue Gesetz gibt Arbeitnehmern das Recht, von ihrem Arbeitgeber einen validen Nachweis für die Angemessenheit der eigenen Bezahlung im Vergleich zu Mitarbeitern gleichgelagerter Tätigkeiten zu verlangen. Mit Hilfe der noventum-Lösung HR-Analytics, die sich auch der DAX-Formelsprache bedient, können Unternehmen dieser Verpflichtung nachkommen.

Was ist DAX?

Mit Hilfe von Data Analysis Expressions (DAX) können sehr mächtige und trotzdem intuitiv benutzbare Measures in SSAS Tabular Modellen erstellt werden. Hierbei ist es z.B. möglich, Aggregationen bzgl. unterschiedlicher Dimensionen zu schachteln, komplexe Berechnungsformeln zu hinterlegen, Time-Intelligence-Funktionalitäten zu nutzen und Filterkontexte zu verändern bzw. zu setzen. Trotz der dadurch möglichen komplexen Definitionen können bei gutem Design die Endanwender bzw. Analysten intuitiv mit diesen Measures arbeiten ohne dabei Gefahr zu laufen, fachlich falsche Ergebnisse zu produzieren.

Ein schönes Beispiel für die Mächtigkeit, Eleganz, Einfachheit und intuitive Nutzung von DAX-basierten Measures innerhalb eines SSAS Tabular Modells ist eine der Implementierungen für die Umsetzung des Entgelttransparenzgesetzes in der noventum- Lösung HR-Analytics. Beim Entgelttransparenzgesetz geht es vereinfacht gesagt darum, dass Unternehmen ihren Mitarbeitern/innen unter gewissen Umständen eine durchschnittliche Lohnauskunft bereitstellen müssen. [Für fachliche Details zum Entgelttransparenzgesetz sei an dieser Stelle auf das Whitepaper von noventum-Berater Dr. Benjamin Klör verwiesen.] Technisch gesehen handelt es sich bei dieser Lohnauskunft streng genommen nicht um einen „Durchschnitt“, sondern um den „Median“ von Gehältern in einer bestimmten Vergleichsgruppe aus Mitarbeitern mit ähnlichen Eigenschaften. Das folgende Beispiel ist aber genauso mit Durchschnitt, Minimum und Maximum umsetzbar.

Beispiel: Entgelttransparenzgesetz mit HR-Analytics

Auf Basis eines bereits bestehenden Datenmodells mussten für die Auswertungen bzgl. des Entgelttransparenzgesetzes lediglich drei zusätzliche DAX-Measures definiert werden.

Die erste Measure rechnet die Bruttogehaltsbestandteile aus der entsprechenden Faktentabelle fact_Salaries mit Hilfe des zugehörigen Beschäftigungsgrades aus der dim_CapacityLevel auf ein „Full Time Equivalent“ (FTE) hoch. Z.B. werden hiermit 2.000 USD eines Mitarbeiters mit einem Beschäftigungsgrad von 50 % zu 4.000 USD (pro FTE) umgerechnet:

Die zweite Measure zählt lediglich die in der gleichen Faktentabelle vorkommenden Verweise auf die Datumsdimension (in diesem Fall handelt es sich um eine Monatsgranularität):

Die finale dritte Measure „iteriert über“ bzw. berechnet für jeden Mitarbeiter über Division der auf FTE’s normierte Summe der Gehaltsbestandteile (s.o.) durch die Monatsanzahl mit Gehaltszahlungen (s.o.) ein fiktives Jahresgehalt und bildet über dieses Ergebnis (pro Mitarbeiter) dann den Median:

Mögliche Ursachen für den Unterschied der Median-Werte bei ansonsten gleichbleibender Selektion könnten in diesem Fall sein, dass der den Median ausmachende männliche Kollege im Zeitraum Juli bis November eine deutlich höhere Gehaltserhöhung als die entsprechende weibliche Kollegin hatte oder der entsprechende männliche Kollege im November eine besondere, einmalige Bonuszahlung hatte, etc. Eine genauere Analyse dieses Effekts kann mit entsprechenden Berechtigungen natürlich per ad-hoc Analyse auf dem HR-Analytics SSAS Modell erfolgen – bis hinunter zu einzelnen Mitarbeitern, Gehaltsbestandteilen und Monaten.

Durch den im HR-Analytics bereits aufgespannten, dimensionalen Raum bzgl. der Gehälter (Gehaltsbestandteile, Mitarbeiterzugehörigkeit, Altersgruppen, Stellen, Organisationszuordnungen, Mitarbeitereigenschaften, etc.) kann in dem hier vorgestellten, exemplarischen Power BI Dashboard sehr einfach eine Auskunft bzgl. des Entgelttransparenzgesetzes für dynamisch selektierbare Vergleichsgruppen erstellt werden, z.B. „alle Entwickler, Techniker und Ingenieure in der Gesellschaft ‚Best Run US‘ mit 8-15 Jahren Mitarbeiterzugehörigkeit, die keine Führungsfunktion haben“ – dies trifft auf 4 Frauen und 13 Männer zu, wobei der Median des hochgerechneten Jahresgehaltes für diese Vergleichsgruppe bei den Frauen bei 54.720 USD und bei den Männern bei 60.001 USD liegt:

Auch wenn die definierte DAX-Measure in einer Kreuztabelle mit Power BI oder in einer Pivottabelle mit Excel genutzt wird, wird immer der mathematisch korrekte Median der auf FTE und volles Jahresgehalt hochgerechneten Gehälter ermittelt. Und das nicht nur für Einzelwerte, sondern auch für Zwischensummen und Gesamtsummen der resultierenden Tabelle:

Performance und intuitiver Nutzung der DAX-Measures

Dieses Beispiel verbildlicht die Eleganz von DAX: Mit 13 formatierten(!) Zeilen Code kann eine Measure definiert werden, die eine durchaus anspruchsvolle Aggregationsvorschrift beinhaltet und dennoch in beliebigen Filterkontexten – inkl. Zwischen- und Gesamtsummen – immer zum korrekten Ergebnis führt und damit von Endanwendern sehr intuitiv genutzt werden kann. Immer wieder begegnen uns Anwendungsfälle, die mit DAX und SSAS Tabular im Vergleich zu den meisten anderen OLAP-Tools nur sehr schwer oder mit Abstrichen bei Performance und Nutzbarkeit implementiert werden können – z.B. ein gleitender Durchschnitt zur Glättung von saisonalen Umsätzen, verschiedenste Durchschnitte und letzte Werte bei Bestandsgrößen, Fehlerausgaben anstelle eines realen Werts falls verschiedene Währungen aggregiert werden („Choose Currency!“), etc.

Wer sich mit DAX beschäftigt, wird schnell über die ansonsten typischen Aggregationen auf einer Kennzahlenspalte (ca. 95 % SUM und ggf. 5% AVG/MIN/MAX) hinausdenken und realisieren, dass er ggf. vorher durch die Funktionalität anderer OLAP-Tools in seiner Kreativität eingeschränkt war. Auch Endanwender in Fachbereichen denken meist in diesen einfachen Strukturen, aber sobald sie die Mächtigkeit von DAX an einigen Beispielen gesehen haben, werden sie gemeinsam mit den Entwicklern sehr mächtige und für ihre analytischen Auswertungen hochinteressante Measure-Definitionen konzipieren.