Granularity of Fact table

View previous topic View next topic Go down

Granularity of Fact table

Post  dwh.arvind on Thu Oct 25, 2012 12:37 am

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

dwh.arvind

Posts : 4
Join date : 2012-10-25

View user profile

Back to top Go down

Re: Granularity of Fact table

Post  ngalemmo on Thu Oct 25, 2012 5:42 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum