Oracle DBMS_CUBE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Creates and maintains analytic cubes
AUTHID CURRENT_USER
Data Types TYPE id_array ... definition TBD
Dependencies
ALL_AWS ALL_PART_TABLES DBMS_UTILITY
ALL_CUBES ALL_TABLES DBMS_XMLGEN
ALL_CUBE_DIMENSIONALITY ALL_TAB_COLS NLS_DATABASE_PARAMETERS
ALL_CUBE_DIMENSIONS DBMS_ADVISOR DUAL
ALL_DIM_ATTRIBUTES DBMS_ASSERT PLITBLM
ALL_DIM_CHILD_OF DBMS_AW SAM_MEASURE_SPECS_T
ALL_DIM_HIERARCHIES DBMS_AW$_COLUMNLIST_T SAM_MEASURE_SPEC_R
ALL_DIM_JOIN_KEY DBMS_AW$_DIMENSION_SOURCES_T SAM_RELATION_SPECS_T
ALL_DIM_LEVELS DBMS_AW_LIB SAM_RELATION_SPEC_R
ALL_DIM_LEVEL_KEY DBMS_CUBE_ADVISE_SEC SAM_SPARSITY_ADVICE
ALL_DIRECTORIES DBMS_CUBE_UTIL USER_INDEXES
ALL_MVIEWS DBMS_LOB USER_IND_COLUMNS
ALL_MVIEW_AGGREGATES DBMS_OLAPI_LIB USER_TABLES
ALL_MVIEW_DETAIL_RELATIONS DBMS_OUTPUT USER_TAB_COLUMNS
ALL_MVIEW_JOINS DBMS_STANDARD UTL_FILE
ALL_MVIEW_KEYS DBMS_STATS_INTERNAL UTL_I18N
ALL_OBJECTS    
Documented Yes
First Available 11.1.0.6
Security Model Owned by SYS with EXECUTE granted to PUBLIC

To create a cube materialized view requires:
CREATE [ANY] MATERIALIZED VIEW, CREATE [ANY] DIMENSION, and ADVISOR

To access cube materialized views from another schema using query rewrite, you must
have these privileges:
GLOBAL QUERY REWRITE, SELECT on the elational source tables, SELECT on the analytic workspace (AW$name) that supports the cube materialized view, SELECT on the cube, and SELECT on the cube's dimensions
Source {ORACLE_HOME}/olap/admin/olapiboo.plb
Subprograms
 
BUILD
Loads data into one or more cubes and dimensions, and prepares the data for querying

This procedure is very poorly named as it builds nothing ... rather it loads data.
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,
rebuild_freepools    IN BOOLEAN,
nested               IN BOOLEAN,
job_class            IN VARCHAR2       DEFAULT NULL);

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

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

 Methods
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.
BEGIN
  dbms_cube.build('GLOBAL.TIME USING (LOAD NO SYNCH, COMPILE), GLOBAL.CHANNEL, GLOBAL.UNITS_CUBE USING (CLEAR LEAVES, LOAD, SOLVE, ANALYZE)', '?', FALSE, 2, FALSE, TRUE, FALSE, 'Units Cube');
END;
/
 
BUILD_SLAVE
Undocumented dbms_cube.build_slave(
script             IN VARCHAR2,
partition_member   IN VARCHAR2,
scheduler_job      IN VARCHAR2
master_build_id    IN BINARY_INTEGER,
job_class          IN VARCHAR2,
slave_build_number IN BINARY_INTEGER);
TBD
 
CREATE_CUBE_DIM_REFRESH_MVIEWS (new 12.1)
Undocumented dbms_cube.create_cube_dim_refresh_mviews(
schema_name              IN VARCHAR2,
cube_dim_name            IN VARCHAR2,
using_constraints_clause IN VARCHAR2);
TBD
 
CREATE_CUBE_REFRESH_MVIEW (new 12.1)
Undocumented dbms_cube.create_cube_refresh_mview(
schema_name              IN VARCHAR2,
cube_name                IN VARCHAR2,
refresh_type             IN VARCHAR2,
using_constraints_clause IN VARCHAR2);
TBD
 
CREATE_CUBE_REWRITE_MVIEW (new 12.1)
Undocumented dbms_cube.create_cube_rewrite_mview(
schema_name              IN VARCHAR2,
cube_name                IN VARCHAR2,
rewrite_type             IN VARCHAR2,
refresh_type             IN VARCHAR2,
using_constraints_clause IN VARCHAR2);
TBD
 
CREATE_EXPORT_OPTIONS
Undocumented dbms_cube.create_export_options(
out_options_xml       IN OUT CLOB,
target_version        IN     VARCHAR2,
suppress_owner        IN     BOOLEAN,
suppress_namespace    IN     BOOLEAN,
preserve_table_owners IN     BOOLEAN,
metadata_changes      IN     CLOB);
TBD
 
