Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Thursday, August 12, 2010

Time for a quick Quiz

I finished up presenting three sessions at the DOUG semminar in Dallas on Tuesday and my voice is shot.I talk way too much.  I was amazed at how many people were there. I'll guess over 100 just to hear me talk. Well actually to hear Edward Roske, Tracy McMullen and me talk. The biggest draw was Tracy's track on OBIEE. It seems to be the hot ticket right now. In all I think it was a very good day and people got a lot out of it. I only stayed for a little of the Rangers/Yankee game that followed the session as I had a long drive back to Austin. Thank you interRel for hosting the event. (Is this a shameless plug for the company or me just being nice?)

It has ben a while since I have done one of my quizes so I thought I would give you an easy one.
Bob is a real zero in life, but he does not like missing anything.. He loves the little round orbs that are nothing. But when he tries in smartview 11 to have his missing values display as zeros he has problems. Can you tell Bob  two ways (without doing find and replace) that he can have true numeric zeros in a smartview retrieve. As added bonuses
How he can take non-numeric data in Excel and show it in formulas as a zero without using an "IF" statement
Second Bonus, How can you have the classic Add-in return numeric zeros.
Third bonus, Why can this be a bad thing?


Cameron Lackpour said...

Answers in order:
1) #NumericZero and (0), yes that last one works, much to my surprise.
2) I suspect you're being tricky, so maybe these answers aren't what you're looking for. I am guessing you mean conversion of a number stored as text. A few options:
1) The VALUE() function around the cell reference to the non-numeric text string will return a value whether it's the result of a SV retrieve or not.
2) Multiplying by 1 in another column will do it. This also works for cells with a leading single apostrohpe.
3) You could also copy and paste special using the multiply option.
4) Also you could do a text to columns conversion although that is a pain.

I'm sure searching would reveal more approachs unless I have totally misunderstood you in which case I have no idea. :)
3) (0)
4) The answer to the second bonus question will send zeros up to db instead of #Missing, A Bad Thing.


Cameron Lackpour

Robert said...

Change the #Missing to a 0 in the Hyperion>Options dialog box, or set it to #NumericZero.

First bonus?

Second bonus - (0) in the classic add-in replaces #Missing with a numeric zero.

Third bonus - when users send data with the classic add-in, the numeric zeros are stored in the cube, which takes up storage space. Smart View doesn't submit zeros.

amarnath said...

I know that there is an option to set how the #Missing values has to be displayed. I am not able to recollect.

If it is Classic Add-in, go to Options, Global, you can set how the #Missing has to be displayed. If you enter 0, all the #Missing will be displayed as 0 (Not numeric 0).

I am sure that there is a way it can be done in swartview but couldn't recollect.

How he can take non-numeric data in Excel and show it in formulas as a zero without using an "IF" statement
NO CLUE :(..