Multi-fact Table Setup Guide
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 load the data into them there . There are only two remaining steps.
- Update the terms in your metadata tables so the i2b2 server knows what fact table to query
- Turn the Multi-fact table feature feature on in the hive_cell_params table. Refer to Multi-fact table home
This setup process is designed to assist users with updating an existing i2b2 environment to be able to query multiple fact tables
Update Ontology 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.
Multiple fact tables
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:
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
Update multifacttable parameter
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';