This document is intended to demonstrate a new method to handle data integrity with respect to data warehousing. There are some occasionsduring data ware house development, where data integrity cannot be enforced :
1) As known, Netezza database cannot enforce foreign key.
2) Data warehouse is not created by star scheme with foreign key enforcement duo to different reasons.
3) There are many different logics on the Framework manager side, such as make one or more columns with logic to be combined as adapter column to connect with conformed dimension.
It is very difficult to write SQL for validating database, because we need to mimic all mapping logic in framework manager and then proceed. In addition, when there are any changes in Framework, we need to change SQL accordingly. In other word, we need to maintain two sets of codes, which is very difficult to keep them in sync.
Solution & implementation
The idea is to take advantage of framework manager model, as all detail logic is already built there. we don’t need to write SQL script, let Framework manager write for us behind the scene. Whether there is DMR model or relational model, we need to create report at business layers. Theoretically, we need to check all links about fact table. Please see sample below, we need to make sure that all data in PCF Performance Cube should be contained in Conformed Delq Status Dim.
The column on fact side Conformed Delq Status Adaptor is with logic below:
case [Business Layer].[PCF Performance Cube].[Realigned Delq Status]
when '0 - Current' then 'NOT DELINQUENT'
when '1 - Class 1' then 'CLASS 0'
when '2 - Class 2' then 'CLASS 1'
when '3 - Class 3' then 'CLASS 2'
when '4 - Class 4' then 'CLASS 3'
when '5 - NPNA' then 'NPNA'
end
The exception report is built from this relationship:
Fact: 'PCF Performance Cube'
Dim: 'Conformed Delq Status Adaptor'
Exception: [Business Layer].[PCF Performance Cube].[Conformed Delq Status Adaptor]
No comments:
Post a Comment