.
.
Oracle Essbase 11.1.2 Bootcamp
.
.
Table of Contents Case Study 1: Bigcorp Exercises Lesson 1: 0
Exercise 1.1 Exercise 1.2
Essbase Overview
Starting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Accessing Administration Services Console . . . . . . . . . . . . . . . . . . . . . 13
Lesson 2:
Exercise 2.1 Exercise 2.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 Analysis and Planning for Bigcorp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
.
Table of Contents
Lesson 5: Exercise 5.1
Developing Dimension Designs
Designing the Product and Customer Dimensions . . . . . . . . . . . . . . . . 29
Lesson 6: Exercise 6.1 Exercise 6.2
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
.
.
Table of Contents
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
. Case Study 1 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.
.
.
Case Study: Bigcorp
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 Essbase 11.1.2 Bootcamp
.
Analysis and Planning for Bigcorp
.
Case Study: Bigcorp • Budget data is created by the budgeting team in Excel spreadsheets. The design 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
.
.
4 Product Report Variance Report Case Study: Bigcorp Essbase 11.1.2 Bootcamp
.
.
Channels Report Product by Channel Essbase 11.1.2 Bootcamp Case Study: Bigcorp 5
.
.
6 Case Study: Bigcorp Customer by Product Category Essbase 11.1.2 Bootcamp
.
.
Exercises Overview This section contains hands-on exercises that you will work on throughout this course. Exercises are grouped by lesson. Your instructor may choose to perform some exercises with you in demonstration format; therefore, you may not be required to complete all exercises. It is, however, important that you complete all exercises as requested by the instructor, because many exercises build upon other exercises. Before beginning these exercises, be sure to read the Bigcorp case study.
.
.
Lesson 1 Essbase Overview Exercises
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.
.
1 Exercises - Oracle Process Manager (ohsInstance3193331783) - Oracle Process Manager (EPM_epmsystem1) - OracleOraDb11g_home1TNSListener - OracleServiceORCL NOTE: If any services failed to start, alert your instructor. 8. Close the Services window. 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
.
.
Lesson 2 Designing Applications and Databases Exercises
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:
.
2 Designing Applications and Databases Exercises 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
.
.
Lesson 3 Designing Data Descriptor Dimensions Exercises
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 Essbase 11.1.2 Bootcamp
.
Finalize the members and hierarchies of the Year Tot dimension:
.
3 Exercises 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
.
.
Lesson 3 Designing Data Descriptor Dimensions Exercises 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
.
.
3 Exercises • 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
. • Overhead Variances
.
Lesson 3 Designing Data Descriptor Dimensions Exercises
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 Essbase 11.1.2 Bootcamp
.
SET UPDATECALC OFF; CALC ALL;
.
3 Exercises 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
.
.
Lesson 3 Designing Data Descriptor Dimensions Exercises 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 Essbase 11.1.2 Bootcamp
.
.
4 Optimizing Data Descriptor Dimensions Exercises
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
.
Add default aliases to Year Tot and Scenario dimension members:
.
Lesson 4 Optimizing Data Descriptor Dimensions Exercises 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 Essbase 11.1.2 Bootcamp
.
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.
.
4 Optimizing Data Descriptor Dimensions Exercises 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
.
.
Lesson 4 Optimizing Data Descriptor Dimensions Exercises 2. Test your calculations: a. Run OptimizeInput.bat (in c:\CF\Bootcamp\Scripts) to clear the database and load input data. 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
.
.
4 Optimizing Data Descriptor Dimensions Exercises • 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: a. Run OptimizeInput.bat (in c:\CF\Bootcamp\Scripts) to clear the database and load input data. 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
.
• Other CGS
.
Lesson 4 Optimizing Data Descriptor Dimensions Exercises 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: a. Run OptimizeInput.bat (in c:\CF\Bootcamp\Scripts) to clear the database and load input data. 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 Essbase 11.1.2 Bootcamp
.
.
5 Exercises 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. Essbase 11.1.2 Bootcamp 29
. Design the product and customer dimensions:
.
Lesson 6 Creating Basic Dimension Build Rules Files Exercises
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 Essbase 11.1.2 Bootcamp
.
The Bigcorp IT team has provided you with text file extracts from its data warehouse for your initial dimension builds.
.
6 Creating Basic Dimension Build Rules Files Exercises • 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
.
.
Lesson 6 Creating Basic Dimension Build Rules Files Exercises • 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 Essbase 11.1.2 Bootcamp
.
.
7 Exercises 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 Essbase 11.1.2 Bootcamp 33
.
.
Lesson 7 Creating Advanced Dimension Build Rules Files 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 Essbase 11.1.2 Bootcamp
.
.
7 Exercises 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 Essbase 11.1.2 Bootcamp 35
.
• Review the Customer dimension plan that you created in Lesson 5 to ensure that you create the entire dimension.
.
Lesson 7 Creating Advanced Dimension Build Rules Files 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 Essbase 11.1.2 Bootcamp
.
.
7 Exercises 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 Essbase 11.1.2 Bootcamp 37
.
.
Lesson 7 Creating Advanced Dimension Build Rules Files 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 Essbase 11.1.2 Bootcamp
.
.
7 Exercises 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. Essbase 11.1.2 Bootcamp 39
. 4. Update the Sales outline, and specify the following options:
.
Lesson 8 Loading Data Exercises
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 Essbase 11.1.2 Bootcamp
.
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.
.
8 Loading Data Exercises 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
.
.
42 b. Refresh the worksheet. a. Select the Flash worksheet, and set localhost_Bigcorp_Sales as the active connection. Exercises Lesson 8 Loading Data Essbase 11.1.2 Bootcamp
.
.
9 Getting Started with Smart View Exercises
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. Essbase 11.1.2 Bootcamp 43
. 1. Run ssheet.bat (in C:\CF\Bootcamp\Scripts) to reset the Sales database.
.
Lesson 9 Getting Started with Smart View Exercises 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 Essbase 11.1.2 Bootcamp
.
.
9 Getting Started with Smart View Exercises
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. Essbase 11.1.2 Bootcamp 45
.
.
Lesson 9 Getting Started with Smart View Exercises 2. Set localhost_bigcorp_sales as the active connection and refresh the worksheet. Data is displayed in the grid. 46 Essbase 11.1.2 Bootcamp
.
.
10 Creating Reports with Smart View Exercises
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. Essbase 11.1.2 Bootcamp 47
. Test an input worksheet by updating data, submitting data to Essbase, and executing a calculation script:
.
Lesson 10 Creating Reports with Smart View Exercises 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 Essbase 11.1.2 Bootcamp
.
.
10 Creating Reports with Smart View Exercises 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. Essbase 11.1.2 Bootcamp 49
.
.
Lesson 10 Creating Reports with Smart View Exercises 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 Essbase 11.1.2 Bootcamp
.
.
10 Creating Reports with Smart View Exercises 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
.
• Chart
.
Lesson 10 Creating Reports with Smart View Exercises 3. Refresh the worksheet, and set the slider to THUNDERBALL. Thunderball data is displayed in the function grid and chart. 52 Essbase 11.1.2 Bootcamp
.
.
11 Data Storage and Calculation Exercises
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. Essbase 11.1.2 Bootcamp 53
.
During multidimensional calculation, input data is transformed into useful information. A thorough understanding of the process of multidimensional calculation is necessary before you create more complex calculations.
.
Lesson 11 Data Storage and Calculation Exercises 4. In Anatomy.xls (in C:\CF\Bootcamp\CalcIt\Anatomy), test the calculations by using the This Is a Block worksheet. NOTE: Remember to enable Excel formatting before refreshing the worksheet. Calculated data is displayed in the worksheet. NOTE: Unlike previous worksheets, this workbook was saved after connecting to the Sales database, and thus contains stored connection information (including the last used connection and the POV). Therefore, you are not required to set the active database connection before you refresh. 54 Essbase 11.1.2 Bootcamp
.
.
11 Data Storage and Calculation Exercises 5. On a separate piece of paper, record the following postcalculation database statistics for Sales, and compare them to the precalculation statistics: • Number of existing blocks • Existing level 0 blocks • Existing upper-level blocks • Percentage of maximum blocks existing Extra Credit: Recording Detailed Statistics Implement an iterative test of Anatomy.csc to calculate the Sales database one dimension at a time. Record the changes to the following database statistics after data load and after each dimension calculation: • Number of existing blocks • Existing level 0 blocks • Existing upper-level blocks • Block density • Percentage of maximum blocks existing Essbase 11.1.2 Bootcamp 55
. • Block density
.
Lesson 12 Creating Calculation Scripts Exercises
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 Essbase 11.1.2 Bootcamp
.
In Anatomy.csc, add a brief information section with your name and the date, a housekeeping section header, and a main rollup section header. Save the changes.
.
12 Creating Calculation Scripts Exercises c. In Anatomy.xls, refresh the This is a Block worksheet and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Level0Block worksheet. Essbase 11.1.2 Bootcamp 57
.
.
Lesson 12 Creating Calculation Scripts Exercises 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 Essbase 11.1.2 Bootcamp
.
.
13 Exercises Controlling the Calculation Process
Lesson 13 Controlling the Calculation Process Exercise 13.1 Focusing Calculations with FIX
In the interest of performance, the Bigcorp management team wants you to modify Anatomy.csc so that it calculates only Current Year data for the months in Qtr 1. 1. Modify the Anatomy.csc script as follows, and save it as CYCalc.csc: a. Focus the script to calculate only for Current Year and months in Qtr 1. b. Make any modifications necessary to the rest of the script as a result of adding the fix statement. c. Use commenting sections to identify the start and end of the fix. 2. Test your calculations: a. Run ResetBigcorp.bat. b. Calculate CYCalc.csc. c. Refresh the Fixit worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Fix It worksheet. Essbase 11.1.2 Bootcamp 59
. Create a current-year calculation based on Anatomy.csc:
.
Lesson 13 Controlling the Calculation Process Exercises
Exercise 13.2 Calculating Conditionally with IF The Sales group gave you a set of business rules for a special forecasting calculation. Create a calculation script that uses conditional logic to satisfy the business rules for the forecast: 1. Using the If It worksheet in Anatomy.xls as a reference, write a calculation that performs the following tasks in the Forecast scenario, and save it as IfIt.csc: • Pushes units from August to September • Pushes units into Qtr 4 months with a two-month lag and a 10% accelerator • Pushes price into September and into the months in Qtr 4 with a one-month lag and a 10% decelerator 2. Test your calculations: a. Run ResetBigcorp.bat. b. Calculate IfIt.csc. c. Refresh the If It worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the If It worksheet. 60 Essbase 11.1.2 Bootcamp
.
.
13 Exercises Extra Credit Controlling the Calculation Process The 10% accelerator for units creates partial units in some months. Add logic to your script so that it does not return partial units: 1. Modify IfIt.csc so that units are rounded to the nearest whole number, and save it as IfItEC. a. Run ResetBigcorp.bat. b. Calculate IfItEC.csc. c. Refresh the If It worksheet in Anatomy.xls, and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the If It_Extra worksheet. Essbase 11.1.2 Bootcamp 61
. 2. Test your calculations:
.
Lesson 14 Referencing Members in Calculations Exercises
Lesson 14 Referencing Members in Calculations Exercise 14.1 Creating Mix Calculations
The Sales group requires “percent of total” mix calculations for analysis, based on several measures. 1. Apply the following logic to add member formulas: Member Unit Mix by Cust Unit Mix Sales $ Mix Formula Logic Units for current customer/units for all customers Units for current product/units for current product family Net sales for current product/net sales for current product family
2. Set storage properties for the modified accounts to Dynamic Calc. 3. Test your calculation results: a. Run ResetBigcorp.bat. b. Calculate Anatomy.csc. 62 Essbase 11.1.2 Bootcamp
.
Add formulas to the Sales outline:
.
14 Referencing Members in Calculations Exercises c. Refresh the Customer Mix worksheet in Anatomy.xls and compare your calculation results to the following figure, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Customer Mix worksheet. Essbase 11.1.2 Bootcamp 63
.
.
Lesson 14 Referencing Members in Calculations Exercises 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 Essbase 11.1.2 Bootcamp
.
.
14 Referencing Members in Calculations Exercises 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
.
.
Lesson 14 Referencing Members in Calculations Exercises 3. Test your calculation: Refresh the Sub Variable worksheet in Anatomy.xls, and compare your calculation results to the following figures:, or open Anatomy_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Sub Variable worksheet. 66 Essbase 11.1.2 Bootcamp
.
.
15 Developing and Testing Complex Calculation Scripts Exercises
Lesson 15 Developing and Testing Complex Calculation Scripts Exercise 15.1 Loading Data to Upper-Level Members
2. Load and calculate multilevel inputs: a. Calculate ClearAll.csc. b. In AdvancedCalcs.xls (in c:\CF\CalcIt\Advanced), from the Multilevel Input worksheet, submit input data. c. If necessary, set the following Smart View options: • Set aliases for Bigcorp Sales to None. • Set the replacement value for #NoData/Missing labels to #NumericZero. d. On the Multilevel AuditSheet worksheet, calculate CYCalc.csc, and refresh to view calculated data. TIP: Examine the Comparison formulas section to determine where calculated data is incorrect. 3. Modify and test CYCalc.csc: a. Save the script as CYMulti.csc and modify the script to perform the following tasks: • Set aggregate missing values to OFF • Calculate Current Year data for all time periods • Consolidate the Year Tot dimension • Recalculate Gross Margin, after all dimensions are consolidated b. Repeat step 2 to test calculations. 4. Load and calculate level 0 inputs: a. Calculate ClearAll.csc. b. In AdvancedCalcs.xls, from the Level 0 InputSheet worksheet, submit input data. Essbase 11.1.2 Bootcamp 67
. 1. Create a script called ClearAll.csc that clears all data from the database.
.
Lesson 15 Developing and Testing Complex Calculation Scripts Exercises c. On the Level 0 Audit worksheet, calculate CYCalc.csc, and refresh to view calculated data. TIP: Examine the Comparison formulas section to determine where calculated data is incorrect. 5. Modify CYCalc.csc: a. Save the script as CYLev0.csc and modify the script to perform the following tasks: • Set aggregate missing values to ON • Calculate Current Year data for all time periods • Consolidate the Year Tot dimension b. Repeat step 4 to test calculations. 68 Essbase 11.1.2 Bootcamp
.
.
16 Normalizing Data Exercises
Lesson 16 Normalizing Data Exercise 16.1 Allocating Current Year Data 1. Save CYLev0.csc as CYAlloc.csc and add normalization that allocates Other CGS data by using a dynamic ratio calculation. a. Calculate ClearAll.csc. b. In AdvancedCalcs.xls, from the Allocation Input worksheet, submit input data. c. On the Allocation AuditSheet worksheet, calculate CYAlloc.csc, and refresh to view calculated data. TIP: Examine the comparison formulas to validate your calculation.
Exercise 16.2 Normalizing Budget Data
1. In Budget.xls (in c:\CF\Bootcamp\CalcIt\Advanced) fill in the normalization table on the Normalization tab. Base your work on the input spreadsheets in the workbook. 2. Save CYAlloc.csc as Budget.csc and add script to normalize the rates and drivers listed in the normalization table. 3. Test the script: a. Run LoadBudget.bat (in c:\CF\Bootcamp\Scripts) to clear Sales and load input data for Current Year and Budget scenarios. b. In Budget.xls, on the Audit Calcs worksheet, calculate Budget.csc, and refresh to view calculated data for the default POV (O-IBM, Lightbolt 365 A, and Budget). Essbase 11.1.2 Bootcamp 69
. 2. Test the script:
.
Lesson 16 Normalizing Data Exercises c. Compare your calculation results to the following figure, or open Budget_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Level 0 worksheet. 70 Essbase 11.1.2 Bootcamp
.
.
16 Normalizing Data Exercises d. Change your POV to Channel Total, Family Total, and Budget, and refresh to view calculated data. Compare your calculation results to the following figure, or open Budget_Solutions.xls (in c:\CF\Bootcamp\CalcIt\Complete) and compare your results to the Upper Level worksheet. Essbase 11.1.2 Bootcamp 71
.
.
Lesson 17 Creating Attribute Dimensions Exercises
Lesson 17 Creating Attribute Dimensions Exercise 17.1 Viewing Attribute Data 1. Create an attribute crosstab report: b. In Analysis.xls (in c:\CF\Bootcamp\Analysis), refresh the Regions worksheet. c. Test the behavior of zooming in on level 0 attributes. d. Using ad hoc operations, create a report with regions in the rows and the customer channels OEM, Retail, and Distributor in the columns. 2. View attribute calculations: a. Delete all but one column from the grid. b. In the remaining column header, enter Attribute Calculations, and refresh the worksheet. c. Zoom in on Attribute Calculations to display discrete attribute calculations. 72 Essbase 11.1.2 Bootcamp
. a. Run ResetBigcorp.bat (in c:\CF\Bootcamp\Scripts) and the CalcBC.csc calculation script.
