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

B28419-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

36 DBMS_CUBE

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

See Also:

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

This chapter contains the following topics:


Using DBMS_CUBE

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

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 fast (partial) refresh. In a fast refresh, only changed rows are inserted in the cube and the affected areas of the cube are re-aggregated.

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

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

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

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

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

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

See the "Usage Notes" for additional details.

Methods do not apply to dimensions.

REFRESH_AFTER_ERRORS

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

FALSE to roll back all objects in the build.

PARALLELISM

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

ATOMIC_REFRESH

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

FALSE enables users to access intermediate results during an build.

AUTOMATIC_ORDER

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

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

ADD_DIMENSIONS

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

FALSE includes only dimensions specifically listed in the script.

SCHEDULER_JOB

Any text identifier for the job, which will appear in the log table.


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.

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:

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 used by a build is controlled by the smallest of these factors:

Suppose UNITS_CUBE has 12 partitions, PARALLELISM is set to 10, and JOB_QUEUE_PROCESSES is set to 4. 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
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
 
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 job
      );
END;
/

IMPORT_XML Procedure

This procedure creates an analytic workspace from an XML template.

Syntax

DBMS_CUBE.IMPORT_XML
       (DIRNAME               IN      VARCHAR2,
        FILENAME              IN      VARCHAR2;

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

DIRNAME

The case-sensitive name of a database directory.

FILENAME

The name of a file containing an XML template.

IN_XML

The name of a CLOB containing an XML template.

OUT_XML

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


Usage Notes

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

Example

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

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

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

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

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