Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E16760-04
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
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

41 DBMS_CUBE

DBMS_CUBE contains subprograms that create OLAP cubes and dimensions, and that load and process the data for querying.

See Also:

Oracle OLAP User's Guide regarding use of the OLAP option to support business intelligence and analytical applications

This chapter contains the following topics:


Using DBMS_CUBE

Cubes and cube dimensions are first class data objects that support multidimensional analytics. They are stored in a container called an analytic workspace. Multidimensional objects and analytics are available with the OLAP option to Oracle Database.

Cubes can be enabled as cube materialized views for automatic refresh of the cubes and dimensions, and for query rewrite. Several DBMS_CUBE subprograms support the creation and maintenance of cube materialized views as a replacement for relational materialized views. These subprograms are discussed in "Using SQL Aggregation Management".

The metadata for cubes and dimensions is defined in XML documents, called templates, which you can derive from relational materialized views using the CREATE_CUBE or DERIVE_FROM_MVIEW functions. Using a graphical tool named Analytic Workspace Manager, you can enhance the cube with analytic content or create the metadata for new cubes and cube dimensions from scratch.

Several other DBMS_CUBE subprograms provide a SQL alternative to Analytic Workspace Manager for creating an analytic workspace from an XML template and for refreshing the data stored in cubes and dimensions. The IMPORT_XML procedure creates an analytic workspace with its cubes and cube dimensions from an XML template. The BUILD procedure loads data into the cubes and dimensions from their data sources and performs whatever processing steps are needed to prepare the data for querying.


Security Model

The following roles and system privileges are required to use this package:

To create dimensional objects in the user's own schema: 

To create dimensional objects in different schemas: 

To create cube materialized views in the user's own schema: 

To create cube materialized views in different schemas: 

If the source tables are in a different schema, then the owner of the dimensional objects needs SELECT object privileges on those tables.


Using SQL Aggregation Management

SQL Aggregation Management is a group of PL/SQL subprograms in DBMS_CUBE that supports the rapid deployment of cube materialized views from existing relational materialized views. Cube materialized views are cubes that have been enhanced to use the automatic refresh and query rewrite features of Oracle Database. A single cube materialized view can replace many of the relational materialized views of summaries on a fact table, providing uniform response time to all summary data.

Cube materialized views bring the fast update and fast query capabilities of the OLAP option to applications that query summaries of detail relational tables. The summary data is generated and stored in a cube, and query rewrite automatically redirects queries to the cube materialized views. Applications experience excellent querying performance.

In the process of creating the cube materialized views, DBMS_CUBE also creates a fully functional analytic workspace including a cube and the cube dimensions. The cube stores the data for a cube materialized view instead of the table that stores the data for a relational materialized view. A cube can also support a wide range of analytic functions that enhance the database with information-rich content.

Cube materialized views are registered in the data dictionary along with all other materialized views. A CB$ prefix identifies a cube materialized view.

The DBMS_CUBE subprograms also support life-cycle management of cube materialized views.

See Also:

Oracle OLAP User's Guide for more information about cube materialized views and enhanced OLAP analytics.

Subprograms in SQL Aggregation Management

These subprograms are included in SQL Aggregation Management:


Requirements for the Relational Materialized View

SQL Aggregation Management uses an existing relational materialized view to derive all the information needed to generate a cube materialized view. The relational materialized view determines the detail level of data that is stored in the cube materialized view. The related relational dimension objects determine the scope of the aggregates, from the lowest level specified in the GROUP BY clause of the materialized view subquery, to the highest level of the dimension hierarchy.

The relational materialized view must conform to these requirements:

You can choose between two modes when rendering the cube materialized view, LOOSE and STRICT. In STRICT mode, any deviation from the requirements raises an exception and prevents the materialized view from being created. In LOOSE mode (the default), some deviations are allowed, but they affect the content of the materialized view. These elements in the relational materialized view generate warning messages:

You can also choose how conditions in the WHERE clause are filtered. When filtering is turned off, the conditions are ignored. When turned on, valid conditions are rendered in the cube materialized view, but asymmetric conditions among dimension levels raise an exception.


Permissions for Managing and Querying Cube Materialized Views

To create cube materialized views, you must have these privileges:

To access cube materialized views from another schema using query rewrite, you must have these privileges:

Note that you need SELECT privileges on the database objects that support the cube materialized views, but not on the cube materialized views.


Example of SQL Aggregation Management

All examples for the SQL Aggregate Management subprograms use the sample Sales History schema, which is installed in Oracle Database with two relational materialized views: CAL_MONTH_SALES_MV and FWEEK_PSCAT_SALES_MV.

About Relational Materialized View CAL_MONTH_SALES_MV

This example uses CAL_MONTH_SALES_MV as the basis for creating a cube materialized view. The following query was used to create CAL_MONTH_SALES_MV. CAL_MONTH_SALES_MV summarizes the daily sales data stored in the SALES table by month.

SELECT query FROM user_mviews 
     WHERE mview_name='CAL_MONTH_SALES_MV';
 
QUERY
--------------------------------------------
SELECT   t.calendar_month_desc
  ,        sum(s.amount_sold) AS dollars
  FROM     sales s
  ,        times t
  WHERE    s.time_id = t.time_id
  GROUP BY t.calendar_month_desc

DBMS_CUBE uses relational dimensions to derive levels and hierarchies for the cube materialized view. The SH schema has relational dimensions for most dimension tables in the schema, as shown by the following query.

SELECT dimension_name FROM user_dimensions;
 
DIMENSION_NAME
-------------------------------------
CUSTOMERS_DIM
PRODUCTS_DIM
TIMES_DIM
CHANNELS_DIM
PROMOTIONS_DIM

Creating the Cube Materialized View

This PL/SQL script uses the CREATE_MVIEW function to create a cube materialized view from CAL_MONTH_SALES_MV. CREATE_MVIEW sets the optional BUILD parameter to refresh the cube materialized view immediately.

SET serverout ON format wrapped
 
DECLARE
     salesaw  varchar2(30);
          
BEGIN
     salesaw := dbms_cube.create_mview('SH', 'CAL_MONTH_SALES_MV',
                'build=immediate');
END;
/

These messages confirm that the script created and refreshed CB$CAL_MONTH_SALES successfully:

Completed refresh of cube mview "SH"."CB$CAL_MONTH_SALES" at 20081112 08:42:58.0
03.
Created cube organized materialized view "CB$CAL_MONTH_SALES" for rewrite at 200
81112 08:42:58.004.

The following query lists the materialized views in the SH schema:

SELECT mview_name FROM user_mviews;

MVIEW_NAME
------------------------------
CB$CAL_MONTH_SALES
CB$TIMES_DIM_D1_CAL_ROLLUP
CAL_MONTH_SALES_MV
FWEEK_PSCAT_SALES_MV

Two new materialized views are registered in the data dictionary:

Cube dimension materialized views support refresh of the cube materialized view. You do not directly administer dimension materialized views.

Disabling the Relational Materialized Views

After creating a cube materialized view, disable query rewrite on all relational materialized views for the facts now supported by the cube materialized view. You can drop them when you are sure that you created the cube materialized view with the optimal parameters.

ALTER MATERIALIZED VIEW cal_month_sales_mv DISABLE QUERY REWRITE;
 
Materialized view altered.

You can also use the DISABLEQRW parameter in the CREATE_MVIEW function, which disables query rewrite on the source materialized view as described in Table 41-5.

Creating Execution Plans for Cube Materialized Views

You can create execution plans for cube materialized views the same as for relational materialized views. The following command generates an execution plan for a query against the SALES table, which contains data at the day level. The answer set requires data summarized by quarter. Query rewrite would not use the original relational materialized view for this query, because its data is summarized by month. However, query rewrite can use the new cube materialized view for summary data for months, quarters, years, and all years.

