i2b2 Developer's Forum
Space shortcuts
Space Tools
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

Modifiers in i2b2 Data Model



Data Model of Modifiers in i2b2


Introduced in Core i2b2 Version 1.6


In Version 1.6 of i2b2 we begin to use the modifier_cd column in the observation_fact table.


The use of this column allows a single fact to be modified with an unlimited number of codes, for example:
A drug can be modified with dose, route, and frequency.
A CPT procedure can be modified with standard CPT modifier codes such as
62 (two surgeons) and AA (anesthesia performed personally by anesthesiologist.)
Essentially, the modifier is a way to extend the concept code. Much of what is expressed as a modifier could be expressed by making multiple concept- modifier combinations in the concept dimension, a simple and effective approach in many cases. However, when modifiers are associated with values (such as with dose), or can occur in many combinations (as in CPT or SNOMED modifiers,) it becomes simpler to use the modifier_cd column.
In the fact table, the modifier is represented by the modifier_cd column, which is now part of the primary key on the fact table. This column is a varchar(50) and its codes are generally 1-30 characters with a 3-19 character prefix that represents the concept type and a variable-length suffix that describes the modifier, often with a colon separating the prefix
from the suffix, for example MED:DOSE. However, the only true constraint is that the code is 50 characters or less. Just as concepts may have values
associated with them (as with lab tests), or not; a modifier may have a
value associated with it, as in the case of a medication dose, which has a numeric value representing the dosage amount; or it may be stand-alone, that is, it does not have an associated value, as occurs with CPT modifiers. In cases where there is a value associated with the modifier, the value is
expressed in the columns of the fact table in the same way as values associated with other concept/value pairs, such as lab tests.
When modifiers are associated with an observation, there are at least two, and often multiple, rows for that observation in the observation_fact table. In the simplest form, there is a 'base' row representing the concept and one additional row per modifier. Sometimes a concept may be repeated more than once in a given encounter or visit, or one concept may be associated with more than one modifier of the same type, for example,
when a medication is given with two different does. To handle this situation, the field instance_num is used, which groups facts into clusters and allows serveral of the same type of modifier to be attached to one fact. There are several options for use of the instance_num column, but in the simplest case it is an integer that ties together several rows representing the same observation.
Below is an example where there are no values associated with the modifier. This example is a CPT code for a Caesarian surgery performed on patient #123 at visit #107 on 3/4/2006. A CPT modifier is a two digit alpha or numeric code that indicates a procedure or service was altered in some way from the stated code descriptor without changing the definition. In this case the CPT modifiers are 62 = "two surgeons", AA = "anesthesia services performed personally by anesthesiologist", and TH = "Obstetrical treatment/services, prenatal or postpartum."



Example 1, CPT for Caesarian surgery with three modifiers for patient # 123 at visit #107 on 3/4/2010.

Patient
_num

Encount er_num

Instanc e_num

Conce pt_cd

Start
_date

Modifi er_cd

ValTyp e_Cd

Tval_
char

Nval_
num

123

107

1

cpt:59
622

2006
0304

@

<null
>

<null
>

<null
>

123

107

1

cpt:59
622

2006
0304

cptmo d:62

<null
>

<null
>

<null
>

123

107

1

cpt:59
622

2006
0304

cptmo d:AA

<null
>

<null
>

<null
>

123

107

1

cpt:59
622

2006
0304

cptmo d:TH

<null
>

<null
>

<null
>




In example 2, a prescription is written for patient #123 at visit #567 for
Aspirin 325 mg QD PO on 4/4/2010

Patient
_num

Encount er_num

Instanc e_num

Conce pt_cd

Start
_date

Modifie r_cd

ValTyp e_Cd

Tval_
char

Nval_
num

123

567

1

med:a spirin

2010
0404

@

<null
>

<null
>

<null
>

123

567

1

med:a spirin

2010
0404

MED(big grin) OSE

N

E

325

123

567

1

med:a spirin

