Validation of dimensions

If you have an Oracle data warehouse and are using materialized views for aggregations then Oracle dimension objects would have almost certainly been defined. These objects describe the hierarchical, attribute and join relationships for dimensions and together with database constraints enable the query rewrite mechanism to successfully rewrite queries that navigate dimensional hierarchies. Dimensional objects are declarative and are not enforced by the database.

One Oracle supplied PL/SQL procedure that is often overlooked is the one that validates dimensions. In Oracle 10g this procedure is DBMS_DIMENSION.VALIDATE_DIMENSION but in earlier versions it was DBMS_OLAP.VALIDATE_DIMENSION. The parameters passed to either version of the procedure are very similar and are documented in Supplied PL/SQL Packages reference and the Data Warehousing guide. The procedure navigates the dimensional hierarchy and checks that all of the relationships defined in the dimension object are valid. The rowid (and reason for failure) of data items that fail validation are written to an exceptions table. This exception table can then be used to identify reference items that fail validation. The real utility of this procedure is that it provides a quick way of checking dimensional integrity on DW systems that use RELY constraints.