Hyperion Essbase Bootcamp Exercises

(1)

(2)

(3)

Case Study 1: Bigcorp

0

Exercises

Lesson 1:

Exercise 1.1 Exercise 1.2

Essbase Overview

Starting Services . . . 12

Accessing Administration Services Console . . . 13

Lesson 2:

Designing Applications and Databases

Creating Block Storage Applications and Databases. . . 14

Creating Dimensions and Members. . . 14

Lesson 3:

Exercise 3.1 Exercise 3.2 Exercise 3.3 Exercise 3.4 Exercise 3.5 Exercise 3.6

Designing Data Descriptor Dimensions

Creating Generic Time Dimensions . . . 16

Tracking Data Sets. . . 16

Creating Outline Calculations. . . 17

Creating Accounts Hierarchies. . . 18

Testing Accounts Calculations . . . 20

Creating Balance Sheet Accounts . . . 21

Lesson 4:

Exercise 4.1 Exercise 4.2 Exercise 4.3 Exercise 4.4 Exercise 4.5

Optimizing Data Descriptor Dimensions

Creating Member Aliases. . . 23

Implementing Dynamic Time Series Members. . . 24

Correcting Percentage and Balance Accounts . . . 25

Adding Variance Reporting . . . 26

Optimizing Data Storage Settings . . . 28



.

Overview . . . 1

Bigcorp Overview. . . 1

(4)

Lesson 5:

Exercise 5.1

Developing Dimension Designs

Designing the Product and Customer Dimensions . . . 29

Lesson 6:

Creating Basic Dimension Build Rules Files

Building the Family Total Rollup . . . 30

Optional: Adding Products to the Family Total Hierarchy . . . 31

Lesson 7:

Exercise 7.1 Exercise 7.2 Exercise 7.3 Exercise 7.4

Creating Advanced Dimension Build Rules Files

Creating Secondary Hierarchies with Parent-Child Build. . . 33

Building the Customer Dimension . . . 34

Optional: Adding UDAs to Customers . . . 36

Optional: Creating the Region Total Dimension . . . 38

Lesson 8:

Exercise 8.1 Exercise 8.2 Exercise 8.3

Loading Data

Loading Actual Data. . . 40

Loading Forecast and Actual Data. . . 40

Optional: Loading Transactional Units . . . 41

Lesson 9:

Exercise 9.1 Exercise 9.2 Exercise 9.3 Exercise 9.4 Exercise 9.5

Getting Started with Smart View

Connecting to Data Sources . . . 43

Starting Ad Hoc Analysis . . . 43

Customizing the Point of View . . . 43

Creating Ad Hoc Reports . . . 45

Creating a Free-Form Report. . . 45

Lesson 10: Creating Reports with Smart View

Exercise 10.1 Exercise 10.2 Exercise 10.3 Exercise 10.4 Updating Essbase Data . . . 47

Viewing Dynamic Data Points . . . 48

Creating Smart Slices . . . 49

Creating a Custom Report . . . 50

Lesson 11: Data Storage and Calculation

Exercise 11.1 Calculating the Sales Database . . . 53

Extra Credit: Recording Detailed Statistics . . . 55

Essbase 11.1.2 Bootcamp

iv

(5)

Lesson 12: Creating Calculation Scripts

Exercise 12.1 Organizing Calculation Scripts . . . 56 Exercise 12.2 Correcting Rates and Percentages . . . 56

Lesson 13: Controlling the Calculation Process

Lesson 14: Referencing Members in Calculations

Exercise 14.1 Creating Mix Calculations . . . 62 Exercise 14.2 Calculating Time Variances . . . 64

Lesson 15: Developing and Testing Complex Calculation Scripts

Exercise 15.1 Loading Data to Upper-Level Members . . . 67

Lesson 16: Normalizing Data

Exercise 16.1 Allocating Current Year Data . . . 69 Exercise 16.2 Normalizing Budget Data . . . 69

Lesson 17: Creating Attribute Dimensions

Exercise 17.1 Viewing Attribute Data . . . 72 Essbase 11.1.2 Bootcamp

v

.

Exercise 13.1 Focusing Calculations with FIX . . . 59 Exercise 13.2 Calculating Conditionally with IF . . . 60 Extra Credit . . . 61

(6)

1

Bigcorp

Overview

This section contains a description of the business and analytic

requirements of the Bigcorp corporation. In the exercises section of this manual, you build a database for Bigcorp. As you build the database, refer to the information provided in this section.

Bigcorp Overview

The Bigcorp corporation typifies the modern business experience: initial success followed by competitive challenge.

Company History

In 1978, a young Silicon Valley engineer founded a company that manufactured hard drives. A small operation at first, the company

partnered exclusively with local computer manufacturers. As the market for personal computers grew during the 1980s, Bigcorp branched out,

distributing and selling its hard drives throughout the United States and the world.

Bigcorp Today

Today that Silicon Valley startup is a multimillion-dollar market leader in the hardware technology sector. Bigcorp, still headquartered in the Silicon Valley, is owned by Hoch Enterprises International, a business

conglomerate. Hoch Enterprises operates Bigcorp within the United States. Global Technology Hard Drives, Inc., a sister company to Bigcorp, operates Bigcorp outside the United States.



(7)

Current Business Challenges

Over the years, as technology has decreased Bigcorp’s manufacturing costs, the proliferation of hard-drive manufacturers has reduced Bigcorp’s market share. With the general availability of universal serial bus (USB) flash drives, Bigcorp executives saw the need to sharpen the company’s competitive edge.

The management team at Bigcorp hired you to build a block storage database that provides the information that it requires and that uses data extracts from its existing systems.

Participant Information

