Essbase Lab Guide-I For << Customer Name >> Prepared By Sushuma P 15th September 2008
Revision and Signoff Sheet Change Record
Author Version Change reference Date
Sushuma P 0.1 Initial draft for review/discussion 15th Sep 2008
Reviewers
Name Version
approved
Comments Date
TABLE OF CONTENTS
... 5
... 5
MODULE 1 ... 6
DESIGININGOUTLINES ... 6
EXERCISE - CREATING A DATABASE ... 6
EXERCISE - BUILDING A TIME DIMENSION ... 8
EXERCISE - BUILDING A SCENARIO DIMENSION ... 11
EXERCISE - CREATING THE ACCOUNTS DIMENSION ... 12
EXERCISE - ADDING TO THE ACCOUNTS DIMENSION ... 13
MODULE 2 ... 16
BUILDING LOAD RULES ... 16
EXERCISE - LOADINGTHE FAMILY TOTAL HIERARCHY ... 16
EXERCISE - (OPTIONAL) USINGTHE LEVEL BUILD METHOD ... 16
EXERCISE - CREATINGA PARENT-CHILD LOAD RULE ... 17
EXERCISE - LOADING DIMENSIONS USINGA PARENT-CHILD RULE ... 17
EXERCISE - (OPTIONAL) CLOROX MINTY ... 17
EXERCISE - LOADINGA CHANNELS HIERARCHYTO CUSTOMER ... 18
EXERCISE - CREATINGA REGION TOTAL ATTRIBUTE DIMENSION ... 19
EXERCISE - (OPTIONAL) BUILDINGTHE CAPACITY TOTAL ATTRIBUTE ... 19
MODULE 3 ... 20
SPREADSHEET REPORTING ... 20
EXERCISE - INSTALLING THE ADD-IN AND TOOLBAR ... 20
EXERCISE - MANAGING CONNECTIONS ... 23
EXERCISE - ANALYZING DATA ... 24
EXERCISE - CHALLENGE 1 ... 26
EXERCISE - CONTROLLING EXPANSION ... 26
EXERCISE - TROUBLESHOOTING REPORTS ... 28
EXERCISE - MANAGING DISPLAY OPTIONS ... 28
EXERCISE - SELECTING MEMBERS FROM THE OUTLINE ... 30
EXERCISE - DESIGNING QUERIES ... 30
EXERCISE - (OPTIONAL) REPLICATING REPORTS WITH CASCADE ... 32
EXERCISE - SPREADSHEET FORMULAS ... 36
MODULE 4 ... 39
CREATING BASIC CALCULATIONS ... 39
Exercise - Examining the Anatomy of a Rollup ... 39
Exercise - Adding a Back Calculation ... 43
Exercise - Focusing with FIX ... 44
Exercise - Focusing with IF ... 45
Exercise - Creating Mix Calculations ... 45
Exercise - Calculating a Moving Average ... 45
Exercise - Managing Calculation Script Maintenance ... 46
Exercise - Optimizing Block Size with Aggressive Dynamic Calculation ... 46
MODULE 5 ... 47
DESIGNING AND OPTIMIZING THE ADVANCED CALCULATION SCRIPTS 47 Exercise - Manipulating Data with Calculation Scripts ... 48
Exercise - Clearing Data ... 48
Exercise - Counting Customers ... 48
Exercise - Allocating Data ... 49
Exercise - (Optional) Advanced Allocations ... 50
EXERCISE SOLUTION ... 51
MODULE 1 ... 51
EXERCISE - BUILDING A SCENARIO DIMENSION ... 51
EXERCISE - CREATING THE ACCOUNTS DIMENSION ... 54
EXERCISE - LOADING THE FAMILY TOTAL HIERARCHY ... 58
EXERCISE - (OPTIONAL) USING THE LEVEL BUILD METHOD ... 64
EXERCISE - CREATING A PARENT-CHILD LOAD RULE ... 67
EXERCISE - LOADING DIMENSIONS USING A PARENT-CHILD RULE ... 69
EXERCISE - (OPTIONAL) CLOROX MINTY ... 70
EXERCISE - LOADINGA CHANNELS HIERARCHYTO CUSTOMER ... 75
EXERCISE - CREATINGA REGION TOTAL ATTRIBUTE DIMENSION ... 80
EXERCISE - (OPTIONAL) BUILDINGTHE CAPACITY TOTAL ATTRIBUTE ... 86
EXERCISE - CHANGING TRANSPARENT PARTITION VALUESAND STRUCTURE ... 88
EXERCISE - LOADING DATA ... 92
... 102
MODULE 3 ... 103
EXERCISE - SPREADSHEET REPORTING CHALLENGE 1: FORMATS ... 103
EXERCISE - TROUBLESHOOTING REPORTS ... 104
EXERCISE - SELECTING MEMBERS FROM THE OUTLINE ... 105
EXERCISE - DESIGNING QUERIES ... 111
MODULE 4 ... 117
EXERCISE - ADDING A BACK CALCULATION ... 117
EXERCISE - FOCUSING WITH FIX ... 118
EXERCISE - CREATING MIX CALCULATIONS ... 120
EXERCISE - CALCULATING A MOVING AVERAGE ... 120
EXERCISE - OPTIMIZING BLOCK SIZE WITH AGGRESSIVE DYNAMIC CALCULATION ... 121
SOLUTIONS ... 124
SOLUTION #1 - SETTING TWO-PASS CALCULATIONS ... 124
SOLUTION #2 - MAKING SCENARIOA DENSE DIMENSION ... 125
MODULE 5 ... 126
EXERCISE - MANIPULATING DATAWITH CALCULATION SCRIPTS ... 126
EXERCISE - CLEARING DATA ... 129
EXERCISE - COUNTING CUSTOMERS ... 129
EXERCISE - ALLOCATING DATA ... 130
EXERCISE - (OPTIONAL) ADVANCED ALLOCATIONS ... 132
MODULE 1
DESIGININGOUTLINES
Exercise - Creating a DatabaseYou have decided to analyze your sales data in Essbase, and the first step is to build an application and database shell. In this exercise, you create the Bigcorp application and Sales database.
1) On the Administration Console, select File > New > Block Storage Application.
2) Click OK.
The Create Application dialog box opens.
3) Verify that localhost is selected in the Analytic Server drop-down list box. 4) In the Application name text box, type Bigcorp.
5) Click OK.
The Bigcorp application is displayed under the Applications node on the Enterprise View tab.
7) Click OK.
8) Verify that localhost is selected in the Analytic Server drop-down list box. 9) In the Application drop-down list box, select Bigcorp.
10) In the Database name text box, type Sales. 11) In the Database type section, select Normal. 12) Click OK.
Exercise - Building a Time Dimension
In this exercise, you build the Time dimension and add dimension members. This exercise assumes that you are working in the Outline Editor.
To open the Outline Editor for the Sales database, expand the Sales database node in the Enterprise View tree and then double-click the Outline icon.
Creating Outline Members
1) In the Outline Editor, select Outline: Sales. 2) Right-click and select Add child.
3) In the text box, type Year Tot. 4) Press Enter.
5) Press Esc. 6) Select Year Tot.
7) Right-click and select Add child. 8) Type Quar ter 1 .
9) Press Enter .
10) Create additional sibling members for Quarter 1 named Quarter 2, Quarter 3, and
Quarter 4.
Changing Outline Member Properties
1) Right-click Quarter 1, and then select Edit member properties.
2) Add an alias called Qtr 1 .
3) Add similar aliases for the remaining quarters. Add aliases to the months where Jan's alias is January,
Creating a Dynamic Time Series Member
1) Open the Member Properties dialog box for Year Tot . 2) Under Dimension Type, select Time .
3) Click OK.
4) Highlight Year Tot and select Outline > Dynamic time series.
5) In the Y-T-D row, select Enabled.
6) In the Gen drop-down list box, select 1 (representing the generation where the Y-T-D accumulation should occur).
8) Repeat this procedure for Q-T-D, selecting 2 for the generation and creating an alias of
QTD .
9) Click OK .
Exercise - Building a Scenario Dimension
Create a Scenario dimension for the Sales database. Add the following children to Scenario:
Current Year Prior Year Forecast Bud get
Scenario Variances
Use this table to create children for the Scenario Variances members:
Member Member Children Notes
Act Vs Bud Current Year
Budget Set children as shared members. Change the consolidation
operator of Current Year to Addition (+) and Budget to Subtraction (-).
Act Vs Fcst None Add the following member formula:
“Current Year” - “Forecast”; Bud Vs Fcst None Add the following member
formula:
“Budget” - “Forecast”; In addition, set the following dimension and member properties:
Set the Consolidation property for the children of Scenario and the children of Scenario Variances to (~) Ignore .
Set the Data Storage property for the Scenario dimension member and the Scenario Variances member to Label Only.
Exercise - Creating the Accounts Dimension
Create an Accounts dimension for the Sales database. Open Corpacct.xls, and select the
Gross Margin Analysis worksheet. Convert from spreadsheet analysis format to Essbase
outline Structure. Use this worksheet as a guide for member names and member relationships.
Exercise - Adding to the Accounts Dimension
In this exercise, you add time balance accounting and expense reporting features to the
Accounts dimension. This exercise assumes that you are working with the Sales outline. Setting Up Time Balance Accounting
1) Select the Accounts dimension and then click the Accounts Dimension Type button on the Outline Editor toolbar.
2) Add a sibling to Gross Margin % called Balance Sheet.
3) Set the Consolidation property for Balance Sheet to (~) Ignore and the Data Storage property to Label Only.
4) Under Balance Sheet, add the following children:
Child Unary Operator Member Properties
Inventory ~ Set as Time Balance
Last (under Account information). Accounts Receivable ~ Set as Time Balance
Last (under Account information).
Setting Up @VAR Formulas and Expense Reporting
2) Right-click and select Delete.
3) In the Outline Confirmation dialog box, click Yes . 4) Under Scenario Variances, select Act Vs Bud .
5) Right-click and select Edit member properties and then select the Formula tab.
6) On the Categorical tab, expand the Math category. 7) Select Insert arguments.
8) Double-click the @VAR math function. The function is displayed in the Formula Editor window.
9) Complete the formula to read: @VAR (“Current Year”, “Budget”);. 10) Click Verify and then click OK at the confirmation message. 11) Click OK to close the Member Properties dialog box.
12) Complete the comparable formulas for the Act Vs Fcst and Bud Vs Fcst. 13) Under Accounts, select Cost of Sales.
14) Right-click and select Edit member properties.
16) Identify and set Expense reporting for all other members in the Accounts dimension where this flag is required, according to the following model:
MODULE 2
BUILDING LOAD RULES
Exercise - Loading the Family Total Hierarchy
In this exercise, you create a load rule to build the Family Total hierarchy. To complete this exercise:
Create a new load rule.
Associate the load rule with the Sales outline.
Read the FamGen.xls data file (located in the \BCI\LoadRule directory) into the blank load rule.
Set the load rule to Dimension build fields view mode. Specify the data file delimiter type.
Ignore any header records.
Set the correct dimension build method.
Associate field headers with dimension and generation references. Verify and save the rule.
Update the outline using the rule.
Exercise - (Optional) Using the Level Build Method
Review the level load rule structure:
Open the load rule file: LoadRule \ Fam_Lev.rul . Open the data file: LoadRule \ FamLev.xls .
Review the load rule structure and contrast to the Fam_Gen load rule. Columns are organized bottom-up, left to right.
Rebuild the Family Total hierarchy using the level load rule:
Delete the product structures from the previous Family Total loading exercise. Load the FamLev.xls file using the Fam_Lev.rul.
Exercise - Creating a Parent-Child Load Rule
Create a parent-child load rule for the Product dimension of the Sales outline. Use the Configpc.xls data file (located in the BCI \LoadRule directory). Associate the columns with field properties.
Ignore any header records. Save the load rule as Par_C h.
Exercise - Loading Dimensions Using a Parent-Child Rule
Use the parent-child load rule you constructed to load the Configuration Total and Line
Total product hierarchies. To complete this exercise:
Load the product hierarchies into the Sales outline.
Load Configpc.xls (located in the BCI\LoadRule directory) with the Par_Ch load rule to create the Configuration Total hierarchy.
Load LinePC.xls (located in the BCI\LoadRule directory) with the Par_Ch load rule to create the Line Total hierarchy.
Exercise - (Optional) Clorox Minty
Based on what you have learned in this chapter, create a new load rule to load
Minty.txt into the Mint dimension in the Sales database. To complete this exercise:
Create a new sparse dimension named Mint.
Use Minty.txt (located in the BCI\LoadRule directory). Reformat the data file.
Save the load rule.
Load Minty.txt into the Sales database.
Delete the Mint dimension from the Sales outline.
The Mint dimension is not used in the following modules
Exercise - Loading a Channels Hierarchy to Customer
Create a load rule that adds Channel information under the Customer dimension in the Sales database. To complete this exercise:
Create a Customer dimension in the outline and save your change. Create a new load rule, using CusChan.xls (located in the
BCI\LoadRule directory) as your data sample. Organize columns as necessary.
Specify a build method.
Define field properties. Save the
load rule. Load
Exercise - Creating a Region Total Attribute Dimension
Create a load rule to define a Region Tota l attribute dimension. To complete this exercise:
Create a text attribute dimension in the Sales outline called Region Total with the following children: Mid West , South , East , and West . Select Customer as the base dimension for Region Total.
Create a load rule, using CusReg.xls (located in the BCI\LoadRule directory) as your sample data file. Specify a build method.
Edit data file fields where necessary. Ignore headers if necessary.
Reject records if necessary. Define field properties. Save the load rule.
Load CusReg.xls into the Sales database.
Exercise - (Optional) Building the Capacity Total Attribute
Create a new load rule to build a Capacity Total attribute dimension in the Sales outline. To complete this exercise:
Create a new load rule using AttCap.xls (located in the BCI\LoadRule directory) as your data sample.
Ignore any header records.
Add a new attribute dimension to the Product base dimension. Select a build method.
Edit fields where necessary. Define field properties. Save the load rule.
Load AttCap.xls into the Sales database
Exercise - Loading Data
In this exercise, you examine several data files with different structures and create load rules to load them into the Sales database. The sample data files are called Budget.txt ,
PriorYr.txt , and Forecast.txt . For each load rule:
1) Create a new load rule, using one of the files listed above as the sample data file. 2) Examine the data file and identify any problems in the data.
4) Edit fields if necessary.
5) If possible, use dynamic headings. Otherwise, ignore header records and specify field Properties manually.
6) Verify and save the load rule.
7) Use your load rule to load the data file into the Sales database.
8) After all data is loaded; calculate Sales with the CalcAll calculation script (located in BCI\Load Rule).
9) Verify your data in the data previewer for Sales.
MODULE 3
Spreadsheet reporting
Exercise - Installing the Add-in and Toolbar
You have been asked to configure the Essbase Spreadsheet Add-in several different ways for different users. In this exercise, you enable and disable the add-in, and you install the Essbase toolbar.
Resetting the Sales Database
If you have not fully completed the exercises in modules 1 and 2, you may want to reset your database to rebuild the outline and reload the data.
1) On your client machine, navigate to the x:\RS\BCI directory.
2) Double-click the ssheet.bat file. The automatic reset script runs and outputs messages in a command window. Wait until its completion.
Testing Default Behavior
When you install Essbase, let your users know the default behavior, as the add-in circumvents some normal Excel functionality.
Open the SSReports.xls file and go through the following steps on the Sandbox worksheet. Ask yourself the following questions: What happened? How is this different from Excel without the add-in? How would your users react if this were a worksheet of non-Essbase data?
1) Double-click anywhere on the blank Sandbox worksheet. 2) Establish a connection to the Bigcorp:Sales database. 3) After establishing a connection, double-click a blank cell. 4) Double-click Year Tot .
5) Double-click a data value. 6) Right-click a blank cell. 7) Double right-click Qtr 1 .
8) Right-click and hold Product , then let go. 9) Right-click and drag Product to Column A.
As you can probably tell from those tests, the mouse buttons behave differently when the add-in is installed. Since most users do not need Essbase functionality all of the time, you can give them different options for enabling it.
Configuring for Infrequent Essbase Users
Infrequent Essbase users may not want to load the add-in every time they open Excel. Showing these users how to enable and disable the add-in themselves is often a good option:
1) In Excel, select Tools > Add-Ins .
3) Click OK .
4) Clear the Sandbox worksheet and repeat some of the previous default behavior tests. How are the results different?
The Essbase menu is no longer displayed on the Excel menu bar. Now Excel behaves as usual, but users have no ability to connect to Essbase until they reverse the preceding steps.
Configuring for Semiregular Essbase Users (or for Beginners)
Users who need Essbase semiregularly may not want to continuously enable and disable the add-in, and yet still want familiar Excel mouse behavior. Beginners to the add-in often find it painful to lose access to Excel shortcuts. For these users, it is often a good solution to disable the Essbase mouse functionality while leaving the add-in loaded:
1) If necessary, select Tools > Add-Ins menu and enable the add-in in Excel. 2) Select Essbase > Options .
3) Select the Global tab.
4) Clear the check boxes for the Enable Secondary Button and Enable
Double-Clicking options and then click OK .
5) Clear the Sandbox worksheet and repeat some of the default behavior tests from above. How are the results different?
The mouse now retains normal Excel behavior. Moreover, since the Essbase Spreadsheet Add-in is still enabled, these users retain the ability to explore and analyze their data through Essbase menu commands or by using the Essbase toolbar.
Configuring for Regular Essbase Users (or for Advanced Users)
Users who use Excel primarily for connecting to Essbase generally find the default settings work the best, as the Essbase mouse
shortcuts give greater speed and flexibility to their analytic tools. But even these users sometimes view spreadsheets that do not contain Essbase data and want regular Excel functionality. In Essbase version 7.1.2, there is a new option to suit these users:
1) Select Essbase > Options . 2) Select the Global tab.
3) Select the Enable Secondary Button and Enable Double-Clicking options. 4) Select the Limit to Connected Sheets option and then click OK .
5) In a new Excel workbook, test the mouse button behavior for double-clicking and right-clicking.
6) Select Essbase > Connect , enter your login information, and then click OK . 7) Now that the worksheet is connected to Essbase, test the mouse button behavior again.
8) Select Sheet2 in the workbook and test the mouse button behavior.
With this option selected, you should see Essbase mouse functionality on worksheets that you have explicitly connected to Essbase, and Excel mouse functionality on nonconnected worksheets.
Disabling and Enabling the Essbase Toolbar
The Essbase toolbar gives users another method for accomplishing tasks on the Essbase menu, but it can be disabled:
1) In Excel, select View > Toolbars .
2) Clear the check mark next to Hyperion Essbase.
The Essbase toolbar is not installed automatically; macros install it when you open the
Esstoolb.xls file (located in Essbase\Client\Sample).
3) Repeat step 1 and then check Hyperion Essbase to display the toolbar again
Exercise - Managing Connections
You have a workbook with multiple sheets, requiring various database connections. In this exercise, you enable and disable connections, and you view your open connections in the Disconnect dialog box.
Connecting to Essbase
1) On Sheet1 of a new Excel workbook, select Essbase > Connect.
2) Select or type the correct server name, enter your username and password, and click
Update.
3) In the Application/Database list, select Bigcorp: Sales and click OK. 4) Select Essbase > Retrieve.
5) Select Sheet2 and then select Essbase > Retrieve.
6) Select Sheet3, select Essbase > Connect, and then connect to Sample: Basic. The current database connection is not displayed on the Excel worksheet.
7) Select Essbase > Disconnect.
You can now see that Sheet1 and Sheet2 are connected to Bigcorp:Sales, whereas Sheet3 is connected to Sample:Basic 8) Click Close.
Changing and Closing Connections
1) Rename Sheet2 NewSheet and then select Essbase > Retrieve.
You receive the following error message: “The resultant report cannot be retrieved. Your report heading cannot be interpreted.”
2) Select Essbase > Disconnect to view your connections.
Newsheet is now connected to Sample: Basic, not Bigcorp: Sales, resulting in the error. 3) Click Close.
4) Close the Excel workbook (do not save the changes) and open a new one. 5) Select Essbase > Disconnect to view your connections.
All the connections are still open, even though the workbook was closed.
6) Click Disconnect to close the selected connection, and then repeat until all connections are closed.
7) Click Close.
After an Essbase connection is established, retrievals on additional worksheets automatically connect to the last database used. When you rename a worksheet, Essbase requires a new connection. Since Sample: Basic is the last database that you connected to, the system used that connection for the new worksheet name, even though it was connected to Bigcorp: Sales originally.
8) Close the Excel workbook (do not save the changes).
Exercise - Analyzing Data
In this exercise, you use basic retrieve operations to navigate from a top-level retrieval to a more refined data set. This exercise assumes that the SSReports.xls file is open. Before you
begin, establish a connection to Bigcorp:Sales on the Sandbox worksheet, and ensure that your options are set so that Essbase mouse actions are enabled.
Basic Zooming
There are many ways to control zoom behavior in the Essbase Spreadsheet Add-in:
Action Options
Zoom in Essbase > Zoom In
Double-click member name Zoom In toolbar button Zoom out Essbase > Zoom Out
Double right-click member name Zoom Out toolbar button
Header dimension Zoom In on a header dimension zoom—reposition
on rows
Header dimension Alt+double-click a header dimension zoom—reposition
on columns
Starting with a basic, top-level retrieval, try the following steps and note what happens: 1) Zoom in on Year Tot .
2) Zoom in on Qtr 1 several times. 3) Zoom out on Qtr 1 .
4) Zoom in and reposition Accounts on the rows. 5) Zoom in and reposition Product on the rows. 6) Zoom in and reposition Scenario on the columns
Other basic Retrieve Operations: In addition to Zoom , there are four basic operations
that can help analysis:
Action Options
Keep Only Essbase> Keeponly Keep only toolbar button. Remove Only Essbase> Remove Only Remove only toolbar button
Pivot rows to columns Essbase>Pivot /column to rows Right click and drag. In-axis pivot-change the Right click and drag Order of nested columns and rows
Dimensions.
Flashback Essbase>flashblack
Flashblack toolbar button.
If necessary, clear the spreadsheet to start again with a basic, top-level retrieval. Try the following steps and note what happens:
Zoom in on year Tot.
Keep only, Qtr1, Qtr2 and Qtr 3.
Zoom in and reposition accounts on rows. Remove only QTR3.
Zoom in and reposition Scenario on rows. Pivot the scenarios to columns
Perform a in –axis pivot to place the time periods as the outer row dimension.
Select Flashback to undo the previous operations.
Exercise - Challenge 1
Starting from a basic top-level retrieve, use the skills you have learned up to this point to duplicate this report. Use SSReports.xls worksheet Challenge1.
Exercise - Controlling Expansion
You can customize your Zoom settings in Essbase > Options > Zoom. Clear the Sandbox spreadsheet and perform a top-level retrieval, then try the following:
1) Select Essbase > Options > Zoom .
2) Under Zoom In, select All
3) Zoom in on Yea r Tot . 4) Zoom in on Accounts . 5) Zoom in on Scenario . 6) Zoom in on Product .
You receive an error message that the maximum number of rows in Excel has been exceeded.
It is very easy, even in a small database, to exceed the maximum number of rows in Excel when you use the deeper zoom options, so they should be used sparingly. Choose other Zoom In settings from Essbase > Options > Zoom and then repeat the preceding steps, noting any differences in the behavior.
Minimizing Expansions in Reports
Your zoom options give you other ways to control expansions. That is especially useful when looking at a large report with nested dimensions, as the one on the Expansion tab in the SSReports.xls workbook.
In this report, your products are grouped by customer. IBM has the highest sales in the OEM Channel, and you want to see their sales details for the LIGHTBOLT performance products, which are the company’s best-sellers. Because retrievals take more time the more real estate you are using in Excel, focus your zooms as tightly as possible.
1) On the Expansion worksheet, zoom in on Family Total for IBM . What does that do to the number of rows in the report?
2) Select Essbase > Flashback to return the report to its original state. 3) On the Zoom tab, select Within Selected Group ( Essbase > Options ).
4) Zoom in on Family Total for IBM again. How is this different from your results for step 1?
5) Continue to zoom in to find the detail of the individual LIGHTBOLT products purchased by IBM.
Even though you are interested only in LIGHTBOLT data, this option still leaves other branches of the Product dimension in the report and taking up space.
6) Zoom out on PERFORMANCE to return the report to its original state. 7) On the Zoom tab, select Remove Unselected Groups .
LIGHTBOLT products again. How does this option differ from the previous one?
Exercise - Troubleshooting Reports
Errors in reports are commonly caused by incorrect labels or label placement. The Troubleshooting.xls file contains a series of tabs that may or may not have retrieval problems. For each tab, diagnose and correct the issue.
Tab Name Diagnosis Resolution
Labels 1 Labels 2 Labels 3 Labels 4 Labels 5 Labels 6 Labels 7 Labels 8
Exercise - Managing Display Options
Essbase gives you many different options for displaying your reports. In this exercise, you take a prebuilt report and change the display options so it matches the finished result.
On the Managing Options tab in the SSReports.xls file, make the necessary changes to display the report as shown in the following sample
:
In Essbase > Options > Style , do the following tasks:
Define a style for the Product and Year Tot dimensions. Define a style for Linked Objects .
In Essbase > Options > Display , do the following tasks: Display aliases.
Display member names and aliases for rows. Display styles.
Suppress #Missing rows. Display no indentation.
Select Apr for the latest time period in Dynamic Time Series. In the report, do the following tasks:
Create a linked reporting object. Replace Year Tot with YTD
Exercise - Selecting Members from the Outline
Even with their most focused settings, the basic zoom options can return more information than is necessary for more selective reporting. In this exercise, you are asked to create a report for the Config A LIGHTBOLT and MAVRIDER products only. Since this product list does not exist as part of the natural outline hierarchy, you use the advanced selection options in the member selection tool to build it.
On the Member Selection tab in the SSReports.xls file, build the report to the following specifications:
Report Section Dimension Members
Report Header Current Year, IBM, Units Column Headers Jan through Apr, Y-T-D(Apr)
Row Headers Config A LIGHTBOLT and MAVRIDER products (level 0 only) The finished report should look as follows:
Exercise - Designing Queries
You have been asked by the Channels organization to modify the previous report to include a list of all level 0 products grouped by the customers in the OEM channel. In addition, they want to see the products sorted within each group in descending order
based on the YTD totals, and they want to filter out rows with no YTD totals.
Because this report requires nested dimensions in the rows and server-based sorting, Essbase Query Designer is the best tool for the job.
On the Member Selection tab in the SSReports.xls file, build the report to the following specifications:
Query Section Dimension Members
Header Current Year, Units. Dimensions
Column Jan through Apr, Y-T-D(Apr). Dimensions
Row Dimensions OEM and children, all level 0 products (suppress shared members).
Data Filtering Restrict data retrieval to rows where data is not a #Missing value for Y-T-D(Apr).
Data Sorting Sort the products by the YTD total.
.
Exercise - (Optional) Replicating Reports with Cascade
Many organizations need to regularly create many similar reports based on a standard template. Rather than replicating these reports manually, Essbase can do it automatically with the Cascade option. In this exercise, you create reports for distribution using the Cascade option. This exercise assumes that the SSReports.xls file is open.
1) Select the Swoosh worksheet. 2) Select OEM .
3) Select Essbase > Cascade .
4) On the Cascade Information tab, ensure that the member is set to cascade on
5) Select the Destination Options tab.
6) Under Destination Directory, browse to the directory where you want to store the reports.
7) Under Destination Types, select One
Workbook, Separate Sheets .
8) Under File Information, select
Open Created Files .
9) Under Naming Information, perform the following tasks: • For Prefix, type OEM .
• For Suffix, type Cust .
• For Workbook Name, type OEMRpt . 10) Select the Format Options tab.
11) Perform the following tasks: • Select Copy Formatting . • Select Include Table of
Contents .
12) Click OK .
The OEMRpt.xls file is generated.
13) Open OEMRpt.xls and review the worksheets.
Viewing the Table of Contents
1) Start Notepad (or any other text editor). 2) Select File > Open .
3) Browse to the directory containing the OEMRpt.xls file. 4) Select OEM0Cust.lst .
You may have to select All Files from the Files of Type drop-down list box to see this file in the directory.
5) Click Open .
Exercise - Spreadsheet Formulas
In this exercise, you use formula protection options on a
spreadsheet report. This exercise assumes that the SSReports.xls file is open.
1) Select the Formulas worksheet. 2) Select Essbase > Retrieve
.
This dialog box is displayed every time you retrieve or zoom in on this report to inform you that a given piece of metadata is not in the database. For the purposes of this course, always click No to skip additional messages and continue.
4) Select cell D5 and type the following formula: =C5-B5 . 5) Copy the formula down to cell D7 .
6) Select Essbase > Retrieve . The formula is not preserved. 7) Select Essbase > FlashBack . 8) Select Essbase > Options > Mode .
9) Under Formula Preservation, select Retain on Retrieval and click OK . 10) If necessary, repeat steps 4 and 5 to create a formula for the
variance column.
11) Select Essbase > Retrieve . Note that the formula is preserved.
To Retain on Zooms
1) Zoom in on PERFORMANCE . The formula is not preserved. 2) Select Essbase > Flashback . 3) Select Essbase > Options > Mode.
4) Under Formula Preservation, select Retain on Zooms and click OK . 5) If necessary, reenter the formula for the variance column.
6) Zoom in on PERFORMANCE .
The formula is preserved for Performance, but its children now have no formula. 7) Select Essbase > Flashback .
The original report is displayed.
8) Select Essbase > Options > Mode .
9) Under Formula Preservation, select Formula Fill and click OK . 10) Zoom in on PERFORMANCE.
Both Performance and its children retain their formulas.
MODULE 4
CREATING BASIC CALCULATIONS
Exercise - Examining the Anatomy of a Rollup
In this exercise, you create a new calculation script that calculates each dimension in the Sales database one by one. This exercise assumes that the database was cleared of all data and that Actual.txt and Forecast.txt were loaded with Loadcorp.rul.
1) In the Enterprise View, select the Bigcorp:Sales database. 2) Select File > New > Scripts > Calculation script and click OK .
3) Select Insert arguments .
4) On the Categorical tab, expand the Functional category and double-click SET
UPDATECALC .
This commands turns off Intelligent Calculation
functionality for this calculation script.
5) In the Calculation Script Editor, modify SET UPDATECALC to OFF; (be sure to include the semicolon).
Calculating the Accounts Dimension
1) On the Categorical tab, select the Functional category and double-click CALC DIM .
The function is displayed in the Calculation Script Editor.
2) In the Calculation Script Editor, highlight the dimList argument
.
3) In the Dimensions list, right-click Accounts and select Insert member name
.
dimlist is replaced by the Accounts member
4) Select File > Save .
5) Name the calculation script CalcCorp . 6) Click OK .
7) Open Anatomy.xls (located in the BCI\CalcIt\Anatomy directory).
8) Connect to Bigcorp:Sales on the This Is A Block worksheet and retrieve. You should see only input data at this point.
9) Select Essbase > Calculation .
The Essbase Calculation dialog box is displayed. 10) Select CalcCorp and click Calculate .
11) Select Essbase > Retrieve and analyze the result. The upper-level Accounts dimension members now contain data.
Calculating the Year Tot Dimension
1) Open the CalcCorp calculation script.
2) In the CALC DIM statement, insert Year Tot after Accounts. Make sure to separate Account and Year Tot with a comma.
3) Save the calculation script. 4) Open Anatomy.xls .
6) Select CalcCorp and click Calculate .
7) Select Essbase > Retrieve and analyze the result.
Calculating the Customer Dimension
1) Open the CalcCorp calculation script.
2) In the CALC DIM statement, insert Customer after Year Tot. Make sure to separate Year Tot and Customer with a comma. 3) Save the calculation script.
4) Open Anatomy.xls .
5) Select Essbase > Calculation
6) Select CalcCorp and click Calculate . 7) Change the page header from IBM to OEM .
OEM is an upper-level member across the Customer dimension.
8) Select Essbase > Retrieve and analyze the result. Data is displayed for Accounts and Year Tot for upper-level blocks across the Customer dimension. New upper-level Customer blocks are created.
Calculating the Product Dimension
1) Open the CalcCorp calculation script.
2) In the CALC DIM statement, insert Product after Customer.
3) Save the
calculation script. 4) Open Anatomy.xls .
5) Select Essbase > Calculation .
6) Select CalcCorp and click Calculate .
7) Change the page header label from Lightbolt 365 A to Lightbolt . Lightbolt is an upper-level member across the Product dimension.
8) Select Essbase > Retrieve and analyze the result
Data is displayed for Accounts and Year Tot for upper-level blocks across the Product dimension. New upper-level Product blocks were created.
Exercise - Adding a Back Calculation
Although you have rolled up all dimensions in the outline, some data is not aggregating correctly. In this exercise, you modify the CalcCorp.csc file to correct rates that are aggregating incorrectly.
1) Open the CalcCorp calculation script.
2) After the CALC DIM statement, add a block of formulas to derive the correct rates for the following accounts:
List Price , Discount % , Labor/Unit , Matl/Unit , Overhead Rate , Gross Margin % .
Enclose the group of formulas in parentheses to prevent jump-ahead behavior from the CALC DIM statement.
3) Save the script.
Exercise - Focusing with FIX
Modify the following calculation script to fix on: Current Year
January, February, and March /* Housekeeping */
SET UPDATECALC OFF; /* The Baseline Fix */ /* The Main Rollup */
CALC DIM (Accounts, Customer, Product);
Save the script as FixIt and calculate. Check your results on the FixIt tab of the Anatomy.xls file.
Exercise - Focusing with IF
Forecasting calculations typically perform time range calculations (counting forward or backward in the Time dimension) and generally include simple to complex conditional logic. In this exercise, you write a forecasting calculation script for the Sales database. 1) Open Anatomy.xls .
2) Using the If It worksheet as a reference, write calculation scripts to:
• Push units from August to September
• Push units into Qtr 4 with a two month lag and a 10% accelerator
• Push price into September and the months in Qtr 4 with a one-month lag and a 10% decelerator
Extra Credit
The 10% accelerator for Units creates partial units in some months. Modify your script so that units are rounded to the nearest whole number.
Exercise - Creating Mix Calculations
Mix calculations are typically a percent-to-total type of ratio calculation that takes a data value at an given level and figures what percentage it is of a related total. You have been asked to create several mix calculations in the Sales outline.
1) Examine the formulas on the Mix Calculation Required tab in the SSReports.xls file.
2) In the Sales outline, add a formula to Unit Mix that calculates the current product’s units divided by the units for its generation 3 ancestor.
3) Add a formula to Sales $ Mix that calculates the current product’s Net Sales divided by the Net Sales for its generation 3 ancestor.
4) Make both Unit Mix and Sales $ Mix Dynamic Calc .
5) Save the outline and check your results on the ANCESTVAL It tab in the SSReports.xls file.
Write a calculation for the Moving Average account that takes a three-month moving average (the current month plus the previous two months) of unit sales. The calculation should span Prio r Year and Current Year .
For the first two months in Prior Year, use the units sold for Moving Average.
Extra Credit
Write your calculation script so that #Missing data is not counted.
Exercise - Managing Calculation Script Maintenance
Many calculation scripts contain values or member names that are variable and must therefore be updated regularly. To make maintenance easier, create server-based substitution variables. This gives you one place to update multiple calculation scripts.
Creating Substitution Variables
1) In the EAS Enterprise View, double-click the Variables node under the server name.
2) In the Application drop-down list box, select Bigcorp . 3) In the Database drop-down list box, select Sales . 4) In the Variable text box, type CurrentMonth . 5) In the Value text box, type Feb .
6) Press Enter .
7) On row 2, repeat steps 2 through 5 to create a second variable called PriorMonth with a value of Jan .
8) Click Close .
Adding Substitution Variables to Calculation Scripts
1) Open CalcCorp.csc .
2) Add the following variance calculation to the Back Calculation section for the Curr
Mo Vs Prior member: @VAR (&CurrentMonth, &PriorMonth);
3) Save and then calculate CalcCorp .
4) Check your results on the Sub Variable tab of the Anatomy.xls file.
Exercise - Optimizing Block Size with Aggressive Dynamic Calculation
To improve retrieval times, Bigcorp Manufacturing has decided to eliminate unnecessary back calculations from their calculation scripts.
In this exercise, you create a new application and database named Dynamic and use the information in the Aggressive.xls workbook such that a back calculation is not necessary to produce correct values.
1) Open Agressive.xls (located in BCI\CalcIt\Dynamic).
The values in the No Dynamic spreadsheet represent a retrieve from the Bigcorp database with no dynamic calculations. These values are correct.
2) Create a new application called Dynamic with a new database called Dynamic . 3) Open the dynamic.otl file from the BCI\CalcIt\Dynamic directory and save it to the
Dynamic database (overwrite the existing outline file).
4) Load Actual.txt and Forecast.txt using Loadcorp.rul (those files are also in the BCI\CalcIt\Dynamic directory).
5) Calculate the Dynamic database.
6) Open Aggressive.xls , and retrieve it into the dynamic spreadsheet.
7) In the comparison spreadsheet, create a formula that subtracts the two spreadsheets and copy it to all data cells. Investigate all nonzero values.
8) Return to the Dynamic database and make any changes required to correct the calculation until the Comparison worksheet in the Agressive.xls workbook indicates that the No Dynamic and Dynamic spreadsheets are in sync.
9) When you are satisfied that the spreadsheets are in sync, return to the database and make a note of the current block statistics.
10) In the dynamic outline, experiment with dynamic calculations and two-pass calculations by setting everything possible to dynamic in a way that still keeps the No Dynamic and Dynamic spreadsheets in sync.
MODULE 5
Exercise - Manipulating Data with Calculation Scripts
Copying data from one scenario to another is often a handy way to quickly populate a scenario with useful data that you can then manipulate as necessary. In the first part of this exercise, you create a budget by copying prior year Actual data into the Budget scenario and then modifying it. In the second part, you populate a Rolling Forecast scenario with a combination of Current Year (Actuals) and Budget data.
Before You Begin
1) Clear all data currently in Bigcorp:Sales.
2) Load Actual.txt and Forecast.txt using the Loadcorp.rul rules file. 3) Calculate the Sales database using CalcAll.csc.
Creating a Budget from Prior Year Actual Data
4) Open Calcu lations.xls (located in the BCI\CalcIt\AdvancedCalcs directory). 5) Select the Datacopy worksheet.
6) Write a calculation script to copy the data from Prior Year to Budg et and increase the new Budget values by 10% .
7) Calculate the script and use the Datacopy worksheet to verify the data.
Creating a Rolling Forecast from Actual and Budget Data
1) In the Substitution Variables dialog box, change the value of the CurrentMonth substitution variable to May .
2) Change the value of the PriorMonth substitution variable to Apr .
3) Write a calculation script to copy all data up to the prior month from Current Year to the Rolling Forecast scenario.
4) Populate all current and future months for Rolling Forecast with Bud get data
Exercise - Clearing Data
As the result of a data load, you realize erroneous data was loaded into Lightbolt 540
S . Write a calculation script to correct the problem. Your script should do the
following tasks:
Clear data from Lightbolt 540 S and its ancestors for the Current Year Recalculate the Product dimension.
Use a back calculation to correct rate values.
In your environment, assume that SET AGGMISSG must be turned off.
Exercise - Counting Customers
disbursement of products across the customer base. In this exercise, you write a calculation for Customer Count to count the number of customers that bought each product.
Extra Credit
Modify this script so that the Customer Count stores a 1 for every occurrence of a product sale. To see all customers for a given product, you must look at the Customer level.
Exercise - Allocating Data
In this exercise, you create a complex calculation script for the
Bigcorp:Sales database. Before You Begin
1) For testing purposes, create a calculation script called ClearAll that clears all data.
2) Load Budget data from the input worksheets in the Pushcorp.xls file (located in the BCI\CalcIt\Pushcorp directory).
Building the Script
Use this normalization table as a reference:
The calculation script should accomplish the following:
1) Turn intelligent calculation off because of multiple passes on blocks.
2) To enhance performance, add a command to make the calculator aggregate missing values, as there are no upper-level inputs to protect.
3) Declare allocation variables. 4) Focus on the Bud get scenario.
5) Push down units and rates loaded at upper levels to level 0. 6) Do a focused rollup for allocation of Other CGS expenses:
Calculate Net Sales , then sum Units and Net Sales across Product and Customer dimensions for members used as the allocation base.
7) Allocate Other CGS expenses to level 0 using variable allocation ratios. Focus calculations using FIX to level 0 members for Customers and Products.
8) Perform dimension rollups.
9) Recalculate any upper-level rates or percentages that contain erroneously aggregated data.
Exercise - (Optional) Advanced Allocations
Use Calculations.xls and the data on the Allocations Input worksheet to create allocations for Material Variances , Labor Variance , and Overhead
Variance . Complete the following tasks:
1) Load Actual.txt with Loadcorp.rul 2) Run CalcAll.csc .
3) After the consolidation is complete, lock and send the data on the Allocations Input worksheet.
4) Write an allocation to distribute Material Variances across all customers and products based on a ratio of individual unit sales for each customer and product over all units sold. Use the Allocations Audit worksheet.
5) Write a new script to push Labor Variances down to the next level of Family
Total based on an even split between the children with data.
6) For Overhead Variances , combine steps 3 and 4. Use the straightline allocation method to push Overhead Variances to the next level of Family Total. At the next generation of Family Total, Overhead Variances should be allocated based on a ratio of individual unit sales for each customer and product over all units sold.
Extra Credit
Write a stepped allocation to push Obsolete Charges down to level 0 of Product so that each level is based on an even split between the children with data.
Exercise solution
Module 1
Exercise - Building a Scenario Dimension
In this exercise, you build a Scenario dimension and set up a variance calculation for the Sales database. This exercise assumes that you have the Sales outline open.
Building a Scenario Dimension
1) Select Year Tot . 2) Click Add Sibling .
3) Type Scenario in the text box. 4) Press Enter twice.
5) Select Scenario . 6) Click Add Child .
7) Add these children to Scenario:
Current Year , Prior Year , Forecast , Budget , and Scenario Variances
8) Select Scenario Variances . 9) Click Add Child .
10) Add these children to Scenario Variances:
Act Vs Bud , Act Vs Fcst , and Bud Vs Fcst .
11) Select Act Vs Bud . 12) Click Add Child .
13) Add these children to Act Vs Bud: Current Year and Budget .
14) For both children of Act Vs Bud, open the Member Properties dialog box and set the storage type to Shared Member .
16) Right-click and select Edit member properties .
17) Change the Consolidation property of the Budget member to
(-) Subtraction .
18) Click OK .
Creating Formulas for Act Vs Fcst and Bud Vs Fcst
1) Right-click Act Vs Fcst and select Edit member properties . 2) Select the
Formula tab.
3) In the Dimensions Essbase Server, expand Scenario . The children of the Scenario dimension are displayed. 4) Double-click Current Year .
Current Year is displayed in the formula window. 5) Expand the Operators list.
6) Double-click the minus sign ( - ).
The minus sign ( - ) is displayed in the formula window. 7) In the Members Essbase Server, double-click Forecast . Forecast is displayed in the formula window.
8) Position the cursor and the end of the formula and type a semicolon ( ; ).
9) Click the Ve rif y button to verify the formula. 10) In the Member Formula dialog box, click OK . 11) In the Member Properties dialog box, click OK .
12) Repeat steps 1 through 11 to create the formula for Bud Vs
Forecast .
13) Select File > Save
Assigning Nonconsolidation Operators
1) Select Current Year .
2) Right-click and select Edit member properties . The Member Properties dialog box is displayed.
3) Change the Consolidation property of the Current Year member to (~) Ignore . 4) Click OK .
5) Change the Consolidation property to (~) Ignore for the following members:
Prior Year , Budget , Forecast , Scenario Variances , Act Vs Bud , Act Vs Fcst ,
and Bud Vs Fcst .
Setting Data Storage to Label Only
1) Select Scenario .
2) Right-click and select Edit member properties . The Member Properties dialog box is displayed.
3) Set the Data Storage property for the Scenario dimension to Label
Only .
Label Only is used for outline members that are a label and whose value has no meaning to the data. When marked Label Only, the member is not included in the data block structure of the database.
5) Repeat steps 2 through 4 for the Scenario Variances member.
6) Verify and save the Sales outline.
Exercise - Creating the Accounts Dimension
In this exercise, you create the Accounts dimension for the Sales database. This exercise assumes that you have the Sales outline open.
1) In the Sales outline, add a sibling to Scenario named Accounts . 2) Add a child to Accounts named Gross Sales .
3) Add children to Gross Sales named Units and List Price . 4) Select List Price .
5) Right-click and select Edit member properties .The Member Properties dialog box is displayed.
6) Change the Consolidation property of the List Price member to (*) Multiplication . 7) Click OK .
8) Open Corpacct.xls (located in the BCI\Outline directory). 9) Select the Gross Margin Analysis worksheet.
10) Compare the formulas and entries for computing Gross Sales in Excel to the Accounts model.
Create Members Through Net Sales Using Unary Operators
1) Create Discounts as a sibling of Gross Sales.
2) Add children to Discounts named Gross Sales and Discount % . 3) Under Discounts , select Gross Sales .
4) Right-click and select Edit member properties . The Member Properties dialog box is displayed.
5) Change the Data Storage property to Shared Member . 6) Click Next .
The Member Discount % is active.
7) Change the Consolidation property of Discount % to
(*) Multiplication .
8) Click OK .
9) Create Net Sales as a child of Accounts.
Since Gross Sales and Discounts calculate Net Sales, they are children of Net Sales. 10) Move Gross Sales and Discounts from their position as siblings of Net Sales to children of Net Sales.
11) Change the Consolidation property of the Discounts member to (-) Subtraction .
Creating Additional Members Through Gross Margin %
1) Analyze parent-child relationships in the Gross Margin Analysis worksheet (Corpacct.xls).
2) Create siblings of Net Sales : Direct Labor, Material, Overhead, Cost Of Sales,
Material Variances, Labor Variances, Overhead Variances, Obsolete Charges, Inventory Adjustments, Other CGS, Gross Margin, Gross Margin % .
3) Move Direct Labor , Material , and Overhead to children of Cost Of Sales . 4) Move Material Variances, Labor Variances, Overhead Variances, Obsolete
Charg es and Inventory Adjustments to children of Other CGS .
5) Move Net Sales, Cost of Sales and Other CGS to children of Gross Margin . 6) Add children to Direct Labor named Units and Labor/Unit .
7) Add children to Material named Units and
Matl/Unit .
8) Add children to Overhead named Direct Labor and Overhead Rate .
Your outline should look similar to the following sample:
Units Set the Data Storage property to Shared Member.
9) Examine all members in the Accounts dimension and look for data storage problems. 10) Compare the relationships in the Corpacct.xls file to the Accounts dimension in the Sales outline, and determine the correct consolidation operators.
11) Use this table to change the properties for the members in the Accounts dimension:
Parent Member Property Settings
Accounts Set the Data Storage property to Label Only.
Gross Margin Set the Consolidation property to ignore (~).
Gross Margin Set the Consolidation property to % ignore (~).
Gross Margin
Cost Of Sales Set the Consolidation property to subtraction (-).
Other CGS Set the Consolidation property to subtraction (-).
Direct Labor
Units Set the Data Storage property to Shared Member.
Labor/Unit Set the Consolidation property to Multiplication (*).
Material
Units Set the Data Storage property to Shared member
Matl/Unit Set the Consolidation property to multiplication (*).
Overhead
Direct Labor Set the Data Storage property to Shared Member.
Overhead Set the Consolidation property to Rate multiplication (*).
12) Select Outline > Verify.
13) Correct any errors that may occur. 14) Select File > Save.
Exercise - Loading the Family Total Hierarchy
In this exercise, you create a load rule and associate the rule with the Sales outline. You open the source file to be loaded and make the appropriate settings to match the characteristics of the file. You then load the Family Total rollup to the Products dimension in the Sales outline.
Opening the Data Prep Editor
1) On the Enterprise View of the Administration Console, select the Bigcorp application and the
Sales database.
2) Select File > New .
The New dialog box is displayed. 3) Select the Scripts tab
4) Select Rules file , and then click OK . The Data Prep Editor is displayed.
Associating the Load Rule with the Sales Outline
1) Select Options > Associate outline .
2) Make sure that the outline is set to the Bigcorp application and the Sales database, and then click OK .
Opening the Source File to Be Loaded
1) Select File > Open data file . The Open dialog box is displayed. 2) On the File System tab, select Excel sheets (*.xls) in the Files of type list. 3) Open the LoadRule/FamGen.xls file.
To see the file, you need to set the file type to XLS or All files.
Setting the Dimension Building Mode
1) Select View > Dimension build fields . Load Rules can dynamically build dimensions or load data into a database. The different modes enable you to define the settings for each of these activities.
Selecting the Data File Delimiter Type
1) Select Options > Data source properties . 2) Select the Delimiter tab
3) Confirm that the delimiter setting is the default Tab . 4) Click OK .
Creating the Product
Dimension
1) Select Options > Dimension
build settings .
2) Select the Dimension
Definition tab.
3) Select Rules File and, next to
Dimensions , click Click here to edit .
4) Enter Product . 5) Press Enter .
Product is displayed in the Rules file Dimensions list. 6) Right-click Product and select Edit properties .
7) Select the Dimension Properties tab.
8) In the Data Storage section, select Label Only . 9) In the Configuration section, select Sparse . 10) Click OK .
The Dimension Build Settings dialog box is displayed. 11) Click OK .
Selecting the Load Building Method
1) Select Options > Dimension build settings . The Dimension Build Settings dialog box is displayed. 2) Select the Dimension Build Settings tab.
3) In the Dimension list, double-click Product .
4) In the Build method section, select Use generation references . 5) Click OK .
You return to the Data Prep Editor.
Associating Field Headers with Dimensions and Generations
1) Select any member or the column header for Field 1, and then select Field >
Properties .
Field Number: 1 is displayed above the Field Definition area. 2) Select the Dimension Build Proper ties tab.
3) In the Dimension list, double-click Product . Product is displayed in the Dimension area.
4) In the Field type section, double-click Generation , for the generation Number, enter 2 .
5) Click Next .
Field Number: 2 is displayed above the Field Definition area.
6) For Field 2, select Product for the Dimension, Property for the Field Type, and
2 for the Number. The property for the preceding generation 2 member is set.
7) Click Next . Field 3 is displayed.
8) Continue the process for the subsequent fields using the information in the following table:
Field # Field Type Generation Dimension
3 Generation 3 Product 4 Generation 4 Product 5 Generation 5 Product 6 Alias 5 Product 9) Click OK .
You return to the Data Prep Editor.
Ignoring the First Header Row
1) Select Options > Data source properties . The Data Source Properties dialog box is displayed. 2) Select the Header tab.
3) In the Header Lines group, enter 1 for the Number of lines to skip . 4) Click OK .
You return to the Data Prep Editor.
Saving the Load Rule
1) Select Options > Validate to validate the load rule. 2) Correct any errors noted.
3) When you receive the message, “The rules file is correct for dimension building,” click OK .
4) Select File > Save .
database.
The file type RUL is automatically set up by Essbase.
6) Select File > Close . The Data Prep Editor closes.
Loading the Family Total Rollup to the Products Dimension 1) In the Sales outline, select Outline > Update Outline . 2) Click Find Data File
and select the
FamGen.xls
file located in the
BCI\LoadRule directory. 3) Click Find Rules File . The Open dialog box is displayed.
4) On the Analytic Server tab, select the Fam_Gen.rul file from the list.
5) Note the location of the dimbuild.err file directory. Review this file if errors occur during the loading process.
6) Click OK .
If the load fails, open the error file and make corrections, and then try again 7) Expand the Product dimension to view the loaded changes.
The outline should appear similar to the following sample
:
If the load did not process correctly, you may close the outline without saving changes
.
Module 2
Exercise - (Optional) Using the Level Build Method In this exercise, you review the level rule structure and redo the Family Total Rollup using the level load rule. This exercise assumes that you have the Essbase Administration Services Console open and the Bigcorp application and Sales database selected.Reviewing the Level Load Rule Structure
2) Click File System . 3) Select Fam_Lev.rul (in the BCI\LoadRule directory) and click OK . 4) If prompted, select English
(Latin1) for File
Encoding.
The Data Prep
Editor is
displayed.
5) Select File >
Open data file .
The Open dialog box is displayed.
6) On the File System tab, select Excel sheets (*.xls) in the Files of type list. 7) Locate and select the FamLev.xls file in the BCI\LoadRule directory.
8) Click OK to open the FamLev.xls file
9) Review the load rule structure and contrast to the Fam_Gen load rule.
Columns are organized bottom-up, left to right. 10) Select File \ Close .
