i2b2 Academic Users Group
Space shortcuts
Space Tools
Skip to end of metadata
Go to start of metadata

Last Updated: 03/18/2013



Configuring Ontology cell for a different user [08-28-2012]
From: Niveditha Thota [thotan.uab] Sent: Tuesday, August 28, 2012 4:12 PM To: Phillips, Lori C.; i2b2 AUG Members Subject: RE: Configuring Ontology cell for a different user
Thanks for pointing that out. The table had i2b2metadata as the c_db_fullschema.
The error during startup was mostly indicating that the sql string in the log files is trying to connect using i2b2metadata.
Nivi

From: Phillips, Lori C. [LCPHILLIPS.PARTNERS] Sent: Tuesday, August 28, 2012 2:49 PM To: Niveditha Thota; i2b2 AUG Members Subject: RE: Configuring Ontology cell for a different user
When you say user, do you mean schema as well?
If so, does ONT_DB_LOOKUP in schema xxx_user have the correct entries that point to schema xxx_metadata_user?
What kind of errors are you getting on startup?


From: Niveditha Thota [thotan.uab] Sent: Tuesday, August 28, 2012 3:36 PM To: i2b2 AUG Members Subject: Configuring Ontology cell for a different user
Hi,
 
I am configuring the i2b2 1.6 system to use Oracle database which is on a different machine than the default database that comes with the VM. Facing some trouble configuring the Ontology cell to point to the new database with modified user names.
 
I have modified the database users info for i2b2hive and i2b2metadata in the following files:
/etc/spring/ontology.properties
ontology.bootstrapdb.metadataschema=xxx_user
etc/jboss/ont-ds.xml and configure your data sources. <datasources> <local-tx-datasource> <jndi-name>OntologyBootStrapDS</jndi-name> <driver-class>oracle.jdbc.driver.OracleDriver</driver-class> <connection-url>jdbc:oracle:thin:.newhostname:1521:xe </connection-url> <user-name>xxx_user</user-name> <password>xxx_password</password> </local-tx-datasource> <local-tx-datasource> <jndi-name>OntologyDemoDS</jndi-name> <driver-class>oracle.jdbc.driver.OracleDriver</driver-class> <connection-url>jdbc:oracle:thin:.newhostname:1521:xe</connection-url> <user-name>xxx_metadata_user</user-name> <password>xxx_metadata_password</password> </local-tx-datasource> </datasources>
 
 
Is there any other place where the database info needs to be changed?
 
Thanks,
Nivi




What is the metadata table column C_COLUMNDATATYPE [08-17-2012]
From: Jack London [Jack.London.jefferson] Sent: Friday, August 17, 2012 10:48 AM To: Schulte, Gregory; i2b2 AUG Members Cc: Mendis, Michael E. Subject: RE: just what is the metadata table column C_COLUMNDATATYPE?
Greg,
You are correct.  "C_COLUMNDATATYPE" refers to the concept path data type, while "VALTYPE_CD" describes the concept value data type.  (Hence the query SQL construcrion I observed makes perfect sense.)
Thanks for your explanation.
Jack

From: Schulte, Gregory [Gregory.Schulte.childrenscolorado] Sent: Friday, August 17, 2012 9:34 AM To: Jack London; i2b2 AUG Members Cc: mmendis.partners Subject: RE: just what is the metadata table column C_COLUMNDATATYPE?
Jack,
 
I believe the "c_columndatatype", in the I2B2 table,  defines the data type for the "c_dimcode" value  (which is probably your concept_path ) and not what the actual data type is for the lab result.
 
The data type for the lab result will be stored in the observation_fact table under the "VALTYPE_CD" column.
 
Hope that helps,
Greg
 
 
From: Jack London [Jack.London.jefferson] Sent: Thursday, August 16, 2012 4:13 PM To: i2b2 AUG Members Cc: mmendis.partners Subject: just what is the metadata table column C_COLUMNDATATYPE?
 
I had created a metadata term today for a numeric lab value.  I set the C_COLUMNDATATYPE to "N" 
 
The web client would return "ERROR" for this concept.  The cause of the error was a query SQL statement that lacked single quotes around the concept path.
 
I first suspected a bug in the web client code I was using, but eventually realized that this error only occurred with my new added concept.  Other numeric valued concepts (such as the many existing lab test concepts) worked just fine.  I then compared metadata numeric entries for existing working concepts with my new one. Voila!  All my other numeric valued concepts (such as serum Iron test result) had C_COLUMNDATATYPE  set to "T"
 
I changed my new concept's C_COLUMNDATATYPE to "T" and all worked fine.
 
So what is C_COLUMNDATATYPE ?  The documentation states that it describes the datatype of the concept or term.  Would not a numeric lab value be "N" ?
 
Can someone clear up my confusion???
 
Jack




Web Client v1.6.04 bug fixed in 1.6.05 [08-16-2012]
From: Jack London [Jack.London.jefferson] Sent: Thursday, August 16, 2012 3:04 PM To: Peter Beninato; i2b2 AUG Members Cc: Mendis, Michael E. Subject: RE: web client v1.6.04 bug fixed in 1.6.05 ?
I checked the version on the development server I am using – it is still at 1.6.01, which is perhaps the explanation of why I am having the problem.  I will defer further consideration of this as a problem until I have upgraded the development server to 1.6.05.
jack

From: Peter Beninato [beninato.ohsu] Sent: Thursday, August 16, 2012 1:16 PM To: Jack London; i2b2 AUG Members Cc: mmendis.partners Subject: RE: web client v1.6.04 bug fixed in 1.6.05 ?
Hi Jack,
 
I just tested Hemoglobin A1C, through all the operator permutations (>, >=, between, <, <=, =).
 
In all cases, the concept path was quoted. There was no "ERROR".
 
And a cursory examination of the sql in qt_query_master, seems to confirm that the criteria and operator are applied correctly.
 
Fyi.
Peter
 
From: Jack London [Jack.London.jefferson] Sent: Thursday, August 16, 2012 9:33 AM To: i2b2 AUG Members Cc: mmendis.partners Subject: web client v1.6.04 bug fixed in 1.6.05 ?
 
When a numeric value is constrained (such as , PSA > 4) the generated sql sometimes lacks quotes around the concept path, and an "ERROR" message is displayed.  (This does not seem to occur when a modifier is involved – different code involved?)
 
I looked at the release notes for 1.6.05 and did not see this bug mention.
 
Jack




Request for comments on i2b2 customization [08-15-2012]
From: Peter Beninato [beninato.ohsu] Sent: Wednesday, August 15, 2012 7:48 PM To: Key, Dustin; i2b2 AUG Members Cc: Frawley, Peter; O'Meara, Ellen; Tran, Diem-Thy Subject: RE: Request for comments on i2b2 customization
Responses in-line.

From: Key, Dustin [key.d.ghc] Sent: Monday, August 13, 2012 5:22 PM To: i2b2 AUG Members Cc: Frawley, Peter; O'Meara, Ellen; Tran, Diem-Thy Subject: Request for comments on i2b2 customization
 
Hi, my site is currently piloting i2b2 in use as a data query tool for mammography data, potentially letting researchers and the public query de-identified mammography data in some capacity.  We have some questions.  Please let me know if you feel that you may be able to assist me and my team with your expertise and share your experience.  Below are some of the main questions we need help with.
 
 
1)  Can i2b2 be used to return a count events rather than people?  1b)  Could it be repurposed to do such?  For instance, a user of i2b2 as data query tool may want to be able to count the number of screening mammograms in a particular year.  It seems to me, that as a cohort discovery tool, i2b2 isn't really wired to do this.   Am I wrong in my thinking, and if not, has anyone repurposed i2b2 to count events, anyway?
      1) On a coarse basis, if you used a surrogate_id for "event", and populated patient_num with the event_surrogate_id. See 1b
      1b) Encounter set. I think in version 1.6 there is the idea of a patient_encounter set, and this might help?  

