7 SQL Access to Analytic Workspaces

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

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

FPMTSCHED(loans, rates, 36, month)

STDDEV(units month)

MAX(actual, forecast)

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

lastname

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.

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.

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.

SET SERVEROUT ON
EXECUTE CWM2_OLAP_MANAGER.SET_ECHO_ON

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

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; 

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.

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

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.

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

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

Description of the illustration analyzer.gif

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; /