Thanks for the email feedback and advice about metric star schema designs in response to my last post. Some very useful suggestions from Griffith got me thinking about storing the actuals as numerator and denominator for percentage measures.
The problem I had was with roll-ups of differing metric ‘currencies’ - percentage v absolute values being handled by the same model. It is easy to aggregate EFTSL actuals on the fly but percentages won’t roll up unless you have the actuals and can recalculate them at the higher level as suggested by Griffith. The problem with this is that the model allocates a status to the measure row which just became dynamic and is now only known at reporting time.
It then struck me that there are only two types of behaviour we need to worry about:
- Percentage
- Absolute
The absolute values encompass things like enrolments, load, income, headcounts etc, they all roll up from an atomic grain very well and the percentage actual elements all roll up too but their targets need to be averaged.
So we need pairs of measure types in the fact table - a target and actual for absolute values and a target and actual for percentage values.
In the roll-up construction we specific sum(x) for the absolute values and avg(x) for the percentage ones and then in the reporting we determine which measures to display based on the properties of the metric being displayed.
At least that is the theory, lets see what happens…