CREATE_IMPORT_OPTIONS
Undocumented dbms_cube.create_export_options(
out_options_xml IN OUT CLOB,
validate_onlyr  IN     BOOLEAN,
rename_table    IN     VARCHAR2);
TBD
 
CREATE_MVIEW
Creates a cube materialized view from the definition of a relational materialized view dbms_cube.create_mview(
mvowner        IN VARCHAR2,
mvname         IN VARCHAR2,
sam_parameters IN CLOB DEFAULT NULL)
RETURN VARCHAR2;
conn sysp@pdbdev as sysdba

grant create cube to sh;
grant create dimension, to sh;
grant create any cube dimension to sh;

conn sh/sh@pdbdev

desc cal_month_sales_mv

set serveroutput on

DECLARE
 salesaw VARCHAR2(30);
BEGIN
  salesaw := dbms_cube.create_mview('SH', 'CAL_MONTH_SALES_MV');
  dbms_output.put_line(salesaw);
END;
/

SELECT object_type, COUNT(*)
FROM user_objects
WHERE created > SYSDATE-15/1440
GROUP BY object_type
ORDER BY 1;

SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-15/1440
ORDER BY 2,1;

desc CB$CAL_MONTH_SALES

SELECT COUNT(*) FROM CB$CAL_MONTH_SALES;

desc CB$TIMES_DIM_D1_CAL_ROLLUP

SELECT COUNT(*) FROM CB$TIMES_DIM_D1_CAL_ROLLUP;

desc CR$CAL_MONTH_SALES

SELECT COUNT(*) FROM CR$CAL_MONTH_SALES;

SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-10/1440
AND object_type = 'CUBE'
ORDER BY 2,1;

SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-10/1440
AND object_type = 'CUBE DIMENSION'
ORDER BY 2,1;

desc user_cube_dimensions

SELECT * FROM user_cube_dimensions;

BEGIN
  dbms_cube.build('CAL_MONTH_SALES');
END;
/

SELECT COUNT(*) FROM CB$CAL_MONTH_SALES;
SELECT COUNT(*) FROM CB$TIMES_DIM_D1_CAL_ROLLUP;
SELECT COUNT(*) FROM CR$CAL_MONTH_SALES;

ALTER MATERIALIZED VIEW cal_month_sales_mv DISABLE QUERY REWRITE;

EXPLAIN PLAN FOR
SELECT t.calendar_quarter_desc,
sum(s.amount_sold) AS dollars
FROM sales s,
times t
WHERE s.time_id = t.time_id
AND t.calendar_quarter_desc LIKE '2001%'
GROUP BY t.calendar_quarter_desc
ORDER BY t.calendar_quarter_desc;

SELECT * FROM TABLE(dbms_xplan.display);

exec dbms_cube.drop_mview('SH', 'CB$CAL_MONTH_SALES');

SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-1
ORDER BY 2,1;

PURGE RECYCLEBIN;

SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-1
ORDER BY 2,1;

ALTER MATERIALIZED VIEW cal_month_sales_mv ENABLE QUERY REWRITE;
Another demo set serveroutput on

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);
  dbms_output.put_line(salescubmv);
END;
/
 
DERIVE_FROM_MVIEW
Creates an XML template for a cube materialized view from the definition of a relational materialized view dbms_cube.derive_from_mview(
mvowner        IN VARCHAR2,
mvname         IN VARCHAR2,
sam_parameters IN CLOB DEFAULT NULL)
RETURN CLOB;
set serveroutput on

DECLARE
 salescubexml CLOB := NULL;
 cmv_xml      CLOB := 'exportXML=CTEMP/sales_cube.xml';
BEGIN
  salescubexml := dbms_cube.derive_from_mview('SH', 'CAL_MONTH_SALES_MV', cmv_xml);
  dbms_output.put_line(salescubexml);
END;
/
 
DRILL_THROUGH (new 12.1)
Undocumented dbms_cube.drill_through(
qdr IN     IN VARCHAR2,
et_columns IN dbms_cube.id_array,
language   IN VARCHAR2,
schema     IN VARCHAR2)
RETURN REF CURSOR;
TBD
 
DROP_MVIEW
Drops a cube materialized view dbms_cube.drop_mview(
mvowner        IN VARCHAR2,
mvname         IN VARCHAR2,
sam_parameters IN CLOB DEFAULT NULL);
exec dbms_cube.drop_mview('SH', 'CB$CAL_MONTH_SALES');

PURGE RECYCLEBIN;
 
EXPORT_XML (new 12.1)
Exports the XML of an analytic workspace to a CLOB

