9 Adding Measures to a Standard Form Analytic Workspace

9.2.2.1 Procedure: Opening OLAP Worksheet from Analytic Workspace Manager

1. Open Analytic Workspace Manager.

2. Connect to your database.

3. From the Tools menu, select OLAP Worksheet.

   The OLAP Worksheet window opens. If you have attached an analytic workspace in Analytic Workspace Manager, then that workspace is attached to your session in OLAP Worksheet.

4. To execute an OLAP DML command, type it in the input pane at the bottom of the window.

   For example, to view the list of attached analytic workspaces, issue this command:

   AW LIST
   
   

   Note that the EXPRESS workspace must always be attached.

   
   

   Note: Because OLAP Worksheet and Analytic Workspace Manager share the same session, you must be careful when moving between the two applications. Your actions in the Object View may have consequences on commands that you issue in OLAP Worksheet. Use the AW LIST command to check the order in which analytic workspaces are attached, since some commands, like LISTNAMES and DEFINE, operate only on the first workspace. Also issue a LIMIT NAME TO ALL command before using commands like EXPORT, which use the status of the NAME dimension.

   
   

9.2.2.2 Procedure: Using the Editor in OLAP Worksheet

Use the Edit window to change the content of a program, model, or aggmap. Alternatively, you can use the property pages in Analytic Workspace Manager to edit these objects, but you cannot execute them there.

You cannot change the contents of a dimension, variable, relation, valueset, or other data container using the editor.

1. To add contents to a program object, issue this command to open the edit window:

   EDIT program_name
   
   

   For example, EDIT CREATE_MEASURE.

   PROGRAM is the default object type; you must specify the other types. For example, you would issue a command such as EDIT AGGMAP units_cube_aggmap to edit an aggregation map.

2. Type the OLAP commands that you want in the program.

3. When you are done editing the program, from the editor's File menu, choose Save, then Close.

4. To compile and execute the program, issue these commands:

   CALL program_name
   
   

   Note: The COMPILE command is optional because the program will compile automatically. However, a separate COMPILE command is useful for quickly identifying syntax errors in the OLAP DML commands.

5. To issue SQL commands, from the Options menu, select SQL Mode. To resume issuing OLAP DML commands, clear SQL Mode.

Example 9-1 shows a sample session in which a program named CREATE_MEASURE is created, compiled, and executed within OLAP Worksheet

DEFINE create_measure PROGRAM

LD Define a database standard form measure

EDIT create_measure
     .
     .           " Enter program code
     .           " Choose Save

CALL create_measure

9.2.3.1 DBMS_AW.EXECUTE Command Format

Following is the basic format of DBMW_AW.EXECUTE, in which you can substitute one or more OLAP DML commands, separated by semicolons, between the single quotes.

EXECUTE DBMS_AW.EXECUTE('dml_command_1; dml_command_2; dml_command_n');

In the following example, the first command opens the GLOBAL analytic workspace. The second command sets the focus on a measure named SALES_PP and assigns an equation to it.