The following table lists participant groups at Bigcorp and their respective data output requirements:

Participant Group Finance

Requirements

• Monthly product development, reported by individual product and grouped by product families

• Details of company cost structures

• Current and prior year actual and budget data Manufacturing

Sales

• Monthly product unit sales, grouped by hard-drive configuration • For sales, current year and forecast data

• Monthly sales revenues by product and by customer • Comparison of new and old product lines

• Customer totals by sales channel and by region • Current-year actual, budget, and forecast data

Data Determinations

The design team identified three main data sources and decided how to handle the data from each source:

• Current and prior year actual sales data is stored in an Oracle data warehouse that IT created to normalize the data. IT can set up an SQL query that extracts this data to flat files on a regular basis, at which point rules files can be used to load the data to the Sales database.

2

.

(8)

team decides to grant the budgeting team security permissions to send its data directly from Excel to the Sales database.

• Forecast data is created by the sales force in Excel spreadsheets. The design team decides to grant the sales force security permissions to send its data directly from Excel to the Sales database.

Sample Bigcorp Reports

The following reports were gathered from the Finance and Sales teams, to give you an idea of the type of analysis that the teams want to perform in Essbase. You can view the reports in a workbook called BCSampleReports.xls (located in

c:\CF\Bootcamp\SSReports). Operating Statement

Essbase 11.1.2 Bootcamp 3

(9)

Variance Report Case Study: Bigcorp

(10)

Essbase 11.1.2 Bootcamp Case Study: Bigcorp

5

(11)

Customer by Product Category

(12)

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, because many exercises build upon other exercises.

Before beginning these exercises, be sure to read the Bigcorp case study.



(13)

Lesson 1

Exercise 1.1

Essbase Overview

Starting Services

In this exercise, you start the necessary services for the class learning activities. The message “Are you sure you want to START all Oracle/Hyperion Services?” is displayed.

2. Click Yes.

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

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 4. 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 12

Essbase 11.1.2 Bootcamp

.

1. From the Managing Services folder on the Windows Desktop, double-click StartServices.

(14)

- 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.

Essbase Overview

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. Essbase 11.1.2 Bootcamp

13

(15)

Lesson 2

Exercise 2.1

Designing Applications and Databases

Creating Block Storage Applications and Databases

You begin to build a database based on the Bigcorp case study.

1. Create an application with the specifications described in the following table:

Option Application type Essbase server Application name Specification Block storage localhost Bigcorp

2. Create a database with the specifications described in the following table:

Option Essbase server Application Database name Database type Specification localhost Bigcorp Sales Normal

3. In Windows Explorer, review the file system folder structure for Bigcorp. 4. Create a custom view for Bigcorp.

Exercise 2.2

Creating Dimensions and Members

Dimensions are based on the Bigcorp case study.

Add dimensions to the Sales outline and add members to dimensions: 1. Add the following dimensions to Bigcorp Sales:

• Year Tot • Accounts • Scenario 14 Essbase 11.1.2 Bootcamp

.

Create a block storage application and database for Bigcorp and review the hierarchy structure of the new database:

(16)

2. Add the members described in the following table to the Year Tot dimension: Parent Year Tot Qtr 1 Qtr 1 Qtr 1 Child Qtr 1 Jan Feb Mar

The following figure shows the Year Tot hierarchy: TIP: Remember to save your outline changes. Essbase 11.1.2 Bootcamp

15

(17)

Lesson 3

Exercise 3.1

Designing Data Descriptor Dimensions

Creating Generic Time Dimensions

After evaluating the Bigcorp design document, you decide to use a generic time design for the Year Tot dimension.

1. Add quarters Qtr 1 through Qtr 4 to generation 2.

2. Add months Jan through Dec to the appropriate quarters. The following figure shows the Year Tot hierarchy:

TIP: Remember to save your outline changes.

Exercise 3.2

Tracking Data Sets

Bigcorp uses the scenarios described in the Bigcorp case study to track data sets. Add the scenarios and scenario variances described in the following table, which Bigcorp requires for analysis of sales data:

16

.

(18)

Designing Data Descriptor Dimensions Parent Scenario Scenario Scenario Scenario Scenario Scenario Variances Scenario Variances Scenario Variances Child Current Year Prior Year Budget Forecast Scenario Variances Act Vs Bud Act Vs Fcst Bud Vs Fcst

Compare your outline to the following figure: TIP: Remember to save your outline changes.

Exercise 3.3

Creating Outline Calculations

Bigcorp analysts require scenario variance calculations in many of their reports. You decide to add variance calculations to the Sales outline so that variances are centrally defined and accessible to users.

Add calculations to Bigcorp scenario variances and modify consolidation operators of the Scenario dimension to control calculation flow:

1. Change the consolidation operator for all Scenario dimension members to ~ (Excluded from Consolidation).

2. Calculate Act Vs Bud using shared members. Essbase 11.1.2 Bootcamp

17

(19)

3. Calculate Act Vs Fcst and Bud Vs Fcst using member formulas. The following figure shows the completed Scenario dimension:

Exercise 3.4

Creating Accounts Hierarchies

The Accounts dimension within the Sales outline is based on the Gross Margin Analysis report in the Bigcorp design document. You can view the report in Corpaccts.xls (in C:\CF\Bootcamp\Outline).

Using only hierarchy relationships for calculation, define a hierarchy for the Accounts dimension based on the calculations described in the following table:

Member Name Gross Sales Discounts Net Sales Direct Labor Material Overhead Cost of Sales Other CGS Gross Margin Calculation Units * List Price

Gross Sales * Discount % Gross Sales - Discounts Labor/Unit * Units Matl/Unit * Units

