Filter Native Essbase ASO Exports

Essbase 21.6 was released in August. One of the enhancements was improved performance of ASO MDX Exports. In order to use this capability, add the DATAFORMATEXPORT directive to your Export statement. 

It's worth noting that this only works for exporting stored data: only Level 0 members and no calculated or formula members. This enhancement removes the limitation of 2³² cells that was previously an issue for ASO MDX exports. If you previously had to break up MDX exports to get around this limit, this should fix that. Apparently it also adds support for duplicate member outlines. 

The example the documentation gives is super interesting because it includes a property, STORED_FLAG, that I don't recall ever seeing before. This property allows you to filter on stored members. The property isn't listed in the documented MDX Properties. I tried using it in some older Essbase versions and it did work so it's probably been available for a while.

Below is the example given in the documentation:

1:  EXPORT INTO FILE "MDXExport_DataExport1.txt" USING DATAEXPFORMAT  
2:  SELECT Filter([Measures].members, [Measures].currentmember.STORED_FLAG)  
3:  ON COLUMNS,   
4:  NON EMPTY(  
5:   Crossjoin(  
6:   Crossjoin(  
7:    Crossjoin(  
8:    Crossjoin(  
9:     Crossjoin(  
10:     Crossjoin(  
11:      Crossjoin(  
12:      Crossjoin(  
13:       Crossjoin(  
14:       Filter([Years].members, [Years].currentmember.STORED_FLAG),  
15:       Filter([Time].members, [Time].currentmember.STORED_FLAG)),  
16:      Filter([Transaction Type].members, [Transaction Type].currentmember.STORED_FLAG)),  
17:      Filter([Payment Type].members, [Payment Type].currentmember.STORED_FLAG)),  
18:     Filter([Promotions].members, [Promotions].currentmember.STORED_FLAG)),  
19:     Filter([Age].members, [Age].currentmember.STORED_FLAG)),  
20:    Filter([Income Level].members, [Income Level].currentmember.STORED_FLAG)),  
21:    Filter([Products].members, [Products].currentmember.STORED_FLAG)),  
22:   Filter([Stores].members, [Stores].currentmember.STORED_FLAG)),  
23:   Filter([Geography].members, [Geography].currentmember.STORED_FLAG))  
24:  )  
25:  ON ROWS FROM ASOSamp.Basic;  

Line 1: This begins the export command, specifies the output file and adds the new DATAEXPORTFORMAT directive.

Line 2: This begins the Select statement and specifies the column members which include all the stored Measures.

Line 4: Adds the NON EMPTY keyword. I believe this to be unnecessary. According to the documentation this will be assumed.

Lines 5-13: A disgusting list of nested Crossjoin functions. Oracle really needs to fix this. Microsoft MDX allows the joining of more than 2 sets in a single Crossjoin function call. This is an 11 dimension cube. One dimension will be in the columns and 10 on the rows. That means we need 9 Crossjoins. Gross. 

Lines 14-23: A filter statement on each line that returns only the stored members from the remaining dimensions.

Line 25: Ends the Export statement and specifies the cube from which we are exporting.

Here's a snippet of what you get back:

 "Original Price" "Price Paid" "Units" "Transactions" "Returns"  
 "Curr Year" "Jan" "Sale" "Cash" "No Promotion" "1 to 13 Years" "Under 20,000" "Camcorders" "017589" "14036" 656.5 656.5 1 2  
 "13681" 1443 1443 2 1  
 "Photo Printers" "14010" 232 232 1 2  
 "14027" 238 238 1 1  
 "13428" 214 214 1 2  
 "13681" 206 206 1 2 206  

If it looks oddly familiar, it is. This is the native Essbase export format. This new feature is the ability to filter native Essbase exports from ASO. If you want to only export a single year, or a handful of scenarios, you can do that.

It's great that we can do this now but what would really take this to the next level would be to export this ASO data in column format. I saw that on the roadmap at Kscope24 so hopefully we'll get that soon.

Complex PBCS ASO Procedural Calcs

The notion that one cannot write complex procedural calculations in ASO was disproved about 10 years ago. I spoke on the topic for the first time at Kscope15. Now that I'm getting more involved in PBCS, I wanted to see if my old tricks worked.

I'm going to attempt to use an MDX function in an ASO Custom Calc script. According to the documentation, this shouldn't work. Probably some of you have resorted to creating formula members that handle these functions and then call those members in your calculations. Let's see if we can get around that because, frankly, it's a pain. The less outline maintenance, the better.

My test case will be very simple. I'm going to copy data from account 4120 to 4130 but the twist here is that I'll bring in the prior month of data.

This task would be trivial in a BSO Calc.

First let's start off in Calc Manager with a calculation that copies the data for the current month.