EXPLAIN PLAN FOR SELECT
           t.calendar_quarter_desc,
           sum(s.amount_sold) AS dollars
  FROM     sales s,
           times t
  WHERE    s.time_id = t.time_id
  AND      t.calendar_quarter_desc LIKE '2001%'
  GROUP BY t.calendar_quarter_desc
  ORDER BY t.calendar_quarter_desc;

The query returns these results:

CALENDAR_QUARTER_DESC    DOLLARS
--------------------- ----------
2001-01               6547097.44
2001-02               6922468.39
2001-03               7195998.63
2001-04               7470897.52

The execution plan shows that query rewrite returned the summary data from the cube materialized view, CB$CAL_MONTH_SALES, instead of recalculating it from the SALES table.

SELECT plan_table_output FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 2999729407

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |    30 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY                 |                    |     1 |    30 |     3  (34)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE CUBE ACCESS | CB$CAL_MONTH_SALES |     1 |    30 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CB$CAL_MONTH_SALES"."D1_CALENDAR_QUARTER_DESC" LIKE '2001%' AND
              "CB$CAL_MONTH_SALES"."SYS_GID"=63)

15 rows selected.

Maintaining Cube Materialized Views

You can create a cube materialized view that refreshes automatically. However, you can force a refresh at any time using the REFRESH_MVIEW Procedure:

BEGIN
     dbms_cube.refresh_mview('SH', 'CB$CAL_MONTH_SALES');
END;
/ 

Completed refresh of cube mview "SH"."CB$CAL_MONTH_SALES" at 20081112
14:30:59.534.

If you want to drop a cube materialized view, use the DROP_MVIEW Procedure so that all supporting database objects (analytic workspace, cube, cube dimensions, and so forth) are also dropped:

BEGIN
     dbms_cube.drop_mview('SH', 'CB$CAL_MONTH_SALES');
END;
/ 

Dropped cube organized materialized view "SH"."CAL_MONTH_SALES" including
container analytic workspace "SH"."CAL_MONTH_SALES_AW" at 20081112 13:38:47.878.

New Database Objects

The CREATE_MVIEW function creates several first class database objects in addition to the cube materialized views. You can explore these objects through the data dictionary by querying views such as ALL_CUBES and ALL_CUBE_DIMENSIONS.

This example created the following supporting objects:


Upgrading Metadata From OLAP 10g to OLAP 11g

You can upgrade an Oracle OLAP 10g analytic workspace to OLAP 11g by saving the metadata in an XML template and importing the XML into a new schema. The original analytic workspace remains accessible and unchanged by the upgrade process.

Tip:

Oracle recommends using Analytic Workspace Manager for performing upgrades. Using that method, you can upgrade in the same schema, whereas the following procedure requires you to upgrade in a different schema. See the Oracle OLAP User's Guide.

These subprograms in DBMS_CUBE support the upgrade process:

Prerequisites: 

The examples in the following procedure upgrade the example GLOBAL analytic workspace. The Oracle 10g schema is named GLOBAL_AW, and the Oracle 11g schema is named GLOBAL.

To upgrade an Oracle OLAP 10g analytic workspace: 

  1. Create an XML file describing the export options, as described in "EXPORT_XML_TO_FILE Procedure".

    <?xml version="1.0"?>
    <Export>
      <ExportOptions>
        <Option Name="SuppressNamespace" Value="True"/> 
        <Option Name="SuppressOwner" Value="True"/> 
        <Option Name="PreserveTableOwners" Value="True"/> 
      </ExportOptions>
    </Export> 
    
  2. Open SQL*Plus or a similar SQL command-line interface and connect to Oracle Database 11g as the new schema owner for the OLAP 11g analytic workspace.

  3. Identify objects that require renaming in OLAP 11g, as described in "INITIALIZE_CUBE_UPGRADE Procedure".

    execute dbms_cube.initialize_cube_upgrade;
    
  4. Create an XML template in OLAP 11g format, as described in "EXPORT_XML_TO_FILE Procedure".

    execute dbms_cube.export_xml_to_file('10g_schema', 'options_dir', 'options_file', 'output_dir', 'output_file');
    

    This example exports the metadata from all OLAP 10g analytic workspaces in the GLOBAL_AW schema to a file named global.xml in the UPGRADE_DIR directory. The options.xml file was created in step1.

    execute dbms_cube.export_xml_to_file('GLOBAL_AW', 'UPGRADE_DIR', 'options.xml', 'UPGRADE_DIR', 'global.xml');
    
  5. Create an OLAP 11g analytic workspace from the XML template, as described in "IMPORT_XML Procedure".

    execute dbms_cube.import_xml('dir_name', 'file_name');
    

    This example creates an OLAP 11g analytic workspace named GLOBAL from the metadata in global.xml:

    execute dbms_cube.import_xml('UPGRADE_DIR', 'global.xml');
    
  6. Load and aggregate the data in the new analytic workspace, as described in "BUILD Procedure".

    execute dbms_cube.build('cube1' [',cube2,...']);
    

    The following example builds two cubes in the GLOBAL analytic workspace:

    execute dbms_cube.build('units_cube', 'price_and_cost_cube');
    

Summary of DBMS_CUBE Subprograms

Table 41-1 DBMS_CUBE Subprograms

Subprogram Description

BUILD Procedure

Loads data into one or more cubes and dimensions, and prepares the data for querying.

CREATE_MVIEW Function

Creates a cube materialized view from the definition of a relational materialized view.

DERIVE_FROM_MVIEW Function

Creates an XML template for a cube materialized view from the definition of a relational materialized view.

DROP_MVIEW Procedure

Drops a cube materialized view.

EXPORT_XML Procedure

Exports the XML of an analytic workspace to a CLOB.

EXPORT_XML_TO_FILE Procedure

Exports the XML of an analytic workspace to a file.

IMPORT_XML Procedure

Creates, modifies, or drops an analytic workspace by using an XML template

INITIALIZE_CUBE_UPGRADE Procedure

Identifies Oracle OLAP 10g objects that must be renamed in Oracle 11g because of namespace conflicts.

REFRESH_MVIEW Procedure

Refreshes a cube materialized view.

VALIDATE_XML Procedure

Checks the XML to assure that it is valid, without committing the results to the database.



BUILD Procedure

This procedure loads data into one or more cubes and dimensions, and generates aggregate values in the cubes. The results are automatically committed to the database.

Syntax

DBMS_CUBE.BUILD (
       script                IN  VARCHAR2,
       method                IN  VARCHAR2        DEFAULT NULL,
       refresh_after_errors  IN  BOOLEAN         DEFAULT FALSE,
       parallelism           IN  BINARY_INTEGER  DEFAULT 0,
       atomic_refresh        IN  BOOLEAN         DEFAULT FALSE,
       automatic_order       IN  BOOLEAN         DEFAULT TRUE,
       add_dimensions        IN  BOOLEAN         DEFAULT TRUE,
       scheduler_job         IN  VARCHAR2        DEFAULT NULL,
       master_build_id       IN  BINARY_INTEGER  DEFAULT 0);

Parameters

Table 41-2 BUILD Procedure Parameters

Parameter Description

script

A list of cubes and dimensions and their build options (see "SCRIPT Parameter").

method

A full or a fast (partial) refresh. In a fast refresh, only changed rows are inserted in the cube and the affected areas of the cube are re-aggregated.

You can specify a method for each cube in sequential order, or a single method to apply to all cubes. If you list more cubes than methods, then the last method applies to the additional cubes.

  • C: Complete refresh clears all dimension values before loading. (Default)

  • F: Fast refresh of a cube materialized view, which performs an incremental refresh and re-aggregation of only changed rows in the source table.

  • ?: Fast refresh if possible, and otherwise a complete refresh.

  • P: Recomputes rows in a cube materialized view that are affected by changed partitions in the detail tables.

  • S: Fast solve of a compressed cube. A fast solve reloads all the detail data and re-aggregates only the changed values.

See the "Usage Notes" for additional details.

Methods do not apply to dimensions.

refresh_after_errors

TRUE to roll back just the cube or dimension with errors, and then continue building the other objects.

FALSE to roll back all objects in the build.

parallelism

Number of parallel processes to allocate to this job (see Usage Notes).

atomic_refresh

TRUE prevents users from accessing intermediate results during a build. It freezes the current state of an analytic workspace at the beginning of the build to provide current sessions with consistent data. This option thaws the analytic workspace at the end of the build to give new sessions access to the refreshed data. If an error occurs during the build, then all objects are rolled back to the frozen state.

FALSE enables users to access intermediate results during an build.

automatic_order

TRUE enables optimization of the build order. Dimensions are loaded before cubes.

FALSE builds objects in the order you list them in the script.

add_dimensions

TRUE automatically includes all the dimensions of the cubes in the build, whether or not you list them in the script. If a cube materialized view with a particular dimension is fresh, then that dimension is not reloaded. You can list a cube once in the script.

FALSE includes only dimensions specifically listed in the script.

scheduler_job

Any text identifier for the job, which will appear in the log table. The string does not need to be unique.

master_build_id

A unique name for the build.


SCRIPT Parameter

The SCRIPT parameter identifies the objects that will be included in the build, and specifies the type of processing that will be performed on each one. The parameter has this syntax:

object [ USING ( commands ) ][,...]

Where:

object is the qualified name of a cube or a dimension in the form aw_name.object, such as GLOBAL.TIME.

SCRIPT Parameter: USING Clause

The USING clause specifies the processing options. It consists of one or more commands separated by commas.

Note:

A cube with a rewrite materialized view cannot have a USING clause, except for the ANALYZE command. It uses the default build options.

The commands can be any of the following.

SCRIPT Parameter: USING Clause: AGGREGATE command

The AGGREGATE command in a script specifies the aggregation rules for one or more measures.

Note:

The AGGREGATE command is available only for uncompressed cubes.

AGGREGATE has the following syntax:

{ AGGREGATE USING MEASURE
     WHEN measure1 THEN operator1
     WHEN measure2 THEN operator2...
          ELSE default_operator
|
 [AGGREGATE USING] operator_clause }
processing_options
OVER { ALL | dimension | dimension HIERARCHIES (hierarchy)}

USING MEASURE Clause

This clause enables you to specify different aggregation operators for different measures in the cube.

Operator Clause

The operator_clause has this syntax:

operator(WEIGHTBY expression | SCALEBY expression)

WEIGHTBY multiplies each data value by an expression before aggregation.

SCALEBY adds the value of an expression to each data value before aggregation.

Table 41-3 Aggregation Operators

Operator Option Description

AVG

WEIGHTBY

Adds data values, then divides the sum by the number of data values that were added together.

FIRST

WEIGHTBY

The first real data value.

HIER_AVG

WEIGHTBY

Adds data values, then divides the sum by the number of the children in the dimension hierarchy. Unlike AVERAGE, which counts only non-NA children, HAVERAGE counts all of the logical children of a parent, regardless of whether each child does or does not have a value.

HIER_FIRST

WEIGHTBY

The first data value in the hierarchy, even when that value is NA.

HIER_LAST

WEIGHTBY

The last data value in the hierarchy, even when that value is NA.

LAST

WEIGHTBY

The last real data value.

MAX

WEIGHTBY

The largest data value among the children of each parent.

MIN

WEIGHTBY

The smallest data value among the children of each parent.

SUM

SCALEBY | WEIGHTBY

Adds data values. (Default)


Processing Options

You can specify these processing options for aggregation:

Usage Notes

Build Methods

The C, S, and ? methods always succeed and can be used on any cube.

The F and P methods require that the cube have a materialized view that was created as a fast or a rewrite materialized view.

Parallelism

Partitioned cubes can be loaded and aggregated in parallel processes. For example, a cube with five partitions can use up to five processes. Dimensions are always loaded serially.

The number of parallel processes actually allocated by a build is controlled by the smallest of these factors:

Suppose UNITS_CUBE has 12 partitions, PARALLELISM is set to 10, and JOB_QUEUE_PROCESSES is set to 4. OLAP uses four processes, which appear as slave processes in the build log.

The SQL engine may allocate additional processes when the PARALLEL_DEGREE_POLICY database initialization parameter is set to AUTO or LIMITED. For example, if OLAP allocates four processes, the SQL engine might determine that two of those processes should be done by four processes instead, for a total of six processes.

Build Logs

OLAP generates three logs that provide diagnostic information about builds:

Analytic Workspace Manager creates these logs automatically as tables in the same schema as the analytic workspace. If you do not use Analytic Workspace Manager, you can create and manage the logs in PL/SQL using the DBMS_CUBE_LOG package.

You can also create the cube log file by running $ORACLE_HOME/olap/admin/utlolaplog.sql. This script creates three additional views:

This report shows a successfully completed build of the objects in the GLOBAL analytic workspace, which has four dimensions and two cubes.

SELECT command, status, build_object, build_object_type type 
     FROM cube_build_report_latest;
 
COMMAND                   STATUS     BUILD_OBJECT                   TYPE
------------------------- ---------- ------------------------------ ----------
BUILD                     COMPLETED                                 BUILD
FREEZE                    COMPLETED                                 BUILD
LOAD NO SYNCH             COMPLETED  CHANNEL                        DIMENSION
COMPILE                   COMPLETED  CHANNEL                        DIMENSION
UPDATE/COMMIT             COMPLETED  CHANNEL                        DIMENSION
LOAD NO SYNCH             COMPLETED  CUSTOMER                       DIMENSION
COMPILE                   COMPLETED  CUSTOMER                       DIMENSION
UPDATE/COMMIT             COMPLETED  CUSTOMER                       DIMENSION
LOAD NO SYNCH             COMPLETED  PRODUCT                        DIMENSION
COMPILE                   COMPLETED  PRODUCT                        DIMENSION
UPDATE/COMMIT             COMPLETED  PRODUCT                        DIMENSION
LOAD NO SYNCH             COMPLETED  TIME                           DIMENSION
COMPILE                   COMPLETED  TIME                           DIMENSION
UPDATE/COMMIT             COMPLETED  TIME                           DIMENSION
COMPILE AGGMAP            COMPLETED  PRICE_CUBE                     CUBE
UPDATE/COMMIT             COMPLETED  PRICE_CUBE                     CUBE
COMPILE AGGMAP            COMPLETED  UNITS_CUBE                     CUBE
UPDATE/COMMIT             COMPLETED  UNITS_CUBE                     CUBE
DBMS_SCHEDULER.CREATE_JOB COMPLETED  PRICE_CUBE                     CUBE
DBMS_SCHEDULER.CREATE_JOB COMPLETED  UNITS_CUBE                     CUBE
BUILD                     COMPLETED                                 BUILD
LOAD                      COMPLETED  PRICE_CUBE                     CUBE
SOLVE                     COMPLETED  PRICE_CUBE                     CUBE
UPDATE/COMMIT             COMPLETED  PRICE_CUBE                     CUBE
BUILD                     COMPLETED                                 BUILD
LOAD                      COMPLETED  UNITS_CUBE                     CUBE
SOLVE                     COMPLETED  UNITS_CUBE                     CUBE
UPDATE/COMMIT             COMPLETED  UNITS_CUBE                     CUBE
ANALYZE                   COMPLETED  PRICE_CUBE                     CUBE
ANALYZE                   COMPLETED  UNITS_CUBE                     CUBE
THAW                      COMPLETED                                 BUILD
 
