Database Reference
In-Depth Information
Figure 5.6
Data preview of Demo Basic after a default load. (From Oracle Essbase Administration Services. With
permission.)
First, let us examine converting calculations in the dimension tagged as Accounts. In
Demo:Basic, we can see that we have two members with formulas in the BSo cube: Profit_%
and margin_%. Both of these use the prebuilt Essbase mathematical operator: “%”.
Profit_% = "Profit % Sales";
Margin_% = "Margin % Sales";
When you open the ASo outline, you can see the formula was converted as is, but if
you validate this or try to resave the outline, you will get an mDx error (and it does not
tell you what is wrong or how to fix the error). So, how do you fix the calculation? First,
you need to know what the calculation is actually doing so you can recode it in mDx.
The “%” operator evaluates member1 as a percentage of member2. A data preview in
EAS of the Demo Basic cube after a default load and calc would reflect the values shown
in Figure 5.6.
This would enable us to determine that Profit_% is using this formula:
(Profit/Sales) * 100
133,980/607,902 = .220397 * 100 = 22.0397
While mDx does not have a % function for us to use, we can certainly recreate the
member calculation as:
([Accounts].[Profit]/[Accounts].[Sales])*100
After we change both formulas and load the same data that was in the original BSo
cube (Demo) to the new ASo cube (ASoDemo), we can now do a data preview as shown
in Figure 5.7 on our converted cube and validate that the data does indeed match and
the calculated values are exactly the same.
Thus, for each member calculation you will proceed through three steps:
1. Evaluate the BSo calculation and determine mathematically what it is doing.
2. Convert that information to an mDx formula.
3. run a report (or data preview) in the original BSo cube as well as the new ASo
cube and verify that the values are correct.
once you have completed this process for each member calculation, the conversions
for Scenario 1 would be deemed complete. There is a good chance that all of the calcu-
lations will not be this simple, and you will need to complete another task in order to
