Opened 4 years ago

Closed 4 years ago

Last modified 3 years ago

#2473 closed enhancement (fixed)

Query by BMI percentile (among children)

Reported by: rwaitman Owned by: dconnolly
Priority: major Milestone: heron-marmaton-update
Component: data-repository Keywords: public-web
Cc: dconnolly, mhoag, badagarla, ngraham Blocked By:
Blocking: 2714 Sensitive: no


In pediatrics, BMI is less relevant than BMI percentile.

A couple possibly related concepts are in the flowsheet tree, but with very few facts:

  • ...10844 BMI %
  • 2428 BMI For Age %

Dan, reporting for Russ, in a meeting w/Dr. Davis

Attachments (2) (1.3 KB) - added by ngraham 4 years ago.
cdc_ex (430 bytes) - added by ngraham 4 years ago.

Download all attachments as: .zip

Change History (23)

comment:1 Changed 4 years ago by dconnolly

  • Milestone changed from heron-lovewell-update to heron-kirwan-update

comment:2 Changed 4 years ago by dconnolly

  • Milestone changed from heron-kirwan-update to heron-republican-update

comment:3 Changed 4 years ago by dconnolly

  • Cc ngraham added
  • Owner changed from dconnolly to ngraham
  • Status changed from new to assigned


A search for "percentile" under GPC LOINC on babel yields a number of results such as the one detailed below. Could you try to get this data out of KUH Epic? If not right away, please talk with Russ or with Sarah Schlachter (staff contact for the obesity group wiki:PMO) about timing.

                <name>11770-5: [percentile] body weight.10th estimated.pop birth wgt gestational age corr.ref</name>
                <visualattributes>LA </visualattributes>
                <totalnum xmlns:xsi="" xsi:nil="true" />
                <tooltip>GPC - Lab \ LOINC \ LOINC Class \ Clinical categories \ Obstetrical studies and measures \ Ob ultrasound impression and measures \ 11770-5: [percentile] body weight.10th estimated.pop birth wgt gestational age corr.ref</tooltip>

comment:4 Changed 4 years ago by dconnolly

  • Blocking set to 2677

