Overwriting Data with #MISSING in ASO Calcs

The way ASO Custom Calculations and Allocations update data in an ASO database is through the loading of slices. This is something to keep in mind if you perform a lot of them – eventually you should merge the slices together if retrieval performance becomes an issue. The other thing to keep in mind is that you can’t use a Custom Calculation or Allocation to copy a #MISSING cell over a cell with data. Why is this important?

Suppose we have a database where we copy Actual data over Forecast data after each month is closed. Suppose further our company exits the Sasparilla business in Massachusetts in May so we end up having no sales going forward. In this case, if I copy May Actual data to Forecast, it won’t do what I want which is to clear out the 15 in the May Forecast cell.

1: Execute Allocation Process on Database SampA.Basic with
2: POV "{([Sales],[May],[Massachusetts],[Sasparilla])}"
3: AMOUNT "([Actual])"
4: TARGET ""
5: RANGE "{[Forecast]}"
6: SPREAD;

The work around I came up with is to clear out the POV before the Calculation/Allocation is run.

1:  Alter Database SampA.Basic clear data in region "{[May],[Forecast]}";  

So if you have a Custom Calculation or Allocation where there is a potential that you’ll need to copy #MISSING values over data, make sure you add a clear statement ahead of it.

Note: I did log a Service Request with Oracle on this subject and they acknowledged this as a bug (#19025751).