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 would say try to stick with a single Date dimension where your month members keys are both the Year and the Month (201101, 201102, etc) and your year level's keys are, well, years.

Then in SSAS link your Actual MeasureGroup to the month level and your Budget MeasureGroup to the Year level.  What SSAS will do is repeat the Budget values for every month of the year. You can then scope those values out if you wish so that at the month level, users only see Actual values, or you could leave the budget values in the months and do some pretty usefull ratio calculations for your users.