Skip Headers

Oracle® OLAP Application Developer's Guide
10g Release 1 (10.1)

Part Number B10333-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

7 SQL Access to Analytic Workspaces

This chapter introduces methods of accessing the data in an analytic workspace using SQL. Most of these methods can be used at runtime as part of an application.

This chapter contains the following topics:

7.1 Overview of SQL Access

Using SQL, you can manipulate analytic workspace data and extract that data into your application. There are various methods that you can use, and the best one depends on the type of analytic workspace you have, the particular task you want to accomplish, and your personal preferences.

7.1.1 Manipulating Analytic Workspace Data

To manipulate analytic workspace data using SQL, you must use PL/SQL procedures that execute OLAP DML commands. The OLAP DML is the language for working in an analytic workspace. Using it, you can create, modify, delete, and populate workspace objects. Any method that you use for performing these tasks uses the OLAP DML.

Several PL/SQL packages are available that execute OLAP DML commands. A call to a single procedure can execute a single OLAP DML command, or dozens of commands to perform a specific task. Among these packages are:

  • DBMS_AW contains procedures for executing individual OLAP DML commands.

  • DBMS_AW_UTILITIES contains procedures for managing custom measures in standard form analytic workspaces that have been enabled for the BI Beans.

  • DBMS_AWM contains procedures for creating standard form analytic workspaces.

You can use any of these packages directly in a SQL interface such as SQL*PLus. Analytic Workspace Manager and OLAP Worksheet are applications that use these SQL packages. Figure 7-1 shows the relationships among them.

Figure 7-1 Analytic Workspace Manager's Use of PL/SQL Packages

Diagram of relationships among GUIs, APIs, and OLAP DML.
Description of the illustration dbms_aw.gif

7.1.2 Querying an Analytic Workspace

The OLAP_TABLE function provides the basic technology for querying an analytic workspace, as described in "Using OLAP_TABLE for Direct Access to Workspace Data". It operates outside of the conventions of standard form, and can access data from any analytic workspace. However, tools that use OLAP_TABLE, such as the enablers, require standard form to construct the appropriate syntax.

7.1.3 About the Active Catalogs

Oracle OLAP provides catalogs of information about standard form analytic workspaces. These active catalogs are generated and maintained automatically without requiring any action by the DBA.

The active catalogs are implemented as public views with names that begin ALL_OLAP2_AW. For example, ALL_OLAP2_AW_CUBES lists cubes in all analytic workspaces, and ALL_OLAP2_AW_DIMENSIONS lists all of the dimensions. You can query the active catalogs directly from SQL.

For descriptions of the active catalogs, refer to the Oracle OLAP Reference.

7.2 Support for Custom Measures

A custom measure is calculated from one or more measures stored in the analytic workspace. Often, it is created by an analyst just for the duration of a session. However, a custom measure can also be saved as a permanent part of the analytic workspace.

These saved custom measures can either be solved at run-time or stored in variables. Run-time calculations do not require disk storage space and do not extend the processing time required for data maintenance. However, they may slow performance. You need to decide which measures to calculate on demand and which, if any, to store. The custom measures described in this chapter are calculated for a query. For instructions on creating stored custom measures, refer to Chapter 9.

7.2.1 Methods of Defining Custom Measures

Two PL/SQL packages support custom measures in an analytic workspace:

  • DBMS_AW_UTILITIES contains procedures for creating, updating, and deleting custom measures. This package operates only on the views created by the enabler for the BI Beans. The custom measures are stored in the predefined columns provided in these views for custom measures. You can define a custom measure to persist either for the duration of the session or permanently.

  • DBMS_AW contains various procedures to execute OLAP DML commands. Several of them can be used in SELECT statements to execute a calculation or data manipulation in the analytic workspace. The calculations are returned along with the rest of the result set. This type of custom measure exists only for the duration of the SELECT statement.

In addition, you can use the OLAP_TABLE function to define and access custom measures outside of the framework of standard form, as described in "Using OLAP_TABLE for Direct Access to Workspace Data".

7.2.2 Analytic Support for Custom Measures

Regardless of the method that you use to define a custom measure, you will express the calculation itself using the OLAP DML. Following are descriptions of the many functions and commands available for manipulating your data. In addition, you can perform inter-row calculations using operators for multiplication (*), division (/), addition (+), subtraction (-), and so forth.

7.2.2.1 Forecasts and Regressions

