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.
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.