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:

OLAP_USER role

CREATE SESSION privilege

To create dimensional objects in different schemas:

OLAP_DBA role

CREATE SESSION privilege

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

CREATE MATERIALIZED VIEW privilege

CREATE DIMENSION privilege

ADVISOR privilege

To create cube materialized views in different schemas:

CREATE ANY MATERIALIZED VIEW privilege

CREATE ANY DIMENSION privilege

ADVISOR privilege

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.

Subprograms 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:

Explicit GROUP BY clause for one or more columns.

No expressions in the select list or GROUP BY clause.

At least one of these numeric aggregation methods: SUM, MIN, MAX, or AVG.

No outer joins.

Summary keys with at least one simple column associated with a relational dimension.

or

Summary keys with at least one simple column and no hierarchies or levels.

Numeric data type of any type for the fact columns. All facts are converted to NUMBER.

Eligible for rewrite. REWRITE_CAPABILITY should be GENERAL; it cannot be NONE. Refer to the ALL_MVIEWS entry in the Oracle Database Reference.

Cannot use the DISTINCT or UNIQUE keywords with an aggregate function in the defining query. For example, AVG(DISTINCT units) causes an error in STRICT mode and is ignored in LOOSE mode.

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:

Complex expressions in the defining query are ignored and do not appear in the cube materialized view.

The AVG function is changed to SUM and COUNT.

The COUNT function without a SUM, MIN, MAX, or AVG function is ignored.

The STDDEV and VARIANCE functions are ignored.

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:

CREATE [ANY] MATERIALIZED VIEW privilege

CREATE [ANY] DIMENSION privilege

ADVISOR privilege

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

GLOBAL QUERY REWRITE privilege

SELECT privilege on the relational source tables

SELECT privilege on the analytic workspace (AW$name) that supports the cube materialized view

SELECT privilege on the cube

SELECT privilege on the dimensions of the cube

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.

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.

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.

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

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;

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:

Analytic workspace CAL_MONTH_SALES_AW (AW$CAL_MONTH_SALES_AW table)

Cube CAL_MONTH_SALES

Cube dimension TIMES_DIM_D1

Dimension hierarchy CAL_ROLLUP

Dimension levels ALL_TIMES_DIM, YEAR, QUARTER, and MONTH

Numerous attributes for levels in the CAL_ROLLUP hierarchy

Upgrading Analytic Workspaces 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 using it to create a new analytic workspace. The original analytic workspace remains accessible and unchanged by the upgrade process.

The OLAP 10g analytic workspace can use CWM metadata or OLAP standard form (AWXML) metadata.

Customizations to the OLAP 10g analytic workspace may not be exported to the XML template. You must re-create them in OLAP 11g.

The original relational source data must be available to load into the new analytic workspace. If the data is in a different schema or the table names are different, then you must remap the dimensional objects to the new relational sources after the upgrade.

You can create the OLAP 11g analytic workspace in the same schema as the OLAP 10g analytic workspace. However, if you prefer to create it in a different schema, then create a new user with the following privileges:

Correcting Naming Conflicts

The namespaces are different in OLAP 11g and OLAP 10g. For a successful upgrade, you must identify any 10g object names that are used multiple times under the 11g naming rules and provide unique names for them.

The following namespaces control the uniqueness of OLAP object names in Oracle 11g:

Schema: The names of cubes, dimensions, and measure folders must be unique within a schema. They cannot conflict with the names of tables, views, indexes, relational dimensions, or any other first class objects. However, these OLAP 11g object names do not need to be distinct from 10g object names, because they are in different namespaces.

Cube: The names of measures must be unique within a cube.

Dimension: The names of hierarchies, levels, and attributes must be unique within a dimension. For example, a dimension cannot have a hierarchy named Customers and a level named Customers.

You can use an initialization table and a rename table to rename objects in the upgraded 11g analytic workspace.

Initialization Table

The INITIALIZE_CUBE_UPGRADE procedure identifies ambiguous names under the OLAP 11g naming rules. For example, a 10g dimension might have a hierarchy and a level with the same name. Because hierarchies and levels are in the same 11g namespace, the name is not unique in 11g; to an 11g client, the hierarchy and the level cannot be differentiated by name.

INITIALIZE_CUBE_UPGRADE creates and populates a table named CUBE_UPGRADE_INFO with unique names for these levels, hierarchies, and attributes. By using the unique names provided in the table, an 11g client can browse the OLAP 11g metadata. You cannot attach an OLAP 11g client to the analytic workspace or perform an upgrade without a CUBE_UPGRADE_INFO table, if the 10g metadata contains ambiguous names.

You can edit CUBE_UPGRADE_INFO to change the default unique names to names of your choosing. You can also add rows to change the names of any other objects. When using an 11g client, you see the new object names. When using an 10g client, you see the original names. However, the INITIALIZE_CUBE_UPGRADE procedure overwrites this table, so you may prefer to enter customizations in a rename table.

During an upgrade from OLAP 10g, the unique object names in CUBE_UPGRADE_INFO are used as the names of 11g objects in the new analytic workspace. However, INITIALIZE_CUBE_UPGRADE does not automatically provide unique names for cubes, dimensions, and measure folders. To complete an upgrade, you must assure that these objects have unique names within the 11g namespace. You can provide these objects with new names in the CUBE_UPGRADE_INFO table or in a rename table.

OLAP 11g clients automatically use CUBE_UPGRADE_INFO when it exists in the same schema as the OLAP 10g analytic workspace.

Rename Table

You can create a rename table that contains new object names for an OLAP 11g analytic workspace. You can then use the rename table in the CREATE_IMPORT_OPTIONS and UPGRADE_AW procedures.

When upgrading within the same schema, you must provide a unique name for the 11g analytic workspace. The UPGRADE_AW procedure provides a parameter for this purpose; otherwise, you must provide the new name in the rename table. The duplication of cube names does not create ambiguity because the 11g cubes are created in a different namespace than the 10g cubes.

The names provided in a rename table are used only during an upgrade and overwrite any names entered in the CUBE_UPGRADE_INFO table.

To create a rename table:

Open SQL*Plus or another SQL client, and connect to Oracle Database as the owner of the 10g analytic workspace.

This example creates an OLAP 11g analytic workspace named GLOBAL11 from an OLAP 10g analytic workspace named GLOBAL10. GLOBAL10 contains no naming conflicts between cubes, dimensions, measure folders, or tables in the schema, so a rename table is not needed in this example.

Custom Upgrade

Open SQL*Plus or a similar SQL command-line interface and connect to Oracle Database 11g as the schema owner of the OLAP 11g analytic workspace.

Generate an initialization table, as described in "Initialization Table". Review the new, default object names and modify them as desired.

Create a rename table, as described in "Rename Table". If you are upgrading in the same schema, you must use a rename table to provide a unique name for the 11g analytic workspace. Otherwise, a rename table is needed only if names are duplicated among the cubes, dimensions, and measure folders of the analytic workspace, or between those names and the existing cubes, dimensions, measure folders, or tables of the destination schema.

Create a SQL script that does the following:

Create an XML document for the export options, as described in "CREATE_EXPORT_OPTIONS Procedure". The SUPPRESS_NAMESPACE option must be set to TRUE for the upgrade to occur.

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 and dimension in sequential order, or a single method to apply to all cubes and dimensions. If you list more objects than methods, then the last method applies to the additional objects.

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.

nested

TRUE performs nested refresh operations for the specified set of cube materialized views. Nested refresh operations refresh all the depending materialized views and the specified set of materialized views based on a dependency order to ensure the nested materialized views are truly fresh with respect to the underlying base tables.

All objects must reside in a single analytic workspace.

job_class

The class this job is associated with.

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:

