22 DBMS_AW_UTILITIES

22.1 About Custom Measures

You can use the DBMS_AW_UTILITIES package to define custom measures within database standard form analytic workspaces and associate the custom measures with columns in relational views. You can define temporary custom measures for use during the current session, or you can save them permanently.

A custom measure is derived from one or more stored measures. It is calculated at run-time and returned in columns of a view that is structured like a fact table. An example of a custom measure is PROFITS, which is calculated by subtracting the COSTS measure from the SALES measure.

Custom measures created by DBMS_AW_UTILITIES are defined as formulas in an analytic workspace. A formula is a workspace schema object representing a calculation. The result set of a formula includes a value for each workspace dimension member currently in status.

22.2 Querying Custom Measures

When the CREATE_CUSTOM_MEASURE procedure successfully creates a new custom measure, it provides the following information.

Custom Measure cust_meas_name created in Workspace workspace_name.
 Custom Measure cust_meas_name mapped to column col_name in View view_name.

You can query the specified column to obtain the results of custom measure calculations.

Alternatively, you can query the following tables to obtain information about custom measures created with CREATE_CUSTOM_MEASURE. These tables also provide the name of the columns that contain the results of custom measure calculations.

• olapsys.CWM2$_AW_PERM_CUST_MEAS_MAP — This table provides information about permanent custom measures. This table is only available to users with DBA privileges.

• olapsys.CWM2$_AW_TEMP_CUST_MEAS_MAP — This table provides information about temporary custom measures. This table is accessible to the current user.

22.3 Example: Creating a Custom Measure

The following example creates a temporary custom measure in the analytic workspace GLOBAL_AW.GLOBAL. The measure returns the difference between Unit Price and Unit Cost for the cube PRICE_CUBE. The custom measure is returned in the view GLOBAL_AW.GLOB_GLOBA_UNITS_CU10VIEW.

To see the output of your queries, direct output to the screen.

SQL>set serverout on
SQL>exec cwm2_olap_manager.set_echo_on;

You can use the following query to obtain a list of the available analytic workspaces.

SQL>select * from all_olap2_aws where aw = 'GLOBAL';

OWNER                          AW                              AW_NUMBER------------------------------ ------------------------------ ----------GLOBAL_AW                      GLOBAL                               1005

The following query returns a list of the enabled views for cubes in the analytic workspaces.

SQL>select * from all_aw_cube_enabled_views where aw_name = 'GLOBAL';

AW_OWNER  AW_NAME CUBE_NAME  HIERCOMBO_NU HIERCOMBO_STR                    SYSTEM_VIEWNAME          USERP_VIE      
--------- ------- ---------- ------------ ------------------------------   ------------------------ ---------
GLOBAL_AW GLOBAL  PRICE_CUBE   ########## DIM:PRODUCT/HIER:PRODUCT_ROLLUP;  GLOB_GLOBA_PRICE_CU4VIEW
                                          DIM:TIME/HIER:CALENDAR
 
GLOBAL_AW GLOBAL  UNITS_CUBE   ########## DIM:CHANNEL/HIER:CHANNEL_ROLLUP;  GLOB_GLOBA_UNITS_CU9VIEW
                                          DIM:CUSTOMER/HIER:MARKET_ROLLUP;
                                          DIM:PRODUCT/HIER:PRODUCT_ROLLUP;
                                          DIM:TIME/HIER:CALENDAR
 
GLOBAL_AW GLOBAL  UNITS_CUBE   ########## DIM:CHANNEL/HIER:CHANNEL_ROLLUP;   GLOB_GLOBA_UNITS_CU10VIEW
                                          DIM:CUSTOMER/HIER:SHIPMENTS_ROLLUP;
                                          DIM:PRODUCT/HIER:PRODUCT_ROLLUP;
                                          DIM:TIME/HIER:CALENDAR

You can query the following Active Catalog view to obtain the names of the measures in the cubes.

SQL>select * from all_olap2_aw_cube_measures where aw_name = 'GLOBAL';

AW_OWNER   AW_NAM  AW_CUBE_NAM   AW_MEASURE_ AW_PHYSICAL_  MEASURE_SOU  DISPLAY_NAM  DESCRIPTI   IS_AGGR
---------  ------- ----------    ----------  ------------  -----------  -----------  ----------   -----
GLOBAL_AW  GLOBAL  PRICE_CUBE    UNIT_COST    UNIT_COST    UNIT_COST    UNIT COST    Unit Cost    YES
GLOBAL_AW  GLOBAL  PRICE_CUBE    UNIT_PRICE   UNIT_PRICE   UNIT_PRICE   UNIT PRICE   Unit Price   YES
GLOBAL_AW  GLOBAL  UNITS_CUBE    UNITS        UNITS        UNITS        UNITS        Units Sold   YES

The following statement creates a numeric formula PRICE_COST in the analytic workspace GLOBAL in the GLOBAL_AW schema. The formula calculates the difference between unit prices and unit costs. The resulting data is returned in the view GLOBAL_AW.GLOB_GLOBA_UNITS_CU10VIEW.

SQL>execute dbms_aw_utilities.create_custom_measure 
           ('GLOBAL_AW.GLOBAL', 'PRICE_COST', 
            'UNIT_PRICE - UNIT_COST', 'temporary',
            'GLOBAL_AW.GLOB_GLOBA_UNITS_CU10VIEW'); 

Custom Measure 'PRICE_COST' created in Workspace 'GLOBAL_AW.GLOBAL'.Custom Measure 'PRICE_COST' mapped to column 'CUST_MEAS_NUM1' 
               in View 'GLOBAL_AW.GLOB_GLOBA_UNITS_CU10VIEW'.

With the following query, you can see your new custom measure listed in the CWM2$_AW_temp_CUST_MEAS_MAP table.

SQL>select * from olapsys.CWM2$_AW_TEMP_CUST_MEAS_MAP 
                         where workspace_name = 'GLOBAL_AW.GLOBAL';

AW_ACCESS_VIEW_NAME                  CUST_ADT_COLUMN  WORKSPACE_NAME    AW_MEASURE_NAME   SESSIONID  USERNAME-----------------------------------  ---------------  ----------------- --- ------------  ---------- -----GLOBAL_AW.GLOB_GLOBA_UNITS_CU10VIEW  CUST_MEAS_NUM1   GLOBAL_AW.GLOBAL  PRICE_COST          325      MYUSER

To obtain the data resulting from the custom calculation, use the following query.

SQL>select CUST_MEAS_NUM1 from GLOBAL_AW.GLOB_GLOBA_UNITS_CU10VIEW;