Recommended Data profiling tool for BI developer

Question

What recommended data profiling tool to retrieve a lot of information and enable to visulize the data into graphical statistics? The data is coming from the source database.

Answer 1

Your questions are quite broad, so this is difficult to answer definitively. However, I'll give an overview of the field.

Data profiling is the process of analysing data to understand the semantics of the data and identify data quality issues that may need to be resolved. Things that data profiling will address include:

• Meanings of various reference data tables or coding schemes in the data. This will almost always be necessary to understand what business logic will be necessary to implement calculations in the ETL processing.

• Content of various data items in the source. This may not be documented sufficiently well that the location or origin is immediately obvious.

• Cardinality and relationships of the data. In some cases foreign keys may not be present in the system, or not a feature of the data source (for example ISAM/VSAM files from older mainframe systems).

• Data issues where the data is incorrect and needs to be fixed at source.

• Reconciliation processes where the results of prototyping, ETL processing or other work need to be reconciled to the data source or some other control.

Often the data can be profiled by just poking around it with a database query tool such as SSMS and writing SQL queries directly against the source, or a copy of the data loaded into a staging or scratch area. Desktop tools such as spreadsheets (pivot tables can be useful) or database systems such as MS-Access may also be helpful.

Purpose built data profiling tools such as Pandora X88 are also available, but these tend to be relatively expensive. Often they are quite a hard sell, even on larger enterprise projects where they represent a tiny fraction of the overall budget.

Often your best approach is to simply copy all your source data into a staging database and poke around it. This work can also form the basis of your staqging processes later on.