In this exercise, you test the process of loading data to upper-level members and the effect of upper-level data loading on calculations.
Creating the ClearAll Calculation Script
1. In Administration Services Console, select File > Editors > Calculation Script Editor.
2. Create the following script:
CLEARBLOCK ALL;
3. Click (validate).
The message "Syntax check was successful." is displayed.
4. Click OK.
5. Save the calculation script as ClearAll.csc.
Loading and Calculating Multilevel Inputs
1. Open UpperLevel.xls (located in c:\CF\EssCalcs\Calcs\Normalize).
2. Select the Multilevel Input worksheet.
3. Connect to DynSales.
4. Set DynSales as the active connection.
5. Calculate ClearAll.csc.
6. Refresh the worksheet
Input data is sent to DynCalc DynSales.
7. Select the Multilevel AuditSheet worksheet.
8. Refresh the worksheet to see the loaded data.
9. Scroll down to the orange Channel Total section, and verify upper-level loads.
10. Calculate CalcDyn.csc.
Oracle Internal & Oracle Academy
Use Only
6 Normalizing Data Solutions
Essbase Analytics Calculate Databases 49
11. Refresh the worksheet.
The message "Calculation Errors" is displayed.
12. Examine the Comparison formulas section, and determine where calculated data is incorrect.
Gross Margin for the intersection of Channel Total and Family Total is incorrect.
Qtr 1 is also incorrect.
Modifying the Calculation Script
1. In Administration Services Console, expand the DynSales database node.
2. Expand the Calculation Scripts node.
3. Double-click CalcDyn.
The Calculation Script Editor window is displayed.
4. Select File > Save as.
5. Save the script as CYMulti.csc.
6. Modify the script to perform the following tasks:
• Set aggregate missing values to OFF.
• Calculate only Current Year data.
• Recalculate Gross Margin, after all dimensions are consolidated.
7. Compare your results to the following script:
/* Information
Created by: YourName Created On:9/22/2005
Purpose: This script consolidates the DynCalc DynSales database in Current Year and then recalculates Gross Margin to offset upper-level data loads.
Oracle Internal & Oracle Academy
Use Only
Lesson 6 Normalizing Data
Solutions CALC DIM (Accounts, "Year Tot", Customer, Product);
/* The Back Calculation */
(
"Gross Margin";
)
/* Close out the Baseline Fix */
ENDFIX
8. Repeat the steps described in “Loading and Calculating Level 0 Inputs” on page 3-50 to test your calculation script.
Loading and Calculating Level 0 Inputs 1. Select the Level 0 Input worksheet.
2. Set DynSales as the active connection.
3. Run ClearAll.csc.
4. Refresh the worksheet
Input data is sent to DynCalc DynSales.
5. Select the Level 0 AuditSheet worksheet.
6. Run CalcDyn.csc.
7. Refresh the worksheet.
8. Examine the comparison formulas for errors.
Modifying the Calculation Script
1. In Administration Services Console, open CalcDyn.csc.
2. Select File > Save as.
3. Save the script as CYLev0.csc.
4. Modify the script to perform the following tasks:
• Set aggregate missing values to ON.
• Calculate only Current Year data.
Oracle Internal & Oracle Academy
Use Only
6 Normalizing Data Solutions
Essbase Analytics Calculate Databases 51
5. Compare your results to the following script:
/* Information
Created by: YourName Created On: 9/22/2005
Purpose: This script consolidates the Bigcorp Sales database in Current Year and then recalculates Gross Margin to offset upper-level data loads.
CALC DIM (Accounts, "Year Tot", Customer, Product);
/* Close out the Baseline Fix */
ENDFIX
6. Repeat the steps in “Loading and Calculating Level 0 Inputs” on page 3-50 to test your calculation script.
Solution 6.2 Developing a Normalization Table
In this exercise, you create a normalization table.1. Open Budget.xls.
2. Select the Normalization worksheet.
3. Based on the input sheets in Budget.xls, complete the normalization table.
Oracle Internal & Oracle Academy
Use Only
Lesson 6 Normalizing Data
Solutions 4. Compare your results to the finished normalization table in the following figure:
Solution 6.3 Copying Rates and Drivers
In this exercise, you create a script to normalize rates and drivers.
1. Open CalcDyn.csc.
2. Save CalcDyn.csc as Budget.csc.
3. Modify the Budget.csc to match the following script:
/* Information
Created by: YourName Created On: 9/22/2005
Purpose: This script normalizes budget data in the DynCalc DynSales database.
*/
/* Housekeeping */
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET MSG SUMMARY;
/* Copy Current Year units to Budget */
FIX(@GENMBRS(Product, 5), @LEVMBRS(Customer, 0), Units) DATACOPY "Current Year" TO "Budget";
ENDFIX
Oracle Internal & Oracle Academy
Use Only
6 Normalizing Data Solutions
Essbase Analytics Calculate Databases 53
/* Baseline Fix */
FIX ("Budget") /* Normalization */
/* Normalize rates loaded to upper levels */
"List Price" = @PARENTVAL("Customer", "List Price");
"Discount %" = "Discount %"->"Family Total";
"Labor/Unit" = "Labor/Unit"->"Channel Total";
"Matl/Unit" = "Matl/Unit"->"Channel Total";
"Overhead Rate" = @MDANCESTVAL(2, "Product", 3, "Customer", 2);
/* Main Rollup */
CALC DIM (Accounts, "Year Tot", Customer, Product);
/* Close out the Baseline Fix */
ENDFIX
4. Save Budget.csc.
5. Run LoadBudget.bat to clear and reload input data.
6. In Budget.xls, select the AuditCalcs worksheet.
7. Set DynCalc as the active connection.
8. Calculate Budget.csc.
Oracle Internal & Oracle Academy
Use Only
Lesson 6 Normalizing Data
Solutions 9. Refresh the worksheet. Compare your results to the following figure:
Solution 6.4 Allocating Other Costs
In this exercise, you complete the budget calculation by adding a section to allocate other costs.
1. Open Budget.csc.
2. Modify Budget.csc to look like the following script:
/* Information
Created by: YourName
Oracle Internal & Oracle Academy
Use Only
6 Normalizing Data Solutions
Essbase Analytics Calculate Databases 55
Created On:9/22/2005
Purpose: This script calculates the DynCalc DynSales database.
/*
/* Housekeeping */
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET MSG SUMMARY;
/* Copy Current Year units to Budget */
FIX(@GENMBRS(Product, 5), @LEVMBRS(Customer, 0), Units) DATACOPY "Current Year" TO "Budget";
ENDFIX
/* Normalization */
/* Normalize rates loaded to upper levels */
"List Price" = @PARENTVAL("Customer", "List Price");
"Discount %" = "Discount %"->"Family Total";
"Labor/Unit" = "Labor/Unit"->"Channel Total";
"Matl/Unit" = "Matl/Unit"->"Channel Total";
"Overhead Rate" = @MDANCESTVAL(2, "Product", 3, "Customer", 2);
/*Focused rollup for allocation of Other Expenses: calculate Net Sales, then sum Units and Net Sales across Product and Customer dimensions for members used as the allocation base.*/
@IDESCENDANTS ("Net Sales");
FIX ("Net Sales", Units)
@IDESCENDANTS ("Family Total");
@IDESCENDANTS ("Channel Total");
ENDFIX
/* Allocate Other CGS */
"Material Variances" =
"Material Variances"->"Family Total"->"Channel Total"
* "Units" / "Units"->"Family Total"->"Channel Total";
"Labor Variances" =
"Labor Variances"->"Family Total"->"Channel Total"
* "Units" / "Units"->"Family Total"->"Channel Total";
"Overhead Variances" =
"Overhead Variances"->"Family Total"->"Channel Total"
* "Units" / "Units"->"Family Total"->"Channel Total";
Oracle Internal & Oracle Academy
Use Only
Lesson 6 Normalizing Data
Solutions
"Obsolete Charges" =
"Obsolete Charges"->"Family Total"->"Channel Total"
* "Net Sales" / "Net Sales"->"Family Total"->"Channel Total";
"Inventory Adjustments" =
"Inventory Adjustments"->"Family Total"->"Channel Total"
* "Net Sales" / "Net Sales"->"Family Total"->"Channel Total";
/* Main Rollup */
CALC DIM (Accounts, "Year Tot", Customer, Product);
3. Save Budget.csc.
Modifying the Outline
1. In the DynSales outline, right-click Net Sales.
2. Select Edit member properties.
The Member Properties dialog box is displayed.
3. From the Data Storage drop-down box, select Store Data.
4. Click OK.
5. Click Save.
The Restructure Database Options dialog box is displayed.
6. Select All data.
7. Click OK.
Testing the Calculation Script
1. Run LoadBudget.bat to clear and reload input data.
2. In Budget.xls, select the AuditCalcs worksheet.
3. Set DynCalc as the active connection.
4. Calculate Budget.csc.
Oracle Internal & Oracle Academy
Use Only
6 Normalizing Data Solutions
Essbase Analytics Calculate Databases 57
5. Refresh the worksheet. Compare your results to the following figure:
Solution 6.5 Optional: Creating a Multilevel Allocation
In this exercise, you create a multilevel allocation for Labor Variances.1. Save Budget.csc as BudgetB.csc.
2. Modify the allocation for Labor Variances to look like the following script:
/* Information
Created by: YourName Created On:9/22/2005
Oracle Internal & Oracle Academy
Use Only
Lesson 6 Normalizing Data
Solutions Purpose: This script calculates the DynCalc DynSales database.
/*
/* Housekeeping */
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET MSG SUMMARY;
/* Copy Current Year units to Budget */
FIX(@GENMBRS(Product, 5), @LEVMBRS(Customer, 0), Units) DATACOPY "Current Year" TO "Budget";
ENDFIX
/* Normalization */
/* Normalize rates loaded to upper levels */
"List Price" = @PARENTVAL("Customer", "List Price");
"Discount %" = "Discount %"->"Family Total";
"Labor/Unit" = "Labor/Unit"->"Channel Total";
"Matl/Unit" = "Matl/Unit"->"Channel Total";
"Overhead Rate" = @MDANCESTVAL(2, "Product", 3, "Customer", 2);
/*Focused rollup for allocation of Other Expenses: calculate Net Sales, then sum Units and Net Sales across Product and Customer dimensions for members used as the allocation base.*/
@IDESCENDANTS ("Net Sales");
FIX ("Net Sales", Units)
@IDESCENDANTS ("Family Total");
@IDESCENDANTS ("Channel Total");
ENDFIX
/* Allocate Other CGS */
"Material Variances" =
"Material Variances"->"Family Total"->"Channel Total"
* "Units" / "Units"->"Family Total"->"Channel Total";
"Overhead Variances" =
"Overhead Variances"->"Family Total"->"Channel Total"
* "Units" / "Units"->"Family Total"->"Channel Total";
/* Multilevel allocation for Labor Variances */
/* Step 1: Allocate Labor Variances to the children of Family Total based on an even split between the children with data */
Oracle Internal & Oracle Academy
Use Only
6 Normalizing Data Solutions
Essbase Analytics Calculate Databases 59
FIX( @CHILDREN( "Family Total"))
"Labor Variances" =
"Labor Variances"->"Family Total"->"Channel Total" /
@COUNT(
/* Step 2: Allocate Labor Variances to Gen5 (level 0) products based on percent of product family units */
FIX( @GENMBRS( "Product", 5))
"Labor Variances" =
@MDANCESTVAL( 2, "Product", 3, "Customer", 2, "Labor Variances")
* ("Units" /
@MDANCESTVAL( 2, "Product", 3, "Customer", 2, "Units"));
ENDFIX
/* Allocate Obsolete Charges and Inventory Adjustments based on percent of Net Sales */
"Obsolete Charges" =
"Obsolete Charges"->"Family Total"->"Channel Total"
* "Net Sales" / "Net Sales"->"Family Total"->"Channel Total";
"Inventory Adjustments" =
"Inventory Adjustments"->"Family Total"->"Channel Total"
* "Net Sales" / "Net Sales"->"Family Total"->"Channel Total";
/* Main Rollup */
CALC DIM (Accounts, "Year Tot", Customer, Product);
3. Save BudgetB.csc.
4. Run LoadBudget.bat to clear and reload input data.
5. In Budget.xls, select the AuditCalcs worksheet.
6. Set DynCalc as the active connection.
7. Calculate Budget.csc.
Oracle Internal & Oracle Academy
Use Only
Lesson 6 Normalizing Data
Solutions 8. Refresh the worksheet. Compare your results to the following figure:
Solution 6.6 Creating Multiple-Database Calculations
In this exercise, you create a location alias to leverage data stored in another database.
Creating a Location Alias
1. In Administration Services Console, right-click the DynSales database node.
2. Select Edit > Location aliases.
The Location Aliases dialog box is displayed.
Oracle Internal & Oracle Academy
Use Only
6 Normalizing Data Solutions
Essbase Analytics Calculate Databases 61
3. In the first row of the table, create a location alias based on the following table:
4. Click Test.
A check mark is displayed in the Verified column.
5. Click Set.
6. Click Close.
Calculating Across Databases
1. Create a calculation script for DynSales based on the following script:
/* Housekeeping */
SET UPDATECALC OFF;
/* Calculate Projected Tax */
"Projected Taxes" = "Gross Margin" * @XREF(Taxes, US, TR0003);
2. Save the script as Taxes.csc.
3. Run ResetDynamic.bat to clear, reload, and calculate input data.
4. Open Taxes.xls (located in c:\CF\EssCalcs\Normalize).
5. Select the ProjectedTax worksheet.
6. Set DynCalc as the active connection.
7. Calculate Taxes.csc.
8. Refresh the worksheet.
Parameter Value
Oracle Internal & Oracle Academy
Use Only
Lesson 6 Normalizing Data
Solutions 9. Compare your results to the following figure: