Welcome to All Things Warehouse Builder

Checking referential integrity with data rules

Jean-Pierre Dijcks
Master Product Manager

So you read my KQIs thingie. This one is a bit more practical in that it is real code without coding :-)

I get incoming data into a table, and what I want to do is check referential integrity without FKs, because they will generate errors while loading. Not quite cool. So here is the scenario:

The incoming data in my STG_TAB:

insert into stg_tab values ('1001', 'Product 1001 Name', '1001 description', 'nothing');
insert into stg_tab values ('1002', 'Product 1002 Name', '1002 description', 'nothing');
insert into stg_tab values ('1003', 'Product 1003 Name', '1003 description', 'nothing');
insert into stg_tab values ('1004', 'Product 1004 Name', '1004 description', 'nothing');
insert into stg_tab values ('1005', 'Product 1005 Name', '1005 description', 'nothing');

The table that has the references to check on has the following data (it is called LKP_TAB):

insert into lkp_tab values ('1001', 'IamProduct1001');
insert into lkp_tab values ('1002', 'IamProduct1002');
insert into lkp_tab values ('1003', 'IamProduct1003');

So the point is that 1004 and 1005 are not around, so they are considered not compliant. And I want to now check this with a data rule. These are steps to get this working:

1) Create a new data rule
    Referential rule type
    Use a single attribute and set both cardinality thingies to 1 : n

Create new datarule:

2) Apply the rule to the STG_TAB table in OWB
    Open the data object and apply the rule
    Bind like this


3) Create a mapping in OWB that has the STG_TAB table
    In the property inspector for the operator, find the DataRule node and open it up
    Set the rule to Move to Error


4) On the mapping, while still highlighting the operator

Set Match By Constraint to NO_CONSTRAINTS (this is assuming the STG_TAB has no PKs or UKs that would work for the delete matching)


Now find the attribute in the table that is doing the lookup (e.g. the pk without being an enforced pk) and highlight that in the map
Set the Match column when Deleting row to YES


5) Validate the mapping
6) Re-deploy the staging table, you must do this because you need to get the error table deployed as well... so don't load data yet, first redeploy! (Or generate the scripts and simply run the DDL for the error table)
7) Deploy the LKP_TAB
8) Deploy the mapping
9) Insert the data from the scripts above in both tables
10) Run the mapping and you will see the 1004 and 1005 records being removed from the STG_TAB and placed in the STG_TAB_ERR

Here is the final data set in both these tables:


The error table (I have imported the table back from the database after deploying it via the STG_TAB deploy step - OWB does not do this automatically) looks like this, just as we expected.


Join the discussion

Comments ( 2 )
  • Antonio Akiyama Thursday, September 18, 2008
    Can I use Data Rules with OWB core features or i need to license Data Quality Option???
  • Jean-Pierre Dijcks Thursday, September 18, 2008
    Hi Antonio,
    Data rules are a part of the Data Quality option. So you need to purchase that to use this features.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.