Page 9 - DCAP606_BUSINESS_INTELLIGENCE
P. 9
Business Intelligence
Notes 1.2.2 Offline Extract, Transform and Load (ETL)
Early on, the one common interface that was provided between the disparate systems in an
association was magnetic tape. Tape formats were standardized, and any system could compose
tapes that could be read by other systems. Thus, the first data warehouses were fed by magnetic
tapes prepared by the various systems inside the association. However, that left the difficulty of
data disparity. The data written by the different systems reflected their native data associations.
Notes The data written to tape by one system often had little relation to the similar data
written by another system.
Even more important was that the data warehouse’s database was designed to support the
analytical functions needed for the business intelligence function. This database design was
typically a highly organised database with complex indices to support Online Analytical
Processing (OLAP). Databases configured for OLAP allowed complex analytical and ad hoc
queries with rapid execution time. The data fed to the data warehouse from the enterprise
systems was converted to a format significant to the data warehouse.
To explain the difficulty of initially stacking this data into a data warehouse, holding it updated,
and resolving discrepancies, Extract, Transform and Load (ETL) utilities were evolved. As their
name suggests, these utilities extract data from source databases, change/transform them into
the widespread data warehouse format, and load them into the data warehouse, as shown in
Figure 1.2.
Figure 1.2: Extract/Transform/Load Structure
ETL – Extract/Transofrm/Load
Source: http://www.gravic.com/shadowbase/images/uses/etl.png
The transform function is the key to the achievement of this approach. Its job is to request a
series of rules to extracted data so that it is properly formatted for loading into the data warehouse.
An example of transformation rules includes:
The selection of data to load.
The translation of encoded items (for example, 1 for male, 2 for female to M, F).
4 LOVELY PROFESSIONAL UNIVERSITY
