Activity Guide.pdf

Full text

(1)Activity Guide. D70048GC10 Edition 1.0 August 2008 D56251. ®. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Oracle Essbase 11.1.1: Bootcamp.

(2) Copyright © 2008, Oracle. All rights reserved.. Lisa Alexander Jumal Qazi. Disclaimer. Technical Contributors and Reviewers Shubho Bhattacharya Art Hetherington Saju Philips Sergiy Percherskyy Aneel Shenker Yulia Stolerman. Editors Susan Moxley Jennifer Stout. Graphic Editors Asha Thampy Satish Bettegowda. This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract. Trademark Notice Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Authors.

(3) 0. Case Study 1: Bigcorp Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Bigcorp Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Analysis and Planning for Bigcorp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3. Exercises Lesson 1:. Essbase Overview. Exercise 1.1 Exercise 1.2. Housekeeping: Starting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Accessing Administration Services Console . . . . . . . . . . . . . . . . . . . . . 12. Lesson 2:. Designing Applications and Databases. Exercise 2.1 Exercise 2.2. Creating Block Storage Applications and Databases. . . . . . . . . . . . . . . 13 Creating Dimensions and Members. . . . . . . . . . . . . . . . . . . . . . . . . . . . 13. Lesson 3:. Designing Data Descriptor Dimensions. Exercise 3.1 Exercise 3.2 Exercise 3.3 Exercise 3.4 Exercise 3.5 Exercise 3.6. Creating Generic Time Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Tracking Data Sets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Creating Outline Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Creating Accounts Hierarchies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Testing Accounts Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Creating Balance Sheet Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19. Lesson 4:. Optimizing Data Descriptor Dimensions. Exercise 4.1 Exercise 4.2 Exercise 4.3 Exercise 4.4 Exercise 4.5. Creating Member Aliases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Implementing Dynamic Time Series Members. . . . . . . . . . . . . . . . . . . . 22 Correcting Percentage and Balance Accounts . . . . . . . . . . . . . . . . . . . 23 Adding Variance Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Optimizing Data Storage Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents.

(4) Lesson 5:. Planning Dimension Designs. Exercise 5.1. Designing the Product and Customer Dimensions . . . . . . . . . . . . . . . . 27. Lesson 6:. Creating Basic Dimension Build Rules Files. Exercise 6.1 Exercise 6.2. Building the Family Total Rollup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Optional: Adding Products to the Family Total Hierarchy . . . . . . . . . . . . 29. Lesson 7:. Creating Advanced Dimension Build Rules Files. Exercise 7.1 Exercise 7.2 Exercise 7.3. Creating Secondary Hierarchies with Parent-Child Build. . . . . . . . . . . . 31 Building the Customer Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Optional: Adding UDAs to Customers . . . . . . . . . . . . . . . . . . . . . . . . . . 34. Lesson 8:. Loading Data. Exercise 8.1 Exercise 8.2 Exercise 8.3. Loading Actual Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Loading Forecast and Actual Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Optional: Loading Transactional Units . . . . . . . . . . . . . . . . . . . . . . . . . . 38. Lesson 9:. Getting Started with Smart View. Exercise 9.1 Exercise 9.2 Exercise 9.3. Connecting to Provider Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Starting Ad Hoc Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Customizing the Point of View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40. Lesson 10: Creating Reports with Smart View Exercise 10.1 Exercise 10.2 Exercise 10.3 Exercise 10.4 Exercise 10.5. Creating Ad Hoc Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Updating Essbase Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Viewing Dynamic Data Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Creating Smart Slices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Creating a Custom Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46. Lesson 11: Data Storage and Calculation Exercise 11.1 Calculating the Sales Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Extra Credit: Recording Detailed Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50. iv. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents.

