There are several challenges an organisation faces while implementing modern analytics platforms. One such challenge is moving from a single-measure-hierarchical model to a multi-measure-analytical model. On the face of it, this may not seem much of a fundamental shift in thinking but not managing and educating users in the new way of thinking could spell disaster.
This can be observed more in financial reporting. Historically, this area has depended on single-measure-hierarchical models to address the reporting requirements. As discussed in the previous blogs, finance reporting has some peculiar needs that should be understood and managed to ensure successful delivery and adoption.
Not addressing this can lead to disillusionment, and the finance team is left with the wrong impression about the modern data warehousing platforms. Most customers are left wondering,
“Why can’t the modern analytics platform do what was accomplished rather easily by legacy data platforms?”
Legacy Approach - Single-measure-hierarchical Model
As evident in the name, the main characteristic of this classic model is one measure or Key figure. The screenshot below shows the storage structure of a single key figure model. Data related to different accounts/measures (highlighted in green) are stored under one key figure (highlighted in red) in the fact table.
To enable the reporting of multiple facts, the model leverages hierarchical structures. The finance reporting uses an unbalanced/ragged hierarchy for this purpose. The screenshot below shows the sample of the Account hierarchy (master data table/dimension). This hierarchy provides the logic to derive the “Net Sales”, “Total Cost of Goods Sold”, and “Gross Profit.”
The reporting is facilitated by combining the single key figure stored in the fact table and account hierarchy to produce the result like the following:
The ragged hierarchy only adds to this complexity since the logical parent of members may not be at the level immediately above the member. The hierarchy descends to varying levels for different drill-down paths when this occurs.
The ragged hierarchy only adds to this complexity since the logical parent of members may not be at the level immediately above the member. The hierarchy descends to varying levels for different drill-down paths when this occurs.
Modern Approach - Multi-measure-analytical model
The multi-measure-analytical paradigm is a modern reporting paradigm; this model uses multiple key figures for reporting different facts. Functionally, it pivots the data in the following manner.
Please note that hierarchies are still facilitated in multi-measure data models. Although it struggles with ragged hierarchies, the flat hierarchies are handled natively. The essential advantage of the multi-measure model is the following.
Flexibility in Analysis – Drag and drop of measures instead of the hierarchy navigation
Structure flexibility – Adding new metrics, KPIs, and measures is easy.
Accurate aggregation with explicit data types – Each measure can be defined with a different field type.
Improved calculations - Calculation takes advantage of columnar data structure resulting in faster calculations. The formula written in DAX is transparent and readable. While the MDX could be pretty challenging to understand
Data integration - Data integration is generally faster and requires fewer transformations.
Clearer terminology for charts and tables - By getting rid of ragged hierarchies; you can make data in graphs and tables easier to understand for your viewers and content creators
Finally – Performance
Summary
While implementing a modern analytical platform, organisations should understand the multi-measure-analytical models and their capabilities. Bringing awareness and educating the key business users and citizen developers in the contemporary reporting paradigm is vital. Since the existing problems are the result of current thinking, we will end up with the same systems until the current thought is challenged and modified.