The OLAP DML offers the most sophisticated and up-to-date forecasting and regression tools, including simple linear regressions, non-linear regression methods, single exponential smoothing, double exponential smoothing, and the Holt-Winters method.

7.2.2.2 Time Series Manipulation

The time series functions perform operations such as lead, lag, and moving average. Table 7-1 describes the time series functions, which can easily be incorporated into custom measures.

Table 7-1 OLAP DML Time Series Functions

Function Returns
CUMSUM Cumulative totals
LAG Value for a previous time period at a specified offset
LAGABSPCT Percentage difference between a value and the absolute value for a previous time period at a specified offset
LAGDIF Difference between a value and the value for a previous time period at a specified offset
LAGPCT Percentage difference between a value and the value for a previous time period at a specified offset
LEAD Value for a subsequent time period at a specified offset
MOVINGAVERAGE A series of averages over a specified range
MOVINGMAX A series of maximum values over a specified range
MOVINGMIN A series of minimum values over a specified range
MOVINGTOTAL A series of totals over a specified range

7.2.2.3 Financial Operations

The financial functions include interest rate calculations, depreciation, and payment schedules, similar to those provided in spreadsheets.

For example, the FPMTSCHED function calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods. The following call to FPMTSCHED calculates 36 payments based on the amounts listed in the LOANS variable, at the interest rates listed in the RATES variable, for the MONTH dimension of these variables.

FPMTSCHED(loans, rates, 36, month)

7.2.2.4 Statistical Operations

Statistical operations include standard deviation, rank, and correlation. For example, the STDDEV function calculates the standard deviation. The function call

STDDEV(units month)

returns the standard deviation of values in the UNITS measure for all months that are currently selected.

7.2.2.5 Numeric Computations

Functions are available to perform a wide variety of computations (such as sine, cosine, square root, minimum, and maximum) and data type conversions.

For example, the MAX function compares two expressions and returns the larger value. This function call

MAX(actual, forecast)

compares the ACTUAL and FORECAST measures and returns the larger values for all dimension members currently selected.

7.2.2.6 Text Manipulation

The OLAP DML provides support for manipulating both single- and multibyte character sets, with functions for concatenating strings, locating a string within a larger body of text, inserting a string, and so forth.

For example, the EXTCHARS function extracts a portion of text. The function call

EXTCHARS('lastname,firstname', 1,8)

extracts the first 8 characters, which contains the characters

lastname

7.2.2.7 Allocation

Allocations are a critical part of planning applications. Given a target for the organization, whether for sales quota, product growth, salary, or equipment, managers must allocate that target among its contributors. The supported allocation methods include:

  • Copy methods (hierarchical copy, minimum, maximum, first, last)

  • Even distribution (even, hierarchical even)

  • Proportional distribution (including weighted distributions and user-defined multidimensional functions)

7.2.2.8 Aggregation

Aggregation is a basic feature of analytic workspaces. When you create a standard form analytic workspace, it contains a default aggregation plan for each cube. Wizards in Analytic Workspace Manager enable you to identify stored aggregate levels quickly and easily.

The OLAP DML offers a broader range of aggregation methods than are currently available through Analytic Workspace Manager or PL/SQL procedures. You can choose whatever method seems appropriate: by level, individual member, member attribute, time range, data value, or other criteria.

7.2.2.9 Models

A model is a set of interrelated equations. These are some of the modeling features supported by the OLAP DML:

  • You can perform calculations for individual dimension members following unique calculation rules.

  • Oracle OLAP determines the order of the calculations, so you can list them in any order without concern for dependencies.

  • Oracle OLAP solves simultaneous equations.

You can assign results either to a variable or to a dimension member. Dimension-based equations provide flexibility; since you do not need to specify the modeling variable until you solve a model, you can run the same model with any other measure with the same dimension. For example, you could run the same model on Budget and Actual, which both have a Line dimension.

7.3 Creating Custom Measures Using DBMS_AW_UTILITIES

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:

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

7.4.1 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 7-1 shows how to obtain the names of the measures.

Example 7-1 Querying the Active Catalog for Measure Names

SELECT aw_cube_name, aw_measure_name   FROM all_olap2_aw_cube_measures   WHERE aw_owner = 'GLOBAL_AW' AND   aw_name = 'GLOBAL';

AW_CUBE_NAME  AW_MEASURE_NAME  
------------- -----------------
PRICE_CUBE    UNIT_COST  
PRICE_CUBE    UNIT_PRICE  
UNITS_CUBE    UNITS  

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