Direct Labor * Overhead Rate Direct Labor + Material + Overhead

Material Variances + Labor Variances + Overhead Variances + Obsolete Charges + Inventory Adjustments Net Sales - Cost of Sales - Other CGS

HINT: The following accounts are input (noncalculated) accounts: • Units • List Price • Discount % 18 Essbase 11.1.2 Bootcamp

.

(20)

• Labor/Unit • Matl/Unit • Overhead Rate • Material Variances • Labor Variances • Obsolete Charges • Inventory Adjustments

Designing Data Descriptor Dimensions

Add a ratio calculation called Gross Margin % with a member formula that calculates Gross Margin as a percentage of Net Sales.

The following figure shows the complete exercise: TIP: Remember to save your outline changes. Essbase 11.1.2 Bootcamp

19

.

(21)

Exercise 3.5

Testing Accounts Calculations

After creating a calculation-intensive dimension, it is a best practice to test the calculations.

Turn off intelligent calculation, input test data, and review your calculation results: 1. Modify the default database calculation to look like the following example: 2. In Smart View, perform the following tasks:

a. In the Smart View panel, connect to Shared Connections (user name/password = admin/hyperion).

b. Add Bigcorp Sales to your private connections. 3. Test accounts calculations:

a. Run AccountsInput.bat (in c:\CF\Bootcamp\Scripts) to load input data. TIP: If data does not load completely, check accountserror.txt (in

c:\CF\Bootcamp\Scripts\logs) to determine the cause of the problem. b. Open Corpaccts.xls (in c:\CF\Bootcamp\Outline) and select the AuditData worksheet.

c. Set localhost_bigcorp_sales as the active worksheet connection, and calculate, retrieve, and verify data.

20

.

SET UPDATECALC OFF; CALC ALL;

(22)

Designing Data Descriptor Dimensions

d. Compare your calculation results to the data on the Gross Margin Analysis worksheet.

Exercise 3.6

Creating Balance Sheet Accounts

Bigcorp analysts need to see units in inventory and accounts receivable balances. Add a balance sheet section to the Accounts dimension:

1. Add a sibling called Balance Sheet to Gross Margin %.

2. Set the consolidation property of Balance Sheet to ~ (Excluded from Consolidation). Essbase 11.1.2 Bootcamp

21

(23)

3. Add the following children to Balance Sheet: Account Inventory Accounts Receivable Consolidation Property ~ ~

The following figure shows the completed balance sheet hierarchy: TIP: Remember to save your outline changes.

22

(24)

Lesson 4

Exercise 4.1

Optimizing Data Descriptor Dimensions

Creating Member Aliases

For reporting purposes, Bigcorp requires longer month and quarter names.

1. Add Year Tot aliases to quarters and months, using the guidelines described in the following table.

Member Type Quarters Months

Alias Guidelines

Quarter 1, Quarter 2, Quarter 3, Quarter 4 Full month name (January, February, and so on)

Year Tot dimension members are displayed with aliases in the outline.

2. Add the aliases described in the following table to Scenario dimension members:

Member Current Year Prior Year Alias CY Actual PY Actual Essbase 11.1.2 Bootcamp 23

.

(25)

TIP: Remember to save your outline changes.

Exercise 4.2

Implementing Dynamic Time Series Members

Enable Dynamic Time Series members for the Year Tot dimension: 1. Tag Year Tot with the Time dimension tag.

2. Enable the following Dynamic Time Series members: • Y-T-D, with the alias YTD

• Q-T-D, with the alias QTD

TIP: Add aliases in a separate step after Dynamic Time Series members are added to the outline.

Y-T-D and Q-T-D are displayed next to Year Tot as active Dynamic Time Series members.

24

.

Bigcorp analysts require year-to-date and quarter-to-date reporting. After evaluating period-to-date design options, you decide to implement a Dynamic Time Series solution.

(26)

TIP: Remember to save your outline changes. 3. Test your calculations:

a. Run OptimizeInput.bat (in c:\CF\Bootcamp\Scripts) to clear the database and load input data.

b. In Smart View, set the Bigcorp Sales connection to display member names instead of aliases.

c. In Optimize.xls (c:\CF\Bootcamp\Outline), select the QTD&YTD worksheet and set localhost_bigcorp_sales as the active connection.

d. Calculate, refresh, and verify data.

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

OptimizeComplete.xls (located in c:\CF\Bootcamp\Outline\Complete) and compare your results to the QTD&YTD worksheet.

Exercise 4.3

Correcting Percentage and Balance Accounts

After testing your calculations, you discover that Qtr 1 calculations are incorrect for Gross Margin %, Inventory, and Accounts Receivable.

Change member property settings to return correct results: 1. Modify the Sales outline as follows:

a. Change Gross Margin % to Dynamic Calc.

b. Tag the Accounts dimension with the accounts dimension type. c. Tag Inventory and Accounts Receivable as time balance last. TIP: Remember to save your outline changes.

Essbase 11.1.2 Bootcamp 25

(27)

2. Test your calculations:

b. In Optimize.xls (c:\CF\Bootcamp\Outline), select the TimeBalance worksheet and set localhost_bigcorp_sales as the active connection.

c. Calculate, refresh, and verify data.

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

OptimizeComplete.xls (located in c:\CF\Bootcamp\Outline\Complete) and compare your results to the TimeBalance worksheet.

Exercise 4.4

Adding Variance Reporting

Natural account balances are not retained in Bigcorp source data. Rather, the sign on revenues from the general ledger is reversed before data is loaded into the database. Thus, expense reporting tags and @VAR functions are required for accurate variance reporting.

Change member properties and formulas to enable correct variance reporting: 1. Tag the following accounts as expenses for variance reporting:

