Multi-fact Table
Space shortcuts
Space Tools

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

How it works now

Since the standard i2b2 star schema only allows for one fact table and in the i2b2 database this is the observation_fact table. When running a query in the i2b2, the CRC knows to search the observation_fact table (because there is only 1) and it knows to use the concept_cd column in the observation_fact table because we define that in the metadata table(s). In the metadata table(s) there is a column called c_facttablecolumn that contains the name of the column in the fact table that will be used when searching for the associated term.

Example: 

The term in your metadata may be the name of a provider. When this term is used in a query you want the 'provider_id' column to be used in the fact table. So in your metadata table c_facttablecolumn = provider_id

 

 

Update Metadata

The CRC now 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

 

Info

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_namec_basecodec_facttablecolumnc_tablenamec_columnname...
 Diabetes mellitusICD9:250.00concept_cdconcept_dimensionconcept_path 
 AmoxicillinNDC:559553071670concept_cdconcept_dimensionconcept_path 
 HematocritLOINC:4544-3concept_cdconcept_dimensionconcept_path 
 X-Ray ReportLCS-i2b2:XR_RPTconcept_cdconcept_dimensionconcept_path 

 

Multiple fact tables

In this sample, we take the above table and updating it to use 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_namec_basecodec_facttablecolumnc_tablenamec_columnname...
 Diabetes mellitusICD9:250.00concept_cdconcept_dimensionconcept_path 
 AmoxicillinNDC:559553071670medication_fact.concept_cdconcept_dimensionconcept_path 
 HematocritLOINC:4544-3lab_fact.concept_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.