B   DBMS_AW PL/SQL Package

Methods for Executing OLAP DML Commands

The DBMS_AW package provides several procedures for executing ad hoc OLAP DML commands. Using the EXECUTE or INTERP_SILENT procedures or the INTERP or INTERCLOB functions, you can execute a single OLAP DML command or a series of commands separated by semicolons.

Which procedures you use depends on how you want to direct output and on the size of the input and output buffers. For example, the EXECUTE procedure directs output to a printer buffer, the INTERP_SILENT procedure suppresses output, and the INTERP function returns the session log.

The DBMS_AW package also provides functions for evaluating OLAP expressions. The EVAL_TEXT function returns the result of a text expression, and EVAL_NUMBER returns the result of a numeric expression.

Guidelines for Using Quotation Marks in OLAP DML Commands

The SQL processor evaluates the embedded OLAP DML commands, either in whole or in part, before sending them to Oracle OLAP for processing. Follow these guidelines when formatting the OLAP DML commands in the olap-commands parameter of DBMS_AW procedures:

• Wherever you would normally use a single quote (') in an OLAP DML command, use two single quotes (''). The SQL processor strips one of the single quotes before it sends the OLAP DML command to Oracle OLAP.

• In the OLAP DML, a double quote (") indicates the beginning of a comment.

Data Storage Options in Analytic Workspaces

Analytic workspaces analyze and manipulate data in a multidimensional format that allocates one cell for each combination of dimension members. The cell can contain a data value, or it can contain an NA (null). Regardless of its content, the cell size is defined by the data type, for example, every cell in a DECIMAL variable is 8 bytes.

Variables can be either dense (they contain 30% or more cells with data values) or sparse (less than 30% data values). Most variables are sparse and many are extremely sparse.

Although data can also be stored in the multidimensional format used for analysis, other methods are available for storing sparse variables that make more efficient use of disk space and improve performance. Sparse data can be stored in a variable defined with a composite dimension. A composite has as its members the dimension-value combinations (called tuples) for which there is data. When a data value is added to a variable dimensioned by a composite, that action triggers the creation of a composite tuple. A composite is an index into one or more sparse data variables, and is used to store sparse data in a compact form. Very sparse data can be stored in a variable defined with a compressed composite, which uses a different algorithm for data storage from regular composites.

Selecting the Best Data Storage Method

In contrast to dimensional data, relational data is stored in tables in a very compact format, with rows only for actual data values. When designing an analytic workspace, you may have difficulty manually identifying sparsity in the source data and determining the best storage method. The Sparsity Advisor analyzes the source data in relational tables and recommends a storage method. The recommendations may include the definition of a composite and partitioning of the data variable.

The Sparsity Advisor consists of these procedures and functions:

The Sparsity Advisor also provides a public table type for storing information about the dimensions of the facts being analyzed. Three objects are used to define the table type:

The following SQL DESCRIBE statements show the object definitions.

DESCRIBE dbms_aw$_columnlist_t
dbms_aw$_columnlist_t TABLE OF VARCHAR2(100)
 
DESCRIBE dbms_aw$_dimension_source_t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DIMNAME                                            VARCHAR2(100)
 COLUMNNAME                                         VARCHAR2(100)
 SOURCEVALUE                                        VARCHAR2(32767)
 DIMTYPE                                            NUMBER(3)
 HIERCOLS                                           DBMS_AW$_COLUMNLIST_T
 PARTBY                                             NUMBER(9)

DESCRIBE dbms_aw$_dimension_sources_t
dbms_aw$_dimension_sources_t TABLE OF DBMS_AW$_DIMENSION_SOURCE_T

Using the Sparsity Advisor

Take these steps to use the Sparsity Advisor:

1. Call SPARSITY_ADVICE_TABLE to create a table for storing the evaluation of the Sparsity Advisor.

2. Call ADD_DIMENSION_SOURCE for each dimension related by one or more columns to the fact table being evaluated.

   The information that you provide about these dimensions is stored in a DBMS_AW$_DIMENSION_SOURCES_T variable.

3. Call ADVISE_SPARSITY to evaluate the fact table.

   Its recommendations are stored in the table created by SPARSITY_ADVICE_TABLE. You can use these recommendations to make your own judgements about defining variables in your analytic workspace, or you can continue with the following step.

4. Call the ADVISE_DIMENSIONALITY procedure to get the OLAP DML object definitions for the recommended composite, partitioning, and variable definitions.

   or

   Use the ADVISE_DIMENSIONALITY function to get the OLAP DML object definition for the recommended composite and the dimension order for the variable definitions for a specific partition.

Example: Evaluating Sparsity in the GLOBAL Schema

Example B-1, "Sparsity Advisor Script for GLOBAL" provides a SQL script for evaluating the sparsity of the UNITS_HISTORY_FACT table in the GLOBAL schema. In the GLOBAL analytic workspace, UNITS_HISTORY_FACT defines the Units Cube and is the source for the UNITS variable. UNITS_HISTORY_FACT is a fact table with a primary key composed of foreign keys from four dimension tables. A fifth column contains the facts for Unit Sales.

The CHANNEL_DIM and CUSTOMER_DIM tables contain all of the information for the Channel and Customer dimensions in a basic star configuration. Three tables in a snowflake configuration provide data for the Time dimension: MONTH_DIM, QUARTER_DIM, and YEAR_DIM. The PRODUCT_CHILD_PARENT table is a parent-child table and defines the Product dimension.

CONNECT global/global
SET ECHO ON
SET LINESIZE 300
SET PAGESIZE 300
SET SERVEROUT ON FORMAT WRAPPED
 
-- Define and initialize an advice table named AW_SPARSITY_ADVICE
BEGIN
     dbms_aw.sparsity_advice_table();
EXCEPTION
     WHEN OTHERS THEN NULL;
END;
/
 
TRUNCATE TABLE aw_sparsity_advice;
 
DECLARE
     dimsources dbms_aw$_dimension_sources_t;
     dimlist VARCHAR2(500);
     sparsedim VARCHAR2(500);
     defs CLOB;
BEGIN
-- Provide information about all dimensions in the cube
     dbms_aw.add_dimension_source('channel', 'channel_id', dimsources, 
         'channel_dim', dbms_aw.hier_levels,
          dbms_aw$_columnlist_t('channel_id', 'total_channel_id'));
     dbms_aw.add_dimension_source('product', 'item_id', dimsources, 
          'product_child_parent', dbms_aw.hier_parentchild, 
           dbms_aw$_columnlist_t('product_id', 'parent_id'));
     dbms_aw.add_dimension_source('customer', 'ship_to_id', dimsources,
         'customer_dim', dbms_aw.hier_levels,
          dbms_aw$_columnlist_t('ship_to_id', 'warehouse_id', 'region_id',
               'total_customer_id'));
     dbms_aw.add_dimension_source('time', 'month_id', dimsources,
           'SELECT m.month_id, q.quarter_id, y.year_id
                FROM time_month_dim m, time_quarter_dim q, time_year_dim y
                WHERE m.parent=q.quarter_id AND q.parent=y.year_id',
            dbms_aw.hier_levels, 
            dbms_aw$_columnlist_t('month_id', 'quarter_id', 'year_id'));
 
-- Analyze fact table and provide advice without partitioning
     dbms_aw.advise_sparsity('units_history_fact', 'units_cube', 
          dimsources, dbms_aw.advice_default, dbms_aw.partby_none);
 
COMMIT;
 
-- Generate OLAP DML for composite and variable definitions
dimlist := dbms_aw.advise_dimensionality('units_cube', sparsedim, 
           'units_cube_composite');
dbms_output.put_line('Dimension list:  ' || dimlist);
dbms_output.put_line('Sparse dimension:  ' || sparsedim);
dbms_aw.advise_dimensionality(defs, 'units_cube');
dbms_output.put_line('Definitions:  ');
dbms_aw.printlog(defs);
 
END;
/

Dimension list:  <channel units_cube_composite<product customer time>>
Sparse dimension:  DEFINE units_cube_composite COMPOSITE <product customer time>
Definitions:
DEFINE units_cube.cp COMPOSITE <product customer time>
DEFINE units_cube NUMBER VARIABLE <channel units_cube.cp<product customer time>>
PL/SQL procedure successfully completed.

SELECT fact, dimension, dimcolumn, membercount nmem, leafcount nleaf, 
     advice, density
     FROM aw_sparsity_advice
     WHERE cubename='units_cube';
 

FACT                 DIMENSION    DIMCOLUMN     NMEM  NLEAF ADVICE        DENSITY
-------------------- ------------ ------------ ----- ------ ------------ --------
units_history_fact   channel      channel_id       3      3 DENSE          .46182
units_history_fact   product      item_id         48     36 SPARSE         .94827
units_history_fact   customer     ship_to_id      61     61 SPARSE         .97031
units_history_fact   time         month_id        96     79 SPARSE         .97664

Aggregation Facilities within the Workspace

Instructions for storing aggregate data are specified in an analytic workspace object called an aggmap. The OLAP DML AGGREGATE command uses the aggmap to preaggregate the data. Any data that is not preaggregated is aggregated dynamically by the AGGREGATE function when the data is queried.

Choosing a balance between static and dynamic aggregation depends on many factors including disk space, available memory, and the nature and frequency of the queries that run against the data. After weighing these factors, you may arrive at a percentage of the data to preaggregate.

Once you have determined the percentage of the data to preaggregate, you can use the Aggregate Advisor. These procedures analyze the distribution of dimension members within hierarchies and identify an optimal set of dimension members to preaggregate.

Example: Using the ADVISE_REL Procedure

Based on a precompute percentage that you specify, the ADVISE_REL procedure analyzes a family relation, which represents a dimension with all its hierarchical relationships, and returns a list of dimension members.

ADVISE_CUBE applies similar heuristics to each dimension in an aggmap for a cube.

Example B-2 uses the following sample Customer dimension to illustrate the ADVISE_REL procedure.

Sample Dimension: Customer in the Global Analytic Workspace

The Customer dimension in GLOBAL_AW.GLOBAL has two hierarchies: SHIPMENTS_ROLLUP with four levels, and MARKET_ROLLUP with three levels. The dimension has 106 members. This number includes all members at each level and all level names.

The members of the Customer dimension are integer keys whose text values are defined in long and short descriptions.

The following OLAP DML commands show information about the representation of the Customer dimension, which is in database standard form.

SET serveroutput ON
---- Number of members of Customer dimension
EXECUTE dbms_aw.execute('SHOW STATLEN(customer)')
106

---- Hierarchies in Customer dimension;
EXECUTE dbms_aw.execute('REPORT W 40 customer_hierlist');
CUSTOMER_HIERLIST
----------------------------------------
MARKET_ROLLUP
SHIPMENTS_ROLLUP

---- Levels in Customer dimension
EXECUTE dbms_aw.execute('REPORT W 40 customer_levellist');

CUSTOMER_LEVELLIST
----------------------------------------
TOTAL_CUSTOMER
REGION
WAREHOUSE
TOTAL_MARKET
MARKET_SEGMENT
ACCOUNT
SHIP_TO
---- Levels in each hierarchy from leaf to highest
EXECUTE dbms_aw.execute('REPORT W 20 customer_hier_levels');
 
CUSTOMER_HIERL
IST            CUSTOMER_HIER_LEVELS
-------------- --------------------
SHIPMENTS      SHIP_TO
               WAREHOUSE
               REGION
               TOTAL_CUSTOMER
MARKET_SEGMENT SHIP_TO
               ACCOUNT
               MARKET_SEGMENT
               TOTAL_MARKET

---- Parent relation showing parent-child relationships in the Customer dimension
---- Only show the last 20 members
EXECUTE dbms_aw.execute('LIMIT customer TO LAST 20');
EXECUTE dbms_aw.execute('REPORT W 10 DOWN customer W 20 customer_parentrel');

           -----------CUSTOMER_PARENTREL------------
           ------------CUSTOMER_HIERLIST------------
CUSTOMER      MARKET_ROLLUP       SHIPMENTS_ROLLUP
---------- -------------------- --------------------
103        44                   21
104        45                   21
105        45                   21
106        45                   21
7          NA                   NA
1          NA                   NA
8          NA                   1
9          NA                   1
10         NA                   1
11         NA                   8
12         NA                   10
13         NA                   9
14         NA                   9
15         NA                   8
16         NA                   9
17         NA                   8
18         NA                   8
19         NA                   9
20         NA                   9
21         NA                   10

---- Show text descriptions for the same twenty dimension members 
EXECUTE dbms_aw.execute('REPORT W 15 DOWN customer W 35 ACROSS customer_hierlist: <customer_short_description>');
ALL_LANGUAGES: AMERICAN_AMERICA
                ---------------------------CUSTOMER_HIERLIST---------------------------
                -----------MARKET_ROLLUP----------- ---------SHIPMENTS_ROLLUP----------
CUSTOMER            CUSTOMER_SHORT_DESCRIPTION          CUSTOMER_SHORT_DESCRIPTION
--------------- ----------------------------------- -----------------------------------
103             US Marine Svcs Washington           US Marine Svcs Washington
104             Warren Systems New York             Warren Systems New York
105             Warren Systems Philladelphia        Warren Systems Philladelphia
106             Warren Systems Boston               Warren Systems Boston
7               Total Market                        NA
1               NA                                  All Customers
8               NA                                  Asia Pacific
9               NA                                  Europe
10              NA                                  North America
11              NA                                  Australia
12              NA                                  Canada
13              NA                                  France
14              NA                                  Germany
15              NA                                  Hong Kong
16              NA                                  Italy
17              NA                                  Japan
18              NA                                  Singapore
19              NA                                  Spain
20              NA                                  United Kingdom
21              NA                                  United States

SET serveroutput ON
EXECUTE dbms_aw.execute('AW ATTACH global_aw.global');
EXECUTE dbms_aw.execute('DEFINE customer_preagg VALUESET customer');
EXECUTE dbms_aw.advise_rel('customer_parentrel', 'customer_preagg', 25);
EXECUTE dbms_aw.execute('SHOW VALUES(customer_preagg)');
31
2
4
5
6
7
1
8
9
20
21

The returned Customer members with their text descriptions, related levels, and related hierarchies, are shown as follows.