Multi-fact Table
Space shortcuts
Space Tools

Versions Compared

Key

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

STILL UPDATING THIS PAGE

Update

The

How it works now

Since 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 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 (one that is not named observation_fact). The c_facttablecolumn column in the i2b2 tab. 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

For the purpose of this document, the 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_factfact  (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.