Exercise 11.1
Calculating the Sales Database
Construct a full database calculation script, testing results before and after calculation:
1. Reset the sales database by running the
C:\CF\Bootcamp\Scripts\ResetBigcorp.bat script.
2. On a separate sheet of paper, record the following precalculation database statistics for Sales:
• Number of existing blocks
• Existing level 0 blocks
• Existing upper-level blocks
• Block density
• Percentage of maximum blocks existing
3. Create a calculation script that turns off intelligent calculation and calculates all dimensions in the Sales database. Save the script as Anatomy.csc.
Essbase 11.1.2 Bootcamp 53
.
During multidimensional calculation, input data is transformed into useful information. A thorough understanding of the process of multidimensional calculation is necessary before you create more complex calculations.
4. In Anatomy.xls (in C:\CF\Bootcamp\CalcIt\Anatomy), test the calculations by using the This Is a Block worksheet.
NOTE: Remember to enable Excel formatting before refreshing the worksheet.
Calculated data is displayed in the worksheet.
NOTE: Unlike previous worksheets, this workbook was saved after connecting to the Sales database, and thus contains stored connection information
(including the last used connection and the POV). Therefore, you are not required to set the active database connection before you refresh.
54
Essbase 11.1.2 Bootcamp
.
5. On a separate piece of paper, record the following postcalculation database statistics for Sales, and compare them to the precalculation statistics:
• Number of existing blocks
• Existing level 0 blocks
• Existing upper-level blocks
• Percentage of maximum blocks existing
Extra Credit: Recording Detailed Statistics
Implement an iterative test of Anatomy.csc to calculate the Sales database one dimension at a time. Record the changes to the following database statistics after data load and after each dimension calculation:
• Number of existing blocks
• Existing level 0 blocks
• Existing upper-level blocks
• Block density
• Percentage of maximum blocks existing Essbase 11.1.2 Bootcamp
55
.
• Block density
Lesson 12 Creating Calculation Scripts
Exercise 12.1
Organizing Calculation Scripts
You want to document your scripts thoroughly for the Bigcorp database administrators, who will eventually maintain the Essbase system.
Exercise 12.2
Correcting Rates and Percentages
Although Anatomy.csc consolidates all Sales database dimensions, some rates and percentages are not aggregated correctly.
Modify Anatomy.csc to add a back calculation, correcting input rates and percentages:
1. Add a block of back-calculation formulas to derive the correct rates for the following accounts:
NOTE: Enclose the block of formulas in parentheses to enforce a second calculation pass.
2. Save Anatomy.csc and test your changes:
a. Run ResetBigcorp.bat.
b. Calculate Anatomy.csc.
56
Essbase 11.1.2 Bootcamp
.
In Anatomy.csc, add a brief information section with your name and the date, a housekeeping section header, and a main rollup section header. Save the changes.
c. In Anatomy.xls, refresh the This is a Block worksheet and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in
c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Level0Block worksheet.
Essbase 11.1.2 Bootcamp 57
.
d. Change the POV to Channel Total, Family Total, and Current Year and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in
c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the UpperLevelBlock worksheet.
58Essbase 11.1.2 Bootcamp
.
Controlling the Calculation Process
Lesson 13 Controlling the Calculation Process
Exercise 13.1
Focusing Calculations with FIX
In the interest of performance, the Bigcorp management team wants you to modify Anatomy.csc so that it calculates only Current Year data for the months in Qtr 1.
1. Modify the Anatomy.csc script as follows, and save it as CYCalc.csc:
a. Focus the script to calculate only for Current Year and months in Qtr 1.
b. Make any modifications necessary to the rest of the script as a result of adding the fix statement.
c. Use commenting sections to identify the start and end of the fix.
2. Test your calculations:
a. Run ResetBigcorp.bat.
b. Calculate CYCalc.csc.
c. Refresh the Fixit worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in
c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Fix It worksheet.
Essbase 11.1.2 Bootcamp 59
.
Create a current-year calculation based on Anatomy.csc:
Exercise 13.2
Calculating Conditionally with IF
The Sales group gave you a set of business rules for a special forecasting calculation.
Create a calculation script that uses conditional logic to satisfy the business rules for the forecast:
1. Using the If It worksheet in Anatomy.xls as a reference, write a calculation that performs the following tasks in the Forecast scenario, and save it as IfIt.csc:
• Pushes units from August to September
• Pushes units into Qtr 4 months with a two-month lag and a 10% accelerator
• Pushes price into September and into the months in Qtr 4 with a one-month lag and a 10% decelerator
2. Test your calculations:
a. Run ResetBigcorp.bat.
b. Calculate IfIt.csc.
c. Refresh the If It worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in
c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the If It worksheet.
60
Essbase 11.1.2 Bootcamp
.
Extra Credit
Controlling the Calculation Process
The 10% accelerator for units creates partial units in some months.
Add logic to your script so that it does not return partial units:
1. Modify IfIt.csc so that units are rounded to the nearest whole number, and save it as IfItEC.
a. Run ResetBigcorp.bat.
b. Calculate IfItEC.csc.
c. Refresh the If It worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in
c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the If It_Extra worksheet.
Essbase 11.1.2 Bootcamp 61
.
2. Test your calculations:
Lesson 14 Referencing Members in Calculations
Exercise 14.1
Creating Mix Calculations
The Sales group requires “percent of total” mix calculations for analysis, based on several measures.
1. Apply the following logic to add member formulas:
Member Unit Mix by Cust Unit Mix
Sales $ Mix Formula Logic
Units for current customer/units for all customers Units for current product/units for current product family
Net sales for current product/net sales for current product family
2. Set storage properties for the modified accounts to Dynamic Calc.
3. Test your calculation results:
a. Run ResetBigcorp.bat.
b. Calculate Anatomy.csc.
62
Essbase 11.1.2 Bootcamp
.
Add formulas to the Sales outline:
c. Refresh the Customer Mix worksheet in Anatomy.xls and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Customer Mix worksheet.
Essbase 11.1.2 Bootcamp 63
.
d. Refresh the Product Mix worksheet in Anatomy.xls and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in
c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Product Mix worksheet.
Exercise 14.2
Calculating Time Variances
The Bigcorp Finance group requires period variance calculations for its analysis and wants you to create the calculations in the Sales database. Because the formulas must be updated regularly (for example, a variance between current month and prior month data must be updated every month), you decide to create substitution variables for easier maintenance.
64Essbase 11.1.2 Bootcamp
.
Create and test substitution variables:
1. From Administration Services Console, add the following substitution variables to Bigcorp Sales:
NOTE: Because quarter names include spaces, separate variables are required for reporting and calculation purposes.
2. In the Sales outline, add variance formulas to the Year Tot dimension members Cur Mo Vs Prior and Cur Qtr Vs Prior:
a. Use @VAR for the variance calculation.
b. Use & (ampersand) to identify variable names in the formulas.
c. Set the data storage property for Cur Mo Vs Prior and Cur Qtr Vs Prior to Dynamic Calc.
NOTE: If you accidentally clear data when saving your outline, you must run ResetBigcorp.bat and calculate Anatomy.csc before proceeding.
Essbase 11.1.2 Bootcamp 65
.
3. Test your calculation: Refresh the Sub Variable worksheet in Anatomy.xls, and compare your calculation results to the following figures:, or open
Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Sub Variable worksheet.
66
Essbase 11.1.2 Bootcamp
.
Lesson 15 Developing and Testing Complex Calculation Scripts
Exercise 15.1
Loading Data to Upper-Level Members
2. Load and calculate multilevel inputs:
a. Calculate ClearAll.csc.
b. In AdvancedCalcs.xls (in c:\CF\CalcIt\Advanced), from the Multilevel Input worksheet, submit input data.
c. If necessary, set the following Smart View options:
• Set aliases for Bigcorp Sales to None.
• Set the replacement value for #NoData/Missing labels to #NumericZero.
d. On the Multilevel AuditSheet worksheet, calculate CYCalc.csc, and refresh to view calculated data.
TIP: Examine the Comparison formulas section to determine where calculated data is incorrect.
3. Modify and test CYCalc.csc:
a. Save the script as CYMulti.csc and modify the script to perform the following tasks:
• Set aggregate missing values to OFF
• Calculate Current Year data for all time periods
• Consolidate the Year Tot dimension
• Recalculate Gross Margin, after all dimensions are consolidated b. Repeat step 2 to test calculations.
4. Load and calculate level 0 inputs:
a. Calculate ClearAll.csc.
b. In AdvancedCalcs.xls, from the Level 0 InputSheet worksheet, submit input data.
Essbase 11.1.2 Bootcamp 67
.
1. Create a script called ClearAll.csc that clears all data from the database.
c. On the Level 0 Audit worksheet, calculate CYCalc.csc, and refresh to view calculated data.
TIP: Examine the Comparison formulas section to determine where calculated data is incorrect.
5. Modify CYCalc.csc:
a. Save the script as CYLev0.csc and modify the script to perform the following tasks:
• Set aggregate missing values to ON
• Calculate Current Year data for all time periods
• Consolidate the Year Tot dimension b. Repeat step 4 to test calculations.
68
Essbase 11.1.2 Bootcamp
.
Lesson 16 Normalizing Data
Exercise 16.1
Allocating Current Year Data
1. Save CYLev0.csc as CYAlloc.csc and add normalization that allocates Other CGS data by using a dynamic ratio calculation.
a. Calculate ClearAll.csc.
b. In AdvancedCalcs.xls, from the Allocation Input worksheet, submit input data.
c. On the Allocation AuditSheet worksheet, calculate CYAlloc.csc, and refresh to view calculated data.
TIP: Examine the comparison formulas to validate your calculation.
Exercise 16.2
Normalizing Budget Data
1. In Budget.xls (in c:\CF\Bootcamp\CalcIt\Advanced) fill in the normalization table on the Normalization tab. Base your work on the input spreadsheets in the workbook.
2. Save CYAlloc.csc as Budget.csc and add script to normalize the rates and drivers listed in the normalization table.
3. Test the script:
a. Run LoadBudget.bat (in c:\CF\Bootcamp\Scripts) to clear Sales and load input data for Current Year and Budget scenarios.
b. In Budget.xls, on the Audit Calcs worksheet, calculate Budget.csc, and refresh to view calculated data for the default POV (O-IBM, Lightbolt 365 A, and Budget).
Essbase 11.1.2 Bootcamp 69
.
2. Test the script:
c. Compare your calculation results to the following figure, or open
Budget_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Level 0 worksheet.
70
Essbase 11.1.2 Bootcamp
.
d. Change your POV to Channel Total, Family Total, and Budget, and refresh to view calculated data. Compare your calculation results to the following figure, or open Budget_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Upper Level worksheet.
Essbase 11.1.2 Bootcamp 71
.
Lesson 17 Creating Attribute Dimensions
Exercise 17.1
Viewing Attribute Data
1. Create an attribute crosstab report:
b. In Analysis.xls (in c:\CF\Bootcamp\Analysis), refresh the Regions worksheet.
c. Test the behavior of zooming in on level 0 attributes.
d. Using ad hoc operations, create a report with regions in the rows and the customer channels OEM, Retail, and Distributor in the columns.
2. View attribute calculations:
a. Delete all but one column from the grid.
b. In the remaining column header, enter Attribute Calculations, and refresh the worksheet.
c. Zoom in on Attribute Calculations to display discrete attribute calculations.
72
Essbase 11.1.2 Bootcamp
.
a. Run ResetBigcorp.bat (in c:\CF\Bootcamp\Scripts) and the CalcBC.csc calculation script.