BIO PRESENTATION W12 Wednesday, October 29, 2003 3:00 PM A DVENTURES IN T ESTING D ATA M IGRATION Geoff Horne iSQA International Conference On Software Testing Analysis & Review October 27-31, 2003 San Jose, CA USA
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 $10 Design $50 (5x) Coding (incl. unit testing) $100 (10x) Testing (system, functional) $300 (30x) Acceptance Testing $400 (40x) $??? ( nn x) Production
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 Acceptance Requirements Testing Software System Specification Testing Integration Software Testing Architecture Unit Detail Testing Design Specification
www.isqa.com Migration Testing - Where the Steps Fit! Acceptance Function Testing System Screens & Testing Reports Integration Queries Testing Unit Checks & Testing Counts
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: 1 Checks and counts • Row counts • Column totals • Check sums • Check totals
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: 2 Database queries • Data queries: SQLs on source and destination tables • Templates: source -> destinations mappings
www.isqa.com Templates/mapping Documents: Source_Database_1 Dest_Database_1 Translation Rules SD1_Table_1 DD1_Table_1 = SD1_T1_Attr_1 SD1_T1_Attr_1 DD1_T1_Attr_1 SD1_T1_Attr_2 DD1_T1_Attr_2 = SD1_T1_Attr_2 SD1_T1_Attr_3 DD1_T1_Attr_3 = SD1_T1_Attr_3 + SD1_T1_Attr_4 SD1_T1_Attr_4 SD1_Table_2 DD1_Table_2 SD1_T2_Attr_1 DD1_T2_Attr_1 = (SD1_T2_Attr_1 * SD1_T2_Attr_3)/52 SD1_T2_Attr_2 DD1_T2_Attr_2 = SD1_T2_Attr_3 + " " + SD1_T2_Attr_4 SD1_T2_Attr_3 DD1_T2_Attr_3 = DD1_T1_Attr_3/SD1_T2_Attr_4 SD1_T2_Attr_4
www.isqa.com Database Queries: Select SD1_T1_Attr_1, Select DD1_T1_Attr_1, SD1_T1_Attr_2, DD1_T1_Attr_2, SD1_T1_Attr_3, DD1_T1_Attr_3 SD1_T1_Attr_4 From Dest_Database_1. DD1_Table_1 From Source_Database_1.SD1_Table_1 Select DD1_T2_Attr_1, Select SD1_T2_Attr_1, DD1_T2_Attr_2, SD1_T2_Attr_2, DD1_T2_Attr_3 SD1_T2_Attr_3, From Dest_Database_1. DD1_Table_2 SD1_T2_Attr_4 From Source_Database_1.SD_Table_2
www.isqa.com Database Queries: Translation Rules Pass/Fail = 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
www.isqa.com Data merger testing techniques: 2 Database queries • 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
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
Recommend
More recommend