This article describes what data profiling is, why to use data profiling and how to check the data profile of a table in TimeXtender Data Integration.

What is data profiling

Data profile tool can be used for data profiling, which is the process of examining, analyzing, reviewing and summarizing data sets to gain insight into the quality of data. 

What can the data profiling be used for?

We can use the data profile tool to show the number of null values, outliers (based on minimum, maximum and average) which can be used to gauge data quality and guide decisions around data cleansing.

It can also be used to decide which data types make sense for a field e.g. in the case of a text field, the maximum number of characters can be used to guide the choice of data type.

Checking the Data Profile of a Table

Right click on a table and select Data Profile.

The data profile shows information regarding each field in the  table. 

This table contains the following records.

The Data Profile for the first field department_id in our table indicates that it is an integer field with no null values, an average of 6, minimum of 1 and maximum of 11. 

If we select the next field on the left, department_name, the data profile indicates that it is a varchar(30) field with zero null values, 11 distinct values, and a maximum length of 16 characters for a single record. 

Pattern Search

The Pattern Search button can be found in the bottom of the data profile window, and can be used to explore the data and find patterns within the data.

The Pattern Search finds a specific number of rows matching a LIKE or NOT LIKE search phrase. A percentage sign can be used as a wildcard search for several characters. For example, the search "%ing" finds all records in the department_name field ending in "ing". Further information regarding wildcard searches can be found by hovering the information icon.

Since "Select top" number of records is set to unlimited, all 4 records in the department_name field are returned.

However, if we changed this property from unlimited to 2 then only 2 records are returned. 

This functionality also works for integer fields, for example, the location_id field is an integer field but we can still search for all location_id records that start with 2 using a "LIKE 2%" search.