Example 7-2 SELECT Statement for Querying the Active Catalog

SELECT cube_name, system_viewname, hiercombo_str 
   FROM all_aw_cube_enabled_views WHERE
   aw_name =   'GLOBAL' AND
   cube_name = 'PRICE_CUBE' OR
   cube_name = 'UNITS_CUBE';

CUBE_NAME    SYSTEM_VIEWNAME           HIERCOMBO_STR
------------ ------------------------- --------------------------------------------------------
PRICE_CUBE  GLOB_GLOBA_PRICE_CU4VIEW   DIM:PRODUCT/HIER:PRODUCT_ROLLUP;DIM:TIME/HIER:Calendar  
UNITS_CUBE  GLOB_GLOBA_UNITS_CU9VIEW   DIM:CHANNEL/HIER:CHANNEL_ROLLUP;
                                       DIM:CUSTOMER/HIER:MARKET_SEGMENT;
                                       DIM:PRODUCT/HIER:PRODUCT_ROLLUP;
                                       DIM:TIME/HIER:Calendar  
UNITS_CUBE  GLOB_GLOBA_UNITS_CU10VIEW  DIM:CHANNEL/HIER:CHANNEL_ROLLUP;
                                       DIM:CUSTOMER/HIER:SHIPMENTS;
                                       DIM:PRODUCT/HIER:PRODUCT_ROLLUP;
                                       DIM:TIME/HIER:Calendar  

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


Note:

Whenever you use DBMS_AW_UTILITIES in a SQL environment such as SQL*Plus, be sure to begin with these settings:
SET SERVEROUT ON
EXECUTE CWM2_OLAP_MANAGER.SET_ECHO_ON

Otherwise, you will not see any diagnostic messages.


Example 7-3 Defining SALES Using DBMS_AW_UTILITIES

SET SERVEROUT ON
EXECUTE CWM2_OLAP_MANAGER.SET_ECHO_ON
EXECUTE DBMS_AW_UTILITIES.CREATE_CUSTOM_MEASURE(
     'global_aw.global', 'sales', 'units * unit_price', 
     'PERMANENT', 'global_aw.glob_globa_units_cu9view');

EXECUTE DBMS_AW_UTILITIES.CREATE_CUSTOM_MEASURE(
     'global_aw.global', 'sales', '', 
     'PERMANENT', 'global_aw.glob_globa_units_cu10view');

7.4.3 Viewing the Workspace Formula

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

EXECUTE DBMS_AW.EXECUTE('DESCRIBE sales');

DEFINE SALES FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL> EQ units * unit_price 

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

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

SELECT aw_access_view_name, cust_adt_column, aw_measure_name 
   FROM olapsys.cwm2$_aw_perm_cust_meas_map 
   WHERE workspace_name = 'global_aw.global';

AW_ACCESS_VIEW_NAME                  CUST_ADT_COLUMN  AW_MEASURE_NAME 
------------------------------------ ---------------- --------------- 
global_aw.glob_globa_units_cu9view   CUST_MEAS_NUM1   sales  
global_aw.glob_globa_units_cu10view  CUST_MEAS_NUM1   sales  

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

7.5 Creating Custom Measures Using OLAP_EXPRESSION

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

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')

7.6 Case Study: Adding Sales to Global Using OLAP_EXPRESSION

Enablement for the BI Beans created two views of the Units cube, one for each of the two hierarchies for the CUSTOMER dimension. The following SELECT statement queries one of the views and generates a new column for Sales. The SALES column is calculated in the analytic workspace.

SELECT time_et, units, OLAP_EXPRESSION(r2c, 'units * unit_price') sales 
   FROM global_aw.glob_globa_units_cu9view WHERE 
      channel_et ='1' AND 
      product_et = '4' AND 
      customer_et = '24' AND 
      time_et > '66' AND 
      units IS NOT NULL 
   ORDER BY OLAP_EXPRESSION(r2c, 'units * unit_price') DESC;

The result set of this SELECT statement is sorted so that the sales figures are listed in descending order.

TIME_ET   UNITS      SALES 
--------- -------- ----------
 8         6       170017.38
68         5       123300.25 
 9         3        93293.85 
 7         3        64931.7 
67         2        50932.26  

7.7 Using OLAP_TABLE for Direct Access to Workspace Data

