This Setup Guide is designed to assist users with updating an existing i2b2 environment to be able to query multiple fact tables.
|
The process of setting up the i2b2 to use multiple fact tables is straight forward. Once you have updated your software to at least version 1.7.12 or higher, create your new fact tables and loaded the data into them there are only two remaining steps.
This setup process is designed to assist users with updating an existing i2b2 environment to be able to query multiple fact tables
.
How classic i2b2 works: The standard i2b2 star schema only allows for one fact table. In the i2b2 database this is the observation_fact
table. When running a query in the i2b2, the CRC queries against the observation_fact table by default.
The CRC needs to know the name of the fact table to search when a term is used in a query. This is done by
updating the metadata tables for each term you want redirected to an alternate fact table. You need to
update the value in c_facttablecolumn column to now be facttablename. columnname. Examples are provided below.
The good news is, you only need to update those terms you want to redirect to a different fact table. If the term is still going to the observation_fact table you do not need to update it. If a table name does not preface the column name the system will work as it does now and the observation_fact table will be searched.
Example:concept_cd vs. lab_facts.concept_cd
The sample tables shown below are only displaying 5 of the 25 columns that are currently part of the i2b2 metadata tables.
Single fact table
This sample shows how the i2b2 metadata table is setup to work with a single fact table.
c_name | c_basecode | c_facttablecolumn | c_tablename | c_columnname |
---|---|---|---|---|
Diabetes mellitus | ICD9:250.00 | concept_cd | concept_dimension | concept_path |
Amoxicillin | NDC:559553071670 | concept_cd | concept_dimension | concept_path |
Hematocrit | LOINC:4544-3 | concept_cd | concept_dimension | concept_path |
X-Ray Report | LCS-i2b2:XR_RPT | concept_cd | concept_dimension | concept_path |
In this sample, we take the above table and update it to point to multiple fact tables. The diagnoses are still going to the observation_fact table, the other three facts are going to one of these three new fact tables:
1. medication_fact (Amoxicillin)
2. lab_fact (Hematocrit)
3. diagnostic_fact (X-Ray Report)
Using this example, the i2b2 metadata table would look like:
c_name | c_basecode | c_facttablecolumn | c_tablename | c_columnname |
---|---|---|---|---|
Diabetes mellitus | ICD9:250.00 | concept_cd | concept_dimension | concept_path |
Amoxicillin | NDC:559553071670 | medication_fact.concept_cd | concept_dimension | concept_path |
Hematocrit | LOINC:4544-3 | lab_fact.concept_cd | concept_dimension | concept_path |
X-Ray Report | LCS-i2b2:XR_RPT | diagnostic_fact.concept_cd | concept_dimension | concept_path |
In the above example, you will notice the entry for Diabetes does not have the prefix 'observation_fact.'. This
is because it is not needed. If a prefix does not exist the system will automatically look to the observation_fact table
Using SQL editor, update the param_name_cd queryprocessor.multifacttable value to true in hive_cell_params table. When the value is set to true the CRC will now look for multiple fact tables. By default, this value is set to false. If you are going to be using multiple fact tables you will need to update this value to be true.
Example in Oracle, use the following update statement to manually update the value to true:
UPDATE hive_cell_params set value ='true' where param_name_cd ='queryprocessor.multifacttable' and cell_id ='CRC';