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 applicationsThis chapter contains the following topics:
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.
Table 36-1 DBMS_CUBE Subprograms
| Subprogram | Description |
|---|---|
|
Loads data into one or more cubes and dimensions, and prepares the data for querying. |
|
|
Creates an analytic workspace, or adds objects to an existing analytic workspace, from an XML template. |
|
|
Checks the XML to assure that it is valid, without committing the results to the database. |
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.
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);
Table 36-2 BUILD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
A list of cubes and dimensions and their build options (see "SCRIPT Parameter"). |
|
|
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.
See the "Usage Notes" for additional details. Methods do not apply to dimensions. |
|
|
|
|
|
Number of parallel processes to allocate to this job (see Usage Notes). |
|
|
FALSE enables users to access intermediate results during an build. |
|
|
|
|
|
|
|
|
Any text identifier for the job, which will appear in the log table. |
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 aUSING 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:
TheAGGREGATE 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)}
This clause enables you to specify different aggregation operators for different measures in the cube.
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 |
|---|---|---|
|
|
|
Adds data values, then divides the sum by the number of data values that were added together. |
|
|
|
The first real data value. |
|
|
|
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. |
|
|
|
The first data value in the hierarchy, even when that value is NA. |
|
|
|
The last data value in the hierarchy, even when that value is NA. |
|
|
|
The last real data value. |
|
|
|
The largest data value among the children of each parent. |
|
|
|
The smallest data value among the children of each parent. |
|
|
|
Adds data values. (Default) |
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.
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.
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.
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.
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;
/
This procedure creates an analytic workspace from an XML template.
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 );
Table 36-4 IMPORT_XML Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The case-sensitive name of a database directory. |
|
|
The name of a file containing an XML template. |
|
|
The name of a CLOB containing an XML template. |
|
|
An XML file generated by |
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.
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;
/
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.
DBMS_CUBE.VALIDATE_XML
(IN_XML IN CLOB );
Table 36-5 VALIDATE_XML Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of a CLOB containing an XML template. |
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.
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;
/