1 / 29

Data Conversions

Steps, Best Practices, and Considerations for Successful Data Conversions. Data Conversions. Norm Bowen 04/12/2011. 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

olisa
Télécharger la présentation

Data Conversions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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!

More Related