= i2b2 - UHL clinical informatics system integration PATS v2

Tags: [[i2b2]]

=== 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.
\\Note that for each version you may need to search for a column of a different name form the last version (cos there good like that, keep you on your toes).

\\ \\**Step 2.** As per summary.
\\ \\**Step 3.** 
\\Find the @OntologyID using SELECT * FROM [i2b2ClinDataIntegration].[dbo].[Ontology]
\\ Find @ParentID using
\\
  SELECT *  FROM [i2b2ClinDataIntegration].[dbo].[!OntologyCode] \\where OntologyID = ## and [!OntologyNodeTypeCode] = 'c' \\ order by [Name]
\\@Name, what ever you think is a better, more obvious name than the one given 
\\@!SourceSystem, you'll find this in the proc [CBRU_ETL_CreateObservations_DWPATS_XXXXXXX] that you have just created.
\\ and finally the @!SourceCode is the !FieldName again found in the proc you have just created.

\\ \\**Step 4.** As per summary.


\\
\\
\\
=== 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.
1. Improve the error handling and reporting.
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]]