[VALIDATE | NO COMMIT] objects [ USING ( commands ) ][,...]

Where:

VALIDATE checks all steps of the build and sends the planned steps to CUBE_BUILD_LOG without executing the steps. You can view all generated SQL in the OUTPUT column of the log table.

NO COMMIT builds the objects in the current attach mode (or Read Only when the analytic workspace is not attached) but does not commit the changes. This option supports what-if analysis, since it enables you to change data values temporarily. See "SCRIPT Parameter: USING Clause: SET command".

objects is the qualified name of one or more cubes or dimensions, separated by commas, in the form [aw_name.]object, such as UNITS_CUBE or GLOBAL.UNITS_CUBE.

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.

Runs DBMS_AW_STATS.ANALYZE, which generates and stores optimizer statistics for cubes and dimensions.

CLEAR [VALUES | LEAVES | AGGREGATES] [SERIAL | PARALLEL]

Prepares the cube for a data refresh. It can also be used on dimensions, but CLEAR removes all dimension keys, and thus deletes all data values for cubes that use the dimension.

These optional arguments control the refresh method. If you omit the argument, then the behavior of CLEAR depends on the refresh method. The 'C' (complete) refresh method runs CLEAR VALUES, and all other refresh methods run CLEAR LEAVES.

VALUES: Clears all data in the cube. All facts must be reloaded and all aggregates must be recomputed. This option supports the COMPLETE refresh method. (Default for the C and F methods)

LEAVES: Clears the detail data and retains the aggregates. All facts must be reloaded, and the aggregates for any new or changed facts must be computed. This option supports the FAST refresh method. (Default for the ? method)

AGGREGATES: Retains the detail data and clears the aggregates. All aggregates must be recomputed.

These optional arguments control the load method, and can be combined with any of the refresh options:

PARALLEL: Each partition is cleared separately. (Default)

SERIAL: All partitions are cleared together.

If you omit the CLEAR command, DBMS_CUBE loads new and updated facts, but does not delete any old detail data. This is equivalent to a LOAD NO SYNC for dimensions.

COMPILE [SORT | NO SORT | SORT ONLY]

Creates the supporting structures for the dimension. (Dimensions only)

These options control the use of a sort order attribute:

SORT: The user-defined sort order attribute populates the sort column in the embedded-total (ET) view. (Default)

NO SORT: Any sort order attribute is ignored. This option is for very large dimensions where sorting could consume too many resources.

SORT ONLY: The compile step only runs the sort.

EXECUTE PLSQL string

Executes a PL/SQL command or script in the database.

EXECUTE OLAP DML string [PARALLEL | SERIAL]

Executes an OLAP DML command or program in the analytic workspace. The options control execution of the command or program:

PARALLEL: Execute the command or program once for each partition. This option can be used to provide a performance boost to complex DML operations, such as forecasts and models.

SERIAL: Execute the command or program once for the entire cube. (Default)

PRUNE: Runs a full table scan on the fact table to determine which partitions to load. For example, if a cube is partitioned by month and the fact table has values only for the last two months, then jobs are only started to load the partitions for the last two months.

PARALLEL: Each partition is loaded separately. (Default)

SERIAL: All partitions are loaded in one SELECT statement.

MODEL model_name [PARALLEL | SERIAL]

Executes a model previously created for the cube. It accepts these arguments:

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:

(ALLOW | DISALLOW) OVERFLOW

Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numerical representation.

CONSIDER: Nulls are included in the calculations. A calculation that includes a null value returns a null value.

IGNORE: Only actual data values are used in calculations. Nulls are treated as if they do not exist. (Default)

MAINTAIN COUNT

Stores an up-to-date count of the number of dimension members for use in calculating averages. Omit this option to count the members on the fly.

SCRIPT Parameter: USING Clause: SET command

The SET command in a script assigns values to one or more cells in a stored measure. It has this syntax:

SET target = expression

