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.

Update Metadata

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

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.