Converting member Calculations is typically the next task you will need to address. This can be a very simple to an extraordinarily complex task. As was discussed earlier in this chapter, this is one of the biggest considerations to determine before you decide to convert a BSo cube to an ASo cube: Can the Business rules (member Formulas) be converted to mDx? In most instances, the answer to this is a qualified yes. Qualified because you will either need to learn mDx to do the conversion yourself, or hire a consultant who knows mDx to do the conversion for you. If you choose to do this yourself, as I did, you need to understand that the learning curve can be steep and the path somewhat jagged. In the end, there is a great deal of satisfaction in making a complete conversion on your own.
typically, the calculation conversion process is going to involve one of two scenar-ios. you will either be converting calculations in the dimension tagged as Accounts (Scenario 1), or you will be converting calculations found in other dimensions (Scenario 2). When the calculations are in other dimensions than the dimension tagged as Accounts, this generally will force you to complete additional steps. If we used Demo Basic as an example, we can step through both of these scenarios.
Note: I keep referring to the “dimension tagged as Accounts” because the dimension itself may not actually be called that. you may have named the dimension metrics, or Sales Data, and then given it the Accounts tag. So, I use the “dimension tagged as Accounts” reference to be very clear which dimension I reference.
Figure 5.5 Wizard select destination screen. (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
Figure 5.6 Data preview of Demo Basic after a default load. (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
Figure 5.7 Comparative Data Preview before and after calculation change to MDX. (From Oracle Essbase Administration Services. With permission.)
• Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals
Figure 5.8 Comparison of three outline change methods. (From Oracle Essbase Administration Services. With permission.)
all the member calculations that were in the BSo cube (Demo). oh, that all of your calculations (and mine) were this easy to convert. I know when I was first learning and writing mDx code, the network 54 Essbase message board (http://www.network54.com/
Forum/58296/) was a priceless tool and kept me sane. I asked a lot of questions on how to convert various BSo calculations to their ASo counterparts. As we continue this chapter, we will assume the calculations are now coded in mDx. Determining Solve order is the next task we want to focus on as we get close to completing the conversion process.