Creating a Business Rule Using a Unit Test

Creating a Unit Test

When creating and debugging business rules, it is important to remember that Oracle Essbase calculations work on a relative reference system where in Essbase calculates the provided syntax as it travels through the blocks in the FIX statement. Consequently, the easiest way to create or debug a business rule is to look at each individual calculation: pick a combination that you are calculating and see if it works. This allows you to consider a small number of values if the business rule does not work and makes your business rule calculate quickly. After the unit test calculates successfully you can test with a wider data set.

For example, assume this scenario: You want to calculate a percentage of Cost of Sales to identify the spread across Products, write the calculated value to a new member named COS Product% for all months, all years, all scenario, and all departments. In this scenario, you should create the business rule for COS Product% account and then pick one product as the single unit test for one department, one month, one year, and one scenario.

The formula that you should calculate against the COS Product% member in the dense Account dimension is COS Product% = "Cost of Sales" as a Percentage of "Cost of Sales"->Total Product.

You can then create a spreadsheet that contains all the individual data values required to perform this calculation for a specific combination (a unit). For this, you can choose "Sales Central" ->Jan->FY15->Forecast for the "Television".product, a Computer Accessory.

For this example, Television has a Cost of Sales value of 12 and Total Products adds up to 100, which means that the only values in the database are the values against 9 Product members for COS Account, all in January FY15 Forecast for "Sales Central". So, you start with a database that has been cleared and has input levl0 data only and create an Oracle Smart View for Office ad hoc query to retrieve the following data values you need.

The formula for this combination or unit test is:COS Product% (D3) = = COS (D1) as a Percentage of Total Product COS (D2), which means that this spreadsheet contains everything required to unit test this business rule.

Using the Unit Test to Create Business Rules

Start the business rule to calculate the COS Product% = member with COS (the first part of the formula). Look at the difference between the target line (line3) and the data source. So, for the COS (individual COS) look at the difference between column C in line 3 and line 1, each identifying Television. The only difference in members between line 3 and line1 is in Account; we only have to specify that as follows:

COS Product% = COS %

Do the same for the second part of the formula (Total COS ), which, in this case, is COS at the top level of Product. Because there are two differences between line3 and line2: COS and Product, specify the location in Product as well to complete the formula:

COS Product% = COS % ->Product;

At this point, executing this rule will retrieve #missing instead of the correct total as shown in the following illustration:

You must precalculate the data you require (Total COS in cell D2) to get the correct total.

Precalculating Required Data

To precalculate the required data to ensure that the business rule COS Product% = COS % ->Product; returns the correct value, you must include COS in it by modifying the rule as follows:

Fix (COS)
Agg (Product);
EndFix
COS Product% = COS % ->Product;