Update: Using Filter() in ASO Allocations and Procedural Calcs

I frequently use the Filter() command in my ASO Allocation and Procedural Calc POVs. It's a very powerful and useful tool. Unfortunately it stopped working at some point -- probably in 11.1.2.3.x. Well, I'm very happy to announce that I've tested the command in 11.1.2.4.006 and it is working again.

As a quick refresher, here are couple of reasons you might want to use Filter() in an ASO allocation/procedural calculation POV. Both of these issues can cause your calculation to fail and resort to painful workarounds.

You have shared members in a hierarchy that originally were at an upper level.

This code will give you a set that consists of all Level 0 Markets that excludes any shared members.
Filter(Descendants([Market],Levels([Market],0)), Not Market.CurrentMember.Shared_Flag)

You have some members in a hierarchy that have formulas.

This code will give you a set that consists of all Level 0 Measures that excludes any dynamic members:

Filter([Measures].Levels(0).Members, [Measures].CurrentMember.Member_Type <> 2)

Thanks for the prompt fix, Oracle!


NOTE: Until further notice and unless specified otherwise, all future posts will pertain to tests performed on Essbase 11.1.2.4.006. If there's something you like me to test out on this release, please leave a comment below.

ASO Procedural Calcs -- Handling Shared Members

ASO allocations and procedural calculations do not allow you to include any dynamic members in your POV. If you include a shared member you get the following warning:

WARNING - 1300005 - The argument [POV] ignores the duplicate member [100-20].

If the prototype of the shared member is not level 0, it will fail your calculation with the following error:

ERROR - 1300033 - Upper-level members, for example [East], are not allowed in argument [POV]. Select a level-0 member. 

In order to remove any shared members from your POV, you can sometimes use the Filter() function to remove them as illustrated below. I say sometimes because this works in some versions and not in others. I currently have an open SR with Oracle that they are investigating. Hopefully it will be resolved soon. What would be even better is if there were calculation options allowing you to ignore things like dynamic members.

In this example, I'm taking all level 0 members under the Market dimension. But this might include some upper level members that are shared in alternate hierarchies. One strategy would be to only take the descendants of the original hierarchy. Another would be to filter out all shared members as shown here.

 {Filter(Descendants([Market],Levels([Market],0)), NOT Market.CurrentMember.Shared_Flag)}  

ASO Allocations -- Performing a Simple Allocation

If you need to translate BSO calc scripts over to ASO procedural calcs/allocations, the Oracle documentation isn't a lot of help. There is no one for one translation table and the Execute Allocation command has so many options that it's difficult to know where to begin. It does take a while to read through and understand. If you've struggled through it before, hopefully this post will help. I'm going to take a simple BSO allocation and translate it for you, explaining things along the way.

Let's assume we have an outline that looks like the one below. This is based off of Sample.Basic, with a few minor adjustments.

Assume we had an allocation from our old Sample Basic cube which was taking last year's Marketing spend and spreading it to this year's Marking budget based upon last year's sales by market. You can see how the data looks below. In this case the $843 gets spread to the various markets in direct proportion to what the market's sales were.

The Old Way

The BSO calc script to do this could use the @ALLOCATE function as below.

1:  FIX("Marketing")  
2:     "Budget" = @ALLOCATE("PY Actual, @LEVMBRS("Market",0),"PY Actual"->"Sales",,share);  
3:  ENDFIX  

Translating to ASO

The most basic difference between a BSO Calc Script and an ASO Allocation is that the former is a file that can be executed in a number of ways: via MaxL, through Smart View, through EAS, etc. and the latter is a MaxL statement. This is the process for creating my MaxL statement.

The first thing I need to do is to start my execute statement specifying the database name.

1:  Execute Allocation Process on Database ASample.Basic with  

Next I need to define my POV. I use the CrossJoin function to define the Range the allocation will be run on. In this case all Level 0 products and all 12 months.

2:  POV "CrossJoin([Jan]:[Dec],  
3:  {Descendants([Total Products],Levels([Product],0))})"  

Now I need to set the AMOUNT I am using in the allocation. In this case it is the Marketing dollars from last year for all Markets. That can be represented using a tuple.

4:  AMOUNT "([Market],[PY Actual],[Marketing])"  

Then I will set the BASIS by which the data will be spread. In this case the PY Actuals, Sales tuple.

5:  BASIS "([PY Actual],[Sales])"  

I will define my TARGET with a tuple pulling data from the intersection of the Budget Scenario and Marketing Measure.

6:  TARGET "([Budget],[Marketing])"  

Next I will define my RANGE as a set made up of the Level 0 members under Total Markets. This is the group of members to which the data will be allocated.

7:  RANGE "{Descendants([Total Markets],Levels([Market],0))}"

Finally I will specify the Allocation method. In this case I will use SHARE. You can use SPREAD if you want to allocate the data values evenly. If you do use SPREAD then make sure to drop the BASIS (simply remove line 5 from the statement) as it doesn't make sense to use a BASIS when allocating evenly.

8:  SHARE;  

Hopefully when you execute, you'll see the following messages telling you the elapsed time and how many cells were created.

 OK/INFO - 1300006 - Essbase generated [5] cells.
 OK/INFO - 1013374 - The elapsed time of the allocation is [0.13] seconds.
 OK/INFO - 1241188 - ASO Allocation Completed on Database ['ASample'.'Basic'].

Just a Start

There are many other options available for use in more complex allocations. This example will get you started. I'll work on some posts involving more complicated allocations in the future.

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).

ASO Procedural Calcs Part 1

I've shown before that using ASO Allocations is generally favorable to using ASO Procedural Calcs. But when are you forced into using a Procedural Calc?

Writing a BSO calc script to subtract Forecasted Cost of Goods Sold from Actual Sales is relatively simple.

• "Margin" = "Actual"->"Sales" - "Forecast"->"COGS";

But this is where we hit a snag with ASO Allocations.

Within an ASO Allocation statement we can use the Amount and AmountContext fields. We can use those to set the source of the data I'm going to allocated. The Amount field allows us to do things like this:

• Amount "([Sales] - [COGS])"

as well as some simple arithmetic. We can use the AmountContext field to fine tune Amount field. We can add something like this:

• AmountContext "([Actual])"

or like this:

• AmountContext "([Actual],[Jan])"

What we cannot do is cross dimensions within the Amount field like this:

• Amount "([Sales],[Actual] - [COGS],[Forecast])"

>>ERROR - 1200679 - The amount expression is invalid. Only arithmetic operations and members from a single dimension are allowed.

This is the point at which we need to switch over to using a Procedural Calc. Here's an example:

1:  Execute Calculation on Database Sample.Basic With  
2:  LOCAL SCRIPT FILE "simple_calc.csc"  
3:  POV "CrossJoin({[Jan]:[Dec]},  
4:  CrossJoin({[Forecast]},  
5:  CrossJoin({Descendants([Total Markets],Levels([Market],0))},  
6:  {Descendants([Total Products],Levels([Product],0))})))"  
7:  SOURCEREGION "CrossJoin({[System Margin],[Sales],[COGS]},  
8:  CrossJoin({[Jan]:[Dec]}, {[Actual],[Forecast]}))";  
9:    
10:  /* Contents of simple.calc.csc */  
11:  ([System Margin]) := (([Actual],[Sales]) - ([Forecast],[COGS]));  
12:    

If you're translating many BSO calc scripts to ASO calcs then chances are good that you'll run into one that forces you to use a Procedural Calc. Why the developers chose to implement Procedural Calcs and Allocations in such a different manner doesn't make a lot of sense to me but my hope is that they will extend the functionality in them.