Case Study:Using OLAP_TABLE to Create Global Custom Measures - OLAP

The Global Corporation requires numerous custom measures in addition to the three stored measures fetched from a star schema into the GLOBAL analytic workspace. The OLAP_TABLE function offers a method of creating these derived measures, although other methods are also available to GLOBAL.

UNITS is one of the stored measures, and the units for the prior period is a required derived measures. Although they are not required, other derived measures such as the difference from the prior period or the percent change may also be desirable. Derived measures can be defined permanently in the analytic workspace or specified in the syntax of the OLAP_TABLE function. This example adds these two measures:

• UNITS_PP calculates the units sold in the prior period.
• UNITS_PCTCHG_PP is the percent change from the prior period.

This example creates a new OLAP Catalog cube for these measures.

Defining Formulas in the Analytic Workspace

If it does not already exist, add UNITS_PP, which returns the value of the prior time period, to the GLOBAL analytic workspace with these commands:

This syntax for defining a formula gives it the same data type and dimensionality as the source object. The new formula has this definition:

Alternatively, you can define UNITS_PP using the property sheets in Analytic Workspace Manager.

Example defines UNITS_PCTCHG_PP in the OLAP_TABLE function, using the OLAP DML LAGPCT function. UNITS_PCTCHG_PP calculates the percent change from the prior period.

Neither UNITS_PP nor UNITS_PCTCHG_PP are defined as standard form measures. To comply with standard form, they need several OLAP DML properties, and they must be registered as measures in the standard form catalogs. However, OLAP_TABLE and the OLAP Catalog do not require standard form; only the tools that simplify their use require standard form.

Querying an Analytic Workspace Using OLAP_TABLE

Example shows a script that fetches data directly into a SQL application using a SELECT statement with the OLAP_TABLE function. This selection is separate from any application enablement process.

To query the Units measures in the GLOBAL analytic workspace, take these steps:

1. Open a file with any text editor, and enter the body of the SQL script shown in Example. Save it with a name such as units_query.sql.
2. Open a SQL*Plus session with a user name that has access rights to the GLOBAL analytic workspace.
3. Execute the SQL script with a command like this one:
   @units_query

There is neither standard form metadata nor application metadata for UNITS_PP. An explanation of the example follows the code.

UNITS_QUERY Script for Querying with OLAP_TABLE

Results of Running the UNITS_QUERY Script

In Example, the arguments to OLAP_TABLE provide the most basic information: the measures you want to see, their dimensions, and the descriptive names for time periods that make this data meaningful. In addition, the OLAP_TABLE function needs the names of the parent relations, which define the hierarchical structure of the dimensions. Since these dimensions were created by the Create Analytic Workspace wizard in Analytic Workspace Manager, the parent relations are named dimension_PARENTREL.

The CUSTOMER dimension has two hierarchies, and a LIMIT command selects the second hierarchy, MARKET_ SEGMENTS; SHIPMENTS is the first hierarchy in the CUSTOMER_ HIERLIST hierarchy dimension, and so it is the default. The other dimensions have only one hierarchy, so there is no need to limit their hierlist dimensions..

The limit map identifies two measures (UNITS and UNITS_PP), both of which are formulas in the analytic workspace. UNITS calculates aggregates from a stored measure, and UNITS_PP returns the value of the prior period, as defined in "Defining Formulas in the Analytic Workspace". Data types are specified only for the selected columns: TIME_NAME, UNITS, and UNITS_PP.

SELECT Statement

In Example, the SELECT statement identifies the columns and rows of interest, just as it does for physical tables in the database. In this particular selection, the WHERE clause limits all dimensions except TIME to a single value, then labels the result set only with the long descriptions for TIME.

Using OLAP_TABLE to Create a Measure View for the BI Beans

Example shows how you can make the data in an analytic workspace available to the BI Beans using OLAP_TABLE. The process involves these steps:

1. Create views that conform with the requirements of the BI Beans.
2. Define OLAP Catalog metadata so that the views can be queried by the BI Beans.