(5) Table of Contents. Exercise 12.1 Organizing Calculation Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Exercise 12.2 Correcting Rates and Percentages . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51. Lesson 13: Controlling the Calculation Process Exercise 13.1 Focusing Calculations with FIX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Exercise 13.2 Calculating Conditionally with IF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Extra Credit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55. Lesson 14: Referencing Members in Calculations Exercise 14.1 Creating Mix Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Exercise 14.2 Calculating Time Variances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59. Lesson 15: Creating Attribute Dimensions Exercise 15.1 Optional: Creating the Region Total Dimension . . . . . . . . . . . . . . . . . . . 61. Lesson 16: Analyzing Varying Attributes Exercise 16.1 Creating Varying Attribute Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . 62 Exercise 16.2 Extending Varying Attribute Dimensions . . . . . . . . . . . . . . . . . . . . . . . . 63. Lesson 17: Analyzing Text and Dates Exercise 17.1 Creating Sales Risk Text Measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Exercise 17.2 Optional: Creating Sales Risk Average Measures . . . . . . . . . . . . . . . . . 66. Exercise Solutions Lesson 1:. Essbase Overview. Solution 1.1 Solution 1.2. Housekeeping: Starting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Accessing Administration Services Console . . . . . . . . . . . . . . . . . . . . . 70. Essbase 11.1.1: Bootcamp. v. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 12: Creating Calculation Scripts.

(6) Table of Contents. Designing Applications and Databases. Solution 2.1 Creating Block Storage Applications and Databases. . . . . . . . . . . . . . . 72 Creating the Bigcorp Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Creating the Sales Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Reviewing the Bigcorp Application Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Reviewing the Bigcorp File Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Creating a Custom View for Bigcorp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Solution 2.2 Creating Dimensions and Members. . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Adding Dimensions to Bigcorp Sales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Adding Members to the Year Tot Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74. Lesson 3:. Designing Data Descriptor Dimensions. Solution 3.1 Creating Generic Time Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Solution 3.2 Tracking Data Sets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Solution 3.3 Creating Outline Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Changing Consolidation Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Calculating Act Vs Bud. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Calculating Act Vs Fcst and Bud Vs Fcst . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Solution 3.4 Creating Accounts Hierarchies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Solution 3.5 Testing Accounts Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Modifying the Default Database Calculation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Connecting to Provider Services and Creating a Favorite Connection . . . . . . . . . 82 Testing Outline Name Matches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Testing Accounts Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Solution 3.6 Creating Balance Sheet Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85. Lesson 4:. Optimizing Data Descriptor Dimensions. Solution 4.1 Creating Member Aliases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Creating Year Tot Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Creating Scenario Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Solution 4.2 Implementing Dynamic Time Series Members. . . . . . . . . . . . . . . . . . . . 88 Tagging Year Tot as a Time Dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Enabling Q-T-D and Y-T-D Dynamic Time Series Members . . . . . . . . . . . . . . . . . 88 Testing Dynamic Time Series Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Solution 4.3 Correcting Percentage and Balance Accounts . . . . . . . . . . . . . . . . . . . 90 Modifying the Outline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Testing Calculation Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Solution 4.4 Adding Variance Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Adding Expense Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92. vi. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 2:.

(7) Adding Variance Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Testing Variance Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Solution 4.5 Optimizing Data Storage Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Testing Default Behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Modifying the Outline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94. Lesson 5:. Planning Dimension Designs. Solution 5.1 Designing the Product and Customer Dimensions . . . . . . . . . . . . . . . . 96 Designing Product Dimension Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Designing Customer Dimension Plan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97. Lesson 6:. Creating Basic Dimension Build Rules Files. Solution 6.1 Building the Family Total Rollup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Opening a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Setting Data Source Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Setting View Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Associating the Sales Outline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Adding the Product Dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Selecting the Dimension Build Method. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Defining Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Validating and Saving the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Updating the Sales Outline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Solution 6.2 Optional: Adding Products to the Family Total Hierarchy . . . . . . . . . . . 107 Connecting to the Bigcorp Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Completing the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Updating the Sales Outline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110. Lesson 7:. Creating Advanced Dimension Build Rules Files. Solution 7.1 Creating Secondary Hierarchies with Parent-Child Build. . . . . . . . . . . 111 Creating the Rules File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Updating the Sales Outline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Solution 7.2 Building the Customer Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Starting a Rules File for the Customer Dimension . . . . . . . . . . . . . . . . . . . . . . . . 113 Manipulating Data Source Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Creating Unique Customer Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Ignoring Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Replacing Text in Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 Adding the Customer Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123. Essbase 11.1.1: Bootcamp. vii. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents.