• Discounts • Discount % • Cost of Sales • Direct Labor 26 Essbase 11.1.2 Bootcamp

.

(28)

• Labor/Unit • Material • Matl/Unit • Overhead • Overhead Rate • Material Variances • Labor Variances • Overhead Variances • Obsolete Charges • Inventory Adjustments

2. In the Scenario dimension, add variance formulas:

a. Replace the current variance calculations with member formulas that use the @VAR function.

b. Tag scenario variances as Dynamic Calc. TIP: Remember to save your outline changes. 3. Test your calculations:

b. In Optimize.xls (c:\CF\Bootcamp\Outline), select the Variance worksheet and set localhost_bigcorp_sales as the active connection.

c. Calculate, refresh, and verify data. Essbase 11.1.2 Bootcamp

27

.

(29)

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

OptimizeComplete.xls (located in c:\CF\Bootcamp\Outline\Complete) and compare your results to the Variance worksheet.

Exercise 4.5

Optimizing Data Storage Settings

The final test of your data descriptor dimensions is to ensure that data is visible at all levels of the hierarchy. Some stored members within the Sales outline should not be stored.

Identify members that do not require storage and optimize the database by changing data storage types:

1. Test the default top-level database calculation:

b. Run the default database calculation.

c. In Optimize.xls, from the TopLevel worksheet, initiate ad hoc analysis on the Bigcorp Sales connection.

2. In the Sales outline, identify members that should not be stored and set the storage property to Label Only. After saving the outline, repeat step 1 to verify data at the top level.

28

(30)

Developing Dimension Designs

Lesson 5

Exercise 5.1

Developing Dimension Designs

Designing the Product and Customer Dimensions

This exercise can be performed as a group activity or on your own. The instructions are for a group activity.

1. Divide into three teams to represent the Bigcorp finance, manufacturing, and sales groups.

2. Select, for each team, one of the following report workbooks (in c:\CF\Bootcamp\Outline):

• FinanceRprts.xls • MfgRpts.xls • SalesRpts.xls

3. For Product and Customer, use a blank sheet of paper to design outline plans that are based on the report workbooks.

4. As a class, discuss which elements are common to multiple plans, identify primary and secondary hierarchies, and identify shared members.

.

(31)

Lesson 6

Creating Basic Dimension Build Rules

Files

Building the Family Total Rollup

Exercise 6.1

Create a rules file to build the Family Total product hierarchy:

1. From Data Prep Editor, as your sample data source, open FamGen.txt (in c:\CF\Bootcamp\Loadrule).

2. Set the data source properties to skip the header row.

3. Set the view options to hide raw data and view dimension build fields. 4. Associate the rules file with the Sales outline.

5. Define the Product dimension in the rules file with the following properties: • Data storage of label only

• Configuration of sparse

6. Select the dimension build method for Product.

7. Based on the contents of the sample data source, define the dimension build field properties.

8. Validate and save the rules file.

9. Update the Sales outline, and specify the following options: • Data file: FamGen.txt

30

.

The Bigcorp IT team has provided you with text file extracts from its data warehouse for your initial dimension builds.

(32)

• Rules file: FamGen.rul

The Product dimension is displayed in the Sales outline, with five generations. TIP: Remember to save your outline changes.

Exercise 6.2

Optional: Adding Products to the Family Total

Hierarchy

When the Sales database moves into production, you plan to implement product dimension updates directly from the IT data warehouse.

Create a rules file that adds new products to the Family Total hierarchy by accessing an Oracle data warehouse:

1. Build the rules file, keeping the following points in mind:

• The data source name for the IT data warehouse is Bigcorp Warehouse. • When you define the data source, you do not have to provide inputs for server, application, database, or dictionary path.

• You must retrieve all records from the Products table.

• Your Oracle database user name/password is bigcorp/hyperion. Essbase 11.1.2 Bootcamp

31

(33)

• After retrieving records from the data warehouse into your rules file, continue with step 2 of the 12-step rules file creation process described in Lesson 6 of the Student Guide.

• The data source contains both new and preexisting products. You must implement the appropriate maintenance options in your dimension build settings.

• The new products in the data source are THUNDERBALL 730 A, THUNDERBALL 730 S, MAVRIDER 730 A, and MAVRIDER 730 S.

2. Complete the rules file, validate it, and save it as FamLev. 3. Update the Sales outline, and specify the following options: • Load type: SQL

• Rules file: FamLev.rul

TIP: Remember to save your outline changes. 32

(34)

Creating Advanced Dimension Build Rules Files

Lesson 7

Creating Advanced Dimension Build

Rules Files

Creating Secondary Hierarchies with Parent-Child

Build

Exercise 7.1

The Bigcorp Marketing and Sales groups have organized information for building the secondary hierarchies of the Product dimension into two comma-delimited extract files that are configured for parent-child building.

Create one rules file to load multiple data sources: 1. Create a rules file.

• Follow the process for creating dimension build rules files.

• Use either ConfigPC.txt or LinePC.txt as the sample data source.

• Ensure that your dimension build settings for Product enable the creation of shared members.

• Save the rules file as ParChild.

2. Update the Sales outline, and specify the following options: • Data sources: ConfigPC.txt and LinePC.txt

(35)

Exercises

• Rules file: ParChild.rul

Config Total and Line Total hierarchies are added to the Product dimension. TIP: Remember to save your outline changes.

Exercise 7.2

Building the Customer Dimension

Bigcorp Sales analysts require customers to be grouped by the OEM, Retail, and

Distributor channels. However, certain OEM customers, such as Apple and Dell, also sell Bigcorp hard drives through their retail outlets. Rather than making channels a separate dimension, you create unique customer names to meet Bigcorp’s analysis requirements. 34

