W12 Wednesday, October 29, 2003 3:00 PM A DVENTURES IN T ESTING D - - PDF document

BIO PRESENTATION International Conference On Software Testing Analysis & Review October 27-31, 2003 San Jose, CA USA

W12

Wednesday, October 29, 2003 3:00 PM

ADVENTURES IN TESTING DATA MIGRATION

Geoff Horne iSQA

Geoff Horne

Geoff Horne - comes from a background of 25 years in IT having worked in software development, sales and marketing, IT management and consulting before putting his propensity for breaking things to good use. He has run many testing projects in New Zealand, Australia and the UK and now specialises in the development of testing strategies and methodologies along with project management. Geoff is married with four children and in his spare time enjoys composing and recording contemporary Christian music.

Independent Software Quality Assurance Ltd

www.isqa.com

www.isqa.com

The Earth Moved! Adventures In Data Migration Testing

Geoff Horne - iSQA

www.isqa.com

• abridged version only of slides in your conference folder
• leave me your business card if you’d like me to email you the

full set

• or, wait a few weeks and they’ll end up on our website
• if you have any queries outside of the question time, take my

card and email me

• I’m not the ultimate authority, I can be wrong
• if your cell phone goes off, you buy me dinner!

www.isqa.com

• Founded by Geoff Horne in April 2002
• New-ish company however with heritage:
• GGD - 1991-2000
• Integrity Software Testing - 2000-2002
• Specialise in testing - our only line of business
• Offer range of testing services:
• testing methodologies
• test planning
• test project management
• testing capability assessments
• automated testing
• load and performance testing
• project quality audits
• testing outsourcing
• testing training & education

www.isqa.com

• Clients in:
• New Zealand
• Australia
• USA
• United Kingdom
• All work is based on our iSTEP Testing Methodology which

incorporates the principles and elements of:

• IEEE standard 802.9
• SEI Capability Maturity Model (CMM)
• TPI - Test Process Improvement

www.isqa.com

*** STOP PRESS ***

Latest Standish Group findings:

• US companies waste $145b on failed IT projects
• 50% of all IT projects fail completely
• Only 9% complete on-time and on-budget
• Poor project management cited as main reason for failure

Questions:

• Is on-time and on-budget the only success criteria?
• How many of the 9% actually delivered on expectations?
• What about the 41% gap?
• How many of those failed employed formal testing

methodologies?

www.isqa.com

So what is a data migration?

• Moving data from one platform to another
• Migrating data from one database to another
• Transferring data from one application to another
• Merging one or more databases
• Merging one or more databases plus non-database

sources eg. spreadsheets

• Extracting data into separate repositories eg. data

warehousing

• Handling schema changes between application

versions

www.isqa.com

And why do they need testing? 1 There is no other sure-fire way to crash an application than to provide it with data it is not expecting….

www.isqa.com

And why do they need testing? 2 Data mergers and migrations can be tricky at best with converted data often remaining buried for months or even years before it is touched and then…..

www.isqa.com

And why do they need testing? 3 Because data mergers are usually one-offs, formal test methodologies are often not employed and this lack of foresight quickly becomes evident after the fact….

www.isqa.com

Get it right first time! Why? Because it costs too much to get it right later!

Requirements Design Coding (incl. unit testing) Testing (system, functional) Acceptance Testing Production

$??? (nnx) $400 (40x) $300 (30x) $100 (10x) $50 (5x) $10

www.isqa.com

Quotable quotes: “There is never enough time and money to get it right first time however there is always seems to be enough of both to fix it later.” “Six months after go-live, no-one remembers that it went in on time - only the frustration and long hours trying to make it work.”

www.isqa.com

Meet Herbert:

• Ex-fastidious user
• Slow and steady
• Exquisitely articulate
• Painfully pedantic
• Unbelievably accurate
• Annoyingly correct
• Tests with military precision

And yes, he’s a real person!

www.isqa.com

Herbert’s First Task: Data cleansing!

• Clean the data first
• Don’t use migration to fix integrity issues
• This can be an exercise in itself…..
• ….but don’t do it and you’ll slow your

migration exercise down by factor of 10!

www.isqa.com

Understand...

• When you are testing a data migration…
• …you are testing software! What software?
• …the migration utilities!

www.isqa.com

Data migration utilities: Applications in their own right:

• Logic paths
• Error handling
• Calculations (for translated attributes)
• Parameter passing
• Database read/write
• Abnormal termination handling
• Version control
• Unit testing!

www.isqa.com

The Four Steps: Database:

1 Checks and counts 2 Database queries

Application:

3 Screens and reports 4 Functionality

www.isqa.com

Software Testing - Overview Process: The V-Model

User

Business Requirements Software Specification Software Architecture Detail Design Specification Unit Testing Integration Testing System Testing Acceptance Testing

www.isqa.com

Migration Testing - Where the Steps Fit!

Unit Testing Integration Testing System Testing Acceptance Testing Checks & Counts Queries Screens & Reports Function

www.isqa.com

Mapping Templates: Essential!

• Specify source table elements
• Specify destination table elements
• Specify how the source table elements

map onto the destination table elements Maybe be tedious however nowhere near as much as it will be if its not done!

www.isqa.com

Data merger testing techniques:

• Row counts
• Column totals
• Check sums
• Check totals

1 Checks and counts

www.isqa.com

Data merger testing techniques: Counts:

