How many zeros are there in Essbase and How to replace them with #Missing

The first post of this month and the last one also 🙁 (I really hope that coming months, I’ll have more interesting things to share)
I’m sure that you have heard about this “For Essbase ‘zeros’ are not ‘nulls’. Essbase treats zero just like any other number.”
There is/was a good discussion on this on Linkedin and I thought of sharing the same topic (illustrate it 🙂 )
For those who are lazy 😉
There are two ways of replacing 0s with #Missing (I came to know about the second way only after going through the above discussion.)
1st and most likely used method

You can use an IF statement to check whether a value is zero or not, if it is a zero replace it with #Missing.

Let’s consider that Budget – Sales of New York and Massachusetts are 0 

I can create a calculation script and get rid of the 0s.

2nd and the brilliant way!!!

This is one which I liked the most (after knowing it of course). 
In Essbase if you try to divide a value by 0, then it populates a message in log files (Member [xxxx] attempts to divide by Missing, Invalid, or Near Zero value (Message will not repeat) and populate the result with #Missing!!!!!
You can create a script which is similar to the one given below
This works faster than the if conditions, (Budget and Sales are members of dense dimensions).
For the co-ordinates where Budget is 0, it adds a #Missing because this will result in Divide by 0 condition.
How to count the 0s in database

So now comes the question, how can I find out how many zeros are there in my database?
One way to do that will be export the level 0 data and then search for 0s in it 🙂 or else you can use Essbase Export Analysis developed by Kyle Goodfriend.
You can give threshold values to check whether a threshold is met or not. It’ll provide information about
How many #Missing Values
How many 0s (Which I’m interested in)
Values at or below the Threshold
Values above Threshold
I thought of testing this out. I took an export before running the calc and the calc replaced 6 zeros.
Before Calc
After Calc
1904 (0s) before calc and 1898 (0s) after calc. (Works perfect :))))

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “How many zeros are there in Essbase and How to replace them with #Missing

  • GlennS

    While your post is interesting, it only tells half the story. removing the zeros does not reduce fragmentation, database size or calculation time. To do that you then must restructure the database

  • Celvin Kattookaran

    @GlennS as you mentioned it's just the half of the story. To reduce fragmentation we must restructure the database.

    I was trying to illustrate the knowledge shared by you and Edward Roske. Thank you for pointing out

  • Anonymous

    Hi thank you for your Blog it is really good , I am kinda new to Hyperion, and I was wondering in the Fix statement why you didn't include the "sales" dimension between the brackets I know the question is not related to the subject and maybe stupid,but I really wanna know if there is any differences or not.

    thank you

  • Malik

    Hi… Its an awesome post… my question is one step beyond.. does the restructure gets rids of all the empty block (ie. a block with all #MISSING). What i've been noticing that it does not as my testing proofs out. And i could be wrong. My understanding is you've to Export and then Import back in to get rid of empty blocks. Can one explain?

  • Malik

    Ok looks like i find the answer here
    Explicit Restructures

    When you manually initiate a database restructure, you perform an explicit restructure. An explicit restructure forces a full restructure of the database. A full restructure comprises a dense restructure plus removal of empty blocks.

    And my understanding was correct as i was thinking in the direction of dense restructure that is not same as full. And thats what i find out that Empty blocks are not removed with dense restructure.

    Can you confirm the above.