(8) Completing the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Updating the Sales Outline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Solution 7.3 Optional: Adding UDAs to Customers . . . . . . . . . . . . . . . . . . . . . . . . . 126 Opening the Rules File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Modifying the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Modifying Dimension Build Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Loading Customer UDAs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130. Lesson 8:. Loading Data. Solution 8.1 Loading Actual Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Starting the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Ignoring Extra Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Defining Data Load Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Validating the Rules File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Loading Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Saving Data Load Choices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Viewing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 Solution 8.2 Loading Forecast and Actual Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 Solution 8.3 Optional: Loading Transactional Units . . . . . . . . . . . . . . . . . . . . . . . . . 137 Starting the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Modifying the Date Field. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Modifying Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 Defining a Data Load Header. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 Modifying Data Load Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 Validating the Rules File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Loading Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Viewing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140. Lesson 9:. Getting Started with Smart View. Solution 9.1 Connecting to Provider Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Resetting the Sales Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Connecting to Bigcorp Sales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Solution 9.2 Starting Ad Hoc Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Solution 9.3 Customizing the Point of View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142. Lesson 10: Creating Reports with Smart View Solution 10.1 Creating Ad Hoc Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Creating the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144. viii. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents.

(9) Setting the Point of View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 Solution 10.2 Updating Essbase Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Setting Display Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Adjusting and Submitting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Calculating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Solution 10.3 Viewing Dynamic Data Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 Opening a Document with Dynamic Data Points . . . . . . . . . . . . . . . . . . . . . . . . . 150 Creating a Linked View in Visual Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Solution 10.4 Creating Smart Slices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Starting a Smart Slice. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Selecting Members. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Setting Query Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Saving the Smart Slice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Creating Ad Hoc Reports from Smart Slices . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Solution 10.5 Creating a Custom Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157. Lesson 11: Data Storage and Calculation Solution 11.1 Calculating the Sales Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Resetting the Sales Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Recording Database Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Creating a Calculation Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 Testing Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Recording Database Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 Extra Credit: Recording Detailed Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163. Lesson 12: Creating Calculation Scripts Solution 12.1 Organizing Calculation Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164 Solution 12.2 Correcting Rates and Percentages . . . . . . . . . . . . . . . . . . . . . . . . . . . 164. Lesson 13: Controlling the Calculation Process Solution 13.1 Focusing Calculations with FIX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Solution 13.2 Calculating Conditionally with IF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Extra Credit Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169. Lesson 14: Referencing Members in Calculations Solution 14.1 Creating Mix Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Adding Member Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171. Essbase 11.1.1: Bootcamp. ix. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents.

(10) Setting Storage Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Testing Calculation Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Solution 14.2 Calculating Time Variances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Creating Substitution Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Creating Variance Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Testing Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176. Lesson 15: Creating Attribute Dimensions Solution 15.1 Optional: Creating the Region Total Dimension . . . . . . . . . . . . . . . . . . 177 Starting the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Adding the Region Total Dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Completing the Rules File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178 Updating the Sales Outline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178. Lesson 16: Analyzing Varying Attribute Data Solution 16.1 Creating Varying Attribute Dimensions . . . . . . . . . . . . . . . . . . . . . . . . 180 Enabling Varying Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Creating an Attribute Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Assigning Base and Independent dimensions to the Attribute Dimension . . . . . . 180 Assigning Managers to Product Lines According to Date Ranges. . . . . . . . . . . . 181 Viewing Varying Attribute Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 Solution 16.2 Extending Varying Attribute Dimensions . . . . . . . . . . . . . . . . . . . . . . . 184 Modifying the Product Manager Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 Assigning Varying Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184. Lesson 17: Analyzing Text and Dates Solution 17.1 Creating Sales Risk Text Measures . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Enabling Typed Measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Creating a Text List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Populating a Text List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Associating Text Lists with Measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Updating Sales Risk Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Solution 17.2 Optional: Creating Sales Risk Average Measures . . . . . . . . . . . . . . . . 190 Creating a Calculation Based on the Sales Risk Measure. . . . . . . . . . . . . . . . . . 190 Testing calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190. Case Study 2: Country Kitchen Desserts Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 x. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents.

(11) Case Study Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Country Kitchen Desserts Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Analysis and Planning for CKD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 Phase I—Designing and Creating the CKD Outline. . . . . . . . . . . . . . . . . . . . . . . . . . . 204 Phase II—Loading Data and Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 Phase III—Calculation and Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 Case Study Solutions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Phase I Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Implementing Dynamic Time Series. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Creating an Alias Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226 Phase II Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229 Connecting to the Essbase Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 Creating the CKD Income Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231 Setting POV Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232 Setting POV Regions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234 Setting POV Customers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 Formatting the Income Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 Creating the Product by Salesperson Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . 237 Setting the Product by Salesperson POV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239 Formatting the Product by Salesperson Report . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Phase III Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Adding Dynamic Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242 Calculating Product Share:. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 Calculating Market Share . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244 Calculating Tax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 Adjusting Budget Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247. Essbase 11.1.1: Bootcamp. xi. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents.

(12) Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Table of Contents. xii. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(13) S t u d y. 1. Bigcorp. 1. 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.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. C a s e.

(14) Case Study: Bigcorp. 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, and Global Technology Hard Drives, Inc., a sister company to Bigcorp, operates Bigcorp outside the United States.. 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.. 2. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Bigcorp Overview.

(15) Case Study: Bigcorp. The management team at Bigcorp has employed you to build a block storage database that provides the information that they require and that uses data extracts from their existing systems.. Participant Information Participant Group. Requirements. Finance. • Monthly product development—reported by individual product; grouped by product families • Details of company cost structures • Current and prior year actual and budget data. Manufacturing. • Monthly product unit sales, grouped by hard-drive configuration • For sales, current year and forecast data. Sales. • 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. • Budget data is created by the budgeting team in Excel spreadsheets. The design team decides to grant the budgeting team security permissions to send their 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 their data directly from Excel to the Sales database.. Essbase 11.1.1: Bootcamp. 3. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Analysis and Planning for Bigcorp.

(16) Case Study: Bigcorp. 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 do in Essbase. You can view the reports in a workbook, called BCSampleReports.xls (located in c:\CF\Bootcamp\SSReports). Operating Statement. Variance Report. 4. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Analyzing Sample Bigcorp Reports.

(17) Case Study: Bigcorp. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Product Report. Channels Report. Essbase 11.1.1: Bootcamp. 5. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(18) Case Study: Bigcorp. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Product by Channel. 6. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(19) Case Study: Bigcorp. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Customer by Product Category. Essbase 11.1.1: Bootcamp. 7. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(20) Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Case Study: Bigcorp. 8. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(21) 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 document in this workbook.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Exercises.

(22) Lesson 1 Essbase Overview Exercises. Exercise 1.1. Housekeeping: Starting Services. Each class machine is a stand-alone server and client. Start the necessary services before starting any exercises: File Name. Location. 1.Start Oracle Services (can take up to 5 mins).bat. c:\CF\Bootcamp\Startup and Shutdown\Startup. 2.Start Hyperion Services.bat. c:\CF\Bootcamp\Startup and Shutdown\Startup. To ensure that all services have started properly, run an EPM System Diagnostic.. 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 trnadmin and password hyperion) 2. Explore the navigation hierarchy. 3. Navigate to the list of Essbase Servers, and open a database outline. 4. Verify the database outline before closing.. 12. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 1 Essbase Overview.

