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

NEXT EMAIL
From: Peter Beninato [beninato.ohsu]
Sent:Monday, January 30, 2012 4:35 PM
To: Henderson, Darren W; i2b2 AUG Members
Subject:RE: i2b2 - v1.6.02 - observation fact primary key - financial encounters - Provider_Id/Patient_Num
Hi,
1) ... you saying your observations are individually time?stamped throughout a single financial encounter?
So a HAR encompasses a set of encounters, each encounter has date/timestamp. The HAR also has a date/timestamp, but similar to your approach, for inpatient, it is admission date/time. I can map the HAR to encounters, and use the HAR for the encounter_num, but I can still reach in and get the individual encounter_dates, and use that for start_date.
Outpatient works differently, and the ability to link via financial encounter has less benefit, because there is little difference between the outpatient encounter data, and related HAR data.
The benefit for outpatient would be, if, say a patient had an office visit, and some labs were ordered, then the next day the patient shows up at the lab for the specimen to be drawn. If the office visit encounter, and the lab encounter were connected then the concept of the "financial encounter" would have benefit. Unfortunately, it seems for outpatient, these events are captured separately, and not necessarily tied together via a "HAR" ( they seem to have separate HARs).
2) ...then you have an "encounter id" for each time a patient received a procedure or an observation was made?
Yes. But I'll use the surrogate HAR Id which will tie them together at the encounter_num level.
3) ... On the last situation you bring up...
So, a patient is admitted to the hospital in the end of December, and has a HAR date of Dec. 27, 2011. During the extended hospital stay, unfortunately, the patient has a hospital?acquired infection, on Jan 10, 2012, which
is recorded as a diagnosis, on an encounter that is tied to the HAR. A grant comes out to study hospital?acquired
infections, and the time scope is 2011. The HAR date would be 2011, but in reality, this infection didn't occur till
2012. That is why, though I can see using the HAR for the encounter_num, I think it is still best to use the individual encounter dates (obfuscated by randomness), as the start_date.
4) Two dates
I'm not sure that there needs to additional functionality. Maybe just a strategy on what date to use for start_date, and then to communicate that clearly to the investigator/end users of the system.
Thanks for the feedback.
Peter

From: Henderson, Darren W [darren.henderson.uky]
Sent: Monday, January 30, 2012 1:08 PM
To: Peter Beninato; i2b2 AUG Members
Subject: RE: i2b2 - v1.6.02 - observation fact primary key - financial encounters - Provider_Id/Patient_Num
You do bring up a good point about temporal constraints. To answer your first question, we are just using the first date of an inpatient stay at the start date.
To make sure I'm understanding your question regarding your encounter Ids, are you saying your observations are individually time?stamped throughout a single financial encounter? That is you have financial encounter id, 1 number whole visit (this HAR), then you have an "encounter id" for each time a patient received a procedure or an observation was made?
On the last situation you bring up, it would be feasible to capture this type of encounter if some of the query language was slightly modified. I believe right now the generator searches for encounters with a start_date between the two time constraints sent in the message. If it were to instead search for encounters with a start_date <= the ceiling and
end_date >= the floor then it would capture events that either overlap into, exist entirely within the timespan searched,
or overlap out of the timespan.
To clarify, if the user selects for example Jan 1 2009 to Jan 31 2009, and there is an encounter that started on say Dec
27, and they were discharged on Jan 12, searching just for the start_date between 1?1?2009 and 1?31?2009 would not return this event in your set. If you searched for start_date <= Jan 31 AND end_date >= Jan 1, you would return this event as well as any event that starts between those two dates as well, but with the end_date restriction you aren't returning extraneous records from the previous year unless they were discharged after Jan 1 as well. This would have to be a feature added by the devs though. The time constraints aren't driven by metadata that you could really alter.
DWH

From: Peter Beninato [beninato.ohsu]
Sent: Monday, January 30, 2012 3:00 PM
To: Henderson, Darren W; i2b2 AUG Members
Subject: RE: i2b2 - v1.6.02 - observation fact primary key - financial encounters - Provider_Id/Patient_Num
Hi.
Thanks. That could be it.
How are you expecting to handle start_date. Let me elaborate.
I have an encounter map table in my ETL schema. It contains the patient encounter id (pat_enc_csn_id for those who use EPIC), and a surrogate ID, for use currently as the encounter_num in observation_fact.
In our system there is a concept of the HAR (Hospital Account Receivable ID is how I interpret what HAR to stand for). And there is also a primary_encounter CSN ID.
I was thinking of adding a column to the encounter_map table for the "financial encounter" which I think will be the
HAR, and probably a "HAR_Date",
Or upon more reflection, I might have a separate table, since I don't want to use the actual HAR, but rather a surrogate
ID.
A surrogate id for the HAR/financial encounter could be loaded as the encounter_num in the observation_fact. I guess I will use the existing encounter_date ( obfuscated by a random factor) as the start_date.
Or...
Should I just apply the random factor to the "HAR_Date", and use that throughout?
(which I think is the 1st day of an inpatient visit)
If someone has a long hospital stay that spans calendar years, and an investigator put a constraint via "Dates", or even just for greater accuracy, it would seem more accurate to use the encounter_date, rather than the HAR_Date.
If you have an opinion regarding this, Please chime in.
Peter

From: Henderson, Darren W [darren.henderson.uky]
Sent: Monday, January 30, 2012 11:43 AM
To: Peter Beninato
Subject: RE: i2b2 - v1.6.02 - observation fact primary key - financial encounters - Provider_Id/Patient_Num
From the claim and db pov's, a unique encounter_num should always be assigned to a single patient_num. Having patient_num in the primary key with encounter_num would then be redundant, and offer no additional layer to ensure uniqueness. Provider_id being part of the primary key helps ensure that if two different providers were to make the same observation with a patients stay, that they could be treated uniquely as such. This can be especially helpful to differentiate the same procedure, performed by different people within the same financial encounter for example.

From: Peter Beninato [beninato.ohsu]
Sent: Monday, January 30, 2012 2:26 PM
To: i2b2 AUG Members
Subject: i2b2 - v1.6.02 - observation fact primary key - financial encounters - Provider_Id/Patient_Num
Hi,
As I start to do some testing regarding the ability to tie counts together via encounter, which will probably require the use of the instance_num, to segregate readings, I went on to explore where that column is used.
I see that it is part of the primary key.
In the situation where someone is inpatient for days, under the same financial encounter, multiple observations like BP
readings need to be differentiated, and the instance_num will do that. (still debating about what start_date to use).
What caught my eye was that Provider_Id is part of the primary key and that Patient_num is not. There must be a reason for the prominence of Provider_Id in the PK.
Can someone explain the logic regarding the fields selected as the primary Key on the observation fact? Thanks.
CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY(ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM
Peter Beninato – OCTRI DW Developer
Oregon Health & Science University
503-494-9985 beninato.ohsu


NEXT EMAIL
From: Lisa Miao [lmiao.regenstrief]
Sent:Monday, January 30, 2012 3:50 PM
To: i2b2 AUG Members
Subject:jboss server runtime shutdown

I have just experienced a jboss server shutdown that I cannot figure out the reason. Here are all the info that I can find in the server.log file.
Has anyone experienced this and know what the cause might be? Thanks,
Lisa.

2012?01?30 15:43:58,564 INFO [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] Located DataSource for hiveId=[Regenstrief] projectId=[/Wishard/]
2012?01?30 15:43:58,564 INFO [edu.harvard.i2b2.crc.dao.OracleDAOFactory] Using datasource java:QueryToolWishardDS
2012?01?30 15:43:58,693 INFO [org.jboss.system.server.Server] Runtime shutdown hook called, forceHalt: true
2012?01?30 15:43:58,693 INFO [org.jboss.system.server.Server] JBoss SHUTDOWN: Undeploying all packages
2012?01?30 15:43:58,700 INFO [org.jboss.ejb.EJBDeployer] Undeploying: file:/opt/jboss?
4.2.2.GA/server/default/tmp/deploy/tmp3094296412767917671QP1.ear?contents/QueryProcessor?EJB.jar
2012?01?30 15:43:59,011 INFO [org.jboss.ejb.plugins.local.BaseLocalProxyFactory] Unbind EJB LocalHome
'querytool.QueryRun' from jndi 'ejb.querytool.QueryRunLocal'
2012?01?30 15:43:59,017 INFO [org.jboss.ejb.plugins.local.BaseLocalProxyFactory] Unbind EJB LocalHome
'querytool.QueryResult' from jndi 'ejb.querytool.QueryResultLocal'
2012?01?30 15:43:59,023 INFO [org.jboss.ejb.plugins.local.BaseLocalProxyFactory] Unbind EJB LocalHome
'querytool.QueryManager' from jndi 'ejb.querytool.QueryManagerLocal'
2012?01?30 15:43:59,029 INFO [org.jboss.ejb.plugins.local.BaseLocalProxyFactory] Unbind EJB LocalHome
'querytool.QueryInfo' from jndi 'ejb.querytool.QueryInfoLocal'
2012?01?30 15:43:59,060 INFO [org.jboss.ejb.plugins.local.BaseLocalProxyFactory] Unbind EJB LocalHome
'querytool.PdoQuery' from jndi 'ejb.querytool.PdoQueryLocal'
2012?01?30 15:43:59,066 INFO [org.jboss.ejb3.EJBContainer] STOPPED EJB:
edu.harvard.i2b2.crc.ejb.analysis.AnalysisPluginInfo ejbName: AnalysisPluginInfo



NEXT EMAIL
From:Mendis, Michael E.
Sent:Monday, January 30, 2012 2:52 PM
To:Law, James; Peter Beninato
Cc: Lisa Miao; i2b2 AUG Members
Subject:Re: which role will allow user to see patient set
That is correct to see patient set you need DATA_LDS
mike

On 1/30/12 2:49 PM, "Law, James" <jlaw.med.umich> wrote:
I believe that if you add DATA_LDS role, then patient sets are enabled.
James

On Jan 30, 2012, at 2:43 PM, Peter Beninato wrote:
I know de?identifed user allows seeing patient sets.

From: Lisa Miao [lmiao.regenstrief]
Sent: Monday, January 30, 2012 11:37 AM
To: i2b2 AUG Members
Subject: which role will allow user to see patient set
Does anyone know which role will allow a user to be able to bring back patient set? I have a user that has "DATA_AGG" role, but can NOT see patient set.
Looking through the document, it seems like the next level is admin. Is it true only ADMIN can see patient set? Is there something in between? Thanks,
Lisa.


NEXT EMAIL
From: Steward, Duane [dsteward.NEMOURS]
Sent:Monday, January 30, 2012 12:55 PM
To: Kohane, Isaac Samuel; i2b2 AUG Members
Cc:Murphy, Shawn N.; Churchill, Susanne E.
Subject:RE: IRB submission artifacts

Was the idea of sharing successful IRB submissions ever realized in the scope of the AUG?
If so I can't find where in the i2b2 community wiki. Keith Marsolo shared one with us offline, but I don't recall seeing this idea coming to life in the fuller sense of a collection on the wiki. It sure would be a valuable resource.
Duane Steward, DVM, MSIE, PhD Chief Computer Scientist for Health Informatics Nemours

From: Kohane, Isaac Samuel [Isaac_Kohane.hms.harvard]
Sent: Wednesday, December 14, 2011 10:26 AM
To: Steward, Duane
Cc: i2b2 AUG Members; Shawn N. Murphy; Susanne Churchill
Subject: Re: IRB submission artifacts <-- Re: questions about dataschema


On Dec 14, 2011, at 9:59 AM, Steward, Duane wrote:
Zak, et al,
The response to one question this morning prompts action on a tangent need we have---i.e., due diligence with IRB
submission for a newly deployed hive.
Are any of you at liberty to share your successful IRB submission artifacts or portions thereof?
I think we can do that and I encourage others to do the same. I wonder if we could keep a set of these documents on our community site?


Zak
Rather than reinvent that wheel from a blank page, it sure makes sense to humbly ask--especially in view of the fact that it entails submitting a protocol for unspecified research use--just one example of unprecedented aspects we're asking an IRB to consider. I for one greatly appreciate the pavement laid in Boston on this path and hope to take lessons learned and shared to heart.
In terms of the answer to this morning's question, is there some place where I can "access" IRB protocols of Brian Wilson, Boston entities or elsewhere? We are eager to learn from any willing to share or advise. I am happy to share with others as we wordsmith an IRB submission for our hive in the next several weeks.
:)uane
Duane Steward, DVM, MSIE, PhD Chief Computer Scientist for Health Informatics Nemours

From: Kohane, Isaac Samuel [Isaac_Kohane.hms.harvard]
Sent: Wednesday, December 14, 2011 8:43 AM
To: Laukkanen Markku
Cc: i2b2 AUG Members
Subject: Re: questions about dataschema
[...]
3. With regard to specimens: I would look at the new cells that Brian Wilson has developed that speaks to better integration with biorepositories and the IRB protocols for access.
[...]
Zak
Isaac S. Kohane, MD, PhD
Professor of Pediatrics and Health Sciences and Technology
Children's Hospital and Harvard Medical School
Director, Countway Library of Medicine
Director, i2b2 National Center for Biomedical Computing
Co-Director, HMS Center for Biomedical Informatics
10 Shattuck Street
Boston, MA 02115
617-432-2144 isaac_kohane.harvard
http://www.chip.org/~zak/

On Dec 14, 2011, at 12:04 AM, Laukkanen Markku wrote:
Hi all AUG members,
I am part of team evaluating i2b2 to our purposes. First some background info:
Currently we have about 120 projects/cohorts (~ 250000 subjects with about million samples).
We are in phase of importing phenotypes into our in house LIMS (some of the cohorts already have those in db)
Our phenotype structure is build using trees, e.q. compared into i2b2 ontology tree, our trees doesn't specify the full parent hierarchy, only nearest parent.
So the leaf node is simply node with no children (smile)
Phenotypes (leaf nodes) can be specified very strictly, namely by type, by allowed values, by min-max values, by enumerated questionnaire values (1 means sick, 2 means maybe sick etc etc). This is for that, that it is automatically possible to do checks for values when importing information for samples/subjects.
Also missing values are taken into account, and the "certainty of information", some of the cohorts date back into 70s
Each cohort will have it's own ontology tree, and similarities between ontology leafs are defined in other table. This makes it possible to define, that ontology items are similar, opposite, nearly similar etc etc. For me it seems, that i2b2 ontology tree only defines exact match ?
The measurement(s)/data points for items in cohorts come both for individuals, and for specific samples (e.q. gender check for samples to look for contamination).
So basically data items for subject can contain measured phenotypes, and data times for samples contain measured items from that particular sample (sampling date, individual's medicine at the time of sampling etc etc)
From the i2b2 point of view, it seems that only patient data points are supported ?
Our samples also create sample tree, in style blood->dna->aliquote->aliquote->aliquote
Our individuals also create family structures parent-child-grand child etc etc
Our question is, is it feasible to try to use i2b2 framework for this, when our researches has a need to a) query subjects from one/several different project(s) at the time
b) query availability of samples for subjects c) do queries based on family structures
d) exclude from queries subjects without any sample's to use left e) access to projects are restricted by end user privileges

Regards, Hi

From: Phillips, Lori C. [LCPHILLIPS.PARTNERS]
Sent: Tuesday, December 13, 2011 6:28 PM
To: Askar Ibragimov; members.i2b2aug
Subject: RE: questions about dataschema
Askar,
I can help with your Ontology table questions.
The Ontology table described in Section 2.2 is equivalent to the I2B2 table identified in the metadata database. This table governs the display of terms in the Navigate Terms view.
Relation to concept_dimension table
Each concept in the metadata table is uniquely defined by its c_fullname. Each unique c_fullname, in turn, has a record in the concept_dimension table where metadata.c_fullname = concept_dimension.concept_path.
Configuring metadata across two tables.
A prime example of this is provided in the demo package metadata database tables.
Three tables are provided: I2B2, CUSTOM_META and BIRN (the structure off all three of these tables is identical to the Ontology table shown in section 2.2)
A separate table, TABLE_ACCESS, identifies the tables used to create a project's metadata or ontology. In our demo package this table contains entries that
assign these three tables to a root level entry in the Navigate Terms view.
I hope this helps you. Lori Phillips
Original Message
From: Askar Ibragimov [askar.ibragimov.gmail] Sent: Tuesday, December 13, 2011 11:03 AM
To: members.i2b2aug
Subject: questions about dataschema
Dear AUG members,
I went across data format for i2b2 and hope that I could resolve few of my questions and verify that I have understood everything right. I am still in understanding process, so some querstions might be a little silly (smile)
In our production, we will need a rather complex data access rights. Is it right that for each project we would need to create separate set
of datatables? I could not see project-related info in data mart tables (observation_fact etc). Or if not, how users
Can we query somehow two or more projects at once, or we're limited to one project at a time?
Is it correct, that for any "enumerated types" (when you have a "lab test" resulting in some choice out of fixed amount), you define all enumerations in CODE_LOOKUP?
Could somebody explain what is the Ontology table defined in Ontology Design, sect.2.2 document? It's not a table in the database. Where this entity exists, how it is related to concepts table?
It is said that ontology can be splitted across few tables, how would you configure that in practice?
Does concepts table allow for some additional columns?
If I have addtional colimns in visit and patient data, are they automatically shown in query results?
and few more from our users who tried workbench:
How do you define variables? How do you define individuals, samples, family relations, etc.?
Thank you in advance, Askar


NEXT EMAIL
From: Mark Weiner [mweiner.mail.med.upenn]
Sent:Monday, January 30, 2012 9:58 AM
To:Mendis, Michael E.; 'Peter Beninato'; i2b2 AUG Members
Subject:RE: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role

Will do (in a separate message). A similar thing happens with the webclient, though the error is that it says the PM cell is not available. On a possibly related note, the loading of the cells is not successful when "localhost:9090" is specified in the "Cell URL" textbox of the "Manage Cells" section of the admin tool. Instead, I must specify the explicit IP address.
When trying to run the admin tool on the same machine as the database and the i2b2 software, specifying the PM location as "localhost:9090" "127.0.0.1:9090" or the IP address in the i2b2_config_data.js file doesn't seem to make any difference.
I recall a problem like this in the past – perhaps something to do with the curl libraries on the newly configured machine?? Curl is installed, but could the version make a difference? Its 7.19.7 on the "bad" machine and 7.21.2 on the good machine. I ran some tests and curl is working. Just a shot in the dark!
Mark

From: Mike Mendis [mmendis.partners]
Sent: Monday, January 30, 2012 9:12 AM
To: Mark Weiner; 'Peter Beninato'; i2b2 AUG Members
Subject: Re: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Mark,
That is strange. Can you send the i2b2_config_data.js for both servers,
mike


On 1/29/12 10:20 PM, "Mark Weiner" <mweiner.mail.med.upenn> wrote:
I spent far too much time this weekend trying to get past the "Requires ADMIN role" error on a new physical machine that was just created. I already had the PM cell running on a virtual machine built from source, though on the vm I was having trouble with getting the other required cells to load
On the new machine, the record with project_id= '@' was in the table I2B2PM.PM_PROJECT_USER_ROLES, and I modified the "i2b2_config_data.js" file as outlined by Peter. However, I was still getting the same error. I was about to give up, but then I decided to alter the "i2b2_config_data.js" file in the admin directory on the VM to point to the PM cell on the new server. Amazingly, this worked, and I proved to myself that it was connecting to the correct cell on the correct server and the correct database by using the admin tool to add a user – the information appeared in the new database, even though the admin tool was running on the VM. To double check this, I altered the i2b2_config_data.js file in the prepackaged i2b2 VM, and it worked from there as well.
I am at a loss to explain why a connection to the PM cell from an ADMIN website running on a different server works, but the same admin website running on the same server as everything else does not work.
Anyone else with similar experiences? Mark

From: Peter Beninato [beninato.ohsu]
Sent: Thursday, December 29, 2011 10:57 AM
To: Mike Mendis; i2b2 AUG Members
Subject: RE: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Hi,
Thanks Mike.
Peter


From: Mike Mendis [mmendis.partners]
Sent: Thursday, December 29, 2011 7:51 AM
To: Peter Beninato; i2b2 AUG Members
Subject: Re: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Peter,
Glad that it working for you now. We are in the process of updating some the documentation based on your experience to make it easier to understand for other users.
Thanks again mike


On 12/28/11 6:37 PM, "Peter Beninato" <beninato.ohsu> wrote:
Hi,
Below are the steps to get the ADMIN site available via the thinclient. Please note that some of the scripting (sql) is geared for Oracle, but easily modified.
Please consider modifying the code for future editions so others won't have to struggle with this. For instance, the INSERT does not exist in:
i2b2\v1_6_02\i2b2createdb?1602\edu.harvard.i2b2.data\Release_1?6\NewInstall\Pmdata\scripts
or i2b2\v1_6_02\i2b2createdb?1602\edu.harvard.i2b2.data\Release_1?6\Upgrade\Pmdata\scripts
Also the project_management_installation_guide.pdf should probably be edited too.
The steps to get the ADMIN site via the thinclient is as follows:
1) INSERT INTO I2B2PM.PM_PROJECT_USER_ROLES
2) Edit the file: i2b2\v1_6_02\i2b2core?src?1602\src\admin\i2b2_config_data.js (before deployment or the same file under the apache server after deployment) for the parameters: domain, name, urlCellPM
Step 1(code):
INSERT INTO I2B2PM.PM_PROJECT_USER_ROLES (
PROJECT_ID, USER_ID, USER_ROLE_CD, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD
)
VALUES (
'@'
,'i2b2'
,'ADMIN'
,sysdate
,sysdate
,'i2b2'
,'A'
); Commit;
Step2:
domain: have this parameter match the value in i2b2pm.pm_hive_data.domain_name name: set this to "localhost"
ulrCellPM: http://localhost:9090/i2b2/rest/PMService/
Below is example admin/i2b2_config_data.js:
{
urlProxy: "index.php", urlFramework: "js?i2b2/",
//???????????????????????????????????????????????????????????????????????????????????????????
// THESE ARE ALL THE DOMAINS A USER CAN LOGIN TO
lstDomains: [
{ domain: "i2b2demo",
name: "localhost",
urlCellPM: "http://localhost:9090/i2b2/rest/PMService/", allowAnalysis: false,
adminOnly: true, debug: true
}
]
//???????????????????????????????????????????????????????????????????????????????????????????
}
Peter Beninato - OCTRI DW Developer
Oregon Health & Science University
503-494-9985
beninato.ohsu <beninato.ohsu>


NEXT EMAIL
From: Steward, Duane [dsteward.NEMOURS]
Sent:Monday, January 30, 2012 9:39 AM
To: Mendis, Michael E.; Mark Weiner; i2b2 AUG Members
Subject:RE: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role

Yes, Mark, similar experience here.
I will confess that our experience confirmed the demand to be careful that the apache http server is operating with the modified i2b2_config_data.js and not a version cached before the modification.
With so many layers to the configuration of i2b2, it is also easy to get tripped up by erroneous assumptions about which instance of i2b2 Axis2 services is being employed in a context of multiple i2b2 stacks---i.e., don't let erroneous or confused URLs in the PM_CELL_DATA tables go unnoticed. If those URLs contain "localhost", don't forget that URL will have different results in web clients at different host contexts.
I am not saying you would be so foolish as to fall into these traps---just confessing some of the things we have had to think about to get past similar problems between the keyboard and chair. Most of the time, I found a flaw in our validation test concealing the flaw in the i2b2 configuration.
Duane Steward, DVM, MSIE, PhD Chief Computer Scientist for Health Informatics Nemours
Office: (407) 650-7675
Cell: (407) 230-6081
Fax: (407) 650-7722

From: Mike Mendis [mmendis.partners]
Sent: Monday, January 30, 2012 9:12 AM
To: Mark Weiner; 'Peter Beninato'; i2b2 AUG Members
Subject: Re: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Mark,
That is strange. Can you send the i2b2_config_data.js for both servers,
mike


On 1/29/12 10:20 PM, "Mark Weiner" <mweiner.mail.med.upenn> wrote:
I spent far too much time this weekend trying to get past the "Requires ADMIN role" error on a new physical machine that was just created. I already had the PM cell running on a virtual machine built from source, though on the vm I was having trouble with getting the other required cells to load
On the new machine, the record with project_id= '@' was in the table I2B2PM.PM_PROJECT_USER_ROLES, and I modified the "i2b2_config_data.js" file as outlined by Peter. However, I was still getting the same error. I was about to give up, but then I decided to alter the "i2b2_config_data.js" file in the admin directory on the VM to point to the PM cell on the new server. Amazingly, this worked, and I proved to myself that it was connecting to the correct cell on the correct server and the correct database by using the admin tool to add a user – the information appeared in the new database, even though the admin tool was running on the VM. To double check this, I altered the i2b2_config_data.js file in the prepackaged i2b2 VM, and it worked from there as well.
I am at a loss to explain why a connection to the PM cell from an ADMIN website running on a different server works, but the same admin website running on the same server as everything else does not work.
Anyone else with similar experiences? Mark

From: Peter Beninato [beninato.ohsu]
Sent: Thursday, December 29, 2011 10:57 AM
To: Mike Mendis; i2b2 AUG Members
Subject: RE: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Hi,
Thanks Mike.
Peter

From: Mike Mendis [mmendis.partners]
Sent: Thursday, December 29, 2011 7:51 AM
To: Peter Beninato; i2b2 AUG Members
Subject: Re: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Peter,
Glad that it working for you now. We are in the process of updating some the documentation based on your experience to make it easier to understand for other users.
Thanks again mike

