ETL: Creating Data For BI - Transformation and Loading

Putting our transactional data in a format that will be suitable for use in business intelligence involves three sets of operations, in sequential order. First, we extract the data from the transactional system or systems. Next, we transform the extracted data to prepare it for consumption. Finally, we load the transformed data into the data warehouse or BI database. BI professionals refer to these operations in total as "Extraction, Transformation, and Loading," or simply “ETL.” This article discusses Transformation and Loading.

Transformation

Once the data are extracted from the transaction system, they typically need to be transformed to prepare them for business intelligence use. Some of the more common transformation operations include the following (to mention just a few):

• De-duplication: Find and remove duplications of customers, products, etc. This is critical if you’re pulling data from two or more transaction systems, as in the case discussed in an earlier article where you have a brick-and-mortar store and an e-commerce store that aren’t integrated together. Speaking of which:

• Merging: When we have data from multiple systems we also need to combine them so that we have a single view across the whole business

• Data conversions: These might include things like currency conversions, converting English to Metric measures or the other way around, and so on.

• Data cleansing: No matter how careful you are in entering data into the transaction system, there will inevitably be errors that no one catches. A well-designed ETL system can be your best defense against such errors slipping through and distorting your view of the business.

• De-normalization: Preparing the data for going from snowflake to star schema. Sometimes this will be done as part of the extraction step.

Loading

Having been extracted and transformed, the data are now loaded into the data warehouse or BI database. Depending on the kind of database you’re using to do BI, there may be other operations that occur during or after this step, such as aggregations. By aggregation we mean that we summarize or “roll up” the data depending on the dimensions in the database so that we can later “drill down” from higher to lower levels of detail.

ETL: Creating Data For BI - Extraction

Putting our transactional data in a format that will be suitable for use in business intelligence (BI) involves three sets of operations, in sequential order. First, we extract the data from the transactional system or systems. Next, we transform the extracted data to prepare it for consumption. Finally, we load the transformed data into the data warehouse or BI database. BI professionals refer to these operations in total as "Extraction, Transformation, and Loading," or simply “ETL.” This article discusses Extraction.

The first step in processing is to extract data from the database where our transaction records are kept. Because we want to minimize any interference with daily business operations that use the transactional system, we typically perform our processing outside of normal business hours. A database administrator (DBA), data architect (DA), or someone else responsible for business intelligence would begin by creating a query that pulls the needed data from the transactional database. With today’s computer systems it’s relatively easy for a system administrator to then create an automated process that runs the query late at night.

Larger companies may use software tools developed especially for ETL that incorporates the extraction query into the larger process. Some of the better-known competitors in this market include Informatica, Ab Initio, and International Business Machine (IBM) Inc.’s DataStage. Depending on the level of sophistication of the ETL process the data that are extracted may be saved into a text file or may be pulled directly into a separate data store for transformation. Sometimes, by clever use of Structured Query Language (SQL), data records can be partially transformed as they are extracted.