2)  Does any site have experiencing modifying the look and feel of the i2b2 web client application?  During practice demos of the pilot these types of questions tend to come up:
 
Can we hide temporal constraint functionality from the query interface?
I tried to hide it via commenting it out in the html and broke the application. I think another part of the code looks for it and it was creating a null pointer exception.
  Is it tough to change the 'financial encounter' verbiage?  Probably just editing the html
Can we hide the various qt breakdowns, patient set, encounter set, return toggles that show up when a query is launched? Yes change the visual attribute to "hidden" (LH) in qt_query_result_type.
Is it possible to change the count obfuscation from +/- 3 , < 3 to some other number?    (I think we have to report real sets that number 0 through 4 as "< 5".) 
I have done various edits to .js files to only show "< 10" in the webclient, the files are:
CRC_ctrlr_QryStatus.js  CRC_ctrlr_QryTool.js    CRC_sdx_QI.js
Let me know if you need more info.
Can we modify lock-out criteria? In edu...crc/etc/spring/crc.properties, has lockout properties in this section:

CRC setfinder query lockout parameter
 
2b)  If so, is it difficult to make these modifications? 
 Difficult? Sometimes, depends on your expertise.
And, if you have done customization, has your site had versioning issues or were you able to avoid them?
Things change version to version, and sometimes making the same change, does *NOT* have the same effect.
 
3)  Has anyone instated a public use of i2b2?  We haven't crossed this bridge yet, but we imagine there would be a lot of questions around training, id management, responding to questions about the ontology, handling lock-outs etc..   How has web-based user registration gone for you?
 
With respect to these questions, we would also be interested in the amount of FTE required to do and support these types of customizations.
 
I appreciate any insight that can be lent on these issues.  Thanks--
 
Dustin
Dustin Key
Programmer Analyst
Group Health Research Institute
206-287-2916


From: Jack London [Jack.London.jefferson] Sent: Tuesday, August 14, 2012 10:03 AM To: Key, Dustin; i2b2 AUG Members Cc: Frawley, Peter; O'Meara, Ellen; Tran, Diem-Thy Subject: RE: Request for comments on i2b2 customization
Dustin,
When you ask " Is it tough to change the 'financial encounter' verbiage?" do you simply mean the drop down choices for "Temporal Constraint" in the web client query tool?  This can be easily changed by editing the default.htm file in the "webclient" path.  For instance, you could remove the word "financial" and have the drop choice be "Selected groups in the same encounter."  In a similar fashion, you could probably conceal the temporal constraint functionality entirely.
Jack

Jack W. London, PhD
Thomas Jefferson University
Director, Research Informatics
Research Professor, Cancer Biology
Informatics Shared Resource Director
Kimmel Cancer Center
BLSB, Suite 808, 233 S. 10th Street
Philadelphia, PA 19107
215-503-4599


From: Key, Dustin [key.d.ghc] Sent: Monday, August 13, 2012 8:22 PM To: i2b2 AUG Members Cc: Frawley, Peter; O'Meara, Ellen; Tran, Diem-Thy Subject: Request for comments on i2b2 customization
 
Hi, my site is currently piloting i2b2 in use as a data query tool for mammography data, potentially letting researchers and the public query de-identified mammography data in some capacity.  We have some questions.  Please let me know if you feel that you may be able to assist me and my team with your expertise and share your experience.  Below are some of the main questions we need help with.
 
 
1)  Can i2b2 be used to return a count events rather than people?  1b)  Could it be repurposed to do such?  For instance, a user of i2b2 as data query tool may want to be able to count the number of screening mammograms in a particular year.  It seems to me, that as a cohort discovery tool, i2b2 isn't really wired to do this.   Am I wrong in my thinking, and if not, has anyone repurposed i2b2 to count events, anyway?
 
2)  Does any site have experiencing modifying the look and feel of the i2b2 web client application?  During practice demos of the pilot these types of questions tend to come up:
 
Can we hide temporal constraint functionality from the query interface?  Is it tough to change the 'financial encounter' verbiage? 
Can we hide the various qt breakdowns, patient set, encounter set, return toggles that show up when a query is launched?
Is it possible to change the count obfuscation from +/- 3 , < 3 to some other number?    (I think we have to report real sets that number 0 through 4 as "< 5".) 
Can we modify lock-out criteria?
 
2b)  If so, is it difficult to make these modifications?  And, if you have done customization, has your site had versioning issues or were you able to avoid them?
 
 
3)  Has anyone instated a public use of i2b2?  We haven't crossed this bridge yet, but we imagine there would be a lot of questions around training, id management, responding to questions about the ontology, handling lock-outs etc..   How has web-based user registration gone for you?
 
With respect to these questions, we would also be interested in the amount of FTE required to do and support these types of customizations.
 
I appreciate any insight that can be lent on these issues.  Thanks--
 
Dustin
 
 
Dustin Key
Programmer Analyst
Group Health Research Institute
206-287-2916
 

----
Conference Videos [08-10-2012]
From: Churchill, Susanne E. Sent: Friday, August 10, 2012 2:49 PM To: i2b2 AUG Members Subject: Conference videos
Dear Members,
 
The videos from the July 2012 NLP Workshop and the AUG Conference are now available via click on your webpage: www.i2b2.org/work/aug.html.
 
Thanks to all who attended for a wonderful, interactive meeting.
 
Susanne 
 

----
Loading Procedure Orders into i2b2 [08-09-2012]
From: Larry Errecart [larry.errecart.ucdmc.ucdavis] Sent: Thursday, August 09, 2012 2:55 PM To: Russ Waitman Cc: Bhargav Adagarla; i2b2 AUG Members Subject: Re: Loading Procedure Orders into i2b2
We've been loading both lab results and actual procedures into our 12b2 instance for a couple of years.  I'll approach the two separately: LAB RESULTS We pull lab results from the Clarity Order_Results table.  These are the labs at their most granular.  For instance, a lipid panel would have a single record in the Order_proc table but each individual test (HDL, LDL, Trigly) would have it's own result record.  Our initial effort was to build an ontology based on the Clarity Component name in a alphabetical hierarchy but this wasn't overly elegant.  Consequently, we decided to use the existing LOINC-based ontology in the demo-data i2b2 table.  This, however, mandated the mapping of our labs to LOINC.  Since then we've been able to piece-meal additions to the ontology with clinician involvement.  Numerical results work fine, even when querying by lab result value but those that result in text (i.e. "POSITIVE") are much more problematic.  You have to take a good hard look at the xml in the ontology record and make sure your lab results correspond to what's in the ontology or author your own.  We had a lot of problems with the results coming into Clarity from or LIS as there were numerous instances of misspellings, transposed letter, etc. PROCEDURES Our Epic instance currently contains no after-the-fact inpatient data nor same day surgery procedures (i.e ICD9 DX codes, ICD9 Procedure codes, DRG, etc).  Consequently we pull these from billing and use the ICD9 procedure codes.  We have rebuilt the entire ICD9 ontology in i2b2, as we found some omissions in the demodata and have since contracted with HLI to keep the ontology up-to-date. Hope this helps larry Larry ErrecartData Systems ManagerUC Davis Clinical and Translational Science Center (CTSC)School of Medicine, Dean's Office2921 Stockton Blvd., Suite 1400Sacramento, CA  95817916-703-9224916-703-9135 (fax) larry.errecart.ucdmc.ucdavis From:        "Russ Waitman" <rwaitman.kumc> To:        i2b2 AUG Members Cc:        "Bhargav Adagarla" <badagarla.kumc> Date:        08/09/2012 11:28 AM Subject:        Loading  Procedure Orders into i2b2
Hi all, Bhargav Adagarla on our team is leading the effort to start harvesting procedure orders out of Epic and into i2b2.  At this point, it's probably mainly an inpatient effort since most of the orders are on the inpatient side here but increasingly as we implement Epic in our ambulatory environment, it's going to get more complicated.  Some of this motivated by clinical research but it's also motivated by my informatics research interests.   I have a simple question that then leads into topics of greater complexity:   1.  Has anyone already done this and how did they create their terminology?  If you harvested it out of Epic, was it in Clarity or somewhere buried in Chronicles?  We can't seem to find an obvious hierarchy.  Any pointers you might have would be very helpful.   2.  Have any people given deep thought to the relation of "orders" to results and also CPT?   There are some orders which clearly result in a specific CPT billing code.  Within the content of our order build see do see a percentage where the foreign field of the procedure order maps to a CPT.     Other orders for say a chemistry panel might map to CPTs but also lab results and LOINC codes.  This is similar to how a medication order has a relationship to the pharmacy dispense and the nursing medication administration record.     Other orders, especially nursing activities orders, likely have no foreign field to a standard terminology.   So for some orders, you might be able to organize them and align them with resulting CPT ontologies.  I could imagine that's one route people are taking.  You could then supplement with other branches for items that don't bill via CPT or don't bill at all.     We'd welcome any experiences others may have.   Sincerely,   Russ Waitman Associate Professor, Director of Medical Informatics Department of Biostatistics Assistant Vice Chancellor for Enterprise Analytics University of Kansas Medical Center 913-945-7087 rwaitman.kumc http://informatics.kumc.edu


