Database Reference
In-Depth Information
Figure 5.7
Comparative Data Preview before and after calculation change to MDX. (From Oracle Essbase Administration
Services. With permission.)
retrieve the correct values. That task involves determining the solve order for the cal-
culation. We will address this in more detail after we discuss Scenario 2—Calculations
that are in other dimensions.
When we were using the Wizard, one of the error messages we received for the member
variance was that Aggregate Storage outlines only allow calculations in the Accounts
Dimension or in a dimension with a Dynamic hierarchy. If you examine the converted
outline, variance is a member of the dimension called Scenario. In the Demo cube, the
formula on this member was:
Actual - Budget;
unlike the Accounts dimension where the formulas were brought into the ASo cube
as they existed in the BSo cube, the formula was removed altogether. This is because
the definition of the dimension has to be altered to facilitate member calculations; it has
to be changed from Stored to Dynamic or hierarchies Enabled. If the cube does not
have a significant amount of data, changing the dimension to be Dynamic should work
fine. on the other hand, if the cube has a lot of data, you will want to use hierarchies
Enabled. This will allow you to have multiple hierarchies with a mixture of Stored and
Dynamic settings. using this should improve your report times after the cube is aggre-
gated. The aggregation is similar to completing the default calculation on a BSo cube.
It is the activity that provides upper level values for speed of retrieval. The members
where the data is loaded can be in a section tagged as Stored and the member with
the calculation can be in a section tagged as Dynamic. once the appropriate hierarchy
information has been changed, the calculation formula can be added to the member.
using the converted Demo cube outline, this change is made by opening the
ASoDemo outline in EAS and right-clicking on the Scenario member. Select the
option to Edit member Properties and you will be in the Information tab by default.
Approximately three quarters of the way down the page is a section called hierarchy
Information. Click on the area where it currently has the value of Stored and choose the
new value. Figure 5.8 shows the original Demo outline, the ASoDemo outline using a
Dynamic setting, and the ASoDemo2 outline using a hierarchies Enabled setting.
to use the hierarchies Enabled option, I created some additional parent members
to separate the loaded versus the calculated members. For me, the third panel has been
the most optimal method for adding member calculations to other dimensions than the
dimension tagged as Accounts. With that task complete, we have finished converting
