Converting a Block Storage Outline to Aggregate Storage in Essbase 11.1.2

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to convert BSO database outlines to ASO in Essbase 11.1.2.

From time to time, you may wish to use an Essbase block storage (BSO) database as a starting point for an aggregate storage (ASO) database, say, as a test of expanded dimensionality. Essbase provides a wizard to help you convert the BSO outline to ASO.

Scenario

The Hyptek corporation, a manufacturer of hard disk drives, has developed an expanded outline for BSO cube Hyptek, used for sales analysis. Hyptek management would like you to convert the Hyptek database outline to ASO.

Software and Hardware Requirements

The following is a list of software requirements:

Oracle® Essbase 11.1.2

Prerequisites

None

Reviewing the BSO Outline

First, review the structure of the 16-dimension Hyptek outline.

.	In the navigation panel of Administration Services Console, right-click the Hyptek outline node and select View.
.	The outline is displayed in Outline Viewer. Outline dimensions and members are examined in more detail in subsequent topics.
.	Click Close to close Outline Viewer.

In the navigation panel of Administration Services Console, right-click the Hyptek
outline node and select View.

The outline is displayed in Outline Viewer.

Outline dimensions and members are examined in more detail in subsequent topics.

Click Close to close Outline Viewer.

Starting the Outline Conversion Wizard

Essbase provides a wizard to help you convert BSO database outlines to ASO.

.	Select File, then Wizards, and then Aggregate Storage Outline Conversion. The Select Source Outline dialog box is displayed.
.	Select the Essbase Server tab, then navigate to the Hyptek database and double-click Hyptek.otl.
.	Click Next. The Verify Corrections to Outline dialog box is displayed. In reviewing the list, note that most of the errors are related to differences in hierarchy types between BSO and ASO.
.	Select Interactive outline correction, and then click Next. The Hyptek outline is displayed.
.	Click Verify. The outline verification errors and warnings are displayed in interactive correction mode.

Correcting the Fiscal Year and Scenario Dimensions

In interactive correction mode, the outline verification errors and warnings are dynamic and can be used for correcting errors in the converted outline.

.	Double-click the error for FY08. The Outline tab is displayed with FY08 highlighted. To fix the error, the Fiscal Year dimension must be changed from a Stored hierarchy to a Dynamic hierarchy.
.	Right-click Fiscal Year, and select Edit member properties. The Member Properties dialog box is displayed.
.	In the Hierarchy drop-down list, select Dynamic.
.	Click OK. The outline reflects the change.
.	Click Verify. The error related to FY08 has been corrected and no longer appears in the list. The remaining errors and warnings are displayed.
.	Double-click the error for Act vs Bud. The Outline tab is displayed with Act vs Bud highlighted. To fix the error, repeat steps 2 and 3 for the Scenario dimension.
.	Click OK. The outline reflects the change.
.	Click Verify. The remaining verification errors and warnings are displayed.

Correcting the Product Dimension

To fix the next several verification errors, change the Product dimension from a Stored to a Multiple Hierarchies Enabled dimension.

.	Double-click the error for FIREBRAND 270 A. The Outline tab is displayed with FIREBRAND 270 A highlighted.
.	Right-click Product, and select Edit member properties. The Member Properties dialog box is displayed.
.	In the Hierarchy drop-down list, select Hierarchies Enabled.
.	Click OK. The outline reflects the change.
.	Click Verify. The remaining verification errors and warnings, all related to the Promotions dimension, are displayed.

Correcting the Promotions Dimension

The Promotions dimension includes aggregation and variance hierarchies. Change it to Multiple Hierarchies Enabled.

.	Double-click the error for No vs Clearance. The Outline tab is displayed with No vs Clearance highlighted.
.	Edit member properties for the Promotions dimension, and change the Hierarchy type to Hierarchies Enabled.
.	Click OK. The outline reflects the change.
.	To add an aggregation member, right-click Promotions and select Add child.
.	In the text box, enter Total Promotions, then press Enter.
.	Press Enter again to exit the edit mode.
.	Press Shift+Click to select the following members: No Promotion Product Line Clearance New Line Introduction
.	Right-click the group and select Cut.
.	Select Total Promotions, then right-click and select Edit, and then Paste child.
.	An outline confirmation message is displayed. Click Yes. A stored hierarchy for Total Promotions is displayed in the outline.
.	In the member properties for Promotional Variances, change the hierarchy type to Dynamic, and then click OK. The outline reflects the change.
.	Click Verify. The outline verifies successfully.
.	Click OK, then click Done. The Select Destination for Aggregate Storage Outline page of the wizard is displayed.

Creating an Aggregate Storage Application and Database

An ASO application and database can be created directly from the outline conversion wizard.

.	Click Create Aggregate Storage Application. The Create Aggregate Storage Application dialog box is displayed.
.	For Application and Database, enter HyptekAS.
.	Click OK. The Outline Conversion Completed step of the outline conversion wizard is displayed.
.	Click Finish. The new HyptekAS ASO application and database are displayed in Administration Services Console. A red asterisk inside the application icon differentiates ASO from BSO applications.

Converting Member Formulas for Average Rates

Member formulas in ASO applications use MDX syntax, which is different from the calculation script syntax used in BSO applications. You convert member formulas in Outline Editor.

