Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
Chapter Overview
This chapter covers the following topics:
Things to consider about extract/transform/load (ETL) development
Common data transformation activities and the chronic underestimating of data
transformation efforts
Three types of reconciliation totals that should be produced by the ETL process:
record counts, domain counts, and amount counts
Peer reviews
Six applicable ETL testing procedures: unit testing, integration testing,
regression testing, performance testing, quality assurance (QA) testing, and
acceptance testing
How to create a formal test plan
Brief descriptions of the activities involved in ETL development, the deliverables
resulting from those activities, and the roles involved
The risks of not performing Step 11
Things to Consider
Source Data Extracts
Who will write the ETL programs? Have those developers written
ETL programs before? Do they understand the ETL process?
Do ETL programs already exist from a previous release or another
BI application? How many of them have to be expanded?
Can we ask the programmers of the operational systems to give us
the extract files, or do we have to get the source data ourselves?
What do we need to know about the operational systems before we
can get the data? What operational programs have to finish running
before we can extract the data from the source files and source
databases?
ETL Tool
Have we worked with this ETL tool before, or is it new to us?
Has the ETL team been sufficiently trained on the ETL tool?
Can the ETL tool perform all the required transformations, or will
we have to write some custom code? In what language (C++,
COBOL)?
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
331/631
ETL Process Dependencies
What are the dependencies among program modules? In what
sequence do we have to run our ETL programs (or the ETL tool
modules)?
How many program modules can we run in parallel?
What are the dependencies among the tables? Do some tables have
to be loaded before others?
How many tables can we load in parallel?
Testing
Will we conduct peer reviews? Are we using extreme programming
(XP) techniques?
How many testers will we have on the project?
Will the subject matter expert and business representative
participate in testing?
Who will be the testing coordinator? Who will log the test results
and maintain the test log?
What type of testing do we need to perform? Integration or
regression testing? Performance testing? QA testing? Acceptance
testing?
Which business people will participate in acceptance testing? Only
the business representative? The subject matter expert? Other
business people?
Technical Considerations
What technical platform issues do we have to take into
consideration?
How is the staging area set up? On a dedicated server?
Will the ETL process be split between the mainframe and one or
more servers?
What environments does the ETL tool run in?
What type of middleware do we need?
The use of ETL tools has become very widespread, but organizations that use them
discover very quickly that these tools have their limitations. Depending on the
complexity of the required source data transformations and on the age and condition
of the source files, custom code often has to be written to augment the ETL tool
functionality.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
332/631
[ Team LiB ]
Source Data Transformation
The technical rules and the business rules for the required source data
transformations were accumulated and defined throughout the steps of project
planning, project requirements definition, data analysis, application prototyping, and
meta data repository analysis. During those steps, the rules were probably extracted
from old manuals, old memos, e-mails, programs (operational and decision
support), and computer-aided software engineering (CASE) tools and provided by
people who remember when and why a business rule was created. These rules are
now reflected as data transformation activities in the ETL process.
Data Transformation Activities
BI projects present the best opportunity to eliminate dead and useless data because
it allows the business people to see their information requirements in a different
light. When properly implemented, the data transformation activities of cleansing,
summarization, derivation, aggregation, and integration will produce data that is
clean, condensed, new, complete, and standardized, respectively (Figure 11.1).
Figure 11.1. Data Transformation Activities
Cleansing: By definition, cleansing is a BI transformation process in which
source data that violates the business rules is changed to conform to those
rules. Cleansing is usually accomplished through edits in the ETL programs,
which use table lookups and program logic to determine or derive the correct
data values and then write those data values into the load files used to populate
the BI target databases.
Summarization: Numeric values are summarized to obtain total figures
(amounts or counts), which can then be stored as business facts in
multidimensional fact tables. Summary totals can be calculated and stored at
multiple levels (e.g., departmental summary of sales, regional summary of
sales, and total sales by country).
Derivation: During this process, new data is created from existing atomic
(detailed) source data. Derivation is typically accomplished by calculations,
table lookups, or program logic. Examples include the following:
- Generating a new code for classifying customers based on a certain
combination of existing data values
- Calculating profit from income and expense items
- Appending the last four digits of a ZIP code based on the address in a
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
333/631
postal lookup table
- Calculating a customer's age based on his or her date of birth and the
current year
Aggregation: All the data about a business object is brought together. For
example, data elements for a customer may be aggregated from multiple
source files and source databases, such as a Customer Master file, a Prospect
file, a Sales file, and demographic data purchased from a vendor. (In
multidimensional database design jargon, the term aggregation also refers to
the roll-up of data values.)
Integration: Data integration based on normalization rules forces the need to
reconcile different data names and different data values for the same data
element. The desired result is to have each unique data element known by one
standard name, with one standard definition and an approved domain. Each
data element should also be associated with its sources files and source
databases as well as its BI target databases. Standardizing the data should be
a business objective.
Underestimating Data Transformation Efforts
Source data transformation is similar to opening a Russian doll—you open one and
there is another inside. It could be an endless process. That is why the time required
for the ETL process is chronically underestimated. The original estimates are usually
based on the amount of technical data conversions required to transform data types
and lengths, and they often do not take into account the overwhelming amount of
transformations required to enforce business data domain rules and business data
integrity rules.
The transformation specifications given to the ETL developer should never be limited
to just technical data conversion rules. For some large organizations with many old
file structures, the ratio of a particular data transformation effort could be as high as
80 percent effort toward enforcing business data domain rules and business data
integrity rules and only 20 percent effort toward enforcing technical data conversion
rules. Therefore, expect to multiply your original estimates for your ETL data
transformation effort by four. Even if you think you have a very realistic timetable for
the ETL process, do not be surprised if you still miss deadlines due to dirty data. If
you do not miss deadlines, do not be surprised to discover you have not cleansed
enough of the data sufficiently.
Insist on full-time involvement from the business representative, and insist on
getting the right business representative—someone who is knowledgeable about the
business and who has authority to make decisions about the business rules. These
stipulations are essential for speeding up the ETL process. Furthermore, urge the
business sponsor and the business representative to launch a data quality initiative
in the organization, or at least in the departments under their control or influence.
When business people drive a data quality initiative, they are more likely to assist
with the ETL transformation process. Remind them that while IT technicians may
know the process semantics, the business people know the data contents and
business semantics. They understand what the data really means.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
334/631
[ Team LiB ]
Reconciliation
One of the most common complaints about BI applications is that the data in the BI
target databases does not match the data in the source systems. As a result,
business people often do not trust the BI data. Ironically, most of the time the data
in the BI target databases is more accurate than the data in the operational source
files or source databases because the data has been reformatted, standardized, and
cleansed. However, without proof this trust cannot be restored. Reconciliation totals
provide that proof and must be available as meta data in the meta data repository.
ETL reconciliation totals are ETL process control totals, not operational reconciliation
totals back to the organization's financial statement or general ledger. The purpose
for ETL reconciliation totals is to ensure that all the data values going into the ETL
process can be reconciled with all the data values coming out of the ETL process.
Storing ETL reconciliation totals, as well as data quality and load statistics, as meta
data highlights the importance of providing a meta data repository. This meta data is
crucial information for the business people who want to see which data was loaded,
which data was rejected and for what reasons, and what the data reliability
(cleanliness) factor is for the BI data after each load cycle. For example, source data
from multiple source files and source databases may not have been properly
synchronized (a timeliness error), which may have caused inconsistencies in analysis
results. With load statistics available as meta data, the business analysts can quickly
recognize and reconcile the problem.
Calculating Reconciliation Totals
Far too many BI projects do not employ the good IT practices commonly enforced in
operational systems. Believing the notion that BI applications are "just" for decision
support and are therefore less critical than operational systems is a grave mistake.
BI applications are as critical as operational systems because the decisions that are
made based on the data in the BI decision-support environment can affect an
organization's strategic direction and vitality.
One time-proven discipline when manipulating data, whether changing the data in
some way or copying it or moving it from one place to another, is to reconcile every
new output to its old input. Every program or program module that reads data and
then writes it to a new file, even if only into a temporary file, must produce
reconciliation totals.
There are three types of reconciliation totals: record counts, domain counts, and
amount counts.
Record Counts
One of the most fundamental reconciliation totals is a simple count of records read
and records written. If records are rejected because they fail an edit check in the ETL
process, the number of records rejected must also be counted. The total count of
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
335/631
records written and records rejected must equal the count of records read (Figure
11.2).
Figure 11.2. Record Counts
Domain Counts
Domain counts involve counting the number of records for each unique domain (data
value) of an input field and counting the number of records for the same unique
domain on the output file. The complication with domain counts enters when an
"overloaded" source data element must be split into multiple columns. An overloaded
source data element is a data element used for multiple purposes, as many of the
one-byte and two-byte code fields are in operational systems. The domain of an
overloaded data element describes not one business object but many business
objects and must therefore be separated into different columns for different tables.
(Business objects are usually implemented as dimension tables.) In that case, the
total of the multiple domain counts on the output side must equal the one domain
count on the input side. If data values were rejected because they did not pass the
data quality edits, there would be an additional count of all the rejected records.
Thus, the total of the multiple output domain counts and the rejected data values
count must equal the input domain count (Figure 11.3).
Figure 11.3. Domain Counts
Amount Counts
Even more important than domain counts are amount counts. They are the primary
mechanism for reconciling source files and source databases to the BI target
databases. Reconciling amounts happens in two ways. One is to summarize every
amount field in every input file and every corresponding amount field in every output
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
336/631
file. If records were rejected, there would be a third summary for the total amount
figure rejected. The combined total of the output amount total and the rejected
amount total must equal the input amount total.
A more complicated amount reconciliation algorithm must be developed if the
incoming amount field is an overloaded source data element that has to be separated
into several different amount columns for the BI target databases. In that case, the
selection and edit criteria in the transformation specifications must be used to create
multiple output amount totals. In addition, the same selection and edit criteria must
be run against the input file to produce the same multiple amount totals for
verification (Figure 11.4).
Figure 11.4. Amount Counts
Storing Reconciliation Statistics
Because of the extensive amount of transformation and cleansing that typically
occurs in the ETL process, the business people should expect the data to be different
on the BI target databases than in the original source files and source databases.
Their desire or need to reconcile the data between source and target is valid.
However, it must be accomplished through using reconciliation totals, not by
comparing source data elements to target columns dollar for dollar.
Therefore, all reconciliation totals produced by every program module for each BI
application load cycle must be stored as meta data in the meta data repository. If
possible, additional categorization of rejected records should be considered with
short descriptions for the rejection reason. These descriptions can be composed
based on the ETL edit criteria.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
337/631
[ Team LiB ]
Peer Reviews
Peer reviews are similar to the "pair programming" concept of extreme programming
(XP), except that the coding itself does not occur in pairs, the meetings often involve
more than two people, and the reviews apply to all types of task deliverables besides
programs. Peer reviews are informal meetings that combine reviewing, testing, and
a limited amount of brainstorming about a project task deliverable. The purpose of
peer reviews is to allow the developer of a given piece of project work to present a
task deliverable to his or her peers for validation or discussion. This is best
accomplished in a core team setting, but a peer review can also involve team
members from the extended team who have a vested interest in that project task.
At the completion of a project task, the developer of the work (or any other core
team member) may decide to ask for a peer review to solicit input to a complex
problem, get feedback to a creative solution, or ask for opinions on an item that is
uncertain or poorly defined. He or she then determines which team members should
participate in the peer review, schedules the informal peer review meeting, and
distributes copies of documents (e.g., specifications, programs, data models,
reports) to the invited peers before the meeting.
The developer of the work leads the meeting. The project manager, who should
always participate in peer reviews, ensures that the meeting does not bog down in
disagreements nor stray from the issues at hand. Each attendee should have
reviewed the distributed documents and should be prepared to comment and to
brainstorm on the issues. Together, the participants are responsible for uncovering
errors or misconceptions in the task deliverable. Discussions are initiated to help the
developer understand the problems uncovered or to allow the developer to justify his
or her work to the satisfaction of all in attendance.
Brainstorming is usually limited to finding the errors or misconceptions; it does not
extend to solving them unless a solution is obvious and can be presented or
discussed within a few minutes. At the conclusion of a peer review the uncovered
errors or misconceptions should be documented, and the developer of the deliverable
is tasked with correcting them or with creating a new solution to the project task.
Peer reviews sound more formal than they really are. They can be best compared to
high-powered, structured brainstorming sessions. Peer review meetings should be
limited to about one hour.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
338/631
[ Team LiB ]
ETL Testing
Unfortunately, testing, like reconciliation, is often done very poorly on BI projects, if
at all. That is not acceptable—and neither is the excuse that "it can be fixed in the
next release." The next release will be even larger and more complicated, and it will
require more time to test. In other words, if it takes too long to test now, it will take
even longer to test later, which usually means that adequate testing is never done.
Rushing into deployment at the expense of testing is inexcusable, especially if it is
done to meet some artificial deadline.
The same types of testing that apply to operational systems also apply to BI
applications (Figure 11.5). The following sections briefly describe each type.
Figure 11.5. Types of Testing
Unit Testing
Unit testing refers to the testing of discrete program modules and scripts, not to
testing the flow or the links between the programs. Every developer must test his or
her program modules and scripts individually (if XP techniques are utilized, this is
done in pairs). There are three components to unit testing: compilation,
functionality, and edits.
Compilation: Obviously, each program module must compile successfully or it
cannot be implemented. There are many testing tools on the market for every
conceivable programming language. These tools allow the developer to trace
every step of the code as it is being executed, displaying the before and after
images of the data for each line of code.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
339/631
Functionality: Each program module must perform the functions for which it
was designed and must produce the expected test results. Therefore, each
developer must create a small test file with valid as well as invalid data to test
every function of his or her program modules. He or she must know in advance
what the program modules should do with the valid and the invalid test data
(expected test results). Unit testing is not completed until all program modules
produce all of the expected results.
Edits: Each program module must catch errors and, depending on the severity
of the error, either produce an error message or gracefully end the program. No
program should ever stop abruptly ("crash" or abend) with a cryptic system
error message. With the high degree of poor-quality data in the source files, it
is quite common that more lines of code are written for edits than for
functionality.
Consider allowing someone other than the developer to
test any given piece of code. This along with peer reviews
should snare most errors that may not be caught because
of pride of ownership and being too close to one's own
code.
If an ETL tool is used, unit testing still applies to the individual ETL tool modules. In
that case, you are testing the validity of your ETL instructions, that is, the ETL
technical meta data. It is important to note that if an ETL tool does not meet the
standards of your organization, you will have to supplement the tool with your own
custom-written code. In that case, unit testing will be a combination of testing the
ETL technical meta data, testing the custom-written code, and testing the
"handshake" between them.
Integration Testing
Integration testing, also known as system testing, is the first complete ETL process
run. This includes all three sets of ETL processes: the initial load, the historical load,
and the incremental load. Just because all program modules pass their individual
unit tests, it cannot be assumed that the entire ETL process will run smoothly. The
interactions and flow of all programs, as specified in the ETL process flow diagram,
must be observed and validated.
Interactions: Program modules receive data, manipulate it, and hand it off to
other program modules. This interaction between the modules must be tested.
The test data used for integration testing is different from that used for unit
testing. A copy of a relatively large subset of representative operational source
data is used for integration testing.
Flow: The ETL process flow diagram should indicate which programs must run
in which sequence, which programs can run in parallel, and where sort and
merge utilities are interjected. This flow must be tested for functionality and
efficiency. Testing for functionality ensures that the right process is performed
on the right data at the right time, that is, that the programs run in the correct
sequence. Testing for efficiency ensures that the entire ETL process can
complete within the expected time frame. If it cannot, the flow must be
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
340/631