Skip to main content

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

 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!!!
Labels:

Comments

  1. kwDecember 23, 2022 at 5:45 AM

    Works until you have a #Missing in MTD, then it doesn't work as it resolves to #Missing for that period even though previous periods have values for MTD.

    ReplyDelete

Post a Comment

Popular posts from this blog

EPM Cloud Tips & Tricks - #1

The first EPM cloud product was released in 2014 and it's been six years till date. I was recently part of an FCCS implementation project. I know what you might be thinking. Coming from completely essbase and planning background and been working on it for almost 12 years and doing an FCCS project? Well, it turned out that way and it was a change for me too than being in my comfort zone and took it as a challenge.  I have been very busy for over the past one year and I didn't really had a chance or time to get back to my blogging and sharing my knowledge. The project finally went live and I am going to share my bit of learnings. Some of it you might alread know The first tip is going to be an easy one and those who have worked in FDMEE / Data Management in the cloud, you might already know it. But, this is very important when it comes to FCCS as zeroes are valid from Balance Sheet standpoint Data Management by default doesn't load zeroes. Below is a excerpt from the document...
1 comment
Read more

OAC - New Interface

At ODTUG Kscope18 , Oracle has revealed new features and groundbreaking updates that are going to come in the next release in OAC. Have you ever wondered what it's going to be I was so excited that I kicked-off the promotional offer and fired up my OAC instance. But, I was surprised and felt sad after looking at the instance that is in public cloud and the instance that is going to come in the next release. Not sure if I am missing anything here which will enable me to access the newer interface / newer instance The new interface looks sleek and looks a lot better than the previous interface. I am using the instance as parts of Hands-On lab at ODTUG Kscope18. Not sure till when this environment will be available. I will update this blog and make it more comparitive with what is available right now in public cloud New JET UI. I don't know if that is the right way to call it but that's what it says when you fire up the OAC instance new interface Less clutter and wa...
Post a Comment
Read more

OAC - Switch Classic to Modern - Trick

Back to back posts today. But, I am excited and wanted to share this cool hack....Not sure if anyone has figured it out.... I did a blogpost on OAC new / modern interface and few features that I really liked. You can find my blogpost here . A good friend (became friends at Kscope18) Vijay Kurian  posted in his blog at theunlockedcube  about the Essbase Cloud features Part I and Part II and he has shown how to switch from classic interface to modern interface For some reason it didn't work for me in the pormotional OAC instance that I was using. However, I was able to switch based on what I poseted in my previous blogpost In general If you want to switch from Classic to Modern, follow the below screenshot However, for some reason that didn't work for me and I didn't knew why What I did might sound pretty silly but now I have the whole new Modern Interface that i can use without having to rely on the Deep Dive environment that I was using earlier. (That's Ora...
Post a Comment
Read more