The OLAP_TABLE function provides the basic technology for extracting data from an analytic workspace. All of the views of analytic workspaces that are generated by the enablers use the OLAP_TABLE function. By using OLAP_TABLE directly, you have full control over data access. You can develop your own views to support applications for which there are no enablers, and you can extract workspace data directly into your application. This capability can provide your application with tremendous flexibility, since user queries can be formulated into calls to OLAP_TABLE at runtime.

While the OLAP tools that use the OLAP_TABLE function require a standard form analytic workspace, the OLAP_TABLE function itself does not use standard form metadata.

7.7.1 Designing Views of an Analytic Workspace

The number of views that you create, and the number and characteristics of the columns in these views, depends largely on the requirements of the applications that these views are designed to support.

Because analytic workspaces contain aggregate data, the views must include the aggregates. There are several formats for presenting aggregate data:

  • Create a star schema with dimension views and measure views. The dimension views list dimension members at all levels in a single column.

  • Create a view that includes columns for all of the dimensions, attributes, and measures.

  • Create a view in rollup form that shows the full parentage of each dimension member in multiple columns.

  • Create a separate table for each aggregation level.

Choose a format that is appropriate for your application and its metadata.

7.7.2 Process Overview

These are the basic steps you must follow to generate views of data stored in an analytic workspace.

  1. Explore the analytic workspace and identify the variables, formulas, relations, and dimensions that you want to expose to your application.

  2. Decide how you want to present these objects in relational tables or views, based on the requirements of the application that will use them.

  3. For each table or view that you plan to create, issue a SELECT statement using the OLAP_TABLE function. The SELECT statement can be an argument to a CREATE VIEW statement.

  4. Commit these changes to the database if you are creating views for general use.

  5. Create whatever metadata is required by your application to query the views.

7.7.3 Using OLAP_TABLE

You use the OLAP_TABLE function in a SQL SELECT statement to query the multidimensional data stored in an analytic workspace. OLAP_TABLE can be used wherever you would use the name of a table or view. You can use SELECT statements to create views, or to fetch data directly from an analytic workspace into an application.

OLAP_TABLE returns a table of objects that can be joined to relational tables and views, or to other tables of objects populated by OLAP_TABLE. It can also return stored workspace data, or it can perform calculations on stored data and return the results of the calculations.

Example 7-4 is a template that you can use as the starting point for the SQL scripts that you will develop for extracting data from your analytic workspace. You can then execute the script with the @ command in SQL*Plus.


Note:

Be sure to verify that you have created the views correctly by issuing SELECT statements against them. Only at that time will any errors in the call to OLAP_TABLE appear.

Example 7-4 Template for Using OLAP_TABLE

SET ECHO ON
SET SERVEROUT ON

--CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, columnn
   FROM TABLE(OLAP_TABLE(
      'connection', 
      'table_obj', 
      'datamap',
      'limit_map'))
    MODEL (
      DIMENSION BY(et_dims, gids)
              MEASURES(measures, attributes, row2cell)
      RULES UPDATE SEQUENTIAL ORDER();
/
COMMIT
/ 
GRANT SELECT ON view_name TO PUBLIC; 

See Also:

Oracle OLAP Reference for a description of OLAP_TABLE syntax.

7.7.4 Using the SELECT MODEL Clause

When used in a SELECT statement that queries OLAP_TABLE, the MODEL clause is an optimization that results in significantly faster response time. It can be used only when creating a table type with embedded total dimensions, such as the views used by the BI Beans and the OLAP API.

Note that while the MODEL clause is used in relational queries for inter-row calculations, you should not use it for this purpose with OLAP_TABLE. For OLAP_TABLE, the MODEL clause is used only to optimize the query.


See Also:

The SELECT entry in the Oracle Database SQL Reference for general information about the MODEL clause.

When used in a SELECT statement that queries an analytic workspace, MODEL has the following arguments.

DIMENSION BY

The names of the embedded total dimension columns, as defined in the limit map. For BI Beans applications, include the GID columns in this list.

Any other columns in the DIMENSION BY list disables this optimization. A properly constructed SELECT statement still executes, but more slowly.

MEASURES

The measures, attributes, R2Cs, and any other columns excluded from the DIMENSION BY list.

RULES UPDATE SEQUENTIAL ORDER

The RULES clause is required, but it should not include complex or inter-row calculations since they will slow the query. Any calculations specified in the RULES clause are performed by SQL. If you want to perform inter-row calculations, you can create a custom measure in the analytic workspace using any of the alternative methods discussed in this chapter, including the limit map of OLAP_TABLE.

UPDATE indicates that you are not adding any custom members in the DIMENSION BY clause. Be sure to include this keyword, because otherwise the SQL WHERE clauses for measures are discarded, which can significantly degrade performance.

SEQUENTIAL ORDER prevents Oracle from evaluating the rules to ascertain their dependencies.

7.8 Case Study: Using OLAP_TABLE to Create Global Custom Measures

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 (described previously in this chapter) 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:

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

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

DEFINE units_pp FORMULA LAG(units, 1, time, LEVELREL time_levelrel)
UPDATE;COMMIT

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

DEFINE UNITS_PP FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL>
EQ lag(units, 1, time, levelrel time_levelrel)

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

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

7.8.2 Querying an Analytic Workspace Using OLAP_TABLE

Example 7-5 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 7-5. 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.

Example 7-5 UNITS_QUERY Script for Querying with OLAP_TABLE

SELECT time_name, units, units_pp FROM TABLE(OLAP_TABLE(
      'global DURATION SESSION',
      '',
      'LIMIT customer_hierlist TO 2',
      'MEASURE units AS NUMBER(16) FROM units
       MEASURE units_pp AS NUMBER(16) FROM units_pp
       DIMENSION channel_dim FROM channel WITH
         HIERARCHY channel_parentrel
       DIMENSION product_dim FROM product WITH
         HIERARCHY product_parentrel      
       DIMENSION customer_dim FROM customer WITH
         HIERARCHY customer_parentrel
       DIMENSION time_dim FROM time WITH
         HIERARCHY time_parentrel
         ATTRIBUTE time_name AS VARCHAR2(8) FROM time_long_description'))
WHERE units IS NOT NULL and
      channel_dim = '1' and
      product_dim = '1' and
      customer_dim = '21';
MODEL
DIMENSION BY(channel_dim, product_dim, customer_dim, time_dim)
        MEASURES(units, units_pp, time_name) 
RULES UPDATE SEQUENTIAL ORDER ();
/

Example 7-6 shows the results of running the script in Example 7-5.

Example 7-6 Results of Running the UNITS_QUERY Script

@units_query

TIME_NAM      UNITS   UNITS_PP
-------- ---------- ----------
Jan-98        11357
Feb-98        11336      11357
Mar-98        11184      11336
                .
                .
                .
2001         230913     202580
2002         201590     230913
2003         109711     201590

93 rows selected.

7.8.2.1 OLAP_TABLE Function

In Example 7-5, 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.

7.8.2.2 SELECT Statement

In Example 7-5, 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.

7.8.3 Using OLAP_TABLE to Create a Measure View for the BI Beans

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

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

Example 7-7 Creating Views for the OLAP API

CREATE OR REPLACE VIEW ts_view_1 AS SELECT * FROM TABLE(OLAP_TABLE(
      'global DURATION SESSION',
      '',
      'LIMIT customer_hierlist to 1',
      'MEASURE units AS NUMBER(16) FROM units
       MEASURE units_pp AS NUMBER(16) FROM units_pp
       MEASURE units_pctchg_pp AS NUMBER(8,2) 
          FROM AW_EXPR LAGPCT(units, 1, time LEVELREL time_levelrel
       ROW2CELL r2c
       DIMENSION channel_et AS VARCHAR2(4) FROM channel WITH
         HIERARCHY channel_parentrel
         INHIERARCHY channel_inhier
         GID channel_gid AS NUMBER(2) FROM channel_gid
       DIMENSION product_et AS VARCHAR2(4) FROM product WITH
         HIERARCHY product_parentrel
         INHIERARCHY product_inhier
         GID product_gid AS NUMBER(2) FROM product_gid 
       DIMENSION customer_et AS VARCHAR2(4) FROM customer WITH
         HIERARCHY customer_parentrel
         INHIERARCHY customer_inhier
         GID customer_gid AS NUMBER(2) from customer_gid
       DIMENSION time_et AS VARCHAR2(8) FROM time WITH
         HIERARCHY time_parentrel
         INHIERARCHY time_inhier
         GID time_gid AS NUMBER(2) FROM time_gid'))
  WHERE units IS NOT NULL
MODEL
         DIMENSION BY(channel_et, channel_gid, product_et, product_gid,
           customer_et, customer_gid, time_et, time_gid)
         MEASURES(units, units_pp, units_pctchg_pp,r2c)
         RULES UPDATE SEQUENTIAL ORDER (); 

7.8.3.2 About the Sample Script

Example 7-7 defines a view that conforms to the requirements of the OLAP API for a 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.

7.8.4 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 7-8 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 7-8. Save it with a name such as ts_cwm.sql.

    Refer to the Oracle OLAP Reference for the complete syntax and usage notes for the CWM2 APIs.

  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:

    1. Issue a ROLLBACK command,

    2. Fix the errors in the script.

    3. 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 7-2 shows the result set of a query issued through a BI Beans application.

Figure 7-2 New Measures Queried Using a BI Beans Sample Application

Capture of new measures in BI Beans sample app.
Description of the illustration analyzer.gif

Example 7-8 Script for Creating OLAP Catalog Metadata for GLOBAL Measures

BEGIN
-- Define TS_CUBE cube with predefined dimensions
CWM2_OLAP_CUBE.CREATE_CUBE('GLOBAL_AW', 'TS_CUBE', 'TS Cube', 'TS Cube', 'Units Time Series Cube');
CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'CHANNEL');
CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'PRODUCT');
CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'CUSTOMER');
CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'Time');
CWM2_OLAP_MEASURE.CREATE_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PP', 'Units PP', 
   'Units Prior Period', 'Units Sold in Prior Period');
CWM2_OLAP_MEASURE.CREATE_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP', 'Units PctChgPP', 
   'Units Pct Chg PP', 'Percent Difference in Units Sold From Prior Period');

