Multi-fact Table
Space shortcuts
Space Tools
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »



The standard i2b2 star schema only allows for one fact table and in the i2b2 database this is called the observation_fact table. Release 1.7.09 now allows for multiple fact tables. In order to use this feature you will need to update your Ontology tables so the i2b2 server knows to search another fact table when running a query.

Need to update the value c_facttablecolumn name column. Currently it is just the column name. However if you want to redirect the system to search another fact table for that term you need to preface the column name with the table name. (see examples below).

Need to update the metadata tables for each term you want redirected to an alternate fact table (one that is not named observation_fact). The c_facttablecolumn column in the i2b2 tab

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

For the purpose of this document, 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
  2. lab_fact
  3. diagnostic_fact

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.




  • No labels