(In #2677) I looked over the major Republican tickets and added the ones that seem like they should block ETL.

comment:5 Changed 4 years ago by ngraham

  • Blocking changed from 2677 to 2677, 2690

comment:6 Changed 4 years ago by ngraham

  • Blocking changed from 2677, 2690 to 2677

(In #2690) Oops...Dan already made an ETL ticket - see #2677.

comment:7 follow-up: Changed 4 years ago by ngraham

From searching Epic User Web it appears that we don't get the BMI percentile directly from Clarity until we go to Epic 2012. But, perhaps we can use Percentile Data Files with LMS Values from the CDC combined with height/weight information in Clarity. It seems at least one customer on Epic User Web has done this and has shared the Crystal report.

Towards the end of this comment, I experimented with using this file from the CDC to generate BMI percentile per encounter (which I would envision being the first steps towards generating observation_facts). Coverage is pretty low using the BMI in the encounter table, but I was really just trying to see how I'd get from BMI to BMI percentile using the CDC data.


I found Child BMI Percentile thread on Epic user web.

Jared Nishida - Quality Assurance & Compliance Representation - Hospital Sisters Health System:

Does anyone know if the Child BMI percentiles are stored anywhere in epic that I can report off of? If not, is there a formula for calculating. Google has failed me in my search.

Brent Nelsen - EpicCare-Ambulatory Reporting Coordinator - Epic:

We are currently working on a pediatrics dashboard and as part of that project, we plan to add a Last BMI Percentile metric to our pediatric Wellness Registries. The data will be extracted to Clarity from there.

Could we consider using Percentile Data Files with LMS Values from the CDC? They have BMI-for-age charts, 2 to 20 years. I wonder if there are licensing restrictions?

It appears that at least one person from Child BMI Percentile the Epic thread noted above uses the CDC file:
Joshua Woolen - Report Designer/Analyst - Deaconess Health System:

...I found a table on the CDC's website that I have been using thus far.

There also appears to be a Pediatric Obesity for CDC BMI Calculator Crystal report in the report repository on Epic User Web.

The summary has some hints as to where to look for the needed information:

Shows necessary information to calculate the BMI Percent for children based on height and weight. The tables needed to run this report are PATIENT, PAT_ENC, CLARITY_DEP,CLARITY_LOC,ZC_SEX,CLARITY_SER

Downloaded the free SAP Crystal Reports Viewer to check it out. Hmm, apparently I can't view the SQL (or however the report was built) with the free viewer - it won't open the .rpt saying it has to be "saved with data".

Coverage for height/weight seems rather small - around 10% of encounters (in the pat_enc table).

/* in pat_enc only about 10% have BMI*/
select count(*) from clarity.pat_enc; --17598849
select count(*) from clarity.pat_enc where bmi is not null; --1777961
select 1777961/17598849 from dual; --0.1010

select count(*)from clarity.pat_enc where height is not null and weight is not null; --1683815
select 1683815/17598849 from dual; --0.0957...

Patient-wise, 50% or so:

with pats_with_bmi as(
  select count(*) cnt from (
    select count(*) cnt, pat_id from clarity.pat_enc 
    where bmi is not null --and contact_date > add_months(sysdate, -36)
    group by pat_id
    having count(*) >= 1
total_pats as (
  select count(distinct pat_id) cnt from clarity.pat_enc --where contact_date > add_months(sysdate, -36)
select b.cnt/t.cnt from pats_with_bmi b, total_pats t
; --0.67 in the past 3 years, 0.50 overall

Experiment: getting from BMI to BMI Percentile using the CDC data

As an experiment, I loaded the CDC data (after pivoting with and using the following query I was able to get BMI percentile for 72,034 encounters. Note that the CDC file only contains percentiles for those ages 2 to 20 years.

/* BMI Percentile by encounter.
pat_bmi as (
    pat.pat_id, pe.pat_enc_csn_id, pat.sex_c, pe.bmi, 
    months_between(coalesce(pat.death_date, pe.contact_date), pat.birth_date) agemo
  from clarity.pat_enc pe
  join clarity.patient pat on pe.pat_id = pat.pat_id 
  where pe.bmi is not null
deltas as (
  select p.pat_enc_csn_id, cdc.*, abs(p.bmi - cdc.bmi) bmi_delta
  from pat_bmi p
  /* Sex codes between CDC and Clarity are opposite (1,2). Age in months is 
  rounded to the nearest .5 in the CDC data.
  join cdc_bmi_percentiles cdc on cdc.agemos = round(p.agemo * 2)/2 and p.sex_c = mod(, 2)+1
  where p.sex_c in (1,2)
select d2.*/*d2.pat_enc_csn_id, percentile bmi_percentile*/ from (
  select min(d.bmi_delta) min_bmi_delta, d.pat_enc_csn_id
  from deltas d
  group by d.pat_enc_csn_id
  ) md
join deltas d2 on d2.pat_enc_csn_id = md.pat_enc_csn_id 
  and d2.bmi_delta = md.min_bmi_delta
; --72,034 encounters

Changed 4 years ago by ngraham

Changed 4 years ago by ngraham

comment:8 Changed 4 years ago by ngraham

  • Owner changed from ngraham to tmcmahon


As per our conversation earlier, I e-mailed Sarah this morning (Fri 5/9/2014 9:17 AM) and copied you asking if she's waiting on this BMI percentile functionality and what the implications to her are if we push it to the next release. Could you contact her with the following additional questions?

  • Clarity doesn't seem to have BMI percentile data directly, but I think we can use data from the CDC to look it up (for patients aged 2 to 20 - see comment:7 for details). Are these data from the CDC what she wants?
  • Does she expect the BMI data to come from any particular workflow? I'm pulling BMI from the patient encounter table in Clarity.
  • I plan to push this to the next release, but if she really wants this functionality I think we can get it in for 72,034 encounters at least (see comment:3).

comment:9 Changed 4 years ago by tmcmahon

  • Milestone changed from heron-republican-update to heron-marmaton-update
  • Owner changed from tmcmahon to ngraham

Spoke with Sarah.

  • Yes, CDC is what the group wants.
  • No particular worflow.  The PCORI obesity group prefers any/all bmi data instead of data limited to 1 workflow
  • She is okay with pushing it until the next release.  The group is  in a holding patter right now and does not have an immediate need for the data.  It can wait until the June release.

The PCORI study may change to include all ages, not just pediatrics.  Can this ticket be changed to include all BMI, not just pediatric BMI?

comment:10 Changed 4 years ago by tmcmahon

  • Blocking 2677 deleted

comment:11 Changed 4 years ago by ngraham

Possibly relevant for finding BMI in flowsheets: Flowsheet mapping for LOINC from James R campbell at (Fri May 9 08:17:22 CDT 2014).

comment:12 Changed 4 years ago by dconnolly

  • Blocking set to 2714

comment:13 Changed 4 years ago by ssuman

Created a user called CDC (centers for disease control and prevention) in <production ID databases - server names removed>

comment:14 Changed 4 years ago by ngraham

I created a Jenkins job (CDC_bmi_percentiles) to stage the CDC BMI percentile data.

CDC Staging scripts added in [c4967c1ab821] - facts and concepts code added in [7fb1a0d62a24].

Ran a test ETL in heron_etl_tiny_no_DBA (Jenkins build 30).

Renamed concept to "Body Mass Index Percentile (age 2 to 20 years)" as per suggestion from Tamara [65d9d779e4d8].

Added automated test for at least one BMI percentile fact < 30 in [eff8b2502e8a].

comment:15 Changed 4 years ago by ngraham

  • Owner changed from ngraham to schandaka


Thanks for agreeing to review - please review with wiki:CodeReviewNotes in mind. It's the bmi_percentile_2473 branch.


comment:16 Changed 4 years ago by dconnolly

  • Owner changed from schandaka to dconnolly
  • Status changed from assigned to accepted

Sravani, I'll leave you to focus on cardio #2713.

comment:17 in reply to: ↑ 7 Changed 4 years ago by dconnolly

Replying to ngraham:

Could we consider using Percentile Data Files with LMS Values from the CDC? They have BMI-for-age charts, 2 to 20 years. I wonder if there are licensing restrictions?

Our approach doesn't invole re-publishing their work (nor derivatives of it), so we only need license to use it, and only for research purposes at that. That's at least implied by the fact that they publish it in machine-readable form.

Meanwhile, I acknowledged it in source:heron_load/README.rst [b2f82ca40863]. Let's be sure we acknowledge it in the release blog item, too.

comment:18 Changed 4 years ago by dconnolly

made some tweaks... now to test them...

  1. [053a11dab2f9] explicit authority
  2. [be51cec2e555] tweak pivot doctest for readability: factor out _mock_io(); add blank lines
  3. [952010258507] use union all rather than union to avoid implicit distinct in encounter_vitals_pivot
  4. [af05410ea06e] modular arithmetic to swap sex codes is too clever; these are codes, not scalars
  5. [eba29e74d833] avoid clarity.pat_enc self-join by grabbing contact_date earlier

did not fix:

  • ugh... more scope-creep in epic_concepts_load.sql (7fb1a0d62a24)

comment:19 Changed 4 years ago by dconnolly

  • Resolution set to fixed
  • Status changed from accepted to closed

ETL tweaks tested in heron_etl_tiny_no_DBA (build 42); staging tested in CDC_bmi_percentiles (build 12).

comment:20 Changed 4 years ago by ngraham

  • Keywords public-web added

comment:21 Changed 3 years ago by lhuang2

Reviewed BMI percentile for age group 2 to 20. There are 10904 out of total 40141 patients or near 25% kids are considered as obesity(or >=95%)in Heron. It is higher than national average. Reviewed data pipeline such as CDC data, CDC data transformation in our DB for look up purpose, ETL calculations using key fields (bmi, sex, agemos) to map CDC percentile to heron, the mapping is before de-id, heron search, heron analysis tool, oracle sql queries to compare data before and after mapping... The BMI percentile looks correct.

Note: See TracTickets for help on using tickets.