Transcription
Data Migrationgin a live GXPenvironmentKaren StickneyKSti kCSDM Information System AnalystLabWare CEC 201219 December 2013
Presentation Overview Brief Description of Legacy System Migration Methodology Validation Process2
Legacyg y Systemy A validated, custom software application for tracking andmanaging GLP and GCP samples and associated data. System comprised of approximately 142 studiesincluding both active and closed studies. Each studybehaves as a self contained database in its own right. High variability of data between studies Examples:pDocumented Visit format Visit and Visit time in one field (visit: Day 1 Predose, visit time:blank) Visit and Visit time in separate fields (visit: DAY 1, visit time:Predose)3
MigrationMethodologyggy Overview Initial mapping and migration tool development Data migration process Move importedpdata into the live Production systemy4
Flow DiagramgData ImportingData Verification andValidationData ManipulationInitial Mapping and MigrationTool DevelopmentData ImportingData Verification andValidation5
Initial Field Mapping and Migration ToolDevelopmentp The initial mapping of all fields between the Legacysystemtandd theth newlyl validatedlid t d ResearchRh LIMS (RLIMS)system.Example of mapping for Clinical ProtocolsLW ColumnLOGIN DATELOGIN BYSAMPLED DATESAMPLED DATETypeSDMS MappingDate/Time SAMPLE.CreatedStampCommentsparsed outstr 20SAMPLE.CreatedStampparsed outSDMS.SAMPLE DOCUMENT.CollectionDate Date/TimeCollectionTimeSAMPLE TYPEPROJECTstr 20str 20SAMPLE DOC.KitTypeMappedCONDITIONstr 20SDMS.SAMPLE.Conditionmapped6
Initial Mapping and Migration ToolDevelopment(cont)p() This process took several months and was re-visited multipletimes throughout the migration process. This was performedusing a representative sampling across all studies. Riskassessmentt completedl t d ffor eachh fifield.ld Custom migration tool configured and tested
Data Export/Mappingppp gg performedpg y Data Cleaningon the studies in the Legacysystem for how data was reported within a study andacross all studies. Duplicate Sample ID’sID s, samples with more than one location,locationblank fields (Sample ID, Condition) Remove commas from any text field (such as comment anddiscrepancy fields) The data was exported out of the Legacy system utilizingthe systems data export tool. Templates created toensure file format consistency. File are then saved as a.CSV file8
Data Export/Mappingppp g (cont)() Study specific mapping files were also created for sampletype, assay, visit and assay status.EExamplel off visiti it mappingi filfileSample doc.visitDAY 0 PRE DOSE 1 DAY 28-35 POST DOSE 2 DAY 28-42 PRE DOSE 2 UNSCHEDULED 1 UNSCHEDULED 2Orders.Clin trl visitDAY 0Orders.Clin trl event PRE DOSE 1DAY 28-35DAY 28-42UNSCHEDULED 1 UNSCHEDULED 2POST DOSE 2PRE DOSE 2N/AN/A
Flow DiagramgData ImportingData Verification andValidationData ManipulationInitial Mapping and MigrationTool DevelopmentData ImportingData Verification andValidation10
Data MigrationToolg This is a set of custom written Perl scripts to processexport files and create files for upload to the RLIMSdatabase, and custom written SQL scripts to verify thed t was correctlydatatl loadedl d d intoi t theth RLIMS databased t bafterfteach migration. A Custom Data Migration Tool was required to performprocessing and conversion because of significantdifferences between the two systems.11
Data Manipulationp Within the PERL scripts (migration tool) Data Manipulation is required to meet the convention of theg changingg g date format 20070503 to 5/3/07RLIMS database. E.g. By evaluation of the Mapping files Example: Example: Patient Gender In Legacy system this field could have a M, F, Female, Male,FEMALE, MALE In Research LIMS system this data was mapped to either Male,Female, M – Male, F - Female12
Flow DiagramgData ImportingData Verification andValidationData ManipulationInitial Mapping and MigrationTool DevelopmentData ImportingData Verification andValidation13
Data importto Stagingpg g Environment This was done to minimize risk to the live ProductionSystem.System Allows for removal and re-importation if needed withoutimpacting daily business operations.operations Increments table – needed to set the increments in theProduction staging system so that when data moved to theactual Production systemsystem, there would not be a conflictconflict. Example: Sample Number was set to 900,000,000 After data verification (validation),(validation) data moved into thelive Production system.14
Validation Methodologygy Data Migration Validation Black Box Approach for Custom Migration Tool Significant Time & Cost Savings Validation based on expected outcome of “known data sets” Combination of Simulated & Real Data Sets OracleOracle’ss External Tables used for Post Migration Verification Original Data Source lacked Native DatabaseExternal Tables allowed comparison of “apples to apples”SQL “Minus”Minus Queries compared External Tables to LIMS TablesAutomated100% Source-to-Destination15
Data MigrationProjectStatusgj21%29%Number IncompleteNumber CleanedNumber Complete71%16
Data migration process Move imppyorted data into the live Production system 4. Flow Diagram Data ImportingData Importing Data Verification and Validation Initial Mapping and Migration Tool Development Data Manipulation Data Importing Data Verification and Validation 5.
