3.5.3.1.1 Single Column Data Check Definitions
Single Column Data Checks help to perform data quality check on only one column selected during DQ Rule creation.
You can include the following Data Quality checks in the DQ Rule, if the check type is set to Single Column Check.
- Range Check - Range Check identifies if the base column data
falls outside a specified range of Minimum and Maximum value. Range check can be
enabled only if the base column has date or number value.
- Select the check-box to enable the Range check.
- Set the warning level to Severity, Warning or Information.
- If the selected Base Column is of Date type, select Minimum and Maximum date range. If the selected base column is of Number type, enter the Range value. You can specify numeric, decimal, and negative values for number Data type.
- Check the Inclusive check-box, to include the specified date/value during the data check.
- Click Edit to add specific filter expressions, as additional conditions. For more information, refer to Creating Expressions.
- Select the Assignment option. The Assignment option is
enabled only if Warning/Information is selected as the Warning level.
- Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
- Specify the Assignment Value.
- Select the Message Severity as 1 or 2 from the drop-down list.
- Select a pre-defined Message to be displayed from the
drop-down list.
To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.
- Null Value Check -Null Value Check checks identifies if there is
any null value in the selected column.
- Select the check-box to enable the Null Value check.
- Set the warning level to Severity, Warning or Information.
- Click Edit to add specific filter expressions, as additional conditions.
- Select the Assignment option. The Assignment option is enabled only
if Warning/Information is selected as the Warning level.
- Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
- Specify the Assignment Value.
- Select the Message Severity as 1 or 2 from the drop-down list.
- Select a pre-defined Message to be displayed from the
drop-down list.
To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.
- Blank Value Check -Null Value Check checks identifies if there
is any entry in the selected column is blank.
- Select the check-box to enable the Blank Value check.
- Set the warning level to Severity, Warning or Information.
- Click Edit to add specific filter expressions, as additional conditions.
- Select the Assignment option. The Assignment option is enabled only
if Warning/Information is selected as the Warning level.
- Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
- Specify the Assignment Value.
- Select the Message Severity as 1 or 2 from the drop-down list.
- Select a pre-defined Message to be displayed from the
drop-down list.
To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.
- Data Length Check -Data Length Check checks for the length of the
base column data using a minimum and maximum value and identifies if it falls
outside the specified range.
- Select the check-box to enable the Data Length check.
- Set the warning level to Severity, Warning or Information.
- Enter the Minimum and maximum values for validation.
- Click Edit to add specific filter expressions, as additional conditions.
- Duplicate Check - Duplicate Check can be used when a combination
of column is unique and identifies all the duplicate data of the base table in terms
of the columns selected for the duplicate check.
- Select the check-box to enable the Duplicate Check.
- Set the warning level to Severity, Warning or Information.
- Click Edit to add specific filter expressions, as additional conditions.
- Click Edit and select the required column to be added to the Column List, for duplicate check validation.
- Custom Check/Business Check- Custom Check/Business Check is a
valid SQL query to identify the data with the query specified as the Custom/business
SQL. You can define the SQL, but the Select clause of the query has to follow the
order as specified in the template of the Custom Check panel.
Sample Template :
"SELECT 'N_COUNTRY_SKEY' PKNAMES, N_COUNTRY_SKEY PK1, null PK2, null PK3, null PK4, null PK5, null PK6, null PK7, null PK8, V_COUNTRY_DESC ERRORCOL FROM DIM_COUNTRY WHERE N_COUNTRY_SKEY >50"
- Select the check-box to enable the Custom Check.
- Set the warning level to Severity, Warning or Information.
- Enter the SQL Query to perform the custom check.
- Column Reference/Specific Value Check - Column Reference /
Specific Value Check compares the base column data with another column of the base
table or with a specified direct value using the list of pre-defined operators.
- Select the check-box to enable the Column Reference check.
- Set the warning level to Severity, Warning or Information. Column reference check can be enabled only if the base column has date or number value.
- Select the Mathematical Operator from the drop-down list.
- Select the Filter Type as one of the
following:
- Select Specific Value and specify the Value. You can specify numeric, decimal, and negative values for number Data type.
- Select Another Column and select Column Name from the drop-down list.
- Click Edit to add specific filter expressions, as additional conditions.
- Select the Assignment option. The Assignment option is enabled only
if Warning/Information is selected as the Warning level.
- Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
- Specify the Assignment Value.
- Select the Message Severity as 1 or 2 from the drop-down list.
- Select a pre-defined Message to be displayed from the
drop-down list.
To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.
- List of Value - List of Value Check verifies the values where a
dimension / master table is not present. This check identifies if the base column
data is not matching with any value or code specified in a list of values.
- Select the check-box to enable the List of Value check.
- Set the warning level to Severity, Warning or Information.
- Select Input Values and specify the List of Values. You can specify numeric or String values.
- Click Edit to add specific filter expressions, as additional conditions.
- Select the Assignment option. The Assignment option is enabled only
if Warning/Information is selected as the Warning level.
- Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
- Specify the Assignment Value.
- Select the Message Severity as 1 or 2 from the drop-down list.
- Select a pre-defined Message to be displayed from the
drop-down list.
To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.
- Referential Integrity Check - Referential Integrity Check
identifies all base column data which has not been referenced by the selected column
of the referenced table. Here, the reference table and columns are user
specified.
- Select the check-box to enable the Referential Integrity Check.
- Set the warning level to Severity, Warning or Information. Column reference check can be enabled only if the base column has date or number value.
- Select the Table (Referential Integrity Check dimension table) from the drop-down list. The base table selected under the Select grid is excluded from the drop-down list.
- Select the Column from the drop-down list. The list displays those columns that have the same Data Type as that of the Base Column selected under Select grid.
- Select the Is Composite Key check-box if the base column is part of a Composite Key.
- Click Edit to add specific filter expressions, as additional conditions.