Health Ontology Mapper
Space shortcuts
Space Tools

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Mapper Schema Design (1.3)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Table of Contents

Table of Contents

Document Version History

Introduction

ENCODING_DIMENSION

MAP_DIMENSION

OBSERVATION_FACT

MAP_DATA_FACT

MAP_AGGR_FACT

Schema Design

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Document Version History

 

Date

Version

Description

Author(s)

07/01/2008

1.0

Initial version

Prakash Lakshminarayanan

07/16/2008

1.1

Incorporated changes pertaining to Map data fact table

Prakash Lakshminarayanan

07/24/2008

1.2

Synchronized mapper schema with latest i2b2 schema changes

Prakash Lakshminarayanan

09/22/2008

1.3

Revised schema design per inputs from the group to improve database performance

Prakash Lakshminarayanan

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

 

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

 

 

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_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

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.

 

 

MAP_DATA_FACT

 

  • This is a new table 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 with the exception of aggregate and archetype data.  Only records that have a one to one mapping with source record in OBSERVATION_FACT will be stored here.
  • 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 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 combination with the columns CONCEPT_PATH, ENCODING_CD and MAP_ID added onto the primary key set of OBSERVATION_FACT.
  • CONCEPT_PATH column is referenced from CONCEPT_DIMENSION to indicate the concept path for which this map applies.
  • MAP_ID column of this table is referenced from MAP_DIMENSION and stores info on the map that created this record.
  • ENCODING_CD is referenced from ENCODING_DIMENSION and holds info on the encoding used in creation of this record.
  • OBSERVATION_FACT_ID column is used to refer back source record of OBSERVATION_FACT from which this record has been generated.
  • MAP_DATE column is designed to store the record creation date.
  • All other columns in this table resemble those in the OBSERVATION_FACT both in name and purpose. 

 

 

MAP_AGGR_FACT

 

  • This new table has been exclusively designed to house aggregate and archetype data created as a result of map execution.
  • To aid aggregate and archetype data storage this new table has a primary key combination consisting of columns CONCEPT_PATH, ENCODING_CD and MAP_ID.
  • All other columns in this table resemble those in the OBSERVATION_FACT both in name and purpose with the only exception of MAP_DATE column.  Only few columns from the OBSERVATION_FACT have been retained so as to support aggregates and archetypes.  In other words only columns on which aggregation can be performed have been retained.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Schema Design