(36)

Creating Advanced Dimension Build Rules Files

Create a rules file for building the Customer dimension:

1. Start a rules file for the Customer dimension, and use CusChan.txt (in c:\CF\Bootcamp\Loadrule) as the sample data source.

• Do not include account manager information in the Customer dimension. • Do not include customer class information in this dimension build.

2. Manipulate the data source fields into the correct order for your planned build method.

3. Create unique customer names with prefixes. • O- (for OEM customers)

• R- (for Retail customers) • D- (for Distributor customers)

4. Modify field properties to ignore extra fields.

5. Make text replacements to conform member names to the Customer dimension plan. • Channels must include OEM, Retail, and Distributor.

• Channels roll up to Channel Total.

6. Add the Customer dimension to the rules file, and specify the following information: • Data storage: label only

• Configuration: sparse

7. Complete the rules file, following steps 8 through 11 of the rule file creation procedure introduced in Lesson 6. Save the rules file as Channels.

8. Update the Sales outline, and specify the following information: • Data source: CusChan.txt

.

• Review the Customer dimension plan that you created in Lesson 5 to ensure that you create the entire dimension.

(37)

Exercises

• Rules file: Channels.rul.

The Customer dimension is added to the outline. TIP: Remember to save your outline changes.

Exercise 7.3

Optional: Adding UDAs to Customers

The Finance group requires customers to be grouped into three classes—Class A, Class L, and Class R—for creating conditional forecasting calculations. These groupings are strictly for calculation purposes, so you create the appropriate UDA for each

customer. 36

(38)

Creating Advanced Dimension Build Rules Files Modify Channels.rul to load customer class UDAs: 1. Open Channels.rul, and save it as CustUDA.rul.

TIP: Open CusChan.txt as the sample data source for the rules file 2. Modify the rules file:

a. Set Field3 to be recognized during dimension builds (this field was previously ignored).

b. Move Field3 to the last field on the right.

c. Modify customer classes so that they are displayed as Class A, Class L, and Class R.

d. Set the dimension build field properties for Field6.

3. Modify the dimension build settings to enable UDA changes.

4. Load customer UDAs to the Sales outline, and specify the following options: • Data source: CusChan.txt

(39)

Exercises

• Rules file: CustUDA.rul

Customer class UDAs are added to the Customer dimension. TIP: Remember to save your outline changes.

Exercise 7.4

Optional: Creating the Region Total Dimension

The Sales group wants to group customers by region in a few reports, and several account managers want to see a crosstab report of Sales channels by region. Create an attribute dimension for regions:

1. Follow the dimension build rules file process to create a rules file. Use CusReg.txt (located in c:\CF\Bootcamp\Analysis) as the sample data source.

2. Define the Region Total dimension as an attribute of Customer. 38

(40)

3. Complete the rules file:

Creating Advanced Dimension Build Rules Files a. Select your dimension build setting for Customer

b. Ensure that your dimension build field properties enable the creation of attribute members

c. Validate and save the file as Regions.rul. • Data source: CusReg.txt

• Rules file: Regions.rul

The Region Total attribute dimension is added to the outline, and attributes are displayed for customers.

.

(41)

Lesson 8

Exercise 8.1

Loading Data

Loading Actual Data

Create a rules file that is based on a sample file from the data warehouse:

1. Start the rules file: Follow the data rules file process to create a rules file that is based on Actual.txt (in c:\CF\Bootcamp\Loadrule) as the sample data source.

2. Set the rules file to ignore extra fields. 3. Define the data load field properties.

4. Validate and save the rules file as Loadcorp.

5. Load data to Sales, and specify the following options: • Data source: Actual.txt

• Rules file: Loadcorp.rul

6. Save the data load choices to c:\My Documents as Actual.xml.

7. In CheckData.xls (in C:\CF\Bootcamp\LoadRule) verify that data was loaded: a. Select the Actual worksheet and set localhost_Bigcorp_Sales as the active connection.

b. Refresh the worksheet.

Exercise 8.2

Loading Forecast and Actual Data

Bigcorp forecast data is collected in a custom Oracle database that is built and

maintained by a senior analyst. The analyst gave you an extract that matches the extract from the IT data warehouse for actual data. Thus, you can use one rules file for both data sources.

Create a saved data load profile that loads both forecast and actual data:

1. From the Data Load dialog box, open the saved data load profile Actual.xml. 2. Modify the data load profile:

40

.

The Bigcorp IT group stores current and prior year actual sales data in an Oracle data warehouse that it created to normalize the data. The IT group set up a SQL query that extracts the data to flat files on a regular basis.

(42)

a. Add the data source Forecast.txt with the rules file Loadcorp.rul. b. Save the new data load profile as ActFcst.xml.

3. Run the saved data load profile.

NOTE: You receive a warning message when loading Actual.txt because no data values are modified by reloading the file.

4. In CheckData.xls, verify that data was loaded:

a. Select the Forecast worksheet, and set localhost_Bigcorp_Sales as the active connection.

b. Refresh the worksheet.

Exercise 8.3

Optional: Loading Transactional Units

The Bigcorp Finance group performs its month-end close on its general ledger by the tenth of each month, at which point the actual data is loaded from a transactional ledger system into the Bigcorp data warehouse. The Sales group wants to see preclose units data from the transactional system as of the last day of each month, before the data is transferred and normalized in the data warehouse.

Create a rules file that is based on daily data in an extract file from the Bigcorp ledger system:

1. Follow the data rules file process to create a rules file that is based on MayAct.txt as the sample data source.

2. Modify dates to correspond to the correct month in the outline. 3. Define data load field properties.