----
i2b2 1.6 and Patient_Dimension [08-01-2012]
From: Sebastian Mate [Sebastian.Mate.imi.med.uni-erlangen.de] Sent: Wednesday, August 01, 2012 2:56 AM To: Phillips, Lori C. Cc: i2b2 AUG Members Subject: AW: i2b2 1.6 and Patient_Dimension
Hi Lori,
thanks a lot for your quick reply!
It is true that it works if I change C_OPERATOR to '=' and C_DIMCODE to for example '50' – then it queries all patients, who are 50 years old. I also see what you mean by "dynamically calculated age", and I think it's very useful. You simply set C_DIMCODE to for example 'sysdate - (365.25 *33) AND sysdate - (365.25 * 32)'. "32" is the age in years.
However, what I want to allow is "dynamic" querying by using the constrain-by-value feature of the clients, instead of hardcoding the value for all entries ("50" and "32" in the examples above):

But I guess this is not possible, because we would have to embed the two values ("20" and "50") somehow into the SQL snippet in C_DIMCODE. After having a look at the source code I think my guess is correct. (smile)
Anyway – I think that we'll be fine with a custom entry for each year, it'll save us a lot of space in the database ...
Thanks again –
Sebastian

Von: i2b2 AUG Members
Im Auftrag von Phillips, Lori C. Gesendet: Dienstag, 31. Juli 2012 17:58 An: 'Sebastian Mate' Cc: i2b2 AUG Members Betreff: RE: i2b2 1.6 and Patient_Dimension
Sebastian,
 
What you are trying to do should work but you are missing one critical piece in the metadata entry.
It looks like you set c_dimcode to  ' ' , where it should be equal to the ages you want queried for.
So if you are looking for ages 5-7 c_dimcode should be '5,6,7'
 
It looks like the generated sql may contain a bug: it used '=' instead of 'in' but I am not sure how it behaves if c_dimcode is not valid.
So try the following:  set the c_dimcode to the range of ages you desire and retry.
 
If there is still an error in the generated SQL, let us know:
     which version of the hive you are running
     which database type you use 
     what your metadata entry looks like
 
I am enclosing a document that may help; look at section 3.2. It describes how to specify the c_dimcode column.
 
In addition look at section 2.2 where we show a dynamically calculated age as opposed to use of the static age_in_years_num.
This example is provided in the demo metadata in the Demographics/Age folders.
Lori