-- Map TS_VIEW_1 view to metadata cube TS_CUBE
CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_LEVELKEY('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'TS_VIEW_1', 'ET',
   'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET;
   DIM:GLOBAL_AW.CUSTOMER/HIER:SHIPMENTS/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET;
   DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET;
   DIM:GLOBAL_AW.Time/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET');
 
CWM2_OLAP_TABLE_MAP.ADD_AWVIEW('GLOBAL_AW', 'TS_VIEW_1', 'r2c'); 

CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PP', 'GLOBAL_AW', 'TS_VIEW_1', 'UNITS_PP',
   'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET;
   DIM:GLOBAL_AW.CUSTOMER/HIER:SHIPMENTS/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET;
   DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET;
   DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;');

CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP', 'GLOBAL_AW', 'TS_VIEW_1', 'UNITS_PCTCHG_PP',
   'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET;
   DIM:GLOBAL_AW.CUSTOMER/HIER:SHIPMENTS/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET;
   DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET;
   DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;');

CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_LEVELKEY('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'TS_VIEW_2', 'ET',
   'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET;
   DIM:GLOBAL_AW.CUSTOMER/HIER:MARKET_SEGMENT/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET;
   DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET;
   DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;');
 
CWM2_OLAP_TABLE_MAP.ADD_AWVIEW('GLOBAL_AW', 'TS_VIEW_2', 'r2c'); 

-- Map TS_VIEW_2 view to metadata cube TS_CUBE
CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PP', 'GLOBAL_AW', 'TS_VIEW_2', 'UNITS_PP',
   'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET;
   DIM:GLOBAL_AW.CUSTOMER/HIER:MARKET_SEGMENT/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET;
   DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET;
   DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;');

CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP', 'GLOBAL_AW', 'TS_VIEW_2', 'UNITS_PCTCHG_PP',
   'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET;
   DIM:GLOBAL_AW.CUSTOMER/HIER:MARKET_SEGMENT/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET;
   DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET;
   DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;');

-- Validate the cube metadata
CWM2_OLAP_VALIDATE.VALIDATE_CUBE('GLOBAL_AW', 'TS_CUBE', 'OLAP API');

-- Create a measure folder
CWM2_OLAP_CATALOG.CREATE_CATALOG('GLOBAL_ANALYTIC_CAT', 'Global Analytic Measures');
CWM2_OLAP_CATALOG.ADD_CATALOG_ENTITY('GLOBAL_ANALYTIC_CAT', 'GLOBAL_AW', 'TS_CUBE', 'UNITS');
CWM2_OLAP_CATALOG.ADD_CATALOG_ENTITY('GLOBAL_ANALYTIC_CAT', 'GLOBAL_AW', 'TS_CUBE', 'UNITS_PP');
CWM2_OLAP_CATALOG.ADD_CATALOG_ENTITY('GLOBAL_ANALYTIC_CAT', 'GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP');
--COMMIT;
end; /