Data Warehousing and Business Intelligence

17 May 2010

Storing percentage measures in the fact table

Filed under: Data Warehousing — Vincent Rainardi @ 7:44 am
Tags:

A few weeks ago somebody asked me at SQL Server Central how he should store percentages in the fact table. And how he could aggregate it up.

A percentage measure (or ratio) needs to be stored as two columns in the fact table. One stores the nomination and the other stores the denomination.  The BI application (report or cube) will then need to calculate the percentage or ratio on the fly. It is necessary to store it as two columns, in order to be able to aggregate it up.

For example, say you have a measure called Ratio1, defined as A divided by B. In the fact table you create 2 measure columns: A and B. The in the report or cube we then create a measure called Ratio1, which is defined as: sum(A) divided by sum(B). This way, it doesn’t matter at what level* this measure it used, the ratio will always be correct.

*level: I’m referring to the hierarchy in the dimension table.

This is what we store in the fact table: (without the “Total” row of course)

This is what we store in the dim1 table:

And this is what the report (or cube) displays on the screen:

Notice that 67% + 20% + 56% is not 44%. But 44% is calculated from (the sum of A) divided by (the sum of B) at that level.

So by storing the nominator and de-nominator of the percentage measure in the fact table we can aggregate correctly at any level in the report or cube.

Leave a Comment »

No comments yet.

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

Blog at WordPress.com.

%d bloggers like this: