D70048GC10_sg2,Oracle activity guide

(1)

Oracle Essbase 11.1.1: Bootcamp

Volume II • Student Guide

D70048GC10 Edition 1.0 September 2008 D56250 ® ®

Oracle Internal & Or

(2)

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 is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Technical Contributors and Reviewers Shubho Bhattacharya Art Hetherington Saju Philips Sergiy Percherskyy Aneel Shenker Yulia Stolerman Editors Susan Moxley Jennifer Stout Graphic Editors Asha Thampy Satish Bettegowda

Oracle Internal & Or

(3)

Preface

Course Objectives . . . xiii

Course Structure . . . xiii

Course Materials . . . xiv

Student Guide . . . xiv

Activity Guide . . . xiv

Conventions . . . xv

Additional Resources . . . xv

Future Courses . . . xvi

Module 1: Creating Databases

Lesson 1: Essbase Overview

Multidimensional Analysis . . . 1-2 Spreadsheet-Based Analysis . . . 1-3 Multidimensional View of Information . . . 1-5 Data Cubes . . . 1-7 Dimension Hierarchies. . . 1-8 Operations in Multidimensional Data Models. . . 1-9 Sparse and Dense Dimensions . . . .1-11 Oracle’s Enterprise Performance Management System . . . 1-12 Oracle BI Suite Enterprise Edition Plus . . . 1-15 Essbase . . . 1-18 Essbase Architecture . . . 1-19 Essbase Components . . . 1-20 Administration Services Console . . . 1-23 Production Environment Components . . . 1-25 Outlines . . . 1-26 Rules Files . . . 1-27 Calculation Scripts . . . 1-29 Smart View. . . 1-31 Spreadsheet Add-in . . . 1-32 Integration Services . . . 1-33

Oracle Internal & Or

(4)

Lesson 2: Designing Applications and Databases

Block Storage Implementation Process . . . 2-2 Analyzing and Planning Implementations . . . 2-3 Identifying Business Results . . . 2-4 Examining Data Sources . . . 2-5 Analyzing Sample Reports. . . 2-6 Designing Block Storage Outlines . . . 2-7 Creating Applications and Databases . . . 2-9 Creating Block Storage Applications . . . 2-12 Creating Block Storage Databases . . . 2-14 Server File Structure . . . 2-16 Design Recommendations. . . 2-17 Creating Outline Structures . . . 2-19 Creating Dimensions and Members. . . 2-22 Moving Dimensions and Members. . . 2-24 Modifying Member Properties . . . 2-26

Lesson 3: Designing Data Descriptor Dimensions

Data Descriptor Dimensions Overview . . . 3-2 Designing Time Dimensions . . . 3-4 Generic Time Designs . . . 3-6 Fiscal Year Crossover Designs . . . 3-7 Designing Scenario Dimensions . . . 3-9 Tracking Data Sets. . . 3-10 Tracking Processes . . . .3-11 Outline Calculations . . . 3-12 Consolidation Operators . . . 3-13 Shared Members . . . 3-15 Member Formulas . . . 3-17 Designing Accounts Dimensions . . . 3-18 Consolidation Order . . . 3-20 Design Considerations. . . 3-22 Creating Accounts Hierarchies . . . 3-24 Testing Outline Calculations. . . 3-25 Intelligent Calculation. . . 3-27 Setting the Default Database Calculation . . . 3-28

Lesson 4: Optimizing Data Descriptor Dimensions

Oracle Internal & Or

(5)

Dimension Types . . . 4-4 Creating Period-to-Date Totals. . . 4-5 Creating Period-to-Date Calculated Members . . . 4-6 Implementing Dynamic Time Series . . . 4-8 Dynamic Calc Members. . . .4-11 Enhancing Accounts Dimensions . . . 4-13 Time Balance Reporting . . . 4-14 Expense Reporting . . . 4-16 Optimizing Data Storage . . . 4-18 Label Only Members . . . 4-19 Implied Shares . . . 4-21

Module 2: Building Rules Files

Lesson 5: Planning Dimension Designs

Business View Dimensions Overview . . . 5-2 Combining Business Views . . . 5-4 Planning Dimensions with Label Outlines . . . 5-8 Designing Primary Hierarchies . . . 5-10 Designing Secondary Hierarchies . . . .5-11

Lesson 6: Creating Basic Dimension Build Rules Files

Rules Files Overview . . . 6-2 Data Sources . . . 6-3 Dimension Build Rules Files . . . 6-4 Creating Dimension Build Rules Files . . . 6-6 Prepping Data Prep Editor (Steps 1–6) . . . 6-8 Creating Dimensions (Step 7) . . . 6-13 Selecting Dimension Build Method (Step 8). . . 6-15 Defining Field Properties (Step 9) . . . 6-17 Validating Dimension Build Rules Files (Step 10) . . . 6-20 Completing Dimension Build Rules Files (Steps 11 and 12) . . . 6-22 Configuring Dimension Maintenance Settings . . . 6-24 Moving Members . . . 6-25 Modifying Member Properties . . . 6-27 Sorting Members . . . 6-29 Updating Members. . . 6-31

Oracle Internal & Or

(6)

Lesson 7: Creating Advanced Dimension Build Rules Files

Advanced Dimension Build Rules Files Overview . . . 7-2 Creating Shared Members. . . 7-3 Parent-Child Build Method . . . 7-4 Other Methods for Creating Shared Members. . . 7-6 Manipulating Fields . . . 7-8 Arranging Fields. . . 7-9 Altering and Ignoring Fields . . . 7-12 Creating User-Defined Attributes . . . 7-14

Lesson 8: Loading Data

Data Load Overview . . . 8-2 Free-form Data Sources . . . 8-3 Data Sources That Require Rules Files. . . 8-5 Creating Data Load Rules Files . . . 8-6 Prepping Data Prep Editor (Steps 1–6) . . . 8-8 Defining Field Properties (Step 7) . . . .8-11 Referencing Missing Dimensions (Step 8) . . . 8-14 Setting Data Load Values Options (Step 9) . . . 8-16 Validating Data Load Rules Files (Step 10) . . . 8-19 Completing Data Load Rules Files (Steps 11 and 12) . . . 8-20 Selecting and Rejecting Records. . . 8-22 Capturing New Members . . . 8-24

Module 3: Reporting with Smart View

Lesson 9: Getting Started with Smart View

Smart View Overview. . . 9-2 Smart View and Spreadsheet Add-in . . . 9-3 Smart View Architecture . . . 9-4 Smart View Toolbar . . . 9-5 Configuring Data Sources . . . 9-8 Configuring Provider Services . . . 9-9 Connecting to Essbase Data Sources . . . .9-11 Creating Ad Hoc Reports . . . 9-13 Retrieving Data . . . 9-15 Display of Multidimensional Data. . . 9-17

Oracle Internal & Or

(7)

Label Placement Guidelines . . . 9-19 Setting the Point of View . . . 9-21 Selecting Dimension Members . . . 9-22 Filtering Dimension Member Selections . . . 9-24

Lesson 10: Creating Reports with Smart View

Manipulating Multidimensional Data . . . 10-2 Navigating Through Hierarchies . . . 10-4 Retaining and Removing Data Subsets . . . 10-5 Pivoting Data . . . 10-7 Updating Essbase Data . . . 10-9 Adjusting Data Values . . . 10-10 Submitting Data . . . 10-12 Calculating Data. . . 10-13 Integrating Essbase Data with Microsoft Office . . . 10-15 Copying Dynamic Data Points . . . 10-17 Creating Linked Views . . . 10-19 Customizing the User Experience . . . 10-21 Smart Slices Overview. . . 10-22 Creating Smart Slices (1/2) . . . 10-24 Creating Smart Slices (2/2) . . . 10-26 Adding Queries and Subqueries to Reports . . . 10-27 Interactive Report Components . . . 10-29 Creating Reports with Report Designer . . . 10-30

Module 4: Creating Basic Calculations

Lesson 11: Data Storage and Calculation

Calculation Overview . . . .11-2 Outline-Based Calculations . . . .11-4 Script-Based Calculations . . . .11-5 Calculation Script Editor. . . .11-8 Database Calculation Order. . . .11-9 Data Block Fundamentals . . . .11-11 Data Blocks and the Index System . . . .11-12 Data Cells . . . .11-14 Dense Dimensions. . . .11-16 Sparse Dimensions . . . .11-18

Oracle Internal & Or

(8)

Dimension Properties. . . .11-21 Block Statistics . . . .11-22 Data Block Creation . . . .11-24 Data Load . . . .11-25 DATACOPY Command . . . .11-26 Sparse Dimension Consolidation . . . .11-27 Member Formulas . . . .11-28 Database Calculation Process. . . .11-29 Input Data Load . . . .11-30 Dense Dimension Calculation Process: Accounts . . . .11-31 Dense Dimension Calculation Process: Time . . . .11-32 Sparse Dimension Calculation Process: Customer . . . .11-33 Sparse Dimension Calculation Process: Product. . . .11-35

Lesson 12: Creating Calculation Scripts

Calculation Script Organization . . . 12-2 Information Section . . . 12-6 Housekeeping Section . . . 12-8 Baseline Fix Section . . . 12-10 Normalization Section . . . 12-12 Main Rollup Section . . . 12-14 Back Calculation Section . . . 12-15 Returning Correct Calculation Results. . . 12-16 Expected Behavior. . . 12-17 Correcting Calculated Percentages . . . 12-19 Design Considerations for Rates . . . 12-22 Preventing Consolidation of Rates. . . 12-24 Correcting Derived Rates. . . 12-25 Correcting Input Rates . . . 12-26 Troubleshooting CALC DIM Processes . . . 12-28 Viewing Calculation Messages . . . 12-29 Single-Pass Calculation with Incorrect Results . . . 12-31 Multiple-Pass Calculation with Correct Results . . . 12-33

Lesson 13: Controlling the Calculation Process

Top-Down Calculation . . . 13-2 Focusing Calculations with FIX Statements. . . 13-4 Calculating Conditionally with IF Statements . . . 13-7

Oracle Internal & Or

(9)

Boolean Functions . . . 13-9 Syntax Requirements. . . .13-11 Comparing FIX and IF Calculation Processes . . . 13-13 Number of Data Blocks Processed . . . 13-14 Number of Calculation Passes. . . 13-16

Lesson 14: Referencing Members in Calculations

Referencing Members Explicitly. . . 14-2 Referencing Members Dynamically . . . 14-4 Referencing Sets of Members . . . 14-5 Referencing Related Members . . . 14-7 Creating Calculation Variables. . . 14-9 Creating Temporary Variables . . . 14-10 Creating Substitution Variables . . . 14-13

Module 5: Extending Analysis Capabilities

Lesson 15: Creating Attribute Dimensions

Attribute Dimensions Overview . . . 15-2 Attribute Dimension Benefits . . . 15-3 Attribute Calculations Dimension . . . 15-5 Adding Attribute Dimensions to Outlines . . . 15-7 Associating Attribute Dimensions . . . 15-8 Assigning Attributes in Outline Editor. . . 15-10 Design Considerations. . . 15-12 Design Considerations: Database Size . . . 15-14 Design Considerations: Batch Calculation Performance . . . 15-15 Design Considerations: Report Layout . . . 15-16 Design Considerations: Report Performance . . . 15-19 Creating Attribute Dimensions with Rules Files . . . 15-20 Adding Attribute Dimensions with Rules Files . . . 15-22 Assigning Attributes in Rules Files. . . 15-23

Lesson 16: Analyzing Varying Attributes

Varying Attributes: Overview . . . 16-2 Creating Varying Attributes: Overview . . . 16-3 Enabling Outlines for Varying Attributes . . . 16-4

Oracle Internal & Or

(10)

Defining Ranges of Variances . . . 16-7 Viewing Varying Attribute Data. . . 16-9

Lesson 17: Analyzing Text and Dates

Typed Measures: Overview . . . 17-2 Enabling Typed Measures . . . 17-4 Creating Text Measures . . . 17-5 Creating Text Lists . . . 17-6 Populating Text Lists . . . 17-7 Associating Text Lists to Measures . . . 17-9 Creating Date Measures (1/2) . . . 17-10 Creating Date Measures (2/2) . . . .17-11 Viewing Typed Measures. . . 17-12 Calculations Based on Typed Measures . . . 17-13

Module 6: Appendices

Appendix A: Creating Advanced Calculations

Script Development Process . . . A-2 Calculation Test Cycle . . . A-4 Prototype Phase . . . A-6 Pilot Phase. . . A-9 Upper-Level Data Loads . . . A-13 Aggregating Missing Values (1/1) . . . A-14 Aggregating Missing Values (2/2) . . . A-15 Loading to Leaf Nodes. . . A-20 Intelligent Calculation. . . A-23 Data Block Marking: Clean Blocks. . . A-25 Data Block Marking: Dirty Blocks. . . A-28 Usage Considerations . . . A-30 Allocating Data. . . A-32 Calculating Fixed Rate Allocations . . . A-34 Calculating Dynamic Ratio Allocations . . . A-36 Normalization . . . A-38 Partitioning Calculations by Scenario . . . A-40 Developing Normalization Tables. . . A-42 Developing Block Diagrams. . . A-45 Normalizing Rates and Drivers . . . A-47

Oracle Internal & Or

(11)

Copying and Clearing Data . . . A-49 Copying Data . . . A-50 Clearing Data with CLEARDATA . . . A-52 Clearing Data with CLEARBLOCK . . . A-54

Appendix B: Creating Reports with Essbase Spreadsheet Add-in

Essbase Spreadsheet Add-in Overview. . . B-2 Installing the Essbase Menu and Toolbar . . . B-3 Managing Database Connections . . . B-5 Retrieving Data . . . B-7 Representing Multidimensional Data in Two-Dimensional Reports . . . B-9 Label Scanning . . . B-11 Manipulating Multidimensional Data . . . B-13 Navigating Data . . . B-15 Retaining and Removing Data Subsets . . . B-17 Pivoting Data . . . B-19 Managing Worksheet Options . . . B-20 Global Options . . . B-21 Zoom Options . . . B-23 Style Options . . . B-25 Display Options . . . B-27 Preserving Excel Formulas . . . B-29 Replicating Reports with Cascade . . . B-31 Creating Cascade Reports (1/2) . . . B-32 Creating Cascade Reports (2/2) . . . B-34 Selecting Members from the Outline . . . B-35 Defining Selection Rules . . . B-37 Applying Additional Rules . . . B-39 Creating Savable Queries . . . B-41 Query Designer Interface. . . B-42 Creating Basic Queries . . . B-43 Creating Advanced Queries. . . B-44 Filtering Data . . . B-45 Sorting Data . . . B-47 Retrieving with Report Scripts . . . B-48

Oracle Internal & Or

(12)

Oracle Internal & Or

(13)

Overview

This module contains appendices to provide information about additional features of Essbase not covered in this course.

Appendices include:

• Creating Advanced Calculations

• Creating Reports with Essbase Spreadsheet Add-in

Oracle Internal & Or

(14)

Oracle Internal & Or

(15)

Objectives

At the end of this appendix, you should be able to: • Describe the script development process

• Implement methods for working with upper-level data loads • Describe intelligent calculation behavior

• Allocate date with calculation scripts • Describe and plan data normalization • Normalize rates and drivers

• Copy and clear data

Oracle Internal & Or

(16)

Script Development Process

When you draft calculation scripts, avoid developing and testing them on your full

database. Because calculating in a multidimensional environment is a complex process, develop and test calculation scripts incrementally, writing line-by-line and implementing frequent test cycles.

Developing and testing calculation scripts on full, real-world databases presents two fundamental problems:

• Calculation test-cycle times are substantially increased when full databases are tested. Long calculation times inhibit incremental development and testing, which are necessary in the Essbase data structure, where calculation dependencies are complex and multidimensional impacts may not be immediately obvious.

C opyrigh t © 2 008, Oracle . All rig hts reserved.

Script Development Process

Oracle Internal & Or

(17)

• Real-world data is often more difficult to audit than test data. As you develop scripts, you should focus on the technical accuracy of your calculation formulas and not attempt to tie results to prescribed control totals. It is typically easier to trace calculations and dependencies with contrived test data than with real-world data. The calculation script development process has two phases:

1. Prototype Phase: You develop scripts and test them for baseline accuracy.

2. Pilot Phase: You test scripts for performance and capture of exception conditions. T I P

After you finish building your outline, you should separate the calculation script development process from the rules file development process to avoid cross-contamination of the data sets required for the processes.

Oracle Internal & Or

(18)

Calculation Test Cycle

When you test calculation scripts for accuracy, you want to be sure that there is nothing happening behind the scenes that can affect your test results. For example, data

calculated twice with the same script can return two different results, returning a false negative or a false positive for one of your test calculations.

Disabling Intelligent Calculation

To help ensure that Essbase calculates every possible result for your calculation script, you can turn off intelligent calculation. If Essbase has marked a particular data block as calculated, intelligent calculation interferes with subsequent calculations.

It is common practice to disable intelligent calculation with the SET UPDATECALC OFF command in the housekeeping section of a script.

The full impact of intelligent calculation is discussed in more detail later in this lesson.

Calculation Test Cycle

CLEARBLOCK ALL; 1 2 3 4 Ch ange d calcu lation script _Test cycle

Oracle Internal & Or

(19)

Implementing Test Cycles

Every time you change your script and need to test calculation results, follow these steps:

1. Clear all data from the database. 2. Load calculation test data.

3. Execute your calculation script. 4. Audit your calculation results.

If you follow this formalized test cycle every time you test a calculation script, you ensure the following outcomes:

• Results that are based on your input data and the subsequent calculation, not on other factors

• Fastest performance possible, given that results must be based on input data and subsequent calculation

Oracle Internal & Or

(20)

Prototype Phase

The prototype phase of calculation script development is for developing a basic script that correctly calculates dependencies and values for baseline calculations. You want the prototype phase to be as fast and efficient as possible, with low calculation test cycle times. To meet this objective, the following process for creating a prototype calculation script is recommended:

1. Create test input data. 2. Create audit spreadsheets. 3. Implement a draft and test cycle.

T I P

Be sure to check your block statistics before and after running your calculation.

Prototype Phase

Input she et

Aud it shee t

Oracle Internal & Or

(21)

Creating Test Input Data

To quickly check your results, you should create test data that is simple, easy to load, and easy to audit. It is not efficient to create prototype calculation scripts when using full or even partial sets of actual data, because the results require too much time to calculate and audit.

To create a simple data set for prototyping:

1. In Excel, create a spreadsheet workbook, with one or more input worksheets, that

represents a focused set of accounts to be tested against a representative cross-section of sparse dimension members.

2. Enter your test data into the blank input sheets.

Creating Audit Sheets

When drafting and testing calculation scripts, you must be able to audit results easily. To accomplish this objective, set up clearly identified audit and comparison sheets, separate from input sheets, in your testing workbook.

Audit sheets are designed to test whether specific calculations are working. Your audit sheets, when retrieved, must reflect member combinations that show both input data and calculation results. Do not try to audit too many types of calculations on one sheet; you can have several audit sheets, each of which tests a different calculation sequence, such as allocations versus back calculations.

As a best practice, create three audit sections:

• Essbase calculation results: data you retrieve from Essbase T I P

In your input worksheets, use only members to which you load data. For example, if Qtr 1 is a calculated member, do not reference Qtr 1 in your input sheet. If your input levels vary, you can create multiple input spreadsheets in one workbook.

T I P

For easier auditing, use multiples of 100 or 1000 or similar simple numbers for which it is easy to trace a series of calculation dependencies.

Oracle Internal & Or

(22)

• Expected calculation results: static data copied from a data source or calculated using Excel formulas (To protect Excel formulas during retrieval, enable formula preservation options. To protect numeric data during retrieval, place an equals sign (=) before the number. Addition of the equals sign converts the number to an Excel formula.)

• Comparison of Essbase calculation results and expected calculation results: Excel formulas calculating the difference between Essbase results and static data (This section enables you to see quickly and easily where Essbase results vary from expected results.)

You can implement these three sections on one audit worksheet or create an audit worksheet for each section.

Implementing Draft and Test Cycles

After setting up input and audit worksheets, you draft and test your prototype script by using a cyclical test procedure. Rather than writing and testing a calculation script in its entirety, test small sections at a time: Write one line of script and then complete the calculation testing cycle; then write another line and test again. In this way, you can identify problematic or incorrect script lines as you write them.

The following tips can help speed the calculation test cycle:

• To clear your database, create a separate calculation script with only the command CLEARBLOCK ALL. This way, you can complete the entire calculation test cycle from Excel, using the capabilities of Smart View Spreadsheet Add-in, rather than switching back to Administration Services Console.

• Use Smart View to submit data from your input sheets, or use Spreadsheet Add-in to lock and send the data.

• Before you execute your prototype calculation script, retrieve from the sheets that audit the results of your Essbase calculation. With this retrieval, you verify the input data that you loaded.

• After calculation, use the Undo feature in Smart View or the Flashback feature in Spreadsheet Add-in to revert to the input data in your spreadsheet.

N O T E

Do not use this method with larger data sets, as performance is better when data is cleared using Administration Services Console.

Oracle Internal & Or

(23)

Pilot Phase

When you develop a prototype script, you confirm that calculations and dependencies work correctly on test data. During the pilot phase of testing, you test the prototype script against real-world data and make necessary changes.

You typically make two types of adjustments to the prototype script during pilot testing: • Performance optimization

• Exception trapping

Performance Optimization

During prototype testing, the database is typically too small for you to accurately assess performance impacts. During the pilot phase, you refine your prototype script to address performance issues.

Pilot Phase

Oracle Internal & Or

(24)

The following tips can help you optimize the performance of your calculation scripts: • Minimize the number of calculation passes in your script. Two calculation passes on

a given set of data blocks require twice as much time as one pass, so use the pilot phase to ensure that all calculations that can be grouped are performed in a single pass.

• For each calculation pass, minimize the number of blocks that Essbase calculates. If Essbase does not need to calculate every existing data block, use FIX statements to focus calculation on the necessary blocks.

• Revise sparse and dense storage settings to reflect your final calculation requirements.

• Revise dynamic calculation (versus batch calculation) approaches to reflect your final calculation requirements.

• Determine whether your calculation returns correct results with intelligent calculation turned on, thus minimizing the number of data blocks required for calculation. The full ramifications of intelligent calculation are discussed later in this appendix.

Exception Trapping

The prototype calculation script typically addresses known mainstream or baseline calculation requirements. Testing your prototype against real data can result in errors that represent exceptions to the baseline and that require further refinement of the script. The following are examples of exception trapping:

• Adjusting allocation formulas because actual data is input at levels other than the levels that were assumed during the prototype phase

• Adding conditional logic to address the handling of zero values in calculations • Adding DATACOPY commands to create data blocks necessary for calculation

Pilot Phase Testing

Testing in the pilot phase follows the calculation, testing-cycle steps of the prototype phase but uses real data instead of test data. Using real data modifies the calculation, test-cycle methodology, as it was described for the prototype phase. Unlike test data, real data tends to reside in exterior data sources that are typically loaded with rules files. Also, the volume of real data tends to be significantly higher than the volume of test data, a difference that increases calculation times.

Oracle Internal & Or

(25)

Additionally, because one of the primary objectives of this phase is to improve calculation performance, you need to mimic your production environment as much as possible and follow the sequence of production operations that you ordinarily follow. For example, upload data from the G/L, make outline modifications, and then run your calculation. Although Excel should still be used in this phase to audit calculation results, you can automate the rest of your calculation test cycle by using a MaxL script. The following example clears the database, loads data files, and then runs a calculation script. The entire process is spooled to a log file, which shows database statistics before and after calculation.

/****************************************************************/ /* This MaxL script clears and then loads data to the */ /* Bigcorp Sales database for testing calculation scripts. */

/* Variables: */

/* $1 = user name */

/* $2 = password */

/****************************************************************/ /* ****************************************/

/* CREATE PROCESS LOG AND LOGIN TO SERVER */ /* ****************************************/ spool on to 'c:\temp\output.txt';

login $1 $2 on 'localhost';

/* ***********************************************************/ /* Activate and then clear data from Bigcorp Sales database */ /* ***********************************************************/ alter system load application 'Bigcorp';

alter application 'Bigcorp' load database 'Sales'; alter database 'Bigcorp'.'Sales' reset;

/* ***************************************/ /* Load data files */ /* ***************************************/ import database 'Bigcorp'.'Sales' data

from server text data_file 'Actual' using server rules_file 'Loadcorp' on error write to 'c:\temp\acterr.txt'; import database 'Bigcorp'.'Sales' data

Oracle Internal & Or

(26)

from server text data_file 'Forecast' using server rules_file 'Loadcorp' on error write to 'c:\temp\forerr.txt';

/**********************************************/ /* Get pre-calculation database statistics*/ /**********************************************/

query database 'Bigcorp'.'Sales' get dbstats data_block; /*****************************/

/* Run calculation scripts*/ /*****************************/

execute calculation 'Bigcorp'.'Sales'.'CalcAll'; /***********************************************/ /* Get post-calculation database statistics*/ /***********************************************/

query database 'Bigcorp'.'Sales' get dbstats data_block; /* ********************************/

/* Close out process log and exit */ /* ********************************/ spool off;

exit;

Oracle Internal & Or

(27)

Upper-Level Data Loads

Block storage databases enable you to load data to any level, not only level 0. However, if you load data to upper levels, you must implement certain calculation or design

schemes. Such schemes protect the data when you consolidate.

Essbase provides three methods of handling upper-level input data (listed from least preferred to most preferred):

• Protecting upper-level inputs by not aggregating missing values • Loading to special leaf nodes

• Allocating upper-level input data to level 0 before consolidating (For a comprehensive discussion of allocating data, see “Allocating Data.”)

Upper-Level Data Loads

Three methods:

• Do not aggregate missing values

• Load to leaf nodes

• Allocate data before consolidation

Oracle Internal & Or

(28)

Aggregating Missing Values (1/1)

The fundamental problem with loading data to upper-level members is that the normal consolidation process can overwrite the data. For example, in the Bigcorp Sales database, you load a value of 100 to Qtr 1 and no data to Jan, Feb, or Mar. However, when you calculate the Year Tot dimension, Essbase receives an instruction to calculate Qtr 1 as Jan+Feb+Mar, and the calculated sum overwrites your input value. Whether the calculation is performed depends on your settings.

Aggregating Missing Values (1/2)

Aggregate missing values ON:

• Overwrites upper-level data loads

• Improves calculation performance

• Is the recommended default setting

Before calculation After calculation

Oracle Internal & Or

(29)

Aggregating Missing Values (2/2)

One of the ways to handle upper-level data loads during the calculation process is to instruct Essbase not to aggregate missing values during hierarchy calculations. This instruction protects upper-level data that, in the hierarchy, has no data below it.

Aggregating Missing Values (2/2)

Aggregate missing values OFF:

• Protects upper-level data loads

• Slows calculation performance

• Is the default setting for new databases

Before calculation After calculation

Oracle Internal & Or

(30)

Controlling Aggregate Missing Values Behavior

There are two ways to control aggregate missing values behavior:

• Default database setting: New databases by default do not aggregate missing values. You can change the default setting on the General tab of the Database Properties dialog box.

• Calculation script: You can use the command SET AGGMISSG ON|OFF to control the aggregation of missing values on a script-by-script basis. This command

overrides the default database setting and is typically included in the housekeeping section of the script.

Performance Implications

When you aggregate missing values during hierarchy consolidations (SET AGGMISSG ON), calculation performance is significantly enhanced for the following reasons:

• Dense calculations within a data block: Cell aggregations that can be performed multiple ways are summed only once. In the following example, Essbase can achieve the same result in the intersection of Cost of Sales and Qtr 1 by using the account formula (Labor+Material+Overhead) or the time period formula (Jan+Feb+Mar). Rather than calculating "Cost of Sales"->"Qtr 1" during the Accounts dimension calculation and then again during the Year Tot dimension calculation, Essbase calculates the cell only once, using the consolidation path of the dimension that it calculates last.

T I P

As a best practice, you should set aggregate missing values on as a database default and turn it off selectively in your calculation scripts.

Jan Feb Mar Qtr 1

Labor 100 100 100 300

Material 50 50 50 150

Overhead 75 75 75 225

Cost of Sales 225 225 225 675

Oracle Internal & Or

(31)

• Sparse calculations between data blocks: Blocks that can be aggregated multiple ways are calculated only once, using an algorithm that attempts to calculate using the fewest possible blocks. In the following example, data block 12 (the intersection of Thunderball and OEM) can be calculated as the sum of blocks 4+8 or the sum of blocks 9+10+11. Rather than using both formulas and thus calculating block 12 twice, Essbase uses only the 4+8 formula because it requires fewer blocks.

Although not aggregating missing values (SET AGGMISSG OFF) is required to protect upper-level data, not aggregating missing values during hierarchy consolidations can have a serious impact on calculation performance, especially in the following two situations:

• When you have a low ratio of calculated data blocks to input data blocks • When you load many data values at parent levels on sparse dimensions; for

example, in the Bigcorp Sales database, if you load many data values into Retail in a sparse Customer dimension

In these situations, the performance overhead required for not aggregating missing values is between 10% and 30%. If calculation performance is critical, reconsider how you configure the database or how you load data, to avoid the need to protect upper-level input data.

Expected Versus Correct Considerations

When you load data to upper levels in your hierarchy, additional issues of expected versus correct calculation behavior must be considered. These issues are difficult to understand without stepping through the calculations. The following example illustrates the problem.

O-Dell O-HP O-IBM OEM

Thunderball 270 A 1 2 3 4

Thunderball 540 A 5 6 7 8

Thunderball 9 10 11 12

Oracle Internal & Or

(32)

The Other CGS account data for Bigcorp Sales is collected in transactional systems without regard for customer or product. Thus, the data is available for input only at the level that represents the total for all products (Family Total) and all customers (Channel Total). The following input sheet loads Other CGS data to the "Current Year"->"Family Total"->"Channel Total" data block.

However, all other account drivers are input to level 0 blocks. The following input sheet loads other account data (units, list price, and so on) to the "Current Year"->"Lightbolt 365 A"->"O-IBM" and "Current Year"->"Thunderball 540 S"->"O-IBM" data blocks.

You calculate the following script, making sure to include instructions to protect the data loaded in the upper-level block.

/* Housekeeping */ SET UPDATECALC OFF; SET AGGMISSG OFF; /* The Main Rollup */

CALC DIM (Accounts, "Year Tot", Customer, Product);

Oracle Internal & Or

(33)

/* The Back Calculation */ (

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

"Overhead Rate" = Overhead / "Direct Labor"; )

However, when you audit your results, you notice that, although totals for Net Sales, Cost of Sales and Other CGS are correct, totals for Gross Margin are not correct.

The incorrect totals for Gross Margin are due to calculation order: The Accounts dimension calculates first, creating Gross Margin totals in both level 0 bocks. When Customer and Product are calculated, Essbase aggregates the data that exists in the level 0 blocks. This aggregation creates incorrect totals for Gross Margin in the "Current Year"->"Family Total"->"Channel Total" block. The Other CGS data is protected during the aggregation by the SET AGGMISSG OFF command.

To correct the incorrect totals, you must calculate Gross Margin in the "Current

Year"->"Family Total"->"Channel Total" block in a separate, additional calculation pass, such as a back calculation, that takes place after the main rollup. This problem can occur frequently when you work with level inputs. If you have a large number of multi-level inputs that force you to recalculate data at upper multi-levels, consider a method other than protecting level loads by not aggregating missing values for handling upper-level input data.

Oracle Internal & Or

(34)

Loading to Leaf Nodes

Another method of handling upper-level input values is to avoid loading data to upper levels. The leaf-node loading method involves the following steps:

1. Create a level 0 member (leaf node) as a child of the upper-level member to which you want to load data. For example, if you want to load Other CGS accounts to Family Total and Channel Total, create children for Family Total and Channel Total, as shown in the example on the slide.

2. Load upper-level inputs to the leaf nodes instead of to the upper-level members. 3. Consolidate as usual.

When you use this method, you are not required to protect upper-level data. Thus, you can aggregate missing values, which improves performance. Also, because all input is level 0, you are not required to correct totals at upper levels, as you are with multi-level inputs.

Loading to Leaf Nodes

• Avoids upper-level input issues

• Requires additional members in the outline

L evel 0 placeh olde r for Fami ly Total input data

L evel 0 placeh older for Cha nne l Total inp ut data

Oracle Internal & Or

(35)

For example, instead of loading Other CGS data to the "Current Year"->"Family Total"->"Channel Total" block, as in the preceding example, you load to the "Current Year"->"No Product"->"No Customer" block, using the following input sheet.

You also load other input account data to specific customer and product blocks, as in the preceding example. The following input sheet loads to the "Current Year"->"Lightbolt 365 A"->"O-IBM" and "Current Year"->"Thunderball 540 S"->"O-IBM" data blocks.

You run the following calculation script. Because you no longer need to protect upper-level input data, you turn the aggregate missing values setting on for better performance. /* Housekeeping */

SET UPDATECALC OFF; SET AGGMISSG ON; /* The Main Rollup */

CALC DIM (Accounts, "Year Tot", Customer, Product); /* The Back Calculation */

Oracle Internal & Or

(36)

(

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

"Overhead Rate" = Overhead / "Direct Labor"; )

Your audit sheet shows the following correct results:

In this example, all data in the "Current Year"->"Family Total"->"Channel Total" data block is aggregated from the three dependent blocks, so no recalculation is required after consolidation.

Oracle Internal & Or

(37)

Intelligent Calculation

A primary goal in calculation script development is optimization (elimination of extra passes through the database index). To optimize calculation, you can use FIX and IF statements, to focus calculations, or you can use an option called intelligent calculation. When you perform a full database calculation, Essbase marks which blocks have been calculated. If you then load a subset of data, you can calculate only the changed data blocks and their ancestors. This selective calculation process is intelligent calculation. By default, intelligent calculation is turned on. You can change the default setting in the essbase.cfg file or on a script-by-script basis with the SET UPDATECALC OFF

command. For information about the essbase.cfg file, see the online Technical Reference, “Essbase.CFG Configuration Settings.”

Intelligent Calculation

Only marked data blocks are recalculated:

9 8 8 9 9 9 9 9 9 9 9 9 8

Oracle Internal & Or

(38)

Intelligent calculation is based on data-block marking. When intelligent calculation is active, during normal processes, within the index file, blocks are marked clean or dirty.

• Clean blocks: Blocks that do not require calculation • Dirty blocks: Blocks that require calculation

When intelligent calculation is active, during calculation, Essbase looks for only dirty data blocks.

N O T E

Intelligent calculation marks data blocks, not data cells, clean or dirty. For

example, assume that, in a database that includes a dense time dimension, data is loaded once per month. When you load data to a month, intelligent calculation marks the affected blocks as dirty. Therefore, all time periods within the affected blocks are recalculated.

Oracle Internal & Or

(39)

Data Block Marking: Clean Blocks

The basis for understanding intelligent calculation is understanding under what

conditions blocks are marked clean and dirty. If any of the following conditions is true, intelligent calculation marks blocks as clean:

• The script includes CALC ALL or CALC DIM on all dimensions.

• The script includes SET CLEARUPDATESTATUS AFTER, a command that applies intelligent calculation to the calculation process.

• The script includes CLEARUPDATESTATUS ONLY, a command that directs intelligent calculation to mark blocks without performing a database calculation.

Data Block Marking: Clean Blocks

• Automatic intelligent calculation

• Forced intelligent calculation

• Forced data block marking

9 9 9 9 9 9 9 9 9 9 9 9 9

Oracle Internal & Or

(40)

Automatic Intelligent Calculation

When you execute a calculation script with a CALC ALL statement or with a CALC DIM statement that includes all dimensions, the intelligent calculation process performs the following actions:

• All previously existing input data blocks are calculated and marked clean.

• All data blocks created by the consolidation process are calculated and marked clean.

• All previously existing dirty blocks are calculated and marked clean. The following script examples automatically enable intelligent calculation:

CALC ALL;

CALC DIM (Accounts, "Year Tot", Scenario, Product, Customer); Even when intelligent calculation is enabled, for CALC DIM statements that do not include all dimensions, Essbase does not use an intelligent calculation process. Rather, Essbase calculates all relevant data blocks, regardless of clean or dirty status, and all data blocks retain their status, dirty or clean.

The following script examples do not enable intelligent calculation: CALC DIM (Accounts, "Year Tot", Scenario, Product); CALC DIM (Accounts, "Year Tot");

AGG (Scenario, Product, Customer)

Forced Intelligent Calculation

SET CLEARUPDATESTATUS AFTER is a calculation command that engages intelligent calculation for any calculation script, regardless of construction. Typically, you use this command where you cannot meet the conditions for a CALC DIM on all dimensions. When you execute a calculation script that includes the SET CLEARUPDATESTATUS AFTER command, data blocks that are marked clean are not calculated, and data blocks that are marked dirty are calculated and marked clean.

Oracle Internal & Or

(41)

The following example demonstrates the use of the SET CLEARUPDATESTATUS AFTER command:

SET CLEARUPDATESTATUS AFTER FIX (@IDESCENDANTS("Qtr 1"))

CALC DIM (Accounts); ENDFIX

Forced Block Marking Without Calculating

SET CLEARUPDATESTATUS ONLY is a calculation script command that instructs Essbase to perform data-block marking but not to perform script calculations. Typically, you use this command immediately after a script segment in which intelligent calculation is disabled.

The following example shows a two-part script:

• The first segment, in which intelligent calculation is disabled, calculates data blocks correctly but does not mark dirty blocks as clean.

• In the second segment, in which data-block marking is enabled, the SET

CLEARUPDATESTATUS ONLY command instructs Essbase to mark as clean all blocks that were previously marked dirty.

/* Turn off intelligent calculation */ SET UPDATECALC OFF;

/* Back Calculation */

"Gross Margin %" = "Gross Margin" / "Net Sales"; "List Price" = "Gross Sales" / Units;

/* Enable data block marking */ SET CLEARUPDATESTATUS ONLY;

"Gross Margin %" = "Gross Margin" / "Net Sales"; "List Price" = "Gross Sales" / Units;

SET CLEARUPDATESTATUS ONLY affects only the database index and does not require the referenced data blocks to be moved into memory. Under most circumstances, therefore, elements of a calculation script following SET CLEARUPDATESTATUS ONLY execute very quickly.

Oracle Internal & Or

(42)

Data Block Marking: Dirty Blocks

If any of the following conditions is true, intelligent calculation marks blocks as dirty, thereby making the blocks eligible for calculation the next time intelligent calculation is engaged:

• Block creation during data input • Data modification

• Creation or modification of descendant blocks • Database restructure

• Block creation during data input

• Data modification

• Creation or modification of descendant blocks

• Database restructure

Data Block Marking: Dirty Blocks

8 8 8 8 8 8 8 8 8 8 8 8 8

Oracle Internal & Or

(43)

Block Creation During Data Input

Blocks created from a data input process are marked dirty as they are created. Blocks can be created during batch data load processes, by submitting data from Smart View for Office, by locking and sending data from Spreadsheet Add-in, or by using other direct input options.

Data Modification

Blocks that include modified data cells are marked dirty. For example, if, within a block, a line manager updates units, the block should be marked dirty because revenues, costs, and margin are now incorrect and need recalculating.

Creation or Modification of Descendant Blocks

Intelligent calculation is efficient because it calculates only affected blocks. The

ancestors of a dirty block need to be calculated because they are dependent on values of the modified descendant. Therefore, all ancestors of input and modified blocks are marked dirty.

Database Restructure

Outline changes prompt Essbase to mark blocks dirty. Which blocks are marked dirty is determined by whether the outline changes involve dense or sparse dimensions:

• Moving, adding, or deleting members of dense dimensions causes all blocks to be marked dirty.

• Moving, adding, or deleting members of sparse dimensions causes only the affected blocks and their ancestors to be marked dirty.

Oracle Internal & Or

(44)

Usage Considerations

Occasionally, clean data blocks are marked dirty. In such cases, calculation efficiency suffers. A more serious problem, however, is a false positive condition, in which dirty blocks are marked clean. In the case of such false positives, data integrity can suffer.

False Positives

False positive conditions can arise in various ways:

• Calculating only a subset of a block. Essbase marks at the block level, not the cell level, so a calculation that is executed on a subset of cells can cause a false positive condition. Only a few cells are calculated, but the block is marked clean, although uncalculated cells remain.

Usage Considerations

Maintain clean and dirty status of blocks:

• False negatives affect calculation efficiency

• False positives affect data integrity

9 8 8 9 9 9 9 9 9 9 9 9 9 False positive

Oracle Internal & Or

(45)

• Calculating a back calculation after calculating all dimensions. A false positive can prevent the required second calculation pass, which corrects upper-level rates and percentages. The false positive condition occurs if the CALC DIM portion of a script engages intelligent calculation, which marks all blocks clean during the CALC DIM process. When Essbase processes the back calculation portion of the script, no data blocks are calculated because all blocks are marked clean.

• Using a FIX statement. Ancestors of a dirty block are not marked dirty until the descendant dirty block is calculated. A false positive can result if the descendant dirty block is calculated within a FIX statement that does not include the dirty ancestor block. After calculation, Essbase marks the descendant block clean, and the ancestor block remains marked clean, although it should be marked dirty.

• Following SET CLEARUPDATESTATUS ONLY with unrelated calculations. You should follow SET CLEARUPDATESTATUS ONLY with a repetition of the section of the script for which you want to force data-block marking. A false positive can occur if SET CLEARUPDATESTATUS ONLY touches blocks that are otherwise dirty and that are not correctly calculated.

Usage Guidelines

Intelligent calculation can provide significant performance benefits in certain situations but requires vigilant maintenance of the clean and dirty status of data blocks to avoid skipping the wrong blocks on calculation. Be very careful when using intelligent

calculation, and be sure to review the detailed discussion of intelligent calculation in the Database Administrator’s Guide.

The intelligent calculation function is most productively used in interactive or iterative situations in which small, incremental changes are made to a database and in which it is not necessary to recalculate the entire database. For example, you can use intelligent calculation in the following situations.

• During the month-end close periods, allocation rates and adjusting entries may be updated multiple times. You use intelligent calculation to view update results without recalculating the entire database.

• In budget or forecasting applications, typically, many users update units and drivers on a regular basis. In such applications, for users to analyze the impact of their updates, a short calculation time is imperative.

Oracle Internal & Or

(46)

Allocating Data

As mentioned earlier in this appendix, a method of handling upper-level input data is to allocate or push down the data that you load to dependent level 0 data blocks, so you can consolidate normally. However, allocations are not restricted to distributing upper-level input data. You can use the methods described here to distribute totals loaded to level 0 data blocks across other level 0 data blocks. When you allocate data, you typically include the following steps in your calculation process:

1. Load input data to upper-level data blocks or designated level 0 data blocks. 2. Allocate data to dependent level 0 data blocks, using various allocation methods.

Allocating Data

Upper-level input allocation:

1 15 % 45 % 40 % 10 0% 2 3 15% 45% 40%

Oracle Internal & Or

(47)

3. Consolidate data using a CALC ALL or CALC DIM statement (For upper-level input data, the statement overwrites the original input).

T I P

If allocating a level 0 input, offset the original input amount by the sum of all allocated data, to avoid doubling the input data upon consolidation.

Oracle Internal & Or

(48)

Calculating Fixed Rate Allocations

Some allocations are based on fixed percentages; for example, expenses loaded to a Corp HQ entity that are allocated to multiple departments based on a fixed percentage for each department. Calculate fixed rate allocations with the following member formula: Allocation destination account = location of input total * location of

fixed rate;

In these cases, load the percentage to a single location in the database and base your allocation on this fixed point of reference, as in the following example.

/* Information Assumptions:

1) Expenses are loaded to generic expense accounts, and then allocated to department-specific accounts based on fixed percentages.

2) Allocation percentages are loaded to the Corp HQ entity in the FinancePct, SalesPct, HRPct, and MktgPct accounts, respectively. */

Calculating Fixed Rate Allocations

15% 45% 10 % 30 %

VAR FinanceAlloc = .15 VAR SalesAlloc = .45 VAR HRAlloc = .10 VAR MktgAlloc = .30

Oracle Internal & Or

(49)

/* Allocate IT Expense */

"IT Exp Finance" = "IT Exp" * "Corp HQ"->FinancePct; "IT Exp Sales" = "IT Exp" * "Corp HQ"->SalesPct; "IT Exp HR" = "IT Exp" * "Corp HQ"->HRPct;

"IT Exp Mktg" = "IT Exp" * "Corp HQ"->MktgPct; /* Allocate Administrative Expense */

"Admin Exp Finance" = "Admin Exp" * "Corp HQ"->FinancePct; "Admin Exp Sales" = "Admin Exp" * "Corp HQ"->SalesPct; "Admin Exp HR" = "Admin Exp" * "Corp HQ"->HRPct;

"Admin Exp Mktg" = "Admin Exp" * "Corp HQ"->MktgPct;

Alternatively, you can define temporary variables in your calculation script to store percentages temporarily, as in the following example.

/* Information Assumptions:

1) Expenses are loaded to generic expense accounts and then allocated to department-specific accounts based on fixed percentages.

*/

/* Housekeeping */

/* Define fixed allocation percentages for each department */ VAR FinanceAlloc = .15;

VAR SalesAlloc = .45; VAR HRAlloc = .10; VAR MktgAlloc = .30; /* Allocate IT Expense */

"IT Exp Finance" = "IT Exp" * FinanceAlloc; "IT Exp Sales" = "IT Exp" * SalesAlloc; "IT Exp HR" = "IT Exp" * HRAlloc;

"IT Exp Mktg" = "IT Exp" * MktgAlloc; /* Allocate Administrative Expense */

"Admin Exp Finance" = "Admin Exp" * FinanceAlloc; "Admin Exp Sales" = "Admin Exp" * SalesAlloc; "Admin Exp HR" = "Admin Exp" * HRAlloc;

"Admin Exp Mktg" = "Admin Exp" * MktgAlloc;

Oracle Internal & Or

(50)

Calculating Dynamic Ratio Allocations

In dynamic ratio allocations, the allocation ratio is calculated based on other data available in the database, such as percent of total headcount, percent of total units, or percent of total sales. This approach obliges you to include a calculation of the

appropriate ratio in addition to your allocation instructions, as in the following formula: Allocation destination account = location of input total * (value of

ratio base in current data block / total value of ratio base);

Calculating Dynamic Ratio Allocations

"O-IBM"->Units / Customer->Units "O-Dell"->Units / Customer->Units "O-Acer"->Units / Customer->Units Allocation ra tio s:

O-IBM O-Dell O- Acer

Custo mer

Oracle Internal & Or

(51)

In Bigcorp Sales, Material Variances is loaded to the total for all products and all

customers. To allocate this total to individual, product-customer data blocks based on a percentage of total units, use the following formula:

‘Material Variances" = "Material Variances"->"Family Total"->"Channel Total" * ( Units / Units->"Family Total"->"Channel Total");

T I P

Depending on when you execute the allocation, you may have to calculate the total ratio base before calculating the allocation. For example, if your allocation is based on a percent of total headcount, you must calculate total headcount before you allocate.

Oracle Internal & Or

(52)

Normalization

In interactive applications, allocations are sometimes standalone processes. However, when allocations are incorporated into a database consolidation, they typically fall into the normalization section of the calculation script architecture.

In relational databases, the process of normalization eliminates redundant data from tables to improve performance and data scalability. Because Essbase does not store data for block storage databases in tables, block storage normalization is a very different process.

Normalization

Allocate upper-level input to level 0 descendants.

Allocate level 0 input to other level 0 blocks.

2

1

