42 DBMS_CUBE

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,
       nested                IN  BOOLEAN         DEFAULT FALSE);
       job_class             IN  VARCHAR2        DEFAULT 'DEFAULT_JOB_CLASS'

Parameters

SCRIPT Parameter

The SCRIPT parameter identifies the objects to include in the build, and specifies the type of processing to perform 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.

The commands can be any of the following.

• AGGREGATE USING [MEASURE]

  Generates aggregate values using the syntax described in "SCRIPT Parameter: USING Clause: AGGREGATE command".

• ANALYZE

  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)

• [INSERT | MERGE] INTO [ALL HIERARCHIES | HIERARCHIES (dimension.hierarchy)] VALUES (dim_key, parent, level_name)

  Adds a dimension member to one or more hierarchies. INSERT throws an error if the member already exists, while MERGE does not. See "Dimension Maintenance Example".

  dimension.hierarchy: The name of a hierarchy the new member belongs to. Enclose each part of the name in double quotes, for example, "PRODUCT"."PRIMARY".

  dim_key: The DIM_KEY value of the dimension member.

  parent: The parent of the dimension key.

  level_name: The level of the dimension key.

• UPDATE [ALL HIERARCHIES | HIERARCHIES (dimension.hierarchy)] SET PARENT = parent, LEVEL=level_name WHERE MEMBER = dim_key

  Alters the level or parent of an existing dimension member. See INSERT for a description of the options. Also see "Dimension Maintenance Example".

• DELETE FROM DIMENSION WHERE MEMBER=dim_key

  Deletes a dimension member. See "Dimension Maintenance Example".

  dim_key: The DIM_KEY value of the dimension member to be deleted.

• SET dimension.attribute[qdr] = CAST('attribute_value' AS VARCHAR2))

  Sets the value of an attribute for a dimension member. See "Dimension Maintenance Example".

  dimension.attribute: The name of the attribute. Enclose each part of the name in double quotes, for example, "PRODUCT"."LONG_DESCRIPTION".

  qdr: The dimension member being given an attribute value in the form of a qualified data reference, such as "PRODUCT"='OPT MOUSE'.

  attribute_value: The value of the attribute, such as 'Optical Mouse'.

• FOR dimension_clause measure_clause BUILD (commands)

  Restricts the build to particular measures and dimension values, using the following arguments. See "FOR Clause Example".

  • dimension_clause:

    dimension ALL | NONE | WHERE condition | LEVELS (level [, level...])

    dimension is the name of a dimension of the cube.

    ALL sets the dimension status to all members before executing the list of commands.

    NONE loads values for no dimension members.

    WHERE loads values for those dimension members that match the condition.

    LEVELS loads values for dimension members in the named levels.

    level is a level of the named dimension.

  • measure_clause:

    MEASURES (measure [, measure...])

    measure is the name of a measure in the cube.

  • commands: Any of the other USING commands.

• LOAD [SYNCH | NO SYNCH | RETAIN] [PRUNE | PARALLEL | SERIAL] [WHERE condition]

  Loads data into the dimension or cube.

  • WHERE limits the load to those values in the mapped relational table that match condition.

  • condition is a valid predicate based on the columns of the mapped table. See the "Examples".

  These optional arguments apply only to dimensions:

  • SYNCH matches the dimension keys to the relational data source. (Default)

  • NO SYNCH loads new dimension keys but does not delete old keys.

    If the parent of a dimension key has changed in the relational data source, this option allows the load to change the parent/child relation in the analytic workspace.

  • RETAIN loads new dimension keys but does not delete old keys.

    This option does not allow the parent of a dimension key to change. If the parent has changed, the load rejects the record. The rejection generates an error in the rejected records log, if the log is enabled.

  These optional arguments apply only to cubes:

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

  • PARALLEL: The model runs separately on each partition.

  • SERIAL: The model runs on all cubes at the same time. (Default)

• SET

  Supports write-back to the cube using the syntax described in "SCRIPT Parameter: USING Clause: SET command". (Cubes only)

• SOLVE [PARALLEL | SERIAL]

  Aggregates the cube using the rules defined for the cube, including the aggregation operator and the precompute specifications. (Cubes only)

  It accepts these arguments:

  • PARALLEL: Each partition is solved separately. (Default)

  • SERIAL: All partitions are solved at the same time.

SCRIPT Parameter: USING Clause: AGGREGATE command

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

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.

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.

  • ALLOW: A calculation that generates overflow executes without error and produces null results. (Default)

  • DISALLOW: A calculation involving overflow stops executing and generates an error message.

• (ALLOW | DISALLOW) DIVISION BY ZERO

  Specifies whether to allow division by zero.

  • ALLOW: A calculation involving division by zero executes without error but returns a null value. (Default)

  • DISALLOW: A calculation involving division by zero stops executing and generates an error message.