Where:

target is a a measure or a qualified data reference.

expression returns values of the appropriate data type for target.

Qualified Data References

Qualified data references (QDRs) limit a dimensional object to a single member in one or more dimensions for the duration of a query.

A QDR has the following syntax:

expression [ { dimension = member }[ , { dimension = member } ...] ]

Where:

expression is a dimensional expression, typically the name of a measure.

dimension is a primary dimension of expression.

member is a value of dimension.

The outside square brackets shown in bold are literal syntax elements; they do not indicate an optional argument. The inside square brackets shown in regular text delimit an optional argument and are not syntax elements.

This example returns Sales values for calendar year 2007:

global.sales[global.time = 'CY2007'
]

The next example returns Sales values only for the United States in calendar year 2007:

sales[customer = 'US', time = 'CY2007'
]

See the Examples for qualified data references in SET commands.

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:

Number of cubes in the build and the number of partitions in each cube.

Setting of the PARALLELISM argument of the BUILD procedure.

Setting of the JOB_QUEUE_PROCESSES database initialization parameter.

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:

Cube build log

Rejected values log

Cube dimension compile log

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:

CUBE_BUILD_LATEST: Returns rows only from the last build.

CUBE_BUILD_REPORT: Returns one row for each command with elapsed times.

CUBE_BUILD_REPORT_LATEST: Returns a report like CUBE_BUILD_REPORT only from the last build.

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

In this example, the Time dimension is partitioned by calendar year, and DBMS_CUBE builds only the partition identified by CY2006. The HIER_ANCESTOR is an analytic function in the OLAP expression syntax.

This script shows dimension maintenance. It adds a new dimension member named OPT MOUSE to all hierarchies, alters its position in the Primary hierarchy, assigns it a long description, then deletes it from the dimension.

Contains the generated XML document, which can be passed into the options_xml parameter of the EXPORT_XML Procedure.

target_version

Specifies the version of Oracle Database in which the XML document generated by EXPORT_XML or EXPORT_XML_TO_FILE will be imported. You can specify two to five digits, such as 11.2 or 11.2.0.2.0. This parameter defaults to the current database version, and so can typically be omitted.

suppress_owner

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.

suppress_namespace

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.

Namespaces allow objects created in Oracle 10g to coexist with objects created in Oracle 11g. You cannot set or change namespaces.

preserve_table_owners

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.

metadata_changes

Contains an 11g XML description of an object that overwrites the exported object description. The XML document must contain all parent XML elements of the modified element with the attributes needed to uniquely identify them. Use the Name attribute if it exists. See the Examples.

Contains the generated XML document, which can be passed to the options_xml parameter of the IMPORT_XML Procedure.

validate_only

TRUE causes the IMPORT_XML procedure to validate the metadata described in the input file or the in_xml parameter, without committing the changes to the metadata.

rename_table

The name of a table identifying new names for the imported objects, in the form [schema_name.]table_name. The IMPORT_XML procedure creates objects using the names specified in the table instead of the ones specified in the XML document. See the Usage Notes for the format of the rename table.

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.

The CREATE_MVIEW and DERIVE_FROM_MVIEW functions use the SQL aggregation management (SAM) parameters described in Table 41-7. 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-7 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)

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)

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)

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.

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.

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.

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.

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.

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

Export Options

The default settings for the export options are appropriate in many cases, so you can omit the options_xml parameter or the options_dirname and options_filename parameters. However, when upgrading Oracle OLAP 10g metadata to OLAP 11g, you must specify an XML document that changes the default settings. This example changes all of the parameters from False to True; set them appropriately for your schema.

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-11 EXPORT_XML_TO_FILE Procedure Parameters

Parameter

Description

object_ids

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

Cube

Dimension

Named build process

Measure folder

You can specify multiple objects by separating the names with commas.