1:  CustomCalcParameters parameters0 = new CustomCalcParameters()  
2:  parameters0.Pov = "Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({[FY24]},{Descendants([Channel], [Channel].dimension.Levels(0))}),{Descendants([Entity], [Entity].dimension.Levels(0))}),{[Dec]}),{Descendants([Product], [Product].dimension.Levels(0))}),{Descendants([Scenario], [Scenario].dimension.Levels(0))}),{Descendants([Version], [Version].dimension.Levels(0))})"  
3:  parameters0.target = ""  
4:  parameters0.creditMember = ""  
5:  parameters0.debitMember = ""  
6:  parameters0.script = "([4130]) := ([4120]);"  
7:  parameters0.offset = ""  
8:  parameters0.sourceRegion = "{([4120])}"  
9:  operation.getApplication().getCube('Vis1ASO').executeAsoCustomCalculation(parameters0)  

For those creating one of these scripts the first time I'll give an overview of what the lines are doing. I'm going to leave out the lines with blank arguments.

Line 2 is the POV. Think of this like your BSO fix statement.

Line 6 is the script or the formula that you want to execute. In this case we're setting Account 4130 equal to 4120.

Line 8 is the source region. If it's not in the fix statement and we need to reference the data, we need to add it in here.

Lines 1 and 9 are groovy specific for executing the calc.

This particular calculation will result in the following:

But we need to get last month's data. Let's try putting an MDX function in Line 6.

 parameters0.script = "([4130]) := ([Period].CurrentMember.PrevMember,[4120]);"  

This change passes validation but when I execute it, I get an error. This tells me that the validation (probably) only cares about the groovy syntax here.

Is there any way around this? I'm going to dig into my bag of tricks and pull out one I've been using for many years with Essbase ASO cubes. Celvin Kattookaran taught me this, I don't know how he found out that it works, I don't even want to know. Anyway, if I put an IIF function on the right side of the formula, somehow it opens up the capability to use any and all MDX functions. Let's see if we can get that to work here.

 parameters0.script = "([4130]) := IIF(1=1,([Period].CurrentMember.PrevMember,[4120]),Missing);"  

My new line includes an IIF function that is set to 1=1 which will always be true. I'm including the Period in my tuple and using the Previous Member function on it.

When I execute the calculation, I get a different error.

This error indicates that our source region doesn't have that previous month in it. This tells me that I'm on the right track and the Essbase engine has begun using the MDX function. The fix for this is relatively simple. I need to add that period into my SourceRegion on line 8. I can either add November into the SourceRegion tuple or add all the months, assuming I'm going to run this for more than just December at some point.

 parameters0.sourceRegion = "Crossjoin({[4120]},{Descendants([Period], [Period].dimension.Levels(0))})"  

Once I changed that, the script ran perfectly.

I suppose a word of caution is in order here. While I've been using this method reliably for the better part of a decade, I'm not sure it's supported. I'm nearly certain it's not officially documented anywhere. But this might just get you out of a jam and maybe you can ditch BSO/Hybrid for good. 😀

Kscope24

 

I returned from Kscope24 in Nashville last night and wanted to shared a few thoughts about the conference.

Sunday Symposiums

The conference before the conference is a great way to get a feel for the trends in the EPM space. There was much talk about AI -- more on that later. The Essbase Symposium covered Federated Partitions and the Redwood interface in Essbase 21.6. If you're a customer with a large (slow) Essbase cube that needs real-time data loading, Oracle wants to work with you to test it out with Federated Partitions. Shoot me an email and I can put you in contact with the right people. Their goal is to get 10 referenceable customers within the next year.

Sessions

The technical sessions are the heart of Kscope. I'm really just coming up to speed on the EPM Cloud side of things so those were valuable to me. I attended a great session by Harsh Dave on the Enterprise Profitability and Cost Management tool. In it I realized that I basically built the homegrown version of this 10 years ago at Sears Holdings. It also cemented my belief that ASO is superior to BSO in just about every way possible. I can't believe how many times people mentioned block creation issues -- the same thing people were talking about 25 years ago when I started with Essbase. If you're serious about Essbase performance you should at least investigate ASO.

Dodeca Customer Panel

On Tuesday we had standing room only at the Dodeca Customer Panel. In it Kroger, LexisNexis, ADT and Principal talked about how they're leveraging Dodeca to run their business. All of them are using our new EPM Cloud connector to get the most of their EPM Cloud investment.

Groovy

There were 17 sessions with Groovy either in the title or the description. I get it, you need it to do your job due to limitations in EPM Cloud but it scares the heck out of me. Let me be clear, the syntax doesn't scare me (but avoid throwing 40 lines of code in a presentation slide, please). The fact that so many companies are writing hundreds, if not thousands of lines of custom Groovy code worries me tremendously. I'm afraid Groovy is the new VBA. The backlash of managers who got burned by having to deal with unmaintainable VBA is still going on to this day.

AI

There was much discussion of AI. It's the buzzword of the day. Very little of it impacts me... yet. I'm sure it will. Similar to the ubiquitous mentions of THE CLOUD a decade ago. Eventually it took hold and I'm sure AI will too. Most of what I saw isn't ready for the real world. 

Wednesday Night Event

We had a great time partying on a the General Jackson Showboat and Broadway. Kscope is a bit like drinking technical content from a firehose and the Wednesday night event is a great way to unwind. I probably ate and drank too much. Oh well...

GO!

