Data Migration solution options Discussion document February 2016
Data Migration solution options – Discussion document February 2016 Sutherland Global Services Proprietary & Confidential 1
Table Of Contents Our Understanding of AON’s need The Value Proposition Data Migration approach and solution options (summary) Detail of solution options, key considerations & success criteria Proposed next step – A quick joint Discovery exercise Snapshot of Sutherland’s experience and case studies Sutherland Global Services Proprietary & Confidential 2
Our Understanding of AON’s need The Objective The Environment The Scope • AON wants to • Migrate Accounting data from ATLAS to Brokasure • Archive history data into an archival solution • Sunset the ATLAS system • ATLAS is a legacy system • ATLAS contains Accounting data • Brokasure is a third party product from Xuber to manage Broking lifecycle and built using Oracle Platform (Forms, Database) hosted on Microsoft Server with Windows client • In • Data Migration and Archival • Systems : Source - ATLAS, Target – Brokasure • Data : ATLAS -> Accounting Records - ~900 K / Annum (2015), Unique Policy - ~20 K / Annum (2015) • Out • Environment setup (Infrastructure, Source and Target system), Implementation and Testing of Brokasure Sutherland Global Services Proprietary & Confidential 3
The Value Proposition A partnership with Sutherland would benefit AON in FOUR ways – Collaborate to access expertise, experience Use of proven tools for speed and accuracy Robotics Process Automation enabled A simple but scalable Archival solution …Access to Sutherland’s deep process , domain and technical experience for enhanced mapping of data – frees up valuable time of in-house staff …Use Sutherland’s proven methodology (e. g. Testing Strategy, Pre-defined Templates), process accelerators, extensive conversion library of standard listings, edit checks and various reports, reduces time and cost …A customized solution by migration phase – e. g. RPA for the Data Validation eliminates the risk of data loss, inaccurate mappings, and delays …and, an option to extend the use of RPA for Data loading …Deployment of a scalable and cost effective Archival solution Sutherland Global Services Proprietary & Confidential 4
Data Migration Approach Data Analysis & Mapping Requirement Capture Activities Team • Understand Business • Capture Migration Requirement • Understand Systems (Source and Target) Client - Business and System SMEs SGS – Migration Lead, Business and Data Analyst • Capture Source and Target data detail • Capture Source and Target Data map • Capture Cleansing Requirement Client - Business and System SMEs SGS – Business and Data Analyst • Deliverables • • Migration Requirement Migration Plan • • Data Mapping document Cleansing Requirement Updated Migration Plan Data Extraction & Cleansing • Extract Data from Source System • Clean data at Record and Field level Client - System SMEs SGS – Technical Lead • Cleansed Source Data Transformation Production Data Load Data Validation & Reconciliation • Use Data Map to convert Cleansed data to Target data format • Use combination of Automated and manual validation to reconcile converted data against source data • Load Data in Production Environment • Sanity check of migrated data Client - Business and System SMEs SGS – Data & Technical Lead Client - Business SMEs SGS – Business and Technical Lead Client - Business and System SMEs SGS – Technical Lead • • Converted Data • Validated and reconciled Data in Staging environment Sign off from Business • • Data Loaded in Production Sign Off from Business The data migration approach will be customised in discussion with AON. All key phases in life cycle e. g. Cleansing, Transformation and Validation will iterate to ensure complete quality migration Sutherland Global Services Proprietary & Confidential 5
Data Migration solution options Solution Option 1 (RPA used for Validation) § Key Features § § Benefit Solution Option 2 (RPA used for Data Load and Validation) Uses staging environment to minimize impact on existing systems Takes care of ‘dirty’ data and missing information Uses custom component for extraction, cleansing and mapping Direct mapping from source table to target table allows faster load Uses RPA (Robotic Process Automation) for validation of converted data Combination of traditional and RPA solution results in better quality data migration with faster load § § § Uses staging environment to minimize impact on existing systems Takes care of dirty data and missing information Uses custom component for extraction, cleansing and mapping Uses RPA (Robotic Process Automation) for load of data using Brokasure screens Uses RPA for validation of converted data Uses RPA for data load, leveraging inbuilt business rules and validation in Brokasure ensuring load of quality data. Reduced dependency on Xuber Archival Solution Key Features Support for load of any type of data Benefit Scalable and cost effective solution, Faster access to data Key Assumptions Currently there is no out of box tool available from Brokasure for migration of data The feasibility of each of the solution option will be validated in discussion with AON to come out with final customised solution Sutherland Global Services Proprietary & Confidential 6
Detail of the solution options 7
Option 1 Data migration solution with RPA for validation Extraction, Cleansing, Mapping and Conversion Extract Source Data (ATLAS) Extract Source Data AS IT IS in Staging database (SD) (Extraction Component) Stage DB Driver Clean each column of each Record Cleansing / Transform Create data relationship for each Record (Cleansing Component) Persist data in SD for table level cleansing (e. g. duplicate record) SD - II Staging DB (SD) Load Clean table (e. g. Duplicate record) Map data to Target system (Cleansing Component) (Mapping Adapter) SD - III Replica of Target DB Testing/Validation Note - Appropriate ETL Tool, Staging DB would be identified in collaboration with the Client Extract Record Level Data from ATLAS screens (RPA solution) Comparator Component Extract single record from Converted data Extract Summary data from Reports in ATLAS (RPA Solution) Comparator Component Query component to extract summary data from DB Sutherland Global Services Proprietary & Confidential Deliverables SQL dump of Data (for load in Brokasure) Record Level Comparison Report Target DB Broaksure Summary Reconciliation Report 8
Option 1 – Key Assumptions • Brokasure to share data structure, data definition and assist in mapping the source data to Brokasure Database tables/fields • Robotic Process Automation assumes availability of Summary and Record level data through ATLAS screens • Proper connectivity to AON network system would be provided for faster development of RPA solution and execution Sutherland Global Services Proprietary & Confidential 9
Option 2 Data migration solution with RPA for data load and validation Extraction, Cleansing, Mapping and Conversion Extract Source Data (ATLAS) Extract Source Data AS IT IS in Staging database (SD) (Extraction Component) Stage DB Driver Clean each column of each Record Cleansing / Transform Create data relationship for each Record (Cleansing Component) Persist data in SD for table level cleansing (e. g. duplicate record) Clean table (e. g. Duplicate record) (Cleansing Component) SD - II Staging DB (SD) Load (RPA Solution) Target DB Brokasure Application Deliverables SD - III Record Level Comparison Report Testing/Validation Note - Appropriate ETL Tool, Staging DB would be identified in collaboration with the Client Use RPA to load data using Brokasure screen Extract Record Level Data from ATLAS screens (RPA solution) Comparator Component Extract Record data using Brokasure screen (RPA solution) Extract Summary data from Reports in ATLAS (RPA Solution) Comparator Component Extract Summary data using Brokasure screen (RPA Solution) Sutherland Global Services Proprietary & Confidential Summary Reconciliation Report 10
Option 2 – Key Assumptions • AON will share the details on screen navigation to create Accounting data in Brokasure • All Accounting data can be entered using Brokasure screens • Robotic Process Automation assumes availability of Summary and Record level data through ATLAS screens • Robotic Process Automation assumes availability of Summary level data through Brokasure screens • Proper connectivity to AON network system would be provided for faster development of RPA solution and execution Sutherland Global Services Proprietary & Confidential 11
Uses of Robotics Process Automation (RPA) in data migration Data Extraction/Cleansing § Easy identification of duplicates, collation of information into a single record, and discarding of unneeded records. § Identify anomalies in the data based on a set of defined business rules. § Where large volumes of data are involved, RPA can run scripts on a 24/7 schedule, where tools may require manual intervention. § Data Validation Data Loading § Verification of an automatic load process can be done through RPA § If data is to be loaded through a UI, this can be done through an RPA solution and will avoid manual errors. Easily scalable solution by simply increasing or decreasing the number of robots in the process. § Validation based on RPA will be useful when there are multiple runs required for loading and verification/validation is done after each iteration (regression testing). § For example a validation based on a summary report from the source and target system can be done through RPA. § RPA can also reconcile a random sampling of data post migration against the original clean data migration file as a kind of an audit. Note: RPA can be utilised to improve the quality and overall time required for the extraction/loading process. It is not envisaged to replace existing tools for extraction. Sutherland’s experience suggests that trade-off vs. the number of robots needing to be deployed would not work to AON’s advantage Sutherland Global Services Proprietary & Confidential 12
Archival Solution Data Extraction and Meaningful Record Creation Source Data (ATLAS) Extract Source Data As IT IS in Staging database (SD) (Extraction Component) Create Meaningful Data Stage DB Driver Load meaningful data in Archival System (No. SQL based) Data Validation Staging DB (SD) Use representative sample data from ATLAS and Archival system and compare it Data exposed through web service Deliverables Data Comparison Report Note - Data Archival solution proposed here is for one time migration of Archival data from ATLAS to Archival solution outside Brokasure. Appropriate Tool for ETL and No. SQL DB would be identified in collaboration with the Client. Sutherland Global Services Proprietary & Confidential 13
Key considerations and success criteria Key considerations What would it take for a partnership to succeed • • Prioritisation of scope by LOB (Insurance, Specialty, Reinsurance) / geography for migration (as applicable) • Business and IT Team work closely – IT is clear with Business on the support it needs • Migration of archival data from ATLAS to an archival solution outside Brokasure • Close working and support from Target platform Team • Sunsetting requirement of ATLAS • Select familiar technology and design a simple solution • Knowledge and understanding of Source data • • Flexibility of Target system to map Source Data Conscious focus on solution for mapping exceptions to Technology avoid data loss success Collaboration The following represent the key areas of that Sutherland would like to consider with AON in more detail • Clearly defined ownership of the migration and roles and responsibilities – AON IT and Sutherland Key Engagement success criteria Key Comprehensive strategy in place for multiple levels of validation Sutherland Global Services Proprietary & Confidential criteria 14
Proposed next step 15
A quick Discovery exercise SGS would like to propose 2 week joint Discovery exercise to understand AON’s data migration requirement and propose right solution and implementation plan Pre-Discovery Week 1 Week 2 • Understand in scope Business Landscape and Business Model • Understand Systems (Source and Target) • Understand Target system Data Model and its implementation • Understand Source system and Data Model implementation in it • Understand Source System Data cleanliness requirement • Capture mapping of Source and Target Data Model • Understand Tools/Solution in place to extract and load data in Source and Target systems respectively • Create Data Migration solution • Create Data Migration Plan • Presentation of Solution and high level plan to AON Organize Discovery exercise by sharing details about key stakeholders and availability Project Sponsor, Business & System SMEs to explain migration requirement, Systems and Data and share all relevant information. SGS to come out with Migration solution and high level plan and present it to AON Team 1. Discovery Plan 1. Business, Systems and Data Understanding document 2. Data Migration solution 3. Migration plan • Capture list of key stakeholders and SMEs Activities • Create detailed plan for Discovery Phase • Send calendar invite for meeting during Discovery phase Expectation from Customer Deliverables Sutherland Global Services Proprietary & Confidential 16
Appendix: Snapshot of Sutherland’s experience, Case studies 17
SGS Data Conversion and Migration Experience Conversion Experience Technologies Expertise SGS has extensive experience in Data Conversion and Migration projects : • 670 conversion projects • 50 BPaa. S data conversions SGS has done data conversion and migration using range of technologies and programming languages, ETL tools (Third party and custom) and range of it’s own proprietary Robotic Process Automation solutions encompassing all phases of data migration life cycle. Sutherland Global Services Proprietary & Confidential 18
Data Conversion and Migration Case Study 1 Client Overview The Client is a publicly traded North American company that provides end to end solutions for healthcare providers Client engagement & Need for Change Key Features Our Client provides Practice Management and Electronic Health Record technology (including Electronic Prescribing, Care Management and Revenue Cycle software) to Physician Practices, Hospital, and other healthcare providers with Practice Management. § § Structured and well defined approach to effectively manage migration journey These independent practices wanted to migrate their clinical data from existing disparate systems to target Client EHR systems while building and establishing custom interfaces as per the client’s requirement and meeting MU standards. § § Robust and clear communication at every level Solution § Custom interfaces and configuration §Business Benefits / Value Adds Data security compliance The project involved migration of data from existing systems (e. g. EMR/EHR databases, Informix) to the new Client Enterprise systems. This transition involved migrating Patients and their respective clinical data into the new systems (TW Database, SQLServer). § § § § Analysis of data and relationship Creation of Requirement document and Plan Mapping of data and design and development of conversion component in SSIS Extraction of data using SSIS from disparate sources in staging area Development of loading components (stored procedures) In-house proprietary toolkits helps in process acceleration while enhancing overall data quality § Highly customized data extraction and conversion services addressed client’s unique needs § Flexible, fast and efficient conversion and migration approach ensures minimum disruption to regular workflow § Proven tools, methodologies and expertise provided clear benefits in terms of time and cost § Client interactions and follow-ups at every stage, maintaining high level of transparency § § Ensured accurate mappings & zero data loss Creation of Tables and views in Staging DB Transformation application in SSIS Proven tools, extensive conversion library and best practices ensures greater flexibility and efficiency Compliance with regulatory guidelines Sutherland Global Services Proprietary & Confidential 19
Data Conversion and Migration Case Study 2 Client Overview The Client is one of the largest Airline company in the world. Client engagement & Need for Change Key Features The client wanted to migrate Air Way Bills from legacy mainframe based system (ORIX) to . NET based system (CROAMIS). § § § Short duration for solution development Ability for solution to scale for large volume of data using additional robots Automated data reconciliation solution Solution Business Benefits / Value Adds SGS leveraged its extensive data migration experience and strong technical capability in Robotic Process Automation to facilitate one of the fastest risk free implementation. § § § The solution involved reading data from an Excel spreadsheet and entering that in the target application using multiple instances of robots. Substantial effort and cost savings for customer 100% conversion with zero error in one go Migration completed in very short period of time The solution was developed and tested in very short period of time. The solution involved migration of 100 K records in production with zero defect using multiple robots. The converted data was then reconciled using robots. The migration was completed with zero error and well before planned time in production. Sutherland Global Services Proprietary & Confidential 20
Sutherland Global Services Proprietary & Confidential
- Slides: 21