Skip Headers

Oracle® OLAP Reference
10g Release 1 (10.1)

Part Number B10334-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

21 DBMS_AW

The DBMS_AW package provides procedures and functions for performing operations within analytic workspaces. With DBMS_AW, you can:


See Also:


This chapter includes the following topics:

21.1 Embedding OLAP DML in SQL Statements

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 the DBMS_AW package to create analytic workspaces from scratch, you may not be able to use OLAP utilities that require standard form. You will have to develop your own relational views of the workspaces using the OLAP_TABLE function. To make the workspaces accessible to the OLAP API, you will have to create your own metadata for the views using the CWM2 packages.

21.1.1 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 will depend 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.

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

21.2 Embedding Custom Measures in SELECT Statements

The OLAP_EXPRESSION function in the DBMS_AW package dynamically executes a single-row numeric function in an analytic workspace and returns the results. You can embed OLAP_EXPRESSION functions in the WHERE and ORDER BY clauses of SELECT statements.

You can use variants of OLAP_EXPRESSION to calculate text, date, or boolean expressions.

The following script was used to create a view named MEASURE_VIEW, which is used in Example 21-1 and Example 21-2 to illustrate the use of OLAP_EXPRESSION.


Sample View: MEASURE_VIEW
CREATE TYPE measure_row AS OBJECT (
   time                       VARCHAR2(12),
   geography                  VARCHAR2(30),
   product                    VARCHAR2(30),
   channel                    VARCHAR2(30),
   sales                      NUMBER(16),
   cost                       NUMBER(16),
   promotions                 NUMBER(16),
   quota                      NUMBER(16),
   units                      NUMBER(16),
   r2c                        RAW(32));
/   
   
CREATE TYPE measure_table AS TABLE OF measure_row;
/