If you were there this year, thank-you for helping making Kscope the best tech conference. If you weren't, start figuring out how you can be there next year. Start thinking of ideas for presentations. I'm available as a co-presenter if you need help. Do whatever you need to do to make sure you're in Grapevine for Kscope25.

Shared Member Problems in BSO Calcs

Shared members are an amazing feature of Essbase but they seem to always present challenges and many of those are not easy to spot.

A question arose on the Planning Cloud Customer Connect recently where the issue the poster was having was related to a shared member/alternate hierarchy. For some reason, it's never immediately apparent that this is the problem. Several people suggested a number of reasons before it dawned on me that a shared member was the culprit.

In a nutshell, they were requesting the parent of a shared member, expecting to get back the shared member's parent. However, they were getting back the protype's parent.

Suppose the following hierarchy:

If you had a calc script that was iterating through the level 0 members under Diet, and you requested the parent of each, you wouldn't get back the member name Diet. For 100-20 you'd get back 100, for 200-20 you'd get back 200 and for 300-30 you'd get back 300.

This problem shows up when you have a formula with something like the following:

@PARENT("Products")

Note: The @PARENT calculation function for Essbase returns the parent of the current member being calculated in the specified dimension.

The workaround the poster settled on was to add a new alias table and look up based upon that using the @ALIAS() function. It would be nice if there was a function, maybe @SPARENT(), that would return the parent of a shared member.

Update: I'm told that @SPARENT() does exist as an undocumented function. I tested the function and it returns the parent of the first instance of a shared member. If you need the second or greater instance, it seems, you're out of luck. Use at your own risk.

Kscope24 Preview

In just over two weeks I'll be flying to Nashville for the best tech conference ever! I'm looking forward to catching up with friends and colleagues from around the world. The Denim & Diamonds event on Wednesday night looks like it will be a blast.

The conference will kick off early on Sunday morning with the Essbase Symposium. I'm hopeful that there will be some cool innovations as the Essbase space has been stagnant for a very long time now.

Following the Essbase session will be the Dodeca Symposium. Tim Tow will be sharing our roadmap and his vision for the future of the product. We've been heavily investing in our EPM Cloud Connector and will be showing that off. If you use EPM Cloud, you'll want to stop by and see what we've been up to.

I've been reviewing the session agenda and picked out three sessions that I'm most looking forward to attending. All three are fantastic speakers and respected experts in their field.

Monday July 15, 4:00 pm - 5:00 pm
Joe Aultman -- Converting an Excel Model into Planning -- A New Approach

Tuesday July 16, 4:00 pm - 5:00 pm
Peter Nitschke -- Form Fundamentals: From Zero to Planning Hero

Wednesday July 17, 1:30 pm - 2:30 pm
Jake Turrell -- From Manual to Magical: Transforming Oracle EPM with PowerShell Automation

Be sure to stop by the Applied OLAP booth to say hello. I look forward to meeting many new people this year. Safe travels...

EPM Cloud Planning Smart Lists

The Backstory:

I never had much use for Smart Lists in Essbase. I remember people asking for the functionality since I started working with Essbase in 1998. Here's a post from the Essbase Network 54 Board in 2000 essentially asking for this capability.

They finally got around to adding "Typed Measures" to Essbase somewhere around 2007. Here's a link to Tim Tow blogging about them in 2008.

A Basic Primer on Smart Lists:

Essbase stores numerical data, specifically 8-byte floating point numbers. Essbase is what Planning/PBCS/EPM Cloud Cubes use to store data. As a work-around to store some text, you can create a list of text values, assign them some numerical value and store that in the database. The application layer then interprets those values as text using the Smart List.

Some Use Cases:

Displaying some status for an Account/Entity/Product/etc.

Displaying the hire month for an employee.

You want an indicator for a member but don't want to create an Attribute dimension.

You want to show text in the numeric area of a report.

How to Create a Smart List in EPM Cloud Planning:

Step 1: Create a Smart List

Find the Create and Manage menu and select Smart Lists.

Press the + sign to add a new Smart List.

Now name your Smart List and decide on options. In this case the Label will be Status. Any missing values will show as Active by default.

Click on the Entries tab and edit your list of values. The Name needs to be alphanumeric and contain no spaces but the Label can contain spaces. Once this is complete, save the Smart List.

Step 2: Update The Outline

Navigate to the Create and Manage menu and select Dimensions.

Add a new member in a dimension and change its Data Type to SmartList. Then set the Smart List value. In this case I added a member in the Account dimension called Entity Status and assigned it the Status Smart List.

Before leaving the Dimensions editor, click on the Evaluation Order tab. This setting tells the application how to resolve conflicts if you have Smart Lists in multiple dimensions. You will need to select the dimension in which your Smart List resides and Save this setting. 

Once the dimension change is saved, be sure to refresh your database.

Step 3: Test Your Change in Smart View

Now you can go into Smart View and retrieve some data to see the Smart List working.

When you click on a cell, you'll get a drop-down box allowing you to change the cell's value. You can write this back to the database.

Good luck using Smart Lists! Let me know the creative ways you're using this feature.

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.