In insurance, the probability that an event happens is a non aggregatable measure. There are also several measures in investment banking which are not aggregatable, particularly those related to risk. Not aggregatable means you can’t sum it up. For example:
- The probability of building A in City X getting an earthquake in 2011 is 3×10-6.
- The probability of building B in City Y getting an earthquake in 2011 is 4×10-6.
- The probability of both building A and B getting an earthquake in 2011 is 5.6×10-6. You can’t add them up.
Another example:
- The probability of building A in City X getting an earthquake in 2011 is 3×10-6.
- The probability of building A in City X getting an windstorm in 2011 is 2×10-6.
- The probability of building A in City X getting an earthquake or a windstorm in 2011 is 4.7×10-6. You can’t add them up.
We would probably design the fact table as normal:
BuildingKey, CityKey, YearKey, PerilKey, Probability
1, 1, 1, 1, 3×10-6
2, 2, 1, 1, 4×10-6
But we need to remember that in the BI (reports, cubes) we can’t sum the measure up.
One thing that we need to ask ourselves is: On which dimension is it not aggregatable? For example, in the above example, is it aggregatable on year?
If a measure is not aggregatable on one dimension, it does not mean that it’s not aggregatable in all dimensions.
Data Warehousing and Data Science 
[…] a numeric is put as a measure, but if it is not aggregatable then what’s the point? It make sense to put it in a dimension as an attribute. But see grain […]
Pingback by A Measure or An Attribute « Data Warehousing and Business Intelligence — 2 March 2011 @ 7:58 am |