D67955GC10_ag.pdf

(1)

Oracle Essbase 11.1.2: Calculate

Block Storage Databases

Activity Guide

D67955GC10 Edition 1.0 December 2010 D71276

Oracle Internal & Or

(2)

This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle.

The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free.

Restricted Rights Notice

If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS

The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Author Lisa Alexander Technical Contributors and Reviewers Dave Collins Frank Laffey Mike Nader Sergiy Pecherskyy Aneel Shenker Stan Ziolkowski Editor Susan Moxley

Oracle Internal & Or

(3)

Exercises

Lesson 1:

Essbase Overview

Exercise 1.1 Starting Services . . . 2 Exercise 1.2 Accessing Administration Services Console . . . 3

Lesson 3:

Calculation Script Basics

Exercise 3.1 Troubleshooting Intelligent Calculation . . . 4

Lesson 4:

Dynamic Calculation Overview

Exercise 4.1 Eliminating Back Calculation . . . 5

Lesson 5:

Developing and Testing IF and FIX Calculation Scripts

Exercise 5.1 Optimizing Script Calculation Performance . . . 7 Exercise 5.2 Creating Complex Conditionals . . . 8

Lesson 6:

Copying and Clearing Data

Exercise 6.1 Creating a Baseline Forecast. . . 10 Exercise 6.2 Clearing Bad Data . . . 12

Lesson 7:

Developing Advanced Calculations

Exercise 7.1 Creating a Working Scenario . . . 14 Exercise 7.2 Calculating a Rolling Average . . . 15

Lesson 8:

Developing Analytic Calculations

Exercise 8.1 Averaging Sales Risks . . . 17

Oracle Internal & Or

(4)

Table of Contents 

Lesson 9:

Multidimensional Dynamic Calculations

Exercise 9.1 Optimizing with Dynamic Calculation . . . 19

Lesson 10: Normalizing Data

Exercise 10.1 Loading Data to Upper-Level Members . . . 21

Exercise 10.2 Developing a Normalization Table . . . 22

Exercise 10.3 Copying Rates and Drivers . . . 22

Lesson 11: Performing Advanced Allocations

Exercise 11.1 Allocating Other Costs . . . 25

Exercise 11.2 Optional: Tuning the Budget Calculation . . . 27

Exercise 11.3 Optional: Creating a Multilevel Allocation. . . 28

Exercise 11.4 Creating Multiple-Database Calculations . . . 30

Exercise Solutions

Lesson 1:

Essbase Overview

Solution 1.1 Starting Services . . . 34

Solution 1.2 Accessing Administration Services Console . . . 35

Opening Administration Services Console . . . 35

Exploring the Navigation Hierarchy . . . 35

Verifying a Database Outline . . . 36

Lesson 3:

Calculation Script Basics

Solution 3.1 Troubleshooting Intelligent Calculation . . . 37

Reviewing the Calculation Script . . . 37

Testing the Calculations . . . 37

Modifying the Calculation Script . . . 38

Lesson 4:

Dynamic Calculation Overview

Solution 4.1 Eliminating Back Calculation . . . 40

Modifying the Sales Outline . . . 40

Modifying CalcAll.csc . . . 44

Oracle Internal & Or

(5)

Table of Contents

Lesson 5:

Developing and Testing IF and FIX Calculation Scripts

Solution 5.1 Optimizing Script Calculation Performance . . . 47

Solution 5.2 Creating Complex Conditionals . . . 49

Creating the Script . . . 49

Lesson 6:

Copying and Clearing Data

Solution 6.1 Creating a Baseline Forecast. . . 52

Creating the Calculation Script. . . 52

Extra Credit: Forecasting Units Based on Trends . . . 53

Solution 6.2 Clearing Bad Data . . . 54

Creating the Calculation Script. . . 54

Lesson 7:

Developing Advanced Calculations

Solution 7.1 Creating a Working Scenario . . . 57

Creating Substitution Variables . . . 57

Copying Data into the Working Scenario . . . 57

Solution 7.2 Calculating a Rolling Average . . . 59

Creating a Rolling Average Calculation . . . 59

Modifying the Outline . . . 59

Copying the Formula to the Outline . . . 60

Lesson 8:

Developing Analytic Calculations

Solution 8.1 Averaging Sales Risks . . . 62

Setting up a Calculated Text Measure . . . 62

Oracle Internal & Or

(6)

Table of Contents 

Lesson 9:

Multidimensional Dynamic Calculations

Solution 9.1 Optimizing with Dynamic Calculation . . . 64

Creating a Test Application . . . 64

Modifying the DynSales Outline . . . 64

Adding DynCalc to Private Connections in Smart View . . . 65

Lesson 10: Normalizing Data

Solution 10.1 Loading Data to Upper-Level Members . . . 68

Creating a Clear Script . . . 68

Loading and Calculating Multilevel Inputs . . . 68

Loading and Calculating Level 0 Inputs . . . 70

Modifying the Calculation Script for Leaf-Node Loads . . . 71

Solution 10.2 Developing a Normalization Table . . . 72

Solution 10.3 Copying Rates and Drivers . . . 73

Creating a Normalization Script: List Price. . . 73

Testing List Price Normalization. . . 73

Completing the Normalization Script . . . 74

Testing Normalization. . . 75

Lesson 11: Performing Advanced Allocations

Solution 11.1 Allocating Other Costs . . . 77

Modifying the Budget Calculation. . . 77

Running a Calculation Test Cycle and Viewing the Calculation Log . . . 78

Modifying the Outline . . . 79

Testing the Normalization Calculations . . . 79

Solution 11.2 Optional: Tuning the Budget Calculation . . . 80

Adding Variables and Replacing Repetitive Code . . . 80

Solution 11.3 Optional: Creating a Multilevel Allocation. . . 83

Adding Multilevel Allocations . . . 83

Solution 11.4 Creating Multiple-Database Calculations . . . 87

Creating a Location Alias . . . 87

Calculating Across Databases . . . 87

Oracle Internal & Or

(7)

Exercises

Overview

This section contains hands-on exercises that you will work on throughout this course. Exercises are grouped by lesson.

Your instructor may choose to perform some exercises with you in

demonstration format. Therefore, you may not be required to complete all exercises. It is, however, important that you complete all exercises as requested by the instructor, as many exercises build upon other exercises. There are no exercises for Lesson 2, Calculation Overview.

Oracle Internal & Or

(8)

Lesson 1 Essbase Overview

Exercises