31 rows selected.

Examples

This example uses the default parameters to build UNITS_CUBE.

EXECUTE DBMS_CUBE.BUILD('GLOBAL.UNITS_CUBE');

The next example builds UNITS_CUBE and explicitly builds two of its dimensions, TIME and CHANNEL.

BEGIN
      DBMS_CUBE.BUILD(
          'GLOBAL.TIME USING (LOAD NO SYNCH, COMPILE),
           GLOBAL.CHANNEL,
           GLOBAL.UNITS_CUBE USING (CLEAR LEAVES, LOAD, SOLVE, ANALYZE)
          ',
               '?',           -- solve
               false,         -- refresh after errors
               2,             -- parallelism
               false,         -- atomic refresh
               true,          -- automatic order
               false,         -- add dimensions
               'Units Cube'   -- identify job
      );
END;
/

CREATE_MVIEW Function

This function creates a cube materialized view from the definition of a relational materialized view.

Syntax

DBMS_CUBE.CREATE_MVIEW (
          mvowner        IN  VARCHAR2,
          mvname         IN  VARCHAR2,
          sam_parameters IN  CLOB  DEFAULT NULL)
     RETURN VARCHAR2;

Parameters

Table 41-4 CREATE_MVIEW Function Parameters

Parameter Description

mvowner

Owner of the relational materialized view.

mvname

Name of the relational materialized view. For restrictions, see "Requirements for the Relational Materialized View".

A single cube materialized view can replace many of the relational materialized views for a table. Choose the materialized view that has the lowest levels of the dimension hierarchies that you want represented in the cube materialized view.

sam_parameters

Parameters in the form 'parameter1=value1, parameter2=value2,...'. See "SQL Aggregation Management Parameters".


SQL Aggregation Management Parameters

The CREATE_MVIEW and DERIVE_FROM_MVIEW functions use the SQL aggregation management (SAM) parameters described in Table 41-5. Some parameters support the development of cubes with advanced analytics. Other parameters support the development of Java applications. The default settings are appropriate for cube materialized views that are direct replacements for relational materialized views.

Table 41-5 SQL Aggregation Management Parameters

Parameter Description

ADDTOPS

Adds a top level and a level member to every dimension hierarchy in the cube. If the associated relational dimension has no hierarchy, then a dimension hierarchy is created.

TRUE: Creates levels named ALL_dimension with level members All_dimension. (Default)

FALSE: Creates only the hierarchies and levels identified by the relational dimensions.

ADDUNIQUEKEYPREFIX

Controls the creation of dimension keys.

TRUE: Creates cube dimension keys by concatenating the level name with the relational dimension key. This practice assures that the dimension keys are unique across all levels, such as CITY_NEW_YORK and STATE_NEW_YORK. (Default)

FALSE: Uses the relational dimension keys as cube dimension keys.

ATRMAPTYPE

Specifies whether attributes are mapped by hierarchy levels, dimension levels, or both.

HIER_LEVEL: Maps attributes to the levels of a particular dimension hierarchy. (Default)

DIM_LEVEL: Maps attributes to the levels of the dimension regardless of hierarchy.

BOTH: Maps attributes to both dimension and hierarchy levels.

AUTO: Maps attributes to the levels of the dimension for a star schema and to the levels of a particular dimension hierarchy for a snowflake schema.

AWNAME

Provides the name of the analytic workspace that owns the cube. Choose a simple database object name of 1 to 30 bytes. The default name is fact_tablename_AWn.

BUILD

Specifies whether a data refresh will immediately follow creation of the cube materialized view.

IMMEDIATE: Refreshes immediately.

DEFERRED: Does not perform a data refresh. (Default)

Note: Only the CREATE_MVIEW function uses this parameter.

CUBEMVOPTION

Controls validation and creation of a cube materialized view. Regardless of this setting, the function creates an analytic workspace containing a cube and its related cube dimensions.

COMPLETE_REFRESH: Creates a complete refresh cube materialized view (full update).

FAST_REFRESH: Creates a fast refresh materialized view (incremental update).

REWRITE_READY: Runs validation checks for a rewrite cube materialized view, but does not create it.

REWRITE: Creates a rewrite cube materialized view.

REWRITE_WITH_ATTRIBUTES: Creates a rewrite cube materialized view that includes columns with dimension attributes, resulting in faster query response times. (Default)

Note: The following settings do not create a cube materialized view. Use Analytic Workspace Manager to drop an analytic workspace that does not have a cube materialized view. You can use the DROP_MVIEW procedure to delete an analytic workspace only when it supports a cube materialized view.

NONE: Does not create a cube materialized view.

COMPLETE_REFRESH_READY: Runs validation checks for a complete refresh cube materialized view, but does not create it.

FAST_REFRESH_READY: Runs validation checks for fast refresh, but does not create the cube materialized view.

CUBENAME

Provides the name of the cube derived from the relational materialized view. Choose simple database object name of 1 to 30 bytes. The default name is fact_tablename_Cn.

DIMJAVABINDVARS

Supports access by Java programs to the XML document.

TRUE: Generates an XML template that uses Java bind variable notation for the names of dimensions. No XML validation is performed. You cannot use the IMPORT_XML procedure to create a cube using this template.

FALSE: Generates an XML template that does not support Java bind variables. (Default)

DISABLEQRW

Controls disabling of query rewrite on the source relational materialized view.

TRUE: Issues an ALTER MATERIALIZED VIEW mview_name DISABLE QUERY REWRITE command.

FALSE: No action.

Note: Only the CREATE_MVIEW function with BUILD=IMMEDIATE uses this parameter.

EXPORTXML

Exports the XML that defines the dimensional objects to a file, which you specify as dir/filename. Both the directory and the file name are case sensitive.

dir: Name of a database directory.

filename: The name of the file, typically given an XML filename extension.

FILTERPARTITIONANCESTORLEVELS

Controls the generation of aggregate values above the partitioning level of a partitioned cube.

TRUE: Removes levels above the partitioning level from the cube. Requests for summary values above the partitioning level are solved by SQL.

FALSE: All levels are retained in the cube. Requests for summary values are solved by OLAP. (Default)

LOGDEST

Directs and stores log messages. By default, the messages are not available.

SERVEROUT: Sends messages to server output (typically the screen), which is suitable when working interactively such as in SQL*Plus or SQL Developer.

TRACEFILE: Sends messages to the session trace file.

PARTITIONOPTION

Controls partitioning of the cube.

NONE: Prevents partitioning.

DEFAULT: Allows the Sparsity Advisor to determine whether partitioning is needed and how to partition the cube. (Default)

FORCE: Partitions the cube even when the Sparsity Advisor recommends against it. The Sparsity Advisor identifies the best dimension, hierarchy, and level to use for partitioning.

dimension.hierarchy.level: Partitions the cube using the specified dimension, hierarchy, and level.

POPULATELINEAGE

Controls the appearance of attributes in a cube materialized view.

TRUE: Includes all dimension attributes in the cube materialized view. (Default)

FALSE: Omits all dimension attributes from the cube materialized view.

PRECOMPUTE

Identifies a percentage of the data that is aggregated and stored. The remaining values are calculated as required by queries during the session.

precompute_percentage[:precompute_top_percentage]

Specify the top percentage for partitioned cubes. The default value is 35:0, which specifies precomputing 35% of the bottom partition and 0% of the top partition. If the cube is not partitioned, then the second number is ignored.

