| Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
|
|
View PDF |
| Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
|
|
View PDF |
You can use the CWM2_OLAP_DELETE package to delete individual cubes, dimensions, or measure folders, or the entire contents of the OLAP Catalog. CWM2_OLAP_DELETE deletes CWM2 metadata created by the CWM2 PL/SQL packages and CWM1 metadata created by Oracle Enterprise Manager. CWM2_OLAP_DELETE deletes both valid and invalid metadata.
OLAP dimensions created in Oracle Enterprise Manager use Oracle Database dimension objects. When deleting these CWM1 dimensions, you can choose whether or not to delete the associated dimension objects. For more information on Oracle dimension objects, see "CREATE DIMENSION" in the Oracle Database SQL Reference.
To rebuild the OLAP Catalog metadata for a relational data source, you can export the data and metadata, delete it, then import it. Use the CWM2_OLAP_EXPORT package and the Oracle Export utility to do the export. Use CWM2_OLAP_DELETE to delete the metadata. Drop the source tables, then use the Oracle import utility to do the import. See Chapter 13, "CWM2_OLAP_EXPORT".
To rebuild analytic workspaces, use the OLAP DML to export the contents of the workspace to an EIF file, then import it in a new workspace. See "Procedure: Import a workspace from a 9i Database into a 10g Database". If you are running in Oracle9i compatibility mode, you will need to re-enable the workspaces and re-create the metadata for the workspaces. See "Enabling Relational Access".
You can use wildcard characters to delete cubes, dimensions, and measure folders whose names meet certain criteria.
Wildcard characters are the underscore "_" and the percent sign "%". An underscore replaces any single character, and a percent sign replaces any zero or more characters. An underscore, but not a percent sign, is also a legal character in a metadata owner or entity name. Any underscore character in the owner or entity name is treated as a wildcard, unless you precede it with a backslash "\" which acts as an escape character.
For example, the following command deletes all the cubes belonging to the owner 'GLOBAL'.
>execute cwm2_olap_delete.delete_cube('GLOBAL', '%', 'yes', 'yes');
The following command deletes all the cubes in the GLOBAL schema whose names start with 'a'.
>execute cwm2_olap_delete.delete_cube('GLOBAL', 'a%', 'yes', 'yes');
If your database includes users 'TESTUSER1' and 'TESTUSER2', you could delete the 'TEST' cube belonging to each of these users with the following command.
>execute cwm2_olap_delete.delete_cube('TESTUSER_', 'TEST', 'yes', 'yes');
If your database includes users 'TEST_USER1' and 'TEST_USER2', you could delete the 'TEST' cube belonging to each of these users with the following command.
>execute cwm2_olap_delete.delete_cube('TEST/_USER_', 'TEST', 'yes', 'yes');
Each procedure in the CWM2_OLAP_DELETE package accepts a parameter that causes a command report to be written to the SQL buffer. You can generate this report without deleting any metadata. A separate parameter controls whether or not you actually execute the delete commands.
|
See Also: "Directing Output" for more information on the SQL buffer and directing the output of OLAP procedures. |
Depending on the metadata entities that you want to delete, the report will list commands like the following.
EXECUTE cwm2_olap_cube.drop_cube ( 'cubeowner', 'cubename') EXECUTE cwm2_olap_dimension.drop_dimension ( 'dimowner', 'dimname') EXECUTE cwm2_olap_catalog.drop_catalog ( 'catalogowner', 'catalogname')
If you choose to drop the dimension objects associated with CWM1 dimensions, the report will also include the following command.
EXECUTE cwm_utility.Collect_Garbage
Use the CWM2_OLAP_MANAGER.SET_ECHO_ON procedure to display the command report on the screen. Use the CWM2_OLAP_MANAGER.BEGIN_LOG procedure to direct the report to a log file. See "Directing Output" for more information.
As long as you have directed the output of the SQL buffer to the screen or to a file, you will see messages describing the success or failure of each stored procedure call. If you choose to delete a cube without generating a command report, you will see only the following.
AMD-00003 dropped Cube "CUBEOWNER.CUBENAME"
If you choose to delete a cube and generate a command report, you will see the following.
EXECUTE cwm2_olap_cube.Drop_Cube('CUBEOWNER', 'CUBENAME');
AMD-00003 dropped Cube "CUBEOWNER.CUBENAME"
Table 10-1 CWM2_OLAP_DELETE
| Subprogram | Description |
|---|---|
|
|
Deletes a cube in the OLAP Catalog. |
|
|
Deletes a dimension in the OLAP Catalog. |
|
DELETE_MEASURE_CATALOG Procedure |
Deletes a measure folder in the OLAP Catalog. |
|
|
Deletes all the metadata in the OLAP Catalog. |
This procedure can be used to delete a cube or group of cubes in the OLAP Catalog. You can also use this procedure to list the commands that will delete the cubes. You can choose to execute these commands or simply list them, without actually deleting the cubes. See "Using a Command Report".
You can identify a group of cubes by specifying wildcard characters in the cube_owner and cube_name parameters. See "Using Wildcards to Identify Metadata Entities".
When you delete a cube, its dimensions are not deleted.
OLAP Catalog cubes are displayed in the view ALL_OLAP2_CUBES.
Syntax
DELETE_CUBE (
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
delete_report IN VARCHAR2,
delete_cube IN VARCHAR2);
Parameters
Table 10-2 DELETE_CUBE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The owner of the cube. See "Using Wildcards to Identify Metadata Entities". |
|
|
The name of the cube. See "Using Wildcards to Identify Metadata Entities". |
|
|
Whether or not to list the commands that will delete the cubes. Specify 'YES' to list the commands. Otherwise specify 'NO'. To display the output on the screen, use the |
|
|
Whether or not to actually delete the cubes. Specify 'YES' to delete the cubes. Otherwise specify 'NO'. |
Example
The following example first generates a command report for deleting the cwm2 cube PRICE_COST in the GLOBAL schema, then actually deletes the cube.
>set serveroutput on size 1000000
>execute cwm2_olap_manager.set_echo_on;
>select * from all_olap2_cubes where OWNER ='GLOBAL';
OWNER CUBE_NAME NVALID DISPLAY_NAME SHORT_DESCRIPTION DESCRIPTION MV
------ --------- ------ ------------ ----------------- ----------- --
GLOBAL PRICE_CUBE O PRICE_CUBE RU
GLOBAL UNITS_CUBE O UNITS_CUBE RU
GLOBAL PRICE_COST N PRICE_COST GS
>execute cwm2_olap_delete.delete_cube('GLOBAL', 'PRICE_COST', 'yes', 'no');
EXECUTE cwm2_olap_cube.Drop_Cube('GLOBAL', 'PRICE_COST');
>select * from all_olap2_cubes where OWNER ='GLOBAL';
OWNER CUBE_NAME NVALID DISPLAY_NAME SHORT_DESCRIPTION DESCRIPTION MV
------ --------- ------ ------------ ----------------- ----------- --
GLOBAL PRICE_CUBE O PRICE_CUBE RU
GLOBAL UNITS_CUBE O UNITS_CUBE RU
GLOBAL PRICE_COST N PRICE_COST GS
>execute cwm2_olap_delete.delete_cube('GLOBAL', 'PRICE_COST', 'yes', 'yes');
EXECUTE cwm2_olap_cube.Drop_Cube('GLOBAL', 'PRICE_COST');
AMD-00003 dropped Cube "GLOBAL.PRICE_COST"
>select * from all_olap2_cubes where OWNER ='GLOBAL';
OWNER CUBE_NAME NVALID DISPLAY_NAME SHORT_DESCRIPTION DESCRIPTION MV
------ --------- ------ ------------ ----------------- ----------- --
GLOBAL PRICE_CUBE O PRICE_CUBE RU
GLOBAL UNITS_CUBE O UNITS_CUBE RU
This procedure can be used to delete a dimension or group of dimensions in the OLAP Catalog. You can also use this procedure to list the commands that will delete the dimensions. You can choose to execute these commands or simply list them, without actually deleting the dimensions. See "Using a Command Report".
You can identify a group of dimensions by specifying wildcard characters in the dimension_owner and dimension_name parameters. See "Using Wildcards to Identify Metadata Entities".
If the dimension was created in Oracle Enterprise Manager, it is a CWM1 dimension. CWM1 dimensions have OLAP Catalog metadata and an associated Oracle dimension object.
When you delete a dimension, all references within cubes to the dimension are also deleted. This causes any cubes that used the dimension to become invalid.
OLAP Catalog dimensions are displayed in the view ALL_OLAP2_DIMENSIONS.
Syntax
DELETE_DIMENSION (
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2,
delete_cwm1_dimension IN VARCHAR2,
delete_report IN VARCHAR2,
delete_dimension IN VARCHAR2);
Parameters
Table 10-3 DELETE_DIMENSION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The owner of the dimension. See "Using Wildcards to Identify Metadata Entities". |
|
|
The name of the dimension. See "Using Wildcards to Identify Metadata Entities". |
|
|
Whether or not to delete the Oracle dimension object associated with a |
|
|
Whether or not to list the commands that will delete the dimensions. Specify 'YES' to list the commands. Otherwise specify 'NO'. To display the output on the screen, use the |
|
|
Whether or not to actually delete the dimensions. Specify 'YES' to delete the dimensions. Otherwise specify 'NO'. |
Example
The following example first generates a command report for deleting the PROD dimension in the GLOBAL schema, then actually deletes the dimension. Since the dimension is a CWM2 dimension, the third parameter to the DELETE_DIMENSION procedure is ignored.
>set serveroutput on size 1000000
>execute cwm2_olap_manager.set_echo_on;
>execute cwm2_olap_delete.delete_dimension
('GLOBAL', 'PROD', 'no','yes', 'no');
EXECUTE cwm2_olap_dimension.Drop_Dimension('GLOBAL', 'PROD');
>execute cwm2_olap_delete.delete_dimension
('GLOBAL', 'PROD', 'no','yes', 'yes');
EXECUTE cwm2_olap_dimension.Drop_Dimension('GLOBAL', 'PROD');
AMD-00003 dropped Dimension "GLOBAL.PROD"
This procedure can be used to delete a measure folder or group of measure folders in the OLAP Catalog. You can also use this procedure to list the commands that will delete the measure folders. You can choose to execute these commands or simply list them, without actually deleting the measure folders. See "Using a Command Report".
You can identify a group of measure folders by specifying wildcard characters in the measure_folder_name parameter. See "Using Wildcards to Identify Metadata Entities".
OLAP Catalog measure folders are displayed in the view ALL_OLAP2_CATALOGS.
Syntax
DELETE_MEASURE_CATALOG (
measure_folder_name IN VARCHAR2,
delete_report IN VARCHAR2,
delete_measure_catalog IN VARCHAR2);
Parameters
Table 10-4 DELETE_MEASURE_CATALOG Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the measure folder. See "Using Wildcards to Identify Metadata Entities". |
|
|
Whether or not to list the commands that will delete the measure folders. Specify 'YES' to list the commands. Otherwise specify 'NO'. To display the output on the screen, use the |
|
|
Whether or not to actually delete the measure folders. Specify 'YES' to delete the measure folder. Otherwise specify 'NO'. |
Example
The following example deletes the two measure folders whose names start with 'TEMP'.
>set serveroutput on size 1000000
>execute cwm2_olap_manager.set_echo_on;
>execute cwm2_olap_delete.delete_measure_catalog
('TEMP%', 'no', 'yes');
AMD-0003 dropped Catalog "Temp1"
AMD-0003 dropped Catalog "Temp2"
This procedure can be used to delete all the metadata in the OLAP Catalog. You can also use this procedure to list the commands that will drop each metadata entity. You can choose to execute these commands or simply list them, without actually deleting the metadata. See "Using a Command Report".
OLAP Catalog metadata is displayed in the OLAP Catalog metadata views, described in Chapter 5.
Syntax
DELETE_OLAP_CATALOG (
delete_cwm1_dimension IN VARCHAR2,
delete_report IN VARCHAR2,
delete_olap_catalog IN VARCHAR2);
Parameters
Table 10-5 DELETE_OLAP_CATALOG Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Whether or not to delete the Oracle dimension object associated with each |
|
|
Whether or not to list the commands that will delete the metadata. Specify 'YES' to list the commands. Otherwise specify 'NO'. To display the output on the screen, use the |
|
|
Whether or not to actually delete all the metadata in the OLAP Catalog. Specify 'YES' to delete the metadata. Otherwise specify 'NO'. |
Example
The following example deletes all the metadata in the OLAP Catalog without generating a command report. Any associated Oracle dimension objects are not deleted.
>set serveroutput on size 1000000
>execute cwm2_olap_manager.set_echo_on;
>execute cwm2_olap_delete.delete_olap_catalog('no', 'no', 'yes');
