Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
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_T
DBMS_AW$_DIMENSION_SOURCE_T
DBMS_AW$_DIMENSION_SOURCES_T
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
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 |