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

Change-Up

It's been over 3 years since I last posted on this blog. It's not because I haven't wanted to post, it's because there really haven't been many (any?) significant changes in the Essbase world worth blogging about. Is it because Essbase is dead? I check the Google search trends for essbase and see this:

It is a little troubling to see this as one who has been developing Essbase applications since 1998 -- a full six years before this chart even begins. But it isn't that Essbase is dead, far from it. Essbase is just behind the scenes now. It's the engine behind some of the most powerful and successful cloud applications on the planet. It's time for me to embrace that fact and learn all there is to know about Oracle EPM Cloud.

My goal is to now post regularly about this "New Essbase". I work for Applied OLAP so expect Dodeca and Drillbridge content to be sprinkled in as well. See you next time.

Essbase Shadow Cubes Introduction

The idea for Essbase shadow cubes has probably been around as long as Essbase has existed in a production environment. Essbase is an OLAP tool -- two of the letters stand for "On Line" meaning it's probably pretty important. When you apply changes to an Essbase outline, that cube is no longer on line. Kind of a downer for an OLAP tool. So people invented clever ways of making copies of a cube, updating the cube and then redirecting users to the updated cube. All the while trying to minimize what could be an hours long process. Shadow Cubes minimize the effort involved. 

Here's an overview of the shadow cube process.

1. Create a shadow cube.

This is currently only done using the REST API. If we could do this with, say, MaxL then this would probably be a single post. Instead I'm going to delve into the REST API as a part of this, hopefully not too long, series.

2. Make whatever changes you want to the shadow cube.

Since you're not touching the cube users are connected to you can do whatever you want here. You can go into the Jet UI (or EAS Lite) and make changes. You can load data, run calculations, update outlines. There are a few exceptions but you can pretty much do whatever you want here and use whatever tool you want, not just the REST API.

3. Promote the shadow cube.

Also currently only done using the REST API. If your changes don't go so well then another option here is to discard the shadow cube. So in one regard Oracle made this really easy to do. In another regard they made it nearly impossible for someone who knows nothing about the REST API. So consider this an opportunity to learn a new skill. It's not that hard, trust me.

Some important notes:

• Users can query data while the shadow cube is there. They cannot load data or run calculations.

• There is a brief outage during the time the shadow cube is promoted. How long is that? I would wager it depends on your infrastructure and the size of the cube. More experimentation is needed. In my tests using shadow cubes in Smart View, the user doesn't lose their connection during the promotion process. So it's possible many users will be unaware that the promotion has taken place. You might need to tell them in some ingenious way. Hopefully you put a timestamp somewhere in your cube. If you don't, you probably should.

• This is only available in Essbase 21c. It doesn't appear to be in the 19c documentation but you're probably not on that version anyway. It's definitely not available in 11.1.2.4. (You should start planning to upgrade to Essbase 21c if you haven't already.)

• Don't make infrastructure changes to the shadow cube. Don't do things like change names of applications or databases. Don't make partition changes. Don't make security changes. Here's what the official documentation says: During a promotion, all security layer associations on the destination application, such as users, groups, and security filters, are retained, while that of shadow/source are lost. The same rule applies for partition definitions.

My next post will be an introduction to the REST API for Essbase.

New Essbase On-Prem Version Is Now Available!

For the first time in almost six years, there's a new on-prem release of Essbase available. I'm calling it on-prem but Essbase 21c is the same version whether you run it in Oracle's cloud, somebody else's cloud or your own Linux box. (Click here to see which distribution versions are supported) This is an exciting day! I've already had a chance to kick the tires and I'm really excited about the performance improvements I've been seeing.

As always, Applied OLAP is ready for Dodeca to attach to the latest versions of Essbase. We're happy to set up a demo of Dodeca and Essbase 21c for those who are curious -- just shoot us a message.

Release notes are available here and the installation file is available on Oracle's eDelivery website.

Enjoy!