Budget/Forecast Versioning and Data

[tomok]

We’ve got a large reporting cube where we keep Actuals (from Oracle), budget and forecasts. We have multiple versions of the forecast, that must be kept, that are created quarterly and sometimes monthly. The configuration for these forecasts is such that for months that have Actual, Forecast equals Actual. In order to maintain this setup there are typically two options, 1) load Actual into each of the forecast versions for the appropriate months or 2) have a rule that forces Forecast to equal Actual for those months. Option 1 does not require any rules but you are duplicating a lot of data. Option 2 (which we currently do) is easy but it requires a bunch of feeders.

I am looking for a way to reduce the memory footprint. Option 1 is a killer, I don’t want to do that. Option 2 requires a bunch of feeders, which is not optimal. I was considering doing this with a combination of consolidations and rules. Here’s the scenario: Each of these forecast versions is a consolidation in the Version dimension, containing the Actual element and Forecast data (where the only months populated are future months). This works fine when first created but it stops working once you start getting Actuals loaded for any of the future months. My idea here is to create elements in the Version dimension that look like this:

C 1st Quarter Forecast
Actuals (weight of 1)
1st Quarter Forecast Inputs (weight of 1)
Actuals – April (weight of -1)
Actuals - May (weight of -1)
……
Actuals – December (weight of -1)

C 2nd Quarter Forecast
Actuals (weight of 1)
2nd Quarter Forecast Inputs (weight of 1)
Actuals – July (weight of -1)
Actuals – August (weight of -1)
……
Actuals – December (weight of -1)

Actuals – April thru Actuals – December would be populated via a rule, but here is the kicker: I don’t need to feed it since I could care less about zero suppression. Can anyone think of a reason I should not do this? Or, does anyone have a different design they use to minimize data duplication?

[Ajay]

Not sure whether this is helpful, but in a previous place I worked at, I was able to convince Finance that having the granular detail, ie by account code etc, was not worth it when forecasting, since the Actuals were simply a start point for a forecast and nothing more. Let me explain.

So using a very simple example, say I have 50 account codes that consolidate into a line on the Income Statement, called “Turnover”. Now for the Actuals, it is reasonable to expect that most, if not all 50 account codes, might be populated in Oracle, and it’s likely you’ll be bringing them into you cube, and loading them against a version called “Actuals”.

For the forecasting of all Forecast “outmonths”, ie the months which aren’t the Actuals, you would have an account set up, which might be along the lines of “Planning Account – Turnover”. This could be added in Oracle, or you add it into your consolidation called “Turnover” in your TM1 dimension. This is the line that users would plant their forecasted “outmonth” values against.

But, you could use that same line to populate the Forecast “inmonths” within the Forecast version, ie the Actuals. So in the example above, the total of the 50 accounts would, via a quick TI, simply populate in the “Forecast” version, one number rather than 50 for the “inmonths”, or Actual months of the forecast, and still give you a full year Forecast number built on the Actuals.

If you have multiple feeds from Oracle daily, you would be able to tie this into the process too, ensuring that as new data comes in from Oracle, you’d update the summary actuals within the “Planning Accounts” of the Forecast.

Of course this may not work given the constraints of the design of your current cube, and of course relies on whether your users can be convinced to ditch the detail within the Actual “inmonths” of the Forecast !!

Let us know how you get on

Ajay

[Wim Gielis]

Ajay,

Basically what you are saying is to reduce the level of detail. By summing up accounts, in all months of a forecast, you definitely get to less detail and less memory. Certainly with TI.

Tom, I haven’t found a better way to approach this situation. Certain cubes will lend itself better to a rules-based approach, whereas others should be done with TI. In any case I tend to make a difference between actuals cubes (more detail) and cubes for forecasting (receiving actuals but these will not change anymore it with TI, otherwise rules & feeders and they are dynamic). Deviations of this approach are possible wherever needed.