Case Studies Data Migration

(1)

Case Studies

Data Migration

(2)

A midsize Biopharmaceutical company with regional offices in the US and Europe needed their five year old Argus system upgraded to the newest version. At the same time, the workflow should be optimized and globalized, Argus Interchange implemented and data from a recently acquired company’s ARISg system migrated into the new instance. Some of the issues the company was faced with included:

 The existing workflow with separate regional centers was suboptimal

 Data exchange between the centers was a major issue

 The product and license configuration did no longer support regulatory reporting in a satisfying fashion

 The old system had performance problems

 Electronic submissions and transmissions were needed because of a large number of licensing partners

Solution

Foresight resources conducted a readiness assessment on all four regional centers and helped design a global operating workflow. The data exchange problems were eliminated by implementing a global fax server solution and digital document storage. The system was completely reconfigured, requiring a full data migration using direct data mapping. Foresight resources performed the following tasks:

 Conduct readiness assessment, analyze business requirements and propose a new global workflow and safety systems infrastructure including a RightFax fax server and an ESTRI gateway.

 Complete reconfiguration of the system to support a global environment, expedited and periodic reporting to more than 80 regulatory agencies and license partners.

 Implementation of Argus Safety 4.1 SP3 and Argus Interchange on a new infrastructure.

 Creation of a new set of standard reports in close collaboration with the end user community.

 Validation of the new system including creation of the full validation documentation

 Migration of 40,000 cases from Argus to Argus using a direct data mapping approach.

 Migration of 5,000 cases from ARISg 5.0.1 to Argus using a direct data mapping approach.

 Verification of both Case Migrations using a statistical relevant sample of cases

 Cutover and post-production support Results Achieved

(4)

 Implemented, configured and validated Argus Safety and Interchange

 Migrated the data from the legacy systems

 Cut-over on a three day weekend without any issues

 Completed the project within 10 months on budget and at time

2 ARGUS DATA MIGRATION

A large CRO needed to migrate ~11,000 cases on behalf of a new sponsor into a hosted instance of Argus Safety™. The client was facing a number of challenges:

 The sponsor purchased a product from another pharmaceutical company that used Argus Safety™, but they did not know how they could or would receive the cases for the migration

 The client needed to rapidly analyze alternatives and gain agreement with the new sponsor on how data would be migrated and how the hosted instance of Argus Safety™ would be configured

 CRO needed to migrate full case data (including attachments) into their Argus instance

 The sponsor and the CRO were on different versions of Argus Safety

 The client needed resources that knew how to configure a new instance of the safety database and how to migrate case information

Solution

Foresight was engaged initially to perform a one week analysis and proof of concept to determine the best alternative.

 Rapidly analyzed data migration alternatives, performed a proof of concept of the recommended solution and then worked with the CRO to gain agreement with the sponsor

 The database migration approach was selected versus an e2b import with post-import cleanup

Subsequent to the POC, the client engaged Foresight to perform the full data migration. Our resources managed the following tasks:

 Analyzed the configuration of the source Argus safety™ database and worked with the CRO to configure a new hosted instance that combined requirements from the source configuration and the new sponsor

 The new hosted instance of Argus Safety™ was rapidly configured to enable capturing of new cases by the sponsor during the data migration effort

 Multiple workshops were conducted with the business users to determine aspect such as:

o Aggregation of various configuration components

o Migration of the other company suspect products that belong to the cases

(5)

 A detailed design document was created which laid out the approach for the migration. This design document contained the configuration and case data mapping. Oracle data pump utilities were used to obtain the data from the sponsor. Oracle PL/SQL were created to replicate the imported data into the Argus instance.

 The database export/import scripts were developed and validated to extract the configuration and cases (related to the product).

 Assistance was provided to the sponsor's IT team to execute these scripts and a data dump of configuration and case data was obtained.

 The database dump was imported to a staging area to copy sponsor's configuration and case data

 Oracle PL/SQL scripts were created to reconcile the sponsor configuration with the CRO's.

 Oracle PL/SQL scripts were created to migrate the case data from the staging area to Argus schema.

 A UI tool was created to turn the migration process into a one click process after the sponsor data has been obtained

 Foresight assisted the client with creation of OQ and PQ validation scripts

 Cut-over plan was developed and monitored Results Achieved

With the help of Foresight Group the client was able to assess alternatives in three days and then migrate the cases to a new instance of Argus Safety™ in under three months.

 Alternatives were assessed in three days and a faster and cheaper alternative was selected than the original plan to import cases via e2b

 The new sponsor was pleasantly surprised by how quickly the migration was performed

 Many planned manual cleanup tasks were eliminated

 The new Argus Safety™ system was rapidly instantiated so new cases could be captured while the data migration was performed on legacy cases

 The case data was migrated 100% including the attachments

 User friendly interface to execute the migration as compared to executing SQL scripts

3 ARGUS DATA MIGRATION BETWEEN ARGUS SYSTEMS

A Top 5 pharmaceutical company was consolidating the safety operations of two of its divisions. As part of the consolidation the safety data between the two divisional databases (1.5 million cases and 55,000 cases) needed to be merged.

(6)

The client was facing a number of challenges:

 The larger database, which was chosen as the destination database, was on Argus version 4.2 SP1 HF2, the source database an Argus version 2.9.2

 Both database systems used electronic submissions and therefore had submission records that needed to be merged

 The two systems used a completely different workflow, vastly different configuration especially for products and studies

 Products, studies and reporting destinations were completely reconfigured in the new system

 Several hundred cases of follow up data had already be reentered manually into the destination database as part of an early cutover and had to be excluded from the migration, but the submission information needed to be migrated

Solution

Foresight resources designed and implemented a hybrid solution using first a vertical upgrade of the source database from 2.9.2 to 4.2 SP1 and then a direct data mapping approach between the two databases. Upon completion of the data migration all cases were migrated within a weekend into the new database. Foresight resources performed the following tasks:

 Create User Requirements, Risk Assessment, Data Migration Plan

 Install copies of both database instances

 Upgrade 2.9.2 database to 4.2 SP1 using Argus scripts and schema validation tools

 Develop Data Configuration and Transform Code

 Qualify Data Transform Tools and validate the process

 Perform multiple Migration Dry-Runs to account for the complex configuration changes

 Perform Migration Pilot Run

 Create Migration Qualification Protocols for Migration, Operational Qualification and Verification

 Perform Migration Validation Run and Verification of a statistical relevant sample of cases from the source database manually compared against cases created from the destination database

 Create Migration Final Report Results Achieved

 Project was completed within a 150 day period (Configuration excluded)

 Migration Production run was completed over a weekend

(7)

4 DATA MIGRATION FROM ARGUS 4.1 TO ARISG 5.0 AND ARISG 5.0 TO ARGUS 6.X

A pharmaceutical company with annual sales of over $7 billion purchased another smaller company. As part of the agreement, the regulatory obligations including pharmacovigilance were transferred to the new license owner. The purchased company had a set of cases in an Argus 4.1 database, and the buying company was operating an ARISg 5.0.2 database. In addition, the purchaser simultaneously began an initiative to transition from their ARISg database to a new Argus 6.0 database. The following issues needed to be resolved:

 In order to minimize the support required of two safety databases, the company did not want to wait for the Argus 6.x implementation project before migrating the Argus 4.1 data into the larger ARISg 5.0 data

 Expedited reports were being initiated via two separate processes and systems and the company wanted to move to one process and system as soon as possible.

Solution

Foresight was engaged to manage two data migrations as well as the implementation of Argus 6.x. For the Argus to ARISg and then ARISg to Argus 6.x data migrations, Foresight resources designed and implemented solutions using a direct data mapping approach between the two databases. Foresight also developed verification scripts to provide 100% checking of direct data mappings between the source and target systems, and developed verification scripts for each branch of business logic for complex data mapping. Foresight resources created the following project deliverables for both data migrations:

 Data Migration Plan

 Data Migration Requirements Specification

 Data Migration Design Specification

 Data Migration Code

 Unit Test Documentation and Code Review

 Migration Qualification Protocol (IQ, OQ, and PQ)

 Dry runs of Migration Qualification

 Migration Qualification Execution