Source “123”,”ABC Customer”,”123 Test St”,”Testville”,”NSW” “456”,”XYZ Customer”,”456 Test St”,”Testville”,”NSW” 1,298 rows counted Destination “123”,”ABC Customer”,”123 Test St”,”Testville”,”NSW” “456”,”XYZ Customer”,”456 Test St”,”Testville”,”NSW” 1,297 rows counted

www.isqa.com

Data merger testing techniques: Checks eg. column totals:

Source SUM Total_Outstanding FROM SD1_Table_1 1,298 rows, SD1_Table_1.Total_Outstanding=$5,098,637.98 Destination SUM Total_Outstanding FROM DD1_Table_1 1,297 rows, DD1_Table_1.Total_Outstanding=$5,098,456.52 The difference may help you highlight the missing data!

www.isqa.com

Data merger testing techniques:

• Data queries: SQLs on source and destination

tables

• Templates: source -> destinations mappings

2 Database queries

www.isqa.com

Templates/mapping Documents:

Source_Database_1 SD1_Table_1 SD1_T1_Attr_1 SD1_T1_Attr_2 SD1_T1_Attr_3 SD1_T1_Attr_4 SD1_Table_2 SD1_T2_Attr_1 SD1_T2_Attr_2 SD1_T2_Attr_3 SD1_T2_Attr_4 Dest_Database_1 DD1_Table_1 DD1_T1_Attr_1 DD1_T1_Attr_2 DD1_T1_Attr_3 DD1_Table_2 DD1_T2_Attr_1 DD1_T2_Attr_2 DD1_T2_Attr_3 Translation Rules = SD1_T1_Attr_1 = SD1_T1_Attr_2 = SD1_T1_Attr_3 + SD1_T1_Attr_4 = (SD1_T2_Attr_1 * SD1_T2_Attr_3)/52 = SD1_T2_Attr_3 + " " + SD1_T2_Attr_4 = DD1_T1_Attr_3/SD1_T2_Attr_4

www.isqa.com

Database Queries:

Select SD1_T1_Attr_1,

SD1_T1_Attr_2, SD1_T1_Attr_3, SD1_T1_Attr_4

From Source_Database_1.SD1_Table_1 Select SD1_T2_Attr_1,

SD1_T2_Attr_2, SD1_T2_Attr_3, SD1_T2_Attr_4

From Source_Database_1.SD_Table_2 Select DD1_T1_Attr_1,

DD1_T1_Attr_2, DD1_T1_Attr_3

From Dest_Database_1. DD1_Table_1 Select DD1_T2_Attr_1,

DD1_T2_Attr_2, DD1_T2_Attr_3

From Dest_Database_1. DD1_Table_2

www.isqa.com

Database Queries:

Translation Rules = SD1_T1_Attr_1 = SD1_T1_Attr_2 = SD1_T1_Attr_3 + SD1_T1_Attr_4 = (SD1_T2_Attr_1 * SD1_T2_Attr_3)/52 = SD1_T2_Attr_3 + " " + SD1_T2_Attr_4 = DD1_T1_Atrr_3/SD1_T2_Attr_4 Pass/Fail

www.isqa.com

• Pre-plan your queries
• Ensure source table queries are working

first

• …and that you have at least developed

your destination table queries Ensures that you save time later when in execution phase Data merger testing techniques:

2 Database queries

www.isqa.com

Data merger testing techniques:

3 Screens and reports:

• Screen enquiries
• List major screens where data is used (as

many a practical)

• Using SQL output, check screen displays
• Print screens for traceability
• Check where same data may be displayed in

different ways

• Reports and listings

www.isqa.com

Data merger testing techniques:

4 Functionality:

• Ensure main functions work eg.
• Batch processes
• Online processing
• Maintenance processes
• Interfaces
• Utilities

Use your regression test scripts!

www.isqa.com

Data merger input/outputs:

Input Output Source database elements Destination database elements Template mapping specifications Translation rules Template mapping specifications Row counts SQL database queries Check totals Table lists

www.isqa.com

Data merger input/outputs:

Input Output Table lists Screen outputs and displays Screen enquiries Reports Table lists Application execution Screen outputs and displays Application output Reports Key function test scripts

www.isqa.com

The Four Step Process: Essential!

• Get each one right before commencing the

next

• If a prior step fails again, go back and get

it right first!

• Track the number of errors etc. arising

through each step - should decrease

www.isqa.com

Data merger iterative process: Table checks Database queries Screen enquiries Application function

www.isqa.com

Conversion utility maturity Table checks Database queries Screen enquiries Application function Data merger iterative process:

www.isqa.com

OK, so you’ve got your Four Steps working: Now refresh your test database!

• First time must be with static data
• therwise you have no before/after

comparison!

• Four steps should be much quicker in

subsequent runs

• Will still find new issues - different data

www.isqa.com

Data migration test runs:

• No. Issues
• No. Database Refreshes

Good enough!

www.isqa.com

To summarise:

• Data migrations and mergers are tricky at best
• Often viewed as one-off exercise so formal rigours not

applied

• Four step process:
• Counts and checks
• Database query samples
• Screen enquiries
• Application function
• Iterative improvement process leads to unattended

migration utility execution

www.isqa.com

OK, wanna know how many defects on Herbert’s data migration project were found to be migration problems? Exactly….

Independent Software Quality Assurance Ltd

www.isqa.com