(23) Lesson 2 Designing Applications and Databases Exercises. Exercise 2.1. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 2 Designing Applications and Databases Creating Block Storage Applications and Databases. Based on the Bigcorp case study, you can begin to build a database. Create a block storage application and database for Bigcorp and review the hierarchy structure of the new database: 1. Create an application with the following specifications: Option. Specification. Application type. Block storage. Application name. Bigcorp. 2. Create a database with the following specifications: Option. Specification. Database type. Normal. Database name. Sales. 3. Review the Bigcorp application structure. Compare your hierarchy to the following figure:. 4. Review the file system folder structure for Bigcorp. 5. Create a custom view for Bigcorp.. Exercise 2.2. Creating Dimensions and Members. Dimensions are based on the Bigcorp case study.. Essbase 11.1.1: Bootcamp. 13. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(24) Lesson 2 Designing Applications and Databases Exercises Add dimensions to the Sales outline and add members to dimensions: 1. Add the following dimensions to Bigcorp Sales:. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. • Year Tot • Accounts • Scenario 2. Add the following members to the Year Tot dimension: Parent. Child. Year Tot. Qtr 1. Qtr 1. Jan. Qtr 1. Feb. Qtr 1. Mar. 3. Compare your outline to the following figure:. 14. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(25) Lesson 3 Designing Data Descriptor Dimensions Exercises. Exercise 3.1. Creating Generic Time Dimensions. After evaluating the Bigcorp design document, you decide to use a generic time design for the Year Tot dimension. Finalize the members and hierarchies of 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. 3. Compare your outline to the following figure:. Exercise 3.2. Tracking Data Sets. Bigcorp uses the scenarios described in the Bigcorp case study to track data sets. Add the following scenarios and scenario variances, which Bigcorp requires for analysis of sales data:. Parent. Child. Scenario. Current Year. Scenario. Prior Year. Scenario. Budget. Scenario. Forecast. Essbase 11.1.1: Bootcamp. 15. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 3 Designing Data Descriptor Dimensions.

(26) Lesson 3 Designing Data Descriptor Dimensions. Parent. Child. Scenario. Scenario Variances. Scenario Variances. Act Vs Bud. Scenario Variances. Act Vs Fcst. Scenario Variances. Bud Vs Fcst. Compare your outline to the following figure:. 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. 3. Calculate Act Vs Fcst and Bud Vs Fcst using member formulas. 4. Compare your outline to the following figure:. 16. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Exercises.

(27) Lesson 3 Designing Data Descriptor Dimensions Exercises. Creating Accounts Hierarchies. The Accounts dimension within the Sales outline is based on the Gross Margin Analysis report in the Bigcorp design document. Define a hierarchy for the Accounts dimension based on the Excel formulas in Corpaccts.xls (in C:\CF\Bootcamp\Outline): Member names:. Essbase 11.1.1: Bootcamp. 17. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Exercise 3.4.

(28) 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: SET UPDATECALC OFF; CALC ALL; 2. In Smart View, perform the following tasks: a. Connect to Provider Services. b. Add Bigcorp Sales to your favorites. 3. On the Check Names worksheet in Corpaccts.xls, ensure that outline names match the prebuilt example. 4. Test accounts calculations:. 18. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Member formulas (Create the calculation for Gross Margin % using a member formula. Calculate all other accounts using hierarchy relationships.):.

(29) Lesson 3 Designing Data Descriptor Dimensions Exercises a. From the InputData worksheet, submit data. b. From the AuditData worksheet, calculate, retrieve, and verify data.. Exercise 3.6. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. c. Compare your calculation results to the following figure:. 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.1: Bootcamp. 19. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(30) Lesson 3 Designing Data Descriptor Dimensions Exercises. Account. Consolidation Property. Inventory. ~. Accounts Receivable. ~. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 3. Add the following children to Balance Sheet:. 4. Compare your outline to the following figure:. 20. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(31) Lesson 4 Optimizing Data Descriptor Dimensions Exercises. Exercise 4.1. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 4 Optimizing Data Descriptor Dimensions Creating Member Aliases. For reporting purposes, Bigcorp requires longer month and quarter names. Add default aliases to Year Tot and Scenario dimension members: 1. Add Year Tot aliases to quarters and months. • Use the following naming conventions: Member Type. Alias Guidelines. Quarters. Quarter 1, Quarter 2, Quarter 3, Quarter 4. Months. Full month name (January, February, and so on). • Compare your outline to the following figure:. 2. Add the following aliases to Scenario dimension members: • Use the following naming conventions: Member. Alias. Current Year. CY Actual. Prior Year. PY Actual. Essbase 11.1.1: Bootcamp. 21. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(32) Lesson 4 Optimizing Data Descriptor Dimensions Exercises. Exercise 4.2. Implementing Dynamic Time Series Members. 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. 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 • Compare your outline to the following figure:. 3. Test your calculations:. 22. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. • Compare your outline to the following figure:.