Overload 1
dbms_cube.export_xml(
object_ids IN     VARCHAR2,
out_xml    IN OUT CLOB);
TBD
Overload 2 dbms_cube.export_xml(
object_ids  IN     VARCHAR2,
options_xml IN     CLOB,
out_xml     IN OUT CLOB);
TBD
Overload 3 dbms_cube.export_xml(
object_ids       IN     VARCHAR2,
options_dirname  IN     VARCHAR2,
options_filename IN     VARCHAR2,
out_xml          IN OUT CLOB);
TBD
 
EXPORT_XML_TO_FILE
Undocumented
Overload 1
dbms_cube.export_xml(object_ids IN VARCHAR2, out_xml IN OUT CLOB);
TBD
Overload 2 dbms_cube.export_xml(
object_ids  IN     VARCHAR2,
options_xml IN     CLOB,
out_xml     IN OUT CLOB);
TBD
Overload 3 dbms_cube.export_xml(
object_ids       IN     VARCHAR2,
options_dirname  IN     VARCHAR2,
options_filename IN     VARCHAR2,
out_xml          IN OUT CLOB);
TBD
 
GET_BREAKOUT_DIMENSIONS (new 12.1)
Undocumented dbms_cube.get_breakout_dimensions(
dimension IN VARCHAR2,
cube      IN VARCHAR2,
schema    IN VARCHAR2)
RETURN dbms_cube.id_array;
TBD
 
IMPORT_XML
Creates, modifies, or drops an analytic workspace by using an XML template

Overload 1
dbms_cube.import_xml(in_xml IN CLOB);
DECLARE
 xml_file     BFILE := bfilename('CTEMP', 'sales_cube.xml');
 in_xml       CLOB;
 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 xml
  dbms_cube.import_xml(in_xml);
END;
/
Overload 2 dbms_cube.import_xml(in_xml IN CLOB, out_xml IN OUT CLOB);
set serveroutput on

DECLARE
 xml_file     BFILE := bfilename('CTEMP', 'sales_cube.xml');
 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 xml
  dbms_cube.import_xml(in_xml, out_xml);
  dbms_output.put_line(out_xml);
END;
/
Overload 3 dbms_cube.import_xml(dirname IN VARCHAR2, filename IN VARCHAR2);
BEGIN
  dbms_cube.import_xml('CTEMP', 'sales_cube.xml');
END;
/
Overload 4 dbms_cube.import_xml(dirname IN VARCHAR2, filename IN VARCHAR2, out_xml IN OUT CLOB);
TBD
Overload 5 dbms_cube.import_xml(
in_xml      IN     CLOB,
options_xml IN     CLOB,
out_xml     IN OUT CLOB);
TBD
Overload 6 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);
TBD
 
INITIALIZE_CUBE_UPGRADE (new 12.1)
Processes analytic workspaces created in Oracle OLAP 10g so they can be used by Oracle OLAP 12c clients. Processes all analytic workspaces in the current schema. dbms_cube.initialize_cube_upgrade;
exec dbms_cube.initialize_cube_upgrade;
 
IS_DRILL_THROUGH_ALLOWED (new 12.1)
Returns TRUE if drill through is enabled dbms_cube.is_drill_through_allowed(
cube   IN VARCHAR2,
schema IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_cube.is_drill_through_allowed('UWCUBE', 'UWCLASS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
REFRESH_MVIEW
Refreshes a cube materialized view 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 NULL,
atomic_refresh       IN BOOLEAN        DEFAULT FALSE,
scheduler_job        IN VARCHAR2       DEFAULT NULL,
sam_parameters       IN CLOB           DEFAULT NULL,
nested               IN BOOLEAN);

-- see build procedure for method parameters
conn sh/sh@pdbdev

set serveroutput on

-- use the default settings to refresh a cube materialized view
exec dbms_cube.refresh_mview('SH', 'CB$FWEEK_PSCAT_SALES');

-- changes the refresh method to use fast refresh if possible, continue
-- refreshing after an error, and use two parallel processes

exec dbms_cube.refresh_mview('SH', 'CB$FWEEK_PSCAT_SALES', '?', TRUE, 2);
 
UPGRADE_AW
Undocumented dbms_cube.upgrae_aw(
sourceaw   IN VARCHAR2,
destaw     IN VARCHAR2,
upgoptions IN CLOB);
TBD
 
VALIDATE_XML
Checks the XML to assure that it is valid, without committing the results to the database

Overload 1
dbms_cube.validate_xml(in_xml IN CLOB);
sh/sh@pdbdev

DECLARE
 xml_file     BFILE := bfilename('STAGE', 'sales_cube.xml');
 in_xml       CLOB;
 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);

  dbms_lob.close(in_xml);
  dbms_lob.close(xml_file);
END;
/
Overload 2 dbms_cube.validate_xml(dirname IN VARCHAR2, filename IN VARCHAR2);
exec dbms_cube.validate_xml('STAGE', 'sales_cube.xml');

Related Topics
DBMS_AW
DBMS_AW_STATS
DBMS_CUBE_LOG
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved