SQL>set serveroutput on
---- Number of members of Customer dimension
SQL>execute dbms_aw.execute('show statlen(customer)')
106

---- Hierarchies in Customer dimension;
SQL>execute dbms_aw.execute('rpr w 40 customer_hierlist');
CUSTOMER_HIERLIST
----------------------------------------
MARKET_ROLLUP
SHIPMENTS_ROLLUP

---- Levels in Customer dimension
SQL>execute dbms_aw.execute('rpr 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
SQL>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
SQL>execute dbms_aw.execute('limit customer to last 20');
SQL>execute dbms_aw.execute('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 
SQL>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

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

Summary of DBMS_AW Subprograms

The following table describes the subprograms provided in DBMS_AW.

---

ADD_DIMENSION_SOURCE Procedure

The ADD_DIMENSION_SOURCE procedure populates a table type named DBMS_AW$_DIMENSION_SOURCES_T with information about the dimensions of a cube. This information is analyzed by the ADVISE_SPARSITY procedure.

Syntax

ADD_DIMENSION_SOURCE ( 
          dimname  IN      VARCHAR2,
          colname  IN      VARCHAR2,
          sources  IN OUT  dbms_aw$_dimension_sources_t,
          srcval   IN      VARCHAR2     DEFAULT NULL,
          dimtype  IN      NUMBER       DEFAULT NO_HIER,
          hiercols IN      columnlist_t DEFAULT NULL,
          partby   IN      NUMBER       DEFAULT PARTBY_DEFAULT);

Parameters

Example

The following PL/SQL program fragment provides information about the TIME dimension for use by the Sparsity Advisor. The source data for the dimension is stored in a dimension table named TIME_DIM. Its primary key is named MONTH_ID, and the foreign key column in the fact table is also named MONTH_ID. The dimension hierarchy is level based as defined by the columns MONTH_ID, QUARTER_ID, and YEAR_ID.

The program declares a PL/SQL variable named DIMSOURCES with a table type of DBMS_AW$_DIMENSION_SOURCES_T to store the information.

DECLARE
     dimsources dbms_aw$_dimension_sources_t;
BEGIN
     dbms_aw.add_dimension_source('time', 'month_id', dimsources, 
          'time_dim', dbms_aw.hier_levels,
          dbms_aw$_columnlist_t('month_id', 'quarter_id', 'year_id'));
                   .
                   .
                   .
END;
/

See Also

"Using the Sparsity Advisor".

---

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,
          compressed              IN   BOOLEAN DEFAULT FALSE);

Parameters

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_DIMENSIONALITY Function

The ADVISE_DIMENSIONALITY function returns an OLAP DML definition of a composite dimension and the dimension order for variables in the cube, based on the sparsity recommendations generated by the ADVISE_SPARSITY procedure for a particular partition.

Syntax

ADVISE_DIMENSIONALITY ( 
          cubename   IN     VARCHAR2,
          sparsedfn  OUT    VARCHAR2
          sparsename IN     VARCHAR2 DEFAULT NULL,
          partnum    IN     NUMBER DEFAULT 1,
          advtable   IN     VARCHAR2 DEFAULT NULL)
     RETURN VARCHAR2;

Parameters

Example

The following PL/SQL program fragment defines two variables to store the recommendations returned by the ADVISE_DIMENSIONALITY function. SPARSEDIM stores the definition of the recommended composite, and DIMLIST stores the recommended dimension order of the cube.

DECLARE
     sparsedim VARCHAR2(500);
     dimlist VARCHAR2(500);
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
          .
          .
          .
dimlist := dbms_aw.advise_dimensionality('units_cube', sparsedim);
dbms_output.put_line('Sparse dimension:  ' || sparsedim);
dbms_output.put_line('Dimension list:  ' || dimlist);
END;
/

The program uses DBMS_OUTPUT.PUT_LINE to display the results of the analysis. The Sparsity Advisor recommends a composite dimension for the sparse dimensions, which are PRODUCT, CUSTOMER, and TIME. The recommended dimension order for UNITS_CUBE is CHANNEL followed by this composite.

Sparse dimension:  DEFINE units_cube.cp COMPOSITE <product customer time>
Dimension list:  channel units_cube.cp<product customer time>

The next example uses the Sparsity Advisor to evaluate the SALES table in the Sales History sample schema. A WHILE loop displays the recommendations for all partitions.

DECLARE
     dimlist VARCHAR2(500);
     sparsedim VARCHAR2(500);
     counter NUMBER(2) := 1;
     maxpart NUMBER(2);
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
          .
          .
          .
 
select max(partnum) into maxpart from sh_sparsity_advice;
WHILE counter <= maxpart LOOP
dimlist := dbms_aw.advise_dimensionality('sales_cube', sparsedim, 
   'sales_cube_composite', counter,   'sh_sparsity_advice');
dbms_output.put_line('Dimension list:  ' || dimlist);
dbms_output.put_line('Sparse dimension:  ' || sparsedim);
counter := counter+1;
END LOOP;
dbms_aw.advise_dimensionality(defs,'sales_cube', 'sales_cube_composite',
   'DECIMAL', 'sh_sparsity_advice');
dbms_output.put_line('Definitions:  ');
dbms_aw.printlog(defs);
END;
/

The Sparsity Advisor recommends 11 partitions; the first ten use the same composite. The last partition uses a different composite. (The SH_SPARSITY_ADVICE table shows that TIME_ID is dense in the last partition, whereas it is very sparse in the other partitions.)

Dimension list:  sales_cube_composite<time channel product promotion customer>
Sparse dimension:  DEFINE sales_cube_composite COMPOSITE COMPRESSED <time channel product promotion customer>
Dimension list:   sales_cube_composite<time channel product promotion customer>
Sparse dimension:  DEFINE sales_cube_composite COMPOSITE COMPRESSED <time channel product promotion customer>
                   .
                   .
                   .
Dimension list:  time sales_cube_composite<channel product promotion customer>
Sparse dimension:  DEFINE sales_cube_composite COMPOSITE COMPRESSED <channel product promotion customer>

See Also

"Using the Sparsity Advisor".

---

ADVISE_DIMENSIONALITY Procedure

The ADVISE_DIMENSIONALITY procedure evaluates the information provided by the ADVISE_SPARSITY procedure and generates the OLAP DML commands for defining a composite and a variable in the analytic workspace.

Syntax

ADVISE_DIMENSIONALITY ( 
          output     OUT    CLOB,
          cubename   IN     VARCHAR2,
          sparsename IN     VARCHAR2 DEFAULT NULL,
          dtype      IN     VARCHAR2 DEFAULT 'NUMBER',
          advtable   IN     VARCHAR2 DEFAULT NULL);

Parameters

Example

The following PL/SQL program fragment defines a variable named DEFS to store the recommended definitions.

DECLARE
     defs CLOB;
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
          .
          .
          .
dbms_aw.advise_dimensionality(defs, 'units_cube_measure_stored', 
     'units_cube_composite', 'DECIMAL');
dbms_output.put_line('Definitions:  ');
dbms_aw.printlog(defs);
END;
/

The program uses the DBMS_OUTPUT.PUT_LINE and DBMS_AW.PRINTLOG procedures to display the recommended object definitions.

Definitions:
DEFINE units_cube.cp COMPOSITE <product customer time>
DEFINE units_cube NUMBER VARIABLE <channel units_cube.cp<product customer time>>

In contrast to the Global schema, which is small and dense, the Sales cube in the Sales History sample schema is large and very sparse, and the Sparsity Advisor recommends 11 partitions. The following excerpt shows some of the additional OLAP DML definitions for defining a partition template and moving the TIME dimension members to the various partitions.

Definitions:
DEFINE sales_cube_composite_p1 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p2 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p3 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p4 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p5 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p6 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p7 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p8 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p9 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p10 COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_composite_p11 COMPOSITE <channel product promotion customer>
DEFINE sales_cube_pt PARTITION TEMPLATE <time channel product promotion customer> -
  PARTITION BY LIST (time) -
   (PARTITION p1 VALUES () <sales_cube_composite_p1<>> -
    PARTITION p2 VALUES () <sales_cube_composite_p2<>> -
    PARTITION p3 VALUES () <sales_cube_composite_p3<>> -
    PARTITION p4 VALUES () <sales_cube_composite_p4<>> -
    PARTITION p5 VALUES () <sales_cube_composite_p5<>> -
    PARTITION p6 VALUES () <sales_cube_composite_p6<>> -
    PARTITION p7 VALUES () <sales_cube_composite_p7<>> -
    PARTITION p8 VALUES () <sales_cube_composite_p8<>> -
    PARTITION p9 VALUES () <sales_cube_composite_p9<>> -
    PARTITION p10 VALUES () <sales_cube_composite_p10<>> -
    PARTITION p11 VALUES () <time sales_cube_composite_p11<>>)
MAINTAIN sales_cube_pt MOVE TO PARTITION p1 -
   '06-JAN-98', '07-JAN-98', '14-JAN-98', '21-JAN-98', -
   '24-JAN-98', '28-JAN-98', '06-FEB-98', '07-FEB-98', -
   '08-FEB-98', '16-FEB-98', '21-FEB-98', '08-MAR-98', -
   '20-MAR-98', '03-JAN-98', '26-JAN-98', '27-JAN-98'
MAINTAIN sales_cube_pt MOVE TO PARTITION p1 -
   '31-JAN-98', '11-FEB-98', '12-FEB-98', '13-FEB-98', -
   '15-FEB-98', '17-FEB-98', '14-MAR-98', '18-MAR-98', -
   '26-MAR-98', '30-MAR-98', '05-JAN-98', '08-JAN-98', -
   '10-JAN-98', '16-JAN-98', '23-JAN-98', '01-FEB-98'
MAINTAIN sales_cube_pt MOVE TO PARTITION p1 -
   '14-FEB-98', '28-FEB-98', '05-MAR-98', '07-MAR-98', -
   '15-MAR-98', '19-MAR-98', '17-JAN-98', '18-JAN-98', -
   '22-JAN-98', '25-JAN-98', '03-FEB-98', '10-FEB-98', -
   '19-FEB-98', '22-FEB-98', '23-FEB-98', '26-FEB-98'
          .
          .
          .

See Also

"Using the Sparsity Advisor".

---

ADVISE_PARTITIONING_DIMENSION Function

The ADVISE_PARTITIONING_DIMENSION function identifies the dimension that the Sparsity Advisor partitioned over, if any. It returns NULL when the Sparsity Advisor did not partition the cube.

Syntax

ADVISE_PARTITIONING_DIMENSION (
          cubename   IN  VARCHAR2,
          sources    IN  dbms_aw$_dimension_sources_t,
          advtable   IN  VARCHAR2 DEFAULT NULL)
     RETURN VARCHAR2;

Parameters

Example

The following program fragment shows the ADVISE_PARTITIONING_DIMENSION function being used to query the results after using the Sparsity Advisor.

DECLARE
     dimsources dbms_aw$_dimension_sources_t;
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
     .
     .
     .
dbms_output.put_line('Partitioning Dimension: ' || 
     dbms_aw.advise_partitioning_dimension('units_cube', dimsources,
    'aw_sparsity_advice'));
END;
/

The program uses DBMS_OUTPUT to display the partitioning dimension, which in this case is the TIME dimension.

Partitioning Dimension: time

See Also

"Using the Sparsity Advisor".

---

ADVISE_PARTITIONING_LEVEL Function

The ADVISE_PARTITIONING_LEVEL function returns the level used by the Sparsity Advisor for partitioning over a dimension. It returns NULL if the Sparsity Advisor did not partition the cube, and raises an exception if the dimension hierarchy is not level-based.

Syntax

ADVISE_PARTITIONING_LEVEL (
          cubename   IN  VARCHAR2,
          sources    IN  dbms_aw$_dimension_sources_t,
          advtable   IN  VARCHAR2 DEFAULT NULL)
     RETURN VARCHAR2;

Parameters

Example

The following program fragment shows the ADVISE_PARTITIONING_LEVEL function being used to query the results after using the Sparsity Advisor.

DECLARE
     dimsources dbms_aw$_dimension_sources_t;
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
     .
     .
     .
dbms_output.put_line('Partitioning Level: ' || 
     dbms_aw.advise_partitioning_level('units_cube', dimsources,
    'aw_sparsity_advice'));
END;
/

The program uses DBMS_OUTPUT to display the partitioning level, which in this case is YEAR.

Partitioning Level: year

See Also

"Using the Sparsity 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,
          compressed              IN   BOOLEAN DEFAULT FALSE);

Parameters

See Also

"Using the Aggregate Advisor".

---

ADVISE_SPARSITY Procedure

The ADVISE_SPARSITY procedure analyzes a fact table for sparsity using information about its dimensions provided by the ADD_DIMENSION_SOURCE procedure. It populates a table created by the SPARSITY_ADVICE_TABLE procedure with the results of its analysis.

Syntax

ADVISE_SPARSITY ( 
          fact       IN      VARCHAR2,
          cubename   IN      VARCHAR2,
          dimsources IN      dbms_aw$_dimension_sources_t,
          advmode    IN      BINARY_INTEGER DEFAULT ADVICE_DEFAULT,
          partby     IN      BINARY_INTEGER DEFAULT PARTBY_DEFAULT,
          advtable   IN      VARCHAR2 DEFAULT NULL);

Parameters

Output Description

Table 3-10 describes the information generated by ADVISE_SPARSITY.

Example

The following PL/SQL program fragment analyzes the sparsity characteristics of the UNITS_HISTORY_FACT table.

DECLARE
     dimsources dbms_aw$_dimension_sources_t;
BEGIN
-- Calls to ADD_DIMENSION_SOURCE for each dimension in the cube
                .
                .
                .

   dbms_aw.advise_sparsity('units_history_fact', 'units_cube', dimsources, 
        dbms_aw.advice_default);
 
END;
/

The following SELECT command displays the results of the analysis, which indicate that there is one denser dimension (CHANNEL) and three comparatively sparse dimensions (PRODUCT, CUSTOMER, and TIME).

SQL> 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         .86545382
units_history_fact   product      item_id         36     36 SPARSE        .98706809
units_history_fact   customer     ship_to_id      61     62 SPARSE        .99257713
units_history_fact   time         month_id        96     80 SPARSE        .99415964

See Also

"Using the Sparsity Advisor".

---

AW_ATTACH Procedure

The AW_ATTACH procedure attaches an 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

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

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

Parameters

Example

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

SQL>execute dbms_aw.aw_attach('global', true);

The next command creates an analytic workspace named GLOBAL_PROGRAMS in the user's schema. GLOBAL_PROGRAMS is attached read/write as the last user-owned analytic workspace.

SQL>execute dbms_aw.aw_attach('global_programs', true, true, 'last');

This command attaches an analytic workspace named SH from the SH_AW schema in read-only mode.

SQL>execute dbms_aw.aw_attach('sh_aw', 'sh');

See Also

"Managing Analytic Workspaces".

---

AW_COPY Procedure

The AW_COPY procedure copies the object definitions and data from one analytic workspace into a new analytic workspace.

AW_COPY detaches the original workspace and attaches the new workspace first with read/write access.

Syntax

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

Parameters

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.

SQL>execute dbms_aw.aw_copy('global', 'demo', null, 3);

See Also

"Managing Analytic Workspaces".

---

AW_CREATE Procedure

The AW_CREATE procedure creates a new, empty analytic workspace and makes it the current workspace in your session.

The current workspace is first in the list of attached workspaces.

Syntax

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

AW_CREATE ( 
           schema        IN VARCHAR2 ,
           awname        IN VARCHAR2 ,
           tablespace    IN VARCHAR2 DEFAULT NULL); 

Parameters

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.

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

SQL>execute dbms_aw.aw_create('global_aw.demo', 'global', 2);

---

AW_DELETE

The AW_DELETE procedure deletes an existing analytic workspace.

Syntax

AW_DELETE ( 
    awname        IN VARCHAR2);

AW_DELETE ( 
    schema        IN VARCHAR2,
    awname        IN VARCHAR2);

Parameters

Example

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

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

AW_DETACH ( 
          awname        IN VARCHAR2);

AW_DETACH ( 
          schema        IN VARCHAR2,
          awname        IN VARCHAR2);

Parameters

Example

The following command detaches the GLOBAL analytic workspace.

SQL>execute dbms_aw.aw_detach('global');

---

AW_RENAME Procedure

The AW_RENAME procedure changes the name of an analytic workspace.

Syntax

AW_RENAME ( 
          oldname       IN VARCHAR2,
          newname       IN VARCHAR2 );

Parameters

Example

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

SQL>execute dbms_aw.aw_rename('global', 'demo');

See Also

"Procedure: Convert an Analytic Workspace to the Latest Storage Format".

---

AW_TABLESPACE Function

The AW_TABLESPACE function returns the name of the tablespace in which a particular analytic workspace is stored.

Syntax

AW_TABLESPACE ( 
          awname        IN VARCHAR2)
     RETURN VARCHAR2;

AW_TABLESPACE ( 
          schema        IN VARCHAR2,
          awname        IN VARCHAR2)
     RETURN VARCHAR2;

Returns

Name of a tablespace.

Parameters

Example

The following example shows the tablespace in which the GLOBAL analytic workspace is stored.