.	A side-by-side comparison of the Hyptek BSO and ASO outlines provides examples of the differences in syntax for formulas in the Average Rates members. Although a full description of MDX syntax used in constructing ASO formulas is beyond the scope of this tutorial, some of the basics are demonstrated in this topic.
.	Open the HyptekAS outline in Outline Editor.
.	Click Verify. Verification errors and warnings for member formulas are displayed.
.	Double-click the error for List Price. The List Price member is highlighted.
.	Right-click List Price and select Edit Member Properties. Member properties for List Price are displayed.
.	Select the Formula tab. The formula for List Price is displayed.
.	Replace the calculation script formula "Gross Sales" / Units; with the MDX equivalent [Gross Margin] / [Net Sales].
.	Click OK. The corrected formula for List Price is displayed in Outline Editor.
.	Repeat steps 4 through 8 to correct the formulas for the following Average Rates members: Change the formula for Discount % from Discounts / "Gross Sales"; to [Discounts] / [Gross Sales]. Change the formula for Labor/Unit from "Direct Labor" / Units; to [Direct Labor] / [Units]. Change the formula for Matl/Unit from Material / Units; to [Material] / [Units]. Change the formula for Overhead Rate from Overhead / "Direct Labor"; to [Overhead] / [Direct Labor]. The corrected formulas are displayed in Outline Editor.
.	Click Verify. The remaining verification errors and warnings for member formulas are displayed.
.	Double-click Gross Margin %, then edit member properties to replace the formula "Gross Margin" / "Net Sales"; with [Gross Margin] / [Net Sales]. The corrected formula is displayed in Outline Editor.
.	Click Verify. The remaining verification errors and warnings are displayed.

Converting Member Formulas for Mix Calculations

Convert the member formulas for mix calculations to MDX syntax.

.	Double-click Sales $ Mix by Product, then edit member properties to replace the formula "Net Sales" / @ANCESTVAL(Product,3,"Net Sales"); with [Net Sales] / ( [Net Sales], Ancestor ([Product].CurrentMember, [Product].Generations(3) ) ).
.	Double-click Unit Mix by Product, then edit member properties to replace the formula Units / @ANCESTVAL(Product,3,Units); with [Units] / ( [Units], Ancestor ([Product].CurrentMember, [Product].Generations(3) ) ).
.	Click Verify. The remaining verification errors and warnings are displayed.

Double-click Sales $ Mix by Product, then edit member properties to replace the formula "Net Sales" / @ANCESTVAL(Product,3,"Net Sales"); with [Net Sales] / ( [Net Sales], Ancestor ([Product].CurrentMember, [Product].Generations(3) ) ).

Double-click Unit Mix by Product, then edit member properties to replace the formula Units / @ANCESTVAL(Product,3,Units); with [Units] / ( [Units], Ancestor ([Product].CurrentMember, [Product].Generations(3) ) ).

Click Verify.

The remaining verification errors and warnings are displayed.

Flagging Expense Items for Variance Calculations

Member formula conversions for variance calculations can be accomplished with the help of User Defined Attributes (UDAs) that flag expense items.

.	Right-click Discounts (located in the Measures dimension), and select Edit Member Properties.
.	Select the UDAs tab.
.	In the assign text box, enter Expense.
.	Click Assign. Expense is added as a UDA assigned to the Discounts measure.
.	Repeat steps 1 through 4 to assign the Expense UDA to the following measures: Cost of Sales Freight Direct Labor Material Overhead Other CGS Material Variances Labor Variances Overhead Variances Obsolete Charges Inventory Adjustments
.	Click OK. Expense is added as a UDA to the expense measures.
.	Save the outline.

Calculating Scenario Variances Using Conditional Logic

A combination of UDAs and conditional logic in ASO outlines provides the equivalent of the Expense Reporting functionality available in BSO outlines.

.	Right-click Act vs Bud (located in the Scenario dimension), and select Edit Member Properties.
.	Select the Formula tab. The formula for Act vs Bud is displayed.
.	Replace the calculation script formula @VAR ("Actuals","Budget"); with the following MDX formula: IIF ( IsUDA ([Measures].CurrentMember, "Expense"), ([Budget] - [Actuals]), ([Actuals] - [Budget]) )
.	To ensure correct solve order, select Scenario, then open the Member Properties dialog box, and, for Dimension solve order, enter 20.
.	Click OK. Outline Editor is displayed.
.	Click Verify. The remaining errors are displayed.

Calculating Promotional Variances

Convert the member formulas for promotional variances to MDX syntax.

.	Double-click No vs Clearance. No vs Clearance is displayed in Outline Editor.
.	Right-click No vs Clearance and select Edit Member Properties.
.	Select the Formula tab. The formula for No vs Clearance is displayed.
.	Replace the calculation script formula "No Promotion"-"Product Line Clearance"; with the MDX formula [No Promotion] - [Product Line Clearance].
.	Click OK. The corrected member formula is displayed in Outline Editor.
.	Click Verify. The remaining errors are displayed.
.	Repeat steps 1 through 6 to correct the member formulas for No vs Intro and Clearance vs Intro.
.	Click OK. The corrected member formulas are displayed.
.	Click Verify. The outline verifies successfully.

Summary

In this tutorial, you learned how to convert a BSO database outline to aggregate storage, correcting for structural differences in the outlines and rewriting member formulas in MDX syntax.

Resources

To learn more about aggregate storage applications, refer to the Oracle Essbase Release 11.1.2 Database Administrator's Guide.
For a list of available online tutorials regarding aggregate storage, search "aggregate storage" on the Oracle Learning Library Web site.
For Oracle University training on aggregate storage applications, search "deploy aggregate storage databases" on the Oracle University Web site.

	About Oracle \| Oracle and Sun \| \| Subscribe \| Careers \| Contact Us \| Site Maps \| Legal Notices \| Terms of Use \| Your Privacy Rights