• (CONSIDER | IGNORE) NULLS

  Specifies whether nulls are included in the calculations.

  • 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 datatype for target.

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

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. The dimensions use the complete (C) method, and the cube uses the fast solve (S) method.

BEGIN
  DBMS_CUBE.BUILD(
    script=>'GLOBAL."TIME", GLOBAL.CHANNEL, GLOBAL.UNITS_CUBE',
    method=>'CCS',
    parallelism=>2);
END;
/

The following example loads only the selection of data identified by the WHERE clause:

BEGIN
  DBMS_CUBE.BUILD(q'!
  GLOBAL."TIME", 
  GLOBAL.CHANNEL, 
  GLOBAL.CUSTOMER, 
  GLOBAL.PRODUCT, 
  GLOBAL.UNITS_CUBE USING (LOAD NO SYNCH 
     WHERE UNITS_FACT.MONTH_ID LIKE '2006%' 
     AND UNITS_FACT.SALES > 5000)!');
END;
/

FOR Clause Example

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.

BEGIN
   dbms_cube.build(q'!
   UNITS_CUBE USING
   (
   FOR "TIME" 
     WHERE HIER_ANCESTOR(WITHIN "TIME".CALENDAR LEVEL "TIME".CALENDAR_YEAR) = 'CY2006'
     BUILD (LOAD, SOLVE)
   )!', 
   parallelism=>1);
END;
/

The next example uses a FOR clause to limit the build to the SALES measure in 2006. All objects are built using the complete (C) method.

BEGIN
  DBMS_CUBE.BUILD(
  script => '
  GLOBAL."TIME", 
  GLOBAL.CHANNEL, 
  GLOBAL.CUSTOMER, 
  GLOBAL.PRODUCT, 
  GLOBAL.UNITS_CUBE USING 
  (
    FOR MEASURES(GLOBAL.UNITS_CUBE.SALES)
      BUILD(LOAD NO SYNCH WHERE GLOBAL.UNITS_FACT.MONTH_ID LIKE ''2006%'')
   )',
  method => 'C',
  parallelism => 2);
END;
/

Write-Back Examples

The following examples show various use of the SET command in a USING clause.

This example sets Sales Target to Sales increased by 5%:

DBMS_CUBE.BUILD('UNITS_CUBE USING(
   SET UNITS_CUBE.SALES_TARGET = UNITS_CUBE.SALES * 1.05, SOLVE)');
 

This example sets the price of the Deluxe Mouse in May 2007 to $29.99:

DBMS_CUBE.BUILD('PRICE_CUBE USING(
  SET PRICE_CUBE.UNIT_PRICE["TIME"=''2007.05'', "PRODUCT"=''DLX MOUSE''] 
  = 29.99, SOLVE)');

The next example contains two SET commands, but does not reaggregate the cube:

DBMS_CUBE.BUILD('PRICE_CUBE USING(
   SET PRICE_CUBE.UNIT_PRICE["TIME"=''2006.12'', "PRODUCT"=''DLX MOUSE'']
   = 29.49, 
   SET PRICE_CUBE.UNIT_PRICE["TIME"=''2007.05'', "PRODUCT"=''DLX MOUSE'']
   = 29.99)'); 

Syntax

DBMS_CUBE.CREATE_EXPORT_OPTIONS (
          out_options_xml        IN/OUT  CLOB,
          target_version         IN      VARCHAR2  DEFAULT NULL,
          suppress_owner         IN      BOOLEAN   DEFAULT FALSE,
          suppress_namespace     IN      BOOLEAN   DEFAULT FALSE,
          preserve_table_owners  IN      BOOLEAN   DEFAULT FALSE,
          metadata_changes       IN      CLOB      DEFAULT NULL);

Parameters

Examples

The following example generates an XML document of export options:

DECLARE
   optionsClob  CLOB;
 
BEGIN
   dbms_lob.createtemporary(optionsClob, false, dbms_lob.CALL); 
   dbms_cube.create_export_options(out_options_xml=>optionsClob, suppress_namespace=>TRUE);
   dbms_output.put_line(optionsClob);
END;
/

The DBMS_OUTPUT.PUT_LINE procedure displays this XML document (formatted for readability:

<?xml version="1.0"?>
<Export TargetVersion="12.1.0.1">
  <ExportOptions>
    <Option Name="SuppressOwner" Value="FALSE"/>
    <Option Name="SuppressNamespace" Value="TRUE"/>
    <Option Name="PreserveTableOwners" Value="FALSE"/>
  </ExportOptions>
</Export>

The next example generates an XML document with a metadata change to the mapping of the American long description attribute of the CHANNEL dimension.

DECLARE
  importClob         clob;
  exportClob         clob;
  overClob           clob;
  exportOptClob      clob;
  importOptClob      clob;
 
BEGIN
  dbms_lob.createtemporary(overClob, TRUE);
  dbms_lob.open(overClob, DBMS_LOB.LOB_READWRITE);
  dbms_lob.writeappend(overClob,58, '<Metadata Version="1.3" MinimumDatabaseVersion="12.1.0.1">');
  dbms_lob.writeappend(overClob,34, '<StandardDimension Name="CHANNEL">');
  dbms_lob.writeappend(overClob,75, '<Description Type="Description" Language="AMERICAN" Value="Sales Channel"/>');
  dbms_lob.writeappend(overClob,20, '</StandardDimension>');
  dbms_lob.writeappend(overClob,11, '</Metadata>');
  dbms_lob.close(overClob); 
 
  -- Enable Oracle Database 12c Release 1 (12.1) clients to access 10g metadata
  dbms_cube.initialize_cube_upgrade;
  
  -- Create a CLOB containing the export options
  dbms_lob.createtemporary(exportOptClob, TRUE);
  dbms_cube.create_export_options(out_options_xml=>exportOptClob, suppress_namespace=>TRUE, metadata_changes=>overClob);
  
  -- Create a CLOB containing the import options
  dbms_lob.createtemporary(importOptClob, TRUE);
  dbms_cube.create_import_options(out_options_xml=>importOptClob, rename_table => 'MY_OBJECT_MAP');
   
   -- Create CLOBs for the metadata
  dbms_lob.createtemporary(importClob, TRUE);
  dbms_lob.createtemporary(exportClob, TRUE);
 
  -- Export metadata from a 10g analytic workspace to a CLOB
  dbms_cube.export_xml(object_ids=>'GLOBAL_AW', options_xml=>exportOptClob, out_xml=>exportClob); 
  
  -- Import metadata from the CLOB
  dbms_cube.import_xml(in_xml => exportClob, options_xml=>importOptClob, out_xml=>importClob);
 
  -- Load and aggregate the data
  dbms_cube.build(script=>'UNITS_CUBE, PRICE_AND_COST_CUBE');
 
END;
/
 

The following is the content of exportClob (formatting added for readability). The XML document changes the description of Channel to Sales Channel.

<Metadata Version="1.3" MinimumDatabaseVersion="12.1.0.1">
  <StandardDimension Name="CHANNEL">
    <Description Type="Description" Language="AMERICAN" Value="Sales Channel"/>
  </StandardDimension>
</Metadata>

Syntax

DBMS_CUBE.CREATE_IMPORT_OPTIONS (
          out_options_xml  IN/OUT  CLOB,
          validate_only    IN      BOOLEAN   DEFAULT FALSE,
          rename_table     IN      VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

See "Rename Table".

Examples

This example specifies validation only and a rename table. For an example of the import CLOB being used in an import, see "IMPORT_XML Procedure".

DECLARE
importClob  clob;
 
BEGIN
  dbms_lob.createtemporary(importClob, TRUE);
 
  dbms_cube.create_import_options(out_options_xml => importClob, rename_table => 'MY_OBJECT_MAP', validate_only => TRUE);
  
  dbms_output.put_line(importClob);
END;
/

It generates the following XML document:

<?xml version="1.0"?>
<Import>
  <ImportOptions>
    <Option Name="ValidateOnly" Value="TRUE"/>
    <Option Name="RenameTable" Value="MY_OBJECT_MAP"/>
  </ImportOptions>
</Import>

Syntax

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

Parameters

SQL Aggregation Management Parameters

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

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:

• ADDTOPS: Adds a top level consisting of a single value to the hierarchies. All of the dimensions in Sales History have a top level already.

• PRECOMPUTE: Changes the percentage of materialized aggregates from 35:0 to 40:10.

• EXPORTXML: Creates a text file for the XML document.

• BUILD: Performs a data refresh.

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

Syntax

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

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.

Syntax

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

Parameters

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

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

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 12c, 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.

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

You can create this XML document manually or by using the CREATE_EXPORT_OPTIONS Procedure.

Usage Notes

See "Upgrading 10g Analytic Workspaces".

Example

For an example of using EXPORT_XML in an upgrade to the same schema, see "Upgrading 10g Analytic Workspaces".

The following PL/SQL script copies an OLAP 12c analytic workspace named GLOBAL12 from the GLOBAL_AW schema to the GLOBAL schema. No upgrade is performed.

To upgrade into a different schema, change the example as follows:

• Call the INITIALIZE_CUBE_UPGRADE procedure.

• Call the CREATE_EXPORT_OPTIONS procedure with the additional parameter setting SUPPRESS_NAMESPACE=>TRUE.

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.

BEGIN
  -- Create a CLOB for the export options
  dbms_lob.createtemporary(optionsClob, TRUE);
  dbms_cube.create_export_options(out_options_xml=>optionsClob, suppress_owner=>TRUE, preserve_table_owners=>TRUE);
 
  -- Create a CLOB for the XML template
  dbms_lob.createtemporary(exportClob, TRUE);
  
  -- Export metadata from an analytic workspace to a CLOB
  dbms_cube.export_xml(object_ids=>'GLOBAL_AW.GLOBAL12.AW', options_xml=>optionsClob, out_xml=>exportClob); 
 
  -- Import metadata from the CLOB
  dbms_cube.import_xml(in_xml=>exportClob);
 
  -- Load and aggregate the data
  dbms_cube.build(script=>'GLOBAL.UNITS_CUBE, GLOBAL.PRICE_AND_COST_CUBE');
 
END;
/

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

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

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

You can create this XML document manually or by using the CREATE_EXPORT_OPTIONS Procedure.

Usage Notes

See "Upgrading 10g Analytic Workspaces".

Examples

The following example generates an XML file named global.xml in OLAP 12c 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 12c 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');

Syntax

DBMS_CUBE.IMPORT_XML
       (dirname               IN      VARCHAR2,
        filename              IN      VARCHAR2 );

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

DBMS_CUBE.IMPORT_XML
       (input_dirname         IN      VARCHAR2,
        input_filename        IN      VARCHAR2 
        options_dirname       IN      VARCHAR2,
        options_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 );

DBMS_CUBE.IMPORT_XML

       (in_xml                IN      CLOB,
        options_xml           IN      CLOB,
        out_xml               IN/OUT  CLOB );

Parameters

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.

See "Upgrading 10g Analytic Workspaces".

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 exports an OLAP 10g template and uses IMPORT_XML to validate it before an upgrade to 12c.

DECLARE
 
  exportOptClob clob;
  importOptClob clob;
  importClob    clob;
  exportClob    clob;
  
BEGIN
 
  -- Create a CLOB for the export options
  dbms_lob.createtemporary(exportOptClob, TRUE);
  dbms_cube.create_export_options(out_options_xml=>exportOptClob, suppress_namespace=>TRUE, preserve_table_owners=>TRUE);
 
  -- Create a CLOB for the XML template
  dbms_lob.createtemporary(exportClob, TRUE);
 
  -- Create a CLOB for import options
  dbms_lob.createtemporary(importOptClob, TRUE);
  dbms_cube.create_import_options(out_options_xml=>importOptClob, validate_only=>TRUE);
   
  -- Create a CLOB for the change log
  dbms_lob.createtemporary(importClob, TRUE);
  
  -- Enable Oracle Database 12c Release 1 (12.1) clients to access 10g metadata
  dbms_cube.initialize_cube_upgrade;
 
  -- Export metadata from an analytic workspace to a CLOB
  dbms_cube.export_xml(object_ids=>'GLOBAL_AW', options_xml=>exportOptClob, out_xml=>exportClob); 
 
  /* Import metadata from the CLOB. No objects are committed to the database
     because the validate_only parameter of CREATE_IMPORT_OPTIONS is set to
     TRUE.
  */
 
  dbms_cube.import_xml(in_xml=>exportClob, options_xml=>importOptClob, out_xml=>importClob);
 
   -- Output the metadata changes
  dbms_output.put_line('This is the validation log:');
  dbms_output.put_line(importClob);
  
END;
/

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>

Syntax

DBMS_CUBE.INITIALIZE_CUBE_UPGRADE;

Usage Notes

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

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.

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

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 0,
          atomic_refresh       IN  BOOLEAN        DEFAULT FALSE,
          scheduler_job        IN  VARCHAR2       DEFAULT NULL,
          sam_parameters       IN  CLOB           DEFAULT NULL,
          nested               IN  BOOLEAN        DEFAULT FALSE );

Parameters

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 20130212 15:04:46.370.

Syntax

DBMS_CUBE.UPGRADE_AW
       (sourceaw              IN  VARCHAR2,
        destaw                IN  VARCHAR2,
        upgoptions            IN  CLOB DEFAULT NULL); 

Parameters

Examples

This example upgrades an OLAP 10g analytic workspace named GLOBAL10 to an OLAP 12c analytic workspace named GLOBAL12, using a rename table named MY_OBJECT_MAP:

BEGIN 
 
  -- Upgrade the analytic workspace
  dbms_cube.upgrade_aw(sourceaw =>'GLOBAL10', destaw => 'GLOBAL12', upgoptions => 'RENAME_TABLE=MY_OBJECT_MAP');
  
  -- Load and aggregate the data
  dbms_cube.build(script=>'UNITS_CUBE, PRICE_AND_COST_CUBE');
   
END;
/

Syntax

DBMS_CUBE.VALIDATE_XML
       (dirname               IN  VARCHAR2,
        filename              IN  VARCHAR2 ); 

DBMS_CUBE.VALIDATE_XML
       (in_xml                IN  CLOB ); 

Parameters

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