SQL> set serveroutput on
SQL> execute dbms_output.put_line('Global is stored in tablespace ' || 
     dbms_aw.aw_tablespace('GLOBAL_AW', 'GLOBAL'));
Global is stored in tablespace GLOBAL_DATA
 
PL/SQL procedure successfully completed.

---

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.

If you do not specify a workspace to update, AW_UPDATE updates all the user-defined workspaces that are currently attached with read/write access.

Syntax

AW_UPDATE ( 
          awname     IN VARCHAR2 DEFAULT NULL);

AW_UPDATE ( 
          schema     IN VARCHAR2 DEFAULT NULL,
          awname     IN VARCHAR2 DEFAULT NULL);

Parameters

Example

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

SQL>execute dbms_aw.aw_update('global');

See Also

"Managing Analytic Workspaces".

---

CONVERT Procedure

The CONVERT procedure converts an analytic workspace from Oracle9i or Oracle Database 10g Release 1 format to Oracle Database 10g Release 2 format.

See "Converting an Analytic Workspace to Oracle 10g Storage Format".

Syntax

CONVERT (
          original_aw      IN VARCHAR2);

CONVERT ( 
          original_aw      IN VARCHAR2,
          converted_aw     IN VARCHAR2,
          tablespace       IN NUMBER DEFAULT);

Parameters

Example

This example performs the conversion in a single step, using the analytic workspace as both the source and the target of the conversion.

SQL>execute dbms_aw.convert ('global_aw');

The next example performs the conversion in several steps. The converted workspace must have the same name as the original workspace, because the fully-qualified names of objects in the workspace include the workspace name.

SQL>execute dbms_aw.rename ('global_aw', 'global_aw_temp');
SQL>execute dbms_aw.convert ('global_aw_temp', 'global_aw');
SQL>execute dbms_aw.delete ('global_aw_temp');

---

EVAL_NUMBER Function

The EVAL_NUMBER function evaluates a numeric expression in an analytic workspace and returns the resulting number.

You can specify the EVAL_NUMBER function in a SELECT from DUAL statement to return a numeric constant defined in an analytic workspace. Refer to the Oracle Database SQL Reference for information on selecting from the DUAL table.

Syntax

EVAL_NUMBER ( 
          olap_numeric_expression     IN    VARCHAR2)
     RETURN NUMBER;

Parameters

Example

The following example returns the value of the DECIMALS option in the current analytic workspace. The DECIMALS option controls the number of decimal places that are shown in numeric output. In this example, the value of DECIMALS is 2, which is the default.

SQL>set serveroutput on
SQL>select dbms_aw.eval_number('decimals') from dual; 

     DBMS_AW.EVAL_NUMBER('DECIMALS')
     -------------------------------
                                   2
     1 row selected.

---

EVAL_TEXT Function

The EVAL_TEXT function evaluates a text expression in an analytic workspace and returns the resulting character string.

You can specify the EVAL_TEXT function in a SELECT from DUAL statement to return a character constant defined in an analytic workspace. Refer to the Oracle Database SQL Reference for information on selecting from the DUAL table.

Syntax

EVAL_TEXT ( 
          olap_text_expression     IN    VARCHAR2)
    RETURN VARCHAR2;

Parameters

Example

The following example returns the value of the NLS_LANGUAGE option, which specifies the current language of the OLAP session. The value of NLS_LANGUAGE in this example is "AMERICAN".

SQL>set serveroutput on
SQL>select dbms_aw.eval_text('nls_language') from dual; 

     DBMS_AW.EVAL_TEXT('NLS_LANGUAGE')
     AMERICAN
     1 row selected.

---

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. In contrast to the RUN Procedure, EXECUTE continues to process commands after it gets an error.

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

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

The next example show how EXECUTE continues to process commands after encountering an error:

SQL> execute dbms_aw.execute('call nothing; colwidth=20');
BEGIN dbms_aw.execute('call nothing; colwidth=20'); END;
 
*
ERROR at line 1:
ORA-34492: Analytic workspace object NOTHING does not exist.
ORA-06512: at "SYS.DBMS_AW", line 90
ORA-06512: at "SYS.DBMS_AW", line 119
ORA-06512: at line 1
 
 
SQL> execute dbms_aw.execute('show colwidth');
20
 
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.

---

INFILE Procedure

The INFILE procedure evaluates the OLAP DML commands in the specified file and executes them in the current analytic workspace.

Syntax

INFILE ( 
          filename     IN   VARCHAR2);

Parameters

Example

