Granularity of Fact table

Hi All,

I am little bit confuse while putting the metric column in fact table ..
let say ..I've following metrics with hierarchy of Location --> District---> region and Week---> Month-->YTD

Report Layout

Metric 1 This Year Value Last Year Value %Value Point based on %
Metric 2 Result Value Grade based on Result
Metric 3 TY LY BSP Points based on %
......................................................
.....................................................

I've planned to design the table as flat ..

Metric 1 _ This Year Value
Metric 1 _ Last Year Value
Metric 1 _ %Value
Metric 1 _ Point based on %
Metric 2 _ Result Value
Metric 2_ Grade based on Result
Metric 3_TY
Metric 3_LY
Metric 3_BSP
Metric 3_Points
..................
.......................

Pls suggest your thoughts ..

Usually, last year, last month, etc... values are obtained by a second query to the fact table using the date dimension to locate the values. But if you only need last year and that value is static it probably doesn't hurt to store in the current year row. It makes fact loading a little bit more complex, but will speed queries.

Percentages, however, are usually calculated on the fly rather than stored in the fact, as they tend to be non-additive measures.