Multi-fact Table
Space shortcuts
Space Tools

Versions Compared


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

Section Column

Multi-fact Table Setup Guide

This Setup Guide is designed to assist users with updating an existing i2b2 environment to be able to query multiple fact tables.




<div style="margin-left: 60px;">
        <span class="image-wrap" style="">
            <a class="confluence-thumbnail-link 625x390" href="">
                <img src="" alt="pdf icon" width="42" height="42" style="float:right" vertical-align="middle">





The following items are required for multi-fact tables to work correctly. 

  • You have a working version of i2b2 that is running release 1.7.12 or higher.
  • All your fact tables reside in the same database.
  • All fact tables have the same data structure including primary keys & indexes.
  • observation_fact remains as one of the fact tables




This setup guide assumes the following: 

  • You
have a working version of i2b2 that is running release 1.7.09 or higher.
  • The i2b2 Admin module has been installed.
  • All of your
    • know how to access and update your database using SQL editor
    • You are familiar with i2b2 data; metadata & data mart tables. (See i2b2 Data Mart & Ontology page for a quick overview)
    • Your fact tables have been setup
    and reside in the same database
    The assumption is that the
    • Your fact tables are organized by concept: e.g. all diagnoses facts are in one table; all medication facts in another

    <div class="i2b2-panel">
    	<table class="i2b2-important">
    			<col width="24">
    					<img src="" width="16" height="16" align="absmiddle" alt="important" border="0">
    					<br>If you are new to i2b2 or not familiar with how to install and setup the i2b2, we highly recommend you look at the <a href="">i2b2 Installation Guide</a> and go through the
    steps to setup the i2b2 demo environment.

    Setup Process 

    Setting The process of setting up the CRC i2b2 to use multiple fact tables is fairly easystraight forward. If Once you are using your existing data sources and projects all you need to do is turn on the new parameter in the file and update have updated your software to at least version 1.7.12 or higher, create your new fact tables and load the data into them. There are only two remaining steps.

    1. Update the terms in your metadata tables so the
    crc knows where to find the fact table. Steps on how to do this are located in the Update CRC Properties File section of this guide.

    However, if you are setting up a new data source and / or project there are additional steps you need to take. That are outside the scope of this project. Additional information on how to do this has been provided on the Additional Resources page of this space.

    1. i2b2 server knows what fact table to query
    2. Turn the Multi-fact table feature on in the hive_cell_params table. Refer to Multi-fact table home

    This setup process is designed to assist users with updating an existing i2b2 environment to be able to query multiple fact tables

    Update Ontology Tables

    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

    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 

     Update multifacttable parameter 

    Using SQL editor, update the param_name_cd queryprocessor.multifacttable value to true in hive_cell_params  table. When the value is set to true the CRC will now look for multiple fact tables. By default, this value is set to false. If you are going to be using multiple fact tables you will need to update this value  to be true.

    Example in Oracle, use the following update statement to manually update the value to true:

    UPDATE hive_cell_params  set value ='true' where param_name_cd ='queryprocessor.multifacttable' and cell_id ='CRC';