Mapper Schema Design (1.0)

1.                      Table of Contents

1. Table of Contents………………………………………………………….…...2
2. Document Version History………………………………………………….. 3
3. Introduction…………………………………………………………………… 4
   1.         Encoding_Dimension……………………………………………………...4
   2.         Map_Dimension…………………………………………………………... 4
   3.         Observation_Fact………………………………………………………….. 5
   4.         Map_Data_Fact…………………………………………………………….. 5
   5.         Observation_Map_Link….……………………………………………….. 6
   6.         Schema Design……………………………………………………………...6

2.                      Document Version History

3.                      Introduction

The proposed Mapper functionality that enables creation of maps for transformation of data from source encoding to target encoding required several changes on the original i2b2 schema.   These changes that were effected on the i2b2 schema are outlined herewith on a table by table basis.

3.1                         ENCODING_DIMENSION

This is a new table created for the purpose of storing the various encodings used in the Mapper system.  ENCODING_CD serves as the primary key for the table.   Other columns of the table are enlisted below:

• ENCODING_NAME – the name of the encoding
• ENCODING_DESC – description of the encoding
• CREATE_USER – the user that created the encoding
• CREATE_DATE – the date of creation of the encoding
• UPDATE_USER – the user that last updated the encoding
• UPDATE_DATE – the date of last update of the encoding

3.2                         MAP_DIMENSION

This table has been created to store info on the mapper xml instances uploaded onto the system.  MAP_ID which is a running sequence no. serves as the primary key for this table.  Other columns of this table are enlisted below:

• MAP_NAME – the name assigned for this map
• CONCEPT_CD – the concept code for which the map has been written
• CONCEPT_PATH – the concept path for which this map applies
• SOURCE_ENCODING – the source encoding of the map
• TARGET_ENCODING – the target encoding of the map
• MAP_PATH – the physical path of the mapper xml instance in the system
• MAP_DESC – description of the map
• IMPORT_DATE – the date of import of the map into the system
• UPDATE_DATE – the date of last update of the map
• UPLOAD_USER – the user that uploaded the map onto the system
• UPDATE_USER – the user that last updated the map
• LAST_RUN_DATE – the last execution date of the map

3.3                         OBSERVATION_FACT

• Added column CONCEPT_PATH referenced from table CONCEPT_DIMENSION for storing the concept path pertaining to the concept code used in this table.  This is required since the original i2b2 design supports only the CONCEPT_CD (this can be the same for multiple concept paths) and resolving the concept path from the same is difficult.
• Added column ENCODING_CD referenced from table ENCODING_DIMENSION to denote the encoding type this record is encoded with.
• Column OBSERVATION_FACT_ID added to store a running sequence no. for uniquely identifying each record in the table.

3.4                         MAP_DATA_FACT

• This is a new table that closely resembles the OBSERVATION_FACT and is designed to store the records created as a result of map execution.  All the transformed records in target encodings will be housed in this table.
• This table has been created to segregate the transformed data from the source data and also to keep the i2b2 fact table (OBSERVATION_FACT) design intact.  Mapper functionality required several design changes on the OBSERVATION_FACT which might have rendered this table incompatible with future i2b2 releases.  This new table resolves the aforesaid issue by providing a similar structure as the i2b2 fact table with the flexibility of effecting new design changes required for the Mapper functionality.  
• In accordance with Mapper functionality the new table is designed to have a new primary key (rather a unique key) with the columns CONCEPT_PATH, ENCODING_CD, MAP_ID being added to the existing primary key combination of the i2b2 fact table (OBSERVATION_FACT).
• MAP_ID column of this table is referenced from MAP_DIMENSION and stores info on the map that created this record.
• MAP_DATA_FACT_ID column is a running sequence no. that uniquely identifies each record in the table and is similar to the OBSERVATION_FACT_ID column of the OBSERVATION_FACT.
• All other columns in this table resemble those in the OBSERVATION_FACT both in name and purpose.

3.5                         OBSERVATION_MAP_LINK

This table provides the link between the i2b2 fact table (OBSERVATION_FACT) and mapper fact table (MAP_DATA_FACT).   In other words it links the transformed records with the source records.   The combination of OBSERVATION_FACT_ID (referenced from OBSERVATION_FACT) and MAP_DATA_FACT_ID (referenced from MAP_DATA_FACT) serve as the primary key of the table.

3.6                         SCHEMA DESIGN