Case Study: Adding Measures to the Global Analytic Workspace - OLAP

"Identifying Required Business Facts" identifies the business measures required by the Global Corporation. Only three measures were acquired from the star schema: Units, Unit Price, and Unit Cost. The remaining business measures can be calculated from those three.

Custom measures can either be solved at run-time or stored in variables. Run-time calculations do not require disk storage space and do not extend the processing time required for data maintenance. However, they may slow performance. You need to decide which measures to calculate on the fly and which, if any, to store. Many of the required business measures are based on sales, extended cost, and margin, as shown in Table. Because these three calculated measures are used so heavily, the example stores them in variables. The other measures can be implemented as formulas and calculated on demand.

Custom Measures for the GLOBAL Analytic Workspace

Creating Measures for SALES, EXTENDED_COST, and MARGIN

The variables for Sales, Extended Cost, and Margin will have the same dimensions as Units, and will be added to the Units cube.

Creating New Variables in GLOBAL

Follow these steps to create SALES_VARIABLE.

1. In the Object View of Analytic Workspace Manager, expand the Variables folder for the GLOBAL analytic workspace.
2. Right-click UNITS_VARIABLE, and choose Create Like from the menu.
The Create Like dialog is displayed.
3. Type SALES_VARIABLE in the Destination Name box, and click OK.
SALES_VARIABLE is added to the list in the Variables folder.
4. Click SALES_VARIABLE to display it in the property viewer. On the Properties page, make the following changes to the settings:
AW$PARENT_NAME: Change to SALES. AW$SEGWDTH_CMD: Change the variable name to SALES_VARIABLE.
Click Apply to save changes to the property pages.
5. Repeat these steps for EXTENDED_COST_VARIABLE and MARGIN_VARIABLE.
6. To save the new definitions, choose Save from the File menu.

Calculating and Storing Values in Variables

The following commands calculate data just at the base level so that the new variables can be aggregated separately. The ACROSS command loops over the dimension members currently in status.
A hyphen at the end of a line continues a command to the next line.

Creating Measure Formulas

Follow these steps to create and register the SALES formula. Repeat them for EXTENDED_COST and MARGIN.

1. In the Object View of Analytic Workspace Manager, expand the Formulas folder for the GLOBAL analytic workspace.
2. Right-click UNITS, and choose Create Like from the menu.
The Create Like dialog is displayed.
3. Type SALES in the Destination Name box, and click OK.
SALES is added to the list in the Formulas folder.
4. Click SALES and make these changes to the property pages:
On the Expression page, change UNITS_VARIABLE to SALES_VARIABLE in the AGGREGATE function call.
On the Properties page, change the values of AW$LOGICAL_NAME and SOURCE_NAME to SALES. Click Apply to save changes to the property pages. 5. To save the new definitions, choose Save from the File menu. 6. To register the SALES measure, open OLAP Worksheet and issue the following commands: Aggregating the New Global Variables After you have created a standard form measure, you can aggregate it the same as any other measure. Because the new measures were added to an existing cube, you can either modify an existing aggregation plan or create a new one for the new measures. Follow these steps: 1. In the OLAP Catalog View, expand the Cubes folder sufficiently to see UNITS_CUBE in the GLOBAL analytic workspace. 2. To modify an existing aggregation plan, do the following: • Expand the Aggregation Plans folder under UNITS_CUBE and right-click the plan. • Choose Edit from the menu. • Add SALES, EXTENDED_COST, and MARGIN to the plan. or To create a new aggregation plan, right-click UNITS_CUBE and choose Create Aggregation Plan Using Wizard. Follow the steps of the wizard, and choose Help for additional information. 3. To deploy the aggregation plan, right-click it and choose Deploy Aggregation Plan from the menu. 4. Choose Save from the File menu. Adding More Custom Measures to GLOBAL The remaining measures can be calculated at runtime using any of the available methods. The following steps create a new formula object and register it as a measure, using the method described in this chapter. Alternatively, you can use the DBMS_AW_UTILITIES package to define permanent custom measures. To define the SALES_PP measure, take these steps: 1. In the Object View, right-click the SALES formula and choose Create Like from the menu. 2. Type SALES_PP as the destination name in the Create Like dialog. 3. Click the new SALES_PP formula and make these changes to the property pages: On the Properties page, change AW$LOGICAL_NAME and SOURCE_NAME to SALES_PP.
On the Expression page, replace the AGGREGATE function with this LAG function:
LAG(sales, 1, time, LEVELREL time_levelrel)
4. Register SALES_PP the same as the other measures.

Repeat these steps for the other measures listed in Table

Using an OLAP DML Program to Add Measures to GLOBAL

The previous examples showed how to define measures manually using Analytic Workspace Manager. Another option is to use an OLAP DML program. Example shows a sample program for adding measures. It takes three arguments:

• The name of the measure
• The name of the source variable
• The name of the cube for the measure

This is the command to run the program:

CALL create_measure('display_name' 'source_variable')

For example, CALL create_measure('Sales' 'sales_variable')

All of the other information is provided in local variables at the beginning of the program. If you use this program as a template for creating measures in your own analytic workspace, then either change the settings of these local variables or change the variables to command-line arguments.

This is the basic sequence of the program:

• Checks that the source variable exists and creates it if it does not. The program does not populate the variable; it just creates the object definition.
• Creates a formula with an AGGREGATE function in the equation. You can alter the equation at any time.
• Registers the new measure in the database standard form catalogs.

Comments are used throughout the program to help you understand how it works. You will also see these symbols:

" (double quote) begins or ends a comment
' (single quote) encloses literal text
& (ampersand) substitutes the value of an expression for the expression itself
(backslash) identifies the next character as literal, not part of command syntax
= (equal) sets the variable on the left to the value of the expression on the right
- (hyphen) continues command onto next line
: (colon) follows the name of label used to redirect processing

For the full syntax and usage of the commands and functions in this program, refer to the Oracle OLAP DML Reference.

DML Program for Adding Measures to UNITS_CUBE