# Creating Custom Measures Using DBMS_AW_UTILITIES - OLAP

The enabler for the BI Beans creates fact views with columns specifically for custom measures defined by the DBMS_AW_UTILITIES package. There are 100 columns for numeric data named CUST_MEAS_NUM1 to CUST_MEAS_NUM100, and 100 columns for text data named CUST_MEAS_TEXT1 to CUST_MEAS_TEXT100.
This is the basic syntax for creating a custom measure:

CALL DBMS_AW_UTILITIES.CREATE_CUSTOM_MEASURE(schema.aw_name, aw_formula_name, aw_formula_expression, 'PERMANENT'|'TEMPORARY', schema.view_name;

The BI Beans enabler creates CWM2 metadata for the views of analytic workspaces, and DBMS_AW_UTILITIES creates CWM2 metadata for the custom measures added to these views. This metadata is stored in tables that identify the mapping between the custom measures and the generic column names of the view:

• CWM2$_AW_TEMP_CUST_MEAS_MAP lists temporary custom measures for the current user. • CWM2$_AW_PERM_CUST_MEAS_MAP lists permanent custom measures for users with the DBA role.

Case Study: Adding Sales to Global Using DBMS_AW_UTILITIES

"Identifying Required Business Facts" identifies the data requirements of the Global Corporation. Only three facts are stored in the star schema; the others must be calculated in the analytic workspace. Because GLOBAL is a standard form analytic workspace that has been enabled for the BI Beans, the DBMS_AW_UTILITIES package is available for the DBA to define these measures.

Acquiring Information About the Analytic Workspace

Before you can define custom measures, you must know the names of measures that are already defined in the analytic workspace. You can query the ALL_ OLAP2_ AW_ CUBE_ MEASURES view in the Active Catalog for the names of measures defined in the GLOBAL analytic workspace. Example shows how to obtain the names of the measures.

Querying the Active Catalog for Measure Names

The ALL_AW_CUBE_ENABLED_VIEWS view identifies the cubes that are enabled for the BI Beans, the names of the views created by the enabler to access those cubes, and the dimensions and dimension hierarchies for each view. Example shows that the Price cube is dimensioned by PRODUCT and TIME, and can be queried through a view named GLOB_GLOBA_PRICE_CU4VIEW. The Units cube is dimensioned by CHANNEL, CUSTOMER, PRODUCT, and TIME. The CUSTOMER dimension has two hierarchies: MARKET_ SEGMENT is shown in GLOB_ GLOBA_UNITS_CU9VIEW and SHIPMENTS is shown in GLOB_GLOBA_UNITS_CU10VIEW.

SELECT Statement for Querying the Active Catalog

Using DBMS_AW_UTILITIES to Define Sales as a Custom Measure

After getting the information you need to define a custom measure, you can define your custom measures using DBMS_AW_UTILITIES. This example defines SALES, which calculates the product of two other measures, UNITS and UNIT_PRICE, for each combination of dimension members.

UNITS is a measure in the Units cube, and UNIT_PRICE is a measure in the Price cube. The Units cube has four dimensions: TIME, PRODUCT, CUSTOMER, and CHANNEL. The Price cube has only two dimensions, TIME and PRODUCT. The product of these two measures will have four dimensions, so SALES must be added to a view of the Units cube.

Example adds the SALES measure to both views for the Units cube. Notice that only the first call specifies the equation for the SALES formula. The second call just identifies the existing SALES formula.

Defining SALES Using DBMS_AW_UTILITIES

Viewing the Workspace Formula

Use this command to see the formula created in the analytic workspace:

You can also view the property sheet for SALES in Analytic Workspace Manager.

Querying the Sales Custom Measure

OLAPSYS.CWM2\$_AW_PERM_CUST_MEAS_MAP identifies the mapping between the SALES custom measure and a column in the views.

Queries for the SALES measure must select values from the CUST_MEAS_NUM1 columns of the two tables.

Creating Custom Measures Using OLAP_EXPRESSION

The DBMS_AW package contains several procedures for specifying run-time calculations.

• OLAP_EXPRESSION performs numeric calculations
• OLAP_EXPRESSION_BOOL performs Boolean calculations
• OLAP_EXPRESSION_DATE performs date calculations
• OLAP_EXPRESSION_TEXT performs text manipulations

You can use these procedures to specify inter-row calculations using SELECT statements on a view of analytic workspace data. The calculations are performed by the OLAP engine. The only requirement for using these functions is that the SELECT statement for the view must contain a call to the OLAP_TABLE function with a ROW2CELL clause. The enabler for the BI Beans generates views of this type, and you can also generate custom views with ROW2CELL columns as described in "Using OLAP_TABLE for Direct Access to Workspace Data".

The syntax of the four functions is identical. The difference between them is only in data type. This is the basic syntax for OLAP_EXPRESSION:

OLAP_EXPRESSION(r2c, expression)

For example: OLAP_EXPRESSION('R2C', 'units * unit_price')