Data Cleansing for Data Warehousing

How important is Extract, Transform, Load (ETL) to data Warehousing?

Politicians raising money can be used as an analogy to compare data cleansing to data warehousing. There is almost no likelihood of one existing without the other. Data cleansing is often the most time intensive, and contentious, process for data warehousing projects.

What is Data Cleansing?
The elevator pitch: "Data cleansing ensures that undecipherable data does not enter the data warehouse. Undecipherable data will affect reports generated from the data warehouse via OLAP, Data Mining and KPI's."
A very simple example of where data cleansing would be utilized is how dates are stored in separate applications. Example: 11th March 2007 can be stored as '03/11/07' or '11/03/07' among other formats. A data warehousing project would require the different date formats to be transformed to a uniform standard before being entered in the data warehouse.

Why Extract, Transform and Load (ETL)?
Extract, Transform and Load (ETL) refers to a category of tools that can assist in ensuring that data is cleansed, i.e. conforms to a standard, before being entered into the data warehouse. Vendor supplied ETL tools are considerably more easy to utilized for managing data cleansing on an ongoing basis. ETL sits in front of the data warehouse, listening for incoming data. If it comes across data that it has been programmed to transform, it will make the change before loading the data into the data warehouse.
ETL tools can also be utilized to extract data from remote databases either through automatically scheduled events or via manual intervention. There are alternatives to purchasing ETL tools and that will depend on the complexity and budget for your project. Database Administrators (DBAs) can write scripts to perform ETL functionality which can usually suffice for smaller projects. Microsoft's SQL Server comes with a free ETL tool called Data Transforming Service (DTS). DTS is pretty good for a free tool but it does has limitations especially in the ongoing administration of data cleansing.
Example of ETL vendors are Data Mirror, Oracle, IBM, Cognos and SAS. As with all product selections, list what you think you would require from an ETL tool before approaching a vendor. It may be worthwhile to obtain the services of consultants that can assist with the requirements analysis for product selection.

Figure 1. ETL sits in front of Data Warehouses

How important is Data Cleansing and ETL to the success of Data Warehousing Projects?
ETL is often out-of-sight and out-of-mind if the data warehouse is producing the results that match stakeholders expectations. As a results ETL has been dubbed the silent killer of data warehousing projects. Most data warehousing projects experience delays and budget overruns due to unforeseen circumstances relating to data cleansing.
How to Plan for Data Cleansing?
It is important is start mapping out the data that will be entered into the data warehouse as early as possible. This may change as the project matures but the documentation trail will come in extremely valuable as you will need to obtain commitments from data owners that they will not change data formats without prior notice.
Create a list of data that will require Extracting, Transforming and Loading. Create a separate list for data that has a higher likelihood of changing formats. Decide on whether you need to purchase ETL tools and set aside an overall budget. Obtain advice from experts in the field and evaluate if the product fits into the overall technical hierarchy of your organization.

Data Warehousing ETL tutorial

The ETL and Data Warehousing tutorial is organized into lessons representing various business intelligence scenarios, each of which describes a typical data warehousing challenge.
This guide might be considered as an ETL process and Data Warehousing knowledge base with a series of examples illustrating how to manage and implement the ETL process in a data warehouse environment.

The purpose of this tutorial is to outline and analyze the most widely encountered real life datawarehousing problems and challenges that need to be taken during the design and architecture phases of a successful data warehouse project deployment.

Going through the sample implementations of the business scenarios is also a good way to compare Business Intelligence and ETL tools and get to know the different approaches to designing the data integration process. This also gives an idea and helps identify strong and weak points of various ETL and data warehousing applications.

This tutorial shows how to use the following BI, ETL and datawarehousing tools: Datastage, SAS, Pentaho, Cognos and Teradata.
Data Warehousing & ETL Tutorial lessons

    Surrogate key generation example which includes information on business keys and surrogate keys and shows how to design an ETL process to manage surrogate keys in a data warehouse environment. Sample design in Pentaho Data Integration
    Header and trailer processing - considerations on processing files arranged in blocks consisting of a header record, body items and a trailer. This type of files usually come from mainframes, also it applies to EDI and EPIC files. Solution examples in Datastage, SAS and Pentaho Data Integration
    Loading customers - a data extract is placed on an FTP server. It is copied to an ETL server and loaded into the data warehouse. Sample loading in Teradata MultiLoad
    Data allocation ETL process case study for allocating data. Examples in Pentaho Data Integration and Cognos PowerPlay
    Data masking and scambling algorithms and ETL deployments. Sample Kettle implementation
    Site traffic analysis - a guide to creating a data warehouse with data marts for website traffic analysis and reporting. Sample design in Pentaho Kettle
    Data Quality - ETL process design aimed to test and cleanse data in a Data Warehouse. Sample outline in PDI
    XML ETL processing

