AUG Email 2011-Dec
Last Updated: 12/29/2011
NEXT EMAIL
From: Mendis, Michael E. Sent: Thursday, December 29, 2011 10: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
From: Peter Beninato Sent: Wednesday, December 28, 2011 7:20 PM To: Peter Beninato; i2b2 AUG Members Subject: RE: SOLUTION -i2b2 - v1.6_02 - webclient - admin - Requires
ADMIN role
Whoops... if this step is necessary...
Step 3) Edit index.php
//$pmURL = "http://127.0.0.1:8080/i2b2/rest/PMService/getServices";
$pmURL = "http://127.0.0.1:9090/i2b2/rest/PMService/getServices";
Peter
On 12/28/11 6:37 PM, Peter Beninato 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 thin client 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
NEXT EMAIL
From: Peter Beninato [beninato.ohsu] Sent: Wednesday, December 28, 2011 2:50 PM To: Mendis, Michael E.; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: RE: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Webclient update... Not sure this was valid test?
When I look at the webclient i2b2_config_data.js, it seems that the urlCellPM is pointing to i2b2.org, and not a localhost.
I just copied over the webclient folder, let me troll through the documentation to see what I might need to edit.
urlCellPM: "http://webservices.i2b2.org/i2b2/rest/PMService/",
From: Mike Mendis [mmendis.partners] Sent: Wednesday, December 28, 2011 11:01 AM To: Peter Beninato; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: Re: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
The entries all look correct.
A few things, is this a new install or upgrade? Can you log into the regular webclient without issues? If not, try install curl. Also is php installed on the linux machine
mike
On 12/28/11 1:55 PM, "Peter Beninato" <beninato.ohsu> wrote:
Hi,
I'm wondering if the "@" entry in PM_PROJECT_USER_ROLES for "i2b2" needs anything else like "USER" too? And/or if I should DELETE other entries for "i2b2" from that table?
Below is my i2b2_config_data.js. I changed domain to "i2b2" since that was the domain_id in pm_hive_data. I can change it back. The domain_name is "i2b2demo"
I have also edited the index.php for $pmURL, and added this line, "http://localhost:9090/i2b2/rest/",
to the $WHITELIST.
index.php
//$pmURL = "http://127.0.0.1:8080/i2b2/rest/PMService/getServices"; $pmURL = "http://127.0.0.1:9090/i2b2/rest/PMService/getServices";
i2b2_config_data.js
{
urlProxy: "index.php",
urlFramework: "js-i2b2/",
//------------------------------------------------------------------------------------------
// THESE ARE ALL THE DOMAINS A USER CAN LOGIN TO
lstDomains: [
{ //domain: "i2b2demo",
domain: "i2b2",
name: "i2b2demo",
urlCellPM: "http://localhost:9090/i2b2/rest/PMService/",
allowAnalysis: false,
adminOnly: true,
debug: false
}
]
//------------------------------------------------------------------------------------------}
From: Mike Mendis [mmendis.partners] Sent: Wednesday, December 28, 2011 10:47 AM To: Peter Beninato; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: Re: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
What does your i2b2_config_data.js look like. It does looks like port 9090 is the correct based on that response you got back from the jboss
On 12/28/11 1:28 PM, "Peter Beninato" <beninato.ohsu> wrote:
Hi,
Might there need to be some edits to index.php too?
Like the $pmURL? Should I change the port to 9090. Also, I don't see an entry in the $WHITELIST for something like http://localhost:9090/i2b2/rest
FROM index.php ************************************* $pmURL = "http://127.0.0.1:8080/i2b2/rest/PMService/getServices"; $pmCheckAllRequests = false;
$WHITELIST = array( "http://", "http://127.0.0.1:9090/axis2/rest/", "http://localhost:9090/axis2/rest/", "http://127.0.0.1:7070/i2b2/rest/", "http://localhost:7070/i2b2/rest/", "http://webservices.i2b2.org", "https://webservices.i2b2.org"
);
From: Peter Beninato [beninato.ohsu] Sent: Wednesday, December 28, 2011 10:10 AM To: Mike Mendis; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: RE: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Hi,
After restarts still error.
When I go to http://localhost:9090/i2b2/services/listServices, I see the PM service:
Service EPR : http://localhost:9090/i2b2/services/PMService Service REST epr : http://localhost:9090/i2b2/services/PMService : http://localhost:9090/i2b2/rest/PMService
Service Description : PMService
Service Status : Active Available Operations
getVersion
getServices
However, if I enter URL from js config file: http://localhost:9090/i2b2/rest/PMService/
<soapenv:Fault><faultcode/><faultstring>I can not find a service for this request to be serviced. Check the WSDL and the request URI; nested exception is:
org.apache.axis2.AxisFault: I can not find a service for this request to be serviced. Check the WSDL and the request URI</faultstring><detail/></soapenv:Fault>
If I go to the Service EPR from the listServices: http://localhost:9090/i2b2/services/PMService
And below is what I see in JBOSS log?
09:50:31,141 INFO [Server] JBoss (MX MicroKernel) [4.2.2.GA (build: SVNTag=JBoss_4_2_2_GA date=200710221139)] Started n 26s:475ms 09:59:42,740 ERROR [AxisRESTServlet] org.apache.axis2.AxisFault: I can not find a service for this request to be servic
d. Check the WSDL and the request URI; nested exception is:
org.apache.axis2.AxisFault: I can not find a service for this request to be serviced. Check the WSDL and the re uest URI
From: Mike Mendis [mmendis.partners] Sent: Wednesday, December 28, 2011 9:49 AM To: Peter Beninato; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: Re: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Yes that could
Try poer 9090 for jboss
On 12/28/11 12:46 PM, "Peter Beninato" <beninato.ohsu> wrote:
Hi
In the file i2b2_config_data.js, there is the following line: urlCellPM: http://localhost:8080/i2b2/rest/PMService/
Port 8080 is mapped to OracleXE. I need to check JBOSS port/services... Could this be problem?
Peter
From: Peter Beninato [beninato.ohsu] Sent: Wednesday, December 28, 2011 9:39 AM To: Mike Mendis; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: RE: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Hi,
I did an insert for that entry. Restarted JBOSS/Apache. Even tried a different browser, in case there was cacheing.
Still get that error, about ADMIN role?
Peter
From: Mike Mendis [mmendis.partners] Sent: Wednesday, December 28, 2011 9:15 AM To: Peter Beninato; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: Re: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Yes it needs a project_id of '@'
On 12/28/11 12:11 PM, "Peter Beninato" <beninato.ohsu> wrote:
Hi,
Thanks. I'm installing/upgrading locally. I have user i2b2.
The user i2b2, had entries on two Project_ids: Demo and Demo2, where it has user_role_cd of ADMIN, and status_cd A.
Does there need to be an entry for a Project_ID of "@"?
Peter
From: Mike Mendis [mmendis.partners] Sent: Wednesday, December 28, 2011 9:07 AM To: Peter Beninato; Michael.C.Ford.kp Cc: i2b2 AUG Members Subject: Re: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Are you using the vm image or installing locally. Because the i2b2 account is the admin account and the password is demouser.
In order for a user to be a admin, they need to have the following in the pm_project_user_role table:
Project_ID @
user_id {the user in this case i2b2}
user_role_cd ADMIN
status_cd A
The only variable that would change is the user_id.
mike
On 12/28/11 11:55 AM, "Peter Beninato" <beninato.ohsu> wrote:
Hi,
I don't see a user "admin" in the table pm_user_data, but hey I'll try anything... ahhh, doesn't work.
Curiously, if I logon with "admin" or as i2b2 (which may have admin privledges) and a bad password (other than demouser), I get the same error regardless.
Several standard users have the same encrypted password, presumably "demouser".
Not sure.
Peter
From: Michael.C.Ford.kp [Michael.C.Ford.kp] Sent: Wednesday, December 28, 2011 8:39 AM To: Peter Beninato Cc: Peter Beninato; i2b2 AUG Members Subject: RE: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
You may want to try user admin with the same password
Michael C. Ford
Application Development Project Manager
Kaiser Permanente
Division of Research 2000 Broadway Oakland, CA 94612
kp.org/thrive <http://kp.org/thrive>
Peter Beninato <beninato.ohsu> 12/28/2011 08:29 AM ToPeter Beninato <beninato.ohsu>, i2b2 AUG Members cc SubjectRE: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Hi,
I saw there were some emails about this: Rob Schuff 10/21/2011 Anandhi Sowmyan on 10/21/2011 Askar Ibragimov 11/29/2011
I reach the URL: http://localhost/admin <http://localhost/admin>
But I can't logon due to error: "Requires ADMIN role, please contact your system administrator."
Stuck/Stopped/Halted on installation of v1.6.02 until this can be resolved.
Have not found solution on WIKI.
Has anyone found a solution?
Peter
From: Peter Beninato Sent: Wednesday, December 21, 2011 1:04 PM To: i2b2 AUG Members Subject: i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
Hi,
Working through the local install. As part of the Project_Management_Installation_Guide, Step 4, I can reach the URL: http://localhost/admin <http://localhost/admin>
But when I logon as i2b2, I seem to get an error. In a pop-up it says:
"Requires ADMIN role, please contact your system administrator"
I thought I saw some email in regards to this error. Is there a column missing like "is_admin"?
Thanks for assistance in resolving.
Peter Beninato - OCTRI DW Developer Oregon Health & Science University 503-494-9985 beninato.ohsu <beninato.ohsu>
NEW EMAIL
From: Murphy, Shawn N. Sent: Wednesday, December 28, 2011 11:55 AM To: Peter Beninato; Mendis, Michael E.; Russ Waitman Cc: i2b2 AUG Members Subject: RESOLVED: i2b2 1.6 doesn't seem to run queires maide with i2b2 1.4
Hi Peter,
This issue was resolved and indeed was on that exact track, there were old queries that were unavailable in Russ' new instance whose master_id's were thus not present (just like the deleted queries). The error message is coming back correctly from the server, so to change the hanging behavior needs some web client programming.
Thanks, Shawn.
From: Peter Beninato [beninato.ohsu] Sent: Wednesday, December 28, 2011 11:36 AM To: Mendis, Michael E.; Russ Waitman; Murphy, Shawn N. Cc: i2b2 AUG Members Subject: RE: i2b2 1.6 doesn't seem to run queires maide with i2b2 1.4
Hi,
I saw a "known issue" regarding using old queries. Perhaps this issue is related?
Query: Using old "deleted" previous queries will cause web client to crash
https://community.i2b2.org/wiki/display/releases/1.6.02+Known+Issues
From: Dan Connolly [dconnolly.kumc] Sent: Thursday, December 22, 2011 9:24 AM To: Russ Waitman Cc: i2b2 AUG Members; Murphy, Shawn N. Subject: Re: i2b2 1.6 doesn't seem to run queires maide with i2b2 1.4
I think I found the problem, and it's on our end. It looks like we migrated our workplace data but not our QT tables from our 1.4 installation.
The log shows:
2011-12-22 08:06:17,553 ERROR [edu.harvard.i2b2.crc.dao.CRCDAO] Querydoesn't exists for masterId :[2103]
So I looked in the QT tables, and sure enough, the QT_QUERY_MASTER table doesn't have data corresponding to the queries that fail to run.
Mike, I'm attaching the XML message, just for reference.
From: Mendis, Michael E. [MMENDIS.PARTNERS] Sent: Thursday, December 22, 2011 5:22 AM To: Russ Waitman; Murphy, Shawn N. Cc: i2b2 AUG Members Subject: RE: i2b2 1.6 doesn't seem to run queires maide with i2b2 1.4
If you can provide a screenshot that would be great. Also if you post the XML messages.
thanks
mike
From: Russ Waitman [rwaitman.kumc] Sent: Thu 12/22/2011 3:06 AM To: Mendis, Michael E.; Murphy, Shawn N. Cc: i2b2 AUG Members Subject: RE: i2b2 1.6 doesn't seem to run queires maide with i2b2 1.4
It's all done with the webclient both when they were created in 1.4 and now being played back in 1.6. We'll try to get a screenshot but when you drag it over it goes into a single panel instead of rehydrating into the proper multipanel query.
Russ
"Mendis, Michael E." 12/21/11 9:41 PM
Russ,
When you created the queries in 1.4, where they done using the webclient or the eclipse workbench?
mike
From: Murphy, Shawn N. Sent: Tuesday, December 27, 2011 12:32 PM To: Russ Waitman Cc: Mendis, Michael E.; i2b2 AUG Members Subject: RE: i2b2 1.6 doesn't seem to run queries made with i2b2 1.4
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 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 resultthe 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
On Wed, 2011-12-21 at 16:25 -0600, Russ Waitman wrote:
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.
Shawn Murphy MD
On Dec 21, 2011, at 4:55 PM, "Russ Waitman" 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 rwaitman.kumc
NEXT EMAIL
From: Henderson, Darren W [darren.henderson.uky] Sent: Friday, December 23, 2011 6: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
NEXT EMAIL
From: Mendis, Michael E. Sent: Friday, December 23, 2011 9:18 AM To: Patibandla, Nandan Cc: i2b2 AUG Members Subject: RE: ERROR while running the query
Was this is a new install or a upgrade? If you where upgrading verify that the new paramaters are in crc.properties. the one the error was referring to was: edu.harvard.i2b2.crc.setfinderquery.obfuscation.count.sigma
From: Patibandla, Nandan [Nandan.Patibandla.childrens.harvard] Sent: Fri 12/23/2011 9:01 AM To: Mendis, Michael E. Cc: i2b2 AUG Members Subject: ERROR while running the query
Hi Mike, I just deployed the new version 1.6.02 of i2b2 and was able to successfully able to deploy it, but when I run the query on the webclient(on;y selected the patient count) , I am getting the following error, please let me know why this is occurring.
Thank you
Nandan
2011-12-23 08:53:37,983 ERROR [edu.harvard.i2b2.crc.dao.CRCDAO] Error executing Application property file(crc.properties) missing edu.harvard.i2b2.crc.setfinderquery.obfuscation.count.sigma entry edu.harvard.i2b2.common.exception.I2B2Exception: Application property file(crc.properties) missing edu.harvard.i2b2.crc.setfinderquery.obfuscation.count.sigma entry
at edu.harvard.i2b2.crc.util.QueryProcessorUtil.getPropertyValue(Unknown Source)
at edu.harvard.i2b2.crc.util.QueryProcessorUtil.getCRCPropertyValue(Unknown Source)
at edu.harvard.i2b2.crc.dao.setfinder.GaussianBoxMuller.getCountSigma(Unknown Source)
at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorHelperDao.executeQuery(Unknown Source)
at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.executeSQL(Unknown Source)
at edu.harvard.i2b2.crc.ejb.ExecRunnable.processQueryRequest(Unknown Source)
at edu.harvard.i2b2.crc.ejb.ExecRunnable.execute(Unknown Source)
at edu.harvard.i2b2.crc.ejb.QueryExecutorMDB.onMessage(Unknown Source)
at edu.harvard.i2b2.crc.ejb.QuerySmallExecutorMDB.onMessage(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.jav a:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.invocation.Invocation.performCall(Invocation.java:359)
at org.jboss.ejb.MessageDrivenContainer$ContainerInterceptor.invoke(MessageDrivenCo ntainer.java:495)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedCo nnectionInterceptor.java:158)
at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor .java:63)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.jav a:121)
at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorB MT.java:173)
at org.jboss.ejb.plugins.MessageDrivenTxInterceptorBMT.invoke(MessageDrivenTxInterc eptorBMT.java:47)
at org.jboss.ejb.plugins.MessageDrivenInstanceInterceptor.invoke(MessageDrivenInsta nceInterceptor.java:116)
at org.jboss.ejb.plugins.RunAsSecurityInterceptor.invoke(RunAsSecurityInterceptor.j ava:109)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInt erceptor.java:138)
at org.jboss.ejb.MessageDrivenContainer.internalInvoke(MessageDrivenContainer.java: 402)
at org.jboss.ejb.Container.invoke(Container.java:960)
at org.jboss.ejb.plugins.jms.JMSContainerInvoker.invoke(JMSContainerInvoker.java:98 7)
at org.jboss.ejb.plugins.jms.JMSContainerInvoker$MessageListenerImpl.onMessage(JMSC ontainerInvoker.java:1287)
at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:266)
at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMessageConsumer .java:891)
at
org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:170) at org.jboss.mq.SpySession.run(SpySession.java:323) at
org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:194)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:7 56)
at java.lang.Thread.run(Thread.java:662) 2011-12-23 08:53:37,983 ERROR [STDERR] java.lang.IllegalStateException: BaseTransaction.rollback [com.arjuna.ats.internal.jta.transaction.arjunacore.notx] [com.arjuna.ats.internal.jta.transaction.arjunacore.notx] no transaction!
NEXT EMAIL
From: Phillips, Lori C. Sent: Thursday, December 22, 2011 1:28 PM To: Dan Connolly; i2b2 AUG Members Subject: RE: documentation on age concepts in i2b2 1.6?
Dan,
Where did you get the oracle script from? This should contain sysdate, not getdate(), in the c_dimcode. (getdate is used in sqlserver).
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.
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_OPERATOR C_DIMCODE patient_num patient_dimension birth_date N BETWEEN getdate() - (365.25 * 2) +1 AND getdate() - 365.25 + 1
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: Peter Beninato [beninato.ohsu] Sent: Wednesday, December 21, 2011 1:55 PM To: Mendis, Michael E.; i2b2 AUG Members Subject: RE: i2b2 - v1.6_02 - UPDATE_QUERYINSTANCE_MESSAGE.sql
The comment sounds like a good idea, especially for someone like me!
From: Mike Mendis [mmendis.partners] Sent: Wednesday, December 21, 2011 10:53 AM To: Peter Beninato; i2b2 AUG Members Subject: Re: i2b2 - v1.6_02 - UPDATE_QUERYINSTANCE_MESSAGE.sql
Yes it is a placeholder, which is needed by the ant script. Because the buid.xml is used by both oracle and sqlserver, if we did not have the file, the oracle would break looking for that file.
I can add the file as a comment something like "/* This file was left intentionally blank */'
Thanks
mike
On 12/21/11 1:50 PM, "Peter Beninato" <beninato.ohsu> wrote:
Hi,
Not sure, in that this might just be a placeholder file.
But when comparing the script to the procedures created there was mismatch.
There was no procedure, UPDATE_QUERYINSTANCE_MESSAGE.
When looking at the file it appears to be empty:
i2b2\v1_6_02\i2b2createdb-1602\edu.harvard.i2b2.data\Release_1-6\NewInstall\Crcdata\scripts \procedures\oracle\UPDATE_QUERYINSTANCE_MESSAGE.sql
FYI.
Peter Beninato - OCTRI DW Developer Oregon Health & Science University 503-494-9985 beninato.ohsu <beninato.ohsu>
NEXT EMAIL
From: Mendis, Michael E. Sent: Wednesday, December 21, 2011 8:54 AM To: Peter Beninato; i2b2 AUG Members Subject: Re: v1.6_02 - Modifier Dimension - Upgrade -
crc_create_datamart_oracle.sql
Peter,
You are correct, they are in fact missing, I looked at the upgrade scripts for 1.6 and they don't have the modifier_dimensions scripts in them. I have added them now, and will be incorporated into the next release.
Thanks for finding it.
Mike
On 12/20/11 7:59 PM, "Peter Beninato" <beninato.ohsu> wrote:
Hi,
I'm working on Upgrading a local install to v1.6. I'm comparing the NewInstall scripts to the Upgrade scripts, and hand running data scripts etc. since, I have a production system, and need to be careful to maintain existing data.
While I'm on v1.4, I took some time to extract v1.5 upgrade scripts and incorporate any changes there into my scripts.
It seems to me that the script for the MODIFIER_DIMENSION might be missing from the Upgrade script ( I don't think this dim was in v1.5).
I see the CTAS statement for Modifier_Dimension in \i2b2\v1_6_02\i2b2createdb?1602 \edu.harvard.i2b2.data\Release_1?6\NewInstall\Crcdata\scripts\crc_create_datamart_oracle.sql
But I don't see it in
i2b2\v1_6_02\i2b2createdb?1602\edu.harvard.i2b2.data\Release_1?6\Upgrade\Crcdata\scripts \crc_create_datamart_oracle.sql
FYI.
Peter Beninato - OCTRI DW Developer Oregon Health & Science University
beninato.ohsu <beninato.ohsu>
NEXT EMAIL
From: Brian Ostasiewski [bostasie.wakehealth] Sent: Wednesday, December 21, 2011 8:38 AM To: i2b2 AUG Members Subject: RE: bug with Dates contraint on dimensional queries?
I never got a response to this original email, and I've hit a similar situation with another dimensional query when specifying "same financial encounter". The SQL that is generated contains this:
update i2b2data.QUERY_GLOBAL_TEMP set panel_count =2 where exists ( select 1 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 i2b2data.visit_dimension where encounter_num IN (select encounter_num from i2b2data.visit_dimension where dsch_phyn_id = '00745') group by encounter_num ,instance_num, concept_cd,start_date,provider_id, patient_num ) t where i2b2data.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2data.QUERY_GLOBAL_TEMP.patient_num = t.patient_num and i2b2data.QUERY_GLOBAL_TEMP.encounter_num = t.encounter_num )
which is trying to select observation_fact columns from visit_dimension.
Brian
From: Brian Ostasiewski Sent: Monday, November 14, 2011 10:49 AM To: i2b2 AUG Members Subject: bug with Dates contraint on dimensional queries? In 1.6.01, we had a user attempt to filter the Dates on a query of Age now, which is a dimensional query against the patient_dimension. The resulting query underneath appears to assume it is going against the observation_fact table. Can someone verify this is an issue? Also, is there a way to disable use of the Dates filter for specific concepts via c_metadataxml? Brian Ostasiewski The sql from qt_query_master: select count(distinct patient_num) as patient_num_count from ( (select patient_num ,1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2data.patient_dimension where patient_num IN (select patient_num from i2b2data.patient_dimension where birth_date <= sysdate - (365.25*65) ) AND ( start_date >= to_date('31-Dec-2010 19:00:00','DD-MON-YYYY HH24:MI:SS') ) group by patient_num ) t ) INTERSECT (select patient_num ,1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2data.observation_fact where CONCEPT_CD IN (select CONCEPT_CD from i2b2data.CONCEPT_DIMENSION where CONCEPT_PATH LIKE '\WFUBMC\Diagnoses\ICD9\Circulatory system (390-459)\Hypertensive disease (401-405)(401) Essential hypertension(401-1) Benign essential hyperten~%') group by patient_num ) t UNION ALL select patient_num ,1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2data.observation_fact where CONCEPT_CD IN (select CONCEPT_CD from i2b2data.CONCEPT_DIMENSION where CONCEPT_PATH LIKE '\WFUBMC\Diagnoses\ICD9\Circulatory system (390-459)\Hypertensive disease (401-405)(401) Essential hypertension(401-9) Unspecified essential hyp~%') group by patient_num ) t UNION ALL select patient_num ,1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2data.observation_fact where CONCEPT_CD IN (select CONCEPT_CD from i2b2data.CONCEPT_DIMENSION where CONCEPT_PATH LIKE '\WFUBMC\Diagnoses\ICD9\Endocrine disorders (240-259)\Other endocrine gland diseases (250-259)(250) Diabetes mellitus(250-0) Diabetes mellitus without~%') group by patient_num ) t ) ) allitem Results in logged exception: 2011-11-14 10:34:50,763 ERROR [edu.harvard.i2b2.crc.dao.CRCDAO] Error while executing sql java.sql.SQLException: ORA-00904: "START_DATE": invalid identifier at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.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) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1272) at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:226) at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorHelperDao.executeQuery(Unknown Source) at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.executeSQL(Unknown Source) at edu.harvard.i2b2.crc.ejb.ExecRunnable.processQueryRequest(Unknown Source) at edu.harvard.i2b2.crc.ejb.ExecRunnable.execute(Unknown Source) at edu.harvard.i2b2.crc.ejb.QueryExecutorMDB.onMessage(Unknown Source) at edu.harvard.i2b2.crc.ejb.QuerySmallExecutorMDB.onMessage(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.jboss.invocation.Invocation.performCall(Invocation.java:359) at org.jboss.ejb.MessageDrivenContainer $ContainerInterceptor.invoke(MessageDrivenContainer.java:495) at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInter ceptor.java:158) 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.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:173) at org.jboss.ejb.plugins.MessageDrivenTxInterceptorBMT.invoke(MessageDrivenTxInterceptorBMT.java :47) at org.jboss.ejb.plugins.MessageDrivenInstanceInterceptor.invoke(MessageDrivenInstanceInterceptor.ja va:116) at org.jboss.ejb.plugins.RunAsSecurityInterceptor.invoke(RunAsSecurityInterceptor.java:109) at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205) at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138) at org.jboss.ejb.MessageDrivenContainer.internalInvoke(MessageDrivenContainer.java:402) at org.jboss.ejb.Container.invoke(Container.java:960) at org.jboss.ejb.plugins.jms.JMSContainerInvoker.invoke(JMSContainerInvoker.java:1092) at org.jboss.ejb.plugins.jms.JMSContainerInvoker $MessageListenerImpl.onMessage(JMSContainerInvoker.java:1392) at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:266) at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMessageConsumer.jav a:906) at org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:170) at org.jboss.mq.SpySession.run(SpySession.java:323) at org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:194) at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:761) at java.lang.Thread.run(Thread.java:619) NEXT EMAIL From: Bhargav Adagarla [badagarla.kumc] Sent: Tuesday, December 20, 2011 12:37 PM To: i2b2 AUG Members Subject: RE: any "R Engine Cell" for i2b2 users? 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-engineproject/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:18 7) [java] at com.sun.tools.xjc.reader.internalizer.Internalizer.buildTargetNodeMap(Internalizer.java:27 1) [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: McMurry, Andrew J. [Andrew_McMurry.hms.harvard] Sent: Friday, December 16, 2011 5:16 PM To: Lisa Miao Cc: i2b2 AUG Members Subject: Re: standard race code In SHRINE, a project that networks many i2b2 hospitals together, we have found common agreement on the following Demographic standards https://open.med.harvard.edu/display/SHRINE/Core+Ontology Race and Ethnicity - CDC Race & Ethnicity Code Sets On Dec 16, 2011, at 2:50 PM, Lisa Miao wrote: Dear i2b2 team and everyone else, I'm wondering what race codes everyone (and i2b2 demo project) is using, and in particular if anyone is using standard(rather than proprietary) race code. Thanks, Lisa. NEXT EMAIL From: Michael Horvath [mhorvath.wakehealth] Sent: Friday, December 16, 2011 3:40 PM To: i2b2 AUG Members members Subject: RE: restarting jboss on i2b2 server VM boot I've noticed in some of the VM's that the third field of the entry /etc/oratab function is set to 'N'. Switching this to 'Y' will tell the dbstart utility to bring it up at boot. I haven't noticed any problems with jboss starting, however. From: Jack London [jack.london.KimmelCancerCenter] Sent: Friday, December 16, 2011 2:38 PM To: i2b2 AUG Members members Subject: restarting jboss on i2b2 server VM boot We have noticed that it is almost always necessary to restart jboss upon rebooting our i2b2 VM server. If we just reboot and then start the web client, queries fail. If we then stop and restart jboss, everything works fine. We suspect that Oracle is not COMPLETELY "up" when jboss initially starts. Stopping and restarting jboss always works. Anyone have this experience? Jack NEXT EMAIL From: Jack London [jack.london.KimmelCancerCenter] Sent: Friday, December 16, 2011 1:53 PM To: i2b2 AUG Members members Subject: web client v1.6 multiple query status lines Have others seen multiple query status lines appear, as shown below: Finished Query: "Breast, NOS@13:47:06" Compute Time: 10 secs Number of patients for "Breast, NOS@13:47:06" patient_count: 744 Number of patients for "Breast, NOS@13:47:06" patient_count: 744 Any idea why? thanks Jack NEXT EMAIL From: Mendis, Michael E. Sent: Friday, December 16, 2011 1:51 PM To: Jack London Cc: Murphy, Shawn N.; i2b2 AUG Members Subject: RE: width of web client query tool panels The left panels (ontology, previous queries) are set to a specific size. The right size will max to the width of the window. But you cant realize the way you are thinking. mike From: Jack London [jack.london.KimmelCancerCenter] Sent: Fri 12/16/2011 1:49 PM To: Mendis, Michael E. Cc: Murphy, Shawn N.; i2b2 AUG Members members Subject: width of web client query tool panels is there any way to make them wider? Jack NEXT EMAIL From: Murphy, Shawn N. Sent: Friday, December 16, 2011 10:55 AM To: i2b2 AUG Members Subject: I2b2 1.6.02 is released Hi All, The next set of fixes and enhancements is available, new stuff is outlined below and in the release notes. Thanks, Shawn. String Constraint In the 1.6.02 release there is now an option to constrain a query by a string of text. Similar to other text and numeric constraints, a user can narrow down their search criteria when setting up the query in the query tool view. The main difference between this new constraint and the existing ones is the string constraint allows users to enter a string of text as opposed to an enumerated or numerical value. Lockout / Obfuscation Parameters In the 1.6.02 new parameters were added to the crc.properties file to support the lockout process for obfuscated users as well as the obfuscation process itself. Data In release 1.6.02, no changes were made to the structure of the database. The only change was to add examples to the metadata, concept_dimension, modifier_dimension, and observation_fact tables to support the new string constraint. Fixes: The following fixes were added to this release: Memory error: not able to load PDO Not able to search for terms with an '&' in the name Demo Database Scripts: Passwords inconsistent PM: ProjectUser parameters are not saved with the project NEXT EMAIL From: Russ Waitman [rwaitman.kumc] Sent: Friday, December 16, 2011 9:56 AM To: Joe.Terdiman.kp; beninato.ohsu Cc: Andrew_McMurry.hms.harvard; i2b2 AUG Members Subject: RE: i2b2/Shrine - ontology - Smoking Status Joe, Thanks for that pointer. Hadn't looked at that table. We'll see if we can pull that in at a subsequent point and see how it overlaps or exchanges with flowsheet and notes information, Russ <Joe.Terdiman.kp> 12/15/2011 7:14 PM Although we do not yet included smoking hx in our i2b2 database or SHRINE, we have looked in our EPIC system at the percentage of patients with smoking hx in the social_hx table vs. free text progress notes. Using NLP on progress notes and an SQL query on the social_hx table in a sample of 1000 patients, we found 20% of patients had a positive hx of smoking from progress notes, but the social_hx table indicated they were either non-smokers or were blank. This ad hoc study clearly demonstrates the ability of NLP to extract significant clinical information from free text reports in the EHR, and the potential for large discrepancies in estimating the prevalence of clinical conditions (smoking in this case) without it. Joe Joe Terdiman, MD, PhD Division of Research Kaiser Permanente 2000 Broadway Oakland, CA 94612 From: Peter Beninato <beninato.ohsu> Sent: 12/15/2011 04:37 PM To: "McMurry, Andrew J." <Andrew_McMurry.hms.harvard>, Russ Waitman <rwaitman.kumc>, i2b2 AUG Members Subject RE: i2b2/Shrine - ontology - Smoking Status Hi Andy, Was smoking status included in any of the SHRINE implementations you've worked on? Our research data warehouse source for smoking status currently comes from EPIC -> CLARITY -> SOCIAL_HX table, but in our data warehouse, if there are other flowsheet sources of interest to our investigators we may include them too. Peter From: Russ Waitman [rwaitman.kumc] Sent: Friday, December 16, 2011 9:07 AM To: Andrew J. McMurry Cc: i2b2 AUG Members; Peter Beninato Subject: Re: i2b2/Shrine - ontology - Smoking Status It's coded value in the flowsheet measures. We don't process any text notes at KUMC yet. We'll probably extract "smart data" from Epic first. Russ From: McMurry, Andrew J. [Andrew_McMurry.hms.harvard] Sent: Thursday, December 15, 2011 4:23 PM To: Russ Waitman Cc: Peter Beninato; i2b2 AUG Members Subject: Re: i2b2/Shrine - ontology - Smoking Status Curious how you are obtaining "smoking status" – as a coded value or by processing of the text note? andy On Dec 15, 2011, at 6:30 PM, Russ Waitman wrote: It's in several places but pretty much a peer at the top of the tree with things like vital signs. We derive the local ontology out of the Epic tables. At a some point Judy Warren in nursing here would like to build a top down approach but until that's done, we're going with a bottom up approach: using the local view of reality as opposed to imposing our world view. We did some clustering of concepts as described in the paper but we need to figure out a naming scheme for the clustered concepts before we make that our normal solution. http://informatics.kumc.edu/work/attachment/wiki/KUBMIPresentations/HERONNursingOntologyTalkAMIAOctober262011 v6.pdf Here's a common spot though and a picture: \Flowsheets\KU\KU Inpatient\Tobacco Use\ Russ Waitman Associate Professor Director of Medical Informatics Department of Biostatistics University of Kansas Medical Center rwaitman.kumc http://informatics.kumc.edu Peter Beninato <beninato.ohsu> 12/15/2011 5:19 PM What does the tree/path look like for where you locate smoking status in your ontology? I was thinking of a top-level folder called something like Social History, but would like to know where others are putting it. Thanks. From: Russ Waitman [rwaitman.kumc] Sent: Thursday, December 15, 2011 3:15 PM To: i2b2 AUG Members; Peter Beninato Subject: Re: i2b2/Shrine - ontology - Smoking Status Hi Peter, It's part of the hierarchy we suck out of our Epic flowsheet build. For the most part these ontologies are highly localized though sharable. We described the project to incorporate that data at AMIA this year if you're interested. Waitman LR, Warren JJ, Manos L, Connolly DW. Expressing Observations from Electronic Medical Record Flowsheets in an i2b2 based Clinical Data Repository to Support Research and Quality Improvement. (presented at AMIA Annual Symposium 2011) Russ Waitman Associate Professor Director of Medical Informatics Department of Biostatistics University of Kansas Medical Center rwaitman.kumc http://informatics.kumc.edu Peter Beninato <beninato.ohsu> 12/15/2011 5:10 PM Hi, Beginning the process of adding Smoking Status to our OHSU Cohort Discovery ontology. Has/was Smoking Status added to any Shrine ontology? If so please reply with c_fullname/c_dimcode (aka) path. If possible would like standardize. If your organization's ontology includes Smoking Status (but not part of a Shrine network), where did you include it in your ontology? Thanks. Peter Beninato - OCTRI DW Developer Oregon Health & Science University beninato.ohsu NEXT EMAIL From: Natter, Marc [Marc.Natter.childrens.harvard] Sent: Friday, December 16, 2011 9:53 PM To: i2b2 AUG Members Subject: Re: questions to the actual i2b2 users And a little more follow-up inline regarding how we do it with i2b2-SSR (i2b2 self-scaling registry platform), used for the CARRAnet and the Harvard-wide Inflammatory Bowel Disease registries - which use a modified SHRINE client to federate results across i2b2 sites and over i2b2 projects: From: Keith Marsolo [keith.marsolo.cchmc] Sent: Wednesday, December 14, 2011 9:29 AM To: Kohane, Isaac Samuel Cc: Askar Ibragimov; i2b2 AUG Members Subject: Re: questions to the actual i2b2 users To follow up on Zak's comments. On Dec 14, 2011, at 8:37 AM, Kohane, Isaac Samuel wrote: One questions answered below: On Dec 14, 2011, at 5:02 AM, Askar Ibragimov wrote: Dear colleagues, we at FIMM evaluating i2b2 as a platform to publish the data collected jointly from one or several databases and appreciate if you share your practical experience on the following topics. In your opinion: 1. Is i2b2 able to store and display all kind of your data in a convenient manner? ANSWER: In the experience of Cincinnati Children's, yes to storing the data and "it depends" on display. It depends on your definition of convenient. CCHMC has built its own version of a workbench (freely available) that adds functionality for displaying reports. Creating those reports are still not as straightforward as we'd like, but we're creating a Report Builder to facilitate this. 2. Have anyone used the system with epidemiological / questionnaire based data? ANSWER: Yes, the CARRAnet (60 sites using i2b2 linked by SHRINE) uses a lot of questionnaire based data (https://carranetwork.org/projects/carranet-registry) In addition to the CARRANet project (in which CCHMC has played a part), we've also built a Forms cell that allows for the display/completion of web forms. It's built on CDISC ODM, which is a standard that allows for the modeling of questionnaire data and metadata. There's a mapping from this forms cell to the CRC. 3. Are existing abilities of Workbench matches your needs and did you ever need something that Workbench does not offer? What kind new functionalities people like to have, is something crucial missing related to queries, web services, data integration etc on basic operations. When we started working with i2b2, the web-based workbench did not exist, so we ended up building our own. ANSWER: We've tried to target our i2b2 installations to the general clinical research user. The eclipse-based workbench is too advanced for this type of user (in my opinion), so we've targeted our tools for use cases like cohort identification, chart review, on-demand reporting, and the request of biosamples. We've also made a few extra changes to allow the handling of observation-related metadata which is similar to the concept of modifiers in version 1.6. 4. Did somebody tried to replace standard data providing cells with someting custom, that takes information from completely other database (for example, a replacement Ontology cell)? How difficult was such a task? 5. Can somebody comment on how easy it s to extend the system for newcomers, especially adding / creating new cells ANSWER: If someone understands Java programming and takes the time to look through the i2b2 documentation and the source code, then it's not horrendously difficult to create new cells. But I don't know if most newcomers would have that experience. It also depends on the complexity of the cell itself. We've made modifications to existing cells and created new ones. So it's possible, but you do need to know what you're doing. Adding new cells that make calls to existing cells is definitely easier than trying to go in a hack away at the existing code. 6 . Our users ask, would it be possible to query several projects at once? Is there a possibility to do queries also based on free text or range of values? How we can create a new query based on an old one? ANSWER: Depending on what you mean by "querying several projects at once", you could hook up the various i2b2 CRCs using SHRINE. We do this in our multi-center registries, where every center in the network has their own i2b2 CRC and they communicate via SHRINE. We've follow the Harvard/Partners model, where only one project is stored in a CRC. We've also made changes to allow us to get by with one PM cell, one ontology cell, etc., but then use multiple CRCs. And I believe that version 1.6 allows you to use previous queries as criteria in new queries. You can take a query, drag it into the cohort tool and add new criteria, filtering down the list. Cheers, Askar NEXT EMAIL From: Peter Beninato [beninato.ohsu] Sent: Wednesday, December 14, 2011 7:07 PM To: i2b2 AUG Members Subject: i2b2 - v1.6.x - data - PM - is_admin - add_user Hi, I downloaded the source code on Dec. 6, 2011. I am reviewing the data upgrade scripts and incorporating changes into some custom scripts. Below are some copy/pastes from some emails that went by concerning problems with adding users via the webclient, and some corrective action for v1.6.01. I think these changes were incorporated in v1.6.01, so maybe I'm missing it, but I don't see the column, is_admin, in either the data install or upgrade scripts. So... 1) In the i2b2pm schema, which table should have a column IS_ADMIN? 2) What value does the column, IS_ADMIN, need to be set to? (Y or Yes?) 3) Is this ALTER TABLE statement in the codebase? If so where? Thanks. Peter Beninato - OCTRI DW Developer Oregon Health & Science University beninato.ohsu From: Peter Beninato Sent: Wednesday, December 14, 2011 3:53 PM To: Peter Beninato Subject: RE: Add user not working correcting in 1.6.00 Are you using 1.6 or 1.6.01? Also correct me, but you are copying the current vm install to a new one correct? Which is fine and should work. On the database side, did you create a user in the pm_projct_user_role table with the following Project_id @ user_id snmtest user_role ADMIN status_cd A mike From: Peter Beninato Sent: Wednesday, December 14, 2011 3:52 PM To: Peter Beninato Subject: FW: Add user not working correcting in 1.6.00 From: Mike Mendis [mmendis.partners] Sent: Thursday, October 27, 2011 12:56 PM To: i2b2 AUG Members Subject: Add user not working correcting in 1.6.00 When using the webclient add to add a new user, it returns a database error. We are going to be releasing a new 1.6.01 that corrects this and other issues. As a temporary fix, alter that PM_USER_TABLE and add the column IS_ADMIN as a varchar2(50) mike NEXT EMAIL From: Peter Beninato [beninato.ohsu] Sent: Wednesday, December 14, 2011 6:20 PM To: i2b2 AUG Members Subject: i2b2 - v1.6.x - data - upgrade Hi, I am reviewing the DB upgrade to incorporate changes into some custom scripts. In the script: i2b2\v1_6_01\i2b2createdb-1601\edu.harvard.i2b2.data\Release_1-6\Upgrade\Metadata\scripts \upgrade_oracle_i2b2metadata_tables.sql I think the indexes in the following two statements should be on the "BIRN" table. (There are indexes for these two fields on the i2b2 table created earlier in the script. Please note and create a JIRA issue as needed. CREATE INDEX META_FULLNAME_BIRN_IDX ON I2B2(C_FULLNAME) ; CREATE INDEX META_APPLIED_PATH_BIRN_IDX ON I2B2(M_APPLIED_PATH) ; FYI Peter Beninato - OCTRI DW Developer Oregon Health & Science University beninato.ohsu NEXT EMAIL From: Dat Q. Phan [Dat.Q.Phan.uth.tmc] Sent: Wednesday, December 14, 2011 3:25 PM To: Lusted, Jeff; i2b2 AUG Members Subject: RE: I2b2 using SSL Jeff, Take a look at i2b2 - Security Baseline from here: https://i2b2.atlassian.net/wiki/display/community/Supplemental+Documentation+for+Installing+and+Configuring+i2b2+v1.6 Hope that helps. Johnny From: Lusted, Jeff [jl99.leicester.ac.uk] Sent: Wednesday, December 14, 2011 12:23 PM To: i2b2 AUG Members Subject: I2b2 using SSL Hi Colleagues! At Leicester we have recently deployed test i2b2 servers with successful access to them from web client and workbench. We have even written a small command line utility which can upload patient data using a PDO. The servers are deployed in two environments: university and hospital. As you can imagine, the hospital environment is locked down, secure and closed. No outside access! The university environment is by its nature somewhat more open. Although we can limit access only to within the university environment, keeping a more open policy would be advantageous. To that end 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? Looking forward to your replies. Kindest regards Jeff Jeff Lusted, Senior Programmer (RCS, IT Services) University of Leicester Email: jl99.le.ac.uk Elite Without Being Elitist Times Higher Awards Winner 2007, 2008, 2009, 2010, 2011 Follow us on Twitter http://twitter.com/uniofleicsnews NEXT EMAIL From: Kohane, Isaac Samuel [Isaac_Kohane.hms.harvard] Sent: Wednesday, December 14, 2011 10:26 AM To: Steward, Duane Cc: i2b2 AUG Members; Murphy, Shawn N.; Churchill, Susanne E. Subject: Re: IRB submission artifacts <-- Re: questions about dataschema 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 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. 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. Duane 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/~zak/ From: Phillips, Lori C. Sent: Wednesday, December 14, 2011 9:35 AM To: Askar Ibragimov; i2b2 AUG Members Subject: RE: questions about dataschema Askar, The sample data in the demo package is purely an example that we expected most people would find useful. It demonstrates several properties of our Ontology design: 1. Ontologies may be organized in several tables. The TABLE_ACCESS table specifies the root level nodes of the navigation tree and may be used to display ontologies from more than one table. A single table (I2B2 in this case) may be used to specify more than one root level node. You ask where a new term , BMI, should be placed. Your first question should be: where do I want it to appear in the tree? If the answer is "I would like a root level node to display this concept" then the easiest solution is to create a new table for that concept and add an entry to TABLE_ACCESS for that table. If the answer is "Place it in the Custom metadata table" then the easiest solution is to either use the Edit Terms feature of the workbench to create the BMI nodes in the Custom metadata tree or create the appropriate entries in the Custom_meta table. The attached document should help explain how the ontology and concept_dimension entries are related. Lori From: Askar Ibragimov [askar.ibragimov.gmail] Sent: Wednesday, December 14, 2011 9:17 AM To: i2b2 AUG Members Subject: Fwd: questions about dataschema Dear Lori, thanks for the explanations. I found these tables. TABLE_ACCESS got in particular the following info: | C_TABLE_CD | C_TABLE_NAME | C_PROTETED_ACCESS | C_HLEVEL | C_FULLNAME |