Results Achieved

 Argus to ARISg migration project completed within 26 weeks to enable the Argus system to be retired and periodic reporting from a single system

 ARISg to Argus migration project completed successfully 8% under budget

 Minimized impact to client resources

(8)

5 DATA MIGRATION FROM ARGUS 4.2 TO ARISG 6.3X

A top 10 global pharmaceutical company acquired a large biotech company. The pharmaceutical company was in the midst of moving from a custom safety database (SDB) to ARISg 6.3x immediately after the acquisition. The combined company needed to develop and implement a strategy to migrate over 150,000 cases from the biotech’s Argus 4.2 SDB to ARISg 6.3x without changing the rollout date of the ARISg implementation for the rest of the company. No available resources to support the new data migration initiative had deep content experience across Argus and ARISg.

Solution

Foresight was engaged to serve as the lead functional and technical analyst across Argus and ARISg. Foresight developed the strategy, data quality assessment, requirements, and mapping across the systems. In addition, Foresight developed many of the transformation scripts, the associated unit tests, and executed the dry runs. Foresight will continue to support the initiative through system test, user acceptance test, and production rollout. More detailed activities include:

 Leveraging previous experience with implementing ARISg and Argus as well as

migrating data across the systems to conducting technical reviews to derive and finalize the mapping document ahead of schedule

 Developing approaches (screen to screen comparisons, report comparisons, default values, reverse mapping) to facility the understanding and approval of mappings by business resources.

 Developing approaches (loading code list into “associated array” memory objects, splitting production migration across several timeframes) to significantly improve performance of migration execution

 Developing approach for creating ARISg case versions (based on submissions to health authorities) from Argus’s flat representation of cases. Method allowed the generation of aggregate reports within ARISg.

 On-time delivery of strategy, data mapping, requirements, and tested modules

 Confidence in and approval by business of Argus to ARISg mapping

 Solution that will allow 150,000 cases (generating an estimated 200,000-225,000 ICSRs) to be migrated within the given migration window

6 ARGUS DATA MIGRATION FROM ARISG

A midsize pharmaceutical company with headquarters in Japan and regional offices in the US and Europe acquired a US based biopharmaceutical and needed to migrate ~28,000 cases from the US company’s ARISg 5.1.1 database to the Japanese company’s Argus 4.1.1

(9)

database. The migration needed to be completed in no more than 20 weeks in order to meet operational and regulatory obligations. Additional challenges included:

 Identifying and reconciling the many and conflicting data entry conventions used by the US company over 10+ years

 Migrating the case files (including source documents) stored in and out of ARISg to Argus

 Identifying submission records associated with license partners and health authorities stored across the ARISg database and on paper files

Solution

Foresight resources led and implemented the global project performing the following tasks:

 Developed overall project plan and managed the execution of the project escalating key decision points and recommendations to steering committee

 Conducted mapping workshops to develop and confirm requirements resulting in over 1,000 mapping requirements

 Designed and developed PL/SQL modules to migrate cases and handle code lists transformations, data file uploads, and 200+ custom transformation rules

 Conducted several dry run migrations with business SMEs to verify migration

 Developed and executed OQ scripts to confirm each migrated field (default values, code list transformations, library transformations, and custom transformations)

 Developed the PQ strategy for sampling migrated cases that were reviewed by SMEs using regulatory reports and detailed case reports

 Developed the validation plan and other validation deliverables

 Developed and executed the hour-hour conversion weekend plan and managed the conversion weekend cut-over. The conversion weekend went through several dry runs and included the shutdown procedures from the US company and back-out procedures in case of failure

 Assisted with developing integrated process between the safety organizations of the two companies

 Completed the project within 5 months on budget and on time

 Completed the migration of the 28,000 cases over the 3 day weekend in less than 2 days with 4 warnings and no errors

 Client was able to shutdown ARISg data center on schedule

 Client was able to generate aggregate reports for migrated cases from Argus with no interruptions

Case Studies Data Migration

Case Studies

Data Migration

TABLE OF CONTENTS