Changes between Version 3 and Version 4 of i2b2 - UHL clinical informatics system integration PATS v2

Timestamp:

09/29/16 10:35:01 (8 years ago)

Author:

dan.lawday

Comment:

--

i2b2 - UHL clinical informatics system integration PATS v2

@@ -3 +3 @@
 Tags: [[i2b2]]
 
-Version 2 of the i2b2 PATS data integration load will also use SSIS, but will address the following issues with version 1.
+=== The PATSV2 data all comes from [UHLDWH].[DWBRICCS].[DWPATS] To add another field from here into the Databases on UHLSQLPRIME01\UHLBRICCSDB (for instance the Genvasc or Briccs DB) you will need to do the following:\\ \\
+
+Summary:
+Step 1. create a new proc in [UHLDWH].[DWBRICCS]
+called [dbo].[CBRU_ETL_CreateObservations_DWPATS_XXXXXXX]
+with XXXXXXX being the name somthing like CSPost with CS meaning **C**ardia**S**urgery and Post meaning post surgery.
+ 
+Step 2. Add the new proc to PROC [UHLDWH].[DWBRICCS].[dbo].[LCBRU_ETL_CreateObservations] 
+
+Step 3. Add the new field/s to the ontology [i2b2ClinDataIntegration].[dbo].[!OntologyCode] and [i2b2ClinDataIntegration].[dbo].[!OntologySourceCode] tables
+useing the proc [i2b2ClinDataIntegration].[dbo].[!CreateOntologyCode]
+\\@OntologyID           = 
+\\@ParentID                     = 
+\\@Name                         = 
+\\@!SourceSystem                = 
+\\@!SourceCode          = 
+
+step 4. This gets automatically done by the way however is importance to acknowledge, The ontology needs to be undated through the other Databases using proc dbo.!UpdateOntologyConceptCodes
+
+
+Details:\\
+Step 1. to create the [dbo].[CBRU_ETL_CreateObservations_DWPATS_XXXXXXX] you will need to find all the PostProcedure and PreProcedure tables relevant and union join them all together, these are the different formats that have been produced over time any which vary slightly over each iteration, however with a little data manipulation they can be stitched together.
+for example to gain data from CardiaSurgery you will need all of the following tables all from ([UHLDWH].**[DWPATS]**.dbo.):\\
+* CS_PostProcedure
+* CS_PreProcedure
+* CS2003F_PostProcedure
+* CS2003F_PreProcedure
+* CS2010F_PostProcedure
+* CS2010F_PreProcedure
+* CS2010G_PostProcedure
+* CS2010G_PreProcedure
+and also for the WHO_SYSTEM_NUMBER and NHSNumber...
+* DWPATS.dbo.Demographics_ALLDEMOGRAPHICS
+* DWBRICCS.dbo.I2B2_Project_Patients
+\\
+as you can see the Pre and Post versions listed above were revised in 2003 and twice in 2010 leading to four versions!.\\ With each version you'll get a Followup, Post, Pre and Sub, however for this example we only needed Pre and Post.
+
+The 
+
+
+Step 2. 
+
+
+
+
+\\
+\\
+=== Below to be deleted...
+
+Version 2 of the i2b2 PATS data integration load will also use SSIS, but will address the following issues with version 1.\l
 
 1. Address and document the correct procedure for dealing with duplicates and missing data, by getting Claire Hutton to define which items are true duplicates and may be discarded and identifying a means of distinguish between duplicates where both should be loaded into i2b2.
@@ -9 +58 @@
 1. Move the SSIS packages onto the BRICCS SQL Server so that we can control their running and updating.
 1. Be configurable so that the source and destination databases can be changed more easily.
+=== end to be deleted
 
 [[BackLinks]]