Microsoft BI SSAS Tabular: on the power of DAX Measures
How noventum HR-Analytics with DAX enables the implementation of the Transparency in Pay Act (EntgTranspG – Entgelttransparenzgesetz)
// Business Intelligence, HR-IT-Management
Data Analysis Expressions (DAX) is a query and modelling language for SQL Server Analysis Services (SSAS) Tabular, Power-BI, and Excel Power Pivot. Calculated columns, tables, and user-defined Measures can be created at a very sophisticated level.
How useful DAX is in business practice is impressively illustrated by a development in the area of the Development in Pay Act. This relatively new law provides employees with the right to demand valid proof from their employer for the commensurability of their own remuneration in comparison to employees with similar activities. With the help of the HR-Analytics solution of noventum, which also relies on the DAX formula language, companies can comply with this obligation.
What is DAX?
With the help of Data Analysis Expressions (DAX), very powerful, yet intuitively usable Measures can be created in SSAS Tabular models, e.g. allowing to nest aggregations with respect to different dimensions, store complex calculation formulas, use time-intelligence functionalities, and modify and/or set filter contexts. Despite the complex definitions made possible, end users and/or analysts can work with these Measures intuitively due to the good design, without running risk of producing materially wrong results.
One of the implementations for the implementation of the Transparency in Pay Act in the noventum solution HR-Analytics is a beautiful example for the power, elegance, simplicity, and intuitive use of DAX-based Measures within an SSAS Tabular model. Put simply, the Transparency in Pay Act stipulates that companies must provide their employees – under certain circumstances – with information on the average wage. [For material details regarding the Transparency in Pay Act, see the white paper by noventum consultant Dr. Benjamin Klör .] From a technical point of view, this wage information is, strictly speaking, not an “average”, but rather a “median” of the salaries in a certain comparison group consisting of employees with similar characteristics. But the following example can be implemented just as well with average, minimum, and maximum.
Example: Transparency in Pay Act with HR-Analytics
Based on an already existing data model, only three additional DAX Measures had to be defined for the analyses with respect to the Transparency in Pay Act.
The first Measure extrapolates the gross salary components from the corresponding table of facts fact_Salaries with the help of the corresponding degree of employment from the dim_CapacityLevel to a “Full Time Equivalent” (FTE). With this, for example, USD 2,000 of an employee with a degree of employment of 50 % are converted to USD 4,000 (per FTE):
The second Measure merely counts the references to the date dimension occurring in the same table of facts (in this case, this is a monthly granularity):
The final third Measure “iterates over” and/or calculates for each employee via division of the sum of the salary components standardised to FTEs (see above) by the number of months with salary payments (see above) a fictitious annual salary and then builds the median across this result (per employee):
The iteration across individual employees is mandatory because there can be different salary components in the table of facts that need to be summed up first before the median is built – e.g. normal gross monthly salary, pro-rata payment of a 13th salary and other bonuses such as a performance bonus or Christmas bonus. Determining a median based on the individual components would therefore lead to false results.
The convincing aspect of the final DAX Measure for determining the median is that it can be used very generically in the analysis, and an “incorrect use” with illogical results is virtually excluded. It is, for example, possible to perform with it both point in time related analyses with respect to the current salary of a specific month as well as period of time related analyses with respect to salaries of several months (e.g. effective annual salaries incl. salary increases, bonus payments, etc.).
In the period of time related analyses it is implicitly taken into consideration in the extrapolation of the fictitious annual salary via the iteration across individual employees that there can be employees who were not employed over the whole period of time under consideration. In the consideration of the effective salaries of a full year for an employee who was only employed for 2 months in this year and earned a total of USD 8,000 (per FTE) during this period, a fictitious annual salary of USD 8,000 * (12 / 2) = 48,000 is calculated. For another employee who was employed all year long and earned a total of USD 50,000 (per FTE), USD 50,000 * (12 / 12) = US 50.000 would be calculated.
Utilisation of the DAX Measures in dashboards and analyses
In the following images, a Power-BI dashboard created ad-hoc based on the SSAS Tabular model for HR-Analytics is depicted. For the currently configured filter context, it can be seen that there are 153 female and 233 male employees who have – point-in-time-related – a fictitious extrapolated median annual salary, based on the salaries applicable in November, of USD 46,733 for the women and USD 48,001 for the men. In the second image, the effective salaries of the 2nd half of the year are extrapolated to a fictitious annual salary with a median in the amount of USD 46,340 for the women and USD 46.341 USD for the men.
In this case, potential causes for the difference in the median values in the otherwise identical selection could be that the male colleague constituting the median in the period of July to November received a significantly higher salary increase than the corresponding female colleagues or that the corresponding male colleague received a special one-time bonus payment, etc. A more precise analysis of this effect can, of course, be performed – with corresponding authorisations – via ad-hoc analysis on the HR-Analytics SSAS model, all the way down to individual employees, salary components, and months.
Through the dimensional space already set out in HR-Analytics with respect to the salaries (salary components, employee tenure, age groups, positions, organisational allocation, employee characteristics, etc.), it is possible in the exemplary Power-BI dashboard presented here to very easily create an output with respect to the Transparency in Pay Act for dynamically selectable comparison groups, e.g. “all developers, technicians, and engineers in the company ‘Best Run US’ with 8-15 years of employee tenure that are not in a management position” – this applies to 4 women and 13 men, wherein the median of the extrapolated annual salary for this comparison group is USD 54,720 for the women and USD 60,001 for the men:
Even if the defined DAX Measure is being used in a cross table with Power-BI or in a pivot table with Excel, the mathematically correct media of the salaries extrapolated to an FTE and full annual salary is always being determined. And this does apply not only to individual values but also to sub totals and total amounts of the resulting table:
Performance and intuitive utilisation of the DAX Measures
This example illustrates the elegance of DAX: With 13 formatted(!) lines of code, a Measure can be defined that contains a quite sophisticated aggregation rule and nevertheless always leads to the correct result in any filter contexts – including sub totals and total amounts – can therefore be utilised very intuitively by end users. Time and again, we are encountering use cases which can be implemented only with a lot of difficulty or with sacrifices in performance and usability with DAX and SSAS Tabular in comparison to most of the other OLAP tools – e.g. a sliding average for the equalisation of seasonal revenues, a variety of averages and last values for existing parameters, error outputs instead of real values if different currencies are being aggregated (“Choose Currency!”), etc.
Those dealing with DAX will quickly think beyond the otherwise typical aggregations on an indicator column (approx. 95 % SUM and, where applicable, 5% AVG/MIN/MAX), and realise that they, where applicable, were previously limited in their creativity by the functionality of other OLAP tools. End users in specialist departments also quite often think in these simple structures, but once they have become aware of the power of DAX based on a few examples, they conceptualise, jointly with the developers, very powerful and – for their analytical assessments – highly interesting Measure definitions.