CREATE OR REPLACE VIEW measure_view AS
SELECT sales, cost, promotions, quota, units,
      time, geography, product, channel, r2c 
   FROM TABLE(CAST(OLAP_TABLE(
     'xademo DURATION SESSION', 
     'measure_table', 
     '',
     'MEASURE sales FROM analytic_cube_f.sales
      MEASURE cost FROM analytic_cube_f.costs
      MEASURE promotions FROM analytic_cube_f.promo
      MEASURE quota FROM analytic_cube_f.quota
      MEASURE units FROM analytic_cube_f.units   
      DIMENSION time FROM time WITH
        HIERARCHY time_member_parentrel
           INHIERARCHY time_member_inhier
      DIMENSION geography FROM geography WITH 
         HIERARCHY geography_member_parentrel
            INHIERARCHY geography_member_inhier
      DIMENSION product FROM product WITH
         HIERARCHY product_member_parentrel
            INHIERARCHY product_member_inhier
      DIMENSION channel FROM channel WITH
         HIERARCHY channel_member_parentrel
            INHIERARCHY channel_member_inhier
   ROW2CELL r2c') 
      AS measure_table))
   WHERE sales IS NOT NULL;
/
COMMIT
/      
GRANT SELECT ON measure_view TO PUBLIC;   

Example 21-1 OLAP_EXPRESSION: Time Series Function with a WHERE Clause

This example uses the view described in "Sample View: MEASURE_VIEW".

The following SELECT statement calculates an expression with an alias of PERIODAGO, and limits the result set to calculated values greater than 200,000. The calculation uses the LAG function to return the value of the previous time period.

SELECT time, cost, OLAP_EXPRESSION(r2c, 
   'LAG(analytic_cube_f.costs, 1, time, 
      LEVELREL time_member_levelrel)') periodago
FROM measure_view
WHERE geography = 'L1.WORLD' AND
CHANNEL = 'STANDARD_2.TOTALCHANNEL' AND
PRODUCT = 'L1.TOTALPROD' and
OLAP_EXPRESSION(r2c, 'LAG(analytic_cube_f.costs, 1, time, 
   LEVELREL time_member_levelrel)') > 200000;

This SELECT statement produces these results.

TIME               COST  PERIODAGO
------------ ---------- ----------
L1.1997         1078031 2490243.07
L2.Q1.97         615399 560379.445
L2.Q2.96         649004 615398.858
L2.Q2.97         462632 649004.473
L2.Q3.96         582693 462632.064
L2.Q4.96         698166 582693.091
L3.AUG96         194498 209476.344
L3.FEB96         186762 252738.981
L3.JAN96         185755 205214.946
               .
               .
               .

Example 21-2 OLAP_EXPRESSION: Numeric Calculation with an ORDER BY CLause

This example uses the view described in "Sample View: MEASURE_VIEW".

This example subtracts costs from sales to calculate profit, and gives this expression an alias of PROFIT. The rows are ordered by geographic areas from most to least profitable.

SELECT geography, sales, cost, OLAP_EXPRESSION(r2c,
   'analytic_cube_f.sales - analytic_cube_f.costs') profit
FROM measure_view
WHERE 
channel = 'STANDARD_2.TOTALCHANNEL' AND
product = 'L1.TOTALPROD' AND
time = 'L3.APR97'
ORDER BY OLAP_EXPRESSION(r2c, 
   'analytic_cube_f.sales - analytic_cube_f.costs') DESC;

This SELECT statement produces these results.

GEOGRAPHY                           SALES       COST     PROFIT
------------------------------ ---------- ---------- ----------
L1.WORLD                          9010260     209476 8800783.17
L2.EUROPE                         3884776      95204 3789571.85
L2.AMERICAS                       2734436      55322 2679114.66
L2.ASIA                           1625379      37259 1588120.61
L3.USA                            1603043      27547 1575496.86
L2.AUSTRALIA                       765668      21692 743976.058
L3.UK                              733090      19144 713945.952
L3.CANADA                          731734      19666 712067.455
L4.NEWYORK                         684008       8020 675987.377
L3.GERMANY                         659428      12440 646988.197
L3.FRANCE                          596767      19307 577460.113
               .
               .
               .

21.3 Using the Aggregate Advisor

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. The Aggregate Advisor procedures require database standard form.


See Also:

Oracle OLAP Application Developer's Guide for information on standard form analytic workspaces.

21.3.1 Aggregation Facilities within the Workspace

Instructions for storing aggregate data are specified in a 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 will 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.

21.3.2 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 21-3 uses a 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 illustrate some aspects of the standard form representation of the Customer dimension.

" ---- Number of members of Customer dimension
>show statlen(customer)
106

" ---- Hierarchies in Customer dimension;
>rpr w 40 customer_hierlist
CUSTOMER_HIERLIST
----------------------------------------
MARKET_ROLLUP
SHIPMENTS_ROLLUP

" ---- Levels in Customer dimension
>rpr w 40 customer_levellist
CUSTOMER_LEVELLIST
----------------------------------------
ALL_CUSTOMERS
REGION
WAREHOUSE
TOTAL_MARKET
MARKET_SEGMENT
ACCOUNT
SHIP_TO
" ---- In the MARKET_ROLLUP hierarchy, ACCOUNT is the leaf level.
" ---- In the SHIPMENTS_HIER hierarchy, SHIP_TO is the leaf level.
" ---- MARKET_HIER                      SHIPMENTS_HIER
" ------------------------------------------------------------
" ---- TOTAL_MARKET                     ALL_CUSTOMERS
" ---- MARKET_SEGMENT                   REGIONS
" ---- ACCOUNT                          WAREHOUSE
" ----                                 SHIP_TO
" ---- 
" ---- Parent relation showing parent-child relationships in the Customer dimension
>limit customer to last 20            "Only show the last 20 members
>rpr 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 
>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 21-3 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.

SQL>SET SERVEROUTPUT ON
SQL>EXECECUTE dbms_aw.execute('aw attach global_aw.global');
SQL>EXECECUTE dbms_aw.execute('define customer_preagg valueset customer');
SQL>EXECECUTE dbms_aw.advise_rel('customer_parentrel', 'customer_preagg', 25);
SQL>EXECECUTE dbms_aw.execute('show values(customer_preagg)');
31
2
4
5
6
7
1
8
9
20
21

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

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 ALL_CUSTOMERS SHIPMENTS_ROLLUP ALL_CUSTOMERS
8 Asia Pacific SHIPMENTS_ROLLUP REGION
9 Europe SHIPMENTS_ROLLUP REGION
20 United Kingdom SHIPMENTS_ROLLUP WAREHOUSE
21 United States SHIPMENTS_ROLLUP WAREHOUSE


Summary of DBMS_AW Subprograms

The following table describes the subprograms provided in DBMS_AW.

Table 21-1 DBMS_AW Subprograms

Subprogram Description
ADVISE_CUBE Procedure
Suggests how to preaggregate a standard form cube, based on a specified percentage of the cube's data.
ADVISE_REL Procedure
Suggests how to preaggregate a standard form dimension, based on a specified percentage of the dimension's members.
AW_ATTACH Procedure
Attaches an analytic workspace to a session.
AW_COPY Procedure
Creates a new analytic workspace and populates it with the object definitions and data from another analytic workspace.
AW_CREATE Procedure
Creates a new, empty analytic workspace.
AW_DELETE
Deletes an analytic workspace
AW_DETACH Procedure
Detaches an analytic workspace from a session.
AW_RENAME Procedure
Changes the name of an analytic workspace.
AW_UPDATE Procedure
Saves changes made to an analytic workspace.
"EXECUTE Procedure"
Executes one or more OLAP DML commands. Input and output is limited to 4K. Typically used in an interactive session using an analytic workspace.
"GETLOG Function"
Returns the session log from the last execution of the INTERP or INTERPCLOB functions.
"INTERP Function"
Executes one or more OLAP DML commands. Input is limited to 4K and output to 4G. Typically used in applications when the 4K limit on output for the EXECUTE procedure is too restrictive.
"INTERPCLOB Function"
Executes one or more OLAP DML commands. Input and output are limited to 4G. Typically used in applications when the 4K input limit of the INTERP function is too restrictive.
"INTERP_SILENT Procedure"
Executes one or more OLAP DML commands and suppresses the output. Input is limited to 4K and output to 4G.
"OLAP_EXPRESSION Function"
Returns the result set of a single-row numeric function calculated in an analytic workspace.
"OLAP_EXPRESSION_BOOL Function"
Returns the result set of a single-row boolean function calculated in an analytic workspace.
"OLAP_EXPRESSION_DATE Function"
Returns the result set of a single-row date function calculated in an analytic workspace.
"OLAP_EXPRESSION_TEXT Function"
Returns the result set of a single-row text function calculated in an analytic workspace.
"PRINTLOG Procedure"
Prints a session log returned by the INTERP, INTERCLOB, or GETLOG functions.


ADVISE_CUBE Procedure

The ADVISE_CUBE procedure helps you determine how to preaggregate a standard form cube in an analytic workspace. When you specify a percentage of the cube's data to preaggregate, ADVISE_CUBE recommends a set of members to preaggregate from each of the cube's dimensions.

The ADVISE_CUBE procedure takes an aggmap and a precompute percentage as input. The aggmap must have a precompute clause in each of its RELATION statements. The precompute clause must consist of a valueset. Based on the precompute percentage that you specify, ADVISE_CUBE returns a set of dimension members in each valueset.

Syntax

ADVISE_CUBE ( 
          aggmap_name             IN   VARCHAR2
          precompute_percentage   IN   INTEGER DEFAULT 20);

Parameters

Table 21-2 ADVISE_CUBE Procedure Parameters

Parameter Description
aggmap_name The name of an aggmap associated with the cube.

Each RELATION statement in the aggmap must have a precompute clause containing a valueset. ADVISE_CUBE returns a list of dimension members in each valueset. If the valueset is not empty, ADVISE_CUBE deletes its contents before adding new values.

precompute_percentage A percentage of the cube's data to preaggregate. The default is 20%.

Example

This example illustrates the ADVISE_CUBE procedure with a cube called UNITS dimensioned by PRODUCT and TIME. ADVISE_CUBE returns the dimension combinations to include if you want to preaggregate 40% of the cube's data.

SET SERVEROUTPUT ON
--- View valuesets
SQL>EXECUTE dbms_aw.execute('describe prodvals');
     DEFINE PRODVALS VALUESET PRODUCT
SQL>EXECUTE dbms_aw.execute('describe timevals');
     DEFINE TIMEVALS VALUESET TIME
--- View aggmap
SQL>EXECUTE dbms_aw.execute ('describe units_agg');
     DEFINE UNITS_AGG AGGMAP
          RELATION product_parentrel PRECOMPUTE (prodvals)
          RELATION time_parentrel PRECOMPUTE (timevals)
SQL>EXECUTE dbms_aw.advise_cube ('units_agg', 40);
----
---- The results are returned in the prodvals and timevals valuesets

See Also

"Using the Aggregate Advisor"


ADVISE_REL Procedure

The ADVISE_REL procedure helps you determine how to preaggregate a standard form dimension in an analytic workspace. When you specify a percentage of the dimension to preaggregate, ADVISE_REL recommends a set of dimension members.

The ADVISE_REL procedure takes a family relation, a valueset, and a precompute percentage as input. The family relation is a standard form object that specifies the hierarchical relationships between the members of a dimension. The valueset must be defined from the dimension to be analyzed. Based on the precompute percentage that you specify, ADVISE_REL returns a set of dimension members in the valueset.

Syntax

ADVISE_REL ( 
          family_relation_name    IN   VARCHAR2,
          valueset_name           IN   VARCHAR2,
          precompute_percentage   IN   INTEGER DEFAULT 20);

Parameters

Table 21-3 ADVISE_REL Procedure Parameters

Parameter Description
family_relation_name The name of a family relation, which specifies a dimension and the hierarchical relationships between the dimension members.
valueset_name The name of a valueset to contain the results of the procedure. The valueset must be defined from the dimension in the family relation. If the valueset is not empty, ADVISE_REL deletes its contents before adding new values.
precompute_percentage A percentage of the dimension to preaggregate. The default is 20%.

See Also

"Using the Aggregate Advisor"


AW_ATTACH Procedure

The AW_ATTACH procedure attaches an existing analytic workspace to your SQL session so that you can access its contents. The analytic workspace remains attached until you explicitly detach it, or you end your session.

AW_ATTACH can also be used to create a new analytic workspace, but the AW_CREATE procedure is provided specifically for that purpose.

Syntax

DBMS_AW.AW_ATTACH ( 
    awname        IN VARCHAR2,
    forwrite      IN BOOLEAN DEFAULT FALSE,
    createaw      IN BOOLEAN DEFAULT FALSE,
    attargs       IN VARCHAR2 DEFAULT NULL,
    tablespace    IN VARCHAR2 DEFAULT NULL);

Parameters

Table 21-4 AW_ATTACH Procedure Parameters

Parameter Description
awname The name of an existing analytic workspace, unless createaw is specified as TRUE. See the description of createaw.
forwrite TRUE attaches the analytic workspace in read/write mode, giving you exclusive access and full administrative rights to the analytic workspace. FALSE attaches the analytic workspace in read-only mode.
createaw TRUE creates an analytic workspace named awname. If awname already exists, then an error is generated. FALSE attaches an existing analytic workspace named awname.
attargs Keywords for attaching an analytic workspace, such as FIRST or LAST, as described in the Oracle OLAP DML Reference under the AW command.

Example

The following SQL call attaches an analytic workspace named GLOBAL in read/write mode.

EXECUTE DBMS_AW.AW_ATTACH('global', TRUE);

The next SQL call attaches GLOBAL_PROGRAMS in read-only mode as the last user-owned analytic workspace. If GLOBAL_PROGRAMS is already attached, this call just changes its position in the list of analytic workspaces.

EXECUTE DBMS_AW.AW_ATTACH('global_programs', false, false, 'last');

AW_COPY Procedure

The AW_COPY procedure creates a new analytic workspace and copies into it both the object definitions and the data from another analytic workspace.

Syntax

DBMS_AW.AW_COPY ( 
    oldname         IN VARCHAR2,
    newname         IN VARCHAR2,
    tablespace      IN VARCHAR2 DEFAULT NULL,
    partnum         IN NUMBER DEFAULT 8);

Parameters

Table 21-5 AW_COPY Procedure Parameters

Parameter Description
oldname The name of an existing analytic workspace.
newname A name for the new analytic workspace.
tablespace The name of a tablespace in which newname will be stored. If this parameter is omitted, then the analytic workspace is created in the user's default tablespace.
partnum The number of partitions that will be created for the AW$newname table.

Example

The following command creates a new analytic workspace named DEMO and copies the contents of GLOBAL into it. The workspace is stored in a table named AW$DEMO, which has three partitions and is stored in the user's default tablespace.

EXECUTE DBMS_AW.AW_COPY('global', 'demo', null, 3);

AW_CREATE Procedure

The AW_CREATE procedure creates a new, empty analytic workspace.

Syntax

DBMS_AW.AW_CREATE ( 
    awname        IN VARCHAR2 ,
    tablespace    IN VARCHAR2 DEFAULT NULL ,
    partnum       IN NUMBER DEFAULT 8 );

Parameters

Table 21-6 AW_CREATE Procedure Parameters

Parameter Description
awname The name of a new analytic workspace. The name must comply with the naming requirements for a table in an Oracle database. This procedure creates a table named AW$awname, in which the analytic workspace is stored.
tablespace The tablespace in which the analytic workspace will be created. If you omit this parameter, the analytic workspace is created in your default tablespace.
partnum The number of partitions that will be created for the AW$awname table.

Example

The following command creates a new, empty analytic workspace named GLOBAL. The new analytic workspace is stored in a table named AW$GLOBAL with eight partitions in the user's default tablespace.

EXECUTE DBMS_AW.AW_CREATE('global');

The next command creates an analytic workspace named DEMO in the GLOBAL_AW schema. AW$DEMO will have two partitions and will be stored in the GLOBAL tablespace.

EXECUTE DBMS_AW.AW_CREATE('global_aw.demo', 'global', 2);

AW_DELETE

The AW_DELETE procedure deletes an existing analytic workspace.

Syntax

DBMS_AW.AW_DELETE ( 
    awname        IN VARCHAR2 );

Parameters

Table 21-7 AW_DELETE Procedure Parameters

Parameter Description
awname The name of an existing analytic workspace that you want to delete along with all of its contents. You must be the owner of awname or have DBA rights to delete it, and it cannot currently be attached to your session. The AW$awname file is deleted from the database.

Example

The following SQL call deletes the GLOBAL analytic workspace in the user's default schema.

EXECUTE DBMS_AW.AW_DELETE('global');

AW_DETACH Procedure

The AW_DETACH procedure detaches an analytic workspace from your session so that its contents are no longer accessible. All changes that you have made since the last update are discarded. Refer to "AW_UPDATE Procedure" for information about saving changes to an analytic workspace.

Syntax

DBMS_AW.AW_DETACH ( 
    awname        IN VARCHAR2);

Parameters

Table 21-8 AW_DETACH Procedure Parameters

Parameter Description
awname The name of an attached analytic workspace that you want to detach from your session.

Example

The following command detaches the GLOBAL analytic workspace.

EXECUTE DBMS_AW.AW_DETACH('global');

AW_RENAME Procedure

The AW_RENAME procedure changes the name of an analytic workspace.

Syntax

DBMS_AW.AW_RENAME ( 
    oldname       IN VARCHAR2 DEFAULT NULL,
    newname       IN VARCHAR2 );

Parameters

Table 21-9 AW_RENAME Procedure Parameters

Parameter Description
oldname The current name of the analytic workspace. The analytic workspace cannot be attached to any session.
newname The new name of the analytic workspace.

Example

The following command changes the name of the GLOBAL analytic workspace to DEMO.

EXECUTE DBMS_AW.AW_RENAME('global', 'demo');

AW_UPDATE Procedure

The AW_UPDATE procedure saves the changes made to an analytic workspace in its permanent database table. For the updated version of this table to be saved in the database, you must issue a SQL COMMIT statement before ending your session.

Syntax

DBMS_AW.AW_UPDATE ( 
    awname     IN VARCHAR2 DEFAULT NULL);

Parameters

Table 21-10 AW_UPDATE Procedure Parameters

Parameter Description
awname Saves changes to awname by copying them to a table named AW$awname. If this parameter is omitted, then changes are saved for all analytic workspaces attached in read/write mode.

Example

The following command saves changes to the GLOBAL analytic workspace to a table named AW$GLOBAL.

EXECUTE DBMS_AW.AW_UPDATE('global');

EXECUTE Procedure

The EXECUTE procedure executes one or more OLAP DML commands and directs the output to a printer buffer. It is typically used to manipulate analytic workspace data within an interactive SQL session.

When you are using SQL*Plus, you can direct the printer buffer to the screen by issuing the following command:

SET SERVEROUT ON

If you are using a different program, refer to its documentation for the equivalent setting.

Input and output is limited to 4K. For larger values, refer to the INTERP and INTERPCLOB functions in this package.

This procedure does not print the output of the DML commands when you have redirected the output by using the OLAP DML OUTFILE command.

Syntax

EXECUTE ( 
          olap_commands     IN    VARCHAR2
          text              OUT   VARCHAR2);

Parameters

Table 21-11 EXECUTE Procedure Parameters

Parameter Description
olap-commands One or more OLAP DML commands separated by semicolons. See "Guidelines for Using Quotation Marks in OLAP DML Commands".
text Output from the OLAP engine in response to the OLAP commands.

Example

The following sample SQL*Plus session attaches an analytic workspace named XADEMO, creates a formula named COST_PP in XADEMO, and displays the new formula definition.

SQL> SET SERVEROUT ON

SQL> EXECUTE DBMS_AW.EXECUTE('AW ATTACH xademo RW; DEFINE cost_pp FORMULA LAG(analytic_cube_f.costs, 1, time, LEVELREL time_levelrel)');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_AW.EXECUTE('DESCRIBE cost_pp');

DEFINE COST_PP FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME>
EQ lag(analytic_cube_f.costs, 1, time, levelrel time.levelrel)

PL/SQL procedure successfully completed.

GETLOG Function

This function returns the session log from the last execution of the INTERP or INTERPCLOB functions in this package.

To print the session log returned by this function, use the DBMS_AW.PRINTLOG procedure.

Syntax

GETLOG()
          RETURN CLOB;

Returns

The session log from the latest call to INTERP or INTERPCLOB.

Example

The following example shows the session log returned by a call to INTERP, then shows the identical session log returned by GETLOG.

SQL> SET SERVEROUT ON SIZE 1000000
SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERP('AW ATTACH xademo; LISTNAMES AGGMAP'));
2 AGGMAPs
------------------------------------------
ANALYTIC_CUBE.AGGMAP.1
SALES_MULTIKEY_CUBE.AGGMAP.1

