Essbase: Flipping Sign on Data Load

I recently encountered a situation similar to that described here.

In essence, I needed to move a bunch of Actuals data into a Planning cube – and some of that data included “Redemptions”  which are stored in our Actuals cube as negative numbers (where they are added) but are stored in Planning as positive numbers (where they are subtracted).  In other words, I needed to selectively perform a sign flip either on the data export or on the data load.

The official answer is to use UDAs to execute a sign flip on data load – but as it turns out, it’s much easier to use “Scale“.

So here’s what I wound up doing:

1) Create a report script to export all required data along indicated dimensions except for the redemption data.

2) Create a report script to export only the redemption data.

3) Export both data sets to separate files.

4) Create a load rule to import the first (redemptionless) data set.

5) Create a load rule to import the redemption data set, setting the scale to -1.0, effectively multiplying the values by negative one.

6) Load the first data set.

7) Load the redemption data.

8) Execute any required business rules and/or calc scripts to agg or calc as necessary.

9) Validate.

I hope that helps!