Time Dimension

Question

Hi I have my Data coming in like this

1. Actuals at Month Level

Year Month GL Actual

2011 Jan GL1 123

2011 Feb GL2 432

2011 Mar GL3 445

2012 Jan GL1 256

2. Budget at Year Level

Year GL Budget

2011 GL1 1000

2011 GL2 2000

2012 GL1 2000

Now should i have a single Time Dimension if yes what keys do i use to Map the Time dimension for Year to Budget Table  and another for Year Month to Actual Table.

Thanks,

Gautam

Answer 1

I like the idea of having a single, and simple, time dimension that should be at the lowest grain you would need.  Presumably down to the day level.  For data that comes in yearly, assign it an arbitrary day (like January 1 or December 31).  For data that comes in monthly, provide the data an arbitrary date like the first of the month.  This will provide you the ability to maximize the use of the time dimension down the line (in SSAS, etc). 

Answer 2

you need to create the one date dimension which have the lowest granularity at day level and point out the year to 01 jan of every year for year and the same for month every start of month with particular year.

this type of dimensions are called role playing dimension. because in your scenario its playing multiple role one for year and one for year+month. you have to join multiple time with fat table as showing in the following snapshot.

thanks,

Zaim Raza.