Presenting Actuals and Forecast Concurrently in a Write-Enabled Cube
I have written a series of entries on writeback applications and wanted to add this last entry highlighting a common cube design challenge associated with these. Quite often with write enabled cubes, we enter forecasted data for future periods. When those periods come to pass we bring in actual (historical) data into the data model while holding onto our forecasts for these periods. And while its easy enough to present forecasted values side-by-side with actual values, we often wish to look over longer spans of time at a single set of values some of which represent actuals and others of which represent forecasts.
To illustrate this, consider a utilization model for employees in a consultancy. In this model, we forecast the number of hours we expect each employee to work over the weeks of a year with the objective of understanding the utilization percentage those employees will obtain. (In a consultancy, maintaining individual and organizational utilization rates (percentages) are critical for ensuring profitability and avoiding employee burn-out and turn-over.)
As we look at an employee's utilization potential utilization rate for a given year, we have some weeks for which we must forecast values and other weeks for which the employee has submitted his or her actual values. The end of the year utilization rate represents a combination of forecasted and actual values.
In our cube, we typically keep forecasted and actual values separated by either employing two separate measure groups, i.e. one for actuals and another for forecast, or by recording both sets of values in a single measure group by assigning actuals to a scenario member in a Scenario dimension that is reserved just for actuals. In the sample database I've provided with this post, I've used to former approach to keep things a bit more simple.
To present actuals and forecast data together, I've modified an otherwise standard Time dimension by adding an attribute, [Is Actual], which is used to identify dates that have passed (and for which I therefore have actuals) and dates that are yet to come (and for which I must rely on forecasted values). I then created a calculated member, [Measures].[Hours], where I bring together actuals and forecasted values based on their association with the [Is Actual] attribute. Here's the calculation:
([Measures].[Actuals],[Date].[Is Actual].[True]) + ([Measures].[Forecast],[Date].[Is Actual].[False])
The result of this expression is that when I am reviewing a time period comprised solely of actuals, the first part of the expression returns a value while the second returns nothing. When I review a period that is comprised solely of forecast, the opposite is true. And when I review a period that should be represented partly by actuals and partly by forecast, each side of the equation returns the proper part. Utilization is then calculated of this "combined" measure.