Data Profiling in Datawarehouse

Data Profiling    

A process whereby one examines the data available in an existing database and collects statistics and information about that data. The purpose of these statistics may be to:

• Find out whether existing data can easily be used for other purposes 
• Give metrics on data quality including whether the data conforms to company standards
•  Assess the risk involved in integrating data for new applications, including the challenges of joins
•  Track data quality
•  Assess whether metadata accurately describes the actual values in the source database
•   Understanding data challenges early in any data intensive project, so that late project surprises are avoided. Finding data problems late in the project can incur time delays and project cost overruns.

Data Profiling categories    :

The overall process is in three steps, which must be executed in order:

•  Column Profiling Column profiling provides critical metadata which is required in order to perform dependency profiling, and as such, must be executed before dependency profiling.

•  Dependency Profiling, which identifies intra-table dependencies. Dependency profiling is related to the normalization of a data source, and addresses whether or not there are non-key attributes that determine or are dependent on other non-key attributes. The existence of transitive dependencies here may be evidence of second-normal form.

•  Redundancy Profiling, which identifies overlapping values between tables. This is typically used to identify candidate foreign keys within tables, to validate attributes that should be foreign keys (but that may not have constraints to enforce integrity), and to identify other areas of data redundancy. Example: redundancy analysis could provide the analyst with the fact that the ZIP field in table A contained the same values as the ZIP_CODE field in table B, 80% of the time.

Benefits of Data Profiling:

• The benefits of data profiling is to improve data quality, shorten the implementation cycle of major projects, and improve understanding of data for the users^.

•  Discovering business knowledge embedded in data itself is one of the significant benefits derived from data profiling^..         

• Data profiling is one of the most effective technologies for improving data accuracy in corporate databases.      

• Although data profiling is effective, it can be challenging not slip into analysis paralysis.

Data Profiling

What is metadata (Data about data)

Meta Data

The ETL metadata functional element is responsible for maintaining information (metadata) about the movement and transformation of data, and the operation of the data warehouse. It also documents the data mappings used during the transformations. Meta data logging provides possibilities for automated administration, trend prediction, and code reuse.

Metadata

Meta data examples:

Examples of data warehouse metadata that can be recorded and used to analyze the activity and performance of a data warehouse include:

o   Data Lineage, such as the time that a particular set of records was loaded into the data warehouse.

o   Schema Changes, such as changes to table definitions. 

o   Data Type Usage, such as identifying all tables that use the "Birthdate" user-defined data type. 

o   Transformation Statistics, such as the execution time of each stage of a transformation, the number of rows processed by the transformation, the last time the transformation was executed, and so on. 

o   DTS Package Versioning, which can be used to view, branch, or retrieve any historical version of a particular DTS package.

o   Data Warehouse Usage Statistics, such as query times for reports. 

Business Metadata:

In IT, Business Metadata is about creating definitions, business rules. The advantage is of this business metadata is whether they are technical or non-technical, everybody would understand what is going on within the organization. Example:

Business Metadata

                     

Technical Metadata:

 Technical metadata describes information about technology such as the ownership of the database, physical characteristics of a database. In Technical metadata, derivation rules are important when formulae or calculations are applied on a column. Example:

Technical Metadata

Facebook Commentbox