PL/SQL procedure successfully completed.


SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.GETLOG());
2 AGGMAPs
------------------------------------------
ANALYTIC_CUBE.AGGMAP.1
SALES_MULTIKEY_CUBE.AGGMAP.1

PL/SQL procedure successfully completed.

INTERP Function

The INTERP function executes one or more OLAP DML commands and returns the session log in which the commands are executed. It is typically used in applications when the 4K limit on output for the EXECUTE procedure may be too restrictive.

Input to the INTERP function is limited to 4K. For larger input values, refer to the INTERPCLOB function of this package.

This function does not return the output of the DML commands when you have redirected the output by using the OLAP DML OUTFILE command.

You can use the INTERP function as an argument to the PRINTLOG procedure in this package to view the session log. See the example.

Syntax

INTERP ( 
          olap-commands     IN   VARCHAR2)
     RETURN CLOB;

Parameters

Table 21-12 INTERP Function Parameters

Parameter Description
olap-commands One or more OLAP DML commands separated by semi-colons. See "Guidelines for Using Quotation Marks in OLAP DML Commands".

Returns

The log file for the Oracle OLAP session in which the OLAP DML commands were executed.

Example

The following sample SQL*Plus session attaches an analytic workspace named XADEMO and lists the members of the PRODUCT dimension.

