OWB - OMB - Data Rules

Card Puncher Data Processing

OWB - OMB - Data Rules

About

Some examples for creating data rules from OMB.

How to create a rule

Custom Data Rule

In the example below we create a single column rule that check that the column which can be supplied is > 300 - by default the custom rule has an attribute VALUE in the group THIS: 

OMBCREATE DATA_RULE 'CUSTOM_RULE' SET PROPERTIES(RULE_TYPE) VALUES('ATTR_VALUE_RULE')
OMBALTER DATA_RULE 'CUSTOM_RULE' SET PROPERTIES(BUSINESS_NAME,DESCRIPTION) VALUES('Custom Rule', 'Single column rule')
OMBALTER DATA_RULE 'CUSTOM_RULE' GROUP 'THIS' ATTRIBUTE 'VALUE' SET PROPERTIES(DATATYPE) VALUES('NUMBER')
OMBALTER DATA_RULE 'CUSTOM_RULE' SET PROPERTIES(ATTR_VALUE_CLAUSE) VALUES('"THIS"."VALUE" > 300')

Custom Data Rule with Multiple Columns

Here we can add additional attributes to the data rule (so many columns can be supplied ie. can check salary > XYZ and job_type in ….): 

OMBCREATE DATA_RULE 'CUSTOM_RULE2' SET PROPERTIES(RULE_TYPE) VALUES('ATTR_VALUE_RULE')
OMBALTER DATA_RULE 'CUSTOM_RULE2' SET PROPERTIES(BUSINESS_NAME,DESCRIPTION) VALUES('Custom Rule2', 'Multi column rule')
OMBALTER DATA_RULE 'CUSTOM_RULE2' GROUP 'THIS' ADD ATTRIBUTE 'ANOTHER_VALUE'
OMBALTER DATA_RULE 'CUSTOM_RULE2' GROUP 'THIS' ATTRIBUTE 'VALUE' SET PROPERTIES(DATATYPE) VALUES('NUMBER')
OMBALTER DATA_RULE 'CUSTOM_RULE2' GROUP 'THIS' ATTRIBUTE 'ANOTHER_VALUE' SET PROPERTIES(DATATYPE) VALUES('VARCHAR2')
OMBALTER DATA_RULE 'CUSTOM_RULE2' SET PROPERTIES(ATTR_VALUE_CLAUSE) VALUES('THIS.VALUE> 300 AND THIS.ANOTHER_VALUE IN (SELECT DISTINCT JOBTYPE FROM EMP_JOBS)')

Fixed Domain List

Create a domain rule with a fixed set of values: 

OMBCREATE DATA_RULE 'DOMAIN_RULE' SET PROPERTIES(RULE_TYPE) VALUES('DOMAIN_LIST_RULE')
OMBALTER DATA_RULE 'DOMAIN_RULE' SET PROPERTIES(BUSINESS_NAME) VALUES('Domain List Rule')
OMBALTER DATA_RULE 'DOMAIN_RULE' ADD DOMAIN_VALUE 'dd' ADD DOMAIN_VALUE 'ee'

Domain Range

Create a domain range rule: 

OMBCREATE DATA_RULE 'DOMAIN_RANGE_RULE' SET PROPERTIES(RULE_TYPE) VALUES('DOMAIN_RANGE_RULE')
OMBALTER DATA_RULE 'DOMAIN_RANGE_RULE' SET PROPERTIES(BUSINESS_NAME) VALUES('Domain Range Rule')
OMBALTER DATA_RULE 'DOMAIN_RANGE_RULE' SET PROPERTIES(MIN_VALUE) VALUES(500)
OMBALTER DATA_RULE 'DOMAIN_RANGE_RULE' SET PROPERTIES(MAX_VALUE) VALUES(20000)
OMBALTER DATA_RULE 'DOMAIN_RANGE_RULE' GROUP 'THIS' ATTRIBUTE 'VALUE' SET PROPERTIES(DATATYPE) VALUES('NUMBER')

Create a No Null Rule

Create a 'no null' rule: 

OMBCREATE DATA_RULE 'NO_NULL_RULE' SET PROPERTIES(RULE_TYPE) VALUES('DOMAIN_NO_NULL_RULE')
OMBALTER DATA_RULE 'NO_NULL_RULE' SET PROPERTIES(BUSINESS_NAME) VALUES('No Nulls Rule')