Lesson 1

Essbase Overview

Exercise 1.1

Starting Services

In this exercise, you start the necessary services for the class learning activities. 1. From the Managing Services folder on the Windows Desktop, double-click

StartServices.

The following message is displayed: "Are you sure you want to START all Oracle/Hyperion Services?"

2. Click Yes.

A process window displays the services that are starting. When the process finishes, the following message is displayed: "All Oracle/Hyperion Services started. Your environment is now ready for your class. Enjoy!"

3. Click OK to close the dialog box.

Wait 3–5 minutes after the window closes before continuing to the next step, so that the services do not cause false failure messages.

4. Select Start, then Programs, then Oracle EPM System, then Foundation Services, then

epmsystem1, and then EPM System Diagnostic.

The Oracle EPM System diagnostic report is displayed in the first column, and PASSED or FAILED is displayed for each service.

5. Perform one of the following actions:

• If all services display a PASSED status, close the report. You successfully completed the exercise.

• If any service displays a FAILED status, close the report, and proceed to step 6. 6. From the Managing Services folder on the Windows Desktop, double-click Services.

The Services window is displayed.

7. Verify that the following services have a status of "Started": - Hyperion Administration Services - Web Application - Hyperion Foundation Services - Managed Server - Hyperion Provider Services - Web Application - Hyperion Studio Service BPMS bpms1

Oracle Internal & Or

(9)

1 Essbase Overview Exercises

- Oracle Process Manager (ohsInstance3193331783) - Oracle Process Manager (EPM_epmsystem1) - OracleOraDb11g_home1TNSListener

- OracleServiceORCL

NOTE: If any services failed to start, alert your instructor.

8. Close the Services window.

Exercise 1.2

Accessing Administration Services Console

Perform the following tasks to familiarize yourself with Administration Services Console: 1. Open Administration Services Console (with user name admin and password

hyperion)

2. Explore the navigation hierarchy: Navigate to the list of Essbase Servers, and open a database outline.

3. Verify the database outline before closing the database outline.

Lesson 2

Oracle Internal & Or

(10)

Lesson 3 Calculation Script Basics

Exercises

Lesson 3

Calculation Script Basics

Exercise 3.1

Troubleshooting Intelligent Calculation

The BigCalc Sales database has a calculation script that returns incorrect results. In this exercise, you troubleshoot the calculation script.

1. In Administration Services Console, review IntCalc.csc (located under BigCalc Sales). Answer the following questions:

• What calculations must each section of the script accomplish?

• How many calculation passes are required for the script to fully calculate? • Will the intelligent calculator affect how the script runs?

2. Test the calculation script:

a. Run ResetData.bat (located in c:\CF\EssCalcs\Scripts) to clear and reload data.

b.Execute IntCalc.csc.

c. Open BasicCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

d.Refresh the IntelligentCalc worksheet to view calculation results. Notice the incorrect results in Qtr 1 for the input rate accounts.

e.Open the BigCalc application log to view calculation messages. 3. Modify, save, and test IntCalc.csc:

a. Modify the script to accomplish the following tasks: • Execute the back-calculation section of the script.

• Mark calculated data blocks clean at the end of the calculation process. b.Save IntCalc.csc.

c. Repeat step 2 to test your calculation results.

Oracle Internal & Or

(11)

4 Dynamic Calculation Overview Exercises

Lesson 4

Dynamic Calculation Overview

Exercise 4.1

Eliminating Back Calculation

You want to improve calculation performance in the BigCalc Sales database. Because back calculation can more than double the time required to run a calculation script, eliminating back calculation can significantly improve your calculation performance.

1. In Administration Services Console, review CalcAll.csc (located under BigCalc Sales). Answer the following questions:

• How many calculation passes are required for the script to fully calculate?

2. Modify the Sales outline in such a way that the back-calculation section of the CalcAll script is no longer necessary.

Hints:

• Open Dynamic.xls (located in c:\CF\EssCalcs\Dynamic). As you add new members to your outline, use the ShadowRates worksheet as a guide for what names to use.

• Prevent input rates from aggregating across dimensions.

• Follow the guidelines for user-friendly modeling: Move input rates to the Input branch of the Accounts dimension. (Moving input rates can affect hierarchy-based calculations and may require additional formulas to be added to the outline.)

NOTE: This exercise has many possible solutions.

3. Modify CalcAll.csc:

a. Remove the back-calculation section of the script. b.Save it as CalcDyn.csc.

a. Run ResetData.bat to clear and reload data. b.Execute CalcDyn.csc.

Oracle Internal & Or

(12)

Lesson 4 Dynamic Calculation Overview

Exercises c. Open BasicCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted, enter

your user credentials (user name admin and password hyperion).

d.Refresh the ShadowRates worksheet and compare your results to the following figure, or open BasicCalcs_Solutions.xls (in

c:\CF\EssCalcs\Calcs\Complete) and compare your results to the ShadowRates worksheet:

Oracle Internal & Or

(13)

5 Developing and Testing IF and FIX Calculation Scripts Exercises

Lesson 5

Developing and Testing IF and FIX

Calculation Scripts

Exercise 5.1

Optimizing Script Calculation Performance

Bigcorp forecast analysts want to improve the performance of a calculation script that assigns list prices to products in each product line. In this exercise, you improve the calculation performance of a prebuilt calculation script.

1. In Administration Services Console, review PriceFIX.csc (located under BigCalc Sales). Answer the following questions:

• How many calculation passes are required for the script to fully calculate? • Will the intelligent calculator affect how the script runs?

a. Run ResetData.bat (located in c:\CF\EssCalcs\Scripts) to clear and reload data.

b.Execute PriceFIX.csc.

c. Open the BigCalc application log to view calculation messages and write down the total calculation time.

d.Open BasicCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

e.Refresh the Prices worksheet to view calculation results.

Verify that prices for each product line match the global list prices at the bottom of the report.

3. In Administration Services Console, modify the PriceFIX script:

a. Reduce the number of calculation passes by substituting IF logic for the multiple fixes.

b.Save the script as PriceIF.

c. Repeat step 2 to test the performance of the modified script.

Oracle Internal & Or

(14)

Lesson 5 Developing and Testing IF and FIX Calculation Scripts

Exercises

Exercise 5.2

Creating Complex Conditionals

Bigcorp forecast analysts want to assign discount percentages to customers based on the number of forecasted units. The percentages must also differ by sales channel.

1. Create and save a calculation script:

