This article shows a special data modeling trick that leads to simpler and more usable data models. This technique is applicable to data models that perform computations on facts in different datasets. Let me demonstrate the technique on a simple data model.
We need to combine the
Quarterly Bonus and the
Payment facts in a single report. One option is to follow the M:N modeling technique. This technique is suitable for situations when we can’t change the data model of the project. The option that I’m introducing in this article require the project’s data model change. However it leads to more usable metrics and overall better performance. The first step is that we need to push the
Employee dataset’s facts to the
Salary dataset. Here is the new model:
Let me emphasize that there are 205 employee records and 3876 salary records in my test data. This means that the 205 different
Quarterly Bonus values are somewhat duplicated (de-normalized) in the 3876 salary records. We need to handle the duplicities in our metrics definitions. Here are the metrics:
Salary Paid :
Employee Quarterly Bonus :
Bonus Paid :
The MIN aggregation in the
Employee Quarterly Bonus metric converts all duplicate
Quarterly Bonus values to a single value per Employee and Quarter. As all these values should be the same, we could also use the MAX or AVG aggregation.
Here is the report with all metrics computed on my test data: