Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications

Подождите немного. Документ загружается.

redesigned, and the entire ETL process must be retested.

If an ETL tool is used, the entire ETL process must still be tested from beginning to

end, except that you are running the ETL tool processes instead of custom-written

programs.

The business representative and the subject matter expert should be involved in

integration testing. They are the first to know whether a particular run was

successful or not. This is also an excellent opportunity for some advanced training for

them, and it will go a long way toward allaying any future suspicions about the

accuracy and quality of the data in the BI target databases.

Integration testing, like unit testing, requires many test runs to remove all the

defects and to tune the flow. Every time the actual test results do not equal the

expected test results, the program producing the error must be corrected, and all

programs must be rerun. But unlike unit testing, integration testing is far too

complicated to be performed without a formal test plan, which should include a

description of the test cases and the sequence in which the programs should be

executed.

Regression Testing

The most complicated and most time-consuming of all types of testing is regression

testing. It is similar to integration testing, but this time the programs that are being

tested are not new. Since the BI decision-support environment is evolving and new

BI applications are added to the ETL process continuously, the project team will have

to perform extensive regression testing on all releases except the first one. With

every new BI release the ETL process has to be modified (enhanced) to extract more

data from the operational systems for the new BI application. The new BI application

may have a separate set of data access and analysis programs, but the ETL process

is shared.

The main goal of regression testing is to make sure that the modifications to existing

ETL programs did not inadvertently produce some errors that did not exist before. If

new programs were added to the ETL process flow, the new interactions between

programs must be tested, and any affected subsequent old programs must be

retested. If the ETL process flow had to be enhanced or redesigned to increase

efficiency, the entire ETL process has to be retested.

To develop a new test plan for every regression test

would be much too time-consuming. Thus, it is advisable

to save the original test plan and the original test data

created for integration testing of the first release. Then

enhance the original test plan for subsequent regression

tests with new programs, new data, and new test cases.

Performance Testing

Performance testing, also known as stress testing, is performed to predict system

behavior and performance. This is very similar to traditional performance testing on

operational systems, but BI performance testing is more complicated because of the

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

341/631

enormous volumes of data in the BI target databases. Since most organizations do

not have more than three to four hours left in their nightly batch windows, the goal

is to find out how much data can be processed during that time and how many

nights it will take to complete the entire ETL process.

Unlike integration testing or regression testing, performance testing does not have to

be performed on every program module. Performance testing could be limited to

only the most critical program modules with the highest volumes of data and the

longest runtimes. In addition to running physical tests, you can use stress test

simulation tools. These simulation tools allow you to describe the production

platform, including other programs running on the same server and sharing the

same space. Based on your input, the tools calculate and project estimated

performance numbers. It is highly recommended to run a simulation prior to actual

performance testing with real data.

Quality Assurance Testing

Most large organizations have strict procedures for moving an application into

production. These procedures usually include QA testing, and in most cases a

separate QA environment is established for such testing. Operations staff direct the

developers in moving databases and programs into the QA environment. Then all

operating instructions and scheduled jobs have to be turned over to the operations

staff for testing. They will go through a simulated production run before allowing the

application components to transfer to the production environment.

Acceptance Testing

Acceptance testing can be performed in one of two ways, depending on how testing

as a whole is set up. Ideally, there should be a separate acceptance test

environment, which could also be used for regression testing of future releases. With

a separate acceptance test environment, QA testing and acceptance testing could be

done at the same time. However, it may not be feasible or justifiable to maintain a

separate acceptance test environment. A simpler alternative is to perform acceptance

testing after QA testing in the same QA environment.

If the business representative actively participated during integration testing or

regression testing, there should be very few surprises during acceptance testing. In

fact, if the business representative is comfortable with the integration or regression

test results, and barring any unforeseen problems detected during QA testing,

separate acceptance testing may not be necessary at all. However, if a traditional

approach was followed in which the business representative was not involved in any

design or testing activities except for occasional reviews, acceptance testing is the

most important test of all.

Some project teams limit acceptance testing to the access and analysis portion of the

BI application and exclude the business representative from ETL testing. That is a big

mistake. When business analysts and business managers complain about incorrect

data in the BI target databases, the reason may not be that the report programs do

