Best Practices and Considerations for Successful Data Conversions

1. Steps, Best Practices, and Considerations for Successful Data Conversions Data Conversions Norm Bowen 04/12/2011

2. About Norm Bowen • Major professional focus is making data as valuable as possible • Broad range of IT and business experiences: • AT&T, CIT, ISC, OCPS • Database & Application Development/Support • ERP Systems • Business/Systems Analysis • Reporting • Backup DBA experience • Experience working with Oracle, Sybase, DB2, SQL Server • Credentials • Masters in Computer Science • Business Intelligence certificate - Univ. of California • MCTS certifications - Business Intelligence & Database Admin • Current: Data & Reporting Analyst on contract at a Healthcare firm in Altamonte Springs

3. What will be covered • Stages and steps of a data conversion project • A guide for progressing from concept to completion, including project planning • Conversion Processes • PM, BA, Technical information for developing, testing and executing the conversion • Additional Considerations • Items not directly a part of the “conversion” but should be considered to potentially provide additional value to tasks being performed and provide more value to the company • OPASS Chime-in • Share your conversion tips / experiences / feedback

4. Reasons for Data Conversion(Migration/Integration) • Migrating to a new system • New Business/Regulatory Requirements • New database • Mergers & Acquisitions

5. Stage 1: Big Picture, Analysis, Feasibility, Project • Get a handle on the big picture / the impact • Understand Source(s) • Understand Destination(s) • Determine Feasibility • Project Plan – 1st draft

6. Understand the Big Picture • What is the conversion about? What systems are involved? • Why is the conversion needed? And how important is it? • Approx how big and complex is this conversion? • # of systems? • Amount of data? • Scope of data being converted? • What is the impact on…? : • Business processes • System processes • Reporting • Systems users • External parties • System processes • Any pre/post dependencies? And their timing? • Can the conversion be done in pieces? Will it have to be done in pieces? • Who are the key players that might become involved? • System/Data Owners • Power Users • C-level Sponsors • Data Architects • Developers, etc.

7. Know your Source(s) & Destination(s) • DESTINATION • What does the data represent? • How will it be used? • Is there data already existing in the destination? • Differences between source and destination • Key measurements that can be used to confirm if conversion was successful • Is Database Type different from source? SOURCE • What does the data represent? • How is it used? • Profile of the data • Subset of data to be converted • Key measurements that can be used to confirm if conversion was successful • Database Type

8. Feasibility – Proof of concept • Proof of concept using data from the most important / most complex processes / data • Verify converted data will meet needs of the new (or existing) system • Do not go through too much before verifying that it will work for what you need • Go No-go Decision

9. Project Plan – Round 1 • Create a non-detailed project plan • Use the information gathered in Stage 1 to estimate the complexity and effort of the tasks, the resources needed, how long it will take, etc. • include competing business activities • Assemble the team • PM / Team Lead • Business / Data Analysts • Business Users / Data Experts / Data Owners • DBA • Data Architect • Consultants • Officially launch the project

10. Stage 2:Impact, Mappings, Project, QA • Impact Analysis • Detailed Data Profiling & Source-Destination Mappings • Data Gap Analysis • Project Plan – 2nd draft • QA Plan

11. Impact Analysis • Business processes affected • System processes affected • Reports • Users (may lead to need for training) • New Application Rollout • Business/IT activities (month-end closes, sales promotions, holidays, other system upgrades, etc) • Equipment Needs & Sourcing • Ongoing Resource Needs • Go No-go Decision

12. Source/Destination Mappings & Data Gap Analysis • Create Source – Destination Mappings • Include ALL data that will be in the conversion process, even data that will be unchanged • List data that will be excluded • List data that will not originate from Source but will be supplied for the Destination • Handle data not accounted for • Important Source data that is not mapped to anything • Destination data that do not have a source • Hard-code / rule-based assignment • Keep Legacy Keys • allow for backward reporting / querying (if you foresee this necessity)

13. Source to Destination Mapping Example • Excel is adequate in many cases • Visio is also a good option • Other more sophisticated tools available

14. QA / Verification Planning • Application Functionality • Source Aggregates vs. Destination Aggregates • Pick a variety of specific records that you follow all the way through the conversion process • Old detailed records vs. New detailed records • Old Reports vs. New Reports • IT + Business Team + QA team

15. Stage 3: Project, Development, QA, Other • Enhance the project plan & move other project tasks along • Determine development methodology – tools, coding, source code control, • Set up environment • Develop conversion • QA - Test, Test, Test the conversion

16. Project • Project Plan - more details, more precise timelines, assignment of all tasks to people, dependencies, fallback plans, etc. • Continue to collect feedback during the development process • Adjust team if necessary; get all parties on board (communication is key on any project) • Get agreement on mapping and other requirements

17. Development • Set up the environment • Servers • Size and build databases • Linked servers • Plan and create a starting point for your conversion environment that you can revert back to for testing and re-testing • database backups & reset scripts that can be used to create a consistent starting point • Develop conversion programs (this could be a multi-person or multi-group effort) • Create DDL scripts, T-SQL programs, Stored Procedures, Functions, .NET programs, batch jobs, SSIS packages, etc. • Clean up scripts / Rebuild scripts • Develop in parallel

18. Development (cont’) • Build in Logging • Determine Order of all processing • Develop such that processes can be run in parallel instead of serially to speed conversion time • Build Master Package(s) or batch job(s) to execute the programs in the correct, most efficient order D_Customer D_Product D_Customer_Profile D_Sales D_AccountsReceivable

19. Development (cont’) • Use performance improvement techniques to tune your conversion processes • SET NOCOUNT ON • Update Statistics • Do not interweave DDL with DML • Use or Drop Indexes / Partitions • Create indexes on source tables to speed data retrieval; remember to clean up after (add to your cleanup script) • Create temporary indexes on destination tables (after loaded) if used for lookup or joins to load other tables • use available tools - DTA, SQL Profiler, Execution Plans

20. Development (cont’) • Build in Commits and Re-startability for long running processes • Create intermediate check-points to verify the conversion along the way – especially between dependencies • Load Customers  Check • Load Products  Check • Load Sales  Check • Traceability • Log updates to audit tables or audit columns • Use permanent tables instead of temp tables to enhance traceability • Include code to handle outliers – including NULLs

21. Development (cont’) • Check destination tables for triggers, and understand what the triggers do; may need to temporarily disable them • ALTER TABLE tablename DISABLE TRIGGER triggername • ALTER TABLE tablename ENABLE TRIGGER triggername • Check indexes and constraints (check, defaults) on destination tables • drop/disable as necessary • then rebuild/enable; may have to use “nocheck” option • ALTER TABLE WITH NO CHECK ADD CONSTRAINT …. • OR Create with NO CHECK • Check for Identity columns – may need to disable them: • SET IDENTITY_INSERT tablename ONperform inserts and then later enable them • SET IDENTITY_INSERT tablename OFF

22. Development (cont’) • Functions frequently used to manipulate/check data • CAST - Sting to Number, Number to String, DateTime to String • CONVERT • SUBSTRING • CHARINDEX • REPLACE • ISNULL • ISNUMERIC • DATEADD, DATEDIFF • LEN • CASE • BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK

23. Development (cont’) • If the destination already contains data that cannot be changed, create conversion programs with that in mind • Include verification that existing data is not affected by the conversion • Handle database to database variations (especially between data types) • Re-build reports and views (keep output same if possible) • Create new system processes (interfaces, feeds, extracts, etc) • Build / Rebuild indexes • Feed information back to the project manager as new information is uncovered regarding run times, new steps, etc

24. Test, Test, Test • Backup databases • Test and Test again • Verify data and functionality • Verify aggregates • Verify selected detailed records • Review data from ALL data types – verify at least one column for each data type • Tune the conversion process; reset the cache when capturing run-times to avoid misleading run times • All relevant teams should be involved in testing; Business Users should be responsible for the final sign-off

25. Other Considerations • Train users in parallel with development; make users aware of any data changes, new data entry methods, etc. • Run old and new system in parallel to compare results, and verify the new system • Set firm cut-off point for when no more updates will be done to the source system • Is this an opportunity for archiving? • Do SLA’s need to be adjusted? • Are we in full compliance with all regulatory requirements?

26. Stage 4: Schedule, Execute, Post Tasks • Stick to the schedule • Keep track of progress; compare results to what was expected; share results • Keep all team members informed of progress • Execute Post-conversion scripts / tasks • Allow limited access to new system or to data to allow for more verification / damage prevention • Make new system/data accessible / Go-Live • Provide close monitoring and support for new system and users

27. Summary • Get ALL stakeholders involved early • Analyze and Plan Well • Develop Smartly • Test Vigorously • Communicate Throughout

28. Your Turn • Your experiences • What are some stand-out items that you recall from conversions you have participated in? • Your best practices • What would you consider a “must-do” step or method for conversions (or conversely a “must-not-do step”)? • Any suggestions to improve this presentation?

29. Contact Norm Bowen 407-547-9488 norm@msbistack.com Thank you for the opportunity to present!