IBM Rational Insight extract, transform, and load framework

Extract, transform, and load (ETL) is a process in data warehousing that involves extracting data from outside sources, transforming it to fit business needs, and ultimately loading it into the data warehouse.

You can use the ETL features of IBM® Rational® Insight (Rational Insight) to extract data from various sources, transform that data through encoded business rules, and load the transformed data into a data mart. During this process, operational data is retrieved from the product source, normalized, and mapped to fact and dimension tables. The component that performs the core ETL process is the IBM Cognos® Data Manager (Data Manager).

Extraction

The ETL framework accepts extracts data through direct database access and through ODBC drivers, including the Rational Insight XML ODBC driver. An organization stores data in traditional relational databases or in other source formats (such as XML). Since Data Manager supports data in tabular format, incoming XML must be converted to relational form prior to the core ETL extraction process. This is done by IBM Rational Insight XML data configuration (XML data configuration), which maps data from XML sources and (using the XML ODBC driver), passes it to Data Manager.

Transformation

The next step is to transform the data using business rules. This is done in a two-step process.
  1. Normalize the data and modify using business rules. These business rules, when executed, perform business logic such as calculating or deriving a column based on other columns.
  2. Store it into physical tables. These physical tables implement the star schema convention, and add dimensions and facts – the characteristics elements of a data warehouse.

Loading

The last step is to load this transformed data into the data mart, a separate area of the warehouse.

Implementation

In Data Manager, each piece of ETL process is a fact or dimension build. The builds can be organized into job streams for different sets of data or tables. The job streams can be executed in the Data Manager directly, or published as a data move task, and then scheduled for execution in the IBM Rational Insight report server. The primary ETL builds are normalized form builds, non-entity builds, look-up builds, dimension builds, and fact builds. Insight implements and supports sample ETL transactions for products such as Rational ClearQuest®, Rational ClearCase®, Rational RequisitePro®, Microsoft® Project, Rational Team Concert, Rational Quality Manager, and Rational TestManager.


Feedback