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.


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.

 Diabetes mellitusICD9:250.00concept_cdconcept_dimensionconcept_path 
 X-Ray ReportLCS-i2b2:XR_RPTconcept_cdconcept_dimensionconcept_path 


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:


 Diabetes mellitusICD9:250.00concept_cdconcept_dimensionconcept_path 
 X-Ray ReportLCS-i2b2:XR_RPTdiagnostic_fact.concept_cdconcept_dimensionconcept_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.