Wednesday, December 18, 2013

How to create Data Quality Checks in OFSAA?

Leave a Comment
In order to create DQ’s in OFSAA go through the following steps:
1.       On starting the OFSAA application click on the Data Quality Framework>>Data Quality Check Library and click on Add New Tab.
2.       Data Quality Definition window opens.
3.       Provide a unique name for the DQ under DQ Name eg. DQ101.
4.       Provide a brief description for the DQ under DQ Description eg. DQ-Product Name
5.       Select require table from the ‘Table Name’ drop down eg. Table1.
6.       Select column on which the DQ is created for Table1.
7.       Select from the list of checks from the application to be applied to the column. Types of DQ's in OFSAA
a.       Range Check:
i.                      It checks if the value of the table column falls outside the range specified in the check.
ii.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the radio button Error/Warning/Information.
iii.                  If value is to be defaulted (only in case of severity ‘Warning’), check Defaulted check box and assign the default value under ‘Default Value’.
iv.                 In order to set the range, provide minimum and maximum values under ‘Min’ and ‘Max’.
v.                   An additional condition can be added if applicable under ‘Filter Condition’.

b.      Null Value Check:
i.                     Null Check checks if table column has NULL.
ii.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the radio button Error/Warning/Information.
iii.                  If value is to be defaulted (only in case of severity ‘Warning’), check Defaulted check box and assign the default value under ‘Default Value’.
iv.                 An additional condition can be added if applicable under ‘Filter Condition’.

c.       Blank Check:
i.                     Null checks if table column has blank value i.e white spaces.
ii.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the button Error/Warning/Information.
iii.                  If value is to be defaulted (only in case of severity ‘Warning’), check Defaulted check box and assign the default value under ‘Default Value’.
iv.                 An additional condition can be added if applicable under ‘Filter Condition’.

d.      Referential Integrity Check:
i.                     Referential Integrity Check checks if the column data has not been taken from a column of reference table defined in the DQ check.
ii.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the button Error/Warning/Information.
iii.                  Select the Reference Table from ‘Table Name’ drop down and reference table column from ‘Column Name’
iv.                 Assignment of Default value is not visible in this check.
v.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the button Error/Warning/Information.
e.      List of Values Check
i.                     LoV check checks if the data in the table column matches with the list of values defined in the DQ check.
ii.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the button Error/Warning/Information.
iii.                  If value is to be defaulted (only in case of severity ‘Warning’), check Defaulted check box and assign the default value under ‘Default Value’.
iv.                 Hardcode the list of values under ‘LOV’.
v.                   An additional condition can be added if applicable under ‘Filter Condition’.

f.        Data Length Check:
i.                     It checks if the length of the value (no. of bytes) of the table column falls outside the range specified in the check.
ii.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the radio button Error/Warning/Information.
iii.                  If value is to be defaulted (only in case of severity ‘Warning’), check Defaulted check box and assign the default value under ‘Default Value’.
iv.                 In order to set the range of bytes, provide minimum and maximum values under ‘Min’ and ‘Max’.
v.                   An additional condition can be added if applicable under ‘Filter Condition’.

g.       Column Reference Check:
i.                     It checks if the value of the table column with another column of the same table.
ii.                   Check ‘Enabled’ check box. Select the severity of the DQ check by selecting the radio button Error/Warning/Information.
iii.                  If value is to be defaulted (only in case of severity ‘Warning’), check Defaulted check box and assign the default value under ‘Default Value’.
iv.                 An additional condition can be added if applicable under ‘Filter Condition’.

h.      Custom/Business Check: Custom check is defined if none the above checks meet the requirements. User can create its own SQL query as per the requirements.

After all the checks are defined for the DQ click on the ‘Generate Query’ button. A query is generated under that covers all the DQ checks defined above.









0 comments:

Post a Comment