4. Reference missing dimensions in the rules file header. 5. Set data load values to add to existing values.

6. Validate and save the rules file as Flash.

7. Load data to Sales, and specify the following options: • Data source: MayAct.txt

• Rules file: Flash.rul

8. In CheckData.xls, verify that data was loaded: Essbase 11.1.2 Bootcamp

41

(43)

a. Select the Flash worksheet, and set localhost_Bigcorp_Sales as the active connection.

Exercises

Lesson 8 Loading Data Essbase 11.1.2 Bootcamp

(44)

Lesson 9

Exercise 9.1

Getting Started with Smart View

Connecting to Data Sources

Before using Smart View, you must connect to a list of data sources in the Smart View panel.

2. In a new Excel workbook, connect to Shared Connections and display the list of data sources for the Essbase - loaclhost server.

Exercise 9.2

Starting Ad Hoc Analysis

Ad hoc analysis provides a high degree of user interactivity with the data.

On a blank worksheet, display Year Tot and Accounts to familiarize yourself with ad hoc reporting:

1. Start ad hoc analysis on the Bigcorp Sales database. 2. Expand Year Tot and Accounts.

Exercise 9.3

Customizing the Point of View

To make the report more useful, you customize the POV with multiple products and customers.

.

(45)

In the report created in the preceding exercise, complete the following tasks: 1. Customize the POV as described in the following table:

Dimension Scenario Product Members Current Year • Family Total • Lightbolt • Thunderball • Value • Roadranger • Mavrider • Firebrand Customer • Channel Total • OEM • Retail • Distributor • Performance

2. On the POV toolbar, select Current Year, LIGHTBOLT, and OEM, and refresh the worksheet.

OEM data for LIGHTBOLT is displayed.

TIP: Select various combinations of product and customer, and refresh the data for each combination to ensure that the POV is working.

44

(46)

Exercise 9.4

Creating Ad Hoc Reports

The Bigcorp Finance team wants you to create an ad hoc report that shows the following information: Report Area Rows Columns Point of view Dimension Members Family Total and its children Accounts, Year Tot, Scenario OEM, Retail, Distributor

1. On the Ad Hoc worksheet in SmartView.xls, create an ad hoc report: a. Use pivot and zoom operations to create the report layout.

b. Use keep-only and remove-only operations to display members. 2. Customize the POV as described in the following table:

Dimension Year Tot Accounts Scenario Members

Level 0 time periods Gross Margin, Net Sales, Gross Sales, Units Prior Year, Current Year

Exercise 9.5

Creating a Free-Form Report

1. On the FreeForm worksheet in SmartView.xls, create a free-form report: a. Page headers: Gross Sales, Jan.

b. Row headers: O-IBM, O-Apple, O-Dell, O-Acer c. Column headers: PY Actual, CY Actual.

(47)

2. Set localhost_bigcorp_sales as the active connection and refresh the worksheet. Data is displayed in the grid.

46

(48)

Lesson 10 Creating Reports with Smart View

Exercise 10.1

Updating Essbase Data

Bigcorp analysts want to use Excel worksheets to submit updated forecast data to Essbase.

1. In SmartView.xls, set localhost_Bigcorp_Sales as the active connection and set the Smart View display options to disable Excel formatting.

2. Adjust and submit August data as follows: • Increase Units by 10%.

• Increase List Price by $5.

• Decrease Discount %, Labor/Unit, Matl/Unit, and Overhead Rate by 3%. 3. Execute the CalcBC calculation script.

.

Test an input worksheet by updating data, submitting data to Essbase, and executing a calculation script:

(49)

4. Compare your results to the following figure or open SmartView_Solutions.xls (in c:\CF\Bootcamp\SSReports\Complete) and compare your results to the AdjustData worksheet.

Exercise 10.2

Viewing Dynamic Data Points

Bigcorp finance analysts want to display sales data in their quarterly reports, which are created in Microsoft Word. They also want to quickly view charts of the data.

Examine a prebuilt Word document with embedded dynamic data points and create a linked view in Visual Explorer:

1. Open VarianceReport.doc (in C:\CF\Bootcamp\SSReports), and perform the following tasks:

a. In the Smart View panel, under Private Connections, connect to the Sales database.

48

(50)

b. Refresh the document.

c. In the Cost Variances table, view the connection and POV information for a cell. 2. Based on a cell in the Cost Variances table, create a linked view in Visual Explorer, using the side-by-side bars view.

Exercise 10.3

Creating Smart Slices

Bigcorp sales representatives for OEM customers require a database query showing only current year OEM sales.

(51)

Create a Smart Slice to meet their requirements:

1. In SmartView.xls, from the Sandbox worksheet, create a smart slice for the Sales database, using the query layout described in the following table:

Query Layout Columns Rows POV Members Qtr 1, Qtr 2 Current Year, DESCENDANTS([Product]), Gross Sales

OEM and children

2. Set the following query options:

Option

Missing Label Text Indentation Numeric Format Value

0 Totals

thousands separator = yes

3. Save the smart slice as OEM_CurYr.

4. Create an ad hoc report from the OEM_CurYr smart slice. The smart slice query is displayed in the worksheet.

Exercise 10.4

Creating a Custom Report

OEM sales managers need a custom dashboard showing OEM sales from a variety of perspectives.

50

(52)

Create a dashboard using Report Designer:

1. In SmartView.xls, from the Dashboard worksheet, insert the OEM_CurYr smart slice into Report Designer.

2. Add the following report components to OEM_CurYr: • Function grid

• Slider (select product lines for slider values)

TIP: You can enter design mode to move or resize charts and sliders. Essbase 11.1.2 Bootcamp

