Validating Dimensions

The information of a dimension object is declarative only and not enforced by the
database. If the relationships described by the dimensions are incorrect, incorrect
results could occur. Therefore, you should verify the relationships specified by
CREATE DIMENSION using the DBMS_DIMENSION.VALIDATE_DIMENSION
procedure periodically.

This procedure is easy to use and has only four parameters:

■ dimension: the owner and name.
■ incremental: set to TRUE to check only the new rows for tables of this
dimension.
■ check_nulls: set to TRUE to verify that all columns that are not in the levels
containing a SKIP WHEN NULL clause are not null.
■ statement_id: a user-supplied unique identifier to identify the result of each
run of the procedure.

The following example validates the dimension TIME_FN in the sh schema:
@utldim.sql
EXECUTE DBMS_DIMENSION.VALIDATE_DIMENSION ('SH.TIME_FN', FALSE, TRUE,
'my 1st example');
Before running the VALIDATE_DIMENSION procedure, you need to create a local
table, DIMENSION_EXCEPTIONS, by running the provided script utldim.sql. If the
VALIDATE_DIMENSION procedure encounters any errors, they are placed in this table.
Querying this table will identify the exceptions that were found. The following
illustrates a sample:
SELECT * FROM dimension_exceptions
WHERE statement_id = 'my 1st example';
STATEMENT_ID OWNER TABLE_NAME DIMENSION_NAME RELATIONSHIP BAD_ROWID
------------ ----- ---------- -------------- ------------ ---------
my 1st example SH MONTH TIME_FN FOREIGN KEY AAAAuwAAJAAAARwAAA

However, rather than query this table, it may be better to query the rowid of the
invalid row to retrieve the actual row that has violated the constraint. In this example, the dimension TIME_FN is checking a table called month. It has found a row thatviolates the constraints. Using the rowid, you can see exactly which row in the month table is causing the problem, as in the following:
SELECT * FROM month
WHERE rowid IN (SELECT bad_rowid
FROM dimension_exceptions
WHERE statement_id = 'my 1st example');
MONTH QUARTER FISCAL_QTR YEAR FULL_MONTH_NAME MONTH_NUMB
------ ------- ---------- ---- --------------- ----------
199903 19981 19981 1998 March 3

Posted in: Dimension