This example creates a measure view of UNITS, UNITS_PP, and UNITS_PCTCHG_PP for the CUSTOMER MARKET_ROLLUP hierarchy. A second view is required for the SHIPMENTS_ROLLUP hierarchy. The example does not show the dimension views either, although the OLAP Catalog and the BI Beans require views of each dimension.

UNITS_PCTCHG_PP is a custom measure defined in the limit map using the AW_EXPR keyword. It uses the OLAP DML LAGPCT function to calculate the percent difference from the prior period.

Creating and Executing the SQL Script

To create the views for the OLAP API, take these steps:

1. Open a file with any text editor, and enter the body of the SQL script shown in Example. Save it with a name such as ts_views.sql.
2. Open a SQL*Plus session with a user name that has access rights to the GLOBAL analytic workspace.
3. Execute the SQL script with a command like this one:
   @ts_view
4. Commit these changes to the database.
5. Issue SELECT commands against the views to verify that they were defined correctly; if not, an error will be generated.

Creating Views for the OLAP API

About the Sample Script

Example defines a view that conforms to the requirements of the OLAP API fora fact table:

• Each dimension has one embedded total column for its members at all hierarchical levels. The columns are named dimension_ET to match the views generated by the OLAP API enabler.
• Each dimension has a column for its grouping IDs. The columns are named dimension_GID to match the views generated by the OLAP API enabler.
• A ROW2CELL column is defined for use by the OLAP_EXPRESSION function. For each dimension, the view identifies these analytic workspace objects:
• The HIERARCHY relation, which defines the hierarchical relationship among dimension members by identifying the parent of each member.
• The INHIERARCHY variable, which identifies whether a dimension member is in the selected hierarchy.
• The GID variable, as described previously.

These objects were created by the Create Analytic Workspace wizard. Notice that the GID variables are the only ones that are mapped to columns in the view.

Defining OLAP Catalog Metadata for Workspace Views

To define OLAP Catalog metadata for views of an analytic workspace, you must use the CWM2 write APIs. You can then view CWM2 metadata in the OLAP Catalog view of Analytic Workspace Manager, or by querying the OLAP Catalog views directly in SQL. You can neither define nor view CWM2 metadata using Oracle Enterprise Manager.

The new measures (UNITS_PP and UNITS_PCTCHG_PP) could be added to the existing Units cube. However, Example shows how you can create a new cube for them using predefined dimensions. The example also creates a new measure folder.

To create the OLAP Catalog metadata for the new measures, follow these steps:

1. Open a file with any text editor, and enter the body of the SQL script shown in Example. Save it with a name such as ts_cwm.sql.
2. Open a SQL*Plus session with a user name that has access rights to the GLOBAL analytic workspace and issue these commands:
   SET ECHO ON SET LINESIZE 135 SET PAGESIZE 50 SET SERVEROUTPUT ON FORMAT WRAPPED SIZE 1000000 EXECUTE CWM2_OLAP_MANAGER.SET_ECHO_ON;

   These settings enable you to see any error messages and view the full report from the validation programs that are run by the script. It is important to validate the metadata before committing it to your database.

3. Execute the SQL script with a command like this one:
   @ts_cwm Note: If the validation messages exceed the maximum buffer size for SQL*Plus, you can redirect them to a log file by using CWM2_OLAP_MANAGER.BEGIN_LOG.
4. If there are errors, then take these steps:
   
   • Issue a ROLLBACK command,
   • Fix the errors in the script.
   • Rerun the script.
5. Copy the metadata to special views for the BI Beans:
   EXECUTE CWM2_OLAP_METADATA_REFRESH.MR_REFRESH(); This procedure issues a COMMIT.

Once these measures are defined in the OLAP Catalog, they are available to BI Beans applications the same as the standard form measures. Figure shows the result set of a query issued through a BI Beans application.

New Measures Queried Using a BI Beans Sample Application

Script for Creating OLAP Catalog Metadata for GLOBAL Measures