2010
0404

MED:F REQ

T

QD

<null
>

123

567

1

med:a spirin

2010
0404

MED:R OUTE

T

PO

<null
>





If the patient was written for another baby (83 mg) Aspirin BID PO on the same day, it would be represented as:

Patient
_num

Encount er_num

Instanc e_num

Conce pt_cd

Start
_date

Modifie r_cd

ValTyp e_Cd

Tval_
char

Nval_
num

123

567

2

med:a spirin

2010
0404

@

<null
>

<null
>

<null
>

123

567

2

med:a spirin

2010
0404

MED(big grin) OSE

N

E

83

123

567

2

med:a spirin

2010
0404

MED:F REQ

T

BID

<null
>

123

567

2

med:a spirin

2010
0404

MED:R OUTE

T

PO

<null
>




Combining the results of examples 1 and 2 in the observation_fact table produces the following set of rows:

Patient
_num

Encount er_num

Instanc e_num

Conce pt_cd

Start
_date

Modifie r_cd

ValTyp e_Cd

Tval_
char

Nval_
num

123

107

1

cpt:59
622

2006
0304

@

<null
>

<null
>

<null
>

123

107

1

cpt:59
622

2006
0304

cptmo d:62

<null
>

<null
>

<null
>

123

107

1

cpt:59
622

2006
0304

cptmo d:AA

<null
>

<null
>

<null
>

123

107

1

cpt:59
622

2006
0304

cptmo d:TH

<null
>

<null
>

<null
>

123

567

1

med:a spirin

2010
0404

@

<null
>

<null
>

<null
>


123

567

1

med:a spirin

2010
0404

MED(big grin) OSE

N

E

325

123

567

1

med:a spirin

2010
0404

MED:R OUTE

T

QD

<null
>

123

567

1

med:a spirin

2010
0404

MED:F REQ

T

PO

<null
>

123

567

2

med:a spirin

2010
0404

@

<null
>

<null
>

<null
>

123

567

2

med:a spirin

2010
0404

MED(big grin) OSE

N

E

83

123

567

2

med:a spirin

2010
0404

MED:R OUTE

T

BID

<null
>

123

567

2

med:a spirin

2010
0404

MED:F REQ

T

PO

<null
>




Looking at the medications in the example above, there are two entries for the concept med:aspirin. The only way to distinguish whether the 325 mg dose is QD or BID is to look in the instance_num column. Since the 325 mg dose has instance_num = 1, it is associated with the frequency for instance_num = 1, which is QD.
By default, the value information and data in all the non-key columns will be assumed to pertain to the modified concept, so there should usually be included a row for the base concept with an "@" in the modifier column. This will allow value information to be applied to the base concept code. Note
that the modifier code is also a way that value information could be attached to the provider code.
The only change to a standard i2b2 database to enable it to work with modifiers is to add an instance number to the instance_num column. Most databases will begin with only one row per observation, and in those cases all rows can be initialized with an instance_num of "1".



The Modifier_Dimension Table and Ontology Table
The modifier_cd is a data element that describes attributes of a fact and it is a dimension column in the fact table. Information about the modifier is contained in a new dimension table in the i2b2 star schema, the modifier_dimension table.
MODIFIER_DIMENSION TABLE
Clustered primary key = modifier_path

Column name

SQL Server data type

Description

MODIFIER_PATH

VARCHAR(700)

The c_fullname of the concept being modified. The
modifier_path is the primary key for the table.

MODIFIER_CD

VARCHAR(50)

Corresponds to the modifier_cd in the fact table.

NAME_CHAR

VARCHAR(2000)

Human-readable name of the modifier

MODIFIER_BLOB

TEXT

Text field for any related data about the modifier.

UPDATE_DATE

DATETIME

Date the field was updated

DOWNLOAD_DATE

DATETIME

Date the field was downloaded

IMPORT_DATE

DATETIME

Date the field was imported

SOURCESYSTEM_CD

