Thursday, May 03, 2012 6:30 PM
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.
Thursday, May 03, 2012 11:23 PM
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.
- Proposed As Answer by Lola WangMicrosoft Contingent Staff Friday, May 04, 2012 8:16 AM