Kind thanks to KUMC and the sites that volunteered to test. With help from Wisconsin and MCW so far, I have a lot of revisions to add to the original script that make the output smaller, the syntax less Oracle-specific, and eliminate or scrub certain fields. If you're not one of the test sites, you might want to hold out for the revised script that I'll send out after having had time to digest the initial reports. In response to some very valid concerns raised, the raw count tables will not be released without explicit consent from the originating sites. Instead, I propose to append to the enclosed table (currently representing UTHSCSA data) and send that out, along with other summary metrics that people might suggest as useful to data standardization and quality control. Is everyone okay with that?
I'll have analogous summaries of the other three tables, but those are
still in preparation.
Here is the information I was able to obtain from running the scripts I
sent earlier on our own data in San Antonio. In 33 lines we get a wealth
of hints about how to make queries work as intended at our site, and
things we need to improve. Part of this was done by aggregation of
OUTPUT_CON_MOD within SQL, but part of it was just manual tinkering with
autofilters on a spreadsheet and describing what I saw. Here is what
each column means and why one should care:
* *site*: Site for which this row of data was generated
* *domain*: A consistent substring shared by a group of related
CONCEPT_CDs (concept codes). These should be all listed in the
YOURMETADATA.SCHEMES table, but I2B2 has no way of enforcing this
rule, so instead I got these from the CONCEPT_CDs that are actually
there.
* *tval_char*: Usually this field to contain categoric values
(ventral/dorsal/lateral, left/right,
high/middle/low/below-threshold, etc.) but it often ends up with a
lot of free-text values dumped into it. In San Antonio data,
COMPONENT_ID facts use it as a free-text dump, FLOW_MEAS_ID facts
use it as a time-stamp (but the time portions are all 0's), and
MEDICATION_ID facts use it to indicate dose ranges (still need to
look into whether these co-occur with NVAL_NUM fields, and if so,
how often the values fail to match). _Recommendations:_ field might
be useful for chronologically ordering multi-day FLOW_MEAS_ID
observations and quality-checking MEDICATION_ID observations.
Further study of lab-value-specific usages of TVAL_CHAR may be
needed before lab-values that use this field can be relied on either
as raw data or as filtering criteria. Certain values in this field
can indicate a numeric fact, but VALTYPE_CD is a more reliable
indicator.
* *modifier_cd*: Here are listed the observed rules for what (if any)
MODIFIER_CDs can occur in observations from each domain. Currently
the only domains in San Antonio data that use modifiers are
diagnoses (ICD9 and DX_ID), allergies, medications, procedure
orders, and family history. Labs also use modifiers, but these are
rarely useful since they are hard-coded to be medians and last
observations regardless of the size, time-span, or intended analysis
of the lab value. _Recommendations_: For queries on diagnoses be
mindful of whether you want only medical history, only active
diagnoses, both, or just primary diagnoses [which in turn raises the
question of how to reliably identify those]. Either way, to avoid
duplicate counts, be sure to filter on MODIFIER_CD. For queries on a
specific allergic reaction, use MODIFIER_CD. For queries on an
allergen regardless of reaction type, leave MODIFIER_CD out, and use
the appropriate SELECT DISTINCT ... GROUP BY ... subquery. For
medications, the Inpatient/Outpatient/Historical/PRN/Other set of
modifiers can co-exist with each other and with the Dose/Dose_MAR
ones on the same drug during the same encounter. Pulling only facts
with Dose or Dose_MAR modifiers /might/ be enough to prevent
redundancies, but not yet certain. For procedure orders filter on
MODIFIER_CD = '@' if you want all procedures, or if you want just
procedures of type XX 'MODIFIER_CD = 'PROCORDERS:XX', but do not
ignore these modifiers or you will get redundant values. For family
history always use modifiers and treat the combination of modifier
and concept code as a distinct column OR if you don't care about
what type of family history, just filter on MODIFIER_CD !=
'FamRelation:18' and then have the appropriate SELECT DISTINCT ...
GROUP BY ... subquery (or for confirmed negative family histories,
MODIFIER_CD = 'FamRelation:18').
* *valtype_cd*: Only lab results (COMPONENT_ID), flowsheets
(FLOW_MEAS_ID), meds (MEDICATION_ID), vitals (PAT_ENC),
PACK_PER_DAY, and TOBACCO_USED_YEARS use this field but the latter
two always have the value set to 'N'. _Recommendations_: can be used
to streamline lengthy queries and for quality control.
o Only COMPONENT_ID facts use both 'N' and 'T' values in this
field. When it's 'N' then TVAL_CHAR is always one of E, G, GE,
L, or LE and NVAL_NUM has a numeric value. When it's 'T' then
TVAL_CHAR will have a comment-like value and NVAL_NUM will be NULL.
o For FLOW_MEAS_ID facts, if this field is 'N' then the TVAL_CHAR
field will always be 'E' and there will almost always be a value
in the NVAL_NUM field; if this field is 'D' then TVAL_CHAR will
contain a datestamp as described above and NVAL_NUM will be
blank; if this field is '@' then TVAL_CHAR will also be '@' and
NVAL_NUM will be blank.
o For MEDICATION_ID, if this field is 'N' then the MODIFIER_CD
will always be in the 'MedObs:Dose|%' class, TVAL_CHAR will be
'E', and NVAL_NUM will contain a numeric value. Otherwise this
field is blank and MODIFIER_CD will be in the
'MedObs:[Inpatient|Outpatient|Historical|PRN|Other]' class,
TVAL_CHAR will be a number, number-range, or '@', and NVAL_NUM
will be empty.
o For PAT_ENC, if this field is 'N' then TVAL_CHAR is 'E' and
NVAL_NUM has a numeric value. The other permitted value seems to
be '@' in which case TVAL_CHAR is also '@' and NVAL_NUM is blank.
o PACK_PER_DAY and TOBACCO_USED_YEARS always have this field set
to 'N'
* *valueflag_cd*: This field is not currently used at San Antonio and
all the values are either NULL, '@', or 'null'.
* *location_cd*: This field is not currently used at San Antonio and
all the values are NULL.
* *units_cd*: Many different units, used only by COMPONENT_ID,
FLO_MEAS_ID, and MEDICATION_ID. PACKS_PER_DAY and TOBACCO_USED_YEARS
also specify units, but those are always 'Packs'. _Recommendations_:
append to the name of the column of interest in the analyzable
output, unless it is found that for a particular variable the units
can vary... in that case, output as separate column. Guard against
missing or incorrect unit values by looking for outliers that
deviate by the value of the conversion factor.
o COMPONENT_ID uses units or leaves this field blank seemingly
independently of VALUETYPE_CD, TVAL_CHAR, NVAL_NUM, or MODIFIER_CD.
o FLO_MEAS_ID always uses units when VALUETYPE_CD = 'N' and
sometimes also uses them when it's '@' or 'D'. When units are
present there is almost always a numeric value in NVAL_NUM.
o MEDICATION_ID uses units or leaves this field blank seemingly
independently of VALUETYPE_CD, TVAL_CHAR, NVAL_NUM, or MODIFIER_CD.
* *nval_num*: COMPONENT_ID, FLOW_MEAS_ID, MEDICATION_ID, PAT_ENC,
PACK_PER_DAY, and TOBACCO_USED_YEARS are the domains that use this
field. When this field is used, it contains a numeric value. When
VALTYPE_CD = 'N' this field is always (COMPONENT_ID, MEDICATION_ID,
PAT_ENC) or usually (the other domains) used. _Recommendations_:
this is where to find the raw data for most properly recorded
numeric facts, and VALTYPE_CD can generally be relied on to find
these facts.
* *concept_path*: Here is only shown the beginning part of the concept
path, to give you an idea of where to look for it in the ontology.
May be additional paths that are not shown here. _Recommendations_:
for quality control check concepts that don't exist in CONCEPT_DIMENSION
* *modifier_path*: Here is only shown the beginning part of the
concept path, to give you an idea of where to look for it in the
ontology. May be additional paths that are not shown here.
_Recommendations_: for quality control check modifiers that don't
exist in MODIFIER_DIMENSION
* *modifier_name*: A modifier name, for ease of interpretation.
*_
Warning_*: The above is just for San Antonio data. I don't know what of
it generalizes to most data, and what is site specific. I2B2 design
documents only say how these fields ought to behave, not how we're
actually using them. The same is true for the intended behavior of the
ETLs of individual sites. The only way I can think of to make sure is to
do counts on actual co-occurrence of these fields in the raw
OBSERVATION_FACT entries as was done above. As more sites run this and
contribute their output, we will start to be able to draw inferences
about what a federated query should do in order to run as intended at
each site.
EDD_results.xls
Description: application/msexcel
_______________________________________________ Gpc-dev mailing list [email protected] http://listserv.kumc.edu/mailman/listinfo/gpc-dev