51

.

(53)

3. Refresh the worksheet, and set the slider to THUNDERBALL. Thunderball data is displayed in the function grid and chart. 52

(54)

Lesson 11 Data Storage and Calculation

Exercise 11.1

Calculating the Sales Database

Construct a full database calculation script, testing results before and after calculation: 1. Reset the sales database by running the

C:\CF\Bootcamp\Scripts\ResetBigcorp.bat script.

2. On a separate sheet of paper, record the following precalculation database statistics for Sales:

• Number of existing blocks • Existing level 0 blocks • Existing upper-level blocks • Block density

• Percentage of maximum blocks existing

3. Create a calculation script that turns off intelligent calculation and calculates all dimensions in the Sales database. Save the script as Anatomy.csc.

.

During multidimensional calculation, input data is transformed into useful information. A thorough understanding of the process of multidimensional calculation is necessary before you create more complex calculations.

(55)

4. In Anatomy.xls (in C:\CF\Bootcamp\CalcIt\Anatomy), test the calculations by using the This Is a Block worksheet.

NOTE: Remember to enable Excel formatting before refreshing the worksheet. Calculated data is displayed in the worksheet.

NOTE: Unlike previous worksheets, this workbook was saved after connecting to the Sales database, and thus contains stored connection information

(including the last used connection and the POV). Therefore, you are not required to set the active database connection before you refresh. 54

(56)

5. On a separate piece of paper, record the following postcalculation database statistics for Sales, and compare them to the precalculation statistics:

• Number of existing blocks • Existing level 0 blocks • Existing upper-level blocks

• Percentage of maximum blocks existing

Extra Credit: Recording Detailed Statistics

Implement an iterative test of Anatomy.csc to calculate the Sales database one dimension at a time. Record the changes to the following database statistics after data load and after each dimension calculation:

• Number of existing blocks • Existing level 0 blocks • Existing upper-level blocks • Block density

• Percentage of maximum blocks existing Essbase 11.1.2 Bootcamp

55

.

(57)

Lesson 12 Creating Calculation Scripts

Exercise 12.1

Organizing Calculation Scripts

You want to document your scripts thoroughly for the Bigcorp database administrators, who will eventually maintain the Essbase system.

Exercise 12.2

Correcting Rates and Percentages

Although Anatomy.csc consolidates all Sales database dimensions, some rates and percentages are not aggregated correctly.

Modify Anatomy.csc to add a back calculation, correcting input rates and percentages: 1. Add a block of back-calculation formulas to derive the correct rates for the following accounts: • List Price • Discount % • Labor/Unit • Matl/Unit • Overhead Rate

NOTE: Enclose the block of formulas in parentheses to enforce a second calculation pass.

2. Save Anatomy.csc and test your changes: a. Run ResetBigcorp.bat.

b. Calculate Anatomy.csc. 56

.

In Anatomy.csc, add a brief information section with your name and the date, a housekeeping section header, and a main rollup section header. Save the changes.

(58)

c. In Anatomy.xls, refresh the This is a Block worksheet and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in

c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Level0Block worksheet.

(59)

d. Change the POV to Channel Total, Family Total, and Current Year and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in

c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the UpperLevelBlock worksheet.

58

(60)

Controlling the Calculation Process

Lesson 13 Controlling the Calculation Process

Exercise 13.1

Focusing Calculations with FIX

In the interest of performance, the Bigcorp management team wants you to modify Anatomy.csc so that it calculates only Current Year data for the months in Qtr 1. 1. Modify the Anatomy.csc script as follows, and save it as CYCalc.csc:

a. Focus the script to calculate only for Current Year and months in Qtr 1.

b. Make any modifications necessary to the rest of the script as a result of adding the fix statement.

c. Use commenting sections to identify the start and end of the fix. 2. Test your calculations:

a. Run ResetBigcorp.bat. b. Calculate CYCalc.csc.

c. Refresh the Fixit worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in

c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Fix It worksheet.

.

(61)

Exercise 13.2

Calculating Conditionally with IF

The Sales group gave you a set of business rules for a special forecasting calculation. Create a calculation script that uses conditional logic to satisfy the business rules for the forecast:

1. Using the If It worksheet in Anatomy.xls as a reference, write a calculation that performs the following tasks in the Forecast scenario, and save it as IfIt.csc: • Pushes units from August to September

• Pushes units into Qtr 4 months with a two-month lag and a 10% accelerator • Pushes price into September and into the months in Qtr 4 with a one-month lag and a 10% decelerator

2. Test your calculations: a. Run ResetBigcorp.bat. b. Calculate IfIt.csc.

c. Refresh the If It worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in

c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the If It worksheet.

60

(62)

Extra Credit

Controlling the Calculation Process

The 10% accelerator for units creates partial units in some months. Add logic to your script so that it does not return partial units:

1. Modify IfIt.csc so that units are rounded to the nearest whole number, and save it as IfItEC.

a. Run ResetBigcorp.bat. b. Calculate IfItEC.csc.

c. Refresh the If It worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in

c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the If It_Extra worksheet.

.

(63)

Lesson 14 Referencing Members in Calculations

Exercise 14.1

Creating Mix Calculations

The Sales group requires “percent of total” mix calculations for analysis, based on several measures.

1. Apply the following logic to add member formulas:

Member Unit Mix by Cust Unit Mix

Sales $ Mix Formula Logic

Units for current customer/units for all customers Units for current product/units for current product family

Net sales for current product/net sales for current product family

2. Set storage properties for the modified accounts to Dynamic Calc. 3. Test your calculation results:

a. Run ResetBigcorp.bat. b. Calculate Anatomy.csc. 62

.

