previous article
For specific use cases such as the contribution margin schema, it makes sense to display key figures in a dimension table instead of the classic fact table. This article describes characteristic features and a method of implementation in Power BI.
Specific disadvantages of the classic fact table
The classic data model for analyse software such as Power BI is the star schema which is characterized by a fact table and various dimension tables. The former - possessing one column per key figure - contains transaction data. Measures each referencing at least one fact column are created to aggregate key figures. However, this method reveals disadvantages for specific use cases such as contribution margin or ROI; these disadvantages can be illustrated by a highly simplified example of a two-stage contribution-margin calculation:
The data model with a classic fact table (with only the date as foreign key in the simplified example) might appears as follows:
- The fact table's disorder and inefficiency increases with the number of key figures.
- It is not possible to represent different hierarchical levels of the key figures.
- Each key figure to be represented requires a separate measure which must be maintained manually. Furthermore, key figures can only be aggregated via additional measures.
- Fixed sorting of key figures in the context of contribution-margin calculations is not possible.
- When creating a visualization, all individual measures must be added manually to it.
- For new key figures, new measures must be created and existing measures must be extended manually, whereby the new measures must also be added manually to the existing visualizations.
Technical implementation of a key-figure dimension
These negative aspects can be avoided by implementing a key-figure dimension. For this, the fact table's key-figure columns are transformed into an attribute column and a value column using the unpivot function in Power Query. Created subsequently is an ID column which receives ID values identical according to those in the attribute column. Now we can create a dimension table based on the ID and attribute columns, and add additional hierarchy levels as well as sort orders as columns.
The fact table's number of columns is significantly reduced by leaving only the foreign keys - including the new Key Figure ID and Key Figure Value columns - there. New key figures do not change the number of columns compared to the conventional fact table. Because only one column with transaction data now exists (negative items must be multiplied by -1 for correct aggregation in reports), only a simple measure must be written to allow display of data:
The data model might thus appear as follows (a hierarchy has already been created for the key-figure dimension):
Visual display
In contrast to the data model without a key-figure dimension ...
- ... the additional columns for sorting also make it possible to visualize the fixed calculation order of the contribution margin schema without manual arrangement of measures. A large number of measures would require enormous expenditure in creation and maintenance here.
- ... the hierarchical structure of the contribution-margin calculation can be visualized via a matrix, for example:
- ... the end user is able to add and remove key figures in visualizations simply through filtration via a hierarchical slicer:
Considering key-figure dimensions
In addition to the advantages described above, however, key-figure dimensions also have certain negative aspects. Creating a measure for a single key figure somewhat complex because all key figures are in the same column which needs to be filtered in the DAX code. Settlement of key figures not registered in source data is also much more complicated, because calculations at the data-record level are not easy.
However, one more positive aspect of using a key-figure dimension is the tables' memory requirements. Although the additional dimension table also requires additional memory, this is compensated by the fact table's smaller size. In our example, however, this is not the case due to the small number of key figures and data records.
Increasing the number of key figures and entries per key figure would reverse the size ratio and relativize the additional memory requirement of the key-figure dimension. In an analysis of memory requirements, this is demonstrated by the, test table "FactContributionMargin Standarf extended" containing 20 additional columns and about 200 new rows:
Recommendation
The described aspects make it highly recommendable to use key-figure dimensions for specific applications such as calculation of contribution margins. This is especially because technical implementation usually does not require an in-depth knowledge of Power BI or DAX.
Do you have a similar use case? Then utilize the key-figure dimension and simply contact us if you have any problems!
Get in touch!