Create a Unique Key Rule

Create a unique key rule with a single column: 

OMBCREATE DATA_RULE 'UK_RULE' SET PROPERTIES(RULE_TYPE) VALUES('IDENTITY_RULE')
OMBALTER DATA_RULE 'UK_RULE' SET PROPERTIES(BUSINESS_NAME) VALUES('UK Rule')
OMBALTER DATA_RULE 'UK_RULE' SET PROPERTIES(IGNORE_NULLS) VALUES('true')

How to set the number of attributes (for a composite unique key rule)? After this you edit the rule in the UI you will see 'Number of Attributes: 2', the attribute names you use will appear when you apply the data rule to a table (so you will bind the attributes from the rule to columns in the table): 

OMBCREATE DATA_RULE 'UK_RULE2' SET PROPERTIES(RULE_TYPE) VALUES('IDENTITY_RULE')
OMBALTER DATA_RULE 'UK_RULE2' SET PROPERTIES(BUSINESS_NAME) VALUES('UK Rule2')
OMBALTER DATA_RULE 'UK_RULE2' SET PROPERTIES(IGNORE_NULLS) VALUES('true')
OMBALTER DATA_RULE 'UK_RULE2' GROUP 'THIS' ADD ATTRIBUTE 'KEY_ATTRIBUTE_2'

Referential Rule

Create a referential rule: 

OMBCREATE DATA_RULE 'REFERENTIAL_RULE' SET PROPERTIES(RULE_TYPE) VALUES('REFERENCE_RULE')
OMBALTER DATA_RULE 'REFERENTIAL_RULE' SET PROPERTIES(BUSINESS_NAME) VALUES('Referential Rule')
OMBALTER DATA_RULE 'REFERENTIAL_RULE' SET PROPERTIES(LOCAL_MIN_COUNT) VALUES(0)
OMBALTER DATA_RULE 'REFERENTIAL_RULE' SET PROPERTIES(LOCAL_MAX_COUNT) VALUES(1)
OMBALTER DATA_RULE 'REFERENTIAL_RULE' SET PROPERTIES(REMOTE_MIN_COUNT) VALUES(0)
OMBALTER DATA_RULE 'REFERENTIAL_RULE' SET PROPERTIES(REMOTE_MAX_COUNT) VALUES(1)

How to set the number of attributes - use group LOCAL/REMOTE, I use the same naming convention for attribute as OWB built-in name: 

OMBCREATE DATA_RULE 'REFERENTIAL_RULE2' SET PROPERTIES(RULE_TYPE) VALUES('REFERENCE_RULE')
OMBALTER DATA_RULE 'REFERENTIAL_RULE2' SET PROPERTIES(BUSINESS_NAME) VALUES('Referential Rule2')
OMBALTER DATA_RULE 'REFERENTIAL_RULE2' SET PROPERTIES(LOCAL_MIN_COUNT) VALUES(0)
OMBALTER DATA_RULE 'REFERENTIAL_RULE2' SET PROPERTIES(LOCAL_MAX_COUNT) VALUES(1)
OMBALTER DATA_RULE 'REFERENTIAL_RULE2' SET PROPERTIES(REMOTE_MIN_COUNT) VALUES(0)
OMBALTER DATA_RULE 'REFERENTIAL_RULE2' SET PROPERTIES(REMOTE_MAX_COUNT) VALUES(1)
OMBALTER DATA_RULE 'REFERENTIAL_RULE2' GROUP 'LOCAL' ADD ATTRIBUTE 'LOCAL_KEY_ATTRIBUTE_2'
OMBALTER DATA_RULE 'REFERENTIAL_RULE2' GROUP 'REMOTE' ADD ATTRIBUTE 'REMOTE_KEY_ATTRIBUTE_2'

Domain Pattern List for Postcode

Create a domain pattern list for British postcodes, any arbitrary regular expression can be supplied: 