Note: When exporting an individual object, be sure to export any objects required to reconstruct it. For example, when you export a cube, you must also export the dimensions of the cube.

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 specify an XML document that changes the default settings, like the following:

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.

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.

An XML document that either describes the analytic workspace or, for validation only, describes any errors. It may contain changes that 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, modify, or drop an entire analytic workspace, or one or more cubes or dimensions. When defining just cubes or dimensions, you must do so within 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 with the EXPORTXML parameter.

The contents of importClob show that the XML is valid. Otherwise, error messages appear in the <RootCommitResult> element.

This is the validation log:
<?xml version="1.0" encoding="UTF-16"?>
<RootCommitResult>
</RootCommitResult>

For an example of IMPORT_XML within the context of an upgrade from 10g to 11g metadata, see "Custom Upgrade".

INITIALIZE_CUBE_UPGRADE Procedure

This procedure processes analytic workspaces created in Oracle OLAP 10g so they can be used by Oracle OLAP 11g clients. It processes all analytic workspaces in the current schema. Run this procedure once for each schema in which there are 10g analytic workspaces.

Without this processing step, 11g clients cannot connect to a database containing a 10g analytic workspace with subobjects of a dimension or cube having the same name. Additionally, some DBMS_CUBE procedures and functions, such as EXPORT_XML and EXPORT_XML_TO_FILE, do not work on the 10g metadata.

After processing, OLAP 11g clients can connect and use the alternate names provided by INITIALIZE_CUBE_UPGRADE for the conflicting subobjects. OLAP 10g clients continue to use the original names.

INITIALIZE_CUBE_UPGRADE does not upgrade any OLAP 10g objects to OLAP 11g format.

This procedure creates and populates a table named CUBE_UPGRADE_INFO. If it already exists, the table is truncated and repopulated.

While the 10g namespace allowed subobjects with the same name in the same dimension or cube, the 11g namespace does not. 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 to customize the names of OLAP 10g objects on OLAP 11g clients.

The UPGRADE_AW, 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.

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 of CONFLICT is 0.

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.

nested

TRUE performs nested refresh operations for the specified set of cube materialized views. Nested refresh operations refresh all the depending materialized views and the specified set of materialized views based on a dependency order to ensure the nested materialized views are truly fresh with respect to the underlying base tables.

All objects must reside in a single analytic workspace.

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.

UPGRADE_AW Procedure

This procedure creates an Oracle OLAP 11g analytic workspace from a copy of the metadata contained in an OLAP 10g analytic workspace. The original OLAP 10g analytic workspace is not affected and can exist at the same time and in the same schema as the OLAP 11g analytic workspace.

UPGRADE_AW automatically runs INITIALIZE_CUBE_UPGRADE if the CUBE_UPGRADE_INFO table does not exist. If it does exist, then UPGRADE_AW does not overwrite it, thus preserving any changes you made to the table.

A new name for the generated 11g analytic workspace. It cannot be the same as sourceaw.

upgoptions

One or more of these upgrade options, as a string in the form 'OPTION=VALUE'. Separate multiple options with commas.

PRESERVE_TABLE_OWNERS:

YES preserves the original source table mappings. Use this option when creating an OLAP 11g analytic workspace in a different schema from the 10g analytic workspace, and you want the new objects mapped to tables in the original schema. (Default)

NO removes the schema owner from the source table mappings. Use this option when creating an OLAP 11g analytic workspace in a different schema from the 10g analytic workspace, and you want the new objects mapped to tables in the destination schema.

RENAME_TABLE: The name of a table that specifies new names for objects as they are created in OLAP 11g format. These changes are in addition to those specified by the INITIALIZE_CUBE_UPGRADE procedure. See "CREATE_IMPORT_OPTIONS Procedure" for information about creating a rename table.

Examples

This example upgrades an OLAP 10g analytic workspace named GLOBAL10 to an OLAP 11g analytic workspace named GLOBAL11, using a rename table named MY_OBJECT_MAP:

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