Data Warehousing and Business Intelligence

1 March 2011

Non Aggregatable Measures

Filed under: Data Warehousing — Vincent Rainardi @ 10:46 pm
Tags:

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.

1 Comment »

  1. […] 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: