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


Ignore:
Timestamp:
09/29/16 10:45:50 (8 years ago)
Author:
dan.lawday
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • i2b2 - UHL clinical informatics system integration PATS v2

    v4 v5  
    55=== 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:\\ \\
    66
    7 Summary:
    8 Step 1. create a new proc in [UHLDWH].[DWBRICCS]
     7== Summary:
     8\\**Step 1.** create a new proc in [UHLDWH].[DWBRICCS]
    99called [dbo].[CBRU_ETL_CreateObservations_DWPATS_XXXXXXX]
    1010with XXXXXXX being the name somthing like CSPost with CS meaning **C**ardia**S**urgery and Post meaning post surgery.
    1111 
    12 Step 2. Add the new proc to PROC [UHLDWH].[DWBRICCS].[dbo].[LCBRU_ETL_CreateObservations]
     12\\ \\**Step 2.** Add the new proc to PROC [UHLDWH].[DWBRICCS].[dbo].[LCBRU_ETL_CreateObservations]
    1313
    14 Step 3. Add the new field/s to the ontology [i2b2ClinDataIntegration].[dbo].[!OntologyCode] and [i2b2ClinDataIntegration].[dbo].[!OntologySourceCode] tables
     14\\ \\**Step 3.** Add the new field/s to the ontology [i2b2ClinDataIntegration].[dbo].[!OntologyCode] and [i2b2ClinDataIntegration].[dbo].[!OntologySourceCode] tables
    1515useing the proc [i2b2ClinDataIntegration].[dbo].[!CreateOntologyCode]
    1616\\@OntologyID           =
    … …  
    2020\\@!SourceCode          =
    2121
    22 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
     22\\ \\**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
    2323
    24 
    25 Details:\\
    26 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.
     24\\
     25== Details:\\
     26\\**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.
    2727for example to gain data from CardiaSurgery you will need all of the following tables all from ([UHLDWH].**[DWPATS]**.dbo.):\\
    2828* CS_PostProcedure
    … …  
    4040as 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.
    4141
    42 The
     42\\ \\**Step 2.** As per summary.
     43\\ \\**Step 3.**
     44\\Find the @OntologyID using SELECT * FROM [i2b2ClinDataIntegration].[dbo].[Ontology]
     45\\ Find @ParentID using SELECT *  FROM [i2b2ClinDataIntegration].[dbo].[OntologyCode]
     46  where OntologyID = ## and [OntologyNodeTypeCode] = 'c'
     47  order by [Name]
     48\\@Name, what ever you think is a better, more obvious name than the one given
     49\\@!SourceSystem, you'll find this in the proc [CBRU_ETL_CreateObservations_DWPATS_XXXXXXX] that you have just created.
     50\\and the @!SourceCode is the FieldName again found in the proc you have just created.
     51
     52\\ \\**Step 4.** As per summary.
    4353
    4454
    45 Step 2.
    46 
    47 
    48 
    49 
     55\\
    5056\\
    5157\\