not work properly but that the ETL process is faulty. Therefore, testing how to get

the data into the BI target databases correctly is more important than testing how to

get it out correctly because an error in a report program is a lot easier to find and

correct than an error in the ETL process. Moreover, since the business representative

is involved in source data analysis and in providing the business rules for data

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

342/631

cleansing, it is only logical that he or she should test the ETL process that

implements those rules. The business representative should ask some of the

following questions.

Is the appropriate data being extracted?

If the source data element is split into multiple columns, is it done correctly

during the transformation process?

If some data elements are merged together, did any integrity problems result

from this transformation process?

Is the data loaded correctly into the appropriate BI target databases and the

appropriate BI tables?

Can the data in the BI target databases be reconciled with the source files and

source databases? Where are the reconciliation totals stored?

Are data values correctly transformed and cleansed? Is bad data slipping

through without notice?

Is the load performance adequate? And is the BI data available to the business

people when they expect it?

[ Team LiB ]

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

343/631

[ Team LiB ]

Formal Test Plan

With the possible exceptions of unit testing and performance testing, ETL testing sessions are

organized events that are guided and controlled by an agenda called a test plan. Each test plan

should specify the information illustrated in Figure 11.6.

Figure 11.6. Test Plan Components

Purpose: Describe in general terms what is being tested. For example:

"Data is extracted from the Customer Master file, Account Tran file, Sales History database,

and Product Master database. The extracted data from Customer Master, Account Tran, and

Sales History must be merged under Customer Number, and the extracted data from

Product Master and Sales History must be merged under Product Number. The ETL

programming specifications include 28 transformations and 46 cleansing algorithms. We

will run 20 test cases to test the transformations and 42 test cases to test the cleansing

algorithms."

Schedule: After reviewing the ETL process flow diagram and determining which programs

have to run in which sequence and which ones can run in parallel, every program in the job

stream must be scheduled to run in exactly that sequence on certain dates at certain times.

Test cases: The bulk of the test plan will be the list of test cases. It is important to have

the business representative participate in writing the test cases. Each test case specifies the

input criteria and the expected output results for each run. It also describes the program

logic to be performed and how the resulting data should look. For example:

"Submit module ETL3.3 using the T11Customer temporary VSAM file and the T11Product

temporary VSAM file. Both temporary files are sorted in descending order. Module ETL3.3

merges the two files and rejects records that do not match on Sale-Tran-Cd and Cust-Num.

All rejected records must trigger the error message: 'ETL3.3.e7 No match found on <print

out Cust-Num> and <print out Prd-Nbr> <print out system date-time stamp>.'"

Test log: A detailed audit trail must be kept of all test runs, itemizing the date and time the

programs ran, the program or program module numbers, who validated them, the expected

test results, the actual test results, whether the test was accepted or not, and any additional

comments. Table 11.1 shows a sample test log.

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

344/631

Remember that all programs in the ETL process are tested and

retested until the complete ETL process runs from beginning to end

as expected.

Table 11.1. Example of a Test Log

Date

Time

Program

Number

Tester

Expected Test

Results

Actual Test

Results

Accepted

(Yes/No)

Comments

8/25/2003

8:45

A.M.

ETL1.1v1

BLB

232,489 sold

loans

983,731

purchased

230,876 sold

loans

983,689

purchased

Yes

There were

1,655

rejected

loans.

Rejections

are valid

and

expected.

8/25/2003

8:45

A.M.

ETL3.1v1

JAL

$30,555,791.32

daily loan

balance

$33,498,231.11

daily loan

balance

Double-

counting

occurs on

ARM IV

loans

funded after

10/01/2003.

8/25/2003

9:10

A.M.

ETL1.2v1

BLB

SRP-Code

totals:

A 398,220

B 121,209

C 228,734

SRP-Code

totals:

A 398,208

B 120,871

C 228,118

966 codes

have invalid

characters

like @ : ^ -.

[ Team LiB ]

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

345/631

[ Team LiB ]

ETL Development Activities

The activities for ETL development do not need to be performed linearly. Figure 11.7

indicates which activities can be performed concurrently. The list below briefly

describes the activities associated with Step 11, ETL Development.

Build and unit test the ETL process.

Under the direction of the ETL lead developer, the ETL programs must be

developed for the three sets of load processes: initial load, historical load, and

incremental load. If you plan to use a database management system (DBMS)

load utility to populate the BI target databases, then only the extract and

transformation programs need to be written, including the programs that create

the final load files. If you plan to use an ETL tool, the instructions (technical

meta data) for the ETL tool must be created. All custom-written ETL programs

and all ETL tool modules must be unit tested for compilation, functionality, and

edits.

Integration or regression test the ETL process.

Once you have unit tested all the individual ETL programs or program modules,

the entire ETL process flow must be tested. This is accomplished with

integration testing on the first release and with regression testing on

subsequent releases. Both types of testing must be performed under a formal

test plan with test cases, expected test results, actual test results, and a log of

test runs.

Performance test the ETL process.

Since many BI target databases are very large databases (VLDBs), it is

important to stress test selected programs or ETL tool modules. Perform stress

testing with full volume data on those programs or ETL tool modules that read

or write to high-volume tables and that perform complicated operations,

especially when running in parallel against high-volume tables. Performance

tests can also be simulated with stress test simulation tools.

Quality assurance test the ETL process.

Most organizations do not allow programs to be moved into production until

they have passed through a QA test process. This test is usually run under the

supervision of the operations staff in a separate QA environment.

Acceptance test the ETL process.

If the business representative and the subject matter expert have been actively

involved in integration or regression testing activities, then acceptance testing

should be little more than a final, formal certification from the business

representative. If they have not been involved, all functions of the ETL process

must be validated to be complete and correct, especially the reconciliation

process.

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

346/631

Figure 11.7. ETL Development Activities

[ Team LiB ]

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

347/631

[ Team LiB ]

Deliverables Resulting from These Activities

ETL test plan

The test plan should state the purpose for each test and show a schedule for

running the tests in a predefined sequence. It should also describe the test

cases, including input criteria and expected output results. A test log should

accompany the test plan, documenting when the tests were run, who ran the

tests, and what the test results were.

ETL programs

All extract, transformation, and load programs and scripts for the entire ETL

process should be coded and tested. If an ETL tool is being used, instructions

for the ETL tool modules should be written and the ETL tool modules should be

tested.

ETL program library

All ETL programs, scripts, and ETL tool modules should reside in a version-

controlled ETL program library or ETL tool library. These ETL programs, scripts,

and ETL tool modules should have been integration or regression tested,

performance tested, QA tested, and acceptance tested for the entire ETL

process.

[ Team LiB ]

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

348/631

[ Team LiB ]

Roles Involved in These Activities

Business representative

The business representative should be involved in integration or regression

testing and in acceptance testing. With the help of the ETL lead developer, he or

she and the subject matter expert write the test cases.

Database administrator

The database administrator can be very instrumental during the ETL

development process. The database administrator assists the ETL lead

developer with the ETL process flow and also reviews all database calls written

by the ETL developers. On more than one occasion a database administrator

has been able to streamline the ETL process by invoking little-known DBMS

utilities at the appropriate point in the ETL process flow.

ETL developers

One of the main tasks assigned to ETL developers is to code or enhance the ETL

programs and to unit test them. If the organization is using an ETL tool, the ETL

developers must write the ETL instructions (technical meta data) for the ETL

tool processes.

ETL lead developer

The ETL lead developer manages the entire ETL process. He or she reviews the

ETL process flow diagram and the ETL program design document with the other

ETL developers and assigns the programming modules to them. He or she

creates the test plan and works with the business representative and the

subject matter expert on creating the test cases. He or she is also responsible

for coordinating the test runs and keeping the test log current.

Subject matter expert

The subject matter expert, either alone or with the business representative,

writes or enhances the test cases for the test plan. He or she also suggests what

reconciliation totals have to be produced. The subject matter expert should

participate as a tester during integration or regression testing and during

acceptance testing.

Testers

Testers can be developers, systems analysts, "power users," subject matter

experts, and anyone else who has some technical skills and is available to

participate in testing. Developers should not test their own code, but they can

test the code written by other developers. Testing is an activity that can easily

be modularized.

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

349/631

It is advisable to get as many testers involved as possible

to accelerate the testing process.

[ Team LiB ]

北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视仅供免费试用/版权原著所有

350/631