VARCHAR(50)

A Code representing the system from which the concept and
modifier originated

UPLOAD_ID

INT

An internal id related to the upload instance





A modifier may be associated with a group of concepts, such as dose with medications, but it is usually not universal, that is, a particular
modifier would not be associated with all concepts. For example, a dose can only modify a medication, it can't modify a diagnosis or procedure. For this
reason, the model for representing modifiers includes a new field in the ontology table, called m_applied_path, that indicates to which paths in the
ontology hierarchy the modifier refers.



ONTOLOGY TABLE
Primary key = c_fullname + c_name

Column name

SQL Server data type

Description

C_HLEVEL

INT

The hierarchical level of the


 

 

concept

C_FULLNAME

VARCHAR(700)

The path of the concept

C_NAME

VARCHAR(2000)

Human readable name of the concept/modifier

C_SYNONYM_CD

CHAR(1)

 

C_VISUALATTRIBUTES

CHAR(3)

 

C_TOTALNUM

INT

 

C_BASECODE

VARCHAR(50)

 

C_METADATAXML

TEXT

 

C_FACTTABLECOLUMN

VARCHAR(50)

 

C_TABLENAME

VARCHAR(50)

 

C_COLUMNNAME

VARCHAR(50)

 

C_COLUMNDATATYPE

VARCHAR(50)

 

C_OPERATOR

VARCHAR(10)

 

C_DIMCODE

VARCHAR(700)

 

C_COMMENT

TEXT

 

C_TOOLTIP

VARCHAR(900)

 

M_APPLIED_PATH

VARCHAR(700)

If the row represents a modifier, m_applied_path is the path of
the concept associated with that modifier.
If the modifier applies to a concept and its descendents, the
path is appended with the wild
card '%'
If the row does not represent a modifier, this field is set to '@'.

UPDATE_DATE

DATETIME

 

DOWNLOAD_DATE

DATETIME

 

IMPORT_DATE

DATETIME

 

SOURCESYSTEM_CD

VARCHAR(50)

 

VALUETYPE_CD

VARCHAR(50)

 

M_EXCLUSION_CD

VARCHAR(25)

If a modifier applies to a concept and its descendents, but not to
particular descendents within the group, then this field is set to 'X'
and m_applied_path is set to the excluded path.

C_PATH

VARCHAR(700)

 

C_SYMBOL

VARCHAR(50)

 

To illustrate the way the dimension and metadata tables work for modifiers, take the example of a disease, such as asthma, that can occur with varying degrees fo severity: mild, moderate and severe. These are the modifers for the concept 'asthma'.
Before adding m_applied_path, the concept looked like this in the ontology table:

c_fullname

c_name

c_basecode

Diagnoses|Diagnoses

 

Diagnoses\Respiratory disorders|Respiratory disorders

 

Diagnoses\Respiratory disorders\asthma|Asthma

ICD9:493

Diagnoses\Respiratory disorders\COPD|Emphysema

ICD9:492





Now, to add the modifiers, mild, moderate and severe, we add the new column, m_applied_path to the ontology table, and three new rows, one for each modifier:

m_applied_path

c_fullname

c_name

c_basecode

@

Diagnoses|Diagnoses

 

@

Diagnoses\Respiratory disorders|Respiratory disorders

 

@

Diagnoses\Respiratory disorders\asthma|Asthma

ICD9:493

@

Diagnoses\Respiratory disorders\COPD|Emphysema

ICD9:492

Diagnoses\Respiratory disorders%

\severe|Severe

MOD:SEVER

Diagnoses\Respiratory disorders%

\moderate|Moderate

MOD:MODER

Diagnoses\Respiratory disorders%

\mild|Mild

MOD:MILD





The field m_applied_path indicates which paths the associated modifier refers to. The '%' wild card is used to indicate that the modifier refers to all extensions to the path 'Diagnoses\Respiratory disorders\'




© Shawn Murphy 2011

  • No labels