On 12/28/11 6:37 PM, "Peter Beninato" <beninato.ohsu> wrote: Hi,
Below are the steps to get the ADMIN site available via the thinclient. Please note that some of the scripting (sql) is geared for Oracle, but easily modified.
Please consider modifying the code for future editions so others won't have to struggle with this. For instance, the INSERT does not exist in:
i2b2\v1_6_02\i2b2createdb?1602\edu.harvard.i2b2.data\Release_1?6\NewInstall\Pmdata\scripts
or i2b2\v1_6_02\i2b2createdb?1602\edu.harvard.i2b2.data\Release_1?6\Upgrade\Pmdata\scripts
Also the project_management_installation_guide.pdf should probably be edited too.
The steps to get the ADMIN site via the thinclient is as follows:
1) INSERT INTO I2B2PM.PM_PROJECT_USER_ROLES
2) Edit the file: i2b2\v1_6_02\i2b2core?src?1602\src\admin\i2b2_config_data.js (before deployment or the same file under the apache server after deployment) for the parameters: domain, name, urlCellPM
Step 1(code):
INSERT INTO I2B2PM.PM_PROJECT_USER_ROLES (
PROJECT_ID, USER_ID, USER_ROLE_CD, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD
)
VALUES (
'@'
,'i2b2'
,'ADMIN'
,sysdate
,sysdate
,'i2b2'
,'A'
); Commit;
Step2:
domain: have this parameter match the value in i2b2pm.pm_hive_data.domain_name name: set this to "localhost"
ulrCellPM: http://localhost:9090/i2b2/rest/PMService/
Below is example admin/i2b2_config_data.js:
{
urlProxy: "index.php", urlFramework: "js?i2b2/",
//???????????????????????????????????????????????????????????????????????????????????????????
// THESE ARE ALL THE DOMAINS A USER CAN LOGIN TO
lstDomains: [
{ domain: "i2b2demo",
name: "localhost",
urlCellPM: "http://localhost:9090/i2b2/rest/PMService/", allowAnalysis: false,
adminOnly: true, debug: true
}
]
//???????????????????????????????????????????????????????????????????????????????????????????
}

Peter Beninato - OCTRI DW Developer
Oregon Health & Science University
503-494-9985
beninato.ohsu <beninato.ohsu>



NEXT EMAIL
From: Lisa Miao [lmiao.regenstrief]
Sent:Friday, January 27, 2012 12:00 PM
To: i2b2 AUG Members
Subject:RE: How can user without admin role see patient count in webclient

Dear everyone,
I sent out the following question and I got very nice responses trying to help out. Thanks everyone.
After following the suggestions and practicing with different roles, I found out by adding the "DATA_AGG" to the user, the user will be able to see the patient count.
Thanks everyone for helping out! I love the way this community works! Lisa.

From: Lisa Miao [lmiao.regenstrief]
Sent: Friday, January 27, 2012 11:05 AM
To: i2b2 AUG Members
Subject: How can user without admin role see patient count in webclient
Dear i2b2 team,
When webclient is used, how can a user who does NOT have admin role see the patient counts on the term tree? It looks like by default, he/she cannot see it. In workbench, there is a place that you can enable yourself to be able to see the patient accounts. What we do in webclient?
Thanks, Lisa.


NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky]
Sent:Friday, January 27, 2012 11:20 AM
To: Peter Beninato; Lisa Miao; i2b2 AUG Members
Subject:RE: How can user without admin role see patient count in webclient

We chose to count distinct patients for demographics, encounters for diagnoses etc. That is an interesting idea though that you brought up about counting encounters, and then altering the c_name field. We might go back and try it your way. Thanks!


From: Peter Beninato [beninato.ohsu]
Sent: Friday, January 27, 2012 11:16 AM
To: Lisa Miao; i2b2 AUG Members
Subject: RE: How can user without admin role see patient count in webclient
Hi,
There is a feature that will show the c_totalNum, which is an option. Under the the icon, "Show Options" (v1.6.02) there is a check box "Enable Patient Counts".
I'm not sure if it has some role restrictions, I don't recall seeing it as an obfuscated user.
Also, my understanding is that c_totalnum, which is what gets displayed if you "Enable Patient Counts", should really be the number of observations which is different than the number of distinct patient_nums.
We have as step in our ETL process that calculates the number of observations, and the number of distinct patients for the ontololgy tree. Then we plug the number of observations into the c_totalnum column, and we update the c_name column and append a slightly obfuscated (< than 10 etc.) count so when a user sees an ontology item, they also have a general idea of how many patients can be found for that item.
Peter

From: Lisa Miao [lmiao.regenstrief]
Sent: Friday, January 27, 2012 8:05 AM
To: i2b2 AUG Members
Subject: How can user without admin role see patient count in webclient
Dear i2b2 team,
When webclient is used, how can a user who does NOT have admin role see the patient counts on the term tree? It looks like by default, he/she cannot see it. In workbench, there is a place that you can enable yourself to be able to see the patient accounts. What we do in webclient?
Thanks, Lisa.



NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky]
Sent:Friday, January 27, 2012 11:16 AM
To: Lisa Miao; i2b2 AUG Members
Subject:RE: How can user without admin role see patient count in webclient
Attachments:Untitled.png

This is my understand of it someone can correct me if I'm incorrect:
This can be done through the web admin client by going to manage projects > the project name > users > the user. To see the patient counts the user must have the Limited Data Set data path checked. The roles can be understood from bottom to top as increasing levels of data access. The patient count plugin shows the exact number of patients without obfuscation so they need Limited Data Set privileges to see that.
You must have the c_totalnum field in your metadata populated to make use of this feature.

From: Lisa Miao [lmiao.regenstrief]
Sent: Friday, January 27, 2012 11:05 AM
To: i2b2 AUG Members
Subject: How can user without admin role see patient count in webclient
Dear i2b2 team,
When webclient is used, how can a user who does NOT have admin role see the patient counts on the term tree? It looks like by default, he/she cannot see it. In workbench, there is a place that you can enable yourself to be able to see the patient accounts. What we do in webclient?
Thanks, Lisa.


NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky]
Sent:Thursday, January 26, 2012 3:11 PM
To: Murphy, Shawn N.; i2b2 AUG Members
Subject:RE: Flaw in query logic for an exclusion type query

Thanks for the quick reply. That sounds great. DWH

From: Murphy, Shawn N. [SNMURPHY.PARTNERS]
Sent: Thursday, January 26, 2012 2:51 PM
To: Henderson, Darren W; i2b2 AUG Members
Subject: RE: Flaw in query logic for an exclusion type query
Thanks Darren, we are looking into this and your other issue as we move forward with 1.6.03 which should be out shortly. We greatly appreciate the work that you are doing on this.
Shawn.

From: Henderson, Darren W [darren.henderson.uky]
Sent: Thursday, January 26, 2012 2:37 PM
To: i2b2 AUG Members
Subject: Flaw in query logic for an exclusion type query
In my testing I believe I have stumbled across an error in the approach taken to produce the result set for a query that includes a panel to be excluded. If anyone recalls, I've written into the list prior concerning exclusion queries and their performance, but received little to no feedback.
I have attached a screengrab of our dev environment (left) vs. our production environment (right). I was testing performance differences, but when the production environment failed to return any patients that fit the criteria, for datasets that are exact copies of each other, I was worried. Upon reviewing the generated SQL in the QT_QUERY_MASTER table for the production environment I believe I discovered a quirky error that I would like to get some feedback on. It would be helpful to see if anyone else can replicate these panels and also review their generated sql in qt_query_master, or at the very least replicate and exclusion query of some kind. Note: we are running version 1.6.02.
The generated SQL for our production environment is attached. Forgive the non?human readable metadata, but if you refer to my previous messages to the AUG you will see the reasons for this. For reference, 11935 is the concept "Benign essential Hypertension" with 5974 encounters, 8143 is Malignant Secondary Hypertension with 19 encounters, 4810 is Diabetes Mellitus with 105,313 encounters. The c_totalnum field is populated in our metadata as well.
If you refer to the attached SQL (Prod Generated SQL 3 Panel Exclusion.sql) you can see that the first thing the query engine does is pull the entire visit_dimension table into temporary space and assign a panel count of 1 to every row.
The next statement updates the temporary table, where the panel count is 1, and the encounter_num and patient_num fields do not exist in the subset of events that have the concept for Benign essential hypertension. In our data, that sets (4.2M – 5974) events panel count field to ?1.
The next statement updates the temporary table, again looking only at those in the temp table with a panel count of 1 (which as of this step only the 5974 visits for b.ess.hypertension retain a panel count of 1), and sets it to ?1 if it doesn't exist in the set of events that have the concept for Malignant secondary hypertension. This is the fundamental flaw.
This fundamentally changes the panel from an OR list into an AND list. I don't believe this is the intended function of the query engine because if the exclusion panel is not included, an OR list is produced by the generator. The result set is incorrect because of this flaw.
What I see when I follow this query step by step, is that the temporary table will eventually have a panel count of ?1 for all events. Meaning no event ever reaches the next panel count step. If the panels are truly to be treated as OR lists, then the result 0 for the panel screenshot I attached is simply wrong, and the SQL generated for exclusion queries must be addressed.
Now for the sake of prudence, please see the two additional screenshots of the production environment that I have attached. One is the result of a normal query without the exclusion panel, with a race breakdown so that one can see that our production environment does contain events that match this query, and members of all races are present for the query. The other screenshot is an exclusion query by faking the exclusion, that is dragging all other concepts in our metadata for race into the 3rd panel. As you can see in the screenshot, it returns the exact same result as the dev environment.
For reference to how the dev environment came up with the number of patients seen in the screenshot see the second attached SQL file that uses common table expressions with left joins to facilitate the query. As a database analyst, this is how I would ask the question by hand, but this is also programmatically generated by a SQL server side hack that I have been developing to address the performance impacts of some of the temp table update queries for many concepts. I also attached the execution plan for the dev sql. The production sql generated 19 separate execution plans for the
batch so I won't attach them here. The major performance concern is that with the update statements and the temporary tables in production the plans are full of full table scans and cannot make use of parallelism to improve performance. Meaning, it does not matter how many cores your server has, you get one process and one read from the disk.
Lastly, if there is a developers mailing list that I should be sending this type of correspondence to, by all means direct me there so I don't waste anyone's time on the Academic Users Group. I notice that very few are interested in discussing these types of issues, instead using this mailing list for more quick?and?easy type issues. My main concern for posting this is that someone else's work may have already been impacted by this problem, and I want confirmation that this query generation is seen in other instances of i2b2.
Thank you for your time.
Darren W. Henderson
Institute for Pharmaceutical Outcomes and Policy
Center for Clinical and Translational Science Division for Biomedical Informatics University of Kentucky
789 S. Limestone Rm. 182
Lexington, KY 40536
(859) 323?7146 (859) 967?4914


NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky]
Sent:Thursday, January 26, 2012 2:40 PM
To: i2b2 AUG Members
Subject:RE: Flaw in query logic for an exclusion type query
Sorry for the almost immediate self?reply, but in the archive I attached, in the generated SQL for the production environment, please ignore the select statements between each of the updates for the #global_temp_table. These were in there for my own testing purposes, and replace <*> that exist in the generated SQL. My apologies.
Darren W. Henderson
Institute for Pharmaceutical Outcomes and Policy
Center for Clinical and Translational Science Division for Biomedical Informatics University of Kentucky
789 S. Limestone Rm. 182
Lexington, KY 40536
(859) 323?7146 (859) 967?4914



NEXT EMAIL
From: Russ Waitman [rwaitman.kumc]
Sent:Wednesday, January 25, 2012 2:26 PM
To: i2b2 AUG Members; Churchill, Susanne E.
Subject:Re: 2012 AUG Meetings

Hi Susanne,
I'd come back to the i2b2 AUG with at least one other person (Dan, Arvinder, Tamara, or new hire).
I don't think we'll be in SF this year,





Russ Waitman
Associate Professor
Director of Medical Informatics Department of Biostatistics University of Kansas Medical Center
913-945-7087 rwaitman.kumc
http://informatics.kumc.edu

"Churchill, Susanne E." <SCHURCHILL.PARTNERS> 1/25/2012 1:06 PM
Dear Colleagues,
i2b2 will again host a free standing i2b2 AUG Meeting in Boston this summer that will interdigitate with an upfront NLP Workshop and be followed by a SHRINE conference. The AUG meeting will start at noon on Tuesday, July 24 and end at noon on Wednesday, July 25. The NLP Workshop will be held on the morning of the 24th and the SHRINE meeting will start immediately after the AUG meeting (noon on Wednesday, July 25) and run to noon on Thursday, July 26. Please give me a show of hands if you anticipate attending the AUG meeting. There will be no registration fees. Agenda requests welcome.
We'll also host a mini 1.6 workshop at the March AMIA Translational IT meeting in San Francisco - date not yet determined (likely March 19/20/21) but will be an early evening venue as in the past. Given the amount of traffic on the list from you, we're considering transmitting this live via webex (if all goes according to plan) if there is interest in doing so (this may be too late for much of the country). Otherwise, we'll video and post to your web page. I'd like a show of hands for a webex vs video, please.
Thanks, Susanne


NEXT EMAIL
From:Murphy, Shawn N.
Sent:Wednesday, January 25, 2012 8:36 AM
To: Jack W. London, Ph.D.; i2b2 AUG Members
Subject:RE: instance numbers
Hi Jack,
Instance numbers are used to distinguish one instance from another if the following keys are all the same for a group of observations referring to a single instance:
Patient_num Encounter_num Concept_cd Start_date Observer_cd
For example, a medication may have several modifiers that accompanies the dispensing of a medication, such as dose, route, sig, etc. Each modification to the principle concept_id (the medication) will have its own row. These rows are grouped by having a common instance_id. If the same medication is dispensed twice on the same day with all those same conditions, but perhaps at different doses, they should have different instance_nums, and these instance_nums will be used to group their respective modifiers.
So they don't need to be sequential numbers, they just need to be distinct when two observations would otherwise have those same keys.
Thanks, Shawn.

From: Jack W. London, Ph.D. [Jack.London.KimmelCancerCenter] Sent: Tuesday, January 24, 2012 8:04 PM
To: i2b2 AUG Members
Subject: instance numbers
Do instance numbers have to be sequential numbers starting with 1?
Can they be any integer, and have relative values not related to observation start dates?
Jack



NEXT EMAIL
From: Travis Nagler [travis.nagler.ucdmc.ucdavis]
Sent:Tuesday, January 24, 2012 1:35 PM
To:Peter Beninato
Cc: i2b2 AUG Members
Subject:RE: V1.5.5 queryRequest and queryResponse
Thanks, Peter. Yes, Ayan's code is the same code I am talking about. Going through it, the code really should work. So your description of the issue sounds likely.
I'll try and get hold of the Print Query button code from 1.6 and see what that tells me. But even then 1.5 still might not have the capability to do it.
Anyone else have a Print Query-type button used in 1.5? Thanks,
Travis
Travis H. Nagler
Senior Database Programmer
American Burn Assoc. - Data Coordinating Center
UC Davis Division of Burn Surgery
UC Davis Health System
2921 Stockton Blvd., Suite 1470
Sacramento, CA, 95817 tel: (916) 703-9215
fax: (916) 703-9124 http://www.ucdmc.ucdavis.edu
travis.nagler.ucdmc.ucdavis

From:Peter Beninato <beninato.ohsu>
To:Travis Nagler <travis.nagler.ucdmc.ucdavis>, "i2b2 AUG Members" <i2b2 AUG Members> Date:01/23/2012 04:01 PM
Subject:RE: V1.5.5 queryRequest and queryResponse
Hi Travis,
A long time ago, I tried to implement the Davis code by Ayan Patel in v1.4 and could not make it work.
I think there might have been some changes between what was available on the client vs. the server between 1.3 and 1.4. This might also be related to some security/obfuscation characteristics (the server/client split).
I think the issue is that some part of the showXML is not "bound" at the time of the call.
In version 1.6.02 there is a "Print Query" button which I think is similar to the functionality you are after, so you may want to consider upgrading, or at least examining the code.
Peter

From: Travis Nagler [travis.nagler.ucdmc.ucdavis]
Sent: Monday, January 23, 2012 3:51 PM
To: i2b2 AUG Members
Subject: V1.5.5 queryRequest and queryResponse
We created a custom function in our old 1.3 version that parsed the queryRequest and queryResponse variables based on how the i2b2 function function showXML(c,r,t) (located in /js-i2b2/hive/hive_helpers.js) worked so we could show the query dates, constraints, set size, etc. in a JavaScript pop-up window.
In moving the query to 1.5.5, the XML parsing fails when it runs. I still get the pop-up box when I use just text without trying to show any of the parsed xml elements, but once I add in the results using getElementsByTagName calls, it shows a blank screen, not even the regular text. For example,
OpenWindow.document.write('<h2>Result for Query: <\/h2><h4>'); OpenWindow.document.write(xmlString.getElementsByTagName('query_name')[0].firstChild.nodeValue); [write more regular text]...
only returns the text "Result for Query:" but is blank after that, not even other text.
The variable is set with var xmlString = i2b2.h.parseXml(i2b2['CRC'].view['QT']['queryRequest']); Can anyone tell me if this is still a valid call to get this information?
I have looked at the showXML('CRC','QT','Stack') once the query was run, and I have matched up all of the elements between it and our code, so I don't see a problem there. I'm down to it now parsing the XML code or being able to parse it (if the parsing string is pulling the wrong item in 'queryRequest').
Can anyone help me out with this? How can I pull the Request and Response xml data to parse it? Any help would be appreciated.
Thanks, Travis

Travis H. Nagler
Senior Database Programmer
American Burn Assoc. - Data Coordinating Center
UC Davis Division of Burn Surgery
UC Davis Health System
2921 Stockton Blvd., Suite 1470
Sacramento, CA, 95817 tel: (916) 703-9215
fax: (916) 703-9124 http://www.ucdmc.ucdavis.edu
travis.nagler.ucdmc.ucdavis



NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky]
Sent:Tuesday, January 24, 2012 11:10 AM
To: i2b2 AUG Members
Subject:Simple query performance boost realized through metadata changes and closure table.
The following tweak should only be attempted by those that are extremely comfortable altering the contents and structure of their i2b2 databases. There are some caveats I should mention that would assist anyone that chooses to attempt this tweak. In our db we have consolidated any given projects data, metadata and workdata tables into one
db. That is, the metadata table dbo.i2b2 resides in the same db as dbo.concept_dimension and dbo.observation_fact. If yours do not, you will have to modify my code to implicity refer to your db names. Also, please note that this was developed on SQL Server, and although I don't see any issues with using this on Oracle, I will can't say 100% that you would not be required to further tweak any of my code to run this on an oracle db.
While I was developing query generation language for improving queries on large scale datasets, I added this additional tweak to my metadata to squeeze all the seconds I can out of the cost of my queries. This tweak makes use a lesser known tool for dealing with hierarchies known as the closure table. I discovered it in a book by Bill Karwin called SQL Antipatterns, part of the Pragmatic Programmers series. A closure table creates ancestor?descendent relationships and stores all possible anc?desc as a compound primary key, with each member (ancestor and descendent) as foreign key references to a primary key in the hierarchy table. In this case, I added an integer identity field to dbo.i2b2 called meta_id. I built the closure table by using the already existing path?to?root style references in c_dimcode. I used other methodology in my generator to build the join syntax, but I played around with the stock metadata and sql generator to build the appropriate join syntax using the c_tablename field to bear the bulk of the syntax duty. You can take a peek in the attached .sql file to see the update statement that alters the concepts.
The attached only alters metadata that originally referred to the concept_dimension table. The join that gets used in place of the subquery where?like syntax returns children concepts quicker, and the optimizer treats the original "from dbo.observation_fact where concept_cd in <subquery>" code as a join in the background, so performance gains are seen in small to large queries. It's especially noticeable when many concepts are chosen from the available ontologies that are usually stored in concept_dimension.
Again, I really only suggest you attempt to play with this if you are very comfortable in your dbs. Also, if query speed is a non?issue for you, ignore this. This was just one additional thing I did to improve our largest datasets. For smaller datasets, the gains may be negligible. I should also say that this change makes the queries practically unintelligible to a human reading them in error messages or logs as you will only see something similar to:
select count(distinct patient_num) as patient_num_count from (
select patient_num from prod_uki2b2data.dbo.observation_fact where
(( concept_cd IN (select concept_cd from prod_uki2b2data.dbo.concept_dimension c join dbo.i2b2 D on c.concept_cd
= D.c_basecode JOIN DBO.METATREE A ON A.DESCENDENT = D.META_ID where A.ANCESTOR = 458)))
) allitem
I don't know what concept 458 is off the top of my head in our metadata. However, you can make a simple view: Create view i2b2_human as (
Select meta_id, c_name, c_basecode
From dbo.i2b2 )
This would make it quite simple to look up something to confirm concepts were passed correctly.
Darren W. Henderson
Institute for Pharmaceutical Outcomes and Policy Center for Clinical and Translational Science Division for Biomedical Informatics
University of Kentucky
789 S. Limestone Rm. 182
Lexington, KY 40536 (859) 323-7146
(859) 967-4914



NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky]
Sent:Tuesday, January 24, 2012 10:11 AM
To: i2b2 AUG Members
Subject:Exclusion panel bug when dealing with same ecounter query
I'm running into a bug concerning excluding a race from a same encounter type query. First I should state that we made the decision to move race into the patient_dimension table seeing as its not particularly dynamic and it doubles the size of observation_fact to leave it there.When the panel attempts to exclude on race_cd it still tries to select a hard?coded lists of fields (provider_id, start_date, concept_cd, instance_num, encounter_num, patient_num) from patient_dimension, as if it were stored in observation_fact.It would be simple for the code behind to check which dimension table a concept is stored in and alter these select members accordingly.An exclusion on something that does not typically change over time that is stored in patient_dimension should only look for patient_num for example.

update prod_uki2b2data.dbo.#global_temp_table set panel_count = ?1
where prod_uki2b2data.dbo.#global_temp_table.panel_count =3 andexists ( select1 as panel_count
from ( select/*+ index(observation_fact fact_cnpt_pat_enct_idx) */ provider_id, start_date, concept_cd, instance_num, encounter_num,patient_num from prod_uki2b2data.dbo.patient_dimension
wherePATIENT_NUM IN (select PATIENT_NUM from prod_uki2b2data.dbo.PATIENT_DIMENSIONwhere RACE_CD IN ('W'))
group byencounter_num ,instance_num, concept_cd,start_date,provider_id, patient_num) t where prod_uki2b2data.dbo.#global_temp_table.patient_num = t.patient_num)

Darren W. Henderson
Institute for Pharmaceutical Outcomes and Policy
College of Pharmacy
University of Kentucky
789 S. Limestone Rm. 182
Lexington, KY 40536
(859) 323?7146 (859) 967?4914


NEXT EMAIL
From: Jack London [jack.london.KimmelCancerCenter]
Sent:Tuesday, January 24, 2012 10:02 AM
To:Mauro
Cc: i2b2 AUG Members
Subject:Re: ExportXLS plugin modification
Mauro,
We are working on a program to do what you propose: multiple instances of observations for concepts are displayed with the associated dates in parentheses. The code gets somewhat messy because you have to have blank cell entries on a row for a patient's concepts that do not have multiple observations. Because we lack your expertise with i2b2 java code, our software is not a plug-in, but a stand-alone program that accesses the i2b2 database tables. This approach has a major failing in that you cannot use the drag-and-drop of the i2b2 web client to enter the desired concepts. I have someone in our group currently modifying the initial code to use ajax for selecting concepts (expanding concept trees as needed).
What would be best would be for someone to adapt our code for packaging the multiple patient observations (with "start dates") in an i2b2 plugin framework.
Jack

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

On Jan 24, 2012, at 9:48 AM, Mauro wrote:
Hi Jack,
you are referring to a major issue in terms of plugin design.
Of course I can modify the table repeating the patient column for each observation (with the same concept).
But in this way the "single" observation would be repeated too and sometimes may confuse.
My suggestion is reporting in each cell the date/time of the observation.
If you have any ideas to better manage the situation let me know, I would be grateful.
Mauro
Ing. Mauro Bucalo, Ext. Collaborator
Dipartimento di Informatica e Sistemistica
Universita' di Pavia
Via Ferrata 1, 27100 Pavia, Italy
tel:++39.0382.985981
e-mail: mauro.bucalo.gmail
web-page: http://it.linkedin.com/in/maurobucalo


2012/1/20 Jack London <jack.london.kimmelcancercenter>:
Mauro,
Would it be difficult to modify the ExportXLS plugin so that it returned ALL observations for each patient in a patient set for a given set of concepts?
Or, perhaps simpler, ALL observations for a single concept for each patient in a patient set (the id's in the patient column would repeat as needed).
Jack



NEXT EMAIL
From: drharr2.g.uky on behalf of Harris, Daniel R [daniel.harris.uky]
Sent:Monday, January 23, 2012 4:43 PM
To: i2b2 AUG Members
Subject:i2b2 svn repository
Dear AUG,
Is there a SVN repository for 1.6x? I noticed that 1.5x is still on the main page's svn repository
Thanks, Daniel Harris
University of Kentucky
Department of Computer Science and
Division of Biomedical Informatics
182 BioPharm Complex Building
789 South Limestone Street
Lexington, Kentucky 40536-0596


NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky]
Sent: Saturday, January 21, 2012 1:05 PM
To: Dan Connolly; beninato.ohsu
Cc: i2b2 AUG Members
Subject: RE: control over style of GENERATED_SQL?
Dan, I thought I remembered from previous conversations that you were on an oracle db. So don't look for the clustered index, that is sql server only. Instead look for fact_cnpt_pat_enct_idx I believe.
SQL server clustered index is equivalent to Oracle Index organized table. But I don't think the i2b2 data model makes use of this in oracle, but can't be 100% here since we use SQL server for i2b2.

