Thursday, June 13, 2024

EPM Cloud ASO Plan Type Procedural Calcs

When learning EPM Cloud Planning, I went through a class that taught me you cannot perform ASO procedural calculations. I didn't like to hear I had to give up something so vital to Essbase ASO. While it's kind of true, I'll show you a work-around.

For an in-depth explanation of the problem, see this really old blog post HERE. In short, summing formula members across other dimensions is really, really difficult. The work-around is to run a procedural calc at level-0 thus storing the data. Then you let ASO do it's aggregation magic.

It's surprisingly easy in EPM Cloud Planning ASO Plan Types.

Step 1: Put the formula into a member.

If you're creating a bunch of calcs, I'd recommend putting these members in a hidden section of the hierarchy. For this example, I created a member in my Account dimension with two members under it.


On the ProcTestSource I put a very simple member formula of the number 9999. I'll get into more complex formulas in future posts.


Step 2: Create a business rule to run an allocation on the ASO cube.


For the formula, I put the Target on the left side and the Source on the right.



Now I run the business rule and retrieve my data in Smart View (or Dodeca). The column on the left is a formula that will evaluate to 9999 at every intersection. The column on the right has the stored value at level 0 and lets ASO handle the aggregations.


If you've got a better way of doing this, please reach out to me. I'm curious to see if there are other ways to accomplish this (other than doing the calculation in a BSO cube and moving the data over).

1 comment:

Pete said...

Hey,

So this works as a really good solution (I call it the resetting the solve order to zero).

Some notes:
- Use NONEMPTYMEMBER or NONEMPTYTUPLE against the calculated member, massively increases performance
- for your POV, at a certain scale (mostly because that POV because a big single crossjoin) it will get substantially slower. Using groovy to iterate through the hierarchy and split the rule up (preferably running it asynchronously via the rest api) is substantially faster.
- the groovy grid export \ import function also works well at smaller scales for the assigning of data.

Pete