SQL> SET SERVEROUT ON SIZE 1000000
SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERP('AW ATTACH cloned; REPORT product'));
PRODUCT
--------------
L1.TOTALPROD
L2.ACCDIV
L2.AUDIODIV
L2.VIDEODIV
L3.AUDIOCOMP
L3.AUDIOTAPE
     .
     .
     .
PL/SQL procedure successfully completed.

INTERPCLOB Function

The INTERPCLOB function executes one or more OLAP DML commands and returns the session log in which the commands are executed. It is typically used in applications when the 4K limit on input for the INTERP function may be too restrictive.

This function does not return the output of the OLAP DML commands when you have redirected the output by using the OLAP DML OUTFILE command.

You can use the INTERPCLOB function as an argument to the PRINTLOG procedure in this package to view the session log. See the example.

Syntax

INTERPCLOB ( 
          olap-commands     IN   CLOB)
     RETURN CLOB;

Parameters

Table 21-13 INTERPCLOB Function Parameters

Parameter Description
olap-commands One or more OLAP DML commands separated by semi-colons. See "Guidelines for Using Quotation Marks in OLAP DML Commands".

Returns

The log for Oracle OLAP session in which the OLAP DML commands were executed.

Example

The following sample SQL*Plus session creates an analytic workspace named ELECTRONICS, imports its contents from an EIF file stored in the dbs directory alias, and displays the contents of the analytic workspace.

