i2b2 Ontology and CRC Discussion: 1
Questions asked and points to ponder. This is a miscellany of points. Access to the demo database is useful here...
Note about missing values
Just a point to be aware of...
There seems to be a convention within i2b2 that missing values can be denoted by the @ sign as a value. That is, NULL is not the only way to show missing values. The @ sign can obviously thereby be used where columns are defined as NOT NULL, including primary key columns. I'm not certain whether the choice of @ sign is configurable or not, and where and when it cannot be used.
Ontology Cell tables compared to CRC concept_dimension table
There are three ontology tables within the demo Ontology cell: birn, i2b2 and custom_meta.
When I do a select count(*) from <table_name> where c_visualattributes like '%LA%' I get the following figures:
- birn: 37
- custom_meta: 0
- i2b2: 93807
When I do a "select count(*) from concept_dimension" within the CRC cell I get a count of 73590.
The totals are 93844 as opposed to 73590. Why is there a difference and what does it mean?
In an Ontology table, what does c_columndatatype really mean?
The docs state: "either ‘T’ for text or ‘N’ for numeric and describes the datatype of the concept". Yet the length of the column is varchar 50.
Within the demo project:
- select count(*) from i2b2 where c_columndatatype like '%N%' ===> returns 0
- select count(*) from i2b2 where c_columndatatype like '%T%' ===> returns 134762
- select count(*) from i2b2 ===> returns 134762
Yet within the query tool (from the Workbench) I can select terms from a Labtest and constrain by value. This is undoubtedly possible because of the optional metadataxml column. But even so, what does c_columndatatype really mean?
By comparison with the observation_fact table in the CRC cell:
- select count(*) from observation_fact where valtype_cd like '%T%' ===> returns 5055
- select count(*) from observation_fact where valtype_cd like '%N%' ===> returns 23731
Experiment conducted (Jeff):
Went into the database and chose to update a column in the Onyx metadata table for the Briccs project...
update onyx set c_COLUMNDATATYPE = 'N' where c_name = 'history_AF_onset' ;
Went into the workbench and refreshed the ontology tree. Dragged the history_AF_onset leaf into a group.
It would not allow me to set a value ('Set value...' was greyed out / disabled).
Being neurotic, I then tried:
update onyx set c_operator = '=' where c_name = 'history_AF_onset'
Restarted the workbench just to be sure. No change!
If you want within the Query Tool to be able set a value for a term in a query, you obviously need to do more than this in the ontology tree.
Facts constrained by enumerated concepts
Facts can be constrained by simply pointing at a concept row within the concept_dimension table with very little else alongside it. For example, a numeric value like AGE can be divided into an enumeration of concepts, say one for each age from 0 to 114, as is the demo system. Here is a selection based upon this:
select * from observation_fact where concept_cd like 'DEM|AGE:50' ;
This retrieved 3 rows of patients aged 50 from the fact table:
| ENCOUNTER_NUM | PATIENT_NUM | CONCEPT_CD | PROVIDER_ID | START_DATE | MODIFIER_CD | VALTYPE_CD | TVAL_CHAR | NVAL_NUM | VALUEFLAG_CD | QUANTITY_NUM | INSTANCE_NUM | UNITS_CD | END_DATE | LOCATION_CD | CONFIDENCE_NUM | OBSERVATION_BLOB | UPDATE_DATE | DOWNLOAD_DATE | IMPORT_DATE | SOURCESYSTEM_CD | UPLOAD_ID |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2005000014 | 1000000014 | DEM|AGE:50 | @ | 22-MAY-06 | @ | @ | - | - | - | - | - | @ | 25-APR-07 | @ | - | - | 25-APR-07 | 25-APR-07 | 25-APR-07 | DEMOGRAPH|DEMO | - |
| 2005000082 | 1000000082 | DEM|AGE:50 | @ | 25-MAY-06 | @ | @ | - | - | - | - | - | @ | 25-APR-07 | @ | - | - | 25-APR-07 | 25-APR-07 | 25-APR-07 | DEMOGRAPH|DEMO | - |
| 2005000095 | 1000000095 | DEM|AGE:50 | @ | 05-MAY-07 | @ | @ | - | - | - | - | - | @ | 25-APR-07 | @ | - | - | 25-APR-07 | 25-APR-07 | 25-APR-07 | DEMOGRAPH|DEMO | - |
I'm in two minds about this. It is a neat way of arranging data as long as values (whether text or numeric) have a reasonably discrete range. It seems this is the reason why lab test results etc do not follow this paradigm for their numeric values.
Constructing an Ontology Tree within the Workbench
Provided you have a root of an ontology tree that is not locked, it is possible to use the edit panel to construct a whole ontology tree within the Workbench! This is a good way to explore the possible settings within an ontology table, especially the c_metadataxml column. Unfortunately, the latter looks definitely aimed at lab results. I'll create some obviously named metadata roots so you can experiment in the demo project.
Family History and Personal History
Just in case you haven't drilled down the demo project's ontology tree very far, here are two interesting branches...
- Ontology/Diagnoses/V-codes/Family History
- Ontology/Diagnoses/V-codes/Personal History
A Principle Worth Stating
This is as much for my benefit as for anyone else...
The only metadata available to a user for forming a query is solely contained within the ontology tables, ie: that available by browsing an ontology tree.
Although there is metadata stored within the observation_fact table itself (and we have to get this correct when loading the data), this particular metadata is only used when executing a query. It is not available at query forming time.
The C_METADATAXML Column
I cannot see any other way of providing metadata to allow values to be entered by a user at query forming time than by providing this optional metadata.
It bears re-reading the above a couple of times and taking in its implications.
For every concept with dynamic values (In the questionnaire think of dates or quantities as possible candidates):
- Each concept (and maybe its ancestors in the tree) will need providing with an XML file, albeit held within a database column.
- That file could be used on data loading to help format the relevant observation_fact. (I think this may be standard practice)
- The presence of this metadata triggers a dialogue when query building which is clearly aimed at lab test values. This is hard coded within the workbench.
On the whole however, I believe we would be wise to limit dynamic valued concepts to labtest results or similar...
Is there a method to this madness?
It looks as if the i2b2 approach is a series of design compromises made along the way. The following looks as if this is the way it has been argued...
First decision:
- Any fact that can present an infinite range of values can only be supplied by a lab test (or something similar).
- Any non-labtest fact should be presented as an enumeration of values within the ontology tree.
Second decision:
Labtests are very varied, some providing a reading from an infinite range (say 123.567), some providing a discrete result from amongst an enumerated list. There are problems regarding units and standards. So we better hive this off completely within a dialogue separate from the ontology tree, and provide another way of customizing the choice (c_metadataxml).
Dates in the observation_fact Table
From the design doc: "Each row describes one observation about a patient made during a visit."
There is a START_DATE and an END_DATE for each observation. The start date is mandatory. The end date is not mandatory. The end date can be the same as the start date.
It is possible to constrain queries within the query tool using a date or date range for a group: there is a data button at the top left hand side of each group panel.
When you encounter a date in the questionnaire, I have in mind that this is where the date should go....
Interventions during this clinical episode. Enter the date at which each intervention was performed for the first time during the current episode of care. CABG Coronary Artery Bypass Graft Valve Surgery TAVI - Transcatheter Aortic Valve Implantation PPCI - Primary Percutaneous Coronary Intervention Other PCI Pacemaker insertion ICD - Implantable Cardioverter Defibrillator LVAD - Left Ventricular Assist Device Thrombolysis Electrophysiology (EP) / Radiofrequency (RF) Ablation Coronary Angiography
MA and LH settings for c_visualattributes in an ontology table
I was having difficulty making sense of some entries in an ontology table within the Ontology cell and their relationship with the patient_dimension table within the CRC cell. And discovered this.
Take the religion "Jewish". In the ontology display this looks like a bottom leaf. It isn't. The following three rows from the ontology table i2b2 (some columns excluded) are relevant:
| C_HLEVEL | C_FULLNAME | C_NAME | C_SYNONYM_CD | C_VISUALATTRIBUTES | C_TOTALNUM | C_BASECODE | C_METADATAXML | C_FACTTABLECOLUMN | C_TABLENAME | C_COLUMNNAME |
|---|---|---|---|---|---|---|---|---|---|---|
| 4 | \i2b2\Demographics\Religion\Jewish\Jewish-JEWISH\ | Jewish | N | LH | - | DEM|RELIGION:jewish | - | concept_cd | concept_dimension | concept_path |
| 4 | \i2b2\Demographics\Religion\Jewish\Jewish-JH\ | Jewish | N | LH | - | DEM|RELIGION:jh | - | concept_cd | concept_dimension | concept_path |
| 3 | \i2b2\Demographics\Religion\Jewish\ | Jewish | N | MA | - | - | - | concept_cd | concept_dimension | concept_path |
The 'LH' setting flags that there are hidden leaves. The 'MA' setting flags that this is not a bottom leaf and has hidden leaves below it. The upshot is that there are two codes relevant to being "Jewish": "DEM|RELIGION:jewish" and "DEM|RELIGION:jh". What you see in the Ontology tree is the 'MA' entry.
The code_lookup, patient_dimension and observation_fact tables within the CRC cell
This is partly related to the previous point, so also concerns ontology entries! And I'm finding this quite confusing.
There are 16 people in the demo system who are Jewish. I won't display all rows...
observation_fact table:
The query "select * from observation_fact where concept_cd like 'DEM|RELIGION:j%'" returns 16 people (some columns and rows excluded):
| ENCOUNTER_NUM | PATIENT_NUM | CONCEPT_CD |
|---|---|---|
| 2005000062 | 1000000062 | DEM|RELIGION:jewish |
| 2005000073 | 1000000073 | DEM|RELIGION:jewish |
| 2005000077 | 1000000077 | DEM|RELIGION:jewish |
patient_dimension table:
The query "select * from patient_dimension where religion_cd like '%j%'" returns 16 people (some columns and rows excluded):
| PATIENT_NUM | VITAL_STATUS_CD | BIRTH_DATE | DEATH_DATE | SEX_CD | AGE_IN_YEARS_NUM | LANGUAGE_CD | RACE_CD | MARITAL_STATUS_CD | RELIGION_CD |
|---|---|---|---|---|---|---|---|---|---|
| 1000000062 | N | 10-SEP-73 | - | M | 33 | english | white | widow | jewish |
| 1000000073 | N | 25-AUG-68 | - | M | 38 | german | hispanic | married | jewish |
| 1000000077 | N | 20-DEC-96 | - | M | 10 | spanish | black | single | jewish |
code_lookup table:
The query "select * from code_lookup where code_cd like 'DEM|RELIGION:j%'" returns 6 rows, only two of which apply to being Jewish. The other 4 concern Jehova's witnesses (some columns and rows excluded):
| TABLE_CD | COLUMN_CD | CODE_CD | NAME_CHAR |
|---|---|---|---|
| PATIENT_DIMENSION | RELIGION_CD | DEM|RELIGION:jewish | Jewish |
| PATIENT_DIMENSION | RELIGION_CD | DEM|RELIGION:jh | Jewish |
Explanation:
I think this makes sense, but please differ if you find a better explanation.
First of all it's interesting to note that patient information can/is duplicated across patient_dimension and observation_fact tables. My reading of the situation is that the entry 'DEM|RELIGION:jewish' in the fact table is identical with the 'jewish' entry in the patient_dimension table. The patient_dimension table has the value in column named RELIGION_CD which in effect replaces the name-space 'DEM|RELIGION:' that prefixes the value in the observation_fact table. So I would argue these are identical. The code_lookup table in column CODE_CD is using the full name-space qualified code. For human readability (for reports etc) the value to use is in the NAME_CHAR column.
It appears the code_lookup table is just what it says. It allows data to be stored in coded form but displayed (when required) in human readable form.
The columns within the patient_dimension table are optional columns. Beware of this omission of a name space within optional columns. I'm not completely sure of all its ramifications.