(64)

c. Refresh the Customer Mix worksheet in Anatomy.xls and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Customer Mix worksheet.

(65)

d. Refresh the Product Mix worksheet in Anatomy.xls and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in

c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Product Mix worksheet.

Exercise 14.2

Calculating Time Variances

The Bigcorp Finance group requires period variance calculations for its analysis and wants you to create the calculations in the Sales database. Because the formulas must be updated regularly (for example, a variance between current month and prior month data must be updated every month), you decide to create substitution variables for easier maintenance.

64

(66)

Create and test substitution variables:

1. From Administration Services Console, add the following substitution variables to Bigcorp Sales: Variable Name CurrentMonth PriorMonth CurQtr PriorQtr CurQtrC PriorQtrC Variable Value Apr Qtr 2 Qtr 1 “Qtr 2” “Qtr 1” Mar

NOTE: Because quarter names include spaces, separate variables are required for reporting and calculation purposes.

2. In the Sales outline, add variance formulas to the Year Tot dimension members Cur Mo Vs Prior and Cur Qtr Vs Prior:

a. Use @VAR for the variance calculation.

b. Use & (ampersand) to identify variable names in the formulas.

c. Set the data storage property for Cur Mo Vs Prior and Cur Qtr Vs Prior to Dynamic Calc.

NOTE: If you accidentally clear data when saving your outline, you must run ResetBigcorp.bat and calculate Anatomy.csc before proceeding. Essbase 11.1.2 Bootcamp

65

(67)

3. Test your calculation: Refresh the Sub Variable worksheet in Anatomy.xls, and compare your calculation results to the following figures:, or open

Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Sub Variable worksheet.

66

(68)

Lesson 15 Developing and Testing Complex

Calculation Scripts

Exercise 15.1

Loading Data to Upper-Level Members

2. Load and calculate multilevel inputs: a. Calculate ClearAll.csc.

b. In AdvancedCalcs.xls (in c:\CF\CalcIt\Advanced), from the Multilevel Input worksheet, submit input data.

c. If necessary, set the following Smart View options: • Set aliases for Bigcorp Sales to None.

• Set the replacement value for #NoData/Missing labels to #NumericZero. d. On the Multilevel AuditSheet worksheet, calculate CYCalc.csc, and refresh to view calculated data.

TIP: Examine the Comparison formulas section to determine where calculated data is incorrect.

3. Modify and test CYCalc.csc:

a. Save the script as CYMulti.csc and modify the script to perform the following tasks:

• Set aggregate missing values to OFF

• Calculate Current Year data for all time periods • Consolidate the Year Tot dimension

• Recalculate Gross Margin, after all dimensions are consolidated b. Repeat step 2 to test calculations.

4. Load and calculate level 0 inputs: a. Calculate ClearAll.csc.

b. In AdvancedCalcs.xls, from the Level 0 InputSheet worksheet, submit input data. Essbase 11.1.2 Bootcamp

67

.

(69)

c. On the Level 0 Audit worksheet, calculate CYCalc.csc, and refresh to view calculated data.

TIP: Examine the Comparison formulas section to determine where calculated data is incorrect.

5. Modify CYCalc.csc:

a. Save the script as CYLev0.csc and modify the script to perform the following tasks: • Set aggregate missing values to ON

• Calculate Current Year data for all time periods • Consolidate the Year Tot dimension

b. Repeat step 4 to test calculations. 68

(70)

Lesson 16 Normalizing Data

Exercise 16.1

Allocating Current Year Data

1. Save CYLev0.csc as CYAlloc.csc and add normalization that allocates Other CGS data by using a dynamic ratio calculation.

a. Calculate ClearAll.csc.

b. In AdvancedCalcs.xls, from the Allocation Input worksheet, submit input data. c. On the Allocation AuditSheet worksheet, calculate CYAlloc.csc, and refresh to view calculated data.

TIP: Examine the comparison formulas to validate your calculation.

Exercise 16.2

Normalizing Budget Data

1. In Budget.xls (in c:\CF\Bootcamp\CalcIt\Advanced) fill in the normalization table on the Normalization tab. Base your work on the input spreadsheets in the workbook. 2. Save CYAlloc.csc as Budget.csc and add script to normalize the rates and drivers listed in the normalization table.

3. Test the script:

a. Run LoadBudget.bat (in c:\CF\Bootcamp\Scripts) to clear Sales and load input data for Current Year and Budget scenarios.

b. In Budget.xls, on the Audit Calcs worksheet, calculate Budget.csc, and refresh to view calculated data for the default POV (O-IBM, Lightbolt 365 A, and Budget). Essbase 11.1.2 Bootcamp

69

.

(71)

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

Budget_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Level 0 worksheet.

70

(72)

d. Change your POV to Channel Total, Family Total, and Budget, and refresh to view calculated data. Compare your calculation results to the following figure, or open Budget_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Upper Level worksheet.

(73)

Lesson 17 Creating Attribute Dimensions

Exercise 17.1

Viewing Attribute Data

1. Create an attribute crosstab report:

b. In Analysis.xls (in c:\CF\Bootcamp\Analysis), refresh the Regions worksheet. c. Test the behavior of zooming in on level 0 attributes.

d. Using ad hoc operations, create a report with regions in the rows and the customer channels OEM, Retail, and Distributor in the columns.

2. View attribute calculations:

a. Delete all but one column from the grid.

b. In the remaining column header, enter Attribute Calculations, and refresh the worksheet.

c. Zoom in on Attribute Calculations to display discrete attribute calculations. 72

.

a. Run ResetBigcorp.bat (in c:\CF\Bootcamp\Scripts) and the CalcBC.csc calculation script.

(74)