This is quite an interesting case. I do not think any analysis needs to have all the dimensions to bring in correct numbers (It does not sound any logical to me though!!). I believe to resolve this kind of issues only, there is a concept called "DEFAULT MEMBER" in multidimensional cubes, which is generally the Generation 1 member. When we fire over a query, the multidimensional database considers these default members while executing this query for the skipped dimensions, which I think should have been too in this case.
I do not have my Essbase Server handy, but could you check if there is any option to set this default member in the outline?
thanks for your input, I don't know if your 'default member' is an older version of Essbase, I found just one hit on line referring to it and how to set it, but it does not appear on my (latest patchset) version of Essbase.
I seem to have made some progress by forcing OBIEE not to use report based totals however, but I have to test this more extensively before I would deem it a fix...
I'm not sure if it's "normal" but this is how it's also working on my side. By default, we have 8 dimensions defined in ESSBASE. When we use Smartview connected to Hyperion Planning, in Excel, you always need to use all the dimensions to have an accurate result.
As per my experience, I need to use 8 dimensions to have an accurate results:
Year / Period / Account / Entity / Product / Currency / Scenario / Version
For Example, in Answer, to have the forecasted revenue for 2013 for worlwide, I will define as:
Year = FY13 / Period = YearTotal / Account = Total Revenue / Entity = Worlwide / Product = All / Currency = USD Constant rate / Scenario = BP / Version = Final
If I do not specify the year, I will have the same Total Revenue but for all years. If I do not include the scenario, I will have the same result but as a sum of all scenarios. It's why you see spurious data.
Also, concerning the "Default" dimension values. Here is an example for the Period Dimension as it's setup in our BI RPD:
- Level 1
- Level 2
- Level 3
- Period Default
If you choose Level 1, you will have only the value: YearTotal
If you choose Level 2, you will have only the values: Quarter 1 / Quarter 2 / Quarter 3 / Quarter 4
If you choose Level 3, you will have only the values: JAN/FEB/MAR/APR/MAY/JUN/JUL/AUG/SEP/OCT/NOV/DEC
If you choose Period Default, you will have all the level values: YearTotal / Quarter 1 / Quarter 2 / Quarter 3 / Quarter 4 / JAN/FEB/MAR/APR/MAY/JUN/JUL/AUG/SEP/OCT/NOV/DEC
You will ask, If we have all the levels in the Period Default, why to use the levels? It's because with the levels you can drill down from one to another. With the Period Default, you will not have the drill down capability in your table.