Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  preeder
 Type:  problem  |  Status:  closed
 Priority:  major|   Milestone:  snow-shrine-1
Component:  data-stds|  Resolution:  wontfix
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+
Changes (by dconnolly):

 * status:  reopened => closed
 * resolution:   => wontfix


Comment:

 This isn't available in the SHRINE demographics ontology (#525), so I
 infer we don't plan to support it.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  preeder
 Type:  problem  |  Status:  reopened
 Priority:  major|   Milestone:  snow-shrine-1
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+
Changes (by dconnolly):

 * status:  closed => reopened
 * resolution:  fixed =>
 * milestone:  data-domains2 => snow-shrine-1


Comment:

 See ticket:411#comment:2

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  preeder
 Type:  problem  |  Status:  closed
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:  fixed
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+
Changes (by dconnolly):

 * status:  assigned => closed
 * resolution:   => fixed


Comment:

 On babel, we have `\\GPC_TERMS\i2b2\Demographics\Age by Value\`

 This seems different from the one site where it's actually deployed, UNMC,
 for no reason I can find. They use `\i2b2\Demographics\Age\Age Range\`.
 Hubert, if it's more than a trivial fix to migrate, let us know.

 Modulo the initial `\\GPC_TERMS\` bit, which we track separately as #201,
 it seems pretty stable. Based on recent discussion with Russ, I'm once
 again inclined to try the optimistic approach to closing tickets.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  preeder
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+
Changes (by dconnolly):

 * owner:  huhickman => preeder


Comment:

 Hubert gave the index details (comment:12); Justin reported having
 reproduced the results 2 Dec (#12). I suppose that wraps this up.

 Phillip, is this scalar age item part of your demographics proposal? #67.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+

Comment (by huhickman):

 We have the following indexes active on our `observation_fact` table:

 ||= INDEX_OWNER =||= INDEX_NAME =||= UNIQUENESS =||= STATUS =||=
 INDEX_TYPE =||= COLUMNS =||
 || BLUEHERONDATA || OBS_FACT_PAT_NUM_BI || NONUNIQUE  || VALID || BITMAP
 || PATIENT_NUM ||
 || BLUEHERONDATA || OBS_FACT_CON_CODE_B ||  NONUNIQUE ||VALID ||
 BITMAP ||   CONCEPT_CD ||
 || BLUEHERONDATA || OBS_FACT_MOD_CODE_BI || NONUNIQUE || VALID || BITMAP
 || MODIFIER_CD ||
 || BLUEHERONDATA || OBS_FACT_UPLOAD_ID_BI || NONUNIQUE || VALID || BITMAP
 ||  UPLOAD_ID ||
 || BLUEHERONDATA || FACT_CNPT_PAT_ENCT_IDX || NONUNIQUE || VALID || NORMAL
 ||  CONCEPT_CD, INSTANCE_NUM, PATIENT_NUM, ENCOUNTER_NUM ||
 || BLUEHERONDATA || OBS_FACT_VALTYPE_CD_BI || NONUNIQUE || VALID || BITMAP
 ||  VALTYPE_CD ||

 The `FACT_CNPT_PAT_ENCT_IDX` is a standard i2b2 index.  The others come
 from the heron code base - though this is not all of the ones that exist
 in the heron code base.  I've been conservative in adding in indexes as we
 go along.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+

Comment (by dconnolly):

 HH to document indexes he's using.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+

Comment (by huhickman):

 In some crude timing experiments with age (judging by the GUI counter in
 the i2b2 web client) , it is slower with the CRC_ENABLE_UNITCD_CONVERSION
 set to ON, but not substantially so.

 ||= Query ||= CRC_ENABLE_UNITCD_CONVERSION=OFF ||=
 CRC_ENABLE_UNITCD_CONVERSION=ON ||
 || < 10 years ||  3.4 seconds  ||  3.8 seconds  ||
 || < 100 years ||  28 seconds  ||  31.9 seconds  ||

 The query generated in the <100 case is as follows:

 < 100 years, CRC_ENABLE_UNITCD_CONVERSION=OFF
 {{{
 #!sql
 insert into BlueHeronData.QUERY_GLOBAL_TEMP (patient_num, panel_count)
 with t as (
 select  /*+ index(observation_fact fact_cnpt_pat_enct_idx) */
 f.patient_num
 from BlueHeronData.observation_fact f
 where
  f.concept_cd IN (select concept_cd from  BlueHeronData.concept_dimension
  where concept_path like '\i2b2\Demographics\Age\Age Range\%')
   AND  (  modifier_cd = '@'  AND
  (( valtype_cd = 'N' AND   nval_num  < 36525 AND  tval_char IN ('E','LE'))
 OR
 ( valtype_cd = 'N' AND   nval_num  <= 36525 AND  tval_char = 'L' ))  )
  group by  f.patient_num
   )
  select  t.patient_num, 0 as panel_count  from t
 }}}

 CRC_ENABLE_UNITCD_CONVERSION=ON

 {{{
 #!sql
 insert into BlueHeronData.QUERY_GLOBAL_TEMP (patient_num, panel_count)
  with t as (
   select  /*+ index(observation_fact fact_cnpt_pat_enct_idx) */
 f.patient_num
 from BlueHeronData.observation_fact f
  where
 f.concept_cd IN (select concept_cd from  BlueHeronData.concept_dimension
 where concept_path like '\i2b2\Demographics\Age\Age Range\%')
AND  (  modifier_cd = '@'  AND(( valtype_cd = 'N' AND   case
 when  units_cd = 'YEARS' then nval_num *  365.25
  when  units_cd = 'MONTHS' then nval_num *  30.4375
  when  units_cd = 'DAYS' then nval_num   end  < 36525 AND
 tval_char IN ('E','LE')) OR ( valtype_cd = 'N' AND   case
  when  units_cd = 'YEARS' then nval_num *  365.25
  when  units_cd = 'MONTHS' then nval_num *  30.4375
   when  units_cd = 'DAYS' then nval_num   end  <= 36525 AND  tval_char =
 'L' ))  )
  group by  f.patient_num
   )
 select  t.patient_num, 0 as panel_count  from t

 }}}

 Turning on the CRC_ENABLE_UNITCD_CONVERSION option allows the storing of
 values in heterogenous units - and does the conversion in SQL for the
 various possible convertible units in the metadata_xml field.

 The default setting is OFF, and i2b2 assumes that the facts are stored in
 normalized units.

 It would be interesting for another site to repeat the timing experiment
 as well.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+

Comment (by huhickman):

 The i2b2 documentation of the unit code conversion is in the CRC Design
 document, section 3.2.2.  I will attach the pages to this ticket, and
 attach some timing results as well.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+
Changes (by dconnolly):

 * owner:  jdale => huhickman


Comment:

 Justin, it helps to tell trac that you're handing the ball to Hubert.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  jdale
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+

Comment (by jdale):

 Hubert - Where you able to locate this?:

 "HH will point to documentation and will experimentally flip the server
 side conversion flag to measure any performance difference"

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  jdale
 Type:  problem  |  Status:  assigned
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+
Changes (by dconnolly):

 * owner:  huhickman => jdale
 * status:  reopened => assigned


Comment:

 Justin volunteered 11 Nov (notes: #12) to try to reproduce Hubert's work.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  reopened
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:  67   |
-+

Comment (by huhickman):

 The SQL to create the prototype fact table is:
 {{{
 #!sql
 create or replace view obs_fact_age_days as
 select  'fabricated_for_' || p.pat_id as encounter_ide,
 p.patient_ide,
 BIRTH_DATE as start_date,
'LOINC:30525-0' as concept_cd,
'@' as modifier_cd,
 case when death_date is null then cast(null as date) else
 death_date end as end_DATE,
 '@' as provider_id,
 'N' as valtype_cd,
 'E' as TVAL_CHAR,
 round(coalesce(death_date, sysdate) - birth_date + date_shift ) as
 NVAL_NUM,
 cast(NULL as varchar2(50)) as VALUEFLAG_CD,
 cast(NULL as decimal(18,5)) as  QUANTITY_NUM,
 cast(null as VARCHAR2(50)) as LOCATION_CD,
 cast(NULL as varchar2(50)) as OBSERVATION_BLOB,
 cast(NULL as decimal(18,5)) as CONFIDENCE_NUM,
 cast(NULL as date) as UPDATE_DATE,
 sysdate as DOWNLOAD_DATE,
 sysdate as IMPORT_DATE,
 'e...@nebraskmed.com' as SOURCESYSTEM_CD,
 'DAYS' as UNITS_CD,
 1 as instance_num,
 100 as upload_id
 frompatient_dimension p
 wherebirth_date is not null
 }}}

 In our case, this is called from a stored procedure.

 A second stored procedure inserts these facts into the de-identified fact
 table in our i2b2 instance (adjust dates, substitutes encounter_num and
 patient_num, etc.).

 Note that `nval_num` field is date shifted in this view.  Additionally,
 these facts should be refreshed daily to maintain accuracy.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  reopened
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:   |
-+
Changes (by dconnolly):

 * status:  closed => reopened
 * resolution:  fixed =>


Comment:

 oops... this isn't a complete proposal after all.

 It's  incomplete w.r.t. our Oct 14 discussion (ticket:12#comment:38):

  DC: Share the SQL etc. such that at least one other site can reproduce
 your results?
  HH: Yes, we’ll share it.

 So I'm re-opening this.

 Hubert, we're standing by for you to share the SQL.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  closed
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:  fixed
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:   |
-+

Comment (by dconnolly):

 Hubert, the terms you delivered has both Age and Age at Visit in the usual
 buckets-of-years style as in the i2b2 demo metadata, but it has only Age
 term with this scalar range style.

 Did you leave out scalar Age at Visit on purpose?

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev

Re: [gpc-informatics] #186: Querying age by numerical constraints

[GPC Informatics]

#186: Querying age by numerical constraints
-+
 Reporter:  huhickman|   Owner:  huhickman
 Type:  problem  |  Status:  closed
 Priority:  major|   Milestone:  data-domains2
Component:  data-stds|  Resolution:  fixed
 Keywords:  age valueset obesity-cohort  |  Blocked By:
 Blocking:   |
-+
Changes (by dconnolly):

 * cc: gpc-dev@… (added)
 * keywords:  age valueset => age valueset obesity-cohort
 * status:  new => closed
 * resolution:   => fixed


Comment:

 UNMC's recent babel update (ticket:64#comment:26) includes the relevant
 details in the "Age Range" term under "UNMC: Demographics".

 I'm closing this to put it on the group agenda.

--
Ticket URL: 

gpc-informatics 
Greater Plains Network - Informatics
___
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev