Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Connection to time dimension with different grains

Status
Not open for further replies.

Olekova

Technical User
Nov 8, 2011
2
0
0
DE
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
 
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

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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top