Checking referential integrity with data rules

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

ApplyRuleToTable:

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

SetRuleActions:

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)

SetNoConstraint:

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

SetDeleteAttribute:

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:

FinalDataCorr:

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.

FinalDataErr:

Comments:

Can I use Data Rules with OWB core features or i need to license Data Quality Option???

Posted by Antonio Akiyama on September 18, 2008 at 12:51 AM PDT #

Hi Antonio, Data rules are a part of the Data Quality option. So you need to purchase that to use this features. JP

Posted by Jean-Pierre Dijcks on September 18, 2008 at 01:42 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today