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

Wie noventum HR-Analytics mit DAX die Umsetzung des Entgelttransparenzgesetzes möglich macht

// Business Intelligence, HR-IT-Management

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:

 

Die Iteration über einzelne Mitarbeiter ist zwingend erforderlich, weil es in der Faktentabelle unterschiedliche Gehaltsbestandteile geben kann, die vor der Bildung des Medians zuerst aufsummiert werden müssen – z.B. normales Bruttomonatsgehalt, anteilige Zahlung eines 13. Gehalts und andere Boni, wie Erfolgszuschlag oder Weihnachtsgeld. Direkt einen Median auf Basis der einzelnen Bestandteile zu ermitteln, würde deshalb zu falschen Ergebnissen führen.

Das Überzeugende an der finalen DAX-Measure für die Ermittlung des Medians ist, dass man sie sehr generisch in der Analyse benutzen kann und eine ‚falsche Nutzung‘ mit unlogischen Resultaten nahezu ausgeschlossen ist. Beispielsweise kann man mit ihr sowohl zeitpunktbezogene Auswertungen mit Bezug auf das aktuelle Gehalt eines bestimmten Monats als auch zeitraumbezogene Auswertungen mit Bezug auf Gehälter mehrerer Monate (z.B. effektive Jahresgehälter inkl. Gehaltserhöhungen, Bonuszahlungen, etc.) durchführen.

Bei den zeitraumbezogenen Auswertungen wird bei der Hochrechnung auf das fiktive Jahresgehalt durch die Iteration über einzelne Mitarbeiter implizit berücksichtigt, dass es Mitarbeiter geben kann, die nicht über den gesamten betrachteten Zeitraum angestellt waren. Z.B. würde bei der Betrachtung der effektiven Gehälter eines vollen Jahres für einen Mitarbeiter, der in diesem Jahr nur 2 Monate angestellt war und dabei insgesamt 8.000 USD (pro FTE) verdient hat, als fiktives Jahresgehalt 8.000 USD * (12 / 2) = 48.000 USD errechnet. Für einen anderen Mitarbeiter, der das ganze Jahr über angestellt war und insgesamt 50.000 USD (pro FTE) verdient hat, würde 50.000 USD * (12 / 12) = 50.000 USD gerechnet.

Nutzung der DAX-Measures in Dashboards und Analysen

In den folgenden Abbildungen wird als Beispiel ein ad-hoc erstelltes Power BI Dashboard auf Basis des SSAS Tabular Modells für HR-Analytics dargestellt. Für den aktuell eingestellten Filterkontext sieht man, dass es 153 weibliche und 233 männliche Mitarbeiter gibt, die zeitpunktbezogen auf Basis der im November gültigen Gehälter als Median 46.733 USD bei den Frauen und 48.001 USD bei den Männern als fiktives, hochgerechnetes Jahresgehalt haben. In der zweiten Abbildung werden die effektiven Gehälter der 2. Jahreshälfte auf ein fiktives Jahresgehalt mit dem Median in Höhe von 46.340 USD bei den Frauen und 46.341 USD bei den Männern hochgerechnet.

 

Abbildung 1: „Zeitpunktbezogene“ Auswertung (Gehaltsstände im November werden hochgerechnet)
Abbildung 2: "Zeitraumbezogene" Auswertung (Effektive Gehälter der 2. Jahreshälfte werden hochgerechnet)

 

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:

Abbildung 3: Exemplarische Selektion/Einschränkung möglicher Vergleichsgruppen

 

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:

Abbildung 4: Pivottabelle mit Zwischensummen in Excel

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.

 

 

 

noventum consulting

Marco Nielinger

Senior Consultant

Zurück