SQL> SET SERVEROUT ON SIZE 1000000
SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERPCLOB('AW CREATE electronics; IMPORT ALL FROM EIF FILE ''dbs/electronics.eif'' DATA DFNS; DESCRIBE'));

DEFINE GEOGRAPHY DIMENSION TEXT WIDTH 12
LD Geography Dimension Values
DEFINE PRODUCT DIMENSION TEXT WIDTH 12
LD Product Dimension Values
DEFINE TIME DIMENSION TEXT WIDTH 12
LD Time Dimension Values
DEFINE CHANNEL DIMENSION TEXT WIDTH 12
LD Channel Dimension Values
          .
          .
          .
PL/SQL procedure successfully completed.

INTERP_SILENT Procedure

The INTERP_SILENT procedure executes one or more OLAP DML commands and suppresses all output from them. It does not suppress error messages from the OLAP command interpreter.

Input to the INTERP_SILENT function is limited to 4K. If you want to display the output of the OLAP DML commands, use the EXECUTE procedure, or the INTERP or INTERPCLOB functions.

Syntax

INTERP_SILENT ( 
          olap-commands     IN   VARCHAR2);

Parameters

Table 21-14 INTERP_SILENT Function Parameters

Parameter Description
olap-commands One or more OLAP DML commands separated by semi-colons. See "Guidelines for Using Quotation Marks in OLAP DML Commands".

