First of all, I feel like I'm treading into sensitive territory here, in
that where I point out on problems it can be misinterpreted as
criticizing other people's work. So let me just re-affirm:
We are all geeks here. It's us against the bugs. When we engage in
debate, both sides win by finding the optimal answer and it doesn't
matter whose answer it turns out to be. Both sides lose by talking past
each other and taking things personally. I admire your work Dan, and the
team you put together, and y'all are a role model to us and hopefully to
other sites. I am grateful to you guys for sharing the Heron ETL code.
Adapting it has saved us YEARS of effort, and it was and is far better
than anything I can even imagine writing from scratch (and I tried,
before I came to understand the Zen of Heron).
This is me trying, in a small way, to give something back. Unfortunately
that something is verbose and probably annoying to read. Sorry. I spent
most of the day trying to file the rough edges off this and probably
didn't get most of them. Anyway, the point is, I mean it constructively.
On 10/06/2014 01:20 PM, Dan Connolly wrote:
I started trying out the script last week.
1st bit of feedback: It was taking a lot longer than the estimated 20
minutes. Just building the 1st intermediate table took longer than that.
Sorry. Revised script in progress. I'm guessing the big resource hog was
OUTPUT_CON_MOD? In the revised script, I filter the result-set on `WHERE
N_VISITS > 10` and that cuts down its size by about 50%.
I guess my high order feedback is: which end is up? What's the goal
here? I was expecting something related to cohort characterization,
but if it is related, I don't understand how. Perhaps it's
clear/obvious to others.
We can't do cohort characterization or any useful queries at all if we
don't understand which modifiers matter and which don't, which values
can occur and which cannot, which type of data are always used and which
aren't, which of our assumptions about each field in OBSERVATION_FACT
are valid and which aren't. And, how much of this differs from site to site.
The parent post to this one was my attempt to clarify what I was trying
to accomplish. If I can get OUTPUT_CON_MOD from each site (abridged, to
drop the <10 occurence lines) I can munge it all into this one sheet,
and report back to the list these answers for all the sites obtained by
inspection of this sheet.
I'm interested to know more about the trial-and-error steps.
Something like this: this seems like a query will get the data we're
looking for. Wait, how come we have duplicate entries? Oh, I see, you
can have multiple encounters per calendar day. Work around it. Wait, how
come we have duplicate entries? Oh, I see, a procedure order with '@' in
its MODIFIER_CD field will always be accompanied by a second identical
procedure order with a MODIFIER_CD that specifies whether it is
Inpatient or Outpatient. Maybe a bug, maybe a feature, but I guess I
better work around it in my queries. Wait, how come we have duplicate
entries? Oh, I see diagnoses have a similar rule governing MODIFIER_CD
usage but more complicated. Wait, why do some diagnoses codes look
different from others? Oh, I see, the DX_ID ones are more specific than
the ICD9 ones, and you have to do some text parsing to retrieve ICD9
codes from them. Wait, how come we STILL have duplicate entries...
...screw this, we'll be at this forever and still not notice the more
subtle/obscure stuff. Let's just count every combination of non-numeric,
non-free-text values (this was before realizing just how variable
TVAL_CHAR and UNITS_CD are) and in one shot see what is a common case,
what is a rare case, and what is a nearly impossible case. And now,
let's see if it's even remotely similar to what's going on at other sites.
I've seen pretty good cohort characterization progress w.r.t. breast
cancer and ALS based on HERON data builder results (which are
analagous to i2b2 PDO results as seen in the timeline plug-in). I
don't understand why that approach isn't suitable.
I haven't had a chance to look at the ALS script they posted yet, I need
to do that, I may learn some useful stuff. Are you saying there is now a
way to get PDOs or data builder results into a denormalized tabular
format that can be analyzed in SAS or R? Because that was the main
problem I encountered before meeting with success just using SQL. I
decided that I'd fall back on R only after I hit a problem SQL couldn't
handle, and so far this hasn't happened.
Also, much as I like R, there is a lot more SQL expertise in the GPC
than R expertise, and everyone already has some type of SQL database
because I2B2 requires it. So a suitably dialect-independent SQL script
is easier to deploy than convincing everyone to add R to their toolchain.
The script comment continues:
* To create a sort of empirical data dictionary we can all refer
to in
constructing federated queries that will run correctly at GPC
sites as they
*currently* are, so that cohort characterization can proceed in
parallel with
data standardization.
How is this "emperical data dictionary" different from babel? We've
been proceeding in parallel, no?
I use Babel to get an initial big-picture idea of what data exists, how
it is organized, and to prototype queries. But...
1. Babel is based on metadata. That our metadata is in sync with our
data is an assumption, not a fact. In order to validate this assumption,
some systematic survey of the actual data needs to be done. This is why
I wrote this script.
2. I cannot collect a list of concept codes of a given type for each
site on Babel without carpal tunnel syndrome
3. I cannot automate Babel queries because paths are just different
enough from site to site that they'll confuse scripts. Don't know, but
maybe concept code prefixes are less variable-- if we all run the
Empirical Data Dictionary script, we'll find out. And even if they are
equally variable, at least we'll have a tabular list covering all sites.
4. I cannot get frequencies of /combinations/ of terms, only of
everything that has a given term in isolation.
5. In the post you're responding to, I have a long list of things I
learned by examining this output. If I knew how to do /any/ of that in
Babel, I would not written all this. I'd still rather use Babel for the
stuff that it can do that I don't know about, so there's less for the
scripts to do and they can run faster.
We do manual terminology alignment in some cases (e.g. breast cancer
procedures for #119) while we work out the details of automated
terminology alignment (#160).
In the meantime, empirical counts of the concepts and their co-occurence
with values in other fields of OBSERVATION_FACT allow us to write cohort
characterization queries we can trust without waiting until the
terminology alignment efforts are complete.
* To help those working on data standardization see what else
needs to be done
and where it needs to be done the most.
* To make certain data quality issues easier to spot.
Data quality issues such as... what?
Concepts missing from CONCEPT_DIMENSION. Multiple capitalization
variants of the same units and TVAL_CHARs. Freeform text showing up in
TVAL_CHAR. Medicine dosage mismatches between the MODIFIER_CD and the
UNITS_CD. I would also say that intended but counterintuitive behavior
is a quality issue if it isn't documented-- for example, the fact that
if you search for a medication and don't exclude
MODIFIER_CD='MedObs:Historical' you are pulling in all visits by
patients who have /ever /taken it, not just the ones who are prescribed
it during that visit. I'm still not sure what exactly 'MedObs:Other' and
'MedObs:Outpatient' mean, but more than one of them can be associated
with the same drug during the same encounter.
We certainly have a long list of things to be done for data
standardization. Can you give an example of how this script helps with
determining where it needs to be done most?
Here is how:
1. We try to derive the same statements for each site after it runs the
script as I made for San Antonio in the list below
2. We find out which of these vary the most across GPC sites, weighted
by the number of visits they affect.
3. Of these we find the ones that have the greatest potential to force
queries to vary from one site to the next independently of problem
domain. Those may be candidates for being a data standardization priority.
4. I haven't yet gotten to the patient, provider, and visit tables.
Those will probably be easier to understand and act on, though-- for the
categoric (multiple choice) variables, we see what the
majority/plurality usages are, and (unless there are technical or
scientific reasons not to) standardize on the most commonly used synonyms.
Is there an objective procedure already in place for prioritizing data
standardization issues?
About the items below...
For domain, why not the first part of the concept path?
1. CONCEPT_PATH is not in the same table as CONCEPT_CD
2. A one-to-one relationship is not enforced between CONCEPT_PATH and
CONCEPT_CD.
3. Concept paths are verbose, and how does one decide what part of the
path should be considered the first part? On the other hand, the
prefixes of almost all CONCEPT_CDs sharing the same domain are invariant
left of the first colon, and this invariant part is short and easy to
remember.
5. Domains are a real I2B2 thing, not something I made up-- that's what
I2B2METADATA.SCHEMES is for, except it isn't necessarily in sync with
the actual data because nothing enforces that relationship
6. One of the things we learn from the empirical data dictionary is that
entries in the same domain do behave similarly to each other in how they
use the other fields in OBSERVATION_FACT
I wonder how free text got into tval_char; the HERON ETL scripts are
fairly conservative about avoiding free text.
We don't have any paragraphs or anything in there, but lots of synonyms
and alternative capitalizations and things that were clearly intended to
be numeric values and will never be visible as such. I2B2 has no way of
enforcing the data type constraints implied by VALUETYPE_CD and when
constraints don't get enforced, data can and will eventually drift. No
matter how conservative, and ETL process is not a constraint.
Structuring a list of recommendations around modifier_cd and such
seems odd to me.
Why does that seem odd? How should we, for example, handle the fact that
almost half the diagnoses have a modifier of 'DiagObs:MEDICAL_HX'?
That's the point of an empirical approach: I'm reporting what I see, and
it has led me to the conclusion that MODIFIER_CD is one of the most
important fields for correctly interpreting an observation. The rest are
less important, though TVAL_CHAR may start to matter as we delve deeper
into lab values and flowsheets.
--
Dan
------------------------------------------------------------------------
*From:* [email protected]
[[email protected]] on behalf of Alex Bokov
[[email protected]]
*Sent:* Monday, October 06, 2014 12:57 PM
*To:* [email protected]
*Subject:* Example (Re: Empirical Data Dictionary)
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
* 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.
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
