| General Information |
| Description |
Creates and maintains analytic cubes |
| Source |
{ORACLE_HOME}/olap/admin/olapiboo.plb |
| First Available |
11.1.0.6 |
| Dependencies |
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_CUBE'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_CUBE'; |
| Security Model |
Execute is 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 |
| Subprograms |
|
| |
BUILD (11.2.0.2 parameters)  |
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 (11.2.0.2 parameter) |
| 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_EXPORT_OPTIONS (new in 11.2.0.2) |
| 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 (new in 11.2.0.2) |
| Undocumented |
dbms_cube.create_export_options(
out_options_xml IN OUT CLOB,
validate_onlyr IN BOOLEAN,
rename_table IN VARCHAR2); |
| TBD |
| |
| CREATE_MVIEW (new in 11.2.0.1) |
| 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 / as sysdba
grant create cube to sh;
grant create dimension, to sh;
grant create any cube dimension to sh;
conn sh/sh
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 (11.2.0.1) |
| 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;
/ |
| |
| DROP_MVIEW (11.2.0.1) |
| 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_TO_FILE (11.2.0.1) |
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 - verify |
dbms_cube.export_xml(
object_ids IN VARCHAR2,
options_dirname IN VARCHAR2,
options_filename IN VARCHAR2,
out_xml IN OUT CLOB); |
| TBD |
| |
| IMPORT_XML (new 11.2 overload) |
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 |
| |
| REFRESH_MVIEW (new in 11.2.0.1 and 11.2.0.2 parameters) |
| 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 |
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 (new in 11.2.0.2) |
| Undocumented |
dbms_cube.upgrae_aw(
sourceaw IN VARCHAR2,
destaw IN VARCHAR2,
upgoptions IN CLOB); |
| TBD |
| |
| VALIDATE_XML (new overload) |
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); |
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'); |