EXECUTE DBMS_AW.EXECUTE('AW ATTACH global RW');
EXECUTE DBMS_AW.EXECUTE('-
     CONSIDER sales_pp; EQ LAG(sales, 1, time, LEVELREL time_levelrel)');

9.2.3.2 Adding Contents to a DML Program From SQL

You can define the contents of a program in a text file, which you can easily modify. Follow these steps:

1. Define a database directory object if you have not done so already.

   CREATE DIRECTORY directory AS 'path_name';
   GRANT permission ON DIRECTORY directory TO users;
   
   

2. Open an analytic workspace if you have not done so already.

   EXECUTE DBMS_AW.EXECUTE('AW CREATE aw_name ');
   
   

   or

   EXECUTE DBMS_AW.EXECUTE('AW ATTACH aw_name RW');
   
   

   It is a good practice to develop your OLAP DML programs in a separate analytic workspace from your data.

3. Create a program object and, optionally, document it by attaching a description. The following syntax defines a new program.

   EXECUTE DBMS_AW.EXECUTE('DEFINE object PROGRAM; LD object description');
   
   

4. Open a text editor and create a file with the following contents:

   CONSIDER program_name
   PROGRAM
        .
        .                 " OLAP DML commands
        .
   END
   
   

   Tip: If your operating system permits you to open multiple windows, you can use one window for your SQL session and another for editing the text file.

5. Execute the text file.

   EXECUTE DBMS_AW.EXECUTE('INFILE directory/filename');
   
   

6. Compile and execute the program.

   EXECUTE DBMS_AW.EXECUTE('CALL program_name');
   
   

Example 9-2 shows a sample session in which a program named CREATE_MEASURE is created, compiled, and executed within a SQL session.

% sqlplus
     .
     .
     .
SQL> CREATE DIRECTORY olapfiles AS '/users/oracle/olapfiles';
SQL> GRANT all ON DIRECTORY olapfiles TO ALL;
SQL> EXECUTE DBMS_AW.EXECUTE('AW ATTACH global_programs RW');
SQL> EXECUTE DBMS_AW.EXECUTE('DEFINE create_measure PROGRAM; LD Get measures');

-- Create a file named getmeas.inf in directory olapfiles with the
-- contents of the program. Start with the template and edit it for
-- the sample data.

SQL> EXECUTE DBMS_AW.EXECUTE('INFILE olapfiles/getmeas.inf');
SQL> EXECUTE DBMS_AW.EXECUTE('CALL create_measure');

9.3.3.1 ALL_MEASURES Dimension

The ALL_MEASURES dimension is a list of all measures in the analytic workspace. The REPORT command shows the contents of data containers, such as dimensions and measures. It has this basic syntax:

REPORT object

To see the contents of the ALL_MEASURES dimension, issue this OLAP DML command:

REPORT W 60 all_measures

The W parameter specifies the column width of the report.

MAINTAIN dimension ADD member

schema.cube.formula.MEASURE

MAINTAIN ALL_MEASURES ADD 'detailed_measure_name'

MAINTAIN ALL_MEASURES ADD 'GLOBAL.UNITS_CUBE.PROFIT.MEASURE'

UPDATE; COMMIT

9.3.3.2 ALL_DESCRIPTIONS Variable

The ALL_DESCRIPTIONS variable stores the short, long, and plural descriptions of each dimension member, which can be used for display. ALL_DESCRIPTIONS is dimensioned by ALL_OBJECTS, ALL_DESCTYPES, and ALL_LANGUAGES.

• ALL_OBJECTS is a concat dimension, which means that it is composed of two or more other dimensions in a concatenated list of dimension members. ALL_OBJECTS is composed of ALL_DIMENSIONS, ALL_CUBES, ALL_MEASURES, ALL_HIERARCHIES, ALL_LEVELS, and ALL_ATTRIBUTES. Maintenance and selection of dimension members is performed on these base dimensions rather than directly on the ALL_OBJECTS concat dimension.

• ALL_DESCTYPES lists LONG, SHORT, and PLURAL as its members. Objects dimensioned by ALL_DESCTYPES can provide multiple descriptors of these types.

• ALL_LANGUAGES lists the languages supported in the analytic workspace. It initially has the database language, such as AMERICAN_AMERICA. If additional languages are added to ALL_LANGUAGES, objects dimensioned by it can provide text in multiple languages.

LIMIT dimension TO values|position

LIMIT all_languages TO 1
REPORT W 65 DOWN all_objects w 20 ACROSS all_desctypes: all_descriptions

LIMIT all_dimensions TO FIRST 2
LIMIT all_objects TO all_dimensions
LIMIT all_languages TO 1
REPORT W 65 DOWN all_objects w 20 ACROSS all_desctypes: all_descriptions

variable(dimension 'member', dimension 'member'...)

LIMIT all_measures TO 'GLOBAL_AW.UNITS_CUBE.UNITS.MEASURE'
LIMIT all_objects TO all_measures
report all_descriptions(all_desctypes 'SHORT', all_languages 'AMERICAN_AMERICA')

LIMIT all_languages TO 1
LIMIT all_measures TO 'detailed_measure_name'
LIMIT all_objects TO all_measures
all_descriptions(all_desctypes, 'SHORT')= 'short description'
all_descriptions(all_desctypes, 'LONG')= 'long description'
all_descriptions(all_desctypes, 'PLURAL')= 'plural description'

UPDATE; COMMIT

9.3.3.3 AW_NAMES Variable

The AW_NAMES variable identifies the full name of objects in the analytic workspace, which correspond to the detailed names of the ALL_OBJECTS dimension. Full workspace object names have this format:

schema.workspace!object

For example, GLOBAL_AW.GLOBAL!SALES.

To see the contents of AW_NAMES, issue this OLAP DML command:

REPORT W 60 DOWN all_objects W 35 aw_names

Use commands such as these to add the workspace name of a new measure:

LIMIT all_measures TO 'detailed_measure_name'
LIMIT all_objects TO all_measures
aw_names = 'full workspace object name'

Issue another REPORT command to make sure that the changes were made correctly, then issue these commands to save them:

UPDATE; COMMIT

9.3.3.4 CUBE_MEASURES Valueset

The CUBE_MEASURES valueset identifies the measures in each cube. It is dimensioned by ALL_CUBES and contains values of the ALL_MEASURES dimension.

The VALUES function returns the contents of a valueset. To see the contents of CUBE_MEASURES, issue this OLAP DML command:

REPORT W 35 DOWN all_cubes W 55 VALUES(cube_measures)

Use commands such as these to add a measure to an existing cube:

LIMIT all_cubes TO cube
LIMIT cube_measures ADD 'detailed measure name'

For example:

LIMIT all_cubes TO 'UNITS_CUBE'
LIMIT cube_measures ADD 'GLOBAL.UNITS_CUBE.PROFIT.MEASURE'

Issue another REPORT command to make sure that the changes were made correctly, then issue these commands to save them:

UPDATE; COMMIT

9.4.1.1 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.

9.4.1.2 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.

" Select base level dimension members
LIMIT time TO time_levelrel 'Month'
LIMIT channel TO channel_levelrel 'CHANNEL'
LIMIT product TO product_levelrel 'ITEM'
LIMIT customer TO customer_levelrel 'SHIP_TO'
 
" Populate variables using calculations
ACROSS time units_cube_composite DO -
     'extended_cost_variable = units_variable * unit_cost_variable'
ACROSS time units_cube_composite DO -
     'sales_variable = units_variable * unit_price_variable'
ACROSS time units_cube_composite DO -
     'margin_variable = sales_variable - extended_cost_variable'
 
" Save the new variables
UPDATE
COMMIT

9.4.1.3 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:

   " Add SALES to the ALL_MEASURES dimension
   MAINTAIN ALL_MEASURES ADD 'global_aw.units_cube.sales.measure'
    
   " Add descriptions to the ALL_DESCRIPTIONS variable
   LIMIT all_measures TO 'global_aw.units_cube.sales.measure'
   LIMIT all_objects TO all_measures
   LIMIT all_languages TO 1
   all_descriptions(all_desctypes, 'SHORT')= 'Sales'
   all_descriptions(all_desctypes, 'LONG')= 'Sales as Units * Price'
   all_descriptions(all_desctypes, 'PLURAL')= 'Sales'
    
   " Add measure name to the AW_NAMES variable
   
   aw_names = 'GLOBAL_AW.GLOBAL!SALES'
    
   " Add measure to the CUBE_MEASURES valueset
   LIMIT all_cubes TO 'GLOBAL_AW.UNITS_CUBE.CUBE'
   LIMIT cube_measures ADD 'GLOBAL_AW.UNITS_CUBE.SALES.MEASURE'
    
   " Save these changes
   UPDATE
   COMMIT
   

9.4.1.4 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:

   1. Expand the Aggregation Plans folder under UNITS_CUBE and right-click the plan.

   2. Choose Edit from the menu.

   3. 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.