The DBMS_AW PL/SQL package provides procedures and functions for interacting with analytic workspaces.
This appendix contains the following topics:
One reference topic for each DBMS_AW subprogram
To interact with Oracle OLAP, you must attach an analytic workspace to your session. When you have DBMS_AW PL/SQL package installed you can perform this task from within SQL*Plus. For example, you can use the following command to attach an analytic workspace with read-only access.
EXECUTE dbms_aw.aw_attach ('awname');
Each analytic workspace is associated with a list of analytic workspaces. The read-only workspace EXPRESS.AW, which contains the OLAP engine code, is always attached last in the list. When you create a workspace, it is attached first in the list by default.
You can reposition an analytic workspace within the list by using keywords such as FIRST and LAST. For example, the following commands show how to move an analytic workspace called MYAW.TEST2 from the second position to the first position on the list.
EXECUTE dbms_aw.execute ('AW LIST'); TEST1 R/O UNCHANGED GLOBAL.TEST1 TEST2 R/O UNCHANGED GLOBAL.TEST2 EXPRESS R/O UNCHANGED SYS.EXPRESS EXECUTE dbms_aw.aw_attach ('test2', FALSE, FALSE, 'FIRST'); EXECUTE dbms_aw.execute ('AW LIST'); TEST2 R/O UNCHANGED GLOBAL.TEST2 TEST1 R/O UNCHANGED GLOBAL.TEST1 EXPRESS R/O UNCHANGED SYS.EXPRESS
From within SQL*Plus, you can rename workspaces and make copies of workspaces. If you have an analytic workspace attached with read/write access, you can update the workspace and save your changes in the permanent database table where the workspace is stored. You must do a SQL COMMIT to save the workspace changes within the database.
The following commands make a copy of the objects and data in workspace test2 in a new workspace called test3, update test3, and commit the changes to the database.
EXECUTE dbms_aw.aw_copy('test2', 'test3'); EXECUTE dbms_aw.aw_update('test3'); COMMIT;
With the DBMS_AW package you can perform the full range of OLAP processing within analytic workspaces. You can import data from legacy workspaces, relational tables, or flat files. You can define OLAP objects and perform complex calculations.
Note:
If you use theDBMS_AW package to create analytic workspaces from scratch, you may not be able to use OLAP utilities, such as Analytic Workspace Manager and the DBMS_AW Aggregate Advisor, which require analytic workspaces of a particular structure.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.
Note:
Do not confuse theDBMS_AW functions EVAL_NUMBER and EVAL_TEXT with the SQL function OLAP_EXPRESSION. See "OLAP_EXPRESSION" for more information.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 can be stored in several different forms in an analytic workspace, depending on whether it is dense, sparse, or very sparse. The Sparsity Advisor is a group of subprograms in DBMS_AW that you can use to analyze the relational source data and get recommendations for storing it in an analytic workspace.
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.
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:
DBMS_AW$_COLUMNLIST_TDBMS_AW$_DIMENSION_SOURCE_TDBMS_AW$_DIMENSION_SOURCES_TThe 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
Take these steps to use the Sparsity Advisor:
Call SPARSITY_ADVICE_TABLE to create a table for storing the evaluation of the Sparsity Advisor.
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.
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.
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 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.
Example B-1 Sparsity Advisor Script for GLOBAL
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;
/
The script in Example B-1, "Sparsity Advisor Script for GLOBAL" generates the following information.
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.
This SQL SELECT statement shows some columns from the AW_SPARSITY_ADVICE table, which is the basis for the recommended OLAP DML object definitions.
SELECT fact, dimension, dimcolumn, membercount nmem, leafcount nleaf,
advice, density
FROM aw_sparsity_advice
WHERE cubename='units_cube';
This query returns the following result set:
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
The management of aggregate data within analytic workspaces can have significant performance implications. To determine an optimal set of dimension member combinations to preaggregate, you can use the ADVISE_REL and ADVISE_CUBE procedures in the DBMS_AW package. These procedures are known together as the Aggregate Advisor.
Based on a percentage that you specify, ADVISE_REL suggests a set of dimension members to preaggregate. The ADVISE_CUBE procedure suggests a set of members for each dimension of a cube.
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.
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.
See Also:
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
Example B-2 ADVISE_REL: Suggested Preaggregation of the Customer Dimension
This example uses the GLOBAL Customer dimension described in Sample Dimension: Customer in the Global Analytic Workspace.
The following PL/SQL statements assume that you want to preaggregate 25% of the Customer dimension. ADVISE_REL returns the suggested set of members in a valueset.
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.
| Customer Member | Description | Hierarchy | Level |
|---|---|---|---|
31 |
Kosh Enterprises |
MARKET_ROLLUP |
ACCOUNT |
2 |
Consulting |
MARKET_ROLLUP |
MARKET_SEGMENT |
4 |
Government |
MARKET_ROLLUP |
MARKET_SEGMENT |
5 |
Manufacturing |
MARKET_ROLLUP |
MARKET_SEGMENT |
6 |
Reseller |
MARKET_ROLLUP |
MARKET_SEGMENT |
7 |
TOTAL_MARKET |
MARKET_ROLLUP |
TOTAL_MARKET |
1 |
TOTAL_CUSTOMER |
SHIPMENTS_ROLLUP |
TOTAL_CUSTOMER |
8 |
Asia Pacific |
SHIPMENTS_ROLLUP |
REGION |
9 |
Europe |
SHIPMENTS_ROLLUP |
REGION |
20 |
United Kingdom |
SHIPMENTS_ROLLUP |
WAREHOUSE |
21 |
United States |
SHIPMENTS_ROLLUP |
WAREHOUSE |