a. Create a script to calculate the business rules in the following table:

Forecasted Units Forecasted Discount Percentage

Less than 300 • OEM customers: 6% • Retail customers: 4.5% • Distributor customers: 5.5% Between 300 and 499 • OEM customers: 10%

• Retail customers: 7% • Distributor customers: 9% 500 and over • OEM customers: 15%

• Retail customers: 12% • Distributor customers: 13%

b.Save the script as DiscFcst.csc. 2. Test the calculation script:

a. Run ResetData.bat to clear and reload data. b.Execute DiscFcst.csc.

c. Open BasicCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(15)

5 Developing and Testing IF and FIX Calculation Scripts Exercises

d.Refresh the Discount% worksheet and compare your results to the following figure, or open BasicCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the Discount% worksheet:

Oracle Internal & Or

(16)

Lesson 6 Copying and Clearing Data

Exercises

Lesson 6

Copying and Clearing Data

Exercise 6.1

Creating a Baseline Forecast

Bigcorp forecast analysts have already created forecasts for May through August. They want to create a baseline forecast from existing actual data for the remaining months of the year.

1. Create a script called BaseFcts.csc that accomplishes the following tasks: a. Copies Sep through Dec prior year actual data to the Forecast scenario b.Increases Sep through Dec forecast prices by 10%

a. Run ResetData.bat to clear and reload data. b.Execute BaseFcst.csc.

c. Open AdvancedCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

d.Refresh the Forecast worksheet and compare your results to the following figure, or open AdvancedCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the Forecast worksheet:

Oracle Internal & Or

(17)

6 Copying and Clearing Data Exercises

3. Extra credit:: Forecasting Units Based on Trends

• Add a section to the script to forecast Sep-Dec units, based on a linear regression trend of forecast and actual data from Jan-Aug.

Hints:

• Before calculating the trend, you must first copy current year Jan-Apr units data into the Forecast scenario.

• Use the linear regression method with the @TREND function.

• Read the description of the @TREND function in the Oracle Essbase Technical

Reference.

• In the @TREND function, you do not need to provide any of the following optional lists:

- Xlist (underlying variable values) - weightList (weights for the data points)

- errorList (differences between the data points)

- XforecastList (underlying variable values for forecasted data) • After testing your calculation, refresh the ForecastEX worksheet in

AdvancedCalcs.xls to check your results against the following figure, or open AdvancedCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the ForecastEX worksheet:

Oracle Internal & Or

(18)

Exercises

Exercise 6.2

Clearing Bad Data

In April, the customer O-Apple decided to stop ordering Roadranger 170 A hard drives, effective in May. Bigcorp analysts want you to clear all forecasted data for the customer-product combination during the months May-Dec.

1. Create a calculation script that clears May-Dec forecasted data from the combination of O-Apple and Roadranger 170 A. Save the script as ClearDat.csc.

a. Run ResetData.bat to clear and reload data. b.Execute ClearDat.csc.

c. Open AdvancedCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(19)

6 Copying and Clearing Data Exercises

d.Refresh the ClearData worksheet and compare your results to the following figure, or open AdvancedCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the ClearData worksheet:

NOTE: In a production environment, as a follow-up to clearing data, you would also modify input files or create rejection rules in your rules files to prevent bad data from being reloaded.

Oracle Internal & Or

(20)

Lesson 7 Developing Advanced Calculations

Exercises

Lesson 7

Developing Advanced Calculations

Exercise 7.1

Creating a Working Scenario

Each month Bigcorp forecast analysts require a working scenario that displays actual data from January through the prior month and forecast data from the current month through December.

Complete the following tasks to facilitate the required analysis:

1. Create the substitution variables for the BigCalc Sales database as described in the following table:

Variable Name Variable Value

CurrentMonth May PriorMonth Apr

2. Create a script that copies data into the Working scenario:

a. Copy Current Year data into Working for Jan through the prior month. b.Copy Forecast data into Working for the current month through Dec. c. Save the script as Working.csc.

a. Run ResetData.bat to clear and reload data. b.Execute BaseFcst.csc and then Working.csc.

NOTE: Running the reset script clears the forecast data calculated by the BaseFcst script. Thus, you must recalculate the forecast before copying it to the Working scenario.