Example

The following commands show the difference in message handling between EXECUTE and INTERP_SILENT. Both commands attach the XADEMO analytic workspace in read-only mode. However, EXECUTE displays a warning message, while INTERP_SILENT does not.

SQL> EXECUTE DBMS_AW.EXECUTE('AW ATTACH xademo');
IMPORTANT: Analytic workspace XADEMO is read-only. Therefore, you will
not be able to use the UPDATE command to save changes to it.

PL/SQL procedure successfully completed.


SQL> EXECUTE DBMS_AW.INTERP_SILENT('AW ATTACH xademo');

PL/SQL procedure successfully completed.

OLAP_EXPRESSION Function

The OLAP_EXPRESSION function enables you to execute single-row numeric functions in an analytic workspace and thus generate custom measures in SELECT statements. In addition to calculating an expression, OLAP_EXPRESSION can be used in the WHERE and ORDER BY clauses to modify the result set of a SELECT.

Syntax

OLAP_EXPRESSION(
          r2c          IN   RAW(32),
          expression   IN   VARCHAR2 )
      RETURN NUMBER;

Parameters

Table 21-15 OLAP_EXPRESSION Function Parameters

Parameter Description
r2c The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE.

ROW2CELL is a component of a limit map parameter of the OLAP_TABLE function. See "Limit Map Parameter".