REMAPCOMPOSITEKEYS

Controls how multicolumn keys are rendered in the cube.

TRUE: Creates a unique key attribute whose values are concatenated string expressions with an underscore between the column values. For example, the value BOSTON_MA_USA might be an expression produced from a multicolumn key composed of CITY, STATE, and COUNTRY columns. In addition, an attribute is created for each individual column to store the relational keys. (Default)

FALSE: Creates a unique key attribute for each column.

RENDERINGMODE

Controls whether a loss in fidelity between the relational materialized view and the cube materialized view results in a warning message or an exception. See "Requirements for the Relational Materialized View".

LOOSE: Losses are noted in the optional logs generated by the CREATE_MVIEW Function and the DERIVE_FROM_MVIEW Function. No exceptions are raised. (Default)

STRICT: Any loss in fidelity raises an exception so that no XML template is created.

SEEFILTERS

Controls whether conditions in the WHERE clause of the relational materialized view's defining query are retained or ignored.

TRUE: Renders valid conditions in the XML template. (Default)

FALSE: Ignores all conditions.

UNIQUENAMES

Controls whether top level dimensional objects have unique names. Cross namespace conflicts may occur because dimensional objects have different namespaces than relational objects.

TRUE: Modifies all relational names when they are rendered in the cube.(Default)

FALSE: Duplicates relational names in the cube unless a naming conflict is detected. In that case, a unique name is created.

UNKNOWNKEYASDIM

Controls handling of simple columns with no levels or hierarchies in the GROUP BY clause of the relational materialized view's defining query.

TRUE: Renders a simple column without a relational dimension as a cube dimension with no levels or hierarchies.

FALSE: Raises an exception when no relational dimension is found for the column. (Default)

VALIDATEXML

Controls whether the generated XML document is validated.

TRUE: Validates the template using the VALIDATE_XML procedure. (Default)

FALSE: No validation is done.


Returns

The name of the cube materialized view created by the function.

Usage Notes

See "Using SQL Aggregation Management"

Examples

All examples for the SQL Aggregate Management subprograms use the sample Sales History schema, which is installed in Oracle Database with two relational materialized views: CAL_MONTH_SALES_MV and FWEEK_PSCAT_SALES_MV.

The following script creates a cube materialized view using CAL_MONTH_SALES_MV as the relational materialized view. It uses all default options.

SET serverout ON format wrapped
 
DECLARE
     salesaw  varchar2(30);

BEGIN
     salesaw := dbms_cube.create_mview('SH', 'CAL_MONTH_SALES_MV');
END;
/

The next example sets several parameters for creating a cube materialized view from FWEEK_PSCAT_SALES_MV. These parameters change the cube materialized view in the following ways:

DECLARE
     salescubemv   varchar2(30);
     sam_param     clob := 'ADDTOPS=FALSE,
                            PRECOMPUTE=40:10,
                            EXPORTXML=WORK_DIR/sales.xml,
                            BUILD=IMMEDIATE';
  
BEGIN
     salescubemv := dbms_cube.create_mview('SH', 'FWEEK_PSCAT_SALES_MV', 
                    sam_param);
END;
/

DERIVE_FROM_MVIEW Function

This function generates an XML template that defines a cube with materialized view capabilities, using the information derived from an existing relational materialized view.

Syntax

DBMS_CUBE.DERIVE_FROM_MVIEW (
          mvowner        IN  VARCHAR2,
          mvname         IN  VARCHAR2,
          sam_parameters IN  CLOB  DEFAULT NULL)
     RETURN CLOB;

Parameters

Table 41-6 DERIVE_FROM_MVIEW Function Parameters

Parameter Description

mvowner

Owner of the relational materialized view.

mvname

Name of the relational materialized view. For restrictions, see "Requirements for the Relational Materialized View".

A single cube materialized view can replace many of the relational materialized views for a table. Choose the materialized view that has the lowest levels of the dimension hierarchies that you want represented in the cube materialized view.

sam_parameters

Optional list of parameters in the form 'parameter1=value1, parameter2=value2,...'. See "SQL Aggregation Management Parameters".


Returns

An XML template that defines an analytic workspace containing a cube enabled as a materialized view.

Usage Notes

To create a cube materialized view from an XML template, use the IMPORT_XML procedure. Then use the REFRESH_MVIEW procedure to refresh the cube materialized view with data.

See "Using SQL Aggregation Management".

Examples

The following example generates an XML template named sales_cube.xml from the CAL_MONTH_SALES_MV relational materialized view in the SH schema.

DECLARE
     salescubexml  clob := null;
     sam_param     clob := 'exportXML=WORK_DIR/sales_cube.xml';
     
BEGIN
     salescubexml := dbms_cube.derive_from_mview('SH', 'CAL_MONTH_SALES_MV',
     sam_param);
END;
/

DROP_MVIEW Procedure

This procedure drops a cube materialized view and all associated objects from the database. These objects include the dimension materialized views, cubes, cube dimensions, levels, hierarchies, and the analytic workspace.

Syntax

DBMS_CUBE.DROP_MVIEW (
          mvowner        IN  VARCHAR2,
          mvname         IN  VARCHAR2,
          sam_parameters IN  CLOB  DEFAULT NULL);

Parameters

Table 41-7 DROP_MVIEW Procedure Parameters

Parameter Description

mvowner

Owner of the cube materialized view

mvname

Name of the cube materialized view

sam_parameters

EXPORTXML: Exports the XML that drops the dimensional objects to a file, which you specify as dir/filename. Both the directory and the file name are case sensitive.

dir: Name of a database directory.

filename: The name of the file, typically given an XML filename extension.


Usage Notes

Use this procedure to drop a cube materialized view that you created using the CREATE_MVIEW and DERIVE_FROM_MVIEW functions. If you make modifications to the cubes or dimensions, then DROP_MVIEW may not be able to drop the cube materialized view.

Some of the CUBEMVOPTION parameters used by the CREATE_MVIEW and DERIVE_FROM_MVIEW functions do not create a materialized view. Use Analytic Workspace Manager to drop the analytic workspace, cubes, and cube dimensions.

If you use the EXPORTXML parameter, then you can use the XML document to drop the cube materialized view, after you re-create it. Use the IMPORT_XML procedure.

See "Using SQL Aggregation Management".

Examples

The current schema has four materialized views. CB$CAL_MONTH_SALES is a cube materialized view for the SALES table. CB$TIMES_DIM_D1_CAL_ROLLUP is a cube dimension materialized view for the TIMES_DIM dimension on the TIMES dimension table. The others are relational materialized views.

SELECT mview_name FROM user_mviews;
 
MVIEW_NAME
------------------------------
CB$CAL_MONTH_SALES
CB$TIMES_DIM_D1_CAL_ROLLUP
CAL_MONTH_SALES_MV
FWEEK_PSCAT_SALES_MV
 

The following command drops both CB$CAL_MONTH_SALES and CB$TIMES_DIM_D1_CAL_ROLLUP.

EXECUTE dbms_cube.drop_mview('SH', 'CB$CAL_MONTH_SALES');

Dropped cube organized materialized view "SH"."CAL_MONTH_SALES" 
including container analytic workspace "SH"."CAL_MONTH_SALES_AW" 
at 20081110 16:31:40.056.

This query against the data dictionary confirms that the materialized views have been dropped.

SELECT mview_name FROM user_mviews;
 
MVIEW_NAME
------------------------------
CAL_MONTH_SALES_MV
FWEEK_PSCAT_SALES_MV

EXPORT_XML Procedure

This procedure writes OLAP metadata to a CLOB.

Syntax

DBMS_CUBE.EXPORT_XML
       (object_ids            IN      VARCHAR2,
        out_xml               IN/OUT  CLOB;

DBMS_CUBE.EXPORT_XML
       (object_ids            IN      VARCHAR2,
        options_xml           IN      CLOB,
        out_xml               IN/OUT  CLOB;

DBMS_CUBE.EXPORT_XML
       (object_ids            IN      VARCHAR2,
        options_dirname       IN      VARCHAR2,
        options_filename      IN      VARCHAR2,
        out_xml               IN/OUT  CLOB;

Parameters

Table 41-8 EXPORT_XML Procedure Parameters

Parameter Description

object_ids

Either of these identifiers:

  • The name of a schema, such as GLOBAL.

  • The fully qualified name of an analytic workspace in the form owner.aw_name.AW, such as GLOBAL.GLOBAL.AW.

options_dirname

The case-sensitive name of a database directory that contains options_filename.

options_filename

A file containing an XML document of export options.

options_xml

A CLOB variable that contains an XML document of export options.

out_xml

A CLOB variable that will store the XML document of OLAP metadata for the objects listed in object_ids.


Export Options

See "EXPORT_XML_TO_FILE Procedure".

Usage Notes

See "Upgrading Metadata From OLAP 10g to OLAP 11g".

Example

The following PL/SQL script uses CLOBs to upgrade an OLAP 10g analytic workspace named GLOBAL in the GLOBAL_AW schema. The result of the upgrade is a copy of the original analytic workspace in OLAP 11g format. The new analytic workspace is also named GLOBAL and is created in the GLOBAL schema.

The PL/SQL client must be connected to the database as GLOBAL. The GLOBAL user must have SELECT permissions on GLOBAL_AW.AW$GLOBAL and on all relational data sources. See "Upgrading Metadata From OLAP 10g to OLAP 11g".

DECLARE
  xmlClob clob;
  optionsClob clob;
BEGIN
  -- Create a CLOB for the export options
  dbms_lob.createtemporary(optionsClob, TRUE);
  dbms_lob.open(optionsClob, DBMS_LOB.LOB_READWRITE);
  dbms_lob.writeappend(optionsClob, 8, '<Export>');
  dbms_lob.writeappend(optionsClob,15, '<ExportOptions>');
  dbms_lob.writeappend(optionsClob,47, '<Option Name="SuppressNamespace" Value="True"/>');
  dbms_lob.writeappend(optionsClob,43, '<Option Name="SuppressOwner" Value="True"/>');
  dbms_lob.writeappend(optionsClob,49, '<Option Name="PreserveTableOwners" Value="True"/>');
  dbms_lob.writeappend(optionsClob,16, '</ExportOptions>');
  dbms_lob.writeappend(optionsClob, 9, '</Export>');
  dbms_lob.close(optionsClob);
 
  -- Create a CLOB for the XML template
  dbms_lob.createtemporary(xmlClob, TRUE);
  
  -- Identify duplicate names
  dbms_cube.initialize_cube_upgrade;
 
  -- Export metadata from an analytic workspace to a CLOB
  dbms_cube.export_xml('GLOBAL_AW.GLOBAL.AW', optionsClob, xmlClob); 
 
  -- Import metadata from the CLOB
  dbms_cube.import_xml(xmlClob);
 
  -- Load and aggregate the data
  dbms_cube.build('GLOBAL.UNITS_CUBE', 'GLOBAL.PRICE_AND_COST_CUBE');
 
END;
/

EXPORT_XML_TO_FILE Procedure

This procedure exports OLAP metadata to a file. This file can be imported into a new or existing analytic workspace using the IMPORT_XML procedure. In this way, you can create a copy of the analytic workspace in another schema or database.

This procedure can also be used as part of the process for upgrading CWM or OLAP standard form (AWXML) metadata contained in an Oracle OLAP 10g analytic workspace to OLAP 11g format.

Syntax

DBMS_CUBE.EXPORT_XML_TO_FILE
       (object_ids            IN      VARCHAR2,
        output_dirname        IN      VARCHAR2,
        output_filename       IN      VARCHAR2;

DBMS_CUBE.EXPORT_XML_TO_FILE
       (object_ids            IN      VARCHAR2,
        options_dirname       IN      VARCHAR2,
        options_filename      IN      VARCHAR2,
        output_dirname        IN      VARCHAR2,
        output_filename       IN      VARCHAR2;

Parameters

Table 41-9 EXPORT_XML_TO_FILE Procedure Parameters

Parameter Description

object_ids

Either of these identifiers:

  • The name of a schema, such as GLOBAL_AW.

  • The fully qualified name of an analytic workspace in the form owner.aw_name.AW, such as GLOBAL_AW.GLOBAL.AW.

options_dirname

The case-sensitive name of a database directory that contains options_filename. See "Export Options".

options_filename

The name of a file containing an XML document of export options. See "Export Options".

output_dirname

The case-sensitive name of a database directory where output_filename is created.

output_filename

The name of the template file created by the procedure.


Export Options

The default settings for the export options are appropriate in most cases, and you can omit the options_dirname and options_filename parameters. However, when upgrading Oracle OLAP 10g metadata to OLAP 11g, you must use these parameters to specify an XML document that changes the default settings, like the following:

<?xml version="1.0"?>
<Export>
  <ExportOptions>
    <Option Name="SuppressNamespace" Value="True"/> 
    <Option Name="SuppressOwner" Value="True"/> 
    <Option Name="PreserveTableOwners" Value="True"/> 
  </ExportOptions>
</Export> 

Table 41-10 Export Options

Option Description

SuppressNamespace

Controls the use of Namespace attributes in XML elements and the namespace qualifier in object names. Enter True to drop the namespace from the XML, or enter False to retain it (default). Enter True when upgrading to Oracle OLAP 11g metadata.

SuppressOwner

Controls the use of the Owner attribute in XML elements and the owner qualifier in object names. Enter True to drop the owner from the XML, or enter False to retain it. Enter True if you plan to import the exported metadata into a different schema, such as when upgrading to Oracle OLAP 11g metadata.

PreserveTableOwners

Controls the use of the owner in qualifying table names in the mapping elements, such as GLOBAL.UNITS_HISTORY_FACT instead of UNITS_HISTORY_FACT. Enter True to retain the table owner, or enter False to default to the current schema for table mappings. If you plan to import the exported metadata to a different schema, you must set this option to True to load data from tables and views in the original schema, unless the destination schema has its own copies of the tables and views.


Usage Notes

See "Upgrading Metadata From OLAP 10g to OLAP 11g".

Examples

The following example generates an XML file named global.xml in OLAP 11g format using the default export settings. The metadata is derived from all analytic workspaces and CWM metadata in the GLOBAL_AW schema. The output file is generated in the WORK_DIR database directory.

execute dbms_cube.export_xml_to_file('GLOBAL_AW', 'WORK_DIR', 'global.xml');

The next example also generates an XML file named global.xml in OLAP 11g format using the export options set in options.xml. The metadata is derived from the GLOBAL analytic workspace in the GLOBAL_AW schema. Both the options file and the output file are in the WORK_DIR database directory.

execute dbms_cube.export_xml_to_file('GLOBAL_AW.GLOBAL.AW', 'WORK_DIR', 'options.xml', 'WORK_DIR', 'global.xml');

The following PL/SQL script upgrades an Oracle OLAP 10g analytic workspace named GLOBAL in the GLOBAL_AW schema to an OLAP 11g analytic workspace named GLOBAL in the GLOBAL schema. See "Upgrading Metadata From OLAP 10g to OLAP 11g" for information about upgrading.

BEGIN
-- Identify duplicate names
dbms_cube.initialize_cube_upgrade;
 
-- Export metadata from the GLOBAL analytic workspace to a file named sales_upgrade.xml
dbms_cube.export_xml_to_file('GLOBAL_AW', 'UPGRADE_DIR', 'options.xml', 'UPGRADE_DIR', 'sales_upgrade.xml');
 
-- Import metadata from sales_upgrade.xml to the current user
dbms_cube.import_xml('UPGRADE_DIR', 'sales_upgrade.xml');
 
-- Load and aggregate the data
dbms_cube.build('GLOBAL.UNITS_CUBE', 'GLOBAL.PRICE_AND_COST_CUBE');
 
END;
/

IMPORT_XML Procedure

This procedure creates, modifies, or drops an analytic workspace by using an XML template.

Syntax

DBMS_CUBE.IMPORT_XML
       (dirname               IN      VARCHAR2,
        filename              IN      VARCHAR2,
        out_xml               IN/OUT  CLOB );

DBMS_CUBE.IMPORT_XML
       (in_xml                IN      CLOB );

DBMS_CUBE.IMPORT_XML
       (in_xml                IN      CLOB,
        out_xml               IN/OUT  CLOB );

Parameters

Table 41-11 IMPORT_XML Procedure Parameters

Parameter Description

dirname

The case-sensitive name of a database directory.

filename

A file containing an XML template.

in_xml

A CLOB containing an XML template.

out_xml

An XML file generated by DBMS_CUBE that shows changes DBMS_CUBE made to the imported XML, such as setting default values or making minor corrections to the XML.


Usage Notes

The XML can define an entire analytic workspace, a single cube, or a single dimension. When re-creating just a cube or dimension, you must provide the context of an existing analytic workspace.

You can also use IMPORT_XML to drop an analytic workspace by using the XML document generated by the DROP_MVIEW procedure.

See "Upgrading Metadata From OLAP 10g to OLAP 11g".

Example

This example loads an XML template from a file named GLOBAL.XML and located in a database directory named XML_DIR.

EXECUTE dbms_cube.import_xml('XML_DIR', 'GLOBAL.XML');

The next example is a SQL script that creates the GLOBAL analytic workspace from an XML template stored in a CLOB. The file is named GLOBAL.XML, and it is located in a database directory named XML_DIR. The previous example shows a more direct method for using an XML file.

DEFINE xml_file = 'GLOBAL.XML';
SET ECHO ON;
SET SERVEROUT ON;
 
DECLARE
     xml_file     BFILE := bfilename('XML_DIR', '&xml_file');
     in_xml       CLOB;
     out_xml      CLOB := NULL;
     dest_offset  INTEGER := 1;
     src_offset   INTEGER := 1;
     lang_context INTEGER := 0;
     warning      INTEGER;
BEGIN
     -- Setup the clob from a file
     DBMS_LOB.CREATETEMPORARY(in_xml, TRUE);
     DBMS_LOB.OPEN(in_xml, DBMS_LOB.LOB_READWRITE);
     DBMS_LOB.OPEN(xml_file, DBMS_LOB.FILE_READONLY);
     DBMS_LOB.LOADCLOBFROMFILE(in_xml, xml_file, DBMS_LOB.LOBMAXSIZE,
        dest_offset, src_offset, 0, lang_context, warning);
  
     -- Import the xml
     DBMS_CUBE.IMPORT_XML(in_xml);
END;
/

INITIALIZE_CUBE_UPGRADE Procedure

This procedure creates a table that identifies Oracle OLAP 10g objects that must be renamed in OLAP 11g because of namespace conflicts.

The export_xml and export_xml_to_file procedures use the names specified in the NEW_NAME column of the table to identify objects in CWM or OLAP standard form (AWXML) analytic workspaces, rather than the original names.

Syntax

DBMS_CUBE.INITIALIZE_CUBE_UPGRADE;

Usage Notes

This procedure creates a table named CUBE_UPGRADE_INFO.

When INITIALIZE_CUBE_UPGRADE detects a name conflict among subobjects such as levels, hierarchies, and dimension attributes, it creates a row in CUBE_UPGRADE_INFO providing a new, unique name for each one. Rows may also be created for objects that do not require renaming; these rows are distinguished by a value of 0 or null in the CONFLICT column. Top-level objects, such as dimensions and cubes, are not listed.

You can edit the table using SQL INSERT and UPDATE if you want different names for the OLAP 11g objects.

This procedure must be called before EXPORT_XML or EXPORT_XML_TO_FILE is used on any OLAP 10g analytic workspace in which two objects or subobjects have the same name.

See "Upgrading Metadata From OLAP 10g to OLAP 11g".

The following table describes the columns of CUBE_UPGRADE_INFO.

Column Datatype NULL Description
OWNER VARCHAR2 NOT NULL Owner of the analytic workspace.
AW VARCHAR2 NOT NULL Name of the analytic workspace.
AWXML_ID VARCHAR2 NOT NULL Full logical name of the object requiring modification, in the form simple_name.[subtype_name].object_type. For example, TIME.DIMENSION and PRODUCT.COLOR.ATTRIBUTE.
NEW_NAME VARCHAR2 NOT NULL The name the object will have in Oracle 11g after the upgrade.
OBJECT_CLASS VARCHAR2 -- DerivedMeasure for calculated measures, or empty for all other object types.
CONFLICT NUMBER -- Indicates the reason that the row was added to CUBE_UPGRADE_INFO:
  • 0: The object does not have a naming conflict but appears in the table for other reasons.

  • 1: Two objects have the same name and would create a conflict in the OLAP 11g namespace. The object type (such as level or hierarchy) will be added to the names.


Examples

The following command creates and populates the CUBE_UPGRADE_INFO table:

EXECUTE dbms_cube.initialize_cube_upgrade;

The table shows that the OLAP 10g analytic workspace has a hierarchy and a level named MARKET_SEGMENT, which will be renamed. The table also contains rows for calculated measures, but these objects do not require renaming: The value for CONFLICT is 0.

SELECT awxml_id, new_name, conflict FROM cube_upgrade_info;

AWXML_ID                                 NEW_NAME                    CONFLICT
---------------------------------------- ------------------------- ----------
CUSTOMER.MARKET_SEGMENT.HIERARCHY        MARKET_SEGMENT_HIERARCHY           1
CUSTOMER.MARKET_SEGMENT.LEVEL            MARKET_SEGMENT_LEVEL               1
UNITS_CUBE.EXTENDED_COST.MEASURE         EXTENDED_COST                      0
UNITS_CUBE.EXTENDED_MARGIN.MEASURE       EXTENDED_MARGIN                    0
UNITS_CUBE.CHG_SALES_PP.MEASURE          CHG_SALES_PP                       0
UNITS_CUBE.CHG_SALES_PY.MEASURE          CHG_SALES_PY                       0
UNITS_CUBE.PCTCHG_SALES_PP.MEASURE       PCTCHG_SALES_PP                    0
UNITS_CUBE.PCTCHG_SALES_PY.MEASURE       PCTCHG_SALES_PY                    0
UNITS_CUBE.PRODUCT_SHARE.MEASURE         PRODUCT_SHARE                      0
UNITS_CUBE.CHANNEL_SHARE.MEASURE         CHANNEL_SHARE                      0
UNITS_CUBE.MARKET_SHARE.MEASURE          MARKET_SHARE                       0
UNITS_CUBE.CHG_EXTMRGN_PP.MEASURE        CHG_EXTMRGN_PP                     0
UNITS_CUBE.CHG_EXTMRGN_PY.MEASURE        CHG_EXTMRGN_PY                     0
UNITS_CUBE.PCTCHG_EXTMRGN_PP.MEASURE     PCTCHG_EXTMRGN_PP                  0
UNITS_CUBE.PCTCHG_EXTMRGN_PY.MEASURE     PCTCHG_EXTMRGN_PY                  0
UNITS_CUBE.CHG_UNITS_PP.MEASURE          CHG_UNITS_PP                       0
UNITS_CUBE.EXTMRGN_PER_UNIT.MEASURE      EXTMRGN_PER_UNIT                   0
UNITS_CUBE.SALES_YTD.MEASURE             SALES_YTD                          0
UNITS_CUBE.SALES_YTD_PY.MEASURE          SALES_YTD_PY                       0
UNITS_CUBE.PCTCHG_SALES_YTD_PY.MEASURE   PCTCHG_SALES_YTD_PY                0
UNITS_CUBE.SALES_QTD.MEASURE             SALES_QTD                          0
UNITS_CUBE.CHG_UNITS_PY.MEASURE          CHG_UNITS_PY                       0

REFRESH_MVIEW Procedure

This procedure refreshes the data in a cube materialized view.

Syntax

DBMS_CUBE.REFRESH_MVIEW (
          mvowner              IN  VARCHAR2,
          mvname               IN  VARCHAR2,
          method               IN  VARCHAR2       DEFAULT NULL,
          refresh_after_errors IN  BOOLEAN        DEFAULT FALSE,
          parallelism          IN  BINARY_INTEGER DEFAULT NULL,
          atomic_refresh       IN  BOOLEAN        DEFAULT FALSE,
          scheduler_job        IN  VARCHAR2       DEFAULT NULL,
          sam_parameters       IN  CLOB           DEFAULT NULL);

Parameters

Table 41-12 REFRESH_MVIEW Procedure Parameters

Parameter Description

mvowner

Owner of the cube materialized view.

mvname

Name of the cube materialized view.

method

A full or a fast (partial) refresh. In a fast refresh, only changed rows are inserted in the cube and the affected areas of the cube are re-aggregated.

You can specify a method for each cube in sequential order, or a single method to apply to all cubes. If you list more cubes than methods, then the last method applies to the additional cubes.

  • C: Complete refresh clears all dimension values before loading. (Default)

  • F: Fast refresh of a cube materialized view, which performs an incremental refresh and re-aggregation of only changed rows in the source table.

  • ?: Fast refresh if possible, and otherwise a complete refresh.

  • P: Recomputes rows in a cube materialized view that are affected by changed partitions in the detail tables.

  • S: Fast solve of a compressed cube. A fast solve reloads all the detail data and re-aggregates only the changed values.

See the "Usage Notes" for the BUILD procedure for additional details.

refresh_after_errors

TRUE to roll back just the cube or dimension with errors, and then continue building the other objects.

FALSE to roll back all objects in the build.

parallelism

Number of parallel processes to allocate to this job.

See the "Usage Notes" for the BUILD procedure for additional details.

atomic_refresh

TRUE prevents users from accessing intermediate results during a build. It freezes the current state of an analytic workspace at the beginning of the build to provide current sessions with consistent data. This option thaws the analytic workspace at the end of the build to give new sessions access to the refreshed data. If an error occurs during the build, then all objects are rolled back to the frozen state.

FALSE enables users to access intermediate results during an build.

scheduler_job

Any text identifier for the job, which will appear in the log table. The string does not need to be unique.

sam_parameters

None.


Usage Notes

REFRESH_MVIEW changes mvname to the name of the cube, then passes the cube name and all parameters to the BUILD procedure. Thus, you can use the BUILD procedure to refresh a cube materialized view. See the "BUILD Procedure" for additional information about the parameters.

Examples

The following example uses the default settings to refresh a cube materialized view named CB$FWEEK_PSCAT_SALES.

SET serverout ON format wrapped
 
EXECUTE dbms_cube.refresh_mview('SH', 'CB$FWEEK_PSCAT_SALES');

The next example changes the refresh method to use fast refresh if possible, continue refreshing after an error, and use two parallel processes.

EXECUTE dbms_cube.refresh_mview('SH', 'CB$FWEEK_PSCAT_SALES', '?', TRUE, 2);

After successfully refreshing the cube materialized view, REFRESH_MVIEW returns a message like the following:

Completed refresh of cube mview "SH"."CB$FWEEK_PSCAT_SALES" at 20081114 15:04:46.370.

VALIDATE_XML Procedure

This procedure checks the XML to assure that it is valid without committing the results to the database. It does not create an analytic workspace.

Syntax

DBMS_CUBE.VALIDATE_XML
       (DIRNAME               IN  VARCHAR2,
        FILENAME              IN  VARCHAR2 ); 

DBMS_CUBE.VALIDATE_XML
       (IN_XML                IN  CLOB ); 

Parameters

Table 41-13 VALIDATE_XML Procedure Parameters

Parameter Description

dirname

The case-sensitive name of a database directory.

filename

The name of a file containing an XML template.

IN_XML

The name of a CLOB containing an XML template.


Usage Notes

You should always load a template into the same version and release of Oracle Database as the one used to generate the template. The XML may not be valid if it was generated by a different release of the software.

Example

This example reports a problem in the schema:

EXECUTE dbms_cube.validate_xml('UPGRADE_DIR', 'MYGLOBAL.XML');
BEGIN dbms_cube.validate_xml('UPGRADE_DIR', 'MYGLOBAL.XML'); END;
 
*
ERROR at line 1:
ORA-37162: OLAP error
'GLOBAL.PRICE_CUBE.$AW_ORGANIZATION': XOQ-01950: The AWCubeOrganization for
cube "GLOBAL.PRICE_CUBE" contains multiple BuildSpecifications with the same
name.
'GLOBAL.UNITS_CUBE.$AW_ORGANIZATION': XOQ-01950: The AWCubeOrganization for
cube "GLOBAL.UNITS_CUBE" contains multiple BuildSpecifications with the same
name.
XOQ-01400: invalid metadata objects
ORA-06512: at "SYS.DBMS_CUBE", line 411
ORA-06512: at "SYS.DBMS_CUBE", line 441
ORA-06512: at "SYS.DBMS_CUBE", line 501
ORA-06512: at "SYS.DBMS_CUBE", line 520
ORA-06512: at line 1

After the problems are corrected, the procedure reports no errors:

EXECUTE dbms_cube.validate_xml('UPGRADE_DIR', 'MYGLOBAL.XML');
 
PL/SQL procedure successfully completed.

This example loads an XML template into a temporary CLOB, then validates it. The script is named GLOBAL.XML, and it is located in a database directory named XML_DIR.

DEFINE xml_file = 'GLOBAL.XML';
 
SET ECHO ON;
SET SERVEROUT ON;
 
 
DECLARE
     xml_file     BFILE := bfilename('XML_DIR', '&xml_file');
     in_xml       CLOB;
     out_xml      CLOB := NULL;
     dest_offset  INTEGER := 1;
     src_offset   INTEGER := 1;
     lang_context INTEGER := 0;
     warning      INTEGER;
BEGIN
     -- Setup the clob from a file
     DBMS_LOB.CREATETEMPORARY(in_xml, TRUE);
     DBMS_LOB.OPEN(in_xml, DBMS_LOB.LOB_READWRITE);
     DBMS_LOB.OPEN(xml_file, DBMS_LOB.FILE_READONLY);
     DBMS_LOB.LOADCLOBFROMFILE(in_xml, xml_file, DBMS_LOB.LOBMAXSIZE,
        dest_offset, src_offset, 0, lang_context, warning);
  
     -- Validate the xml
     DBMS_CUBE.VALIDATE_XML(in_xml);
END;
/