Online Analytical Processing (OLAP) and Business

There are many business intelligence tools available to help make sense of the huge volumes of data that the average business must process. One of the most commonly used methods is Online Analytical Processing, also known as OLAP.
OLAP is only one kind of data analysis method but it is particularly useful in certain business circumstances such as process management, forecasting, budgeting, marketing and financial reporting among other areas. One of its defining features is that the databases allow complex queries that return results quickly.
OLAP systems run on the OLAP cube concept and have the potential to return results from queries in a fraction of the time taken by other methods. There are a number of different types of OLAP system.
    Multidimensional - MOLAP
    This is also simply known as OLAP and uses databases whose structures are optimized in advance for certain functionalities. Advantages include the fact that query results are returned very quickly and data can be stored using much smaller space than relational databases.
    Relational - ROLAP
    ROLAP differs from MOLAP in that it does not require any pre-computation, nor does it need to store any information. All information is accessed directly from the relational database. It can use standard query language (SQL) queries to request certain information from the database and present it to the end user. One of the main advantages of ROLAP is that it can handle very large volumes of data. Also, because data is stored on a standard relational database it can be accessed by SQL queries and presented with normal reporting tools.
    Hybrid - HOLAP
    There is no exact consensus on what a HOLAP is precisely, but as you might imagine it combines features of both multidimensional and relational OLAPs. In this scenario, some of the data will be kept in the MOLAP store and the rest in the ROLAP store. It is up to the designer how much and what kind of data is kept in each, and how it is partitioned.
    These are the three main types of OLAPs you will encounter. However, you may also hear of the following:
          o WOLAP - Web-based OLAP
          o DOLAP - Desktop OLAP
          o RTOLAP - Real-Time OLAP
          o SOLAP - Spatial OLAP
    There are a range of OLAP products on the market today, however the most commonly used by far is Microsoft Analysis Services, which has been available since 1998. There are also a number of other commercial offerings like Hyperion and Cognos. Since 2000, a number of open source products have emerged including Palo and Mondrian.

SOURCE:http://www.relativitycorp.com/data/article1.html

Data Warehousing Concepts

The IBM Cognos tool, Decision Stream or by it's latest version name Data Manager, is one of the ETL development tools available to support data delivery. Data Manager has all of the functionality needed to extract the data from the source system, create the necessary programming to transform the data, and deliver it to the warehouse.

Extract the source data

Once the BI analyst has worked with the business user to determine the requirements necessary, an ETL programmer begins by writing a SQL statement against the source system(s) to gather all the required elements. It is a best practice to simply pull the data from the source system without attempting to make any changes to it. In the extract process, the best performance will be from a simple select statement. The Decision Stream tool calls this the data stream.

Transformations of extracted data

The temporary work table has all the data elements required to support the transformation step. The transformation step is the "meat and potatoes" of building a structured data warehouse. All of the elements extracted from the source system(s) are used in this step. The Decision Stream tool has derivations, dimensional lookups, and functions, to provide the programmer with all the tools needed to transform the data to meet the business requirements. The tool calls this the transformation model.

A derivation is a calculation type of operation. An example would be adding 19000000 to dates that are in the CYYMMDD format or multiplying the quantity by the unit price to get a total gross price. There are many supported calculation types within a Decision Stream derivation. This include but are not limited to date math, type conversion, contol structures such as if..then..else, and boolean operators.

Dimensional lookups are used to create a link to the necessary control files. An example would be using the transaction date and the transaction currency to include the active foreign exchange rate for the transaction. Dimensional lookups often use the actual value of the key data to bring back the surrogate identification of the record containing the key data in the master or control files.

A function does everything else. Often a function is used at the beginning of the process to get literal or variable data used within the process or at the end to validate the transformations were successful. An example of this would be to go get the beginning and ending date ranges used in the current process, then update those date ranges at the end of the transformation. Generating the total extracted against the total transformed is another common function.

Load the final delivery table(s)

Lastly the delivery module provides the method for the data to be delivered to a table or multiple tables. While this seems like an afterthought, it is important to structure the columns so that the most used columns are at the beginning of the delivery. It is also a best practice to avoid delivering transition data. A field that is only used as a source (the date in CYYMMDD format) should not be delivered to the final table.

Utilizing the IBM Cognos Decision Stream tool gives the ETL developer all the necessary tools to gather, manipulate, and deliver the data needed to meet the reporting needs of the business.

Sources:
IBM Cognos Decision Stream

Ralph Kimball