merged online covers - stickyminds...software testing - overview process: the v-model user business...
TRANSCRIPT
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 HorneGeoff Horne - comes from a background of 25 years in IT having worked in softwaredevelopment, sales and marketing, IT management and consulting before putting hispropensity for breaking things to good use. He has run many testing projects in NewZealand, Australia and the UK and now specialises in the development of testingstrategies and methodologies along with project management. Geoff is married with fourchildren and in his spare time enjoys composing and recording contemporary Christianmusic.
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 thefull 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 counts2 Database queries
Application:3 Screens and reports4 Functionality
www.isqa.com
Software Testing - Overview Process: The V-Model
User
BusinessRequirements
SoftwareSpecification
SoftwareArchitecture
Detail DesignSpecification
UnitTesting
IntegrationTesting
SystemTesting
AcceptanceTesting
www.isqa.com
Migration Testing - Where the Steps Fit!
UnitTesting
IntegrationTesting
SystemTesting
AcceptanceTesting
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_1SD1_Table_1
SD1_T1_Attr_1SD1_T1_Attr_2SD1_T1_Attr_3SD1_T1_Attr_4
SD1_Table_2SD1_T2_Attr_1SD1_T2_Attr_2SD1_T2_Attr_3SD1_T2_Attr_4
Dest_Database_1DD1_Table_1
DD1_T1_Attr_1DD1_T1_Attr_2DD1_T1_Attr_3
DD1_Table_2DD1_T2_Attr_1DD1_T2_Attr_2DD1_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 elementsDestination database elements Template mapping specificationsTranslation rules
Template mapping specifications Row countsSQL database queries Check totals
Table lists
www.isqa.com
Data merger input/outputs:
Input Output
Table lists Screen outputs and displaysScreen enquiries Reports
Table lists Application executionScreen outputs and displays Application outputReportsKey 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 otherwise 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.
Iss
ues
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