OMBCREATE DATA_RULE 'DOMAIN_PATTERN_RULE' SET PROPERTIES(RULE_TYPE) VALUES('DOMAIN_PATTERN_LIST_RULE')
OMBALTER DATA_RULE 'DOMAIN_PATTERN_RULE' SET PROPERTIES(BUSINESS_NAME) VALUES('Domain Pattern Rule')
OMBALTER DATA_RULE 'DOMAIN_PATTERN_RULE' SET PROPERTIES(DESCRIPTION) VALUES('British Postcode RegExp')
# I have escaped various characters with \ such as [ ] {  }
OMBALTER DATA_RULE 'DOMAIN_PATTERN_RULE' 
   ADD DOMAIN_VALUE '(^(\[A-PR-UWYZ0-9\]\\[A-HK-Y0-9\]\\[AEHMNPRTVXY0-9\]?\[ABEHMNPRVWXY0-9\]? \{1,2\}\[0-9\]\\[ABD-HJLN-UW-Z\]\\{2\}|GIR 0AA)$)'

Customize Built in

Create a customized version of the built-in telephone format rule (there are common formats defined)

telephone DOMAIN_FORMAT_TELEPHONE_RULE
IP address DOMAIN_FORMAT_IP_RULE
SSN DOMAIN_FORMAT_SSN_RULE
Date DOMAIN_FORMAT_DATE_RULE
Number DOMAIN_FORMAT_NUMBER_RULE
URL DOMAIN_FORMAT_URL_RULE
Email DOMAIN_FORMAT_EMAIL_RULE

Some of these are localized versions but it is fairly straightforward to create versions for other locales. 

OMBCREATE DATA_RULE 'COMMON_FORMAT_TELE' SET PROPERTIES(RULE_TYPE) VALUES('DOMAIN_FORMAT_TELEPHONE_RULE')
OMBALTER DATA_RULE 'COMMON_FORMAT_TELE' SET PROPERTIES(BUSINESS_NAME) VALUES('Domain Format Telephone Rule')
OMBALTER DATA_RULE 'COMMON_FORMAT_TELE' SET PROPERTIES(DESCRIPTION) VALUES('Telephone Customized')
# I have escaped [ ] {  }
OMBALTER DATA_RULE 'COMMON_FORMAT_TELE' ADD DOMAIN_VALUE '(^\[\\[:space:\]\\]*\[0-9\]\\{3\}\[\\[:punct:\]|\[:space:\]\\]?\[0-9\]\\{4\}\[\\[:space:\]\\]*$)'

Create Functional Dependency

Create a functional dependency rule: 

OMBCREATE DATA_RULE 'FUNC_DEP_RULE' SET PROPERTIES(RULE_TYPE) VALUES ('FUNCTIONAL_DEP_RULE')
OMBALTER DATA_RULE 'FUNC_DEP_RULE' SET PROPERTIES(FUNCTIONAL_DEP_THRESHOLD) VALUES (10)

If you want more than 1 determinant then add to the group (there is a group for DETERMINANTS and DEPENDENCY): 

OMBALTER DATA_RULE 'FUNC_DEP_RULE' GROUP 'DETERMINANTS' ADD ATTRIBUTE 'DETERMINANT_2'
OMBALTER DATA_RULE 'FUNC_DEP_RULE' GROUP 'DEPENDENCY' ADD ATTRIBUTE 'DEPENDENCY_2'

How to Apply a Rule to a Table?

So how do you actually add a rule usage to a table…..you have to tie the table to the rule and the columns to the attributes!

The example below adds a domain rule to the COUNTRIES table binding the VALUE attribute to the COUNTRY_NAME column 

OMBALTER TABLE 'COUNTRIES' ADD DATA_RULE_USAGE 'DRU' SET REF DATA_RULE '../DRS/DOMAIN_RULE' GROUP 'THIS' 
SET REF TABLE 'COUNTRIES' ATTRIBUTE 'VALUE' SET REF COLUMN 'COUNTRY_NAME'

Reference




Discover More
Global Explorer Public Data Rule
OWB - Data Rules

Data Rule is the key concept of the data quality. They are part of the Data Quality option to Warehouse Builder. If you don't know what is a data rule, please refer you to data rule. For a complete explanation...
Card Puncher Data Processing
OWB - OMB (Oracle Meta Base) Language

OMB is : the scripting language from . a set of java function embedded in the scripting langage tcl (with the help of tcljava). Thus, you must...
Owb Referential Rule
OWB - Referential Rule

Referential Rule permit to enforce referential integrity. When you create a rule and you select the type referential, the software will ask you to enter the definition of the . Properties Definition...



Share this page:
Follow us:
Task Runner