36 DBMS_CUBE

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

Using DBMS_CUBE

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

The metadata for cubes and dimensions is defined in XML templates, which you can generate interactively using a graphical tool named Analytic Workspace Manager.

The IMPORT_XML procedure creates an analytic workspace with its cubes and dimensions. 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. You can also use Analytic Workspace Manager to create analytic workspaces from XML and initiate a data load. The two methods are completely interchangeable.

Summary of DBMS_CUBE Subprograms

Table 36-1 DBMS_CUBE Subprograms

Subprogram	Description
BUILD Procedure	Loads data into one or more cubes and dimensions, and prepares the data for querying.
IMPORT_XML Procedure	Creates an analytic workspace, or adds objects to an existing analytic workspace, from an XML template.
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,
       REFRESH_AFTER_ERRORS  IN  BOOLEAN         DEFAULT,
       PARALLELISM           IN  BINARY_INTEGER  DEFAULT,
       ATOMIC_REFRESH        IN  BOOLEAN         DEFAULT,
       AUTOMATIC_ORDER       IN  BOOLEAN         DEFAULT,
       ADD_DIMENSIONS        IN  BOOLEAN         DEFAULT,
       SCHEDULER_JOB         IN  VARCHAR2        DEFAULT );

Parameters

Table 36-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 partial refresh. You can specify a method for each cube in sequential order, or a single method to apply to all cubes: `C`: Complete refresh clears all dimension values before loading. (Default) `?`: Fast solve if possible, and otherwise a complete refresh. `F`: Fast refresh of a cube materialized view, which performs an incremental refresh of only changed rows in the source table. 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. (Default)
`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. (Default) FALSE enables users to access intermediate results during an build.
`AUTOMATIC_ORDER`	`TRUE` enables optimization of the build order. Dimensions are loaded before cubes.(Default) `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 build, which will appear in the log table. Each build is assigned a unique sequential number, but a text identifier is useful for tracking a particular 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 fully 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.

CLEAR [ VALUES | LEAVES | AGGREGATES ]

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.

The optional arguments control the refresh method:
- VALUES: Clears all data in the cube. This option supports the COMPLETE refresh method. (Default for the C and F methods)
- LEAVES: Clears the detail data and retains the aggregates. This option supports the FAST refresh method. (Default for the ? method)
- AGGREGATES: Clears the aggregates and retains the detail data.
LOAD [SYNCH | NO SYNCH]

Loads data into the dimension or cube. The 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.
SOLVE

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

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

Runs DBMS_AW_STATS.ANALYZE, which generates and stores optimizer statistics for cubes and dimensions.
EXECUTE OLAP DML string

Executes an OLAP DML command or program in the analytic workspace.
EXECUTE PLSQL string

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

Executes a model previously created for the cube.
AGGREGATE USING [MEASURE]

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

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

Usage Notes

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 used 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. The build will use four processes, which will appear as slave processes in the build log.

Build Log

The build log is stored as a table named CUBE_BUILD_LOG in the owner's schema. It is updated dynamically, so that you can monitor the progress of a build. Analytic Workspace Manager creates this log automatically. Otherwise, you can create the log file by running $ORACLE_HOME/olap/admin/utlolaplog.sql.

The following is an example of some columns from the default build of a cube named UNITS_CUBE.

SQL> SELECT command, status, build_object, build_object_type type 
     FROM cube_build_log WHERE build_id='1';
 
COMMAND              STATUS     BUILD_OBJECT                         TYPE
-------------------- ---------- ------------------------------------ ----------
BUILD                STARTED    BUILD GLOBAL.UNITS_CUBE              BUILD
LOAD SYNCH           STARTED    TIME                                 DIMENSION
LOAD SYNCH           COMPLETED  TIME                                 DIMENSION
COMPILE              STARTED    TIME                                 DIMENSION
COMPILE              COMPLETED  TIME                                 DIMENSION
UPDATE               STARTED    TIME                                 DIMENSION
UPDATE               COMPLETED  TIME                                 DIMENSION
COMMIT               STARTED    TIME                                 DIMENSION
COMMIT               COMPLETED  TIME                                 DIMENSION
LOAD SYNCH           STARTED    PRODUCT                              DIMENSION
LOAD SYNCH           COMPLETED  PRODUCT                              DIMENSION
COMPILE              STARTED    PRODUCT                              DIMENSION
COMPILE              COMPLETED  PRODUCT                              DIMENSION
UPDATE               STARTED    PRODUCT                              DIMENSION
UPDATE               COMPLETED  PRODUCT                              DIMENSION
COMMIT               STARTED    PRODUCT                              DIMENSION
COMMIT               COMPLETED  PRODUCT                              DIMENSION
LOAD SYNCH           STARTED    CUSTOMER                             DIMENSION
LOAD SYNCH           COMPLETED  CUSTOMER                             DIMENSION
COMPILE              STARTED    CUSTOMER                             DIMENSION
COMPILE              COMPLETED  CUSTOMER                             DIMENSION
UPDATE               STARTED    CUSTOMER                             DIMENSION
UPDATE               COMPLETED  CUSTOMER                             DIMENSION
COMMIT               STARTED    CUSTOMER                             DIMENSION
COMMIT               COMPLETED  CUSTOMER                             DIMENSION
LOAD SYNCH           STARTED    CHANNEL                              DIMENSION
LOAD SYNCH           COMPLETED  CHANNEL                              DIMENSION
COMPILE              STARTED    CHANNEL                              DIMENSION
COMPILE              COMPLETED  CHANNEL                              DIMENSION
UPDATE               STARTED    CHANNEL                              DIMENSION
UPDATE               COMPLETED  CHANNEL                              DIMENSION
COMMIT               STARTED    CHANNEL                              DIMENSION
COMMIT               COMPLETED  CHANNEL                              DIMENSION
LOAD                 STARTED    UNITS_CUBE                           CUBE
LOAD                 COMPLETED  UNITS_CUBE                           CUBE
SOLVE                STARTED    UNITS_CUBE                           CUBE
SOLVE                COMPLETED  UNITS_CUBE                           CUBE
UPDATE               STARTED    UNITS_CUBE                           CUBE
UPDATE               COMPLETED  UNITS_CUBE                           CUBE
COMMIT               STARTED    UNITS_CUBE                           CUBE
COMMIT               COMPLETED  UNITS_CUBE                           CUBE
BUILD                COMPLETED  BUILD GLOBAL.UNITS_CUBE              BUILD
 
42 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 build
      );
END;
/

IMPORT_XML Procedure

This procedure creates an analytic workspace from an XML template.

Syntax

DBMS_CUBE.IMPORT_XML
       (IN_XML                IN      CLOB;

DBMS_CUBE.IMPORT_XML
       (IN_XML                IN      CLOB
       OUT_XML                IN/OUT  CLOB );

Parameters

Table 36-4 IMPORT_XML Procedure Parameters

Parameter	Description
`IN_XML`	The name of 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 template must be loaded into a CLOB (see Example). 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.

Example

This example loads an XML template into a temporary CLOB, then creates the GLOBAL analytic workspace. 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);
  
     -- Import the xml
     DBMS_CUBE.IMPORT_XML(in_xml);
END;
/

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
       (IN_XML                IN  CLOB );

Parameters

Table 36-5 VALIDATE_XML Procedure Parameters

Parameter	Description
`IN_XML`	The name of a CLOB containing an XML template.

Usage Notes

The XML template must be loaded into a CLOB (see Example).

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