From: Sebastian Mate [Sebastian.Mate.imi.med.uni-erlangen.de] Sent: Tuesday, July 31, 2012 11:38 AM To: Phillips, Lori C. Cc: i2b2 AUG Members Subject: AW: i2b2 1.6 and Patient_Dimension
Hi Lori,
let me continue with this old e-mail discussion – I'm trying to modify our i2b2 ontology to allow querying for the patient's age in PATIENT_DIMENSION (instead of the OBSERVATION_FACT). I want to create a SINGLE ontology entry for the age,  not a whole age hierarchy as in the demo data. Is this possible?
I've tried to follow your documents and created this entry (highlighted are the relevant columns):

INSERT INTO I2B2
       (
              C_HLEVEL,C_FULLNAME,C_NAME,C_SYNONYM_CD,C_VISUALATTRIBUTES,C_TOTALNUM,C_BASECODE,C_METADATAXML,
              C_FACTTABLECOLUMN ,
              C_TABLENAME,
              C_COLUMNNAME,
              C_COLUMNDATATYPE  ,
              C_OPERATOR,
              C_DIMCODE,
              C_COMMENT,C_TOOLTIP,UPDATE_DATE,DOWNLOAD_DATE,IMPORT_DATE,VALUETYPE_CD,M_APPLIED_PATH,M_EXCLUSION_CD,C_PATH,C_SYMBOL
       )
       VALUES
       (
              4, '\i2b2\ADM\DEM\AUFNALTJ\ Alter (direkt)\', ' Alter (direkt)', 'N', 'LAE', NULL,
              'DEM

AUFNALTER',
              '<?xml version="1.0" encoding="UTF-8"?><ValueMetadata><Version>3.2</Version><CreationDateTime>2010-11-17T20:21:04.486+01:00</CreationDateTime>    <Loinc>Unspecified</Loinc><UnitValues><NormalUnits>Jahre</NormalUnits></UnitValues><DataType>Integer</DataType><Oktousevalues>Y</Oktousevalues></ValueMetadata>',
              'patient_num', 'patient_dimension', 'age_in_years_num', 'N', 'IN', ' ',
              '','Age',SYSDATE,'','','','.','','','');


However, the generated SQL looks like this:

insert into I2B2_KERNDATENSATZ.QUERY_GLOBAL_TEMP ( patient_num , panel_count)
select   patient_num ,1 as panel_count  from ( select  /*+ index(observation_fact fact_cnpt_pat_enct_idx) */  patient_num  from I2B2_KERNDATENSATZ.patient_dimension where  patient_num IN (select patient_num from I2B2_KERNDATENSATZ.patient_dimension    where age_in_years_num =  )    group by   patient_num  ) t



Thanks
Sebastian
PS: Sorry for the excessive use of e-mail formatting

Von: i2b2 AUG Members
Im Auftrag von Phillips, Lori C. Gesendet: Dienstag, 24. Mai 2011 21:42 An: Phillip Reeder; i2b2 AUG Members Betreff: RE: i2b2 1.6 and Patient_Dimension
Definitely; the metadata has to be specified in a certain way to make this happen.
This document explains. (see page 6)
 
For the example where we search by 'race_cd = white' the metadata entry would have:
 
c_facttablecolumnname = patient_num
c_tablename =     patient_dimension
c_columnname =    race_cd
c_operator =    '='
c_dimcode = white
 

From: Phillip Reeder [Phillip.Reeder.uth.tmc] Sent: Tuesday, May 24, 2011 3:29 PM To: i2b2 AUG Members Subject: i2b2 1.6 and Patient_Dimension
Is it possible to use the race_cd, language_cd, religion_cd, etc.  columns  from the patient_dimension in a query?  I'm working on loading our data into the 1.6 schema and wanted to verify the functionality before I create observation_fact rows from all the columns.
Thanks,
Phillip Reeder
Manager, Systems Analyst Services
The University of Texas Health Science Center at Houston
Center for Clinical and Translational Sciences
School of Biomedical Informatics
ph 713-500-3970

  • No labels