expression A numeric calculation that will be performed in the analytic workspace.

Returns

An evaluation of expression for each row of the table object returned by the OLAP_TABLE function.

To return text, boolean, or date data, use the OLAP_EXPRESSION_TEXT, OLAP_EXPRESSION_BOOL, or OLAP_EXPRESSION_DATE functions in this package.

Note

You can use OLAP_EXPRESSION only with a table object returned by the OLAP_TABLE function. The returned table object must have a column populated by a ROW2CELL. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.

Example

See "Embedding Custom Measures in SELECT Statements".


OLAP_EXPRESSION_BOOL Function

The OLAP_EXPRESSION_BOOL function enables you to execute single-row boolean functions in an analytic workspace and thus generate custom measures in SELECT statements. In addition to calculating an expression, OLAP_EXPRESSION_BOOL can be used in the WHERE and ORDER BY clauses to modify the result set of a SELECT.

Syntax

OLAP_EXPRESSION_BOOL(
          r2c          IN   RAW(32),
          expression   IN   VARCHAR2 )
     RETURN NUMBER;

Parameters

Table 21-16 OLAP_EXPRESSION_BOOL Function Parameters

Parameter Description
r2c The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE.

ROW2CELL is a component of a limit map parameter of the OLAP_TABLE function. See "Limit Map Parameter".

expression A boolean calculation that will be performed in the analytic workspace.