(33) Lesson 4 Optimizing Data Descriptor Dimensions Exercises a. Clear the Sales database.. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. b. In Optimize.xls (c:\CF\Bootcamp\Outline), from the InputData worksheet, submit data. c. From the QTD&YTD worksheet, calculate, refresh, and verify data. d. Compare your calculation results to the following figure:. 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. 2. Test your calculations: a. Clear the Sales database. b. In Optimize.xls, from the InputData worksheet, submit data. c. From the TimeBalance worksheet, calculate, refresh, and verify data.. Essbase 11.1.1: Bootcamp. 23. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(34) Lesson 4 Optimizing Data Descriptor Dimensions Exercises. 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 • Labor/Unit • Material • Matl/Unit • Overhead • Overhead Rate • Other CGS • Material Variances • Labor Variances. 24. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. d. Compare your calculation results to the following figure:.

(35) Lesson 4 Optimizing Data Descriptor Dimensions Exercises • Overhead Variances • Obsolete Charges. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. • Inventory Adjustments 2. In the Scenario dimension, replace the current variance calculations with member formulas that use the @VAR function. 3. Test your calculations: a. Clear the Sales database. b. In Optimize.xls, from the InputData worksheet, submit data. c. From the Variance worksheet, calculate, refresh, and verify data. d. Compare your calculation results to the following figure:. 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.. Essbase 11.1.1: Bootcamp. 25. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(36) Lesson 4 Optimizing Data Descriptor Dimensions Exercises. 1. Test the default top-level database calculation: a. Clear the Sales database. b. In Optimize.xls, from the InputData worksheet, submit data. c. From the TopLevel worksheet, calculate and refresh data. 2. In the Sales outline, identify members that should not be stored and set the storage property to Label Only. Repeat step 1 to verify data at the top level.. 26. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Identify members that do not require storage and optimize the database by changing data storage types:.

(37) Lesson 5 Planning Dimension Designs Exercises. Exercise 5.1. 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. Design the product and customer dimensions: 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: • FinanceRprts.xls • MfgRpts.xls • SalesRpts.xls 3. For Product and Customer, use the blank space on this page 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.1: Bootcamp. 27. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 5 Planning Dimension Designs.

(38) Lesson 6 Creating Basic Dimension Build Rules Files Exercises. Exercise 6.1. Building the Family Total Rollup. The Bigcorp IT team has provided you with text file extracts from their data warehouse for your initial dimension builds. Create a rules file to build the Family Total product hierarchy: 1. From Data Prep Editor, as your sample data source, open FamGen.txt. 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. 7. 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 • Rules file: FamGen.rul. 28. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 6 Creating Basic Dimension Build Rules Files.

(39) Lesson 6 Creating Basic Dimension Build Rules Files Exercises. 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 need to provide inputs for server, application, database, or dictionary path. • You must retrieve all records from the Products table. • Your Oracle database username and password and your Essbase username and password are the same. • After you retrieve records from the data warehouse into your rules file, continue with step 2 of the 12-step rules file creation process.. Essbase 11.1.1: Bootcamp. 29. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 10. Compare the Product dimension in the Sales outline to the following figure (entire dimension not shown):.

(40) Lesson 6 Creating Basic Dimension Build Rules Files Exercises. • 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. 30. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. • The data source contains both new and preexisting products. You must implement the appropriate maintenance options in your dimension build settings..

(41) Lesson 7 Creating Advanced Dimension Build Rules Files Exercises. Exercise 7.1. Creating Secondary Hierarchies with Parent-Child Build. 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 dimension build rules file process. • 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 • Rules file: ParChild.rul. Essbase 11.1.1: Bootcamp. 31. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 7 Creating Advanced Dimension Build Rules Files.

(42) Lesson 7 Creating Advanced Dimension Build Rules Files Exercises. Exercise 7.2. Building the Customer Dimension. Bigcorp Sales analysts require customers to be grouped by the channels OEM, Retail, and Distributor. 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. Create a rules file for building the Customer dimension: 1. Start a rules file for the Customer dimension, and use CusChan.txt 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. • Review the Customer dimension plan in Lesson 5 to ensure that you create the entire dimension.. 32. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. • Compare your Product dimension to the following figure (entire dimension not shown):.