c. Open AdvancedCalcs.xls (in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(21)

7 Developing Advanced Calculations Exercises

d.Refresh the Working worksheet and compare your results to the following figure, or open AdvancedCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the Working worksheet:

Exercise 7.2

Calculating a Rolling Average

For unit sales, Bigcorp finance analysts require a six-month rolling average calculation that spans the Prior Year and Current Year scenarios.

1. Create a calculation script called RollAvg.csc that calculates the account Rolling 6 Avg Units as the average of units for the current month and the prior five months.

Hints:

• Because time-related members exist in multiple dimensions, your average must be cross-dimensional.

• The @XRANGE function creates a range of multidimensional members, but requires modification of your outline to work properly.

• This exercise has many possible solutions.

2. If you used @XRANGE in your calculation script, modify your scenario dimension so the scenarios are in chronological order.

a. Run ResetData.bat to clear and reload data. b.Execute RollAvg.csc.

c. Open AdvancedCalcs.xls (in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(22)

Exercises d.Refresh the RollingAverage worksheet and compare your results to the following

figure, or open AdvancedCalcs_Solutions.xls (in

c:\CF\EssCalcs\Calcs\Complete) and compare your results to the RollingAverage worksheet:

4. Copy the formula to the outline:

a. Copy the formula from the calculation script.

b.In the BigCalc Sales outline, open the Member Properties for Rolling 6 Avg Units and paste the formula into the formula editor.

c. Save the outline. If prompted, discard all data.

Oracle Internal & Or

(23)

8 Developing Analytic Calculations Exercises

Lesson 8

Developing Analytic Calculations

Exercise 8.1

Averaging Sales Risks

Before the next sales cycle, Bigcorp decided to identify its riskiest customers to best allocate limited risk mitigation resources. In this exercise, you modify an existing text measure to calculate sales risk averages for all customer channels.

1. In the BigCalc Sales outline, modify the calculated text measure Sales Risk Avg based on the properties in the following table:

Member Property Value

Type Text

Select Text List Sales Risk Levels Data Storage Dynamic Calc

Formula Level 1 customers: Sales Risk Avg is an average of Sales Risk

across all children of the current customer.

Level 0 customers: Sales Risk Avg is equal to Sales Risk.

2. Test the calculation:

a. Run ResetData.bat to clear and reload data.

NOTE: Because Sales Risk Avg is dynamic, a calculation script is not required to view the calculation results.

b.Open AdvancedCalcs.xls (in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(24)

Lesson 8 Developing Analytic Calculations

Exercises c. Refresh the SalesRiskAvg worksheet and compare your results to the following

figure, or open AdvancedCalcs_Solutions.xls (in

c:\CF\EssCalcs\Calcs\Complete) and compare your results to the SalesRiskAvg worksheet:

Oracle Internal & Or

(25)

9 Multidimensional Dynamic Calculations Exercises

Lesson 9

Multidimensional Dynamic Calculations

Exercise 9.1

Optimizing with Dynamic Calculation

You want to optimize your data block size by dynamically calculating dense calculations. In this exercise, you apply dynamic calculations wherever appropriate to members of dense dimensions, and then test your calculation results. This exercise has many possible solutions.

1. In Administration Services Console, create a test application: a. Copy the BigCalc application and name it DynCalc.

b.Rename the DynCalc Sales database DynSales.

2. Modify the DynSales outline to include dynamic calculations wherever appropriate, and save the outline.

3. From Excel, open the Smart View panel and add the DynSales database to your list of private connections.

a. Run ResetDynamic.bat to clear, reload, and calculate data.

NOTE: ResetDynamic.bat automatically calculates the CalcDyn calculation script after clearing and reloading data.

b.Open Dynamic.xls (in c:\CF\EssCalcs\Dynamic). If prompted, enter your user credentials (user name admin and password hyperion).

c. Select the Aggressive worksheet, and set localhost_DynCalc_DynSales as the active

connection.

_{Oracle Internal & Or}

(26)

Lesson 9 Multidimensional Dynamic Calculations

Exercises d.Refresh the worksheet and compare your results to the following figure, or open

Dynamic_Solutions.xls (in c:\CF\EssCalcs\Calcs\Dynamic) and compare your results to the Aggressive worksheet:

Oracle Internal & Or

(27)

10 Normalizing Data Exercises

Lesson 10 Normalizing Data

Exercise 10.1

Loading Data to Upper-Level Members

The Bigcorp management team wants to load current year Other CGS measures to the total of all products and all customers, rather than deriving and loading level 0 data. In this exercise, you use the BigCalc Sales database to test different methods of loading upper-level data and you modify CalcDyn.csc, as necessary, to return correct

calculation results at all levels.

1. Create a calculation script that clears all data in the BigCalc Sales database. Save the file as ClearAll.csc.

2. Use UpperLevel.xls (in c:\CF\EssCalcs\Normalize) to load and calculate multilevel input data:

a. Calculate ClearAll.csc to clear the Sales database. b.Submit the data on the Multilevel Input worksheet.

c. Run CalcDyn.csc (you are testing an existing calculation against new input data to see if it returns correct results).

d.Refresh the Multilevel Audit worksheet to view the calculation results.

e.Examine the Comparison formulas section, and determine where calculated data is incorrect.

NOTE: If calculations are correct, the following message is displayed in cell B3: No Calculation Errors.

3. Modify the calculation script:

a. Save CalcDyn.csc as CYMulti.csc.

b.Modify the script to correct the calculation issues and limit the calculation to only Current Year data.

c. Repeat step 2 to test the changes.

4. Use UpperLevel.xls (in c:\CF\EssCalcs\Normalize) to load and calculate level 0 input data:

a. Calculate ClearAll.csc to clear the Sales database.

Oracle Internal & Or

(28)

Lesson 10 Normalizing Data

Exercises b.Submit the data on the Level 0 Input worksheet.

c. Run CalcDyn.csc (you are testing an existing calculation against new input data to see if it returns correct results).

d.Refresh the Level 0 Audit worksheet to view the calculation results.

e.Examine the Comparison formulas section, and determine where calculated data is incorrect.

NOTE: If calculations are correct, the following message is displayed in cell B3: No Calculation Errors.

5. Modify the calculation script:

a. Save CalcDyn.csc as CYLev0.csc.

b.Modify the script to optimize calculation: aggregate missing values and calculate only Current Year data.

c. Repeat step 2 to test the changes.

Exercise 10.2

Developing a Normalization Table

Bigcorp budget analysts want to load budget data to upper-level members. You must create a complex script for budget data. Before creating your script, you plan the normalization process based on sample input data.

1. Open Budget.xls (in c:\CF\EssCalcs\Normalize). 2. Select the Normalization Table worksheet.

3. Based on the input sheets in Budget.xls, complete the normalization table.

Exercise 10.3

Copying Rates and Drivers

Based on the input data in Budget.xls and the normalization table that you developed in the preceding exercise, you create a script to normalize rates and drivers.

1. Create a normalization script for DynCalc DynSales: a. Modify CalcDyn.csc to accomplish the following tasks:

• Calculate only Budget data.

Oracle Internal & Or

(29)

10 Normalizing Data Exercises

• Copy List Price down to level 0 blocks. b.Validate the script and save it as Budget.csc. 2. Test List Price normalization:

a. Run LoadBudget.bat (in c:\CF\EssCalcs\Scripts) to clear and reload input data.

b.In Budget.xls, select the AuditCalcs worksheet.

c. If necessary, open the Smart View panel and double-click the DynSales database to connect.

d.Calculate Budget.csc, and refresh the worksheet.

e.Observe the calculation results: Why is no data returned for List Price?

3. Complete the normalization script. Modify Budget.csc to accomplish the following tasks:

• Create level 0 Budget data blocks.

• Provide current year units data for budget calculations. • Normalize all rates and drivers.

4. Test the normalization script:

a. Run LoadBudgetAct.bat (in c:\CF\EssCalcs\Scripts) to clear and reload budget and actual input data.

Oracle Internal & Or

(30)

Lesson 10 Normalizing Data

Exercises e.Compare your results to the following figure, or open Budget_Solutions.xls (in

c:\CF\EssCalcs\Normalization\Complete) and compare your results to the AuditCalcs worksheet:

Oracle Internal & Or

(31)

11 Performing Advanced Allocations Exercises

Lesson 11 Performing Advanced Allocations

Exercise 11.1

Allocating Other Costs

In this exercise, you complete the budget calculation by adding a section to allocate other costs.

Keep the following points in mind:

• The allocation base must be aggregated before the ratios are calculated. • Calculations for stored members should not be dependent on Dynamic Calc

members. If they are, consider changing the Dynamic Calc members to stored members.

1. Modify Budget.csc. Add a section to the normalization process that allocates other costs based on the criteria in the following table:

Accounts Allocation Basis

Material Variances % of Units Labor Variances % of Units Overhead Variances % of Units Obsolete Charges % of Net Sales Inventory Adjustments % of Net Sales

2. Run a calculation test cycle and view the calculation log:

a. Run LoadBudgetAct.bat to clear and reload actual and budget input data. b.In Administration Services Console, calculate Budget.csc.

c. Open the application log for DynCalc and scroll to the end of the log. d.Observe the warning messages relating to Net Sales, for example:

Regular member [Inventory Adjustments] depends on dynamic-calc member [Net Sales].

3. Modify the outline:

a. In the DynSales outline, change Net Sales to a stored member. b.Save the outline.

4. Test the normalization script:

Oracle Internal & Or

(32)

Lesson 11 Performing Advanced Allocations

Exercises a. Run LoadBudgetAct.bat (in c:\CF\EssCalcs\Scripts) to clear and reload

budget and actual input data.

e.Compare your results to the following figure, or open Budget_Solutions.xls (in c:\CF\EssCalcs\Normalization\Complete) and compare your results to the AuditCalcs2 worksheet:

Oracle Internal & Or

(33)

Exercise 11.2

Optional: Tuning the Budget Calculation

The allocations in the budget calculation contain sections of repetitive code, which increases the chances of user error when creating the script. In this exercise, you replace repetitive code with variables.

1. Add variables and replace repetitive code in Budget.csc:

a. Declare script variables AllocUnits and AllocSales in the Housekeeping section of the script.

b.In the allocation section of the script, assign values to the two variables. Each variable stands for an allocation ratio.

c. In the formulas for the Other CGS accounts, replace the allocation ratios with script variables.

d.Validate the script and save it as BudgetA.csc.

TIP: Remember that script variables must be used inside a calculation member block, similar to IF statements.

2. Test the modified script:

d.Calculate BudgetA.csc, and refresh the worksheet.

Oracle Internal & Or

(34)

Exercises e.Compare your results to the following figure, or open Budget_Solutions.xls (in

c:\CF\EssCalcs\Normalization\Complete) and compare your results to the AuditCalcs2 worksheet:

Exercise 11.3

Optional: Creating a Multilevel Allocation

Bigcorp budget analysts changed the business logic for the allocation of Labor Variances. In this exercise, you create a multilevel allocation for Labor Variances.

1. Add multilevel allocations to Budget.csc:

a. Modify the allocation for Labor Variances to complete the following calculation steps:

• Allocate data to the children of Family Total based on an even split between the children that have data.

Oracle Internal & Or

(35)

• Allocate data from the children of Family Total down to level 0 members based on the percentage of total units in the respective product family.

b.Save Budget.csc as BudgetB.csc. 2. Test the modified script:

d.Calculate BudgetB.csc, and refresh the worksheet.

e.Compare your results to the following figure, or open Budget_Solutions.xls (in c:\CF\EssCalcs\Normalization\Complete) and compare your results to the AuditCalcs3 worksheet:

Oracle Internal & Or

(36)

Exercises

Exercise 11.4

Creating Multiple-Database Calculations

Bigcorp tax analysts maintain a separate database, BigTax Taxes, to store tax rates and other tax-related information. Bigcorp forecast analysts want to calculate the projected tax based on the gross margin stored in DynSales and the U.S. tax rate stored in BigTax Taxes.

1. In the DynCalc DynSales database, create a location alias of Taxes for the BigTax Taxes database.

2. Create a cross-database calculation script called Taxes.csc that accomplishes the following tasks:

• Turns intelligent calculation off.

• Calculates Projected Tax for all scenarios as gross margin times the current US tax rate stored in the TR0003 account in BigTax Taxes.

a. Run ResetDynamic.bat to clear, reload, and calculate input data.

b.Open Taxes.xls (in c:\CF\EssCalcs\Normalize) and select the ProjectedTax worksheet.

c. Set DynCalc as the active connection.

d.Calculate Taxes.csc and refresh the worksheet.

Oracle Internal & Or

(37)

e.Compare your results to the following figure, or open Taxes_Solutions.xls (in c:\CF\EssCalcs\Normalization\Complete) and compare your results to the Projected Tax worksheet:

Oracle Internal & Or

(38)

Exercises

Oracle Internal & Or

(39)

Exercise Solutions

Overview

This section contains step-by-step solutions to successfully complete all exercises. Solutions are grouped by lesson.

There are no solutions for Lesson 2, Calculation Overview.

Oracle Internal & Or

(40)

Solutions

Lesson 1

Essbase Overview

Solution 1.1

Starting Services

In this exercise, you start the necessary services for the class learning activities. 1. From the Managing Services folder on the Windows Desktop, double-click

StartServices.

The following message is displayed: "Are you sure you want to START all Oracle/Hyperion Services?"

2. Click Yes.

A process window displays the services that are starting. When the process finishes, the following message is displayed: "All Oracle/Hyperion Services started. Your environment is now ready for your class. Enjoy!"

3. Click OK to close the dialog box.

Wait 3–5 minutes after the window closes before continuing to the next step, so that the services do not cause false failure messages.

4. Select Start, then Programs, then Oracle EPM System, then Foundation Services, then

epmsystem1, and then EPM System Diagnostic.

The Oracle EPM System diagnostic report is displayed in the first column, and PASSED or FAILED is displayed for each service.

5. Perform one of the following actions:

• If all services display a PASSED status, close the report. You successfully completed the exercise.

• If any service displays a FAILED status, close the report, and proceed to step 6. 6. From the Managing Services folder on the Windows Desktop, double-click Services.

The Services window is displayed.

7. Verify that the following services have a status of "Started": - Hyperion Administration Services - Web Application - Hyperion Foundation Services - Managed Server - Hyperion Provider Services - Web Application - Hyperion Studio Service BPMS bpms1

Oracle Internal & Or

(41)

1 Essbase Overview Solutions

- Oracle Process Manager (ohsInstance3193331783) - Oracle Process Manager (EPM_epmsystem1) - OracleOraDb11g_home1TNSListener

- OracleServiceORCL

NOTE: If any services failed to start, alert your instructor.

8. Close the Services window.

Solution 1.2

Accessing Administration Services Console

Opening Administration Services Console

1. Select Start, then Programs, then Oracle EPM System, then Essbase, then Essbase Administration Services, and then Start Administration Services Console.

The Administration Services Console login prompt is displayed. 2. Enter the following information:

• Server: localhost • Username: admin • Password: hyperion 3. Click OK.

Administration Services Console is displayed.

Exploring the Navigation Hierarchy 1. Select the Enterprise View tab (Ctrl+Shift+E).

2. Expand Essbase Servers, then localhost, then Applications, then Sample, and then Basic. A list of database objects is displayed.

3. Double-click the Outline node (Ctrl+Enter).

The Basic outline is displayed in the object window.

Oracle Internal & Or

(42)

Solutions Verifying a Database Outline

1. Click Verify.

The following message is displayed: "The outline verified successfully." 2. Click OK.

3. Select File, and then Close.

Lesson 2

Oracle Internal & Or

(43)

3 Calculation Script Basics Solutions

Lesson 3

Calculation Script Basics

Solution 3.1

Troubleshooting Intelligent Calculation

Reviewing the Calculation Script

1. In Administration Services Console, expand the BigCalc application node. 2. Expand the Sales database node.

3. Expand the Calculation Scripts node. 4. Double-click IntCalc.csc.

5. Click Yes to lock the script while viewing.

IntCalc.csc is displayed in Calculation Script Editor. 6. Review the calculation script and answer the following questions:

• What calculations must each section of the script accomplish? • Housekeeping: Sets log messaging to a summary level • Main rollup: Calculates all dimensions

• Back calculation: recalculates input rates across all data blocks

• How many calculation passes are required for the script to fully calculate? 2 (one pass for the main rollup, one pass for the back calculation)

• Will the intelligent calculator affect how the script runs? Yes

Testing the Calculations

1. In Windows Explorer, browse to c:\CF\EssCalcs\Scripts. 2. Double-click ResetData.bat to clear and reload data. 3. In Calculation Script Editor for IntCalc.csc, press F5.

The Execute Calculation Script dialog box is displayed. 4. Click OK to execute the calculation script.

5. Open BasicCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted for user credentials, complete the following steps:

• For User Name, enter admin.

Oracle Internal & Or

(44)

Lesson 3 Calculation Script Basics

Solutions • For Password, enter hyperion.

• Click Connect.

6. Select the IntelligentCalc worksheet and, on the Essbase ribbon, click Refresh. Notice the incorrect results in Qtr 1 for the input rate accounts: List Price, Discount %, Labor/Unit, Matl/Unit, and Overhead Rate are all incorrectly aggregated across time.

7. In Administration Services Console, examine the calculation messages in the application log:

a. Right-click the BigCalc application node. b.Select View, and then Log.

The Log Viewer Options dialog box is displayed. c. Select Starting date, and click OK.

d.Scroll down in the application log until you see a block of messages starting with the text Received Command [Calculate] from user [admin@Native

Directory] using [IntCalc.csc]. Examine the calculation messages in the log.

e.Close the Log Viewer window.

Modifying the Calculation Script

1. Modify the body of IntCalc.csc to match the following script: /* Housekeeping */

SET MSG SUMMARY; SET UPDATECALC OFF; /* Main Rollup */ CALC ALL;

/* Back Calculation */ /* Correct input rates */ (

"List Price" = "Gross Sales" / Units;

"Discount %" = "Discounts" / "Gross Sales"; "Labor/Unit" = "Direct Labor" / Units; "Matl/Unit" = Material / Units;

"Overhead Rate" = Overhead / "Direct Labor"; "Gross Margin %";

Oracle Internal & Or

(45)

3 Calculation Script Basics Solutions

)

/* Mark calculated data blocks clean */ SET CLEARUPDATESTATUS ONLY;

CALC ALL;

2. In the File menu, select Save.

3. Repeat the steps under "Testing the Calculations" to test your calculation results. 4. Close Calculation Script Editor.

Oracle Internal & Or

(46)

Solutions

Lesson 4

Dynamic Calculation Overview

Solution 4.1

Eliminating Back Calculation

3. Expand the Calculation Scripts node. 4. Double-click CalcAll.csc.

CalcAll.csc is displayed in Calculation Script Editor. 6. Review the calculation script and answer the following questions:

• Housekeeping: Disables intelligent calculation, enables aggregation of missing values, and sets log messaging to a summary level

• Main rollup: Calculates all dimensions except scenario

• Back calculation: Recalculates input rates across all data blocks

• How many calculation passes are required for the script to fully calculate? 2 (one pass for the main rollup, one for the back calculation)

7. Close Calculation Script Editor.

Modifying the Sales Outline 1. Double-click the Sales outline node.

Outline Editor is opened. 2. Find List Price:

a. In the Edit menu, select Find members.

b.In the "Search for" text box, enter List Price, and click OK. The Find Results tab and match message are displayed. c. Click OK to close the message.

Oracle Internal & Or

(47)

4 Dynamic Calculation Overview Solutions

d.On the Find Results tab, double-click List Price.

The Outline tab is displayed, and List Price is selected. 3. Move List Price to the Input branch:

a. In the Edit menu, select Cut.

b.In the Accounts dimension, select Input. c. In the Edit menu, select Paste child.

The following message is displayed: Are you sure you want to move the selected member?

d.Click Yes.

4. Repeat steps 2 and 3 for the following members: • Discount %

• Labor/Unit • Matl/Unit • Overhead Rate

5. Change the consolidation property for List Price:

a. Under Input, right-click List Price and select Edit member properties. The Member Properties dialog box is displayed.

b.In the Consolidation drop-down list, select (^) Never. c. Click OK.

6. Repeat step 5 for the following members: • Discount %

• Labor/Unit • Matl/Unit • Overhead Rate

7. Add a shadow rate to the outline:

a. Right-click Gross Sales, and select Add child.

b.In the text box, enter Avg List Price and press Enter. c. Press Esc to close the next text box.

Oracle Internal & Or

(48)

Solutions d.Open the Member Properties dialog box for Avg List Price.

e.In the Consolidation drop-down list, select (~) Ignore. f. In the Data Storage drop-down list, select Dynamic Calc. g.Select the Formula tab, and enter the following formula:

"Gross Sales" / "Units";

h.Click Verify to check the formula syntax.

The following message is displayed: Formula verified successfully. No syntax errors detected.

i. Click OK to close the message, and then click OK to close the Member Properties dialog box.

8. Repeat step 7 to add the remaining shadow rates to the outline based on the following table:

Member Parent Member Properties Member Formula

Avg Disc % Discounts Consolidation (~) Ignore Data Storage: Dynamic Calc

Variance reporting expense: true

"Discounts" / "Gross Sales";

Avg Labor/Unit Direct Labor (under Cost of Sales)

Consolidation (~) Ignore Data Storage: Dynamic Calc

"Direct Labor" / "Units";

Avg Matl/Unit Material Consolidation (~) Ignore Data Storage: Dynamic Calc

"Material" / "Units";

Avg OH Rate Overhead Consolidation (~) Ignore Data Storage: Dynamic Calc

"Overhead" / "Direct Labor";

Oracle Internal & Or

(49)

9. Add formulas to outline members based on the following table:

Member Parent Member Formula

Gross Sales Net Sales Units * "List Price";

Discounts Net Sales "Gross Sales" * "Discount %"; Direct Labor Cost of Sales "Labor/Unit" * Units;

Material Cost of Sales "Matl/Unit" * Units;

Overhead Cost of Sales "Direct Labor" * "Overhead Rate";

10. Change the consolidation operator for the following members to Ignore (~): • Units (under Gross Sales)

• Gross Sales (shared, under Discounts) • Units (shared, under Direct Labor) • Units (shared, under Material)

• Direct Labor (shared, under Overhead)

11. Change the data storage property for Gross Margin % to Dynamic Calc.

Oracle Internal & Or

(50)

Solutions 12. Compare your completed outline to the following figures:

Gross Margin accounts:

Input accounts:

13. Click Save.

14. If prompted to restructure data, select Discard all data.

Modifying CalcAll.csc 1. Open CalcAll.csc.

2. Save CalcAll.csc as CalcDyn.csc.

3. Remove the back-calculation section of the script. 4. Verify CalcDyn.csc:

Oracle Internal & Or

(51)

a. In the Syntax menu, select Check syntax.

The following message is displayed: Syntax check was successful. b.Click OK.

5. Save CalcDyn.csc.

1. In Windows Explorer, browse to c:\CF\EssCalcs\Scripts. 2. Double-click ResetData.bat to clear and reload data.

3. Open BasicCalcs.xls, and select the ShadowRates worksheet (if prompted, enter your user credentials).

4. On the Essbase ribbon, click Calculate.

The Calculation Scripts dialog box is displayed. 5. In the script list, select CalcDyn, and click Launch.

The following message is displayed: CalcDyn was successful. 6. Click OK, and then Close to return to Smart View.

7. On the Essbase ribbon, click Refresh.

Oracle Internal & Or

(52)

Solutions 8. Compare your calculation results to the following figure, or open

BasicCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the ShadowRates worksheet:

9. Close Excel.

Oracle Internal & Or

(53)

5 Developing and Testing IF and FIX Calculation Scripts Solutions

Lesson 5

Developing and Testing IF and FIX

Calculation Scripts

Solution 5.1

Optimizing Script Calculation Performance

3. Expand the Calculation Scripts node. 4. Double-click PriceFIX.csc.

PriceFIX.csc is displayed in Calculation Script Editor. 6. Review the calculation script and answer the following questions:

• Housekeeping: Sets log messaging to a summary level, disables intelligent calculation, aggregates missing values

• Baseline Fix: Limits the calculation to the Forecast scenario • Normalization: Assigns prices to products by product line • Main rollup: Calculates all dimensions

• Back calculation: Recalculates input rates across all data blocks

• How many calculation passes are required for the script to fully calculate? 6 (five for normalization, one for the main rollup)

• Will the intelligent calculator affect how the script runs? Yes (which is why it is disabled in the Housekeeping section)

1. In Windows Explorer, browse to c:\CF\EssCalcs\Scripts. 2. Double-click ResetData.bat to clear and reload data.

Oracle Internal & Or

(54)

Solutions 3. In Calculation Script Editor for PriceFix.csc, press F5.

The Execute Calculation Script dialog box is displayed. 4. Click OK to execute the calculation script.

5. Examine the calculation messages in the application log: a. Right-click the BigCalc application node.

b.Select View, and then Log.

The Log Viewer Options dialog box is displayed. c. Select Starting date, enter today’s date, and click OK.

d.Scroll down the application log until you see a block of messages starting with the text Received Command [Calculate] from user [admin@Native Directory] using [PriceFix.csc]. Examine the calculation messages in the log.

e.Write down the total calculation time.

6. Open BasicCalcs.xls (located in c:\CF\EssCalcs\Calcs). If prompted, enter your user credentials (user name admin and password hyperion).

7. Select the Prices worksheet and, on the Essbase ribbon, click Refresh.

Verify that prices for each product line match the global list prices at the bottom of the report.

Modifying the Calculation Script

1. Modify the body of PriceFIX.csc to match the following script: /* Housekeeping */

SET MSG SUMMARY; SET UPDATECALC OFF; SET AGGMISSG ON; /* Baseline Fix */ FIX(Forecast)

/* Normalization */

/* Assign prices to products by product line */ "List Price"

(

IF(@ISDESC("LIGHTBOLT"))

"List Price" = "No Customer"->"No Product"->"LB Price"; ELSEIF(@ISDESC("THUNDERBALL"))

Oracle Internal & Or

(55)

"List Price" = "No Customer"->"No Product"->"TB Price"; ELSEIF(@ISDESC("ROADRANGER"))

"List Price" = "No Customer"->"No Product"->"RR Price"; ELSEIF(@ISDESC("MAVRIDER"))

"List Price" = "No Customer"->"No Product"->"MV Price"; ELSEIF(@ISDESC("FIREBRAND"))

"List Price" = "No Customer"->"No Product"->"FB Price"; ENDIF

)

/* Main Rollup */

CALC DIM (Accounts, "Year Tot", Customer, Product); /* Close out the Baseline Fix */

ENDFIX

2. Save the script as PriceIF.csc.

3. Repeat the steps under "Testing the Calculations" to test the performance of the modified script.

Solution 5.2

Creating Complex Conditionals

Creating the Script

1. In Administration Services Console, in the navigation hierarchy, select the Sales database node.

2. In the File menu, select Editors, and then Calculation Script Editor. Calculation Script Editor is displayed.

3. Create the following calculation script: /* Housekeeping */

SET UPDATECALC OFF; /* Baseline Fix */ FIX ("Forecast")

/* Assign values to Discount % */ "Discount %"

(

IF (Units < 300)

IF (@ISCHILD ("OEM"))

Oracle Internal & Or

(56)

Solutions ELSEIF (@ISCHILD ("Retail"))

"Discount %" = .045;

ELSEIF (@ISCHILD ("Distributor")) "Discount %" = .055;

ENDIF

ELSEIF (Units >= 300 AND Units < 500) IF (@ISCHILD ("OEM"))

"Discount %" = .10;

ELSEIF (@ISCHILD ("Retail")) "Discount %" = .07;

ENDIF ELSE

IF (@ISCHILD ("OEM")) "Discount %" = .15;

ELSEIF (@ISCHILD ("Retail")) "Discount %" = .12;

ENDIF ENDIF )

/* Close out Baseline Fix */ ENDFIX

4. Save the script as DiscFcst.csc. Testing the Calculations

1. Run ResetData.bat to clear and reload data. 2. Execute DiscFcst.csc.

3. Open BasicCalcs.xls (in c:\CF\EssCalcs\Calcs) and select the Discount%

worksheet. If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(57)

4. Refresh the worksheet, and compare your results to the following figure, or open BasicCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the Discount% worksheet:

Oracle Internal & Or

(58)

Solutions

Lesson 6

Copying and Clearing Data

Solution 6.1

Creating a Baseline Forecast

Creating the Calculation Script 1. Create the following calculation script:

/* Housekeeping */ SET UPDATECALC OFF; /* Baseline Fix */ FIX (Sep:Dec)

/* Copy Budget data to Forecast scenario */ DATACOPY "Prior Year" TO Forecast;

/* Increase Forecast prices */ FIX ("List Price")

Forecast = Forecast * 1.1; ENDFIX

/* Close out the Baseline Fix */ ENDFIX

2. Verify the syntax of the script. 3. Save the script as BaseFcst.csc. Testing the Calculations

1. Run ResetData.bat to clear and reload data. 2. Execute BaseFcst.csc.

3. Open AdvancedCalcs.xls (in c:\CF\EssCalcs\Calcs) and select the Forecast worksheet. If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(59)

6 Copying and Clearing Data Solutions

4. Refresh the worksheet, and compare your results to the following figure, or open AdvancedCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the Forecast worksheet:

Extra Credit: Forecasting Units Based on Trends 1. Modify BaseFcst.csc to match the following script:

/* Housekeeping */ SET UPDATECALC OFF; /* Baseline Fix */ FIX ("Sep":"Dec")

/* Copy Budget data to Forecast scenario */ DATACOPY "Prior Year" TO "Forecast";

/* Increase Forecast prices */ FIX ("List Price")

"Forecast" = "Forecast" * 1.1; ENDFIX

/* Copy Jan-Apr Actual units to Forecast */ FIX("Jan":"Apr", "Units")

DATACOPY "Current Year" TO "Forecast"; ENDFIX

/* Trend calculation for Sep-Dec Units */ FIX("Forecast")

Oracle Internal & Or

(60)

Solutions "Units" = @TREND("Jan":"Aug",,,,,"Sep":"Dec", LR);

ENDFIX

/* Close out the Baseline Fix */ ENDFIX

2. Verify and save the changes to the script.

3. Repeat the instructions under "Testing the Calculations" to test your calculation, substituting the ForecastEX worksheet for Forecast. Check your results against the following figure, or open AdvancedCalcs_Solutions.xls (in

c:\CF\EssCalcs\Calcs\Complete) and compare your results to the ForecastEX worksheet:

Solution 6.2

Clearing Bad Data

Creating the Calculation Script 1. Create the following calculation script:

/* Housekeeping */ SET UPDATECALC OFF;

/* Clear Roadranger 170 A data for O-Apple */ FIX ("Forecast", "May":"Dec")

CLEARDATA "O-Apple"->"ROADRANGER 170 A"; ENDFIX

Oracle Internal & Or

(61)

6 Copying and Clearing Data Solutions

NOTE: This exercise has many possible solutions.

2. Verify the syntax of the script. 3. Save the script as ClearDat.csc. Testing the Calculations

1. Run ResetData.bat to clear and reload data. 2. Execute ClearDat.csc.

3. Open AdvancedCalcs.xls (in c:\CF\EssCalcs\Calcs) and select the ClearData worksheet. If prompted, enter your user credentials (user name admin and password hyperion).

Oracle Internal & Or

(62)

Solutions 4. Refresh the worksheet, and compare your results to the following figure, or open

AdvancedCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the ClearData worksheet:

NOTE: In a production environment, as a follow-up to clearing data, you would also modify input files or create rejection rules in your rules files to prevent bad data from being reloaded.

Oracle Internal & Or

(63)

7 Developing Advanced Calculations Solutions

Lesson 7

Developing Advanced Calculations

Solution 7.1

Creating a Working Scenario

Creating Substitution Variables

1. In Administration Services Console, in the navigation hierarchy, right-click the

localhost Essbase Server node.

2. Select Edit, and then Variables.

The Substitution Variables dialog box is displayed. 3. In the first row, complete the following tasks:

• In the Application drop-down list, select BigCalc. • In the Database drop-down list, select Sales. • In the Variable text box, enter CurrentMonth. • In the Value text box, enter May.

4. Click Set.

5. Repeat steps 3 and 4 to create the variable PriorMonth with a value of Apr. 6. Click Close.

Copying Data into the Working Scenario 1. Create the following calculation script:

/* Information

Before this calculation is executed, the baseline forecast must be created by running BaseFcst.csc.

*/

/* Housekeeping */ SET UPDATECALC OFF;

/* Copy actual data into the Working scenario */ FIX (Jan:&PriorMonth)

DATACOPY "Current Year" TO "Working"; ENDFIX

Oracle Internal & Or

(64)

Solutions /* Copy forecast data into the Working scenario */

FIX (&CurrentMonth:Dec)

DATACOPY "Forecast" TO "Working"; ENDFIX

2. Verify the script and save it as Working.csc.

1. Run ResetData.bat to clear and reload data. 2. Execute BaseFcst.csc and then Working.csc.

NOTE: Running the reset script clears the forecast data calculated by the BaseFcst script. Thus, you must recalculate the forecast before copying it to the Working scenario.

3. Open AdvancedCalcs.xls (in c:\CF\EssCalcs\Calcs) and select the Working worksheet. If prompted, enter your user credentials (user name admin and password hyperion).

4. Refresh the worksheet, and compare your results to the following figure, or open AdvancedCalcs_Solutions.xls (in c:\CF\EssCalcs\Calcs\Complete) and compare your results to the Working worksheet: