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.
1904 (0s) before calc and 1898 (0s) after calc. (Works perfect :))))