I got an email from my boss Edward Roske about an entry in the Tech Reference. He is working on a cool super secret project (all will be Reveled and revealed at KScope) and he asked me about something he saw in the Tech reference on the AGGMISSG command.
For those of you who don’t like to read the tech reference I’ll save you the time going to it.
Specifies whether Essbase consolidates #MISSING values in the database.
The default behavior of SET AGGMISSG is determined by the global setting for the database, as described in the Oracle Essbase Database Administrator's Guide.
SET AGGMISSG ON | OFF ;
SET AGGMISSG commands apply to calculating sparse dimensions.
SET AGGMISSG OFF;
What struck him as funny and me as well was the statement
SET AGGMISSG commands apply to calculating sparse dimensions. (my highlighting).
Neither he nor I could remember it acting that way. I reached out to MMIP Cameron Lackpour and he opened his System 9.3.1 tech reference and it said the same thing.
Thinking this can’t be right, think Planning with upper level periods allowing input and being dense, I decided to test it.
Using Cameron’s FDITHWW sample Basic, I cleared all the data and set the upper levels of year to be stored.
I used Smart View to populate the following intersection
(Note Profit shows up because Measures is dynamic)
I then ran the following calculation script:
SET AGGMISSG OFF;
SET UPDATECALC OFF;
Here are my results:
As you can see, my dense dimension acted like Edward and I expected, with it ignoring the #MISSING children and keeping Q1 and AGGing it up to Year. This means the Tech reference is slightly askew.
As a side, there is something else in the Tech ref example, If you look there is a statement:
I’d never heard of it and a search of the Tech reference has the only reference in the Aggmissg example. Trying to run it gives an invalid syntax so this is inaccurate as well.
I will be submitting both of these opportunities to the Documentation group as they actually do fix these type of errors when they are found.
Moral of the story, Even if you read it in the Documentation, try it yourself and you might be surprised at the results.