Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
[ Team LiB ]
Designing the ETL Process Flow
The Source-to-Target Mapping Document
Before the ETL process flow can be designed (or enhanced), the detailed ETL transformation specifications for
data extraction, transformation, and reconciliation have to be developed, given that they will dictate the process
flow. A common way to document the ETL transformation specifications is in a source-to-target mapping
document, which can be a matrix or a spreadsheet (Table 9.3).
The source-to-target mapping document should list all BI tables and columns and their data types and lengths. It
should map the applicable source data elements to the columns, along with their data types and lengths, and it
should show the source files and source databases from which the source data elements are being extracted.
Finally, and most importantly, the document should specify the transformation logic for each column.
This document can then be used to create the actual programming specifications for the ETL developers or to
create instructions (technical meta data) for the ETL tool.
The ETL Process Flow Diagram
Once the source-to-target mapping document has been completed, the ETL lead developer, with the help of the
database administrator and the data quality analyst, must design the ETL process flow, as illustrated by the
example in Figure 9.5.
Figure 9.5. Sample ETL Process Flow Diagram
The purpose for the ETL process flow diagram is to show the process dependencies between all extracts, sorting
and merging utilities, transformations, temporary work files or tables, error-handling processes, reconciliation
activities, and the load sequence. The ETL programs, or ETL tool modules, will have to run in this sequence.
Extracts: There may be operational interdependencies among several source files and source databases
from which data is being extracted. These interdependencies have to be understood because they may affect
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
291/631
the timing and the sequence of running the ETL extract programs.
Sorting and merging utilities: Almost every step requires the extracted data to be sorted in a particular
fashion so that it can be merged before further processing can occur. Sorting can also greatly improve load
performance.
Table 9.3. Example of a Source-to-Target Mapping Document
Table
Column
Type/Length
Data
Element
Type/Length
Source
File/Database
Transformation
Specifications
CUSTOMER
CUSTOMER_ID
INTEGER
CUST-NUM
CID
CNO
X(9)
INT
X(12)
Cust—Master
CUSTOMER
CUST
If ABC-Code = "R" then
Assign new customer
number to record
with CUST-NUM
Else
If ABC-Code = "S"
Assign new customer
number to record
with CID
Else
Assign new customer
number to record
with CNO
Transformations: Most of the data has to be transformed for a variety of reasons. It is important to
examine the most opportune times to perform the transformations. Remember that there is only one
coordinated ETL process for the BI decision-support environment. Therefore, transformations applicable to
all source data, such as data type and code conversions or data domain enforcements, should be performed
early in the ETL process flow. Transformations specific to a target database, such as aggregation and
summarization for a specific data mart, should occur toward the end of the ETL process flow.
Temporary work files or tables: Sorting, merging, and transforming require a lot of temporary storage
space to hold the interim results. These temporary work files and tables can be as large or larger than the
original extracts. Furthermore, these temporary work files and tables are not really "temporary." Plan to
have that space available for your staging area permanently.
Error-handling processes: During the ETL process many errors are detected as the data-cleansing
specifications are applied to the source data. If error reports are created or erroneous records are rejected
into a suspense file, they should be shown on the ETL process flow diagram.
Reconciliation activities: Every program module that manipulates data should produce reconciliation
totals. This can be in the form of input and output record counts, specific domain counts, or amount counts.
Record counts are sufficient for extract, sort, and merge modules. Domain counts are appropriate for more
complicated transformation specifications, such as separating data values from one source data element into
multiple target columns. Amount counts are usually performed on all amount data elements, whether they
are moved as is, transformed into a new format, or used in a calculation. (ETL reconciliation will be
described in more detail in Step 11, ETL Development.)
Load sequence: It is necessary to determine the sequence in which the tables have to be loaded because of
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
292/631
their potential interdependencies and because of a possible recursive relationship on one table. For example,
the Product dimension table may have to be loaded before the Sales table is loaded, if RI is turned on and if
the sales data references products. Other tables may be loaded simultaneously, which can greatly improve
the speed of the load process.
The Staging Area
A staging area is the place where the ETL process runs. It refers to dedicated disk space, ETL program libraries,
temporary and permanent work files and tables—even a dedicated server. The staging area can be centralized or
decentralized. For example, it can be a central mainframe staging area if most of the source data is in flat files on
a mainframe. It can also be on a dedicated server onto which the source data is loaded. Many times the staging
area is decentralized. For example, a convoluted mainframe file with many redefines and occurs clauses may
have to be flattened out with a COBOL program in a staging area on the mainframe before it is downloaded to a
staging area on a UNIX box for further processing by an ETL tool.
The ETL process is by far the most complicated process to be designed and developed in any BI project. Since
there is only one (logical) coordinated ETL process for the BI decision-support environment, expanding the ETL
programs with each new BI application becomes very complicated, and regression testing requires more and
more time. For these reasons, most organizations prefer to use an ETL tool for all or some of the ETL process,
especially for the extract and transformation processes, as shown in Figure 9.6.
Figure 9.6. Common Use of ETL Tools in the ETL Process
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
293/631
[ Team LiB ]
Evaluating ETL Tools
When using an ETL tool, the transformation specifications get translated into
instructions for the ETL tool. These instructions can then be stored as technical meta
data in the meta data repository. Expanding the ETL process and running regression
tests are made easier with the tool because there is less human intervention and
therefore fewer chances to introduce errors.
When evaluating ETL products, follow these steps.
Perform a cost-benefit analysis to compare licensing (buying) an ETL product
with building the ETL process in-house. Although both options can be expensive
for different reasons (licensing a very sophisticated ETL tool is expensive, but so
is maintaining custom-built software), your first choice should be licensing an
ETL tool. If the ETL tool cannot handle all of the required transformations,
supplement the licensed product with your own specialized code for those
transformations. If your transformation requirements are simple and you have
a small budget, you may want to buy a less sophisticated ETL tool or consider
building your own ETL process.
1.
Compile a list of ETL products and vendors that are likely to meet your
requirements. Attend trade shows to learn more about the products and
vendors. Caveat emptor—be a cautious and skeptical buyer.
Let your transformation and cleansing
requirements—not vendor hype—drive your product
evaluation and selection process.
2.
Compare the ETL products and vendors to your weighted data transformation
requirements. Include the business rules for data cleansing as part of your ETL
tool selection criteria. For example, some ETL tools cannot read flat files and
cannot perform some of the very complicated transformations. If you need
those capabilities, you must be aware of ETL tool limitations because you may
need to license an additional data-cleansing tool to perform those processes, or
augment the ETL tool functionality with custom-written code.
3.
Evaluate each ETL product objectively and prepare a scorecard that compares
the product features and their effectiveness. The reputation and responsiveness
of a vendor are equally as important as the features of the products. Therefore,
prepare another scorecard comparing the vendors.
4.
Check the vendors' client references by talking with people at organizations that
already use the tools you are considering. This is the most cost-effective and
informative way to evaluate the tools.
5.
Narrow the list of ETL products and vendors to a short list of two or three
candidates. Otherwise, you will waste too much time comparing all the
products, and it may take "forever" to make the final decision. You might then
select an inferior tool just to end the frustration and delay.
6.
Arrange for product demos since "seeing is believing." Spend some time
preparing the test cases so that all vendors on the short list get to demonstrate
the performance and effectiveness of their products using the same test cases.
7.
Test the vendor products even though it takes away time from your project8.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
294/631
schedule. Testing is the best way to discover any glitches that may happen
before using a vendor product in production. Try to negotiate a 30-day trial
period.
8.
(The process of evaluating and selecting products and vendors is described in more
detail in Step 10, Meta Data Repository Design.)
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
295/631
[ Team LiB ]
ETL Design Activities
The activities for ETL design do not need to be performed linearly. Figure 9.7
indicates which activities can be performed concurrently. The list below briefly
describes the activities associated with Step 9, ETL Design.
Create the source-to-target mapping document.
Use the source data analysis results and the business rules from the previous
steps and incorporate them into the transformation specifications. Document
the transformation specifications in a source-to-target mapping matrix or
spreadsheet.
1.
Test the ETL tool functions.
It is very important to test the ETL tool functions before designing the ETL
process flow and before deciding how to set up the staging area. For example,
it would be worthless to install a currently popular ETL tool that cannot read flat
files on a mainframe if 90 percent of your source data is in flat files on your
mainframe. Therefore, test the ETL tool functions and determine whether
supplemental code must be written to perform some complicated and lengthy
transformations that the tool cannot handle.
2.
Design the ETL process flow.
The most challenging aspect of ETL design is creating an efficient ETL process
flow. Because most data staging windows are very small—only a few hours per
night—the ETL process must be streamlined as much as possible. That means
breaking the ETL process into small program components so that as many as
possible can be run in parallel.
3.
Design the ETL programs.
Since most organizations require several years of historical data to be loaded
with the first BI application release, there are three sets of ETL programs to
consider: the initial load, the historical load, and the incremental load. The
incremental load will probably be a delta load and will therefore be the most
complicated to design. Modularize the ETL programs as much as possible, and
create programming specifications for each ETL program module.
4.
Set up the ETL staging area.
Determine whether you need a centralized staging area on a dedicated server
or whether it would make more sense to implement a decentralized staging
area in your environment. Deciding factors are the type and location of source
files and source databases, as well as the functions, capabilities, and licensing
terms of the ETL tool.
5.
Figure 9.7. ETL Design Activities
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
296/631
Do not create a separate staging area for each data mart.
A decentralized, coordinated staging area is not the same
thing as separate, uncoordinated staging areas for
different BI target databases and different BI
applications.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
297/631
[ Team LiB ]
Deliverables Resulting from These Activities
Source-to-target mapping document
This document contains the transformation specifications for each BI column,
including instructions for data cleansing, RI checking, reconciliation, and error
handling, as well as algorithms for aggregations and summarizations.
1.
ETL process flow diagram
The ETL process flow diagram shows the process sequence and the process
dependencies among all ETL process components, such as program modules,
temporary and permanent work files and tables, and sort, merge, and load
utilities.
2.
ETL program design document
This document is created from the source-to-target mapping document after
the ETL process flow has been determined. It contains the actual programming
specifications for every ETL program module for the initial load, historical load,
and incremental load. Portions of this document will be given to different ETL
developers to code the program modules.
3.
Staging area
The staging area should contain program libraries with version control as well
as dedicated space for temporary and permanent work files and tables.
4.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
298/631
[ Team LiB ]
Roles Involved in These Activities
Data quality analyst
Working with the ETL lead developer, the data quality analyst must transfer his
or her knowledge about the condition of the source files and source databases
discovered during Step 5, Data Analysis. Since the data quality analyst usually
has a systems analysis background, he or she can assist in or even take over
creating the source-to-target mapping document.
Database administrator
The database administrator must be involved in the design of the ETL process
because of its database aspects (RI, indexing, clustering, and the use of the
DBMS load utility). The database administrator can provide valuable input to
the ETL process flow and can sometimes reduce the ETL staging window by
several hours.
ETL lead developer
The ETL lead developer is responsible for the entire ETL process. With the help
of the database administrator, the data quality analyst, and the subject matter
expert, the ETL lead developer designs the ETL process flow and creates the ETL
program design document with the actual programming specifications for the
ETL developers (or instructions for the ETL tool).
Subject matter expert
The subject matter expert plays an advisory role during this step. Since the
subject matter expert was involved in identifying the source data and finding
the data quality problems, he or she should participate in creating the source-
to-target mapping document. The subject matter expert will also be the liaison
to the business representative who must validate the business rules used in the
ETL process.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
299/631
[ Team LiB ]
Risks of Not Performing Step 9
This is not an optional step—not even if you plan to use an ETL tool. Every BI project
team must evaluate the source data and figure out how to improve it, change it,
standardize it, and make it more useful before moving it into the BI target
databases. A BI project is not like a systems conversion project, where you simply
try to move from one technology platform to another while transferring the data as
is. A BI project is more like a system redesign or a business process improvement
project, where you want to change the data. You cannot afford to move data as is
from source to target and then wait for the database to reject a data element for
technical reasons. You must plan and design the required changes for business
reasons.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
300/631