From: Dan Connolly [dconnolly.kumc]
Sent: Friday, January 20, 2012 4:37 PM
To: beninato.ohsu
Cc: i2b2 AUG Members
Subject: RE: control over style of GENERATED_SQL?
Yes, setting this to 0 seems to have the desired effect. Thanks!
My intuition agrees with others who say both styles of queries should work if the indexes are right, so I'd still like to find out more about what's going on.
But for now, we have at least one way to deal with this performance issue.
Dan Connolly, KUMC Medical Informatics
913-945-6741

On Fri, 2012-01-20 at 07:54 -0800, Peter Beninato wrote:
Hi Dan,
Below is thread of email I sent in response to the issues of the query reuse you discovered. It is in crc.properties. There might be other places that need to be tweaked, but it is a start. edu.harvard.i2b2.crc.setfinderquery.skiptemptable.maxconcept=40



NEXT EMAIL
From: Dan Connolly [dconnolly.kumc]
Sent: Friday, January 20, 2012 3:47 PM
To: Nandan.Patibandla.childrens.harvard
Cc: i2b2 AUG Members
Subject: RE: control over style of GENERATED_SQL?

On Fri, 2012-01-20 at 16:53 +0000, Dan Connolly wrote:
On Fri, 2012-01-20 at 16:17 +0000, Patibandla, Nandan wrote: Hi Dan,
Which version of i2b2 are you using, are they the same on both test and prod, it look to me that you are using
1.6 on test and 1.5 on prod.
We have never even downloaded 1.5, but we worked with 1.4 until recently, and we have had some issues with old stuff where we thought we had gotten rid of it.
Just to make sure, is indexing (OF_IDX_ClusteredConcept) done on the test version. Good question; we'll look into it.
No; we don't seem to have OF_IDX_ClusteredConcept in test nor production. That seems to appear only in the sqlserver datamart creation scripts:
$ grep -ir OF_IDX_ClusteredConcept /usr/local/i2b2_1.602/edu.harvard.i2b2.data/
/usr/local/i2b2_1.602/edu.harvard.i2b2.data/Release_1-
6/NewInstall/Crcdata/scripts/crc_create_datamart_sqlserver.sql:CREATE CLUSTERED INDEX OF_IDX_ClusteredConcept ON OBSERVATION_FACT
$ grep -ir OF_IDX_ClusteredConcept /usr/local/i2b2_1.6RC4/edu.harvard.i2b2.data/
/usr/local/i2b2_1.6RC4/edu.harvard.i2b2.data/Release_1-
4/NewInstall/Demodata/scripts/crc_create_datamart_sqlserver.sql:CREATE CLUSTERED INDEX OF_IDX_ClusteredConcept ON Observation_Fact
Dan Connolly, KUMC Medical Informatics
913-945-6741


NEXT EMAIL
From: Dan Connolly [dconnolly.kumc]
Sent: Friday, January 20, 2012 11:53 AM
To: Nandan.Patibandla.childrens.harvard
Cc: i2b2 AUG Members
Subject: RE: control over style of GENERATED_SQL?
On Fri, 2012-01-20 at 16:17 +0000, Patibandla, Nandan wrote: Hi Dan,
Which version of i2b2 are you using, are they the same on both test and prod, it look to me that you are using
1.6 on test and 1.5 on prod.
We have never even downloaded 1.5, but we worked with 1.4 until recently, and we have had some issues with old stuff where we thought we had gotten rid of it.
Just to make sure, is indexing (OF_IDX_ClusteredConcept) done on the test version. Good question; we'll look into it.
Thank you
Nandan

From: Dan Connolly [dconnolly.kumc]
Sent: Friday, January 20, 2012 10:59 AM
To: darren.henderson.uky
Cc: members.i2b2aug
Subject: RE: control over style of GENERATED_SQL?

On Fri, 2012-01-20 at 09:51 -0500, Henderson, Darren W wrote:
Dan, are the test and prod databases one and the same, or separate?
They're separate, but very similar.
Production has data we got from source systems as of November, and test has data we got in December.
Dan Connolly, KUMC Medical Informatics
913-945-6741



NEXT EMAIL
From: Peter Beninato [beninato.ohsu]
Sent:Friday, January 20, 2012 11:53 AM
To: Henderson, Darren W; i2b2 AUG Members
Subject:RE: control over style of GENERATED_SQL?
Might want to check statistics are current too...

From: Henderson, Darren W [darren.henderson.uky]
Sent: Friday, January 20, 2012 8:35 AM
To: i2b2 AUG Members
Subject: RE: control over style of GENERATED_SQL?
Check to ensure your indexes built correctly, and are not fragmented (rebuild). The first query should run just as or faster than the temp tables if indexing is correct.

From: Dan Connolly [dconnolly.kumc]
Sent: Friday, January 20, 2012 10:58 AM
To: Henderson, Darren W
Cc: i2b2 AUG Members
Subject: RE: control over style of GENERATED_SQL?
On Fri, 2012-01-20 at 09:51 -0500, Henderson, Darren W wrote: Dan, are the test and prod databases one and the same, or separate?
They're separate, but very similar.
Production has data we got from source systems as of November, and test has data we got in December.
Dan Connolly, KUMC Medical Informatics
913-945-6741


NEXT EMAIL
From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
Sent:Friday, January 20, 2012 11:17 AM
To:Dan Connolly
Cc: i2b2 AUG Members
Subject:RE: control over style of GENERATED_SQL?

Hi Dan,
Which version of i2b2 are you using, are they the same on both test and prod, it look to me that you are using 1.6 on test and 1.5 on prod.
Just to make sure, is indexing (OF_IDX_ClusteredConcept) done on the test version. Thank you
Nandan
From: Dan Connolly [dconnolly.kumc]
Sent: Friday, January 20, 2012 10:59 AM
To: darren.henderson.uky
Cc: i2b2 AUG Members
Subject: RE: control over style of GENERATED_SQL?
On Fri, 2012-01-20 at 09:51 -0500, Henderson, Darren W wrote:
Dan, are the test and prod databases one and the same, or separate?
They're separate, but very similar.
Production has data we got from source systems as of November, and test has data we got in December.
Dan Connolly, KUMC Medical Informatics
913-945-6741



NEXT EMAIL
From: Peter Beninato [beninato.ohsu]
Sent:Friday, January 20, 2012 10:54 AM
To: Dan Connolly; i2b2 AUG Members
Subject:RE: control over style of GENERATED_SQL?

