Social Icons

Thursday, August 15, 2013

NONEMPTYMEMBER in MDX Formula to the Rescue - Essbase ASO

Most of the Essbase ASO Applications have View dimension for handling Dynamic Time Series (Mostly Q-T-D and Y-T-D). As you all know that ASO application does not have an in-built functionality to handle Dynamic Time Series.

We have a DTS Dimension with 3 members. MTD,QTD and YTD. 
  • All the data is loaded at MTD member
  • QTD and YTD have MDX formulas to calculate the Qurater-To-Date and Year-To-Date as we have both Balance Sheet Accounts and P&L Accounts
I was working on a report which is pretty simple but the combination of the data that has to be displayed is varied. Below is the layout of the report
  • ROWS - Legal Entities (around 600+), Analysis Codes (1200+) and 19 BS Accounts and 25 P&L Accounts
  • COLUMNS - Current Month, Last Month of Prior Quarter, Variance, Variance %
This is a Y-T-D report and when i am trying to retrieve the report it is taking very long time. I didn't knew what was the issue.Tried couple of things
  1. Changed from YTD to MTD and the report was opening in less that 2 mins. But this will not help as we have to look at YTD balances for P&L
  2. Ran an MDX query with Query Tracking ON and run the design aggregation. Didn't help much as most of the hierarchies in the report were alternate hierarchies
  3. Looked at the formula for QTD and YTD. Formula looks good. I didn't see a reason why the formula might not work
Something suddenly struck my brain. Let me see how many combinations I would make. It turned out to be 31,680,000 (600 x 1200 x 44). That's pretty huge cardinality. But I have suppression enabled on the report. But imagine when i use YTD, it will have to calculate approx 31 Million combinations x 2 (current Month, Prior Month) and that is pretty huge.

i have suppressed on Report now how can I do the same in Formula and then I thought of using NONEMPTYMEMBER which has come to the Rescue.

I used NONEMPTYMEMBER [MTD] as the first line of the formula and now the report opens in around 2 mins.  Such a huge difference.

Excert from Oracle EPM Documentation (Latest

 The NONEMPTYMEMBER and NONEMPTYTUPLE properties enable MDX in Essbase to query on large sets of members or tuples while skipping formula execution on non-contributing values that contain only #MISSING data.

You can find more information at the link HERE 

Note: NONEMPTYMEMBER has nothing to do with the report. It is an ASO MDX functionality which eliminates any Missing data during calculation and calculates only those combination where data exist.

Happy Learning!!!

No comments:

Post a Comment