(43) Lesson 7 Creating Advanced Dimension Build Rules Files Exercises. 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 in 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 • Rules file: Channels.rul.. Essbase 11.1.1: Bootcamp. 33. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 2. Manipulate the data source fields into the correct order for your planned build method..

(44) Lesson 7 Creating Advanced Dimension Build Rules Files Exercises. 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 user-defined attribute (UDA) for each customer.. 34. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. • Compare your Customer dimension with the following figure:.

(45) Lesson 7 Creating Advanced Dimension Build Rules Files Exercises Modify Channels.rul to load customer class UDAs:. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 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 be the last field on the right. c. Modify the text in the customer class field to look like the following figure:. d. Set the dimension build field properties for Field6.. Essbase 11.1.1: Bootcamp. 35. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(46) Lesson 7 Creating Advanced Dimension Build Rules Files Exercises 3. Modify the dimension build settings to enable UDA changes.. • Data source: CusChan.txt • Rules file: CustUDA.rul 5. Compare your Customer dimension with the following figure:. 36. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 4. Load customer UDAs to the Sales outline, and specify the following options:.

(47) Lesson 8 Loading Data Exercises. Exercise 8.1. Loading Actual Data. 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. 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 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), follow the instructions on the Actual worksheet to verify that data was loaded.. Essbase 11.1.1: Bootcamp. 37. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 8 Loading Data.

(48) Lesson 8 Loading Data Exercises. 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. Modify the saved data load profile Actual.xml, so that you can load the following data sources: Data Source. Rules File. Actual.txt. Loadcorp.rul. Forecast.txt. Loadcorp.rul. 2. Save the new data load profile as ActFcst.xml. 3. Run the saved load process. NOTE. You receive a warning message when loading Actual.txt because no data values are modified by reloading the file. 4. In CheckData.xls, follow the instructions on the Forecast worksheet to verify that data was loaded.. Exercise 8.3. Optional: Loading Transactional Units. The Bigcorp Finance group performs its month-end close on its general ledger by the 10th 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. 3. Reference missing dimensions in the rules file header. 38. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Exercise 8.2.

(49) Lesson 8 Loading Data Exercises 4. Set data load values to add to existing values.. 6. Load data to Sales, and specify the following options: • Data source: MayAct.txt • Rules file: Flash.rul 7. In CheckData.xls, follow the instructions on the Flash worksheet to verify that data was loaded.. Essbase 11.1.1: Bootcamp. 39. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 5. Validate and save the rules file as Flash..

(50) Lesson 9 Getting Started with Smart View Exercises. Exercise 9.1. Connecting to Provider Services. Before using Smart View, you must connect to Provider Services and access an Essbase server. Connect to Bigcorp Sales in Data Source Manager: 1. Run ssheet.bat (in C:\CF\Bootcamp\Scripts) to reset the Sales database. 2. In a new Excel workbook, connect to Provider Services and Bigcorp Sales.. 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, resulting in the following report:. Exercise 9.3. Customizing the Point of View. To make the report more useful, you customize the point of view (POV) with multiple products and customers.. 40. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 9 Getting Started with Smart View.

(51) Lesson 9 Getting Started with Smart View Exercises In the report created in the preceding exercise, complete the following tasks:. Dimension. Members. Scenario. Current Year. Product. • Family Total • Performance • Lightbolt • Thunderball • Value • Roadranger • Mavrider • Firebrand. Customer. • Channel Total • OEM • Retail • Distributor. 2. On the POV toolbar, make the following selections: • Current Year • LIGHTBOLT • OEM 3. Refresh the worksheet, and compare your results to the following figure:. 4. Select various combinations of product and customer, and refresh the data for each combination to ensure that the POV is working.. Essbase 11.1.1: Bootcamp. 41. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 1. Customize the POV as follows:.