Returns

An evaluation of expression for each row of the table object returned by the OLAP_TABLE function.

Return values are numbers 1 (true) or 0 (false).

To return text, numeric, or date data, use the OLAP_EXPRESSION_TEXT, OLAP_EXPRESSION, or OLAP_EXPRESSION_DATE functions in this package.

Note

You can use OLAP_EXPRESSION_BOOL only with a table object returned by the OLAP_TABLE function. The returned table object must have a column populated by a ROW2CELL. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.

Example

See "Embedding Custom Measures in SELECT Statements".


OLAP_EXPRESSION_DATE Function

The OLAP_EXPRESSION_DATE function enables you to execute single-row date functions in an analytic workspace and thus generate custom measures in SELECT statements. In addition to calculating an expression, OLAP_EXPRESSION_DATE can be used in the WHERE and ORDER BY clauses to modify the result set of a SELECT.

Syntax

OLAP_EXPRESSION_DATE(
          r2c          IN   RAW(32),
          expression   IN   VARCHAR2 )
RETURN DATE;

Parameters

Table 21-17 OLAP_EXPRESSION_DATE Function Parameters

Parameter Description
r2c The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE.

ROW2CELL is a component of a limit map parameter of the OLAP_TABLE function. See "Limit Map Parameter".

expression A date calculation that will be performed in the analytic workspace.

Returns

An evaluation of expression for each row of the table object returned by the OLAP_TABLE function.

To return text, boolean, or numeric data, use the OLAP_EXPRESSION_TEXT, OLAP_EXPRESSION_BOOL, or OLAP_EXPRESSION functions in this package.

Note

You can use OLAP_EXPRESSION_DATE only with a table object returned by the OLAP_TABLE function. The returned table object must have a column populated by a ROW2CELL. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.

Example

See "Embedding Custom Measures in SELECT Statements".


OLAP_EXPRESSION_TEXT Function

The OLAP_EXPRESSION_TEXT function enables you to execute single-row text functions in an analytic workspace and thus generate custom measures in SELECT statements. In addition to calculating an expression, OLAP_EXPRESSION_TEXT can be used in the WHERE and ORDER BY clauses to modify the result set of a SELECT.

Syntax

OLAP_EXPRESSION_TEXT(
          r2c          IN   RAW(32),
          expression   IN   VARCHAR2 )
RETURN VARCHAR2;

Parameters

Table 21-18 OLAP_EXPRESSION_TEXT Function Parameters

Parameter Description
r2c The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE.

ROW2CELL is a component of a limit map parameter of the OLAP_TABLE function. See "Limit Map Parameter".

expression A text calculation that will be performed in the analytic workspace.

Returns

An evaluation of expression for each row of the table object returned by the OLAP_TABLE function.

To return numeric, boolean, or date data, use the OLAP_EXPRESSION, OLAP_EXPRESSION_BOOL, or OLAP_EXPRESSION_DATE functions in this package.

Note

You can use OLAP_EXPRESSION_TEXT only with a table object returned by the OLAP_TABLE function. The returned table object must have a column populated by a ROW2CELL. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.

Example

See "Embedding Custom Measures in SELECT Statements".


PRINTLOG Procedure

This procedure sends a session log returned by the INTERP, INTERPCLOB, or GETLOG functions of this package to the print buffer, using the DBMS_OUTPUT package in PL/SQL.

When you are using SQL*Plus, you can direct the printer buffer to the screen by issuing the following command:

SET SERVEROUT ON SIZE 1000000

The SIZE clause increases the buffer from its default size of 4K.

If you are using a different program, refer to its documentation for the equivalent setting.

Syntax

DBMS_AW.PRINTLOG ( 
          session-log     IN   CLOB);

Parameters

Table 21-19 PRINTLOG Procedure Parameters

Parameter Description
session-log The log of a session.

Example

The following example shows the session log returned by the INTERP function.

SQL> SET SERVEROUT ON SIZE 1000000
SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERP('DESCRIBE analytic_cube_f.profit'));

DEFINE ANALYTIC_CUBE.F.PROFIT FORMULA DECIMAL <CHANNEL
GEOGRAPHY PRODUCT TIME>
EQ analytic_cube.f.sales - analytic_cube.f.costs

PL/SQL procedure successfully completed.