Oracle Internal & Or

(53)

In block storage databases, normalization is required when you load data to data blocks other than the blocks that are the intended, final target for the data, as described in the following examples:

• You load total miscellaneous expenses to a block that represents the total of all customers and all products. Prior to consolidating the database, you allocate total miscellaneous expenses to each individual, product-customer block (level 0) based on the percentage of total units sold in each level 0 block.

• You load total IT expenses to a level 0 block for a corporate headquarters entity. Prior to consolidating the database, you allocate total IT expenses to each office location (level 0) based on the percentage of total headcount for each office.

The common thread in the examples is that the calculations are required to happen prior to consolidation of the database. Normalization is a general umbrella for any data

manipulation that needs to occur after data input and before main database consolidation.

Oracle Internal & Or

(54)

Partitioning Calculations by Scenario

For most block storage databases, within the accounts dimension, scenarios are the primary driver of modeling and calculation requirements. Data from scenario to scenario typically differs with respect to the form of input. Because you have only one outline model for the accounts dimension, you may need to calculate input data differently from one scenario to the next:

• Inputs for budget and forecast data are typically units, rates, and other drivers, and many dollar amounts are derived though calculation.

• Budget data often contains much product detail (for example, standard cost by product) and overhead detail (for example, salaries by employee) but little customer detail (for example, only top ten customers budgeted).

Partitioning Calculations by Scenario

One accounts model, multiple calculation requirements:

• Actual data:

– Input dollars and units – Derive rates

• Budget data:

– Input units and rates – Derive dollars

• Forecast data:

– Input units and rates – Derive dollars

Oracle Internal & Or

(55)

• Inputs for forecast data are similar to inputs for budget data, but the level of detail is different, depending on what kind of forecasting you need. For example, in a sales forecasting application, forecast data can have very detailed customer information. • Inputs for actual data are typically dollars and units, and rates are derived through

calculation. In contrast to budget data, actual data often contains more revenue detail by customer and less overhead detail.

Because of the type and level of inputs for each scenario, different scenarios often require different data load procedures and different calculation scripts.

Oracle Internal & Or

(56)

Developing Normalization Tables

Before creating calculation scripts, you should document the data input and calculation requirements for each scenario with a normalization table. The table helps you keep track of the assumptions that you make about the input data and provides a clear roadmap for building normalization calculations into your scripts.

Normalization table development has the following steps:

1. Analyze data for a scenario to determine how many dimensions require normalization.

2. In a spreadsheet program, create a normalization table for the scenario, including sections for each dimension that requires normalization.

3. Complete the table, based on assumptions about input data.

Developing Normalization Tables

1. Determine how many dimensions require normalization.

2. Create a normalization template.

3. Complete the template based on input data.

Oracle Internal & Or

(57)

Determining What Dimensions Require Normalization

Before building a normalization table, determine how many dimensions require

normalization in each scenario. To accomplish this task, examine your input data. The following example represents budget input data for Bigcorp Sales.

In this example, a value of 73,000 is loaded to the intersection Budget->"Material Variances"->Jan->"Family Total"->"Channel Total". Examine the full cell address, one dimension at a time, to determine whether data must move along the dimension.

Based on this analysis, Bigcorp Sales budget data requires normalization in two dimensions: Product and Customer.

Creating Normalization Tables

Normalization tables are simply documentation templates that you create in a

spreadsheet program. They are not connected to an Essbase database, so they do not require Spreadsheet Add-in.

Member Name Movement Required?

Budget No. All 73,000 remains in the budget scenario.

Material Variances No. All 73,000 remains in the Material Variances account. Jan No. All 73,000 remains in the Jan time period.

Family Total Yes. A percentage of the 73,000 must be allocated to each product. Channel Total Yes. A percentage of the 73,000 must be allocated to each customer.

Oracle Internal & Or

(58)

A normalization table lists all input accounts in the row axis. For each dimension that you normalize, include the following four columns:

A blank normalization table template looks like the following example.

Completing Normalization Tables

After you create the template, enter the relevant information in the table, documenting as much as possible. A normalization table for the Other CGS accounts of the previous budget input example looks like the example on the slide.

Column Name Description

Data Type How data for the current account is created (direct input, formula calculation, consolidation, and so on)

Input Level Generation or level at which data is loaded (for the dimension being normalized)

Push to Level Generation or level to which input data must be moved (for the dimension being normalized)

Methodology How to move data in the dimension being normalized (Common methods include copying data and allocating data.)

Oracle Internal & Or

(59)

Developing Block Diagrams

Another way to visualize the normalization process is to create a diagram of data blocks to see the movement required from the input data and the relationships between input blocks and normalization targets.

A block diagram is a two-dimensional representation of data blocks, where rows

represent generations of dimension1 and columns represent generations of dimension2. The resulting matrix represents all combinations between the generations of dimension1 and dimension2.

N O T E

In databases with a large number of sparse dimensions, creating a complete block diagram can be too complicated and should not be used as a development technique.

Developing Block Diagrams

Oracle Internal & Or

(60)

Although normalizations can have interim steps, the final destination, or target block, for normalized data is a level 0 block. To build a block diagram, start with a single target block in the lower left corner. This block is an arbitrary selection to enable creation of a representative slice of your database.

After creating your diagram, analyze your input data and mark on the diagram the blocks to which data is loaded. Marking helps you visualize the movement of data required during normalization.

Oracle Internal & Or

(61)

Normalizing Rates and Drivers

Rates and other drivers, unlike total dollar amounts, do not require distribution based on an allocation. Instead, you use member formulas to copy these drivers from their input location to their final destination.

The block diagram on the slide shows the input locations for budget drivers in Bigcorp Sales; all of the drivers require normalization to the target (level 0) block.

You can use explicit or dynamic references in your formula. Which type of reference you choose depends on the nature of your input data.

• Single member input: When you load rates to one fixed member in the dimension being normalized, normalize using explicit references. In the example on the slide, the discount percentage for each customer is loaded to the total for all products. To calculate Discount % in the target blocks, use the following formula:

"Discount %" = "Discount %"->"Family Total";

Normalizing Rates and Drivers

Oracle Internal & Or

(62)

• Multiple member input: When you load rates to multiple members in the dimension being normalized, normalize using dynamic references. In the example on the slide, the list price for each product is loaded to customer channels; there are different prices for OEM, Retail, and Distributor customers. To calculate List Price in the target blocks, use the following formula:

"List Price" = @PARENTVAL(Customer, "List Price");

Oracle Internal & Or

(63)

Copying and Clearing Data

As part of your normalization process, you may be required to copy or clear data sets. You can place copy and clear commands in the housekeeping section or the

normalization section of your calculation script. The following table describes the available data manipulation commands:

Command Description

DATACOPY mbrName1 TO

mbrName2

For copying data sets from one member to another CLEARBLOCK ALL | UPPER |

NONINPUT | DYNAMIC

For clearing previous input or upper-level data or stored dynamic calculations

CLEARDATA mbrName For clearing specific members or member combinations