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 CardiaSurgery 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.

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.
2. Improve the error handling and reporting.
3. Move the SSIS packages onto the BRICCS SQL Server so that we can control their running and updating.
4. Be configurable so that the source and destination databases can be changed more easily.

end to be deleted

Error: Macro BackLinks(None) failed

'Environment' object has no attribute 'get_db_cnx'