[[Pathology|back]]

Version 1 of the i2b3 pathology data integration load uses two SSIS packages, loaded on the UHL Data warehouse server, to move data from the DWPATH database on the UHL data warehouse server to i2b2 databases on the UHLSQLBRICCSDB\UHLBRICCSDB server.  One package moves data to the production i2b2 database, the other moves data to the test database.  

== Packages

The two packages are identical and only differ in the destination database and the set of patients that they extract.

|| ||Package Name||Destination Database||Patient Set||
||Test||Pathology To I2B2.dtsx||i2b2_b1_data||Test patients from i2b2_b1_data||
||Production||Pathology To I2B2 APP03.dtsx||i2b2_app03_b1_data||Actual patients from i2b2_app03_b1_data||

== Procedure

Both packages use the same following three steps which utilise the DWBRICCS database on the UHL data warehouse server and the i2b2ClinDataIntegration database on the I2B2 server as an intermediate databases.

=== 1. Import I2B2 Patients to DWBRICCS

Runs the stored procedure USP_DWH_IMPORT_BRICCS_PATIENTS in the DWBRICCS database on the UHL data warehouse, which copies patients from the destination database on the I2B2 database server to the DWBRICCS database using a linked SQL server.

=== 2. Delete Pathology from I2B2

Runs the stored procedure USP_DWH_DELETE_PATHOLOGY_FROM_I2B2 in the i2b2ClinDataIntegration database, which deletes all pathology data from the Observation_Fact table of the destination i2b2 database.  These are identified as all records where the concept_cd begins with 'PAT:'.

=== 3. Insert Pathology into I2B2

Runs the stored procedure USP_DWH_INSERT_PATHOLOGY_I2B2 in the i2b2ClinDataIntegration database, which loads data into the destination i2b2 database from the view UVW_BRICCS_PATHOLOGY_RESULTS in the DWBRICCS database on the UHL data warehouse as a linked server.