My previous post generated a lot of discussion on our internal email lists so I thought I’d post a follow-up encapsulating some of these points.
There are two things going on in these workbooks… data densification which allows the calculation engine to fill in empty cells; and domain padding which creates the empty cells for us in the first place.
The kind of domain padding used in this technique is what I call “explicit” domain padding – it’s triggered by the “show missing values” menu option and works for dates and numeric bins. That’s why we have to bin the PointID field – it would be great if we could do this directly on a numeric dimension but that’s not possible. Domain padding also occurs in a way I call “implicit” domain padding. This happens when you apply a table calculation over an addressing field that has different values in different partitions.
To explain this better – consider the following data:
Note that neither A nor B has a complete year. By right-clicking on the date axis and selecting “show missing values” we trigger explicit domain padding:
You can see that the domain is completed for each category, showing the missing months. However there is no data densification happening at this point – the added cells are null. We can trigger implicit domain padding by simply creating a table calculation that is “compute using” on the inner dimension (in this case month):
I did not have to select “show missing values” but the compute engine filled those values in automatically. Note that in this case we are also seeing data densification as there are now values computed for those new cells.
You can find a workbook I used to test the different combinations here:
To be honest at this point I understand explicit domain padding much better than I understand implicit (that’s still a bit hit and miss for me much like all table calcs are) but when you can harness this effectively it’s surprisingly useful.