Start Your Data Journey

Table Analysis

The main use of Table Analysis is to analyse the data in single tables. However, it is possible to analyse data in multiple tables.

Table analysis has following options:

  • Business Rules
  • Unique Fields Set
  • Dependant Fields Set

Business Rules

Business Rules allow the user to write their own SQL rules to validate against the table. Please note that the rule should return only one column as the output and the condition is a positive condition meaning it will flag the rows satisfying the condition as DQ errors.

Business Rules can be created or imported from file.

Option 1. Create Business Rule using SQL

For instance user wants to write SQL query the rule to validate the Committed Date must be lesser than Receipt date and ship date, then the rule check is defined as,

select L_COMMITDATE from LINEITEM
where NVL(L_COMMITDATE,'1990-01-01')> NVL(L_SHIPDATE,'1990-01-01')
and NVL(L_COMMITDATE,'1990-01-01')> NVL(L_RECEIPTDATE,'1990-01-01')

Do the following steps to create business rules.

1.1. Click on Table Analysis.

1.2. Click on + Add rule button.

1.3. Provide required info to add new rule.

Field Name Field Description
Rule Name Specify the name of the rule.
Rule Description Specify description about rule.
Rule Dimension(s) Add/Select the rule dimensions.
SQL Specify new rule SQL

1.4. Click on Verify button and it will validate SQL rule.

1.5. Click on save button

After following above steps (Sample View):

Option 2. Upload Business Rule SQLs

For instance user wants upload the rules from a file.

Follow the below steps to upload business rules from excel file.

2.1. Click on Table Analysis.

2.2. Click on Upload Rules button.

2.3. Choose the Rules file.

2.4. Click on validate button.

2.5. Click on save button.

After following above steps (Sample View):

Unique Fields Set

This rule is to validate the uniqueness of data in the selected set of columns.

For instance user wants to validate data uniqueness in combination of L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER, then the rule check is defined as,

select count(*),L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER from LINEITEM
group by L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER
having count(*) > 1
Follow below steps to define the Unique Fields Set.

Step 1. Click on Unique Fields Set.

Step 2. Click on drop down button.

Step 3. select the columns L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER.

Step 4. Click on save button



After following above steps (Sample View):


Dependant Fields Set


This rule is to validate the Not Null fields set in each group of columns. For instance user wants to validate Not null fields set in combination of L_ORDERKEY,L_PARTKEY,L_SUPPKEY columns, then the rule check is defined as,


select L_ORDERKEY,L_PARTKEY,L_SUPPKEY from LINEITEM
where (L_ORDERKEY is null and L_PARTKEY is null and L_SUPPKEY is null)


Follow the below steps to define the Dependant Fields Set.


Step 1. Click on + Add button.


Step 2. Fill the rule description.


Step 3. select the columns L_ORDERKEY,L_PARTKEY,L_SUPPKEY.


Step 4. Click on save button


After following above steps (Sample View):








	
		Menu