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 :))))
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
@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
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.
Please mail me your query to my mail address
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?
Ok looks like i find the answer here
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.
To remove empty blocks you can perform an manual restructure or you can even run a CLEARBLOCK EMPTY script.
Ok I got to know the reason for putting it as 'Budget = Budget * Budget / Budget' ….Thanks Celvin, it was nice blog as always!!!