The following example executes the OLAP DML commands specified in the file test_setup.tst. The directory path of the file is specified in the OLAP directory object called work_dir.

SQL>execute dbms_aw.execute('cda work_dir');
SQL>execute dbms_aw.infile('test_setup.tst');

---

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

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

Returns

The log for the 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 object, 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

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_ON Function

The OLAP_ON function returns a boolean indicating whether or not the OLAP option is installed in the database.

Syntax

OLAP_ON ( )
    RETURN BOOLEAN;

Returns

The value of the OLAP parameter in the V$OPTION table.

---

OLAP_RUNNING Function

The OLAP_RUNNING function returns a boolean indicating whether or not the OLAP option has been initialized in the current session. Initialization occurs when you execute an OLAP DML command (either directly or by using an OLAP PL/SQL or Java package), query an analytic workspace, or execute the STARTUP Procedure.

Syntax

OLAP_RUNNING( )
    RETURN BOOLEAN;

Returns

TRUE if OLAP has been initialized in the current session, or FALSE if it has not.

Example

The following PL/SQL script tests whether the OLAP environment has been initialized, and starts it if not.

BEGIN
 IF DBMS_AW.OLAP_RUNNING() THEN
   DBMS_OUTPUT.PUT_LINE('OLAP is already running');
 ELSE
     DBMS_AW.STARTUP;
     IF DBMS_AW.OLAP_RUNNING() THEN
        DBMS_OUTPUT.PUT_LINE('OLAP started successfully');
    ELSE
       DBMS_OUTPUT.PUT_LINE('OLAP did not start. Is it installed?');
     END IF;
 END IF;
END;
/

---

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

PRINTLOG ( 
          session-log     IN   CLOB);

Parameters

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.

---

RUN Procedure

The RUN 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. In contrast to the EXECUTE Procedure, RUN stops processing commands when it gets an error.

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.

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

RUN ( 
          olap_commands     IN      STRING, 
          silent            IN      BOOLEAN DEFAULT FALSE);
RUN ( 
          olap_commands     IN      CLOB,   
          silent            IN      BOOLEAN DEFAULT FALSE);
RUN ( 
          olap_commands     IN      STRING, 
          output            OUT     STRING);
RUN ( 
          olap_commands     IN      STRING, 
          output            IN OUT  CLOB);
RUN ( 
          olap_commands     IN      CLOB,   
          output            OUT     STRING);
RUN ( 
          olap_commands     IN      CLOB,
          output            IN OUT  CLOB);

Parameters

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 serveroutput on

SQL> execute dbms_aw.run('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.run('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.

The next example shows how RUN stops executing commands after encountering an error.

SQL> execute dbms_aw.execute('show colwidth');
10
 
PL/SQL procedure successfully completed.
 
SQL> execute dbms_aw.run('call nothing; colwidth=20');
BEGIN dbms_aw.run('call nothing; colwidth=20'); END;
 
*
ERROR at line 1:
ORA-34492: Analytic workspace object NOTHING does not exist.
ORA-06512: at "SYS.DBMS_AW", line 55
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at line 1
 
SQL> execute dbms_aw.execute('show colwidth');
10
 
PL/SQL procedure successfully completed.

---

SHUTDOWN Procedure

The SHUTDOWN procedure terminates the current OLAP session.

By default, the SHUTDOWN procedure terminates the session only if there are no outstanding changes to any of the attached read/write workspaces. If you want to terminate the session without updating the workspaces, specify the force parameter.

Syntax

SHUTDOWN ( 
          force     IN    BOOLEAN  DEFAULT NO);

Parameters

---

SPARSITY_ADVICE_TABLE Procedure

The SPARSITY_ADVICE_TABLE procedure creates a table for storing the advice generated by the ADVISE_SPARSITY procedure.

Syntax

SPARSITY_ADVICE_TABLE (
          tblname   IN    VARCHAR2 DEFAULT);

Parameters

Example

The following example creates a table named GLOBAL_SPARSITY_ADVICE.

execute dbms_aw.sparsity_advice_table('global_sparsity_advice');

See Also

ADVISE_SPARSITY Procedure for a description of the columns in tblname.

"Using the Sparsity Advisor".

---

STARTUP Procedure

The STARTUP procedure starts up an OLAP session without attaching any user-defined workspaces.

STARTUP initializes the OLAP processing environment and attaches the read-only EXPRESS workspace, which contains the program code for the OLAP engine.

Syntax

STARTUP ( );