Hi Dan,
Below is thread of email I sent in response to the issues of the query reuse you discovered. It is in crc.properties. There might be other places that need to be tweaked, but it is a start.
?Peter
Hi,
In src\edu.harvard.i2b2.crc\etc\spring\crc.properties there is a parameter:
###############################################################

  1. CRC setfinder query without using temp table
    ###############################################################
    edu.harvard.i2b2.crc.setfinderquery.skiptemptable.maxconcept=40


    With 8 panels, might there be approximately 40 concepts and if so, maybe there is some issue with the code that works with this temp table?
    Peter

    From: Dan Connolly [dconnolly.kumc]
    Sent: Friday, January 20, 2012 6:37 AM
    To: i2b2 AUG Members
    Subject: control over style of GENERATED_SQL?
    We're struggling with performance differences between two i2b2 installations. Our test installation takes >100 seconds to run "or" queries, i.e. queries with two or more concepts in the same panel; the same query takes ~10 seconds in our production instance.
    We discovered that the GENERATED_SQL in test uses one SQL select count(...) from observation_fact, whereas in production it uses 2 inserts into a temp table and then a select count(...) on the temp table.
    (For some time it seemed odd to me that i2b2 would manually plan the query with a temp table rather than letting Oracle plan the query, but I've seen there performance difference with my own eyes now.)
    I have a vague memory of discussion of configuration options that influence this behavior, but I don't recall details.
    I'd appreciate help in locating the details.
    for reference, the SQL generated in production:
    INSERT INTO BlueHerondata.QUERY_GLOBAL_TEMP (patient_num, panel_count)
    SELECT patient_num, 1 FROM (
    SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_numFROM BlueHerondata.observation_factWHERE (((concept_cd IN (select concept_cd from BlueHerondata.concept_dimension cwhere concept_path like '\i2b2\Demographics\KUMC radius\5mi%')))) group by patient_num having count(star) >=1) t
    <*>
    INSERT INTO BlueHerondata.QUERY_GLOBAL_TEMP (patient_num, panel_count) SELECT patient_num, 1 FROM (
    SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_numFROM BlueHerondata.observation_factWHERE (((concept_cd IN (select concept_cd from BlueHerondata.concept_dimension cwhere concept_path like '\i2b2\Demographics\KUMC radius\10mi%'))))
    group by patient_num having count(star) >=1) t
    <*>
    INSERT INTO BlueHerondata.DX (patient_num) SELECT * FROM (
    SELECT DISTINCT t.patient_num FROM BlueHerondata.QUERY_GLOBAL_TEMP t WHERE panel_count =
    1
    ) q
    <*>

    vs. test:

    select count(distinct patient_num) as patient_num_count from (
    select patient_num from BlueHerondata.observation_fact where
    ((concept_cd IN (select concept_cd from BlueHerondata.concept_dimensionwhere concept_path like '\i2b2\Demographics\KUMC radius\5mi%'))
    OR
    (concept_cd IN (select concept_cd from BlueHerondata.concept_dimensionwhere concept_path like '\i2b2\Demographics\KUMC radius\10mi%')))
    ) allitem

    Dan Connolly, KUMC Medical Informatics
    913-945-6741



    NEXT EMAIL
    From: Henderson, Darren W [darren.henderson.uky]
    Sent:Friday, January 20, 2012 9:52 AM
    To: i2b2 AUG Members
    Subject:RE: control over style of GENERATED_SQL?
    Dan, are the test and prod databases one and the same, or separate?
    Darren W. Henderson
    Institute for Pharmaceutical Outcomes and Policy
    College of Pharmacy
    University of Kentucky
    789 S. Limestone Rm. 182
    Lexington, KY 40536 (859) 323?7146
    (859) 967?4914



    From: Dan Connolly [dconnolly.kumc]
    Sent: Friday, January 20, 2012 9:37 AM
    To: i2b2 AUG Members
    Subject: control over style of GENERATED_SQL?
    We're struggling with performance differences between two i2b2 installations. Our test installation takes >100 seconds to run "or" queries, i.e. queries with two or more concepts in the same panel; the same query takes ~10 seconds in our production instance.
    We discovered that the GENERATED_SQL in test uses one SQL select count(...) from observation_fact, whereas in production it uses 2 inserts into a temp table and then a select count(...) on the temp table.
    (For some time it seemed odd to me that i2b2 would manually plan the query with a temp table rather than letting Oracle plan the query, but I've seen there performance difference with my own eyes now.)
    I have a vague memory of discussion of configuration options that influence this behavior, but I don't recall details.
    I'd appreciate help in locating the details.
    for reference, the SQL generated in production:
    INSERT INTO BlueHerondata.QUERY_GLOBAL_TEMP (patient_num, panel_count)
    SELECT patient_num, 1 FROM (
    SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_numFROM
    BlueHerondata.observation_factWHERE (((concept_cd IN (select concept_cd from
    BlueHerondata.concept_dimension cwhere concept_path like '\i2b2\Demographics\KUMC
    radius\5mi%')))) group by patient_num having count(star) >=1) t
    <*>
    INSERT INTO BlueHerondata.QUERY_GLOBAL_TEMP (patient_num, panel_count) SELECT patient_num, 1 FROM (
    SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_numFROM BlueHerondata.observation_factWHERE (((concept_cd IN (select concept_cd from BlueHerondata.concept_dimension cwhere concept_path like '\i2b2\Demographics\KUMC radius\10mi%'))))
    group by patient_num having count(star) >=1) t
    <*>
    INSERT INTO BlueHerondata.DX (patient_num) SELECT * FROM (
    SELECT DISTINCT t.patient_num FROM BlueHerondata.QUERY_GLOBAL_TEMP t WHERE panel_count =
    1
    ) q
    <*>

    vs. test:

    select count(distinct patient_num) as patient_num_count from (
    select patient_num from BlueHerondata.observation_fact where
    ((concept_cd IN (select concept_cd from BlueHerondata.concept_dimensionwhere
    concept_path like '\i2b2\Demographics\KUMC radius\5mi%')) OR
    (concept_cd IN (select concept_cd from BlueHerondata.concept_dimensionwhere concept_path like '\i2b2\Demographics\KUMC radius\10mi%')))
    ) allitem

    Dan Connolly, KUMC Medical Informatics
    913-945-6741



    NEXT EMAIL
    From:Mendis, Michael E.
    Sent:Thursday, January 19, 2012 7:48 PM
    To: Peter Beninato; i2b2 AUG Members
    Subject:RE: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
    great thanks, will examine in more detail.
    has been a bug I been wanting to resolve big time. thanks
    mike

    From: Peter Beninato [beninato.ohsu.edu] Sent: Thu 1/19/2012 6:27 PM
    To: Peter Beninato; Mendis, Michael E.; i2b2 AUG Members
    Subject: RE: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
    Well, it happens with only counts too. This is with a Firefox browser.

    [ From: Peter Beninato [beninato.ohsu|mailto:beninato@ohsu.edu].edu]
    Sent: Thursday, January 19, 2012 3:23 PM To: Mike Mendis; i2b2 AUG Members
    Subject: RE: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
    Hi Mike,

    Attachments are email system problem so Query Status output and xml are in email body.
    This also manifests on the "Print Query". The number of repetitions also varies and it is hard to detect a pattern.
    The way I have seen it reliable reproduced is to run the query for patient set
    and count, and then run the same query again for patient_set, patient count and breakdown.
    PLEASE NOTE
    I think it might be related to the patient_set (or maybe paging of patient set in terms of repeats). If I just run for count and breakdown, I don't typically see repetition.
    QUERY STATUS - Begin
    Finished Query: "Age in -BMI [n~@15:15:56" [8.7 secs]
    Compute Time: 6 secs
    Patient Set for "Age in -BMI [n~@15:15:56"
    Gender patient breakdown for "Age in -BMI [n~@15:15:56" Female: 590
    Male: 536
    Unknown: 0
    Gender patient breakdown for "Age in -BMI [n~@15:15:56"
    Female: 590
    Male: 536
    Unknown: 0
    Number of patients for "Age in -BMI [n~@15:15:56" patient_count: 1126
    Gender patient breakdown for "Age in -BMI [n~@15:15:56" Female: 590
    Male: 536
    Unknown: 0
    Number of patients for "Age in -BMI [n~@15:15:56" patient_count: 1126
    Gender patient breakdown for "Age in -BMI [n~@15:15:56" Female: 590
    Male: 536
    Unknown: 0
    Gender patient breakdown for "Age in -BMI [n~@15:15:56" Female: 590
    Male: 536
    Unknown: 0
    Number of patients for "Age in -BMI [n~@15:15:56" patient_count: 1126
    Gender patient breakdown for "Age in -BMI [n~@15:15:56" Female: 590
    Male: 536
    Unknown: 0
    Number of patients for "Age in -BMI [n~@15:15:56" patient_count: 1126
    Number of patients for "Age in -BMI [n~@15:15:56" patient_count: 1126
    Number of patients for "Age in -BMI [n~@15:15:56" patient_count: 1126
    Number of patients for "Age in -BMI [n~@15:15:56"
    patient_count: 1126
    Gender patient breakdown for "Age in -BMI [n~@15:15:56" Female: 590
    Male: 536
    Unknown: 0
    Number of patients for "Age in -BMI [n~@15:15:56" patient_count: 1126
    Gender patient breakdown for "Age in -BMI [n~@15:15:56" Female: 590
    Male: 536
    Unknown: 0

    • #* #* XML - Begin ****
      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <ns6:request xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/cell/ont/1.1/"
      xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/pdo/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/hive/plugin/" xmlns:ns2="http://www.i2b2.org/xsd/hive/pdo/1.1/" xmlns:ns6="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns8="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/">
      <message_header>
      <proxy>
      <redirect_url>http://octrii2.ohsu.edu:9090/i2b2/rest/QueryToolService/request</r edirect_url>
      </proxy>
      <sending_application>
      <application_name>i2b2_QueryTool</application_name>
      <application_version>1.6</application_version>
      </sending_application>
      <sending_facility>
      <facility_name>PHS</facility_name>
      </sending_facility>
      <receiving_application>
      <application_name>i2b2_DataRepositoryCell</application_name>
      <application_version>1.6</application_version>
      </receiving_application>
      <receiving_facility>
      <facility_name>PHS</facility_name>
      </receiving_facility>
      <security>
      <domain>i2b2demo</domain>
      <username>beninato</username>
      <password token_ms_timeout="1800000" is_token="true">SessionKey:So2AEllrQZ6SQWiOJfyS</password>
      </security>
      <message_type>
      <message_code>Q04</message_code>
      <event_type>EQQ</event_type>
      </message_type>
      <message_control_id>
      <message_num>35YBLRdPgA912Ep9KiZsj</message_num>
      <instance_num>0</instance_num>
      </message_control_id>
      <processing_id>
      <processing_id>P</processing_id>
      <processing_mode>I</processing_mode>
      </processing_id>
      <accept_acknowledgement_type>messageId</accept_acknowledgement_type>
      <project_id>ohsu</project_id>
      </message_header>
      <request_header>
      <result_waittime_ms>180000</result_waittime_ms>
      </request_header>
      <message_body>
      <ns4:psmheader>
      <user group="ohsu" login="beninato">beninato</user>
      <patient_set_limit>0</patient_set_limit>
      <estimated_time>0</estimated_time>
      <query_mode>optimize_without_temp_table</query_mode>
      <request_type>CRC_QRY_runQueryInstance_fromQueryDefinition</request_type>
      </ns4:psmheader>
      <ns4:request xsi:type="ns4:query_definition_requestType" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <query_definition>
      <query_name>Age in -BMI [n~@15:15:56</query_name>
      <query_timing>ANY</query_timing>
      <specificity_scale>0</specificity_scale>
      <panel>
      <panel_number>1</panel_number>
      <panel_accuracy_scale>0</panel_accuracy_scale>
      <invert>0</invert>
      <panel_timing>ANY</panel_timing>
      <total_item_occurrences>1</total_item_occurrences>
      <item>
      <hlevel>2</hlevel>
      <item_name>Age in Years (Current) [n~862,026] 12 - 16 years</item_name>
      <item_key>\\i2b2\i2b2\Demographics\Age\</item_key>
      <tooltip>Demographic \ Age in Years (Current)</tooltip>
      <class>ENC</class>
      <item_icon>FA</item_icon>
      <item_is_synonym>false</item_is_synonym>
      <constrain_by_value>
      <value_type>NUMBER</value_type>
      <value_unit_of_measure>years</value_unit_of_measure>
      <value_operator>BETWEEN</value_operator>
      <value_constraint>12 and 16</value_constraint>
      </constrain_by_value>
      </item>
      </panel>
      <panel>
      <panel_number>2</panel_number>
      <panel_accuracy_scale>0</panel_accuracy_scale>
      <invert>0</invert>
      <panel_timing>ANY</panel_timing>
      <total_item_occurrences>1</total_item_occurrences>
      <item>
      <hlevel>2</hlevel>
      <item_name>BMI [n~151,804] > 30 US</item_name>
      <item_key>\\i2b2\i2b2\Vitals\BMI\</item_key>
      <tooltip>Vitals \ BMI \ </tooltip>
      <class>ENC</class>
      <item_icon>FA</item_icon>
      <item_is_synonym>false</item_is_synonym>
      <constrain_by_value>
      <value_type>NUMBER</value_type>
      <value_unit_of_measure>US</value_unit_of_measure>
      <value_operator>GT</value_operator>
      <value_constraint>30</value_constraint>
      </constrain_by_value>
      </item>
      </panel>
      </query_definition>
      <result_output_list><result_output priority_index="11" name="patientset"/>
      <result_output priority_index="12" name="patient_count_xml"/>
      <result_output priority_index="13" name="patient_gender_count_xml"/>
      </result_output_list>
      </ns4:request>
      </message_body>
      </ns6:request>
      <ns5:response xmlns:ns2="http://www.i2b2.org/xsd/hive/pdo/1.1/" xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/1.1/"
      xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/pdo/1.1/"[ xmlns:tns="h|http://axis2.crc.i2b2.harvard.edu/]ttp://axis2.crc.i2b2.harvard.edu" xmlns:ns9="http://www.i2b2.org/xsd/cell/ont/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns6="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/cell/crc/psm/analysisdefinition/1.1/"
      xmlns:ns10="http://www.i2b2.org/xsd/hive/msg/result/1.1/" xmlns:ns8="http://www.i2b2.org/xsd/cell/pm/1.1/">
      <message_header>
      <i2b2_version_compatible>1.1</i2b2_version_compatible>
      <hl7_version_compatible>2.4</hl7_version_compatible>
      <sending_application>
      <application_name>CRC Cell</application_name>
      <application_version>1.601</application_version>
      </sending_application>
      <sending_facility>
      <facility_name>i2b2 Hive</facility_name>
      </sending_facility>
      <receiving_application>
      <application_name>i2b2_QueryTool</application_name>
      <application_version>1.6</application_version>
      </receiving_application>
      <receiving_facility>
      <facility_name>i2b2 Hive</facility_name>
      </receiving_facility>
      <message_control_id>
      <instance_num>1</instance_num>
      </message_control_id>
      <project_id>ohsu</project_id>
      </message_header>
      <response_header>
      <info>Log information</info>
      <result_status>
      <status type="DONE">DONE</status>
      <polling_url interval_ms="100" />
      </result_status>
      </response_header>
      <message_body>
      <ns4:response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns4:master_instance_result_responseType">
      <status>
      <condition type="DONE">DONE</condition>
      </status>
      <query_master>
      <query_master_id>350</query_master_id>
      <name>Age in -BMI [n~@15:15:56</name>
      <user_id>beninato</user_id>
      <group_id>ohsu</group_id>
      <create_date>2012-01-19T15:05:57.000-08:00</create_date>
      <request_xml><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <ns3:query_definition xmlns:ns2="http://www.i2b2.org/xsd/cell/crc/psm/1.1/" xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/analysisdefinition/1.1/"
      xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/">
      <query_name>Age in -BMI [n~@15:15:56</query_name>
      <query_timing>ANY</query_timing>
      <specificity_scale>0</specificity_scale>
      <panel>
      <panel_number>1</panel_number>
      <panel_timing>ANY</panel_timing>
      <panel_accuracy_scale>0</panel_accuracy_scale>
      <invert>0</invert>
      <total_item_occurrences>1</total_item_occurrences>
      <item>
      <hlevel>2</hlevel>
      <item_name>Age in Years (Current) [n~862,026] 12 - 16 years</item_name>
      <item_key>\\i2b2\i2b2\Demographics\Age\</item_key>
      <item_icon>FA</item_icon>
      <tooltip>Demographic \ Age in Years (Current)</tooltip>
      <class>ENC</class>
      <constrain_by_value>
      <value_operator>BETWEEN</value_operator>
      <value_constraint>12 and 16</value_constraint>
      <value_unit_of_measure>years</value_unit_of_measure>
      <value_type>NUMBER</value_type>
      </constrain_by_value>
      <item_is_synonym>false</item_is_synonym>
      </item>
      </panel>
      <panel>
      <panel_number>2</panel_number>
      <panel_timing>ANY</panel_timing>
      <panel_accuracy_scale>0</panel_accuracy_scale>
      <invert>0</invert>
      <total_item_occurrences>1</total_item_occurrences>
      <item>
      <hlevel>2</hlevel>
      <item_name>BMI [n~151,804] > 30 US</item_name>
      <item_key>\\i2b2\i2b2\Vitals\BMI\</item_key>
      <item_icon>FA</item_icon>
      <tooltip>Vitals \ BMI \ </tooltip>
      <class>ENC</class>
      <constrain_by_value>
      <value_operator>GT</value_operator>
      <value_constraint>30</value_constraint>
      <value_unit_of_measure>US</value_unit_of_measure>
      <value_type>NUMBER</value_type>
      </constrain_by_value>
      <item_is_synonym>false</item_is_synonym>
      </item>
      </panel>
      </ns3:query_definition>
      </request_xml>
      </query_master>
      <query_instance>
      <query_instance_id>350</query_instance_id>
      <query_master_id>350</query_master_id>
      <user_id>beninato</user_id>
      <group_id>ohsu</group_id>
      <start_date>2012-01-19T15:05:57.000-08:00</start_date>
      <end_date>2012-01-19T15:06:03.000-08:00</end_date>
      <query_status_type>
      <status_type_id>6</status_type_id>
      <name>COMPLETED</name>
      <description>COMPLETED</description>
      </query_status_type>
      </query_instance>
      <query_result_instance>
      <result_instance_id>492</result_instance_id>
      <query_instance_id>350</query_instance_id>
      <description>Patient Set for "Age in -BMI [n~@15:15:56"</description>
      <query_result_type>
      <result_type_id>1</result_type_id>
      <name>PATIENTSET</name>
      <display_type>LIST</display_type>
      <visual_attribute_type>LA</visual_attribute_type>
      <description>Patient set</description>
      </query_result_type>
      <set_size>1126</set_size>
      <start_date>2012-01-19T15:05:57.000-08:00</start_date>
      <end_date>2012-01-19T15:06:01.000-08:00</end_date>
      <query_status_type>
      <status_type_id>3</status_type_id>
      <name>FINISHED</name>
      <description>FINISHED</description>
      </query_status_type>
      </query_result_instance>
      <query_result_instance>
      <result_instance_id>493</result_instance_id>
      <query_instance_id>350</query_instance_id>
      <description>Number of patients for "Age in -BMI [n~@15:15:56"</description>
      <query_result_type>
      <result_type_id>4</result_type_id>
      <name>PATIENT_COUNT_XML</name>
      <display_type>CATNUM</display_type>
      <visual_attribute_type>LA</visual_attribute_type>
      <description>Number of patients</description>
      </query_result_type>
      <set_size>1126</set_size>
      <start_date>2012-01-19T15:05:57.000-08:00</start_date>
      <end_date>2012-01-19T15:06:01.000-08:00</end_date>
      <query_status_type>
      <status_type_id>3</status_type_id>
      <name>FINISHED</name>
      <description>FINISHED</description>
      </query_status_type>
      </query_result_instance>
      <query_result_instance>
      <result_instance_id>494</result_instance_id>
      <query_instance_id>350</query_instance_id>
      <description>Gender patient breakdown for "Age in -BMI [n~@15:15:56"</description>
      <query_result_type>
      <result_type_id>5</result_type_id>
      <name>PATIENT_GENDER_COUNT_XML</name>
      <display_type>CATNUM</display_type>
      <visual_attribute_type>LA</visual_attribute_type>
      <description>Gender patient breakdown</description>
      </query_result_type>
      <set_size>1126</set_size>
      <start_date>2012-01-19T15:05:57.000-08:00</start_date>
      <end_date>2012-01-19T15:06:02.000-08:00</end_date>
      <query_status_type>
      <status_type_id>3</status_type_id>
      <name>FINISHED</name>
      <description>FINISHED</description>
      </query_status_type>
      </query_result_instance>
      </ns4:response>
      </message_body>
      </ns5:response>

      From: Mike Mendis [mmendis.partners] Sent: Thursday, January 19, 2012 11:05 AM
      To: Peter Beninato; i2b2 AUG Members
      Subject: Re: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
      Great sounds like thing for the most part are working. For the multiple query status repeating. I am trying to fix this, but having issues reproducing it. When it occurs, can you send me the xml for the response, wondering if what comes back might be causing it. Also any ideas of how to reproduce it, if you know.
      Thanks mike

      On 1/19/12 2:00 PM, "Peter Beninato" <beninato.ohsu> wrote: Hi,
      (FYI:: The sets of breakdowns seem to be repeating in the Query Status window.)

      Following Mike's suggestion I ran the following update on the qt_breakdown_path
      (below).
      The value had been set to
      i2b2_Demo <file:///\\i2b2_Demo> , and the update changes it to
      i2b2 <file:///\\i2b2> which matches are production ontology.
      I also needed to stop/start JBOSS.
      My Age breakdown still throws an error. But we implemented a query-by-value, and left it at the folder level.
      I will try an experiment and add a leaf. If that still errors out, then my guess
      is that the breakdowns work best with discrete values, and not a query-by-value scenario.
      Thanks.
      Peter

      *************************************************
      This script updates the install data. It replaces
      i2b2_Demo with i2b2 in the first tuple of the
      value to match production system
      *************************************************
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Gender\' where name = 'PATIENT_GENDER_COUNT_XML'
      ;
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Race\' where name = 'PATIENT_RACE_COUNT_XML'
      ;
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Vital Status\' where name = 'PATIENT_VITALSTATUS_COUNT_XML'
      ;
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Age\' where name = 'PATIENT_AGE_COUNT_XML'
      ;
      commit;

      From: Mike Mendis [mmendis.partners] Sent: Thursday, January 19, 2012 9:31 AM
      To: Peter Beninato; i2b2 AUG Members
      Subject: Re: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
      Peter,
      What does the qt_breakdown_path look like, I am looking for errors maybe in the concept path.
      mike

      On 1/19/12 12:06 PM, "Peter Beninato" <beninato.ohsu> wrote: Hi,
      I sent this out on Tuesday with the runQueryInstance_fromQueryDefinition.xml. I'm resending as I didn't get the bounce back email from the DL, and have come
      to learn that many emails with attachments get filtered, so I'm not sure this
      even made it out.
      If I run a query and request any of the breakdowns, the query throws an Error. I get an error like.:
      </request_xml>
      </query_master>
      <query_instance>
      <query_instance_id>290</query_instance_id>
      <query_master_id>290</query_master_id>
      <user_id>beninato</user_id>
      <group_id>ohsu</group_id>
      <start_date>2012-01-17T10:10:34.000-08:00</start_date>
      <end_date>2012-01-17T10:10:53.000-08:00</end_date>
      <message>edu.harvard.i2b2.common.exception.I2B2DAOException: QueryResultPatientSetGenerator.generateResult:Error when getting children from ontology [Database error]
      at edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator.generateResult(Unknown
      Source)
      .when any of the patient breakdowns are being utilized. The error refers to the ontology?
      Are there some new columns in the ontology i2b2 table that need to be filled in?
      There was not much more information in the server.log.
      Is anyone else having problems with the patient breakdowns? Is it a role issue, the user has de-identified privledges? Thanks.
      Peter Beninato - OCTRI DW Developer
      Oregon Health & Science University
      503-494-9985
      beninato.ohsu <beninato.ohsu.edu>




      NEXT EMAIL
      From:Murphy, Shawn N.
      Sent:Thursday, January 19, 2012 5:03 PM
      To: Peter Beninato; Mendis, Michael E.; i2b2 AUG Members
      Subject:RE: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
      Hi Peter,
      Yes, the way that the breakdown query works is that it does a query for each of the children of a designated parent which is assigned to a breakdown topic.
      However, I think you could get a query_by_value to work by making the SQL and putting it into ontology table next to an item. If I understand your tree right, the age would indeed need children with various age brackets, but could be whatever you want (see the way the SQL is embedded in the demo project age's children).
      If you wait a few days, you can even make these hidden and it will work, but need to wait for 1.6.03 for that because right now in 1.6.02 you will get an error if you use a hidden item in a query.
      Thanks, Shawn.

      From: Peter Beninato [beninato.ohsu]
      Sent: Thursday, January 19, 2012 3:52 PM
      To: Peter Beninato; Mendis, Michael E.; i2b2 AUG Members
      Subject: RE: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
      Hi,
      So the Age breakdown error was related to the fact that there was no leaf (C_VISUALATTRIBUTES = LA) in the i2b2 table for Demographics Age.
      But, the relevant value is in nval_num in the observation_fact, so the patient breakdowns for Query-by-value is problematic.
      With the query-by_value approach, only one number is reported, the count of all patients. I think the current code probably looks at c_name and c_basecode/concept_cd.
      To support query_by_value, I think it would be necessary to consider whether the c_metadataxml field is null, or not. If not, then, query_by_value, and more parsing needs to be done, if numeric or character value.
      Peter

      From: Peter Beninato [beninato.ohsu]
      Sent: Thursday, January 19, 2012 11:00 AM
      To: Mike Mendis; i2b2 AUG Members
      Subject: RE: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
      Hi,
      (FYI:: The sets of breakdowns seem to be repeating in the Query Status window.)
      Following Mike's suggestion I ran the following update on the qt_breakdown_path (below).
      The value had been set to
      i2b2_Demo, and the update changes it to
      i2b2 which matches are production ontology. I also needed to stop/start JBOSS.
      My Age breakdown still throws an error. But we implemented a query?by?value, and left it at the folder level.
      I will try an experiment and add a leaf. If that still errors out, then my guess is that the breakdowns work best with discrete values, and not a query?by?value scenario.
      Thanks.
      Peter


      *************************************************
      This script updates the install data. It replaces
      i2b2_Demo with i2b2 in the first tuple of the
      value to match production system
      *************************************************
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Gender\' where name = 'PATIENT_GENDER_COUNT_XML'
      ;
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Race\' where name = 'PATIENT_RACE_COUNT_XML'
      ;
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Vital Status\' where name = 'PATIENT_VITALSTATUS_COUNT_XML'
      ;
      update qt_breakdown_path
      set VALUE = '\\i2b2\i2b2\Demographics\Age\' where name = 'PATIENT_AGE_COUNT_XML'
      ;
      commit;

      From: Mike Mendis [mmendis.partners]
      Sent: Thursday, January 19, 2012 9:31 AM
      To: Peter Beninato; i2b2 AUG Members
      Subject: Re: i2b2 - v1.6.02 - Error - patient breakdowns on Query Runs
      Peter,
      What does the qt_breakdown_path look like, I am looking for errors maybe in the concept path.
      mike

      On 1/19/12 12:06 PM, "Peter Beninato" <beninato.ohsu> wrote:
      Hi,
      I sent this out on Tuesday with the runQueryInstance_fromQueryDefinition.xml.
      I'm resending as I didn't get the bounce back email from the DL, and have come to learn that many emails with attachments get filtered, so I'm not sure this even made it out.
      If I run a query and request any of the breakdowns, the query throws an Error. I get an error like...:
      </request_xml>
      </query_master>
      <query_instance>
      <query_instance_id>290</query_instance_id>
      <query_master_id>290</query_master_id>
      <user_id>beninato</user_id>
      <group_id>ohsu</group_id>
      <start_date>2012-01-17T10:10:34.000-08:00</start_date>
      <end_date>2012-01-17T10:10:53.000-08:00</end_date>
      <message>edu.harvard.i2b2.common.exception.I2B2DAOException: QueryResultPatientSetGenerator.generateResult:Error when getting children from ontology [Database error]
      at edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator.generateResult(Unknown
      Source)
      ...when any of the patient breakdowns are being utilized. The error refers to the ontology?
      Are there some new columns in the ontology i2b2 table that need to be filled in? There was not much more information in the server.log.
      Is anyone else having problems with the patient breakdowns? Is it a role issue, the user has de?identified privledges?
      Thanks.
      Peter Beninato - OCTRI DW Developer
      Oregon Health & Science University
      503-494-9985
      beninato.ohsu <beninato.ohsu.edu>



      NEXT EMAIL
      From: Henderson, Darren W [darren.henderson.uky]
      Sent:Thursday, January 19, 2012 4:48 PM
      To: Lisa Miao; i2b2 AUG Members
      Subject:RE: can we resize "Query Status" window on the web client?
      You can hit print query in the query tool part of the UI and the query status window will pop out into another window. This can be resized like any browser window.
      One bug that we've noticed to exist: if you reload query results from the previous query window, hitting print query will not properly populate the tables that are drawn in this new window, nor will any of your results be sent to this window. But if you rerun the query (if not too time consuming) you can hit print again and get the big view of your results.

      Darren W. Henderson
      Institute for Pharmaceutical Outcomes and Policy
      College of Pharmacy
      University of Kentucky
      789 S. Limestone Rm. 182
      Lexington, KY 40536 (859) 323?7146
      (859) 967?4914

      From: Lisa Miao [lmiao.regenstrief]
      Sent: Thursday, January 19, 2012 4:18 PM
      To: i2b2 AUG Members
      Subject: can we resize "Query Status" window on the web client?
      Does anyone know if we can resize the "Query Status" window (lower right corner) on the web client? As far as I know, after we run a query, this is the window that displays the results of the age breakdown, gender breakdown, etc. The current window is quite small to see everything.
      Thanks,
      Lisa.



      NEXT EMAIL
      From:Mendis, Michael E.
      Sent:Thursday, January 19, 2012 4:21 PM
      To: Lisa Miao; i2b2 AUG Members
      Subject:RE: can we resize "Query Status" window on the web client?
      Lisa,
      Sorry in the current version, you can't resize that panel.
      mike

      Original Message
      From: Lisa Miao [lmiao.regenstrief] Sent: Thu 1/19/2012 4:18 PM
      To: i2b2 AUG Members
      Subject: can we resize "Query Status" window on the web client?
      Does anyone know if we can resize the "Query Status" window (lower right corner) on the web client? As far as I know, after we run a query, this is the window that displays the results of the age breakdown, gender breakdown, etc. The current window is quite small to see everything.
      Thanks, Lisa.



      NEXT EMAIL
      From: Schulte, Gregory [Gregory.Schulte.childrenscolorado]
      Sent:Thursday, January 19, 2012 12:00 PM
      To: i2b2 AUG Members
      Subject:Age at visit < 1
      Attachments:image001.jpg

      Not sure if this was mentioned or not....
      We are currently reworking the "Age at visit" concepts to include granular age concepts for patients under 2 and noticed an error when running queries against the current concept of "Visit at age less than 1 year old".
      The error is cause by the C_OPERATOR being set to "<" when it should be "BETWEEN" in the i2b2_metadata.i2b2 table for C_NAME of "Visit at age less than 1 year old" ( populated by i2b2_metadata_visitDetails_insert_data.sql script ) or you could change the C_DIMCODE .
      Here's the C_OPERATOR and C_DIMCODE For "Visit at age less than 1 year old":
      '>' ,' ((select birth_date from i2b2demodata.dbo.patient_dimension where patient_num = i2b2demodata.dbo.visit_dimension.patient_num) ?1) AND ((select birth_date from i2b2demodata.dbo.patient_dimension where patient_num = i2b2demodata.dbo.visit_dimension.patient_num) + (365.25 * 1)?1)'
      Thanks,
      Greg Schulte | Clinical Research Data Warehouse Database Analyst
      13123 East 16th Avenue, Box 485 | Aurora, CO 80045
      Phone: 720.777.5740 | Gregory.Schulte.childrenscolorado



      NEXT EMAIL
      From: Yves Thorrez [Yves.Thorrez.uzbrussel.be]
      Sent:Thursday, January 19, 2012 10:11 AM
      To: Mendis, Michael E.; i2b2 AUG Members Subject:RE: i2b2 v1.6.02 - query issue

      There is this one:
      org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update i2b2demodata.dbo.qt_query_master set delete_flag =?,delete_date=? where query_master_id = ? and delete_flag = ?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'false' to data type int.
      com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'false' to data type int.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
      at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
      at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:699)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:477) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:693) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:752) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:760)
      at edu.harvard.i2b2.crc.dao.setfinder.QueryMasterSpringDao.deleteQuery(Unknown Source)
      at edu.harvard.i2b2.crc.ejb.QueryInfoBean.deleteQueryMaster(Unknown Source)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:601)
      at org.jboss.invocation.Invocation.performCall(Invocation.java:359)
      at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:158)
      at
      org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:169)
      at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:350)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:181)
      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:168)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)
      at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
      at org.jboss.ejb.Container.invoke(Container.java:960)
      at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:430)
      at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)
      at $Proxy164.deleteQueryMaster(Unknown Source)
      at edu.harvard.i2b2.crc.delegate.setfinder.DeleteQueryMasterHandler.execute(Unknown Source)
      at edu.harvard.i2b2.crc.delegate.setfinder.QueryRequestDelegate.handleRequest(Unknown Source)
      at edu.harvard.i2b2.crc.axis2.QueryService.handleRequest(Unknown Source)
      at edu.harvard.i2b2.crc.axis2.QueryService.request(Unknown Source)
      at sun.reflect.GeneratedMethodAccessor114.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:601)
      at org.apache.axis2.receivers.RawXMLINOutMessageReceiver.invokeBusinessLogic(RawXMLINOutMessageReceiver.java:88
      )
      at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.receive(AbstractInOutSyncMessageReceiver.java:39)
      at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:493)
      at org.apache.axis2.transport.http.util.RESTUtil.invokeAxisEngine(RESTUtil.java:153)
      at org.apache.axis2.transport.http.util.RESTUtil.processPostRequest(RESTUtil.java:102)
      at org.apache.axis2.transport.http.AxisRESTServlet.doPost(AxisRESTServlet.java:66)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
      at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
      at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
      at java.lang.Thread.run(Thread.java:722)


      From: Mike Mendis [mmendis.PARTNERS.ORG]
      Sent: donderdag 19 januari 2012 16:07
      To: Yves Thorrez; i2b2 AUG Members
      Subject: Re: i2b2 v1.6.02 - query issue
      Are there any other errors reported in the jboss log (/opt/jboss/server/default/log/server.log)?
      mike

      On 1/19/12 10:02 AM, "Yves Thorrez" <Yves.Thorrez.uzbrussel.be> wrote:
      It seems that the error I reported (see stack trace at bottom) occurs in my installation when one of the panels uses a modifier, and temporal constraint 'independent' is selected...

      From: Yves Thorrez
      Sent: donderdag 19 januari 2012 15:36
      To: 'Mike Mendis'; i2b2 AUG Members
      Subject: RE: i2b2 v1.6.02 ? query issue
      1.6.01 was a fresh install of both the Hive cells and the data.
      On another note: I've noticed a number of occasions where the query has timed out but its results are available from Previous Queries.

      From: Mike Mendis [mmendis.PARTNERS]
      Sent: donderdag 19 januari 2012 15:30
      To: Yves Thorrez; i2b2 AUG Members
      Subject: Re: i2b2 v1.6.02 ? query issue
      Was this a fresh install of 1.6.01 or was that a upgrade from a earlier version?

      On 1/19/12 8:51 AM, "Yves Thorrez" <Yves.Thorrez.uzbrussel.be> wrote:
      We're on SQL Server 2008. I didn't reload the database, just the middle tier.
      Yves

      From: Mike Mendis [mmendis.PARTNERS]
      Sent: donderdag 19 januari 2012 14:49
      To: Yves Thorrez; i2b2 AUG Members
      Subject: Re: i2b2 v1.6.02 ? query issue
      What database is this on? When you did a new install, was this also for the database?

      On 1/19/12 8:45 AM, "Yves Thorrez" <Yves.Thorrez.uzbrussel.be> wrote:
      Hi Mike,
      We had v1.6.01, and did a new installation (not an upgrade) of v1.6.02 on the same JBoss instance. Recreating the query from the ontology showed the same symptoms.
      Yves

      From: Mike Mendis [mmendis.PARTNERS]
      Sent: donderdag 19 januari 2012 14:31
      To: Yves Thorrez; i2b2 AUG Members
      Subject: Re: i2b2 v1.6.02 ? query issue
      Was this a upgrade from a earlier version, if so which? Also if you recreate the query from the ontology does it run correctly?
      mike

      On 1/19/12 7:52 AM, "Yves Thorrez" <Yves.Thorrez.uzbrussel.be> wrote: Hi,
      Using i2b2 1.6.02, we have a query that appears to fail ("the cell's status message could not be understood"). The error message from the server log (below): "Query not found with masterid =[469]"
      The query_master_id of the generated query is 474 (having rerun it a number of times), and this is consistent between
      QT_Query_Result_Instance and QT_Query_Master.
      It looks like the sequence counter for the query_master_id is stuck at 469, since every time the same query is run, the value of 469 is reported.
      When the "failed" query is dragged to the Query Tool, the correct patient set size is shown in Query Status (as seen in
      QT_Query_Result_Instance).
      Other strange behavior: when a new query, say 'query 2', runs successfully and subsequently the previously failed 'query 1' is dragged to the query tool and (even though the correct result from the previous run is seen) and runs again, 'query 2' in Previous Queries is substituted for 'query 1' (so 'query 2' is no longer seen). Any idea what might cause this?
      2012?01?19 09:40:03,145 ERROR [STDERR] edu.harvard.i2b2.common.exception.I2B2DAOException: Query not found
      with masterid =[469]
      2012?01?19 09:40:03,145 ERROR [STDERR]at edu.harvard.i2b2.crc.dao.setfinder.QueryMasterSpringDao.deleteQuery(Unknown Source)
      2012?01?19 09:40:03,145 ERROR [STDERR]at edu.harvard.i2b2.crc.ejb.QueryInfoBean.deleteQueryMaster(Unknown Source)
      2012?01?19 09:40:03,145 ERROR [STDERR]at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2012?01?19 09:40:03,145 ERROR [STDERR]at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      2012?01?19 09:40:03,145 ERROR [STDERR]at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      2012?01?19 09:40:03,145 ERROR [STDERR]at java.lang.reflect.Method.invoke(Method.java:601)
      2012?01?19 09:40:03,145 ERROR [STDERR]at org.jboss.invocation.Invocation.performCall(Invocation.java:359)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:158)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:169)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)
      org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:350)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:181)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:168)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.Container.invoke(Container.java:960)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:430)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)
      2012?01?19 09:40:03,161 ERROR [STDERR]at $Proxy164.deleteQueryMaster(Unknown Source)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      edu.harvard.i2b2.crc.delegate.setfinder.DeleteQueryMasterHandler.execute(Unknown Source)
      2012?01?19 09:40:03,161 ERROR [STDERR]at edu.harvard.i2b2.crc.delegate.setfinder.QueryRequestDelegate.handleRequest(Unknown Source)
      2012?01?19 09:40:03,161 ERROR [STDERR]at edu.harvard.i2b2.crc.axis2.QueryService.handleRequest(Unknown
      Source)
      2012?01?19 09:40:03,161 ERROR [STDERR]at edu.harvard.i2b2.crc.axis2.QueryService.request(Unknown Source)
      2012?01?19 09:40:03,161 ERROR [STDERR]at sun.reflect.GeneratedMethodAccessor138.invoke(Unknown Source)
      2012?01?19 09:40:03,161 ERROR [STDERR]at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      2012?01?19 09:40:03,161 ERROR [STDERR]at java.lang.reflect.Method.invoke(Method.java:601)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.axis2.receivers.RawXMLINOutMessageReceiver.invokeBusinessLogic(RawXMLINOutMessageReceiver.java:88
      )
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.receive(AbstractInOutSyncMessageReceiver.java:39)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:493)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.axis2.transport.http.util.RESTUtil.invokeAxisEngine(RESTUtil.java:153)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.apache.axis2.transport.http.util.RESTUtil.processPostRequest(RESTUtil.java:102)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.axis2.transport.http.AxisRESTServlet.doPost(AxisRESTServlet.java:66)
      2012?01?19 09:40:03,161 ERROR [STDERR]at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
      2012?01?19 09:40:03,161 ERROR [STDERR]at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
      2012?01?19 09:40:03,161 ERROR [STDERR]at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
      2012?01?19 09:40:03,161 ERROR [STDERR]at
      org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
      2012?01?19 09:40:03,161 ERROR [STDERR]at java.lang.Thread.run(Thread.java:722)
      2012?01?19 09:40:03,286 INFO [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] Located DataSource for hiveId=[i2b2demo] projectId=[/Demo/]
      2012?01?19 09:40:03,286 INFO [edu.harvard.i2b2.crc.dao.OracleDAOFactory] Using datasource java:QueryToolDemoDS
      Thanks,
      Yves

      Yves Thorrez
      Medische Informatieverwerking yves.thorrez.uzbrussel.be <yves.thorrez.uzbrussel.be> Laarbeeklaan 101 ? 1090 Brussel
      Tel 02 477 69 45 ? Fax 02 477 69 75
      www.uzbrussel.be <http://www.uzbrussel.be> <http://www.uzbrussel.be> <http://www.uzbrussel.be> <http://www.uzbrussel.be/>



      NEXT EMAIL
      From: Sanderson, Iain C. [sandersi.musc]
      Sent:Wednesday, January 18, 2012 5:34 PM
      To: Obeid, Jihad; i2b2 AUG Members
      Cc:Larsen, Richard
      Subject:RE: i2b2 user acceptance testing

      Thanks Jihad
      Iain.
      Dr. Iain Sanderson.
      Endowed Chair in Biomedical Informatics, Center for Healthcare Quality.
      Director, Biomedical Informatics Program, SCTR. sandersi.musc
      (843) 792?1914

      From: Obeid, Jihad [mailto:jobeid.musc] Sent: Wednesday, January 18, 2012 5:31 PM To: i2b2 AUG Members
      Subject: i2b2 user acceptance testing
      Hi All,
      We are about to go live with an i2b2 implementation as a self?service tool for researchers.
      Has anyone in this group used any user acceptance testing scenarios or used surveys of their beta users for feedback?If so, would you mind sharing your experience and perhaps your survey material? Thanks,
      Jihad
      Jihad Obeid, MD
      Associate Director, Biomedical Informatics Program South Carolina Translational Research Institute Medical University of South Carolina
      Phone: (843)792?0272
      Email: jobeid.musc



      NEXT EMAIL
      From: Dan Connolly [dconnolly.kumc] Sent:Wednesday, January 18, 2012 12:15 PM To:Murphy, Shawn N.
      Cc: i2b2 AUG Members
      Subject:RE: can't get "same encounter" queries to work
      That's possible. We had some old java classes left over somehow. We did a bunch of stuff, including re-installing the hive, to clear that up.
      Maybe it cleared this up too; I'll have to check. Thanks for the suggestion.

      On Tue, 2012-01-17 at 14:14 -0500, Murphy, Shawn N. wrote:
      Dan, do you have an old XSD on your server side?
      Thanks,
      Shawn.


      From: Dan Connolly [dconnolly.kumc]
      Sent: Friday, January 13, 2012 11:34 AM
      To: i2b2 AUG Members
      Subject: can't get "same encounter" queries to work
      This is another case where the request in the XML Message History
      shows the constraints we're interested in, but the response does not (let alone the
      generated SQL).
      The request shows:
      <query_timing>SAMEVISIT</query_timing>
      ....
      <panel_timing>SAMEVISIT</panel_timing>
      But the response does not.
      Any suggestions about what might be going on or how to fix it?

      Dan Connolly, KUMC Medical Informatics
      913-945-6741


      NEXT EMAIL
      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent:Tuesday, January 17, 2012 5:05 PM
      To: Schulte, Gregory; i2b2 AUG Members
      Subject:RE: database scripts v 1.6.02
      Thank Mike and Greg. I understand.
      Nandan

      From: Schulte, Gregory [Gregory.Schulte.childrenscolorado]
      Sent: Tuesday, January 17, 2012 5:04 PM
      To: Patibandla, Nandan; members.i2b2aug
      Subject: RE: database scripts v 1.6.02
      Nandan,
      The sequence creation in SQL server are specified when the table is created with an "identity" property. (http://msdn.microsoft.com/en?us/library/aa933196%28v=SQL.80%29.aspx)
      Example in PM_CELL_PARAMS creation:
      CREATE TABLE PM_CELL_PARAMS (
      IDINT IDENTITY(1,1) PRIMARY KEY,
      The local temp tables (tables starting with #, and are deleted when the user disconnects) in sql server are created dynamically, looking at an example in our QT_QUERY_MASTER table:
      insert into i2b2demodata.#global_temp_table ( patient_num , panel_count) select patient_num ,1 as panel_countfrom ( selectpatient_numfrom i2b2demodata.observation_fact whereconcept_cd IN (select concept_cd from i2b2demodata.concept_dimensionwhere concept_path LIKE
      '\i2b2\Diagnoses\Congenital anomalies (740-759)(758) Chromosomal anomalies(758-0) Down''s syndrome%')AND ((MODIFIER_CD IN ( select MODIFIER_CD from i2b2demodata.MODIFIER_DIMENSION where MODIFIER_PATH LIKE '\Admit Diagnosis%' ))
      )group bypatient_num) t

      insert into i2b2demodata.#dx (patient_num) select * from ( select distinct patient_numfrom i2b2demodata.#global_temp_table where panel_count = 1 ) q
      Hope this helps, Greg


      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent: Tuesday, January 17, 2012 2:33 PM
      To: i2b2 AUG Members
      Subject: database scripts v 1.6.02
      Hi Mike,
      The database which we are using is SQL server. When I compare the scripts of both SQL server and Oracle, the SQL Server scripts are missing the creation of temporary tables
      DX, QUERY_GLOBAL_TEMP, GLOBAL_TEMP_PARAM_TABLE, GLOBAL_TEMP_FACT_PARAM_TABLE, MASTER_QUERY_GLOBAL_TEMP
      And also the Sequence Creation, which is causing the error when I am trying to run the query by selecting the "Age Patient Breakdown". It is looking fot the #DX table which does not exist in the databse. Should we create these tables? Below is the query generated based which is throwing the error.
      Also can you please update the scripts for SQL server which will be helpful.
      SQL Query
      select count(distinct PATIENT_NUM) as item_count from i2b2_v16_crc.dbo.patient_dimension where patient_num in (select patient_num from i2b2_v16_crc.dbo.#DX ) and patient_num IN (select patient_num from i2b2_v16_crc.dbo.patient_dimension where birth_date >
      getdate() - (365.25 *80) + 1)
      Thank you
      Nandan



      NEXT EMAIL
      From:Mendis, Michael E.
      Sent:Tuesday, January 17, 2012 4:35 PM
      To: Patibandla, Nandan; i2b2 AUG Members
      Subject:Re: database scripts v 1.6.02
      That is because on sqlserver these are created dynamically, and on oracle they need to exist first
      mike

      On 1/17/12 4:33 PM, "Patibandla, Nandan" <Nandan.Patibandla.childrens.harvard> wrote:
      Hi Mike,
      The database which we are using is SQL server. When I compare the scripts of both SQL server and Oracle, the SQL Server scripts are missing the creation of temporary tables
      DX, QUERY_GLOBAL_TEMP, GLOBAL_TEMP_PARAM_TABLE, GLOBAL_TEMP_FACT_PARAM_TABLE, MASTER_QUERY_GLOBAL_TEMP
      And also the Sequence Creation, which is causing the error when I am trying to run the query by selecting the "Age Patient Breakdown". It is looking fot the #DX table which does not exist in the databse. Should we create these tables? Below is the query generated based which is throwing the error.
      Also can you please update the scripts for SQL server which will be helpful.
      SQL Query
      select count(distinct PATIENT_NUM) as item_count from i2b2_v16_crc.dbo.patient_dimension where patient_num in (select
      patient_num from i2b2_v16_crc.dbo.#DX ) and patient_num IN (select patient_num from i2b2_v16_crc.dbo.patient_dimension where birth_date > getdate() ? (365.25 *80) + 1)
      Thank you
      Nandan



      NEXT EMAIL
      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent:Tuesday, January 17, 2012 1:42 PM
      To: Peter Beninato; i2b2 AUG Members
      Cc:Mendis, Michael E.
      Subject:RE: Patient Set Limit in 1.6.02
      Thank you Peter, That helped.
      Nandan

      From: Peter Beninato [beninato.ohsu]
      Sent: Tuesday, January 17, 2012 12:04 PM
      To: Patibandla, Nandan; members.i2b2aug
      Cc: MMENDIS.PARTNERS
      Subject: RE: Patient Set Limit in 1.6.02
      Hi,
      You can edit: CRC_sdx_PRS.js
      On line 370 increase 200 upwards, I used 10,000 and got the patient set to display.
      var sdxParentNode = node.data.i2b2_SDX;
      var options = i2b2.CRC.params;
      if (node.data.i2b2_SDX.origData.size > 200)
      {
      } else {
      }
      }
      node.loadComplete();
      alert("The patient count is greater then that can be displayed.");
      i2b2.sdx.Master.getChildRecords(sdxParentNode, scopedCallback, options);

      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent: Friday, January 13, 2012 12:29 PM
      To: i2b2 AUG Members
      Cc: MMENDIS.PARTNERS
      Subject: Patient Set Limit in 1.6.02
      Hi Everyone,
      I just deployed the new instance of i2b2 and the webclient, when I run the query and try to look at the patient set, it gives a popup "The Patient count is greater than that can be displayed".
      I was wondering where should I set the limit of the patient set to be displayed. Thank you
      Nandan



      NEXT EMAIL
      From: Peter Beninato [beninato.ohsu] Sent:Tuesday, January 17, 2012 1:32 PM To: i2b2 AUG Members
      Subject:i2b2 - v1.6.02 - Error - patient breakdowns on Query Run
      Attachments:runQueryInstance_fromQueryDefinintion.xml

      Hi,
      I get an error like...:
      </request_xml>
      </query_master>
      <query_instance>
      <query_instance_id>290</query_instance_id>
      <query_master_id>290</query_master_id>
      <user_id>beninato</user_id>
      <group_id>ohsu</group_id>
      <start_date>2012-01-17T10:10:34.000-08:00</start_date>
      <end_date>2012-01-17T10:10:53.000-08:00</end_date>
      <message>edu.harvard.i2b2.common.exception.I2B2DAOException: QueryResultPatientSetGenerator.generateResult:Error when getting children from ontology [Database error]
      at edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator.generateResult(Unknown
      Source)
      ...when any of the patient breakdowns are being utilized.
      The error refers to the ontology? Attached is full xml of runQueryInstance_fromQueryDefinition. Are there some new columns in the ontology i2b2 table that need to be filled in?

      Peter Beninato - OCTRI DW Developer
      Oregon Health & Science University
      503-494-9985
      beninato.ohsu <beninato.ohsu.edu>



      NEXT EMAIL
      From: Peter Beninato [beninato.ohsu]
      Sent:Friday, January 13, 2012 2:32 PM
      To: Dan Connolly; i2b2 AUG Members
      Subject:RE: i2b2 1.6 patient counting problem: generated SQL missing a panel

      Hi,
      In src\edu.harvard.i2b2.crc\etc\spring\crc.properties there is a parameter:
      ###############################################################
  2. CRC setfinder query without using temp table
    ###############################################################
    edu.harvard.i2b2.crc.setfinderquery.skiptemptable.maxconcept=40


    With 8 panels, might there be approximately 40 concepts and if so, maybe there is some issue with the code that works with this temp table?
    Peter

    From: Dan Connolly [dconnolly.kumc]
    Sent: Friday, January 13, 2012 7:57 AM
    To: i2b2 AUG Members
    Subject: Re: i2b2 1.6 patient counting problem: generated SQL missing a panel
    Oops; those attachments don't show the problem, actually.
    I'm pretty sure I saw it, but I'm struggling to package up the details. I hope I'm not just crazy...

    On Fri, 2012-01-13 at 15:49 +0000, Dan Connolly wrote:
    We're seeing some basic counting problems since we upgraded to 1.6.
    For example, an investigator used 8 panels to count a denominator cohort of ~150 patients, and then added a panel for prostate cancer. The count didn't change, which
    he found implausible.
    I'm attaching the REQUEST_XML and the GENERATED_SQL for his query. The GENERATED_XML shows all 9 panels, including the prostate cancer,
    but the prostate cancer concept path doesn't show up in the generated
    SQL at all.
    Is there some configuration setting that is used by the SQL generation code that we might have neglected?
    Any help with a diagnosis or solution would be greatly appreciated.

    Dan Connolly, KUMC Medical Informatics
    913-945-6741



    NEXT EMAIL
    From: Peter Beninato [beninato.ohsu]
    Sent:Friday, January 13, 2012 11:41 AM
    To: Steward, Duane; i2b2 AUG Members
    Subject:RE: failure to create timer table in 1.602 build

    Hi,
    I restarted JBOSS and grep'd for "ORA?00942: table or view does not exist" and do not get that error. But I do see a lot of messages in the form of:
    2012?01?13 08:25:43,697 DEBUG [org.jboss.ejb.txtimer.TimerImpl] Timer was not registered with Tx, resetting state:
    [id=1326470964446,target=[target=jboss.j2ee:service=EJB3,ear=QP1.ear,jar=QP?An?
    EJB.jar,name=CronEjb],remaining=59821,periode=60000,in_timeout]
    And when I didn't have the AGG_SERVICE_ACCOUNT in the project, many queries reached the 180 second limit, and it does not seem to get picked up by any other queues.
    For what it is worth there is a query that seems to happen around the same time in the log, so you might want to look at your CRC_ANALYSIS_JOB table?
    2012?01?13 08:24:43,695 DEBUG [org.springframework.jdbc.core.JdbcTemplate] Executing SQL query [ select * from i2b2hive.CRC_ANALYSIS_JOB where queue_name = ? and status_type_id = ? order by create_date]

    From: Steward, Duane [dsteward.NEMOURS]
    Sent: Friday, January 13, 2012 8:07 AM
    To: i2b2 AUG Members
    Subject: failure to create timer table in 1.602 build
    We have built and deployed version 1.602 on Ubuntu and Red Hat (6.1). The Workbench and Web Clients appear to work but ever since the CRC cell was added (following the published installation documents) the JBoss Server logs the following exception followed by a handful of more exceptions that appear to be related to this inability to create a timer table. The database has been validated flawless by pointing other i2b2 working instances to it. So far there is no apparent effect on the workbench or queries tried with the i2b2demo data. We have seen this with every attempt to build 1.602 on multiple platforms.
    Has anyone else run into this?
    12:57:19,451 INFO [DLQ] Bound to JNDI name: queue/DLQ
    12:57:19,519 INFO [ConnectionFactoryBindingService] Bound ConnectionManager
    'jboss.jca:service=ConnectionFactoryBinding,name=JmsXA' to JNDI name 'java:JmsXA'
    12:57:19,644 INFO [ConnectionFactoryBindingService] Bound ConnectionManager
    'jboss.jca:service=DataSourceBinding,name=OntologyBootStrapDS' to JNDI name 'java:OntologyBootStrapDS'
    12:57:19,654 INFO [ConnectionFactoryBindingService] Bound ConnectionManager
    'jboss.jca:service=DataSourceBinding,name=OntologyDemoDS' to JNDI name 'java:OntologyDemoDS'
    12:57:19,750 INFO [ConnectionFactoryBindingService] Bound ConnectionManager
    'jboss.jca:service=DataSourceBinding,name=PMBootStrapDS' to JNDI name 'java:PMBootStrapDS'
    12:57:19,907 INFO [ConnectionFactoryBindingService] Bound ConnectionManager
    'jboss.jca:service=DataSourceBinding,name=WorkplaceBootStrapDS' to JNDI name 'java:WorkplaceBootStrapDS'
    12:57:19,920 INFO [ConnectionFactoryBindingService] Bound ConnectionManager
    'jboss.jca:service=DataSourceBinding,name=WorkplaceDemoDS' to JNDI name 'java:WorkplaceDemoDS'
    12:57:22,734 INFO [JmxKernelAbstraction] creating wrapper delegate for: org.jboss.ejb3.stateless.StatelessContainer
    12:57:22,739 INFO [JmxKernelAbstraction] installing MBean: jboss.j2ee:jar=CRCLoader?
    EJB.jar,name=DataMartLoader,service=EJB3 with dependencies:
    12:57:22,835 INFO [EJBContainer] STARTED EJB: edu.harvard.i2b2.crc.loader.ejb.DataMartLoader ejbName:
    DataMartLoader
    12:57:22,852 WARN [DatabasePersistencePolicy] Unable to get timer handles for containerId:
    jboss.j2ee:service=EJB3,jar=CRCLoader?EJB.jar,name=DataMartLoader java.sql.SQLException: ORA?00942: table or view does not exist
    at oracle.jdbc.driver.DatabaseError.throwSQLException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTloer.processError(T4CTTloer.java:331)
    at oracle.jdbc.driver.T4CTTloer.processError(T4CTTloer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
    at oracle.jdbc.driver..OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
    at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)


    Duane Steward, DVM, MSIE, PhD
    Chief Computer Scientist for Health Informatics
    Nemours



    NEXT EMAIL
    From: Dan Connolly [dconnolly.kumc]
    Sent:Friday, January 13, 2012 11:37 AM
    To: Nandan.Patibandla.childrens.harvard
    Cc: i2b2 AUG Members; beninato.ohsu
    Subject:RE: i2b2 - v1.6.02 - Previous Queries - Number of Patients - webclient

    On Thu, 2012-01-12 at 23:21 +0000, Patibandla, Nandan wrote:
    Hi peter,
    I dont thik it gets displayed in the previous query section.
    It used to, in 1.4.
    One of our users brought this up with us too.
    Mike, please correct me if I am wrong.
    Nandan

    Peter Beninato <beninato.ohsu> wrote:
    Hi Nandan,
    Thanks for the reply. I have done as you outlined.
    I see the count in the Query Status, but it is not displayed in the Previous Query section.
    Peter

    From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
    Sent: Thursday, January 12, 2012 3:04 PM
    [ To: Peter Beninato; i2b2 AUG Members|mailto:members@i2b2aug.org]
    Subject: RE: i2b2 - v1.6.02 - Previous Queries - Number of Patients - webclient
    Hi Peter,
    Please drag the root node of the particular query to the query name on the right top, then it will display the count and status.
    Thank you
    Nandan

    [ From: Peter Beninato [beninato.ohsu]|mailto:beninato@ohsu.edu]
    Sent: Thursday, January 12, 2012 5:55 PM
    [ To: i2b2 AUG Members|mailto:members@i2b2aug.org]
    Subject: i2b2 - v1.6.02 - Previous Queries - Number of Patients - webclient
    Hi,
    In the webclient, under Previous Queries, the "Number of patients for..." doesn't seem to display the count.
    This behavior has been observed as both an obfuscated, and a de-identifed user.
    Fyi.
    Peter Beninato- OCTRDI W Developer
    Oregon Health & Science University
    503-494-9985
    beninato.ohsu<beninato.ohsu>



    NEXT EMAIL
    From: Dan Connolly [dconnolly.kumc]
    Sent:Friday, January 13, 2012 11:27 AM
    To: i2b2 AUG Members
    Subject:Re: i2b2 1.6 patient counting problem: generated SQL missing a panel
    My mistake was assuming that QT_QUERY_MASTER.REQUEST_XML has exactly the same bytes as the XML Message History in the web client. But not so.
    In fact, it seems that my problem is somewhere in between.
    webclient_msg_history_shows_9.xml is what I'm now looking at in the web client message history;
    it shows 9 panels, including prostate cancer (Malignant neoplasm of prostate).
    webclient_msg_history_response.xml is the corresponding response in the webclient XML Message History. Note it bears <query_master_id>5086</query_master_id>.
    Note also that its <request_xml> is missing the prostate cancer panel. (?!) I wonder how that could be.
    I2B2_REQUEST_XML_5086.xml is the I2B2_REQUEST_XML column of QT_QUERY_MASTER
    with query_master_id=5086. It's missing the prostate cancer panel too.


    On Fri, 2012-01-13 at 09:57 -0600, Dan Connolly wrote:
    Oops; those attachments don't show the problem, actually.
    I'm pretty sure I saw it, but I'm struggling to package up the details. I hope I'm not just crazy...

    On Fri, 2012-01-13 at 15:49 +0000, Dan Connolly wrote:
    We're seeing some basic counting problems since we upgraded to 1.6.
    For example, an investigator used 8 panels to count a denominator cohort of ~150 patients, and then added a panel for prostate cancer. The count didn't change, which
    he found implausible.
    I'm attaching the REQUEST_XML and the GENERATED_SQL for his query. The GENERATED_XML shows all 9 panels, including the prostate cancer,
    but the prostate cancer concept path doesn't show up in the generated
    SQL at all.
    Is there some configuration setting that is used by the SQL generation code that we might have neglected?
    Any help with a diagnosis or solution would be greatly appreciated.
    Dan Connolly, KUMC Medical Informatics
    913-945-6741



    NEXT EMAIL
    From: Schulte, Gregory [Gregory.Schulte.childrenscolorado]
    Sent:Friday, January 13, 2012 11:16 AM
    To: Mendis, Michael E.; i2b2 AUG Members
    Subject:RE: OOM java errors
    Thanks Mike and Nandan.
    I was running 1.6.01, just checked on a 1.6.02 version and it's working properly.
    Greg

    From: Mike Mendis [mmendis.partners]
    Sent: Friday, January 13, 2012 8:58 AM
    To: Schulte, Gregory; i2b2 AUG Members
    Subject: Re: OOM java errors
    Which version are you running, there was a OOS that was resolved in 1.6.02

    On 1/13/12 10:56 AM, "Schulte, Gregory" <Gregory.Schulte.childrenscolorado> wrote:
    Any one run into Out of Memory Errors when expanding the "patient set" ?
    We are currently running on windows server 2008 R2 with the below Java memory settings: JAVA_OPTS=%JAVA_OPTS% ?Xms512m ?Xmx1024m ?XX:PermSize=256m ?XX:MaxPermSize=512m
    Anytime we try to expand the patient set on the webclient or workbench we get the below errors in the Jboss log.. this is for a patient set of only 23.
    java.lang.OutOfMemoryError: GC overhead limit exceeded
    2012?01?13 08:47:50,062 ERROR [STDERR] Exception in thread "ScannerThread"
    2012?01?13 08:47:54,202 ERROR [STDERR] java.lang.OutOfMemoryError: GC overhead limit exceeded
    2012?01?13 08:47:54,202 ERROR [org.jboss.ejb.plugins.LogInterceptor] Unexpected Error in method: public abstract edu.harvard.i2b2.crc.datavo.pdo.query.PatientDataResponseType edu.harvard.i2b2.crc.ejb.PdoQueryLocal.getPlainPatientData(edu.harvard.i2b2.crc.datavo.db.DataSourceLookup,edu.ha rvard.i2b2.crc.datavo.pdo.query.GetPDOFromInputListRequestType,java.lang.String) throws edu.harvard.i2b2.common.exception.I2B2Exception
    java.lang.OutOfMemoryError: GC overhead limit exceeded
    2012?01?13 08:47:54,202 ERROR [STDERR]at javax.management.modelmbean.ModelMBeanAttributeInfo.clone(ModelMBeanAttributeInfo.java:417)
    2012?01?13 08:47:54,202 ERROR [STDERR]at javax.management.modelmbean.ModelMBeanInfoSupport.getAttribute(ModelMBeanInfoSupport.java:764)
    2012?01?13 08:47:54,202 ERROR [edu.harvard.i2b2.crc.delegate.pdo.PdoQueryRequestDelegate] Throwable javax.ejb.EJBException: Unexpected Error
    java.lang.OutOfMemoryError: GC overhead limit exceeded
    at org.jboss.ejb.plugins.LogInterceptor.handleException(LogInterceptor.java:430)
    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:209)
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)
    at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
    at org.jboss.ejb.Container.invoke(Container.java:960)
    at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:430)
    at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)
    at $Proxy161.getPlainPatientData(Unknown Source)
    at edu.harvard.i2b2.crc.delegate.pdo.GetPDOFromInputListHandler.execute(Unknown Source)
    at edu.harvard.i2b2.crc.delegate.pdo.PdoQueryRequestDelegate.handleRequest(Unknown Source)
    at edu.harvard.i2b2.crc.axis2.QueryService.handleRequest(Unknown Source)
    at edu.harvard.i2b2.crc.axis2.QueryService.pdorequest(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.axis2.receivers.RawXMLINOutMessageReceiver.invokeBusinessLogic(RawXMLINOutMessageReceiver.java:88
    )
    at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.receive(AbstractInOutSyncMessageReceiver.java:39)
    at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:493)
    at org.apache.axis2.transport.http.util.RESTUtil.invokeAxisEngine(RESTUtil.java:153)
    at org.apache.axis2.transport.http.util.RESTUtil.processPostRequest(RESTUtil.java:102)
    at org.apache.axis2.transport.http.AxisRESTServlet.doPost(AxisRESTServlet.java:66)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
    at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
    at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
    at java.lang.Thread.run(Thread.java:662)
    2012?01?13 08:47:54,202 ERROR [STDERR]at javax.management.modelmbean.ModelMBeanInfoSupport.getDescriptor(ModelMBeanInfoSupport.java:572)
    2012?01?13 08:47:54,217 ERROR [STDERR]at org.jboss.mx.modelmbean.ModelMBeanInvoker.override(ModelMBeanInvoker.java:830)
    2012?01?13 08:47:54,217 ERROR [STDERR]at
    org.jboss.mx.server.AbstractMBeanInvoker.getAttribute(AbstractMBeanInvoker.java:350)
    2012?01?13 08:47:54,217 ERROR [STDERR]at org.jboss.mx.server.MBeanServerImpl.getAttribute(MBeanServerImpl.java:556)
    2012?01?13 08:47:54,217 ERROR [STDERR]at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:140)
    2012?01?13 08:47:54,217 ERROR [STDERR] at $Proxy12.getSuffixOrder(Unknown Source)
    2012?01?13 08:47:54,217 DEBUG [org.springframework.beans.factory.support.DefaultListableBeanFactory] Returning cached instance of singleton bean 'appType'
    2012?01?13 08:47:54,217 ERROR [STDERR] at
    org.jboss.deployment.scanner.URLDeploymentScanner.updateSorter(URLDeploymentScanner.java:671)
    2012?01?13 08:47:54,217 ERROR [STDERR]at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:474)
    2012?01?13 08:47:54,217 ERROR [STDERR]at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:2
    63)
    2012?01?13 08:47:54,217 ERROR [STDERR]at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:274)
    2012?01?13 08:47:54,217 ERROR [STDERR]at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:225)
    2012?01?13 08:47:54,217 DEBUG [edu.harvard.i2b2.common.util.jaxb.JAXBUtil]
    object.toString()edu.harvard.i2b2.crc.datavo.i2b2message.RequestMessageType
    2012?01?13 08:47:54,217 ERROR [STDERR] Exception in thread
    "ContainerBackgroundProcessor[StandardEngine[jboss.web]]"
    2012?01?13 08:47:54,217 ERROR [STDERR] java.lang.OutOfMemoryError: GC overhead limit exceeded
    2012?01?13 08:47:54,217 DEBUG [edu.harvard.i2b2.crc.axis2.QueryService] Response in service<?xml version="

    Thanks,
    Greg Schulte | Clinical Research Data Warehouse Database Analyst
    13123 East 16th Avenue, Box 485 | Aurora, CO 80045
    Phone: 720.777.5740 | Gregory.Schulte.childrenscolorado <schulte.kelly.tchden>




    NEXT EMAIL
    From:Davis, Michael (Oncology) [davismk.upmc]
    Sent:Friday, January 13, 2012 10:43 AM
    To:Kohane, Isaac Samuel; Mauro
    Cc: i2b2 AUG Members
    Subject:RE: Export XLS Plugin 1.6
    Did anyone get this to work in Internet Explorer (7.x)? It doesn't seem to display properly and the drag and drop no longer works. It works great in FireFox.

    From: Kohane, Isaac Samuel [Isaac_Kohane.hms.harvard] Sent: Thursday, January 12, 2012 10:20 AM
    To: Mauro
    Cc: members.i2b2aug.org members
    Subject: Re: Export XLS Plugin 1.6
    Excellent!
    On Jan 12, 2012, at 10:13 AM, Mauro wrote:
    I'm proud to announce the fixed export plugin for the 1.6 version of i2b2.
    [http://code.google.com/p/i2b2-export-xls-plugin/downloads/list| http://code.google.com/p/i2b2-export-xls-plugin/downloads/list]

    Ing. Mauro Bucalo, Ext. Collaborator
    Dipartimento di Informatica e Sistemistica Universita' di Pavia Via
    Ferrata 1, 27100 Pavia, Italy
    tel:++39.0382.985981
    [ e-mail: mauro.bucalo.gmail|mailto:mauro.bucalo@gmail.com]
    web-page: http://it.linkedin.com/in/maurobucalo



    NEXT EMAIL
    From: Peter Beninato [beninato.ohsu]
    Sent: Thursday, January 12, 2012 1:09 PM
    To: Dan Connolly
    Cc: i2b2 AUG Members
    Subject:RE: i2b2 - v1.6.02 - ontology - observations/c_totnum - AGG_SERVICE_ACCOUNT- turn off observation count in ontology?
    Hi,
    Thanks Dan.
    I unchecked the "Enable Patient Counts" check box, and the number does disappear.
    I'm wondering if this can be set as a parameter somewhere in the config files to apply across all user? Or as a user param in ADMIN?
    Also, it is interesting that it is called "Enable Patient Counts", and the role of c_totalnum.
    Should c_totalnum be populated with the count of observations ( for performance optimization), or should it be populated with the number of patients?
    Peter

    From: Dan Connolly [dconnolly.kumc]
    Sent: Thursday, January 12, 2012 9:58 AM
    To: Peter Beninato
    Cc: i2b2 AUG Members
    Subject: Re: i2b2 - v1.6.02 - ontology - observations/c_totnum - AGG_SERVICE_ACCOUNT- turn off observation count in ontology?

    On Thu, 2012-01-12 at 09:42 -0800, Peter Beninato wrote: Hi,
    [...]
    My understanding is that c_totalnum columns is also used to choose which panel to run first for query optimization.
    Interesting. I hadn't realized that.

    Is there a way to turn off the display of the number of observation in the ontology for non-obfuscated users?
    Yes, it's an option available to end-users, in fact.
    The little checkbox to the right of "Navigate Terms" brings up some options; among them is
    "Enable Patient Counts".
    I think you can change the default by editing default.htm; we did for "Disable Modifiers" and it worked.[http://informatics.kumc.edu/work/changeset/16b281b3f17a/i2b2-webclient|http://informatics.kumc.edu/work/changeset/16b281b3f17a/i2b2-webclient]
    I attempted to achieve this by removing the AGG_SERVICE_ACCOUNT (aka data_aggravation account) with deleterious effects.
    The code seems to really want to have this account as part of project.
    That's the account that one cell (CRC? I'm not sure) uses to talk to another (ONT?) during queries. So it's necessary.
    And yeah, troubleshooting when it's not set up right can be a pain.
    Dan Connolly, KUMC Medical Informatics
    913-945-6741


    NEXT EMAIL
    From:Murphy, Shawn N.
    Sent:Thursday, January 12, 2012 12:02 AM
    To:Peter Beninato; Phillips, Lori C.; Dan Connolly; Marc.Natter.childrens.harvard
    Cc: i2b2 AUG Members
    Subject:RE: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    HI All,
    Lots of great discussion today.
    Let's start by looking at the \\C_TABLE_CD\ issue. Lori is absolutely correct in her "fix", but just to elaborate a bit more....
    The TABLE_ACCESS first few columns looks like:

    | C_TABLE_CD | C_TABLE_NAME | | | | | | |

    BIRN

    BIRN

    N

    0

    \BIRN|Clinical Trials

    N

    CA

    CUST & META

    CUSTOM_META

    N

    0

    \Custom Metadata|Custom & Metadata

    N

    CA

    i2b2

    I2B2

    N

    0

    \i2b2|i2b2

    N

    FH

    i2b2_DEMO

    I2B2

    N

    1

    \i2b2\Demographics|Demographics

     

    CA

    i2b2_DIAG

    I2B2

    N

    1

    \i2b2\Diagnoses|Diagnoses

     

    FA

    i2b2_EXPR

    I2B2

    N

    1

    \i2b2\Expression Profiles Data|Expression Profiles Data

     

    FA

    i2b2_LABS

    I2B2

    N

    1

    \i2b2\Labtests|Laboratory Tests

     

    FA

    i2b2_MEDS

    I2B2

    N

    1

    \i2b2\Medications|Medications

     

    FA

    i2b2_PROC

    I2B2

    N

    1

    \i2b2\Procedures|Procedures

     

    FA

    i2b2_PROV

    I2B2

    N

    1

    \i2b2\Providers|Providers

     

    FA

    i2b2_REP

    I2B2

    N

    1

    \i2b2\Reports|Reports

     

    FA

    i2b2_VISIT

    I2B2

    N

    1

    \i2b2\Visit Details|Visit Details

     

    CA



    Without the yellow one. You could put "i2b2" in the C_TABLE_CD column for every entry and get back to the 1.4 format where \\i2b2\ is used for every item. Note, these are just like "pointers" and ultimately there just allow the lookup of C_TABLE_NAME. The only problem is that if you try to make them all "i2b2" you will get a primary key constraint. Don't worry about that, relax the constraint in the table first and then do it. I thought at first you could also put in the yellow row and it would be fine. It is, but the H doesn't hide it in the WC ontology view (our bad, we'll fix this), so not as good for now. I kind of agree with Peter that we should have done this in the demo so as not to be confusing in upgrades, but I think we got carried away with the primary key constraint thing.
    The second issue is more profound, and has to do with different versions of a terminology and a terminologies hierarchy. Marc really had the answer here, and I will just elaborate a bit more.
    The real answer is that generally you want to just add to your ontologies, keeping the same hierarchy for all the remaining ones.
    If one truly wants to adopt a totally new hierarchy, but wants to keep the old queries running, then keep both hierarchies and hide the old one. The old one can't be used any more, and if you want to allow that option then don't hide it, but you probably want to hide it. Very important is to keep the old hidden terms in you concept dimension. The issue is that the Hidden terms cause an error now when trying to use them, we'll look into this, but for now the use of synonym=Y labels cleverly described below should work.
    We'll get this working more cleanly in 1.6.03. Thanks,
    Shawn.


    From: Peter Beninato [beninato.ohsu]
    Sent: Wednesday, January 11, 2012 5:18 PM
    To: Phillips, Lori C.; Dan Connolly; Marc.Natter.childrens.harvard
    Cc: i2b2 AUG Members
    Subject: RE: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    Hi Lori,
    As I was away from my desk I was thinking that this might be a situation where an entry in the c_synonym code is set to Y, so it isn't presented in the ontology, but you do have an entry in the i2b2 table.
    I will experiment with the approach as described. I just thought that to re?use a query that at some point in the drag and drop it would reference the ontology cell.
    As ponder it more, the sql in the qt_master clob really does only reference the concept_dimension.
    Hmmm , but if that is the case, why this problem with the meta.table_access? Which is in the meta schema, and not just looking at the concept_dimension?
    Thanks.
    Peter

    From: Phillips, Lori C. [LCPHILLIPS.PARTNERS]
    Sent: Wednesday, January 11, 2012 1:23 PM
    To: Dan Connolly; Marc.Natter.childrens.harvard
    Cc: i2b2 AUG Members
    Subject: RE: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    Dan, Peter,
    Two different problems/concepts in this thread please be sure to notice the differences.
    The solution that Marc suggests below is for Peter's scenario where the Oct 2011 version of ICD9 doesnt match the 2010 version.
    In this case terms have moved from one path to a new one resulting in a new concept_path or c_fullname
    Marc's solution works well here. – overload the concept_dim table so that both the old concept path and the new one have entries.


    Dan,
    For your case you are concerned about the change in the table_cd prefix on the concept's key: table_cd + c_fullname =
    metadata key.
    In 1.4 we had one top level root in the navigate terms tree ("Ontology) with table_cd = i2b2. hence the concepts = \\i2b2\
    + c_fullname
    If you move forward to 1.6 where we show an example of a flattened hierarchy in the tree; the table_access table_cd =
    i2b2 does not appear here.
    The easiest thing you can do would be to modify one of the table_access entries such that its table_cd = i2b2.
    OR. you can continue to use the 1.4 version of the table_access where there is just one root node for ICD9 type terms.
    Also, I am picking up on something else that may be confusing. We did not change c_fullnames (concept_paths) from
    1.4 to 1.6
    For example: Diabetes mellitus has a c_fullname is the same in both versions:
    \i2b2\Diagnoses\Endocrine disorders (240-259)\Other endocrine gland diseases (250-259)(250) Diabetes mellitus\

    Lori

    From: Dan Connolly [dconnolly.kumc]
    Sent: Wednesday, January 11, 2012 3:36 PM
    To: Marc.Natter.childrens.harvard
    Cc: i2b2 AUG Members
    Subject: Re: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    OK, that's helpful.
    It's the sort of "burden going forward" that I was hoping to avoid, but
    I guess I've made my bed and now I get to lie in it.


    On Wed, 2012-01-11 at 15:28 -0500, Natter, Marc wrote:
    Peter, Dan,
    As long as the concept_cd for each of your entries hasn't changed, you can simply
    'overload' the concept_dimension table with additional rows that persist the legacy ontology mappings (e.g. merge the contents of the old and new concept_dimension tables, and then your old queries should work. We do this for a different purpose in CARRAnet- to provide cross-mappings within ontologies. I haven't tested this out with v1.6, but from what I understand now I predict it should work as it does for us in v1.5.
    Sincerely,
    Marc
    From: Peter Beninato <beninato.ohsu<beninato.ohsu>> Date: Wed, 11 Jan 2012 14:30:11 -0500
    To: Dan Connolly <dconnolly.kumc<dconnolly.kumc>>, "mmendis.partners<mmendis.partners>"
    <mmendis.partners<mmendis.partners>> Cc: "i2b2 AUG Members<i2b2 AUG Members>"
    <i2b2 AUG Members<i2b2 AUG Members>>
    Subject: RE: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR Regarding ICD9
    I re-loaded the ICD9-CM recently from the UMLS, which was updated on Oct. 01, 2011. There are path changes within that "standard" ontology.
    For instance:
    ICD9CM:317 (Mild intellectual disabilities (317))
    Is now under a path:
    \i2b2\Diagnoses\ICD9CM\DISEASES AND INJURIES\MENTAL, BEHAVIORAL AND NEURODEVELOPMENTAL
    DISORDERS\INTELLECTUAL DISABILITIES\Mild intellectual disabilities\
    In the prior year's ICD9CM ( that I derived from UMLS) it was: ICD9CM:317 (Mild mental retardation (317))
    Under a path:
    \i2b2\Diagnoses\ICD9CM\DISEASES AND INJURIES\MENTAL DISORDERS\MENTAL RETARDATION\Mild mental retardation\
    Changes like this will cause previous queries to break.
    At some point (soon...), I hope to export the updated ICD9CM ontology and post to the wiki. FYI.
    Peter

    From: Dan Connolly [dconnolly.kumc] Sent: Wednesday, January 11, 2012 11:13 AM
    To: mmendis.partners<mmendis.partners>
    Cc: i2b2 AUG Members<i2b2 AUG Members>; Peter Beninato
    Subject: Re: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    So the metadata your users interact with in production doesn't share much with the demo metadata?
    We have some metadata that we maintain locally (e.g. the lab hierarchy from our hospital), but
    (a) for stuff like ICD9 codes, we just use the demo metadata in production (we have discovered
    that it's incomplete, but we get by, for now) and
    (b) even for our local/custom metadata, we followed the structure of the demo metadata, i.e. the paths started with \i2b2 and such.
    I gather we weren't really supposed to do either of these?

    On Wed, 2012-01-11 at 13:30 -0500, Mike Mendis wrote:
    The demodata we did not intend to be upgraded, it was a demonstration of what i2b2 can do.This gives us the flexibility to improve the data.In 1.5 and earlier, we had all the ontologies in one root, in 1.6 we broke it out into what you see Medication, Diagnoses, Lab, procedures, ect making it easier to find the concepts.We also cleanup the data and removed some bad data, patients over 500 years old, ect.
    The software, we take great care in providing bckward compatibilty and providing upgrade scripts and procedures for migrating non-demodata to the current version.

    On 1/11/12 1:20 PM, "Dan Connolly" <dconnolly.kumc> wrote:
    So it's by design that queries from earlier versions don't work with 1.6? Is that OK with your user community, Mike? Did they not have much invested in older queries?
    For our users, we'd really like to support the queries they built prior to our recent move to 1.6.
    As an interim measure, I tweaked the TABLE_ACCESS entries back to '\i2b2'. It seems to work,
    though I'm not 100% sure there are no knock-on problems.
    I hope this difference between our local install and what you guys release/support doesn't
    become too much of a burden going forward.
    On Wed, 2012-01-11 at 12:49 -0500, Mike Mendis wrote:
    Peter,
    I assuming you upgraded from a 1.4 or 1.5 i2b2 to a new 1.6?If that is the case the metadata in our sample demodata changed in 1.6, in the past it started with \i2b2, now for diagnoses it is i2b2_DIAG, medications it is i2b2_MEDS, ect..As a result your old queries will not run because the metadata can not be found anymore.
    mike



    NEXT EMAIL
    From: Natter, Marc [Marc.Natter.childrens.harvard]
    Sent:Wednesday, January 11, 2012 4:24 PM
    To: i2b2 AUG Members
    Subject:Re: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR

    I believe you can may be able to get away with only merging the concept_dimension table (in the project itself). I have to admit that I don't know offhand whether the workplace cell calls the ont cell before the CRC to refresh all of the tablename, tablecolumn, operator, and dimcode, etc fields it needs to construct the CRC query. If it does, then yes, you'd need to also overload the ontology cell (metadata) table. If so, then when you create your new branch, you could just mark the very top level as hidden in the visualattributes field (second character 'H') for your 2011 version and only show the 2012 tree.
    Supporting multiple ontologies that way isn't necessarily a bad thing for special use cases, since you can alway hide all/part from the end user as needed - we use it as a nice way to make flatter, easier access 'short--cut' ontologies for various domains that peacefully coexist.
    Marc
    From: Peter Beninato <beninato.ohsu<beninato.ohsu.edu>>
    Date: Wed, 11 Jan 2012 15:53:12 -0500
    To: Dan Connolly <dconnolly.kumc<dconnolly.kumc>>, M Natter
    <marc.natter.childrens.harvard<mailto:marc.natter.childrens.harvard.edu>>[ Cc: "member|mailto:members@i2b2aug.org]s.i2b2aug.org<members.i2b2aug>"
    <i2b2 AUG Members<i2b2 AUG Members>>
    Subject: RE: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    But then I think I'm supporting two ontologies, a 2011 version and 2012 version, and the user has to select two items to drag and drop, for both concept_paths?

    From: Dan Connolly [dconnolly.kumc] Sent: Wednesday, January 11, 2012 12:36 PM
    To: Marc.Natter.childrens.harvard<mailto:Marc.Natter.childrens.harvard.edu>[ Cc: members.i2b2aug.or|mailto:members@i2b2aug.org]g<members.i2b2aug>
    Subject: Re: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    OK, that's helpful.
    It's the sort of "burden going forward" that I was hoping to avoid, but
    I guess I've made my bed and now I get to lie in it.

    On Wed, 2012-01-11 at 15:28 -0500, Natter, Marc wrote:
    Peter, Dan,
    As long as the concept_cd for each of your entries hasn't changed, you can simply 'overload' the concept_dimension table with additional rows that persist the legacy ontology mappings (e.g. merge the contents of the old and new concept_dimension tables, and then your old queries should work. We do this for a different purpose in CARRAnet- to provide cross-mappings within ontologies. I haven't tested this out with v1.6, but from what I understand now I predict it should work as it does for us in v1.5.
    Sincerely,
    Marc

    From: Peter Beninato <beninato.ohsu.edu<beninato.ohsu.edu><mailto:beninato.ohsu>> Date: Wed, 11 Jan 2012 14:30:11 -0500
    To: Dan Connolly <dconnolly.kumc<dconnolly.kumc><dconnolly.kumc>>,[ "mmendis.partner|mailto:mmendis@partners.org]s<mmendis.partners><mmendis.partners>"
    <mmendis.partners<mailto:mmendis.partners.org><mmendis.partners.org>>
    Cc: "members.i2b2aug.org<members.i2b2aug><members.i2b2aug.org>"
    <i2b2 AUG Members<members.i2b2aug><i2b2 AUG Members>>
    Subject: RE: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR Regarding ICD9
    I re-loaded the ICD9-CM recently from the UMLS, which was updated on Oct. 01, 2011. There are path changes within that "standard" ontology.
    For instance:
    ICD9CM:317 (Mild intellectual disabilities (317))
    Is now under a path:
    \i2b2\Diagnoses\ICD9CM\DISEASES AND INJURIES\MENTAL, BEHAVIORAL AND NEURODEVELOPMENTAL DISORDERS\INTELLECTUAL DISABILITIES\Mild intellectual disabilities\
    In the prior year's ICD9CM ( that I derived from UMLS) it was: ICD9CM:317 (Mild mental retardation (317))
    Under a path:
    \i2b2\Diagnoses\ICD9CM\DISEASES AND INJURIES\MENTAL DISORDERS\MENTAL RETARDATION\Mild mental retardation\
    Changes like this will cause previous queries to break.
    At some point (soon...), I hope to export the updated ICD9CM ontology and post to the wiki.

    FYI.
    Peter


    From: Dan Connolly [dconnolly.kumc] Sent: Wednesday, January 11, 2012 11:13 AM
    To: mmendis.partners.org<mmendis.partners><mmendis.partners>
    Cc: members.i2b2aug.org<members.i2b2aug><members.i2b2aug.org>; Peter Beninato
    Subject: Re: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    So the metadata your users interact with in production doesn't share much with the demo metadata?
    We have some metadata that we maintain locally (e.g. the lab hierarchy from our hospital), but
    (a) for stuff like ICD9 codes, we just use the demo metadata in production (we have discovered that it's incomplete, but we get by, for now) and
    (b) even for our local/custom metadata, we followed the structure of the demo metadata,
    i.e. the paths started with \i2b2 and such.
    I gather we weren't really supposed to do either of these?


    On Wed, 2012-01-11 at 13:30 -0500, Mike Mendis wrote:
    The demodata we did not intend to be upgraded, it was a demonstration of what i2b2 can do. This gives us the flexibility to improve the data. In 1.5 and earlier, we had all the ontologies in one root, in 1.6 we broke it out into what you see Medication, Diagnoses, Lab, procedures, ect making it easier to find the concepts. We also cleanup the data and removed some bad data, patients over 500 years old, ect.
    The software, we take great care in providing bckward compatibilty and providing upgrade scripts and procedures for migrating non-demodata to the current version.

    On 1/11/12 1:20 PM, "Dan Connolly" <dconnolly.kumc<dconnolly.kumc>> wrote: So it's by design that queries from earlier versions don't work with 1.6?
    Is that OK with your user community, Mike? Did they not have much invested in older queries?
    For our users, we'd really like to support the queries they built prior to our recent move to 1.6. As an interim measure, I tweaked the TABLE_ACCESS entries back to '\i2b2'. It seems to work, though I'm not 100% sure there are no knock-on problems.
    I hope this difference between our local install and what you guys release/support doesn't become too much of a burden going forward.

    On Wed, 2012-01-11 at 12:49 -0500, Mike Mendis wrote: Peter,
    I assuming you upgraded from a 1.4 or 1.5 i2b2 to a new 1.6? If that is the case the metadata in our sample demodata changed in 1.6, in the past it started with \i2b2, now for diagnoses it is i2b2_DIAG, medications it is i2b2_MEDS, ect.. As a result your old queries will not run because the metadata can not be found anymore.
    mike


    NEXT EMAIL
    From: Peter Beninato [beninato.ohsu] Sent:Wednesday, January 11, 2012 3:55 PM To:Peter Beninato; Dan Connolly
    Cc: i2b2 AUG Members
    Subject:RE: i2b2 - v1.6.02 - query reuse - design concern
    Whoops... I re-used, the re-run query, so I got only one panel, when I went back to original the other panels are shown. Great! Thanks.

    From: Peter Beninato [beninato.ohsu.edu] Sent: Wednesday, January 11, 2012 12:51 PM
    To: Dan Connolly
    Cc: i2b2 AUG Members
    Subject: RE: i2b2 - v1.6.02 - query reuse - design concern
    I get the same behavior, one panel, no access to criteria and settings.

    From: Dan Connolly [dconnolly.kumc] Sent: Wednesday, January 11, 2012 12:34 PM
    To: Peter Beninato
    Cc: i2b2 AUG Members
    Subject: Re: i2b2 - v1.6.02 - query reuse - design concern
    On Wed, 2012-01-11 at 11:23 -0800, Peter Beninato wrote:
    Hi,
    I did want to share a concern with the community about query re-use in
    v1.6.02.
    And I have only started to explore query re-use, so please understand,
    if I misrepresent the application behavior...
    If a user had run a query with 5 panels, and then goes to re-use it in v1.6.02, it appears that the previous queries criteria are wrapped up, and represented by an entry in Panel 1.
    I suspect what's happening is that the target of your drag-and-drop operation is the query panel area rather than the query name area.
    That used to cause the client to load the query in a usable fashion, but now the client seems to do something kinda useless in that case, as you have observed. I'm hoping that's a bug and that a fix will appear in due course. Meanwhile, ...
    Try dropping the query on the query name field.
    Dan Connolly, KUMC Medical Informatics
    913-945-6741



    NEXT EMAIL
    From:Mendis, Michael E.
    Sent:Wednesday, January 11, 2012 12:49 PM
    To: Peter Beninato; i2b2 AUG Members
    Subject:Re: i2b2 - v1.6.02 - Workplace Query reuse - demo data - table_access - ERROR
    Peter,
    I assuming you upgraded from a 1.4 or 1.5 i2b2 to a new 1.6? If that is the case the metadata in our sample demodata changed in 1.6, in the past it started with \i2b2, now for diagnoses it is i2b2_DIAG, medications it is i2b2_MEDS, ect.. As a result your old queries will not run because the metadata can not be found anymore.
    mike

    On 1/11/12 12:39 PM, "Peter Beninato" <beninato.ohsu> wrote: Hi,
    I had run a query back in November of 2009 against the demo data, and saved it into the workplace.
    It was for males with disorder of Lipid Metabolism.
    See attached orig_qry_clob_males_lipid.txt for query definition. When re?run, it throws an error.
    The generated SQL clob is null.
    Attached find the runQueryInstancefromQueryDefinition.xml
    Here's a snippet:
    Error when getting metadata from ontology [Could not locate record in table_access table] It seems to be complaining about the mapping in the table_access table.
    I did go back to the original query, and found entries in the i2b2 and concept_dimension table for the "concept_path".
    Fyi.
    Peter Beninato - OCTRI DW Developer
    Oregon Health & Science University
    503-494-9985
    beninato.ohsu <beninato.ohsu>



    NEXT EMAIL
    From: Dan Connolly [dconnolly.kumc] Sent:Wednesday, January 11, 2012 11:31 AM To: i2b2 AUG Members
    Subject:soapenv:Fault "Error during unmarshall" in REngine seti2b2data request: help?
    Attachments:,req.xml; ,response.xml

    We're making steady, if slow, progress getting the R Engine cell and the KaplanMeierStat plug-in to talk to each other.
    We got past a couple configuration issues, but now, when the plug-in tries to relay the patient data from the CRC to the R Engine cell, we get a SOAP fault:
    <faultstring>Umashaller error: Error during unmarshall
    ...
    nested exception is:
    edu.harvard.i2b2.common.exception.I2B2Exception: Umashaller error: Error during unmarshall
    nested exception is:
    org.apache.axis2.AxisFault: Umashaller error: Error during unmarshall
    ...
    nested exception is:
    edu.harvard.i2b2.common.exception.I2B2Exception: Umashaller error: Error during unmarshall
    ...
    nested exception is:
    edu.harvard.i2b2.common.exception.I2B2Exception: Umashaller error: Error during unmarshall
    I thought perhaps this was an issue that any SOAP developer might run into, so I searched around, and I came across roughly the same thing at http://services.i2b2.org/i2b2/rest/PMService/getServices
    So perhaps the i2b2 developers could give me some insight as to what's happening here?
    One possibility we're look into is that some XML schema types changed between when the R Engine cell was developed and the 1.6 release:

    diff -u it.fsm.i2b2.recell/it.fsm.i2b2.xml/xsd/hive/pdo_1.1/i2b2_PDO.xsd edu.harvard.i2b2.xml/xsd/hive/pdo_1.1/i2b2_PDO.xsd
    • #-- it.fsm.i2b2.recell/it.fsm.i2b2.xml/xsd/hive/pdo_1.1/i2b2_PDO.xsd 2010-06-08
      14:20:02.000000000 -0500
      +++ edu.harvard.i2b2.xml/xsd/hive/pdo_1.1/i2b2_PDO.xsd2011-07-28
      09:37:44.000000000 -0500
      @@ -11,8 +11,10 @@
      <xs:sequence>
      <xs:element ref="pdo:event_set" />
      <xs:element ref="pdo:concept_set"/>
      +<xs:element ref="pdo:modifier_set"/>
      <xs:element ref="pdo:observer_set" />
      -<xs:element ref="pdo:pid_set" />
      +<xs:element ref="pdo:pid_set" />
      +<xs:element ref="pdo:eid_set" />
      <xs:element ref="pdo:patient_set"/>
      <xs:element ref="pdo:observation_set" maxOccurs="unbounded"/>
      </xs:sequence>

      Perhaps we have to add something about modifiers and eids to cells/RECell/i2b2_msgs.js?

      Dan Connolly, KUMC Medical Informatics
      913-945-6741



      NEXT EMAIL
      From:Mendis, Michael E.
      Sent:Wednesday, January 11, 2012 10:23 AM
      To: Peter Beninato; Michael.C.Ford.kp; i2b2 AUG Members
      Subject:Re: Release 1.6 issues
      The best method for going from 1.4 ?> 1.6, is to go from 1.4 ?> 1.5 ?> 1.6 and use the ones in the upgrade folder. On 1/10/12 7:43 PM, "Peter Beninato" <beninato.ohsu> wrote:
      Hi,
      If this question is directed to me, what I did was hand run the CREATE/REPLACE procedure scripts, and then ran the hand?crafted (compilation derived from the i2b2 codebase) alter scripts, which I think captured the changes from v1.4 to v1.6.
      Peter


      From: Michael.C.Ford.kp [Michael.C.Ford.kp]
      Sent: Tuesday, January 10, 2012 4:15 PM To: Peter Beninato; i2b2 AUG Members Subject: RE: Release 1.6 issues
      I find the following when I searched for it. .www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf" class="external-link" rel="nofollow"linktype="raw" wikidestination="https://dev.www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf" originalalias="https://dev.www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf" >.www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf" class="external-link" rel="nofollow"linktype="raw" wikidestination="https://dev.www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf" originalalias="https://dev.www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf" >.www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf" class="external-link" rel="nofollow">https://dev.www.i2b2.org/software/files/PDF/Data%20Upgrade%20Guide_1-5.pdf
      and the zip file i2b2demodata-15.zip <https://dev.www.i2b2.org/software/download.html?d=221> .www.i2b2.org/software/archive.html" class="external-link" rel="nofollow"linktype="raw" wikidestination="https://dev.www.i2b2.org/software/archive.html" originalalias="https://dev.www.i2b2.org/software/archive.html" >.www.i2b2.org/software/archive.html" class="external-link" rel="nofollow"linktype="raw" wikidestination="https://dev.www.i2b2.org/software/archive.html" originalalias="https://dev.www.i2b2.org/software/archive.html" >.www.i2b2.org/software/archive.html" class="external-link" rel="nofollow">https://dev.www.i2b2.org/software/archive.html
      the scripts look like the same scripts except there are a series of triggers being updated also. So is this what I need to apply ?
      Michael C. Ford
      Application Development Project Manager
      Kaiser Permanente
      Division of Research
      2000 Broadway
      Oakland, CA 94612
      510.891.3576 (office)
      925.366.7730 (mobile phone) Michael.C.Ford.kp
      kp.org/thrive <http://kp.org/thrive>

      Peter Beninato <beninato.ohsu> 01/10/2012 02:01 PM
      ToPeter Beninato <beninato.ohsu>, Michael C Ford/CA/KAIPERM.KAIPERM, "mmendis.partners" <mmendis.partners>
      cc"i2b2 AUG Members" <i2b2 AUG Members> SubjectRE: Release 1.6 issues
      Hi,
      Below is listing of procedures that need to be run against the data schema. IGNORE the two crc_* scripts.
      Attached are three alter scripts that need to be run against the PM, DATA, and META schemas. I think those steps will get your schemas in sync.

      From: Peter Beninato [beninato.ohsu]
      Sent: Tuesday, January 10, 2012 1:52 PM
      To: Michael.C.Ford.kp; mmendis.partners
      Cc: i2b2 AUG Members
      Subject: RE: Release 1.6 issues
      I think there were a number of stored procedures that were added in v1.5 which v1.6 might rely on.
      From: Michael.C.Ford.kp [Michael.C.Ford.kp]
      Sent: Tuesday, January 10, 2012 1:46 PM
      To: mmendis.partners
      Cc: i2b2 AUG Members
      Subject: Re: Release 1.6 issues
      This is a VMImage that I connected to an Oracle test database we have.
      The database was a 1.4 instance that ran fine, the VMImage was using the XE internal database before.
      I installed a Oracle client and configured it, connection is being made. no errors in the log, can't see what the issue is.
      Michael C. Ford
      Application Development Project Manager
      Kaiser Permanente
      Division of Research
      2000 Broadway
      Oakland, CA 94612
      510.891.3576 (office)
      925.366.7730 (mobile phone) Michael.C.Ford.kp
      kp.org/thrive <http://kp.org/thrive>

      Mike Mendis <mmendis.partners> 01/10/2012 12:39 PM ToMichael C Ford/CA/KAIPERM.KAIPERM, <i2b2 AUG Members> cc
      SubjectRe: Release 1.6 issues
      This is using the latest 1.6.02 webclient? Was the 1.4 webclient working fine on this box before the upgrade? On 1/10/12 3:04 PM, "Michael.C.Ford.kp <Michael.C.Ford.kp> " <Michael.C.Ford.kp
      <Michael.C.Ford.kp> > wrote:
      I have a new issue with the 1.6 release.
      I have it configured and the PM file has been upgraded to include the CLOB's. the databases are all from a 1.4 release with just the PM tables being updated.
      When I signon I see "An error has occurred in the Cell's Ajax Library" there are no errors in the logs on the server.
      within firebug I see the posts webclient/index.php are all empty with the exception of the first response.
      <ns2:response xmlns:ns2="http://www.i2b2.org/xsd/hive/msg/1.1/
      <http://www.i2b2.org/xsd/hive/msg/1.1/> " xmlns:ns4="http://www.i2b2.org/xsd/cell/pm/1.1/
      <http://www.i2b2.org/xsd/cell/pm/1.1/> " xmlns:ns3="http://www.i2b2.org/xsd/hive/msg/version/
      <http://www.i2b2.org/xsd/hive/msg/version/> " xmlns:tns="http://ws.pm.i2b2.harvard.edu"
      <http://ws.pm.i2b2.harvard.edu%22/> >
      <message_header>
      <i2b2_version_compatible>1.1</i2b2_version_compatible>
      <hl7_version_compatible>2.4</hl7_version_compatible>
      <sending_application>
      <application_name>PM Cell</application_name>
      <application_version>1.601</application_version>
      </sending_application>
      <sending_facility>
      <facility_name>i2b2 Hive</facility_name>
      </sending_facility>
      <receiving_application>
      <application_name>PM Cell</application_name>
      <application_version>1.601</application_version>
      </receiving_application>
      <receiving_facility>
      <facility_name>i2b2 Hive</facility_name>
      </receiving_facility>
      <datetime_of_message>2012-01-10T11:45:15.133-08:00</datetime_of_message>
      <message_control_id>
      <message_num>2F95z4i6m20Kg1sF6i033</message_num>
      <instance_num>1</instance_num>
      </message_control_id>
      <processing_id>
      <processing_id>P</processing_id>
      <processing_mode>I</processing_mode>
      </processing_id>
      <accept_acknowledgement_type>AL</accept_acknowledgement_type>
      <application_acknowledgement_type>AL</application_acknowledgement_type>
      <country_code>US</country_code>
      <project_id>undefined</project_id>
      </message_header>
      <response_header>
      <result_status>
      <status type="DONE">PM processing completed</status>
      </result_status>
      </response_header>
      <message_body>
      <ns4:configure>
      <environment>DEVELOPMENT</environment>
      <helpURL>http://www.i2b2.org</helpURL> <http://www.i2b2.org%3c/helpURL%3e>
      <user>
      <full_name>Michael Ford</full_name>
      <user_name>a447738</user_name>
      <password token_ms_timeout="1800000" is_token="true">SessionKey:ZdD3waZjenforGYD3jRH</password>
      <domain>development</domain>
      <is_admin>false</is_admin>
      <project id="Demo">
      <name>VDW Sandbox</name>
      <wiki>Demo</wiki>
      <path>/Demo</path>
      <role>DATA_LDS</role>
      <role>DATA_DEID</role>
      <role>ADMIN</role>
      <role>USER</role>
      <role>DATA_OBFSC</role>
      <role>DATA_PROT</role>
      <role>MANAGER</role>
      <role>DATA_AGG</role>
      </project>
      </user>
      <domain_name>development</domain_name>
      <domain_id>i2b2</domain_id>
      <active>true</active>
      <cell_datas>
      <cell_data id="CRC">
      <name>Data Repository</name>
      <url>http://dor-ti2b2:9090/i2b2/rest/QueryToolService/</url> <http://dor-ti2b2:9090/i2b2/rest/QueryToolService/%3c/url%3e>
      <project_path>/</project_path>
      <method>REST</method>
      <can_override>true</can_override>
      </cell_data>
      <cell_data id="FRC">
      <name>File Repository </name>
      <url>http://dor-ti2b2:9090/i2b2/services/FRService/</url> <http://dor- ti2b2:9090/i2b2/services/FRService/%3c/url%3e>
      <project_path>/</project_path>
      <method>SOAP</method>
      <can_override>true</can_override>
      </cell_data>
      <cell_data id="ONT">
      <name>Ontology Cell</name>
      <url>http://dor-ti2b2:9090/i2b2/rest/OntologyService/</url> <http://dor- ti2b2:9090/i2b2/rest/OntologyService/%3c/url%3e>
      <project_path>/</project_path>
      <method>REST</method>
      <can_override>true</can_override>
      </cell_data>
      <cell_data id="WORK">
      <name>Workplace Cell</name>
      <url>http://dor-ti2b2:9090/i2b2/rest/WorkplaceService/</url> <http://dor- ti2b2:9090/i2b2/rest/WorkplaceService/%3c/url%3e>
      <project_path>/</project_path>
      <method>REST</method>
      <can_override>true</can_override>
      </cell_data>
      </cell_datas>
      <global_data />
      </ns4:configure>
      </message_body>
      </ns2:response>
      any ideas or help on how I can debug this issue ?
      thanks in advance
      Michael C. Ford
      Application Development Project Manager
      Kaiser Permanente
      Division of Research
      2000 Broadway
      Oakland, CA 94612
      510.891.3576 (office)
      925.366.7730 (mobile phone) Michael.C.Ford.kp <Michael.C.Ford.kp>
      kp.org/thrive <http://kp.org/thrive <http://kp.org/thrive> >
      Thank you.


      NEXT EMAIL
      From:Mendis, Michael E.
      Sent:Wednesday, January 11, 2012 12:46 AM
      To: Peter Beninato; Patibandla, Nandan; i2b2 AUG Members
      Subject:RE: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Glad all is well. The operators GE and LE are working as designed, but the issue is if the c_name has a '<' in it which for the Less than might be possible
      ( something < 10ml ) in the name, than the webclient did not escape is correctly and send that to the CRC. Which could not parse because it was not valid XML. This fix will be part of .03
      Thanks again mike

      From: Peter Beninato [beninato.ohsu.edu] Sent: Tue 1/10/2012 7:40 PM
      To: Peter Beninato; Patibandla, Nandan; i2b2 AUG Members
      Subject: RE: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Hi,
      Mike Mendes suggested I examine the file size which led back to my source zips and the time stamp.
      Sorry for the confusion, but in fact, I am running v1.6.02 downloaded on
      12/16/2011 ( I had downloaded 1.6.01 on 12/6).
      As indicated, I did a test yesterday against a lab in our install (Hemoglobin
      A1C), and was able to confirm that all the operators were working correctly.
      Fyi.
      Peter

      From: Peter Beninato [beninato.ohsu.edu] Sent: Tuesday, January 10, 2012 11:27 AM
      To: Patibandla, Nandan; members.i2b2aug
      Subject: RE: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Hi,
      I'm on version 1.6.02 which was downloaded on 06 Dec. 2011.
      I tested the operators yesterday, and looked at sql clob in qt_query_master, and they all functioned as expected.
      If the version running is also 1.6.02 but downloaded on a different day, is it possible that multiple builds are masquerading as the same version?
      Peter

      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent: Tuesday, January 10, 2012 10:27 AM[ To: i2b2 AUG Members|mailto:members@i2b2aug.org]
      Subject: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not
      working
      Hi Mike,
      We implemented the metadata xml for one of the lab as a test case and when we check the option " By value" and select the "Less than" or "Less Than or Equal To" option in the webclient, the query keeps on running with out giving any
      error message in the Jboss log but can see the attached in the Message log for the "runQueryInstance_fromQueryDefinition".
      Can you please let me know if anything should be fixed.
      Thank you
      Nandan



      NEXT EMAIL
      From:Mendis, Michael E.
      Sent:Tuesday, January 10, 2012 3:17 PM
      To: Patibandla, Nandan; Peter Beninato; i2b2 AUG Members
      Subject:Re: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      I believe that it is actually a webcllient issue, when it send the request to the crc, it looks like the item_name that it got from the ontology needs to escape out the <. As in the following:
      <item_name>Calcium < 20 mg/dl</item_name>
      Mike

      On 1/10/12 2:58 PM, "Patibandla, Nandan" <Nandan.Patibandla.childrens.harvard> wrote:
      Hi Mike,
      I have the latest version of both the webclient and the src but still getting the error on LTE and LT. Thank you
      Nandan

      From: Mike Mendis [mmendis.partners]
      Sent: Tuesday, January 10, 2012 2:48 PM
      To: Peter Beninato; Patibandla, Nandan; i2b2 AUG Members
      Subject: Re: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      It might be, I looked at the build date on the i2b2 site, for the server code and it is: Dec 13 09:43 i2b2core?src?1602.zip
      Dec 13 09:40 i2b2webclient?1602.zip
      Also on the software site, the release was on December 15, But if you did get the software somehow on the 6th, I would suggest getting the latest. Maybe our staging web server got pushed to production by accident.
      mike

      On 1/10/12 2:27 PM, "Peter Beninato" <beninato.ohsu> wrote: Hi,
      I'm on version 1.6.02 which was downloaded on 06 Dec. 2011.
      I tested the operators yesterday, and looked at sql clob in qt_query_master, and they all functioned as expected. If the version running is also 1.6.02 but downloaded on a different day, is it possible that multiple builds are
      masquerading as the same version?
      Peter

      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent: Tuesday, January 10, 2012 10:27 AM
      To: i2b2 AUG Members
      Subject: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Hi Mike,
      We implemented the metadata xml for one of the lab as a test case and when we check the option " By value" and select the "Less than" or "Less Than or Equal To" option in the webclient, the query keeps on running with out giving any error message in the Jboss log but can see the attached in the Message log for the "runQueryInstance_fromQueryDefinition".
      Can you please let me know if anything should be fixed.
      Thank you
      Nandan



      NEXT EMAIL
      From: Robert Schuff [schuffr.ohsu]
      Sent:Tuesday, January 10, 2012 3:13 PM
      To: i2b2 AUG Members
      Subject:Re: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Oregon Clinical & Translational Research Institute Oregon Health & Science University http://www.octri.org/

      From: "Mendis, Michael E." <MMENDIS.PARTNERS>
      Date: Tue, 10 Jan 2012 11:48:12 ?0800
      To: Peter Beninato <beninato.ohsu>, "Patibandla, Nandan" <Nandan.Patibandla.childrens.harvard>, "i2b2 AUG Members" <i2b2 AUG Members>
      Subject: Re: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      It might be, I looked at the build date on the i2b2 site, for the server code and it is:
      Dec 13 09:43 i2b2core?src?1602.zip
      Dec 13 09:40 i2b2webclient?1602.zip
      Also on the software site, the release was on December 15, But if you did get the software somehow on the 6th, I would suggest getting the latest. Maybe our staging web server got pushed to production by accident.
      Mike

      On 1/10/12 2:27 PM, "Peter Beninato" <beninato.ohsu> wrote:
      Hi,
      I'm on version 1.6.02 which was downloaded on 06 Dec. 2011.
      I tested the operators yesterday, and looked at sql clob in qt_query_master, and they all functioned as expected. If the version running is also 1.6.02 but downloaded on a different day, is it possible that multiple builds are masquerading as the same version?
      Peter


      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent: Tuesday, January 10, 2012 10:27 AM
      To: i2b2 AUG Members
      Subject: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Hi Mike,
      We implemented the metadata xml for one of the lab as a test case and when we check the option " By value" and select the "Less than" or "Less Than or Equal To" option in the webclient, the query keeps on running with out giving any error message in the Jboss log but can see the attached in the Message log for the "runQueryInstance_fromQueryDefinition".
      Can you please let me know if anything should be fixed.
      Thank you
      Nandan



      NEXT EMAIL
      From:Phillips, Lori C.
      Sent:Tuesday, January 10, 2012 3:01 PM
      To:Patibandla, Nandan; Mendis, Michael E.; Peter Beninato; i2b2 AUG Members
      Subject:RE: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working

      Nandan,
      It would be helpful if you could send your metadata_xml for this concept and the query request that is being sent to the CRC.
      Also, if you use a lab concept from the demo set that we provide, do you have the same problem? Thanks
      Lori Phillips


      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent: Tuesday, January 10, 2012 2:59 PM
      To: Mendis, Michael E.; Peter Beninato; i2b2 AUG Members
      Subject: RE: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Hi Mike,
      I have the latest version of both the webclient and the src but still getting the error on LTE and LT. Thank you
      Nandan

      From: Mike Mendis [mmendis.partners]
      Sent: Tuesday, January 10, 2012 2:48 PM
      To: Peter Beninato; Patibandla, Nandan; i2b2 AUG Members
      Subject: Re: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      It might be, I looked at the build date on the i2b2 site, for the server code and it is: Dec 13 09:43 i2b2core?src?1602.zip
      Dec 13 09:40 i2b2webclient?1602.zip
      Also on the software site, the release was on December 15, But if you did get the software somehow on the 6th, I would suggest getting the latest. Maybe our staging web server got pushed to production by accident.
      mike

      On 1/10/12 2:27 PM, "Peter Beninato" <beninato.ohsu> wrote:
      Hi,
      I'm on version 1.6.02 which was downloaded on 06 Dec. 2011.
      I tested the operators yesterday, and looked at sql clob in qt_query_master, and they all functioned as expected. If the version running is also 1.6.02 but downloaded on a different day, is it possible that multiple builds are masquerading as the same version?
      Peter

      From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard]
      Sent: Tuesday, January 10, 2012 10:27 AM
      To: i2b2 AUG Members
      Subject: Metadata XML for LABS: Less Than and Less THan or Equal To Opions not working
      Hi Mike,
      We implemented the metadata xml for one of the lab as a test case and when we check the option " By value" and select the "Less than" or "Less Than or Equal To" option in the webclient, the query keeps on running with out giving any error message in the Jboss log but can see the attached in the Message log for the "runQueryInstance_fromQueryDefinition".
      Can you please let me know if anything should be fixed.
      Thank you
      Nandan



      NEXT EMAIL
      From:Mendis, Michael E.
      Sent:Tuesday, January 10, 2012 12:27 PM
      To: Patibandla, Nandan; i2b2 AUG Members
      Subject:Re: Special Characters like '[' in the concept path producing errors
      Nandan,
      Can you send me the xml request. The ] should be supported. Also what verison are you running.
      mike

      On 1/10/12 12:03 PM, "Patibandla, Nandan" <Nandan.Patibandla.childrens.harvard> wrote:
      Hi i2b2 group,
      We have about 1000 ontologies in the i2b2 table with the special character '[' in the concept path. In case of running a query with this particular ontologies, the webclient is producing 'Error' in red in the query result.
      I was just wondering whether there is any fix for this or we should have to replace this with any other character.
      Thank you
      Nandan



      NEXT EMAIL
      From: Henderson, Darren W [darren.henderson.uky]
      Sent:Monday, January 09, 2012 11:05 AM
      To: i2b2 AUG Members
      Subject:RE: i2b2 - v1.6.02 - encounter set
      Shawn,
      On a side?note, when a user chooses an encounter set result type, in the server log I'm seeing batch size commits of only
      500 rows to the qt_patient_enc_collection table. Is there a server control variable somewhere in the configuration files to increase this batch size to reduce the number of transactions required to store the encounter set?
      Darren W. Henderson
      Institute for Pharmaceutical Outcomes and Policy
      College of Pharmacy
      University of Kentucky
      789 S. Limestone Rm. 182
      Lexington, KY 40536 (859) 323?7146
      (859) 967?4914


      From: Murphy, Shawn N. [SNMURPHY.PARTNERS]
      Sent: Sunday, January 08, 2012 7:06 AM
      To: Peter Beninato; i2b2 AUG Members
      Subject: RE: i2b2 - v1.6.02 - encounter set
      Hi Peter,
      The encounter set will be the entire set for the patient if there are no query constraints on encounter timing, i.e. if the facts could occur at any time in the patient's life. If the query is constrained so all the facts must occur within a specific encounter, only the qualifying encounter_nums should be returned.
      Thanks, Shawn.

      From: Peter Beninato [beninato.ohsu]
      Sent: Friday, January 06, 2012 4:31 PM
      To: Peter Beninato; i2b2 AUG Members members
      Subject: RE: i2b2 - v1.6.02 - encounter set
      A slightly different encounter_set question...
      Is the intent of the encounter set to return all of the encounters for the patient in the patient set?
      select count(distinct encounter_num)
      fromobservation_fact
      where patient_num IN ( select distinct patient_num from observation_fact
      where concept_cd LIKE 'DEM|MARITAL:married'
      )

      From: Peter Beninato
      Sent: Friday, January 06, 2012 1:24 PM
      To: i2b2 AUG Members members
      Subject: i2b2 - v1.6.02 - encounter set
      Hi,
      I ran a query against the demo data set for Demographics::Marital Status::Married as a user with de?identified privledges.
      The query returns a patient set of 27.
      In the Previous Query section, when I expand the patient set I see 27 patients. The ids correspond to the patient_num.
      My question(s) concern the encounter set.
      1) Where do the Ids PR CRC_ID ? ### come from?
      2) In observation fact there are also 27 distinct encounter_nums, but in qt_patient_enc_collection for that
      result_instance_id there is count of distinct encounter_num of 2463. Shouldn't there only be 27?
      Peter Beninato – OCTRI DW Developer
      Oregon Health & Science University
      503-494-9985 beninato.ohsu


      NEXT EMAIL
      From: Russ Waitman [rwaitman.kumc]
      Sent:Thursday, January 05, 2012 5:37 PM
      To: Bill Adams; i2b2 AUG Members
      Subject:Re: i2b2 User Agreement
      Hi Bill,
      Yes. Our hospital and clinics are separate from our university and we made sure to establish a governance process and system access and data use agreements. Our materials are organized at the bottom of this link under "Governance":
      http://informatics.kumc.edu/work/wiki/HERON
      Russ Waitman
      Associate Professor
      Director of Medical Informatics Department of Biostatistics University of Kansas Medical Center
      913-945-7087
      rwaitman.kumc http://informatics.kumc.edu

      "Adams, Bill" <Bill.Adams.bmc> 1/5/2012 4:11 PM
      Dear Colleagues,
      We are at last launching our i2b2 web?client as a general research tool. Our current web?client restricts users to aggregate counts only. For this type of access I would be curious to know if User Agreements are routinely signed before granting access and if so, would also appreciate the opportunity to review Agreements used at other institutions if others are willing to share.
      Thanks in advance,
      Bill Adams, BU?CTSI Director of Clinical Research Informatics



      NEXT EMAIL
      From:Phillips, Lori C.
      Sent:Thursday, January 05, 2012 3:15 PM
      To: Keith Bobitt; i2b2 AUG Members
      Subject:RE: Query building methods
      I am not exactly sure what you are asking for but have a look at the project edu.harvard.i2b2.xml
      It contains all the xml definitions of the messages documented in each cell's messaging document.


      From: Keith Bobitt [kbobitt.uab] Sent: Thursday, January 05, 2012 2:56 PM[ To: i2b2 AUG Members|mailto:members@i2b2aug.org]
      Subject: Query building methods
      Could someone point me towards the i2b2 API's used with the RESTful web service inside JBoss and used for programmatically building the xml structure used to
      query the database? Also where would I find the XML definition of this structure? After looking through documentation and some of the source, I have yet to recognize them.
      Thanks, Keith Bobitt
      Biomedical Informatics, UAB Center for Clinical and Translational Science
      Office: (205) 934-9522 | Cell: (205) 215-8400 | kbobitt.uab




      NEXT EMAIL
      From: Bhargav Adagarla [badagarla.kumc]
      Sent:Wednesday, January 04, 2012 5:36 PM
      To: i2b2 AUG Members
      Subject:Registering a web plugin
      Hello all,
      Could some one give me pointers (or any available documentation) on how to register a web plugin in the i2b2_loader.js file in the webclient installation folder (i2b2/js-i2b2)? I understand, it should be in the following format
      { code: "ExampHello", forceLoading: true,
      forceConfigMsg: { params: [] }, forceDir: "cells/plugins/examples"
      },
      I am trying to install the R-Engine plugin that Daniele and team developed (http://code.google.com/p/i2b2-r-engine- project/) and registering the plugin is one of the steps. Thanks.
      Regards,
      Adagarla, Bhargav Srinivas

      Bhargav Adagarla 12/20/2011 11:36 AM
      Hello,
      I am Bhargav Adagarla, (from University of Kansas Medical Center) trying to install the R-Engine plugin that Daniele and team developed (http://code.google.com/p/i2b2-r-engine-project/).
      When I was trying to build it, according to the instructions: (http://code.google.com/p/i2b2-r-engine-project/wiki/HowTo)
      the code fails at ant -f master_build.xml build-all with the following error message:
      jaxb_gen:
      [java] Exception in thread "main" java.lang.NullPointerException
      [java]at com.sun.tools.xjc.reader.internalizer.Internalizer.buildTargetNodeMap(Internalizer.java:1
      87)
      [java]at com.sun.tools.xjc.reader.internalizer.Internalizer.buildTargetNodeMap(Internalizer.java:2
      71)
      [java]at com.sun.tools.xjc.reader.internalizer.Internalizer.transform(Internalizer.java:118)
      [java]at
      com.sun.tools.xjc.reader.internalizer.Internalizer.transform(Internalizer.java:80) [java]at
      com.sun.tools.xjc.reader.internalizer.DOMForest.transform(DOMForest.java:432)
      [java]at com.sun.tools.xjc.ModelLoader.buildDOMForest(ModelLoader.java:326) [java]at com.sun.tools.xjc.ModelLoader.loadXMLSchema(ModelLoader.java:358) [java]at com.sun.tools.xjc.ModelLoader.load(ModelLoader.java:151)
      [java]at com.sun.tools.xjc.ModelLoader.load(ModelLoader.java:97) [java]at com.sun.tools.xjc.Driver.run(Driver.java:293)
      [java]at com.sun.tools.xjc.Driver.run(Driver.java:174) [java]at com.sun.tools.xjc.Driver._main(Driver.java:99) [java]at com.sun.tools.xjc.Driver.access$000(Driver.java:57) [java]at com.sun.tools.xjc.Driver$1.run(Driver.java:79)
      BUILD FAILED
      /home/badagarla/REngine/it.fsm.i2b2.recell/it.fsm.i2b2.rengine/master_build.xml:8: The following error occurred while executing this line:
      /home/badagarla/REngine/it.fsm.i2b2.recell/it.fsm.i2b2.common/build.xml:55: Java returned: 1
      I was wondering if this was an error you had encountered or an error that you would expect and have pointers as to how I can resolve it. Any help would be appreciated.
      I have also attached a more detailed log from ant. Thanks. Regards,
      Adagarla, Bhargav Srinivas
      Clinical Application Administrator
      University of Kansas - Medical Center



      NEXT EMAIL
      From:Phillips, Lori C.
      Sent:Wednesday, January 04, 2012 4:07 PM
      To:Dan Connolly
      Cc: i2b2 AUG Members
      Subject:RE: documentation on age concepts in i2b2 1.6?
      Dan,
      I am not sure where you found metadata installation scripts for demo2/.
      To my knowledge, we stopped providing project demo2 scripts in Release 1.6.
      Lori


      From: Dan Connolly [dconnolly.kumc]
      Sent: Tuesday, January 03, 2012 3:49 PM
      To: Phillips, Lori C.
      Cc: i2b2 AUG Members
      Subject: RE: documentation on age concepts in i2b2 1.6?
      On Thu, 2011-12-22 at 13:27 -0500, Phillips, Lori C. wrote:
      Dan,
      Where did you get the oracle script from?
      This should contain sysdate, not getdate(), in the c_dimcode. (getdate is used in sqlserver).
      Ah... good question... that leads to part of the problem.
      The metadata in i2b2metadata is OK, but in i2b2metadata2 (which we use as the basis of our build), it's not. I traced it down to demo2/scripts/oracle/i2b2_metadata_demographics_insert_data.sql ; i.e.:
      grep getdate edu.harvard.i2b2.data/Release_1-
      6/NewInstall/Metadata/demo2/scripts/oracle/i2b2_metadata_demographics_insert_data.sql|hea d -3
      VALUES(4, '\i2b2\Demographics\Age\>= 65 years old\108\', ' 108 years old', 'N', 'LA ', NULL, 'DEM|AGE:108', NULL, 'patient_num', 'patient_dimension', 'birth_date', 'N',
      'BETWEEN', 'getdate() - (365.25 * 109) + 1 AND getdate() - (365.25 * 108) + 1', NULL,
      'Demographics \ Age \ >= 65 years old \ 108 years old', '@', TO_DATE('2007-04-10
      00:00:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'DEM2FACT CONVERT', NULL);
      VALUES(4, '\i2b2\Demographics\Age\>= 65 years old\109\', ' 109 years old', 'N', 'LA ',
      NULL, 'DEM|AGE:109', NULL, 'patient_num', 'patient_dimension', 'birth_date', 'N',
      'BETWEEN', 'getdate() - (365.25 * 110) + 1 AND getdate() - (365.25 * 109) + 1', NULL,
      'Demographics \ Age \ >= 65 years old \ 109 years old', '@', TO_DATE('2007-04-10
      00:00:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'DEM2FACT CONVERT', NULL);
      VALUES(4, '\i2b2\Demographics\Age\>= 65 years old\110\', ' 110 years old', 'N', 'LA ', NULL, 'DEM|AGE:110', NULL, 'patient_num', 'patient_dimension', 'birth_date', 'N',
      'BETWEEN', 'getdate() - (365.25 * 111) + 1 AND getdate() - (365.25 * 110) + 1', NULL,
      'Demographics \ Age \ >= 65 years old \ 110 years old', '@', TO_DATE('2007-04-10
      00:00:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'DEM2FACT CONVERT', NULL);

      In 1.6 we demonstrated the use of metadata that is computed dynamically .. meaning if you want run a query for all 10 year olds, the age is computed from the patient_dim as you see below. In 1.4 this was done by placing a static 'fact' into the obs_fact table. In this case it doesnt matter when you run the query, the patient is always listed as a 10 year old (unless the record is modified and updated).
      If you want to run age facts the same way you did in 1.4, use the 1.4 age related metadata that refers to concept_dimension based facts.

      OK, that's one option we can consider.
      Lori


      From: Dan Connolly [dconnolly.kumc]
      Sent: Thursday, December 22, 2011 10:33 AM
      To: i2b2 AUG Members
      Subject: documentation on age concepts in i2b2 1.6?
      We're loading age facts just like we used to with 1.4, but i2b2 1.6 seems to ignore them. It seems to go by birthdate:
      C_FACTTABLECOLUMN C_TABLENAME C_COLUMNNAME C_COLUMNDATATYPE C_OPERATO
      patient_numpatient_dimension birth_dateNBETWEEN

      When I run an age query, I get: java.sql.SQLException: ORA-00904: "GETDATE": invalid identifier
      (full request/response XML attached)
      I suspect I missed some documentation on this, though I remember something about it from a presentation at an AUG meeting a while back.
      I read the 1.602 release notes, but they only seemed to discuss the differences between 1.601 and
      1.602.
      Is there something about the differences between 1.4 and 1.6 (or at least: between 1.5 and 1.6) that I can read? I just skimmed some of the CRC and Metadata docs, and I don't see anything about this.
      for reference: this is #780 in our trac.
      Dan Connolly, KUMC Medical Informatics
      913-945-6741



      NEXT EMAIL
      From: Dan Connolly [dconnolly.kumc]
      Sent:Wednesday, January 04, 2012 3:52 PM
      To: i2b2 AUG Members
      Subject:Re: Compatibility between queries, metadata between 1.4 and 1.6?
      On Tue, 2012-01-03 at 22:37 +0000, Dan Connolly wrote: [...]
      I suppose I can find where old queries are loaded by the web client and get it to make sure panel_timing is set.
      found it: po.timing = i2b2.h.getXNodeVal(qp[i1],'panel_timing'); It seems to work when I change that to:
      po.timing = i2b2.h.getXNodeVal(qp[i1],'panel_timing') || 'ANY';
      A patch is attached.
      Dan Connolly, KUMC Medical Informatics
      913-945-6741



      NEXT EMAIL
      From:Phillips, Lori C.
      Sent:Wednesday, January 04, 2012 11:18 AM
      To: Phillips, Lori C.; Jack London; i2b2 AUG Members
      Subject:RE: WORKPLACE_ACCESS table
      Let me amend this...
      Entries may be added manually, otherwise, Peter's comment is correct: entries are made automatically to the table on initial login. ie. If you don't have a folder one is created the first time you access it.
      Lori

      From: Phillips, Lori C.
      Sent: Wednesday, January 04, 2012 11:13 AM To: Jack London; i2b2 AUG Members
      Subject: RE: WORKPLACE_ACCESS table
      Hi Jack,
      The WORKPLACE_ACCESS table is only used by the WORKPLACE plugin. Entries to it have to be made manually; adding a user &/or assigning them to a project in the admin tool has no affect on this table.
      This table governs what folders appear in an user's workplace plugin.
      Users with ROLE == USER will see unsharable folders assigned to them by project and any sharable folder. Users with ROLE == MANAGER will see all folders for a project.
      Lori

      From: Jack London [jack.london.KimmelCancerCenter] Sent: Wednesday, January 04, 2012 9:33 AM
      To: members.i2b2aug.org members
      Subject: WORKPLACE_ACCESS table
      Can someone explain the purpose of the WORKPLACE_ACCESS table? What is the consequence of a user not being in this table? Does it affect use of web client plugins?
      How is a user added to this table? (Creating a user and assigning them to a project appears to have no affect on this table.)
      We use the web client, v1.6.01. Jack
      Jack London, Ph.D.
      Research Professor Cancer Biology
      Thomas Jefferson University
      Director, Informatics Shared Resource
      Kimmel Cancer Center
      808 BLSB, 233 S. 10th St. Philadelphia, PA 19107
      215-503-4599



      NEXT EMAIL
      From: Peter Beninato [beninato.ohsu]
      Sent:Wednesday, January 04, 2012 11:01 AM
      To:Jack London; i2b2 AUG Members
      Subject:RE: WORKPLACE_ACCESS table

      Not sure if this is completely accurate.
      It is for the Workplace folders. I think there maybe a function upon initial login that creates the entry in the table for the user, so they then have a folder.
      That seems to be the behavior in 1.4.

      From: Jack London [jack.london.KimmelCancerCenter] Sent: Wednesday, January 04, 2012 6:33 AM
      To: members.i2b2aug.org members
      Subject: WORKPLACE_ACCESS table
      Can someone explain the purpose of the WORKPLACE_ACCESS table? What is the consequence of a user not being in this table? Does it affect use of web client plugins?
      How is a user added to this table? (Creating a user and assigning them to a project appears to have no affect on this table.)
      We use the web client, v1.6.01. Jack

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


      NEXT EMAIL
      From: Russ Waitman [rwaitman.kumc] Sent:Tuesday, January 03, 2012 6:05 PM To:Murphy, Shawn N.
      Cc: i2b2 AUG Members; Mendis, Michael E.
      Subject:RE: i2b2 1.6 doesn't seem to run queries made with i2b2 1.4
      Shawn,
      Thank you for the reply. It happens all the time for certain queries. We're planning to release 1.6 on Thursday. We'll then reconfirm these issues and provide more details on our new platform.
      Russ

      "Murphy, Shawn N." <SNMURPHY.PARTNERS> 12/27/2011 11:32 AM
      Hi Russ,
      We aren't able to reproduce 591, is it all the time or sporadic? If sporadic, any ideas about when it happens? Thanks,
      Shawn.

      From: Russ Waitman [rwaitman.kumc]
      Sent: Wednesday, December 21, 2011 5:39 PM
      To: Russ Waitman; Murphy, Shawn N.
      Cc: i2b2 AUG Members
      Subject: Re: i2b2 1.6 doesn't seem to run queries made with i2b2 1.4
      The only other bug that we've verified is still there is
      Ticket 591 i2b2 hangs when you modify the "occurs" criteria multiple times and setvalues are involved
      http://informatics.kumc.edu/work/ticket/591
      We have another bug we want to check but we can't because we can't reuse our test scenario.
      Ticket 497 When you "reuse" queries, you get a different result the second time
      http://informatics.kumc.edu/work/ticket/497
      We'll let people know if we get these resolved. We also had an enhancement below to 1.4 which we plan to fix in 1.6. We'll update the group when that code fix is committed on our site.
      Ticket 243 Make the timeline hover show the name of the concept instead of just concept_cd
      http://informatics.kumc.edu/work/ticket/243

      Russ

      "Russ Waitman" 12/21/11 4:26 PM
      Thanks Shawn. We may have done something wrong and can share details. Otherwise, we're going over noted 1.4 bugs and happy to see them fixed in 1.6.
      Russ

      "Murphy, Shawn N." 12/21/11 4:22 PM
      We'll look into this Russ, would not want that.

      On Dec 21, 2011, at 4:55 PM, "Russ Waitman" <rwaitman.kumc> wrote:
      Hi,
      Dan and Arvinder are closing down on migrating us from 1.4 to 1.6. We're getting excited except when we drag over a query created on 1.4 it causes the client to lock up in 1.6 (http://informatics.kumc.edu/work/ticket/775) . It says locked and stays that way.
      Any ideas? We may not have done the database migration quite right?
      We'd hate to tell people "write down the queries you want to keep and rebuild them after our downtime".

      Russ Waitman
      Director of Medical Informatics
      Associate Professor, Department of Biostatistics
      University of Kansas Medical Center
      913-945-7087 rwaitman.kumc



      NEXT EMAIL
      From: Russ Waitman [rwaitman.kumc] Sent:Tuesday, January 03, 2012 6:03 PM To: i2b2 AUG Members
      Subject:soliciting best practices for auditing i2b2 use
      Hi,
      We're getting some degree of usage at KU of our repository (over 3600 queries, 70 users since ~ Jan 2010) and have an oversight process in place which we want to augment with more than ad hoc auditing.
      We have some initial ideas and wanted to see if other people were doing.
      Background
      We only support the web client. Our current process allows faculty and executives to sponsor use by students and staff http://informatics.kumc.edu/work/wiki/HERONTrainingMaterials (done with REDCap). We host a de-identified repository with 0-365 days of date shifting. We have all users sign a systems access agreement but grant limited data set access to users so they can use plugins like the timeline and new plugins we hope to develop for preliminary analysis. Students
      and staff use i2b2 under the supervision of the faculty/executive and should stay within the scope of the "preliminary to research" topic outlined by the faculty. The HERON Data Request Oversight Committee (DROC) meets monthly but members can approve requests for system access and data use as they come in electronically. If there are no outstanding requests, the meeting then is used to provide status updates, define policy (such as auditing), and shepherd
      the system's adoption.
      Primary Goal
      Focus on cases where there is heightened risk of re-identification.
      Possible Auditing Approaches
      1.) We could set up processes to look at all Patient Sets (not just counts) which were returned to users that contained fewer than 5 patients. We'd then feed these queries into a readable report for an auditing role overseen by the DROC.
      If it looks suspicious, we'd conduct a fact finding meeting with the user and their sponsor (if they were a student or staff) within the week so use is still fresh in their mind. Waiting a month is too long for people to remember what they
      were doing. Based on our hospital privacy official's experience, you also want to close down in a week to lower the anxiety level since most use is appropriate.
      Does anyone have rules of thumb for how many queries they review or how they filter them down to a manageable number?
      2.) We would also compliment this with automated reports to sponsors and students/staff which would highlight what queries their students/staff have conducted since they are responsible for oversight as well.
      That would also put people on notice that our auditing process is in place and active.
      Remind them that if some students and staff haven't used the system in months that they can de-activate their sponsorship.
      We might also incorporate a survey so we can understand how the system is used and couch this contact as a feedback opportunity for us from the investigator. Examples:
      i. Was your query for: a) feasibility determination for new grant proposal, b) patient recruitment, c) supporting a specific study you have planned, d) fishing/hypotheses generation,
      ii. Was their data you wish we had incorporated that would significantly enhance your research agenda? Click here to
      request a project meeting or click here to give us your purchase order....

      Related Question
      3. Does i2b2 have a mechanism to audit plugin use? The reason that would be useful is to understand if a user pulls back a patient set but starts fishing in variables unrelated to their research topic and seems to be attempting to re- identify the patient.
      We'd welcome feedback you have or thought on your processes. We'll probably shoot for incorporating a more formal auditing process into our February meeting.
      Sincerely,
      Russ Waitman
      Associate Professor
      Director of Medical Informatics
      Department of Biostatistics
      University of Kansas Medical Center
      913-945-7087 rwaitman.kumc http://informatics.kumc.edu



      NEXT EMAIL
      From: Dan Connolly [dconnolly.kumc] Sent:Tuesday, January 03, 2012 5:37 PM To: i2b2 AUG Members
      Subject:Re: Compatibility between queries, metadata between 1.4 and 1.6?
      Attachments:failed.xml; succeded.xml

      We played around with TABLE_ACCESS and got the old query to work, but only after addressing another issue:
      We looked at the difference between using an old query, which failed,
      and re-creating the same query by dragging the concept over, which succeeded
      (details attached). We discovered that in the failure case, the client sends:
      <panel_timing>undefined</panel_timing>
      If I fiddle with the "Treat all groups independently" toggles before sending the old query, the client seems to set panel_timing to something the back end can deal with (ANY).
      I suppose I can find where old queries are loaded by the web client and get it to make sure panel_timing is set. Any help finding the relevant bit of code
      is appreciated.

      On Tue, 2012-01-03 at 20:20 +0000, Dan Connolly wrote:
      Hi,
      Our saga to get queries from our 1.4 installation to work on our 1.6 installation continues. We restored the old QT_* stuff, so the client no longer freezes.
      But now it gives 0 results.
      Looking at the XML messages today in our 1.6 instance, I get:

      <item_key>\\i2b2_DEMO\i2b2\Demographics\HICTR Participant\</item_key>
      vs when I re-use the query from August when I was using our 1.4 instance:

      <item_key>\\i2b2\i2b2\Demographics\HICTR Participant\</item_key>
      I see some corresponding changes in the TABLE_ACCESS table. Any suggestions?
      If I want 1.4 queries to continue to work, am I going to be able to use the metadata that comes with 1.6, or would you suggest that I splice in stuff from 1.4? It looks like splicing in the 1.4 stuff is going to be necessary to get our age data to continue to work.
      Dan Connolly, KUMC Medical Informatics
      913-945-6741



      NEXT EMAIL
      From: Phillip Reeder [Phillip.Reeder.uth.tmc]
      Sent:Tuesday, January 03, 2012 3:02 PM
      To: Dan Connolly; jl99.leicester.ac.uk
      Cc: i2b2 AUG Members
      Subject:RE: I2b2 using SSL
      I think the only additional step we take at UT Houston is to use AJP Port forwarding from Apache to JBoss. This allows apache to handle the SSL for JBoss as well as securing the webclient with SSL.
      The only issues we have run into is java trust issues with self signed certs when using the thick client. Phillip

      From: Dan Connolly [dconnolly.kumc]
      Sent: Tuesday, January 03, 2012 1:53 PM
      To: jl99.leicester.ac.uk
      Cc: i2b2 AUG Members
      Subject: Re: I2b2 using SSL

      On Wed, 2011-12-14 at 18:22 +0000, Lusted, Jeff wrote:
      ...I would like to ask members whether they have experience of securing i2b2 using SSL? Was it straightforward? And what problems were encountered, if any?
      We use SSL. I'm not sure I'd call it straightforward, but looking over my notes shows no problems that are i2b2- specific.
      It's just that there are a lot of moving parts: i2b2 in JBoss, php in apache, SSL certificates, enterprise firewall rules, etc.
      In our set-up, (a) the jboss server that holds the i2b2 hive only accepts connections from localhost, and
      (b) the web client index.php is accessed via an apache server running SSL. My notes show I used this article as a guide:
      Step by Step: Configuring SSL Under Apache. by Juliet Kemp
      03/04/2008

      Dan Connolly, KUMC Medical Informatics
      913-945-6741



      NEXT EMAIL
      From: Henderson, Darren W [darren.henderson.uky]
      Sent:Tuesday, January 03, 2012 2:07 PM
      To: Phillip Reeder; i2b2 AUG Members
      Subject:RE: Query optimization for large datasets

      I have continued to tweak and fix some bugs within the hack itself, but merely for my own purposes. There are a couple of hurdles that I'm trying overcome. I'm still in the process of adding the occurs feature that I originally spoke to in my first message to the left outer syntax. I'm also struggling with the #DX table that is created as sometimes it simply isn't due to the options selected in the query UI itself.
      If the query timing is left as "ANY" and only a number of patients is selected as the result, an ERROR is returned simply because the DX table does not get created in the background, so my query language that inserts into the DX table fails to return a set. I assume the system in the background is waiting for the response from a generated count(patient_num) type query, and not waiting for the DX table to receive the result set, to then run a count on. If the query timing is set to same encounter, number of patients can be the only result selected because the DX table does get created when SAME timing is selected.
      My current fix for that is just to make the CheckSkipTempTable.java file always return false. So a DX table is always made.

      From: Phillip Reeder [Phillip.Reeder.uth.tmc]
      Sent: Tuesday, January 03, 2012 1:36 PM
      To: Henderson, Darren W; i2b2 AUG Members
      Subject: RE: Query optimization for large datasets
      This sounds like it could be a very good enhancement for the SQL Server version of i2b2. Has anyone else tried anything like this? Are there any other SQL Server specific tweaks that others have made to improve performance? Either i2b2 code level or SQL Server side?
      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

      From: Henderson, Darren W [darren.henderson.uky]
      Sent: Friday, December 23, 2011 5:23 PM
      To: i2b2 AUG Members
      Subject: Query optimization for large datasets
      Greetings all. I am a database analyst working at the University of Kentucky for our CTSA/BMI group. For the better part of the past 9 months or so I've been tasked with bringing i2b2 up for our clinicians and researchers on campus. We are currently on i2b2 v 1.6 with a SQL server 2008 instance backing it with 8 cores and 32gb of RAM attached to a SAN. I have worked here as one of our research database gurus (read data mining monkey) for several years now.
      Our current desires are to use i2b2 to provide access to clinical data from the University Medcenter, as well as access to two large datasets that we currently maintain. We maintain a snapshot of all Medicaid claims for the state of Kentucky for the years 2000?2009, and will soon have 2010 – current available as well. We also have 3 years of the commercial insurance I3 database, 2007?2009. In my work to prepare the Medicaid data and I3 data for use in i2b2 I have come across a couple issues that I would like to discuss, in hopes of learning how others are approaching large scale databases in i2b2. My colleague Daniel Harris participated in a webinar recently led by Shawn Murphy in which I believe the comment was made in passing that Partners currently has an instance of i2b2 with ~1.5 Billion rows. The i3 data is nearing 3B without prescription or lab data in the model. The Medicaid data is ~220M rows in Observation, ~148M in Visit_dimension, for ~2M rows in patient dimension.
      The main issue that I have come across that prompted me to begin participating in the AUG is query behavior I noticed when an exclusion panel is added to a query. Our instance may be lacking some setting in the files to prompt a more efficient query generation, but what I'm seeing with this kind of query is a dump of the entire visit_dimension into a temporary table where it is then processed one panel concept at a time as a large series of update statements. The SQL server that we are maintaining the data on has a lot of horsepower, but the query ran for 3 hours before failing due to a lack of tempdb space, or transaction log space (the error message was not captured at failure).
      I've been working at an experimental fix so that my first message to the AUG would not simply be a complaint. I wanted to be able to offer something for the developers to consider. I've developed a hack that I kind of shoehorned into the SQL server side of things, so that I would not need to edit as many java files in order to deploy the change. The change is applied to the QT_QUERY_MASTER table. First, the REQUEST_XML field must be altered to varchar(max) instead of the deprecated TEXT datatype. Then the functions that I have attached take the REQUEST_XML that is stored, and with a trigger on the QT_QUERY_MASTER table, generates a field that I added to the table called generated_sql_v2. Then the only change I really had to make in the java files was to add that field as a member of the class that pulls from the qt_query_master. As it turns out, there was already a check in place that says if generated sql is null go generate it, else run it. So by shoehorning my column in its way, your generation never fires (my apologies).
      The function that I developed creates SQL that uses a feature of SQL server known as Common?table expressions.
      Similar syntax is available in Oracle, but I have not developed anything for an Oracle solution since we are SQL server for our i2b2 purposes. Each common table that is built refers to the one above it as the starting point for the joins that take place. I chose to use LEFT OUTER JOIN within each common?table for every concept that is placed in the panel. The reason behind this is that this makes dealing with an exclusion panel much easier and elegant. The predicate at the bottom of the common table is either: WHERE not (or1.patient_num is null AND or2.patient_num is null) for a normal panel or WHERE (or1.patient_num is null AND or2.patient_num is null). So for the normal panel you want all those from the LEFT member of the join where at least one of the right members returned something. For the invert, you want only those where none of the members returned something. The or1, or2, orX aliases are tracked programmatically as I iterate through panel items that exist in the request for the current panel. The document that I attached will show an example of some of the queries that are generated programmatically along with their generation and a timestamp.
      The benefit that I've noticed is that queries that contain exclusions or several concepts are returned much more rapidly. The function does attempt to order the panels using the TOTALNUM field in the metadata, so the first CTE is usually small. The left joins simply make each successive CTE a smaller set.
      I am not suggesting that anyone should attempt to shove my hack into their active systems. This was more an experiment or proof?of?concept so that I could suggest to the development team a cleaner way of accomplishing the exclusion (invert) logic. The current version of the code that I have attached does not provide functionality for occurs constraints yet. I simply did not finish it before I left for the holiday break. I was also unsure about the intent of the occurs modifier. IF it supposed to apply to each concept in a panel independently e.g. this happens 2 times OR this happens 2 times, then it would be fairly easy to program this functionality into the left join by replacing the dimension table joined with an inline view using a group by having.
      I apologize that my first message to the user group is such a mouthful, but I look forward to your feedback, and/or adoption of some or all of the query approach so that i2b2 performs as fast as possible for data sets of all sizes. Or should you already have a fix for query times inflating during exclusions I look forward to it.
      Thank you all for your work and time put into i2b2. We have many people excited here at UK about the prospect of our data being useable for simple things in a much more approachable medium.

      Darren W. Henderson
      Institute for Pharmaceutical Outcomes and Policy
      College of Pharmacy
      University of Kentucky
      789 S. Limestone Rm. 182
      Lexington, KY 40536 (859) 323?7146
      (859) 967?4914



      NEXT EMAIL
      From: Schulte, Gregory [Gregory.Schulte.childrenscolorado]
      Sent:Tuesday, January 03, 2012 12:14 PM
      To: 'Michael.C.Ford.kp'; i2b2 AUG Members
      Subject:RE: I2B2 VM
      Michael,
      I believe the ORA?28000 error indicates you established a connection but the user you were connecting as is locked.
      If you log into your Oracle instance run: select account_status from dba_users where username = '<username you are logging in as>'
      This will indicate if the user is LOCKED or OPEN
      To unlock the user: alter user <username> account unlock; Hope that helps.
      Greg

      From: Michael.C.Ford.kp.org [Michael.C.Ford.kp]
      Sent: Thursday, December 29, 2011 4:57 PM
      To: i2b2 AUG Members
      Subject: I2B2 VM
      So I have my VM running with Desktop access to Centos. I now have a new problem.
      I wanted to connect to our Oracle instances vs. the internal Oracle instance.
      I modified the various XML configurations and I received the following.
      2011-12-28 17:31:04,938 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
      org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: ORA-28000: the account is locked)
      Ok I don't have a client got it, did a little research and found I needed Instant client 11.2 from[http://oss.oracle.com/projects/php/files/EL5/i386/|http://oss.oracle.com/projects/php/files/EL5/i386/]
      did an RPM oracle-instantclient11.2-basic-11.2.0.3.0-1.i386.rpm
      client is now installed at /usr/lib/oracle/11.2/client there I find bin and lib. Now comes my questions.
      it looks like I need to define an Oracle_home would this be /usr/lib/oracle/11.2 or /usr/lib/oracle/11.2/client ?
      I think it would be /usr/lib/oracle/11.2 or /usr/lib/oracle/11.2/client
      Now this needs to go into a script as
      ORACLE_HOME= /usr/lib/oracle/11.2 or /usr/lib/oracle/11.2/client
      export ORACLE_HOME
      would it be .profile under root or tomcat ?
      then I think I need to define a tnsnames.ora under /usr/lib/oracle/11.2 or /usr/lib/oracle/11.2/client/network/admin
      any help in this area would be very much appreciated.
      my hope is to get the VM to the point I can use it as a development platform.

      Michael C. Ford
      Application Development Project Manager
      Kaiser Permanente
      Division of Research
      2000 Broadway
      Oakland, CA 94612
      510.891.3576 (office)
      925.366.7730 (mobile phone)[ Michael.C.Ford.kp|mailto:Michael.C.Ford@kp.org]
      kp.org/thrive
  • No labels