(52) Lesson 10 Creating Reports with Smart View Exercises. Exercise 10.1 Creating Ad Hoc Reports The Bigcorp Finance team wants you to create an ad hoc report that shows the following information: Report Area. Dimension Members. Rows. Family Total and its children. Columns. OEM, Retail, Distributor. Point of view. Accounts, Year Tot, Scenario. 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. c. Compare your report to the following figure:. 2. Customize the POV as follows: Dimension. Members. Year Tot. Generation 3 time periods. Accounts. Gross Margin, Net Sales, Gross Sales, Units. Scenario. Prior Year, Current Year. Exercise 10.2 Updating Essbase Data Bigcorp analysts want to use Excel worksheets to submit updated forecast data to Essbase.. 42. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 10 Creating Reports with Smart View.

(53) Lesson 10 Creating Reports with Smart View Exercises Test an input worksheet by updating data, submitting data to Essbase, and executing a calculation script.. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 1. In SmartView.xls, set the display option so that “Use Excel formatting” is unavailable. 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. 4. Refresh the worksheet and compare your results to the following figure:. Essbase 11.1.1: Bootcamp. 43. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(54) Lesson 10 Creating Reports with Smart View Exercises. 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. Refresh the document. b. 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.. 44. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Exercise 10.3 Viewing Dynamic Data Points.

(55) Lesson 10 Creating Reports with Smart View Exercises. Exercise 10.4. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 3. Compare your results to the following figure:. Creating Smart Slices. Bigcorp sales representatives for OEM customers require a database query showing only current year OEM sales.. Essbase 11.1.1: Bootcamp. 45. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(56) Lesson 10 Creating Reports with Smart View Exercises. 1. In SmartView.xls, select the Sandbox worksheet and start a Smart Slice for Bigcorp Sales. 2. In the Smart Slice query designer, select the following members: Query Layout. Members. Columns. Qtr 1, Qtr 2. Rows. OEM and children. POV. Current Year, DESCENDANTS([Product]), Gross Sales. 3. Set the following query options: Option. Value. missingLabelText. 0. essIndent. Tables. numFormat. thousands separator = yes. 4. Save the Smart Slice as OEM_CurYr. 5. Create an ad hoc report from the OEM_CurYr Smart Slice. 6. Compare your results to the following figure:. Exercise 10.5. Creating a Custom Report. OEM sales managers need a custom dashboard showing OEM sales from a variety of perspectives.. 46. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Create a Smart Slice to meet their requirements:.

(57) Lesson 10 Creating Reports with Smart View Exercises Create a dashboard using Report Designer: 1. Insert a query into Report Designer based on OEM_CurYr.. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 2. Add the following report components to OEM_CurYr: • Function grid • Chart • Slider (select product lines for slider values) 3. Compare your results to the following figure:. NOTE. To resize or move charts and slider components, you must enter Design Mode in Excel. You can access Design Mode from the Control Toolbox toolbar in Excel.. Essbase 11.1.1: Bootcamp. 47. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(58) Lesson 11 Data Storage and Calculation Exercises. Exercise 11.1 Calculating the Sales Database During multidimensional calculation, input data is transformed into useful information. A thorough understanding of the process of multidimensional calculation is necessary before creating more complex calculations. 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. In the following table, record the precalculation database statistics for Sales: Statistic. Precalculation. Postcalculation. Number of existing blocks Block density Existing level 0 blocks Existing upper-level blocks 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.. 48. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Lesson 11 Data Storage and Calculation.

(59) Lesson 11 Data Storage and Calculation Exercises. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. 4. In Anatomy.xls (in C:\CF\Bootcamp\CalcIt\Anatomy), test the calculations by using the This Is a Block worksheet, and compare your results to the following figure (entire worksheet not shown):. 5. In the preceding database statistics table, record the postcalculation database statistics for Sales.. Essbase 11.1.1: Bootcamp. 49. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(60) Lesson 11 Data Storage and Calculation Exercises. Implement an iterative test of Anatomy.csc to calculate the Sales database one dimension at a time. Record the changes to the database statistics in the following table: Statistic. Data Load. Accounts. Year Tot. Customer. Product. Number of existing blocks Block density Existing level 0 blocks Existing upper-level blocks Percentage of maximum blocks existing. 50. Essbase 11.1.1: Bootcamp. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and Consultores De Integracion De Sistemas SA De CV use only. Extra Credit: Recording Detailed Statistics.

No results found