First, I've read several threads on time dimensions here, but as yet not one dealing with this particular topic. If I've missed one, more than happy to review it.
We track open/closed periods with a simple 1/0 flag where 1 = open. For the sake of ease in reconciling and to facilitate certain functionality within our model, we load actuals for various aspects of the forecasting applications into staging cubes. Here are some questions:
- In the rule, use an IF to STET the cell if the period is closed and load via TI?
- If retrieving actuals from the staging cubes instead of using a TI, use IF statements on all rules ( IF period is Forecast, forecast calc, actual calc )?
- As we only deal with 13 periods at a time, use aliases (Actuals1, Actuals2, ..., Forecast11, Forecast12) then the rules would have on the LHS (Actuals1...Actuals12) for Actual rules and (Forecast1...Forecast12) for forecast rules. The aliases change as periods are closed. Without going into detail, the problem here is it possible for two different live versions to have different open/closed periods.
- use the alias method to STET the Actuals periods and load via TI (has same problem as previous)?
- Use separate Actuals and Forecast measures. Actual calculations are multiplied by a time dim ActualsFlag value of 1 and Forecast are multiplied by a similar flag. Each are consolidated into a single element so only Actuals or Forecast are represented for each period appropriately?
- Suggestions?