Case Study: Adding Sales to Global Using DBMS_AW_UTILITIES - OLAP

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


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.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

OLAP Topics