by ZD (@zsvoboda)
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 that we have mentioned earlier. 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:
SELECT SUM(Payment)SELECT MIN(Bonus (Quarterly)) BY Employee, Quarter/Year (Payment)ALL IN ALL OTHER DIMENSIONSSELECT SUM(Employee Quarterly Bonus)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:
