78 DBMS_HIERARCHY
DBMS_HIERARCHY
contains subprograms for validating the data in tables used by hierarchies and analytic views.
This chapter contains the following topics:
78.1 DBMS_HIERARCHY Overview
The DBMS_HIERARCHY
package contains functions for validating that the contents of a database table are suitable for use by an analytic view or a hierarchy, a function for verifying the success of the validation, and a procedure for creating a table for logging validation operations.
Note:
Names specified by parameters of the DBMS_HIERARCHY
subprograms are case-sensitive.
For information about using analytic views, see Oracle Database Data Warehousing Guide.
78.2 DBMS_HIERARCHY Security Model
Summarizes security considerations for the validation of analytic view and hierarchy objects.
Note:
To ensure that the user has enough tablespace to log validation operations, do one of the following:-
GRANT
UNLIMITED
TABLESPACE
TO
username;
-
ALTER
USERNAME
usernameQUOTA
sizeON
tablespace_name;
The following system privileges are required to use this package:
To validate objects in the user’s own schema:
-
CREATE TABLE
privilege forCREATE_VALIDATE_LOG_TABLE
or to haveVALIDATE_ANALTYIC_VIEW
orVALIDATE_HIERARCHY
automatically create a table -
SELECT
privilege on the tables or views used by the analytic view or hierarchy -
INSERT
privilege on the tables used by the attribute dimensions of the hierarchy or the fact table used by the analytic view
To validate objects in different schemas:
-
CREATE ANY TABLE
privilege forCREATE_VALIDATE_LOG_TABLE
or to have theVALIDATE_ANALTYIC_VIEW
orVALIDATE_HIERARCHY
automatically create a table -
INSERT ANY TABLE
privilege on the tables used by the attribute dimensions of the hierarchy or the fact table used by the analytic view
78.3 Summary of DBMS_HIERARCHY Subprograms
This table lists the DBMS_HIERARCHY
subprograms and briefly describes them.
Subprogram | Description |
---|---|
Creates a table that you can use for logging messages generated by the |
|
VALIDATE_ANALYTIC_VIEW Function |
Validates that the data in a table is suitable for use by an analytic view. |
VALIDATE_CHECK_SUCCESS Function |
Indicates whether a prior call to |
Validates that the data in a table is suitable for use by a hierarchy. |
78.3.1 CREATE_VALIDATE_LOG_TABLE Procedure
This procedure creates a table that you can use for logging messages generated by the VALIDATE_ANALYTIC_VIEW
or VALIDATE_HIERARCHY
function, which validate data used by an analytic view or hierarchy.
The table that this procedure creates has the following structure.
NAME NULL? DATATYPE
------------------ -------- --------
LOG_NUMBER NOT NULL NUMBER
ACTION_ORDER NOT NULL NUMBER
OBJECT_OWNER NOT NULL VARCHAR2(128 BYTE)
OBJECT_NAME NOT NULL VARCHAR2(128 BYTE)
ACTION NOT NULL VARCHAR2(10 BYTE)
TIME NOT NULL TIMESTAMP(6)
ERROR_NUMBER NUMBER
ERROR_MESSAGE VARCHAR2(4000)
Syntax
DBMS_HIERARCHY.CREATE_VALIDATE_LOG_TABLE (
table_name IN VARCHAR2,
owner_name IN VARCHAR2 DEFAULT NULL
IGNORE_IF_EXISTS IN PL/SQL BOOLEAN DEFAULT FALSE);
Parameters
Parameter | Description |
---|---|
table_name |
The name of the table to create. |
owner_name |
The name of the schema in which to create the table. If owner_name is NULL , then the table is created in the current user’s schema.
|
IGNORE_IF_EXISTS |
A Boolean that indicates whether to create the table if a table by the same name exists. If you specify a table, it must have the same structure as the table that this procedure creates. |
Examples
Example 78-1 Creating a Validation Log Table
BEGIN
DBMS_HIERARCHY.CREATE_VALIDATE_LOG_TABLE (
'VAL_AV_HIERARCHY_LOG',
'AV_USER',
FALSE
);
END;
/
78.3.2 VALIDATE_ANALYTIC_VIEW Function
This function validates that the data in a table or view conforms to the logical constraints inherent in the definition of an analytic view.
Syntax
DBMS_HIERARCHY.VALIDATE_ANALYTIC_VIEW (
analytic_view_name IN VARCHAR2 DEFAULT NULL,
analytic_view_owner_name IN VARCHAR2 DEFAULT NULL,
log_table_name IN VARCHAR2 DEFAULT NULL,
log_table_owner_name IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
Parameters
Parameter | Description |
---|---|
analytic_view_name |
The name of the analytic view to validate. |
analytic_view_owner_name |
The name of the owner of the schema that contains the analytic view. |
log_table_name |
The name of the validation log table in which to put the results of the validation operation. |
log_table_owner_name |
The name of the owner of the schema in which the validation log table exists or in which to create the table. |
Returns
The number of the entry in the validation log table for the validation results.
Usage Notes
If the log_table_name
parameter is NULL
, then the VALIDATE_ANALYTIC_VIEW
function creates a validation log table. The name of the table it creates is DBMS_HIERARCHY_LOG.
When the validation operation begins, a row is inserted into the log table with the action of START
. When the operation completes, a row is inserted into the log table with the action of END
. When an error is detected, a row is inserted into the log table with the action of ERROR
, and the associated error_number
and error_message
columns are populated. All rows inserted into the validation log table include a log number and the time of the insert.
The VALIDATE_ANALYTIC_VIEW
function verifies that the following conditions are true for each attribute dimension the analytic view is dimensioned by:
-
The key values found in the fact table for the attribute dimension must exist in the star schema dimension table for that attribute dimension.
-
The referenced attribute values for the attribute dimension must be unique across all rows of the star schema dimension table for that dimension.
Also, for every hierarchy in the analytic view, the function verifies that the following conditions are true:
-
The primary key of a level determines a unique value for each attribute of the level.
-
For each row of the table or view used by the attribute dimension of the hierarchy, the value for every level key column (including alternate keys) of a
NOT
NULL
level is non-NULL
. -
For each row of the table or view, either all level key columns and alternate key columns of a
SKIP
WHEN
NULL
level must beNULL
or they must all be non-NULL
. This verifies that the alternate level key is determined by the level key. -
For each group of rows that have the same alternate key column values for a level, the key column values must have the same column values. This verifies that the level key is determined by the alternate level key, which is required for an alternate key.
Examples
Example 78-2 Validating an Analytic View
DECLARE
log_num NUMBER;
obj_name VARCHAR2(8) := 'SALES_AV';
BEGIN
log_num := DBMS_HIERARCHY.VALIDATE_ANALYTIC_VIEW(obj_name);
END;
/
78.3.3 VALIDATE_CHECK_SUCCESS Function
This function indicates whether a prior call to VALIDATE_HIERARCHY
or VALIDATE_ANALYTIC_VIEW
was successful or produced validation errors.
Syntax
DBMS_HIERARCHY.VALIDATE_CHECK_SUCCESS (
TOPOBJ_NAME IN VARCHAR2,
TOPOBJ_OWNER IN VARCHAR2,
LOG_NUMBER IN VARCHAR2
LOG_TABLE_NAME IN VARCHAR2
LOG_TABLE_OWNER_NAME IN VARCHAR2 )
RETURN VARCHAR2;
Parameters
Parameter | Description |
---|---|
TOPOBJ_NAME |
The name of the hierarchy or analytic view. |
TOPOBJ_OWNDER |
The owner of the hierarchy or analytic view. |
LOG_NUMBER |
The number of the log entry. |
LOG_TABLE_NAME |
The name of the log table. |
LOG_TABLE_OWNER_NAME |
The name of the schema in which the table exits. |
Returns
A VARCHAR2
that is SUCCESS if no errors occurred or ERROR if errors did occur.
Examples
Example 78-3 Using VALIDATE_CHECK_SUCCESS
This example finds out whether the prior call to VALIDATE_ANALTYIC_VIEW
encountered errors.
DECLARE
log_num NUMBER;
succ VARCHAR2(7);
obj_name VARCHAR2(8) := 'SALES_AV';
BEGIN
log_num := dbms_hierarchy.validate_analytic_view(obj_name);
succ := dbms_hierarchy.validate_check_success(
topobj_name => obj_name, log_number => log_num);
IF (succ != 'SUCCESS') THEN
RAISE_APPLICATION_ERROR(
num => -20000,
msg => 'Validate failed!');
END IF;
END;
/
78.3.4 VALIDATE_HIERARCHY Function
This function validates that the data in a table or view conforms to the logical constraints inherent in the definitions of an attribute dimension that uses the table or view and a hierarchy that uses the attribute dimension.
Syntax
DBMS_HIERARCHY.VALIDATE_HIERARCHY (
hier_name IN VARCHAR2,
hier_owner_name IN VARCHAR2 DEFAULT NULL,
log_table_name IN VARCHAR2 DEFAULT NULL,
log_table_owner_name IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
Parameters
Parameter | Description |
---|---|
hier_name |
The name of the hierarchy to validate. |
hier_owner_name |
The name of the owner of the schema that contains the hierarchy. |
log_table_name |
The name of the validation log table in which to put the results of the validation operation. |
log_table_owner_name |
The name of the owner of the schema in which the validation log table exists or in which to create the table. |
Returns
The number of the entry in the validation log table for the validation results.
Usage Notes
If the log_table_name
parameter is NULL
, then the VALIDATE_HIERARCHY
function creates a validation log table. The name of the table it creates is DBMS_HIERARCHY_LOG.
When the validation operation begins, a row is inserted into the log table with the action of START
. When the operation completes, a row is inserted into the log table with the action of END
. When an error is detected, a row is inserted into the log table with the action of ERROR
, and the associated error_number
and error_message
columns are populated. All rows inserted into the validation log table include a log number and the time of the insert.
The VALIDATE_HIERARCHY
function verifies that the following conditions are true for the hierarchy:
-
The primary key of a level determines a unique value for each attribute of the level.
-
For each row of the table or view used by the attribute dimension of the hierarchy, the value for every level key column (including alternate keys) of a
NOT
NULL
level is non-NULL
. -
For each row of the table or view, either all level key columns and alternate key columns of a
SKIP
WHEN
NULL
level must beNULL
or they must all be non-NULL
. This verifies that the alternate level key is determined by the level key. -
For each group of rows that have the same alternate key column values for a level, the key column values must have the same column values. This verifies that the level key is determined by the alternate level key, which is required for an alternate key.
Examples
Example 78-4 Validating a Hierarchy and Specifying a Table Name
This example validates the PRODUCT_HIER
hierarchy and specifies that the results be inserted in the table named VAL_AV_HIERARCHY_LOG
. The owner of the hierarchy and of the schema that contains the table is AV_USER.
-- Create a log table.
BEGIN
DBMS_HIERARCHY.CREATE_VALIDATE_LOG_TABLE (
'VAL_AV_HIERARCHY_LOG',
'AV_USER',
FALSE
);
END;
/
-- Validate the hierarchy.
DECLARE
log_num NUMBER;
obj_name VARCHAR2(12) := 'PRODUCT_HIER';
table_name VARCHAR2(28) := 'VAL_AV_HIERARCHY_LOG';
BEGIN
log_num := DBMS_HIERARCHY.VALIDATE_HIERARCHY(obj_name, 'AV_USER', table_name);
END;
/
Query the log table.
SELECT LOG_NUMBER, ACTION, OBJECT_NAME, ERROR_NUMBER, ERROR_MESSAGE
FROM AV_USER.VAL_AV_HIERARCHY_LOG;
WHERE OBJECT_NAME = 'PRODUCT_HIER';
LOG_NUMBER ACTION OBJECT_NAME ERROR_NUMBER ERROR_MESSAGE
---------- ---------- ------------------- ------------ -------------
1 START PRODUCT_HIER
1 END PRODUCT_HIER
Example 78-5 Validating a Hierarchy Without Specifying a Table Name
This example shows that if you do not specify a validation log table, then the VALIDATE_HIERARCHY
function creates one named DBMS_HIERARCHY_LOG.
DECLARE
log_num NUMBER;
obj_name VARCHAR2(12) := 'PRODUCT_HIER';
BEGIN
log_num := DBMS_HIERARCHY.VALIDATE_HIERARCHY(obj_name);
END;
Query the log table.
SELECT LOG_NUMBER, ACTION, OBJECT_NAME, ERROR_NUMBER, ERROR_MESSAGE
FROM DBMS_HIERARCHY_LOG
WHERE OBJECT_NAME = 'PRODUCT_HIER';
LOG_NUMBER ACTION OBJECT_NAME ERROR_NUMBER ERROR_MESSAGE
---------- ---------- ------------------- ------------ -------------
1 START PRODUCT_HIER
1 END PRODUCT_HIER