Connection to time dimension with different grains

[Olekova]

Hello,

I have two fact tables with a different time granularity.

First fact table has the grain month, second has the grain quarterly. Should they use the same time dimension, or should I have a time dimension for each granularity?

If I have a time dimension like this

key year quarter month
1 2010 q1-2010 january
2 2010 q1-2010 february
3 2010 q1-2010 march

If always should this as the time dimension for every granularity, how would you connect a fact table with the grain quarterly? Would you always use the first key in the quarter(here 1) or the last key (3)?

Thanks a lot for your help!!

Olekova

 

[blom0344]

If the reporting end uses sophisticated BI tools like BO or Cognos and multi-fact querying is an option then 1 dimension would suffice (as these tools can handle multi-grain) Otherwise you can define a time table at the lowest grain and define views against it to suit higher levels..

Ties Blom

 

[Olekova]

Tanks for tue answer. What i do not understand is how i should generate the foreign key in a fact table with quarterly grain. Could u help me there?
Thanks!

Olekova

 

[blom0344]

A key should preferably be an integer, so in my ETL I would build something like (example in T-SQL) (prior to aggregatestep to quarter-level):

case
when month(somedate) between 1 and 3
then year(somedate)*100+1
when month(somedate) between 4 and 6
then year(somedate)*100+2
when month(somedate) between 7 and 9
then year(somedate)*100+3
when month(somedate) between 10 and 12
then year(somedate)*100+4
else 0
end

for 2011 this yields:

201101 (first quarter)
201102 (2nd quarter)
201103 (3rd quarter)
201104 (4th quarter)


Ties Blom