posted 12/31/2009 by CurrentMember - Views: [2882]
One of the powerful features of SSAS 2005 (and above) is the capability to have one cube with multiple measure groups at different grains. For example, say we have sales facts, loaded from an order system, that have a grain of day and forecast facts, from a 3rd-party forecasting tool, that have a grain of month. Forecasts typically become less and less accurate as we lower the grain. Annual forecast may be a best guess. A day level forecast may be challenging to build and highly suspect if we could. So we might settle and say a Month level forecast is the best we can do.
In this scenario we can do many comparisons of these facts. We can do year level comparisons (i.e Annual Sales vs Forecasted Annual Sales). We can also do Monthly Sales vs Monthly Forecasted Sales. What about day? Forecast does not exist at the day level.
The default behavior of SSAS is that if a measure is viewed at a level lower than its grain, then the grain level value will be repeated for lower levels. To demonstrate this, let’s look at the example below. [Widget Sales] is valid at the day level and above. [Widget Sales Forecast] on the other hand is valid at the month level and above.
Date Hierarchy Widget Sales Widget Sales Forecast
FY 2009 115 1000
Jan 09 100 90
Feb 09 15 110
Feb 1, 2009 5 110
Feb 2, 2009 10 110
Notice that the [Widget Sales Forecast] value for Feb 09 is 110 and it is repeated at the day level for Feb 1st and Feb 2nd. Is 110 the forecast for Feb 1st? Obviously, the answer is no. It is the forecast for Feb 09.
This demonstrates that if we allow simultaneous drilldown of measures with different grains, we get what, some would consider, unexpected results at levels below the level of the higher grained fact; forecast in this case.
How do we deal with this? If “Sales vs Forecast” is presented in a SSRS report, then we could hide both measures at the day level. For ad-hoc client tools, like Excel, our options are to my knowledge 1) educate users that forecast does not make sense at the day level or 2) create an allocation calculation that allocates the month forecast over days of the month or 3) overwrite the forecast with NULL at the day level so that it is not displayed.
I did not like option 1. Option 2 creates data that is likely not accurate. As mentioned before, it would be a worse guess that is derived from what is already just a best guess. So I opted for option 3. Here is how I did it:
// Overwrite forecast with null at the intersection of forecast and
// Date hierarchy levels lower than [Month]. [Month] is the grain
// of forecast. This effectively keeps the forecast fact from being
// viewed at ANY level lower than [Month]
Scope (
[Measures].[Widget Sales Forecast]
, Descendants([Date].[Year-Month-Day].CurrentMember
,[Date].[Year-Month-Day].[Month],AFTER)
);
This = NULL;
End Scope;
Our first example now looks like this when viewed in an ad-hoc client tool:
Date Hierarchy Widget Sales Widget Sales ForecastFY 2009 115 1000 Jan 09 100 90 Feb 09 15 110 Feb 1, 2009 5 Feb 2, 2009 10
Feb 1, 2009 5
Feb 2, 2009 10
I hope this helps someone else dealing with this. I’m interested in how other folks have approached this issue and any new issues that may pop up by using this technique.
Luke