Implementation Bootcamp
Activity Guide
D64290GC10 Edition 1.0 December 2009
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.
Oracle Hyperion Planning
Implementation Boot Camp
Lab Guide
Developed in Partnership With:
Title of Presentation
Presenter’s Name
Presenter’s Title
Table of Contents
Oracle Hyperion Planning Implementation Boot Camp
Lab Guide
Housekeeping & Case Study Introduction 3
Section Objectives ... 3
Exercise 1.1 – Copy Distributed Files ... 4
Exercise 1.2 – Launch the Essbase Services ... 5
Exercise 1.3 – Introduce Case Study and Discuss Plans for building Essbase application ... 6
Essbase Modeling 11 Section Objectives ...11
Exercise 2.1 – Create new Essbase Application and Database ...12
Exercise 2.2 – Create a New Database from Files ...28
Exercise 2.3 – Add Essbase Spreadsheet Add-in for Excel and update Options ...34
Exercise 2.4 –Essbase Spreadsheet Add-in for Ad-hoc Queries and Reports ...42
Exercise 2.5 – Smart View for Office for Ad-hoc Queries and Reports ...47
Exercise 2.6 – Essbase Data Loads ...54
Exercise 2.7 – Essbase Dense/Sparse Settings and Database Properties: Dimensions, Statistics and Storage ...62
Exercise 2.8 – CALC DIM command and review of block creation and Database Statistics ...63
Exercise 2.9 – Review Blocks, Index, Dense/Sparse, Compression and Fragmentation concepts ...71
Exercise 2.10 – Member Calculations and Member Set Functions ...72
Exercise 2.11 – Correct Percent, Rate and Variance Calculations ...74
Exercise 2.12 – Dynamic Calculations ...77
Classic Planning Application Creation 78 Section Objectives ...78
Exercise 3.1 – Create Hyperion Planning Relational Database Repository, Create Planning Application and Data Source ...79
Exercise 3.2 – Model Planning Application Dimensions ...106
Exercise 3.3 – Outline Load Utility ...120
Data Form Design 121 Section Objectives ...121
Exercise 4.1 – Forms Designer ...122
Exercise 4.2 – Business Rules...135
Exercise 4.3 – Additional Form Components ...148
Security and Administration 121 Section Objectives ...121
Exercise 5.1 - Planning Security ...171
Exercise 5.2 - Life Cycle Management ...183
Appendices 189 Section Objectives ...189
Appendix A.1 – Copy Files to Virtual Image ...189
1
Housekeeping & Case Study Introduction
Section Objectives
In this section, you:
• Copy the distributed required files for completion of the exercises • Launch the services required to run Essbase Server, Essbase
Administration Services (EAS) and Smart View • Review
Exercise 1.1 – Copy Distributed Files
In this exercise, you copy the distributed files to be used for the exercises in this workbook to your version of BIC2g (Business Intelligence Challenge 2 Go) EPM Demo Image
Copy exercise files…
1. Insert the thumb drive that is distributed by instructor.
2. See Appendix A-1 for details on copying files saved on your Host system to various Virtual systems.
3. Start your Virtual Image.
Following Dialog Box displays:
This is a known issue and not a concern, click OK button
4. Copy and expand the files Planning_Training folder of the thumb drive to a new folder on the VM image: c:\BootCamp
Exercise 1.2 – Launch the Essbase Services
In this exercise, you launch the services necessary to access Essbase Server, Essbase Administration Services and Smart View.
See Appendix A.1 for specific instructions for starting the necessary services.
Start Services…
1. For the exercises in this section, the following services are needed. (see Appendix A.1 for specific instructions on starting the necessary services)
OracleOraDb11g_home1TNSListener
OracleServiceORCL
Hyperion Foundation OpenLDAP
Hyperion Foundation Shared Services
Hyperion Essbase Services 11.1.1 - hypservice_1
Hyperion Administration Services - Web Application
Hyperion Provider Services - Web Application
At end of Day, Stop Services in reverse order before Shut Down of virtual image to help avoid Shared Services corruption issues.
Exercise 1.3 – Introduce Case Study and Discuss Plans for
building Essbase application
In this exercise, you will be introduced to the case study that we will use for the entire boot camp. You will be provided with reports and a
sample input data format and asked to plan out the application structure in terms of dimensions and members.
Review the reports provided by the client …
1. Review the following reports provided by the client and with the rest of the attendees in an interactive session, agree on the Dimensions and Members that will need to be included in Essbase to produce the reports.
2. What Dimensions are required in Essbase to build reports? 3. What are the Members Hierarchies in the Dimensions? 4. What questions should we ask the client?
5. What recommendations can we make to the client?
7. Does this answer any questions? 8. Does it raise additional questions?
2
Essbase Modeling
Section Objectives
At the end of this section, you will be able to:
• Create new Essbase Application and Database • Build new Essbase Database Outline
• Use Essbase Spreadsheet Add-in to do ad-hoc queries and create reports
• Use Smart View for Office to do ad-hoc queries and create reports • Build and Run Essbase Data Load Rules
• Understand Essbase Architecture: Dense, Sparse, Blocks and Index • Learn Calculation Fundamentals
Exercise 2.1 – Create new Essbase Application and Database
In this exercise, you log in to Essbase Administration Services and create a new Application and Database.Log in to Essbase Administration Services …
1. From the Windows Start button select All Programs >
Oracle EPM System > Essbase > Administration Services > Start Administration Services Console
After selecting this path once, Start Administration Services Console should be added to your program favorites.
The Administration Services console window appears and a Login dialog box displayed:
2. Under Username, type demoadmin Under Password, type Demov52 Click OK.
3. Expand Essbase Servers so that demodrive appears Create Application
and Database…
4. From the Windows Start button select All Programs >
Oracle Right-click on demodrive and select Create > Application > Using block storage
(you can also Create Application with demodrive Server selected and
by selecting menu bar item Actions > Create > Application for
“demodrive” > Using block storage)
The Create Application dialog box displays:
5. Under Application name:, type AutoDemo and click OK.
Be patient! This may take about 30 seconds as Essbase creates new file structure for new Application and related objects and allocates additional memory as new Application is started.
6. Expand demodrive node (or double-click) so that Applications appears in next level.
7. Expand Applications node so that AutoDemo appears in next level. 8. Right-click on AutoDemo and select Create database …
The Create Database dialog box displays:
9. Under Database name: type New and click OK.
10. Expand AutoDemo node so that New appears in next level. 11. Expand New node so that Outline appears in next level. 12. Right-click on Outline and select Edit.
13. Hide/Unhide the Navigation Pane on the left by selecting View and then uncheck/check Navigation.
15. Click on button to Add a child to selected member button (see above) and enter the Dimensions: Measures, Time and Scenario.
16. Press Enter to close the input box.
17. Select Time and click on button to Add a child to selected
member
18. Enter Time dimensions members: Year, M1, M2, M3, … M12, Q1, Q2, Q3 and Q4 and press Enter to exit Input box
19. Select M10, M11 and M12 (Crtl-click each M11 and M12 or Shift-click M12) and Shift-click on button to Mark the selected member to
20. Select Q4 and click on button to Paste the marked member as a
The Outline Confirmation dialog box is displayed.
21. Click Yes.
Confirmation is useful to avoid making inadvertent changes, but can be turned off by selecting Tools > Console Options… and then selecting Outline Tools tab in the Options dialog box as displayed below.
Following the Paste, the Outline Editor shows the members in the new position:
22. Repeats steps 19-21 to move the other Month members as children of appropriate Quarter members
23. Select the Year member, right-click and select 1st option Edit
member properties …
24. The Member Properties dialog box displays, enter Default Alias of “Calendar Year” and click Next button to “Save modifications and change selection to the next member”
25. Update remaining Aliases for Time dimension using Quarter 1, January, February, March, Quarter 2, April …
26. Add Members for Measures dimension through Total Gross Sales (refer to page 6), hint: change Consolidation properties by using right-click option to Edit member properties …
27. Tag Measures dimension as Accounts and Time dimension as
28. Select one of Account members and Edit member properties …, Notice new properties under Account information
29. Select Time dimension, right-click and select Dynamic time
series…
30. Enable Y-T-D as Gen1 and Q-T-D as Gen2 and click OK
31. After Time dimension you will now see (Active Dynamic Time
Series Members: Q-T-D). Why isn’t Y-T-D included? Year is the
predefined generation name for the Y-T-D Dynamic Time Series. Since Year already exists as member in the Time dimension, Essbase
won’t allow adding a Dynamic Times Series with generation name of Year.
32. Rename the Year member to “Full Year” and enable Enable Y-T-D dynamic time series as Gen1
The Measures dimension in Outline Editor should appear as follows:
33. Add Scenario members Actual (~), Budget (~) and “Actual vs Budget” (~) with appropriate Consolidation properties.
34. While in the Member Properties dialog box for Actual vs. Budget, select the Formula tab.
35. Check the checkbox for Insert Arguments and under Math category (or near bottom of Alphabetical listing), select @VAR function, double-click @VAR to move it to the Script pane.
36. Expand the Scenario dimension and with mbrName1 highlighted, click on Actual, then with mbrName2 highlighted, double-click on Budget.
37. Click on the Verify button. Member Formula dialog box displays -
38. Click OK button. The message indicates “unexpected end of
expression”. All formulas and calc script commands need to end
with a semi-colon (;). Add a semi-colon at end of formula and click on the Verify button. Member Formula dialog box displays -
39. Click OK button, then click OK button in Member Properties dialog box.
The Scenario dimension in Outline Editor should appear as follows:
40. What are two other ways to calculate Actual – Budget? What are the pros and cons of each approach?
41. Save Outline and remain in Essbase Administration Services for following exercise.
Exercise 2.2 – Create a New Database from Files
In this exercise, you create new AutoDemo:Auto database, upload Outline from file in BootCamp folder, save Outline to server, load data from file in BootCamp folder and calculate database to be used in subsequent exercises.
Create new Database…
1. Repeat steps 8 and 9 from exercise 2.1 to create new database in AutoDemo Application called Auto
2. From the menu bar, select File > Open… and in Open dialog box, with tab on File System, navigate to C:\BootCamp\Calcs\calcbeg.otl and press OK button.
3. The outline will appear in the Outline Editor. From the menu bar, select File > Save as… and in Save As dialog box, with tab on Essbase Server, double-click on Auto, select Auto.otl and click OK button.
A Confirmation dialog box displays:
4. Click Yes button.
A Outline Editor dialog box displays:
5. Click No button.
6. In Navigation pane, select the new AutoDemo:Auto database. Right-click and select Load data…
7. In the Load Data dialog box, click on Find Data File button and in Open dialog box, with tab on File System, navigate to
C:\BootCamp\Calcs\explev0.txt and click OK button.
8. Back in Load Data dialog box click OK button to start data load. The Data Load Results dialog box displays:
9. After confirming Status of Success, click Close button.
In Navigation pane, select the AutoDemo:Auto database. Right-click and select Execute calculation…
10. Click OK button to initiate the default calculation which will take about 10 seconds to complete.
11. In Navigation pane, select the AutoDemo:Auto database. Right-click and select Preview data
The Data Preview Grid displays:
12. If you see data, this confirms data has been successfully loaded and calculated at the top level of each dimension. The number shown is followed by E8, so it is 205,939,054.
13. From Navigation Pane expand AutoDemo node so that Auto appears and expand Auto node so that Outline appears, right-click on Outline and select View. Notice the following items:
- Measures: many Metrics, but no formulas, just examples
- Time: PTD members using shared members, “No Time” member - Scenario: ScenarioCalculations members
- Total Dealers: Regions alternate hierarchy, “No Dealer” member - Department: “No Department” member
- Products: “No Product” member
- US Regions: Attribute dimension, go back to Total Dealers and select AW 001, notice UDA, right-click and View Member
Properties, notice attribute of “West US”, right-click and Remove split view, to close Member Properties pane
14. Leave the Essbase Administration Services console open to review the outline as we work through other Day 1 exercises.
Exercise 2.3 – Add Essbase Spreadsheet Add-in for Excel and
update Options
In this exercise, you add the Essbase Spreadsheet Add-in to copy of Excel on the Demo image using file from EssbaseClient\bin directory.
Add Essbase Spreadsheet Add-in…
1. From the Windows Start button select All Programs >
Microsoft Office > Microsoft Office Excel 2007
After selecting this path once, Microsoft Excel should be added to your program favorites.
2. Select Microsoft Office button (in upper left hand corner) and select Excel Options at bottom of dialog box.
Select Add-ins on the left pane. The Add-ins pane displays on the right:
15. With Manage: Excel Add-ins selected in the dialog box at the bottom, click on adjacent Go… button.
16. Click on Browse… button to the right.
Browse to C:\ORCL\HYSL\products\Essbase\EssbaseClient\bin and select essexcln.xll file
17. Press OK button.
Update the Default Options…
7. Select Essbase > Options
8. On Display tab, under Cells, select Adjust Columns. 9. Clink on Global tab.
Exercise 2.4 – Essbase Spreadsheet Add-in for Ad-hoc Queries
and Reports
In this exercise, you will familiarize yourself with using the Essbase Spreadsheet Add-in to create ad-hoc queries and reports in Excel.
Connect to an Essbase Database…
1. From the Excel 2007 Add-Ins ribbon, select Essbase >
Connect…
The Essbase System Login dialog box displays.
2. Enter Username: demoadmin, Password: Demov52 and click OK button.
3. Select Application/Database: AutoDemo:Auto and click OK button.
4. Do default Retrieve using Essbase > Retrieve or double-click in the Excel grid. The value of 205,939,054 represents one cell in the cube at the Dimension level of each of the 6 dimensions represented in the Auto database. Notice that this is the exact same number as shown in the Data Preview Grid.
5. Double-click on Measures (or use Essbase > Zoom In). You will see the 2 Gen1 members under Measures.
6. Do Essbase > Flashback and then Essbase > Options… and on Zoom tab, change Zoom In to All Levels and click OK.
7. Again zoom in on Measures. Now you will see all levels of the Measures dimension, similar to a P&L statement. Be careful not to use the All Levels or the Bottom Level settings when doing zoom in on a large dimension as it will bring back more data than you can reasonably analyze and may take a long time to complete or error out if there are more rows than Excel can accommodate.
8. Select Essbase > Options… and on Zoom tab, change Zoom In back to Next Level and click OK.
9. Select “Fleet Units Sales” and Essbase > Keep Only.
10. Double-click on “Total Dealers” and Pivot the Measure member “Fleet Unit Sales” from Rows to a Column using Essbase > Pivot or by drag and drop. Double-click on “North America”. Double-click on Dealers. Double-click on “Auto World”. Double-click on Regions. click on “Western US”. click on Regions. Double-click on “Western US”. You can see the Regions alternate hierarchy uses shared members.
11. Select “Total Dealers” and Essbase > Keep Only and then
Essbase > Member Selection…, in the Member Selection dialog
box select member “Total Dealers” and click Add-> button so that “Total Dealers” appears under Rules:
12. Right-click on “Total Dealers” under Rules: and select Subset…, click on the Add as AND Condition button.
The Subset Dialog box should be as below:
13. Click on OK. On Member Selection dialog box, under Rules:, under “Total Dealers”, you will now see “Attribute of UDAWest”, click on
OK. The 5 members of “Total Dealers” dimension with UDA of
UDAWest will be in each row. Retrieve to see data.
14. Right double-click on “AW 001” (or use Essbase > Zoom In), continue zooming out to “North America”, then zoom in and zoom in again on Regions.
15. In new column (cell G1), enter the Attribute dimension “US Regions” (or use Essbase > Member Selection…, and select Dimension “US Regions”), then zoom in on “US Regions” and pivot the attribute dimension to columns.
You would not typically include both alternate hierarchies AND attribute dimensions for the same members. Discuss why you might use one of the other.
16. Delete row 2 attribute dimension members, zoom-in on Dealers, zoom-in on “Auto World”, select AW 001 and Essbase > Keep
17. In place of Departments, type cc6350 and do a Retrieve. If you know the names of members, you can simply type them in. Notice that the data value doesn’t change. CC6350 is the only department with Fleet Unit Sales data.
18. In place of Products, type 155 and do a Retrieve.
The Essbase Error dialog box displays:
Essbase only recognizes Text members and Excel be default recognizes number strings as Numbers.
19. In place of 155, type ’155 (with single quote in front) and do a
Retrieve. Now Essbase recognizes 155 as a member. In place of ‘155, type 155-1000-10 (without single quote in front) and do a Retrieve. With dashes in the string, Excel now recognizes this as a Text 20. Zoom In on Time and Pivot the Total Dealers member “AW 001”
from Rows to a Column.
21. Why does PTD have no value? Check the outline for an explanation. 22. Type PTDApr in place of PTD and do a Retrieve.
23. In new row below Time, type y-t-d(apr) and do a Retrieve. This uses Dynamic Time Series to generate the same values as created by using the PTD members. Why use PTD members versus Y-T-D Dynamic Time Series?
24. Select Calendar Year and Essbase > Keep Only.
25. Zoom In on Calendar Year and each of the Quarters so all months are displayed.
26. Select Essbase > Options… and on Display tab under Aliases, select checkbox to Use Aliases, click OK and do a Retrieve to display member aliases.
27. Select Scenario, and use Alt + double-click to Zoom In across Rows, select 2003 and Scenario (Ctrl+click) and Essbase > Remove
Only.
28. Zoom In on 2004 and ScenarioCalculations.
Why does Actual/Budget only have values for the months? See Outline to find an answer. What are options to fix this? 29. Keep Only “2004 Actual”, “2004 Budget” and “2004 Forecast”
30. Copy the values of Jan:Jul from “2004 Actual” to “2004 Forecast” and add new value for Aug -> “2004 Forecast” of 48. Then do
Essbase > Lock, Essbase > Send and do a Retrieve.
31. Create Report1 (see page 6) using Essbase Spreadsheet Add-in. 32. Select Essbase > Disconnect…
Exercise 2.5 – Smart View for Office for Ad-hoc Queries and
Reports
In this exercise, , you will familiarize yourself with using the Smart View for Office to create ad-hoc queries and reports in Excel.
Connect to an Essbase database…
1. From the Excel 2007 Hyperion ribbon, select Data Source Manager.
The Smart View Data Source Manager window displays.
2. Use the Switch button (on left) to select Common Provider
Connections in lieu of Independent Provider Services.
Then under Pre-defined drop-down, select All in lieu of Pre-defined
3. Select the Oracle Essbase connection and expand to display the
demodrive node, select demodrive.
4. Enter Username: demoadmin, Password: Demov52 and click the
Connect button.
5. Expand AutoDemo node, right-click on Auto and select Ad-Hoc
Analysis.
You can also Add to Favorites (for End-Users) or Add To Pre-defined (for Administrators).
6. The default query (Measures and Time) displays, with the top level in the other dimensions included in the Point of View (POV).
7. Zoom In on Time using double-click (or use the Zoom In button). 8. Select “Calendar Year” and use the Keep Only button.
9. Type “Fleet Unit Sales” in place of Measures and click on Refresh button.
10. Click on Total Dealers drop down in POV and select …
The Member Selection dialog box displays:
11. Select Total Dealers, AW 001, AW 002, AW 003 and AW 004 and click the Add button and then the OK button.
The values are added to POV as displayed below:
12. Select AW 001 and click Refresh button.
13. Left-click on Products in POV and drag to a colum.
14. Use Select “Calendar Year” and Pivot button to switch it to row and select “Fleet Sales Units” and Pivot button to switch it to column.
15. Zoom In on “Calendar Year”, Q1, Q2, Q3 and Q4.
16. Click on Undo button 4 times (to Zoom Out from quarters) and click on Redo button 3 times to Zoom In on Q1 thru Q3. Undo is enabled on the Options dialog box and the Ad Hoc tab – “In Number of Undo Actions, specify the number of permissible Undo operations — 2 through 100”.
17. Right-click on any cell and select Hyperion > Ad Hoc Analysis >
Change Alias Table.
18. Select Default alias and click OK button.
Notice that aliases are displayed in grid as well as in the POV. 19. Select Products in grid and Member Selection button 20. In Member Selection dialog box, 1st click Remove button to
remove Products from under Selection, then expand “Total Products”, SUV and “Bump Jumper”, select “Bump Jumper EUV” and click the Add button and then the OK button.
21. Right-click and drag “Bump Jumper EUV” from grid back to the POV (or use Hyperion > Ad Hoc Analysis > Pivot to POV) 22. Left-click and drag Scenario from the POV to a column and
Right-click and drag “Fleet Unit Sales” from grid back to the POV 23. In the POV, select Department CC6350 (under Functional > “Sales
and Mktg” > Sales)
24. Select Scenario in grid, Member Selection button, select “2004 Actual”, “2004 Budget” and “2004 Forecast” (exclude Scenario) and Refresh. You should recognize “2004 Forecast” data entered earlier. 25. Revise Aug -> “2004 Forecast” from 48 to 51, notice cell changes to
yellow to indicate value has been updated.
26. Click on Submit Data button (this combines Lock and Send into one step), notice cell changes back to white to indicate there are no outstanding updates.
27. Create Report1 (see page 6) using Smart View for Office. 28. In Smart View Data Source Manager, right-click on
AutoDemo:Auto, click on Disconnect > Data Source
Connection followed by Disconnect > Common Provider Connection.
Exercise 2.6 – Essbase Data Loads
In this exercise you will create new Data Load Rules for loading a text files to Essbase.
Log in to Essbase Administration Services …
1. Log on to Essbase Administration Serices (follow steps from Exercise 2.1, page 11)
2. Expand Essbase Servers node, demodrive node, Applications node, and AutoDemo node, right-click on AutoDemo and select Create
database …
The Create Database dialog box displays:
Under Database name type AutoPlan and click OK. 3. Repeat steps 2 through 5 from exercise 2.2 to open
C:\BootCamp\Model\FinishedAutoplan.otl from File Server and Save to AutoPlan.otl on Essbase Server.
4. Review AutoPlan outline making note of dimensions and format of members.
5. Right-click on AutoPlan and select Create > Rules file, the Date Prep Editor pane displays.
6. From menu bar select File > Open data file (NOT Open), select
File System tab and select C:\BootCamp\Load\LoadACT.txt file
7. Select Options > Associate outline (or use Associate outline shortcut button) and confirm AutoDemo:AutoPlan is selected and click OK button.
8. Select Options > Data source properties (or use Data source properties shortcut button). The Delimiter tab has Delimiter set as
Tab (the default), for this tab delimited file. If appropriate, you can
select other delimiter including Custom delimiters like | (pipe) or specific Column width.
9. Select the Header tab. Since the data file has some month number labels in Row 1, we want to ignore it – under Header lines change
Number of lines to skip from 0 to 1.
Data source header records can be used if a header row contains all the Data Load or Dimension Build field names.
10. Are all dimensions represented in data?
Select Options > Data load settings (or use Data load setting shortcut button). Leave Data Load Values tab with default -
Overwrite existing values. On Header Definition tab, expand
Dimension > Scenarios and double-click on Actuals member so that “Actuals” appears after Header > Name, then expand Dimension > Versions and double-click on Final member so that “Final” appears after Header > Name
Data Load Setting dialog box should look as follows:
Click OK button.
11. Select View and make sure Data load fields is checked. This is a toggle, with either Data load fields or Dimension build fields checked (or use Data load fields shortcut button).
12. Select Field > Properties and the Field Properties dialog box displays for Field number:1.
13. Select the Data Load Properties tab and double-click on Dimension > Year so that “Year” appears after Data load > Field definition.
Field Properties dialog box should look as follows:
14. Click Next >> button and proceed to map fields to dimensions: Field2 – Total Dealers
Field3 – Entity Field4 – Products Field5 – Account
Field6 – M1 (expand Period > YearTotal > Q1) Field6 – M1 Field7 – M2 Field8 – M3 Field9 – M4 Field10 – M5 Field11 – M6 Field12 – M7 Field13 – M8 Field14 – M9 Field15 – M10 Field16 – M11 Field17 – M12
15. What do we do with Field18?
Select checkbox to Ignore field during load
16. Click OK button.
In Data Prep Editor pane all columns should now show corresponding Dimension or Member name, or in case of Field18, the values (“No Time” in row 1) should be grayed out
17. Are any changes needed to map data to outline members?
18. Select Feild1 and then select Field > Properties. On the Global
Properties tab, enter Replace – 20 and With – FY as shown
19. Click OK button.
In Data Prep Editor pane all values under Field1/”Year” should now appear as FY04.
20. Change Field Properties for Field3/”Entity” to include prefix of “CC” and click OK button.
21. Select Options > Validate (or Validate shortcut button) The Validate Rules File confirmation box should be displayed.
Click OK button.
22. Select File > Save As… and save in Essbase Server, Look in: AutoPlan as File name: LoadAct
23. Right-click on AutoPlan, select Load data… and the Data Load dialog box displays.
24. Click on Find Data File button and select File System tab and select C:\BootCamp\Load\LoadACT.txt file and click OK button. 25. Click on Find Rules File button and select Essbase Server tab
and select LoadAct.rul file and click OK button.
Data Load dialog box should look as shown below:
26. Click the OK button to initiate the data load.
Data Load Results dialog box should be displayed indicating Status of Success:
Click Close button.
27. On your own, create a new data load rule for AutoDemo:Auto using C:\BootCamp\Load\datafield.txt file.
What is different? What do we do?
28. Keep Essbase Administration Services Console open for following exercise.
Exercise 2.7 – Essbase Dense/Sparse Settings and Database
Properties: Dimensions, Statistics and Storage
In this exercise you will review Dense/Sparse settings of the AutoDemo:Auto database, calculate the block size and review Database Properties.
Review Essbase Outline
Properties…
1. From the Essbase Administration Services Console Navigation Pane, select AutoDemo:Auto Outline (under Auto), right-click and select
Edit, the Outline Editor pane displays.
2. Select Properties tab and scroll down to bottom to review
Dimension Storage Types
3. Select Outline tab and expand the dense dimensions by selecting dimension name and then Outline > Expand to descendants. 4. Count the stored members for each dense dimension remembering to
ignore Shared Members and Dynamic Calc Members and Implied Shared Members.
5. Calculate the Block Size (Dense Dimension1 stored members x Dense Dimension2 stored members x … x 8 bytes/member).
6. From the Essbase Administration Services Console Navigation Pane, select AutoDemo:Auto Database, right-click and select Edit >
Properties, the Database Properties pane displays.
7. Select Dimensions tab and review Type, Members and Member Stored columns. Notice that no members are stored for Attribute Dimensions. Do values of Members Stored for Dense dimensions match your calculations from step 4?
8. Select Statistics tab, does Block Size match your calculations from step 5?
Average clustering ratio of 1.0 indicates no fragmentation. If you experience fragmentation that degrades performance, consider a database restructure or alternatively, Export Lev0 data, Clear database, Load Export file and Calculate.
9. Select Storage tab and review information. When is RLE or ZLIB compression appropriate?
10. Based on understanding of Dense and Sparse, how should data load files be formatted to optimize performance?
Exercise 2.8 – CALC DIM command and review of block creation
and Database Statistics
In this exercise you will create a Rollup Calc script using CALC DIM command and iteratively add additional dimensions to gain better understanding of how CALC DIM command works and when blocks are created.
Start by Clearing data…
1. From the Essbase Administration Services Console Navigation Pane, select AutoDemo:Auto database, right-click and select Clear > All
data. The Confirm Database Clear dialog box displays. Clink Yes
button. Database Clear dialog box will then be displayed. Click OK button.
2. Right-click on Auto database and select Create > Calculation
script, the Calculation Script Editor pane displays.
3. On bottom left-hand side of Calculation Script Editor pane, make sure Insert Arguments is checked and with Categorical tab selected, expand Functional and scroll down and double-click on SET UPDATECALC
Edit command to be SET UPDATECALC OFF; and press Enter to go to 2nd line. Review Essbase Technical Reference to understand details for this command.
4. Add 2nd command – SET MSG DETAIL; and press Enter to go to 3rd line. Review Essbase Technical Reference to understand details for this command.
5. Add 3rd command – CALC DIM(dimlist); with dimlist highlighted, right-click on Measures and select Insert member name.
6. From menu bar select Syntax > Check syntax (or use Check syntax shortcut button).
The Calculation Script Editor confirmation box should be displayed:
If there are any errors, they should be displayed in the Messages pane. 7. From menu bar select File > Save as…, and save in Essbase
Server, Look in: Auto as File name: Rollup and click OK button.
8. Before we go to Excel, delete the Application log file. Right-click on AutoDemo Application and select Delete > Log.
A confirmation dialog box displays:
Click OK button.
9. Open new Excel workbook, from Hyperion ribbon 1st confirm that for Options on Ad Hoc tab that the checkbox Suppress Rows No
Data/#Missing is not checked. Select Data Source Manager,
Connect to Provider Services, navigate to AutoDemo:Auto database, right-click and select Ad-Hoc Analysis.
10. Configure the following grid, enter data values shown below and Click on Submit Data button.
11. On the Add-Ins ribbon, select Hyperion > Calculation options
> Calculate.
12. Select Calculation Script – Rollup and Cube – Auto and click the
Launch button.
Click OK button.
13. From Hyperion ribbon, click on Refresh button. New values for “Fleet Sales” and “Auto Sales” are displayed.
14. How many blocks exist in the database? From Essbase Administration Services Console, right-click on AutoDemo Application and select View > Log.
15. Modify Rollup.csc adding Time to CALC DIM command - CALC DIM ("Measures","Time");
Save revised Calc Script, right-click on Auto database and select
Execute Calculation… .
Select Rollup and click OK button.
16. In Excel, click on Refresh button. New values for Q1 and “Calendar Year” are displayed.
17. How many blocks exist in the database? From Essbase Administration Services Console, right-click on AutoDemo Application and select View > Log.
18. Modify Rollup.csc adding “Total Dealers” to CALC DIM command. Save revised Calc Script, and execute calculation. How many blocks exist in the database? View the Outline and the “Total Dealers” hierarchy to help determine the correct answer. Remember to consider Shared Members. Then view log file to confirm results. 19. Modify Rollup.csc adding Products to CALC DIM command. Save
revised Calc Script, and execute calculation. How many blocks exist in the database? View the Outline and the Products hierarchy to help determine the correct answer. Then view log file to confirm results. 20. Modify Rollup.csc adding remaining Scenario and Department
dimensions and save revised Calc Script. This will be used later in subsequent exercises.
21. Clear All data from AutoDemo:Auto database (see step 1 for details). 22. Load data from C:\BootCamp\Calcs\explev0.txt file and calculate
database using (default) calculation (see steps 6-10 in Exercise 2.2 for details).
23. In Excel, start with query for Measures and Time and Sparse dimensions “2004 Actual”, “AW 001”, “CC6350” and “155-1000-10”. Change Options on Ad-Hoc tab to Zoom-In All Levels and Zoom In on Measures and Time. Notice that 12 of 18 Stored Time members have data and 19 of 55 Stored Measures members have data. The block density based on this one block would be 12/18 * 19/55 or 23%. Check AutoDemo:Auto Database Statistics and see that it shows average Block Density of 25.46%. This is actually a reasonable value for many applications.
Exercise 2.9 – Review Blocks, Index, Dense/Sparse,
Compression and Fragmentation concepts
In this exercise you will review a series of worksheets to reinforce the concepts discussed earlier regarding Essbase Architecture, with specific related to the AutoDemo:Auto database.
Open Excel Architecture workbook…
1. Open C:\BootCamp\Calcs\123Arch.xls. 2. Review and discuss the initial 5 worksheets:
- Our Block View - Our Index View
- Basic Consolidation D-S Stats - Compression
- Fragmentation
3. Prepare for next exercise by leaving 123Arch.xls open and from the Essbase Administration Services Console Navigation Pane, select AutoDemo:Auto database, right-click and select Clear > All data. The Confirm Database Clear dialog box displays. Clink Yes button. Database Clear dialog box will then display. Click OK button.
Exercise 2.10 – Member Calculations and Member Set Functions
In this exercise you will be introduced to Member Calculations and Member Set FunctionsOpen Excel Architecture workbook…
1. Open C:\BootCamp\Calcs\123Arch.xls and select Member Calc worksheet.
2. Right-click on Member Calc worksheet name, select Move or
Copy… and select Before sheet: Member Calc and make sure to
select Create a copy checkbox as shown below.
3. Select Data Source Manager and connect to AutoDemo:Auto database using right-click and selecting Ad-hoc Analysis.
A Smart View dialog box displays:
4. Copy the data values from original Member Calc worksheet to the Member Calc (2) worksheet and Click on Submit Data button. 5. From Administration Services Console Navigation Pane, right-click
on Auto database and select Create > Calculation script, the Calculation Script Editor pane displays.
6. On bottom left-hand side of Calculation Script Editor pane, make sure
Insert Arguments is checked and with Alphabetical tab selected,
expand Functional and scroll down and double-click on SET UPDATECALC. Change to SET UDATECALC OFF;
7. On top left-hand hand side of Calculation Script Editor pane, make sure Use Aliases is checked, expand Measures and “Net Sales” and right-click on “Total Cost of Sales (Alias: COS)” and select Insert member name. Validate/Check syntax and File > Save As… MembCalc.csc in Essbase Server.
8. Execute MembCalc.csc
9. In Smart View click on Refresh to see results of calculation. Why are there no results for “Total Cost of Goods Sold”?
10. Revise MembCalc.csc adding the children of “Total Cost of Goods Sold”:
"Incentives & Rebates"; "Marketing";
"COS";
11. Save and execute revised MembCalc.csc do Smart View Refresh. You could also use Member Set function @ICHILDREN(“COS”);
For more than one level use @IDESCENDANTS. Member Calcs provide finer level of detail than available with CALC DIM;
Exercise 2.11 – Correct Percent, Rate and Variance Calculations
In this exercise you will fix issues related to incorrect Percent, Rate and Variance calculations resulting from default consolidation of results.Reload Data…
1. Load data from C:\BootCamp\Calcs\explev0.txt file and calculate database using (default) calculation (see steps 6-10 in Exercise 2.2 for details).
2. Open C:\BootCamp\Calcs\123Arch.xls, select Rates and
consolidations worksheet and connect to AutoDemo:Auto database using right-click and selecting Ad-hoc Analysis.
Is anything wrong with the calculations?
3. Select “AW 001” and Zoom Out, select “Auto World” and Zoom In. Is there anything else that is wrong with the calculations?
1st let’s fix “Rebate % of Sales”
calculation …
4. What should the calculation for “Rebate % of Sales” be? Is it obvious or do we need to ask the client?
Think about this carefully from a business perspective.
5. From Administration Services Console Navigation Pane, right-click on Outline under Auto:Demo:Auto database and select Edit.
6. In Outline Editor, expand Measures, Metrics and “Sales Metrics”. Right-click on “Rebates % of Sales” and select Edit member
properties…. In Member Properties dialog box, select Formula tab
and enter formula - "Rebates" % "Auto Sales";
7. Click Verify button, click OK button for Member Formula success dialog box , click OK button for Member Properties dialog box and
Save button in Outline Editor.
8. Recalculate database using (default) calculation and in Smart View click Refresh. Did the results for “Rebate % of Sales” change? Why not?
9. Recalculate database using Rollup.csc calculation (note: SET
UPDATECALC OFF; turns Intelligent Calculation off) and in Smart View click Refresh. Are results now correct? Why not?
10. In Outline Editor, right-click on “Rebates % of Sales” and select Edit
member properties…. In Member Properties dialog box, select Information tab and change Two-Pass calculation: from false
to true, click OK button for Member Properties dialog box and Save button in Outline Editor.
Restructure Database Options dialog box is displayed:
Select All data (default) and click OK button.
11. Recalculate database using Rollup.csc calculation and in Smart View click Refresh. Are results now correct? Why not?
12. Revise Rollup.csc adding CALC TWOPASS; and This explicitly forces Two-Pass calculations. Recalculate database using Rollup.csc calculation and in Smart View click Refresh. Are results now correct? Note that CALC ALL; automatically forces any Two-pass
calculations. Are there other options to make correct calculation? 13. In Outline Editor, right-click on “Rebates % of Sales” and select Edit
member properties…. In Member Properties dialog box, select Information tab and change Two-Pass calculation: from true to false, change DataStorage from Store Data to Dynamic Calc,
click OK button for Member Properties dialog box and Save button in Outline Editor. For Restructure Database Options dialog box, select All data (default) and click OK button.
14. Revise Rollup.csc by commenting out CALC TWOPASS - /* CALC TWOPASS; */. Save and recalculate database using Rollup.csc calculation and in Smart View click Refresh. Are results correct? Dynamic Calc using a different calculation order that often resolves percent and rate calculation issues. In some cases you may also need Dynamic Calc and Two-Pass calculation.
Now let’s fix the Price roll-up issues …
15. Any ideas? Dynamic Calc won’t work, because then the Lev0 Price input data is lost. Two-Pass won’t work because ???? Seems to work! 16. The solution is something called a Back Calc, recalculating after the
Main Rollup.
17. Revise Rollup.csc by adding:
(“Fleet Price” = “Fleet Sales” / “Fleet Unit Sales”; “Dealer Price” = “Dealer Sales” / “Dealer Unit Sales”;)
Back Calc. Save and recalculate database using Rollup.csc calculation and in Smart View click Refresh. Are results correct?
Finally let’s fix “2004 Actual vs 2004 Budget” …
18. Select the Dynamic Retrieve worksheet and click Refresh. Is anything wrong?
19. In Outline Editor, expand Scenario and ScenarioCalculations, right-click on “2004 Actual vs 2004 Budget” and select Edit member
properties…. In Member Properties dialog box, select
Information tab and change DataStorage from Store Data to Dynamic Calc, click OK button for Member Properties dialog box
and Save button in Outline Editor. For Restructure Database Options dialog box, select All data (default) and click OK button.
20. When changing member(s) to Dynamic Calc there is no need to recalculate the database. Select the Dynamic Retrieve worksheet and click Refresh. Is the data still wrong? Think about the calculation order.
21. In Outline Editor, right-click on “2004 Actual vs 2004 Budget” and select Edit member properties…. In Member Properties dialog box, select Information tab and change Two-Pass calculation: from false to true, click OK button for Member Properties dialog box and Save button in Outline Editor. For Restructure Database Options dialog box, select All data (default) and click OK button. 22. Select the Dynamic Retrieve worksheet and click Refresh. Is the data
correct? Select Dynamic Variance worksheet to confirm numbers are correct. Anything else that needs to be fixed? Are higher Rebates good?
Expense Reporting …
23. In Outline Editor, expand Measures, “Net Sales”, “Total Costs of Sales” and “Incentives and Rebates”, right-click on Rebates and select Edit member properties…. In Member Properties dialog box, select Information tab and change Variance reporting
expense: from false to true, click OK button for Member
Properties dialog box. Repeat for ““Rebates % of Sales” and click
Save button in Outline Editor. For Restructure Database Options
dialog box, select All data (default) and click OK button. 24. Select the Dynamic Retrieve worksheet and click Refresh. 25. From Data Source Manager, disconnect from Data Source
Connection and Common Provide Connection (see end of
Exercise 2.12 – Dynamic Calculations
In this exercise you will learn how to set Dynamic Calc Members and understand their impact on database block size.
Change Time consolidations to Dynamic Calc…
1. In Outline Editor, expand Time and “Calendar Year”, select Q1, then Q2, Q3 and Q4 using Ctrl+click, right-click and select Edit member
properties…. In Member Properties dialog box, select
Information tab and change DataStorage from Store Data to Dynamic Calc, click OK button for Member Properties dialog box.
Repeat for “Calendar Year” and click Save button in Outline Editor. For Restructure Database Options dialog box, select All data
(default) and click OK button.
2. From the Essbase Administration Services Console Navigation Pane, select AutoDemo:Auto Database, right-click and select Edit >
Properties, the Database Properties pane displays.
3. Select Dimensions tab and notice that Members Stored decreased from 18 to 13. Select Statistics tab, notice that Block Size has decreased by almost 30%. Select the Dynamic Retrieve worksheet and click Refresh. Notice that query response time hasn’t noticeably changed.
3
Classic Planning Application Creation
Section Objectives
At the end of this section, you will be able to:
• Create new Planning Database, Application and Data Source • Model Dimensions and Members in new Planning Application • Use Outline Load Utility to load Metadata to Planning Application
Exercise 3.1 – Create Hyperion Planning Relational Database
Repository, Create Planning Application and Data Source
In this exercise you will create the Hyperion Planning Relational
Database Repository which stores Planning metadata and security and then a Data Source. Each Planning application must be associated with a Data Source, which links the relational database and the Essbase server.
Start Services…
1. For the exercises in this section, start following services (see Appendix A.1 for specific instructions on starting the necessary services)
OracleOraDb11g_home1TNSListener
OracleServiceORCL
Hyperion Foundation OpenLDAP
Hyperion Foundation Shared Services
Hyperion Apache 2.0
Hyperion Workspace - Web Application
Hyperion Workspace - Agent Service
Hyperion RMI Registry
Hyperion Planning - Web Application
Hyperion Essbase Services 11.1.1 - hypservice_1
Hyperion Administration Services - Web Application
At end of Day, Stop Services in reverse order before Shut Down of virtual image to help avoid Shared Services corruption issues.
Start at Demo Drive Homepage…
2. Double-click on the Demo Drive Homepage shortcut found on the Demo Drive desktop (see below)
Create Tablespace…
3. On the left-hand side of page, expand Oracle Database
DemoUtilities and click on Create Tablespace to display
C:\Home\CreateTablespace.html page on right as shown below.
4. Click on Create Tablespace link at the bottom of page on the right to open
C:\DemoUtilities\3PS\Database\Oracle\create_tablespace\Admin folder
Double-click on RunCreateTablespace.exe
Create Tablespace Utility dialog box displays:
5. Enter Tablespace Name - AutoPlan and click OK button.
Click OK button and wait several seconds.
Process finished dialog box displays:
Click OK button and leave Create Tablespace Utility dialog box open.
6. Return to C:\Home\CreateTablespace.html page and click on Logs
link on the right to open
C:\DemoUtilities\3PS\Database\Oracle\create_tablespace\Logs folder
Double-click on log.txt to open file in Notepad.
7. Scroll down to end of file and confirm that Tablespace created successfully.
8. Close the log.txt file and
C:\DemoUtilities\3PS\Database\Oracle\create_tablespace\Logs folder 9. Create EPMa tablespace repeating steps 4-7 using EMPaPlan instead
of AutoPlan.
10. Click on Quit button to close Create Tablespace Utility dialog box.
Quit Pressed dialog box displays:
Click OK button 11. Close the
C:\DemoUtilities\3PS\Database\Oracle\create_tablespace\Admin folder
Create User…
12. Back on Homepage, on the left-hand side of page, expand Oracle
Database DemoUtilities and click on Create User to display
13. Click on Create User link at the bottom of page on the right to open C:\DemoUtilities\3PS\Database\Oracle\create_user\Admin folder
14. Double-click on RunCreateUser.exe
15. Enter Oracle user – AutoUser and User password –
Demov52. Leave default entries USERS and TEMP unchanged and
click OK button.
OK Pressed dialog box displays:
Click OK button and wait several seconds.
Process finished dialog box displays:
16. Return to C:\Home\CreateUser.html page and click on Logs link on the right to open
Double-click on log.txt to open file in Notepad.
17. Scroll down to end of file and confirm that Grant succeeded..and
Commit complete messages displayed. Do not be concerned about
error - ORA-30041: Cannot grant quota on the tablespace
18. Close the log.txt file and
19. Click on Quit button to close Create User Utility dialog box.
Quit Pressed dialog box displays:
Click OK button
20. Close the C:\DemoUtilities\3PS\Database\Oracle\create_user\Admin folder
Create Data Source…
21. On the left-hand side of Home page, collapse Oracle Database
Utilities and click on Home
Notes:
- you can also access Oracle EPM Workspace directly from Internet Explorer Favorites
- 1st time you access Workspace, you should see message “Pop-up blocked. To see this pop-up or additional options click here …”. Select “Always Allow Pop-ups from This Site…” or Setting > Turn Off Pop-up Blocker
http://demodrive:19000/workspace/index.jsp page displays:
Enter User Name: demoadmin and Password: Demov52
23. Click on Navigate menu (in upper left-hand corner), select
Administer > (scroll down) Classic Application Administration > Planning Administration
Classic Application Wizard page displays:
Click Manage Data Source button.
Collapse pane on left and click Create Data Source button. 24. Data Source definition page displays:
Enter values as shown above:
Data Source Name *: AutoPlan Select Database Platform – Oracle Application Database
Server *: demodrive Port *: 1521
Database *: ORCL User *: AutoUser Password *: Demov52
Essbase Server
25. Click Validate Database Connection button on bottom left. Message at top should be displayed:
The database connection was successful.
Click the
Server *: demodrive
User *: demoadmin Password *: Demov52
after message to clear message.
Click Validate Essbase Connection button on bottom right. Message at top should be displayed:
The Essbase connection was successful.
Click the after message to clear message. Click Finish button in bottom right-hand corner.
The previous page with message at top should be displayed:
Data source AutoPlan was created successfully
Click the after message to clear message. Create
Application…
26. From Classic Application Wizard page, following successful creation of the AutoPlan Data Source, click the Create Application button in the bottom right-hand corner.
Enter values as shown above:
Data Source – AutoPlan Application – AutoPlan
Shared Services Project – Planning Instance – PLN
Sample Application unchecked
Click the Next button.
Enter values as shown above:
Base Time Period – 12 Months Fiscal Start Year – 2004
Fiscal Start Month – January
Weekly Distribution – 4-4-5 Distribution Total Years – 3
Click the Next button.
29. Page displays with Currencies tab:
Under Will your application support multiple currencies? Select
No
Click the Next button.
Enter values as shown above:
Plan Type 1 – AutoFin Plan Type 2 – AutoHR
Plan Type 3 – unchecked and Name deleted
Click the Next button.
31. Page displays with Finish tab displaying summary of selected settings:
Click the Finish button.
Be patient! This process will require ~ 1 minute.
Click on 1st link to refresh workspace Navigate->Applications menu
32. At this point the Planning tables will be created in the Planning Relational Database Repository. To view tables, click Start > All
Right-click on Connections and select New Connection 33. The New / Select Database Connection dialog box displays:
Enter:
Connection Name – AutoPlan User – AutoUser
Password – Demov52
Change SID from xe to ORCL
Click Save button and then Connect button 34. On left-hand pane, expand AutoPlan and Tables.
Select HSP_CALENDAR table and on right-hand pane select the
You will see information entered during the Create Application process such as FIRST_YEAR - 2004 and NUM_YEARS – 3 Close SQL Developer.
Register Application…
35. Click on Navigate > Applications > (scroll down) Planning > AutoPlan
The 1st time it will take a number of seconds for applications to appear after you select Applications.
The AutoPlan page displays.
36. From menu bar on AutoPlan page, select Administration >
Register Application G
Select AutoPlan and click Register Application button.
For Select Shared Services Project: select Planning and click
OK button.
Back on Register Application page message at top should be displayed:
Applications successfully registered.
Click the after message to clear message. Click Finish button in bottom right-hand corner. Create Database…
37. Click on Navigate > Applications > (scroll down) Planning > AutoPlan. From menu bar on AutoPlan page, select
Administration > Manage Database B
Manage Database page displays:
Select Database checkbox and click Create button.
Click Create button.
Cube Refresh/Create in Progress displays:
When Create Complete message displayed, click Finish button. 38. At this point the Essbase databases related to AutoPlan application
have been created. Open Essbase Administration Services Console and View AutoPlan:AutoFin Outline to see Dimensions and Members.
Exercise 3.2 – Model Planning Application Dimensions
In this exercise you will learn how to change member names and aliases, add members to existing dimensions, add Account members and understand Account Types, add Custom Dimensions and add Attribute Dimension.Update Period names and aliases…
1. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab and from Dimensions drop
down list select Period. Note: Begbalance is primarily used for Balance Sheet accounts. Expand YearTotal and Q1, select Jan and click Edit button. Change Name: to M1 and Alias: to Jan and click
Save button. An error message displays - An object with the name Jan already exists. In order to change Alias to an existing
Name, you need to Edit and Save the member twice. Close the error message and with just Name: of M1 click Save button. Now for M1, click Edit button. Add Alias: Jan and click Save button.
2. Repeat step 1 for remaining months M2/Feb through M12/Dec
3. From menu bar on AutoPlan page, select Administration > Alias
Tables A. On Alias Tables page click Add button.