*NEXT EMAIL*
From: [Peter Beninato \[beninato.ohsu\]|mailto:beninato@ohsu.edu]
Sent:Monday, January 30, 2012 4:35 PM
To: [Henderson, Darren W; i2b2 AUG Members|mailto:members@i2b2aug.org]
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|mailto:darren.henderson@uky.edu]\]
Sent: Monday, January 30, 2012 1:08 PM
To: [Peter Beninato; i2b2 AUG Members|mailto:members@i2b2aug.org]
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.oh|mailto:beninato@ohsu.edu]s[u\]|mailto:beninato@ohsu.edu]
Sent: Monday, January 30, 2012 3:00 PM
To: Henderson, Darren W; [i2b2 AUG Members|mailto:members@i2b2aug.org]
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 [\[dar|mailto:darren.henderson@uky.edu]r[en.henderson.uky|mailto:darren.henderson@uky.edu]\]
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.oh|mailto:beninato@ohsu.edu]s[u\]|mailto:beninato@ohsu.edu]
Sent: Monday, January 30, 2012 2:26 PM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\] |mailto:lmiao@regenstrief.org]
Sent:Monday, January 30, 2012 3:50 PM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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|mailto:members@i2b2aug.org]
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|mailto:jlaw@med.umich.edu]> 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|mailto:lmiao@regenstrief.org]\]
Sent: Monday, January 30, 2012 11:37 AM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:dsteward@NEMOURS.ORG]
Sent:Monday, January 30, 2012 12:55 PM
To: [Kohane, Isaac Samuel; i2b2 AUG Members |mailto:members@i2b2aug.org]
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 Sam[uel \[Isaac_Kohan|mailto:Isaac_Kohane@hms.harvard.edu]e[.hms.harvard\]|mailto:Isaac_Kohane@hms.harvard.edu]
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 Sam[uel \[Isaac_Kohan|mailto:Isaac_Kohane@hms.harvard.edu]e[.hms.harvard\]|mailto:Isaac_Kohane@hms.harvard.edu]
Sent: Wednesday, December 14, 2011 8:43 AM
To: Laukkanen Markku
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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|mailto:isaac_kohane@harvard.edu]
[http://www.chip.org/~zak/|http://www.chip.org/%7Ezak/]
\\
\\
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 :-)
\\
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; [me|mailto:members@i2b2aug.org][mbers.i2b2aug|mailto:mbers@i2b2aug.org]
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 Ibragim[ov \[askar.ibragimov.gmail\]|mailto:askar.ibragimov@gmail.com] Sent: Tuesday, December 13, 2011 11:03 AM
To: [me|mailto:members@i2b2aug.org][mbers.i2b2aug|mailto:mbers@i2b2aug.org]
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 :-)
\\
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\]|mailto:mweiner@mail.med.upenn.edu]
Sent:Monday, January 30, 2012 9:58 AM
To:Mendis, Michael E.; 'Peter [Beninato'; i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:mmendis@partners.org]
Sent: Monday, January 30, 2012 9:12 AM
To: Mark Weiner; 'Peter Be[ninato'; i2b2 AUG Members|mailto:members@i2b2aug.org]
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|mailto:mweiner@mail.med.upenn.edu]> 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|mailto:beninato@ohsu.edu]\]
Sent: Thursday, December 29, 2011 10:57 AM
To: Mike Mendis; [i2b2 AUG Members|mailto:members@i2b2aug.org]
Subject: RE: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
\\
Hi,
\\
Thanks Mike.
\\
Peter
\\
\\
\\
From: Mike Mendis \[[mmendis.partners|mailto:mmendis@partners.org]\]
Sent: Thursday, December 29, 2011 7:51 AM
To: Peter Beninato; [i2b2 AUG Members|mailto:members@i2b2aug.org]
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|mailto:beninato@ohsu.edu]> 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/|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/|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 |mailto:beninato@ohsu.edu]<[beninato.ohsu|mailto:beninato@ohsu.edu]>
\\
\\
\\
*NEXT EMAIL*
\\
From: [Steward, Duane \[dsteward.NEMOURS\]|mailto:dsteward@NEMOURS.ORG]
Sent:Monday, January 30, 2012 9:39 AM
To: [Mendis, Michael E.; Mark Weiner; i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:mmendis@partners.org]
Sent: Monday, January 30, 2012 9:12 AM
To: Mark Weiner; 'Peter Be[ninato'; i2b2 AUG Members|mailto:members@i2b2aug.org]
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|mailto:mweiner@mail.med.upenn.edu]> 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|mailto:beninato@ohsu.edu]\]
Sent: Thursday, December 29, 2011 10:57 AM
To: Mike Mendis; [i2b2 AUG Members|mailto:members@i2b2aug.org]
Subject: RE: SOLUTION - i2b2 - v1.6_02 - webclient - admin - Requires ADMIN role
\\
Hi,
\\
Thanks Mike.
\\
Peter
\\
\\
From: Mike Mendis \[[mmendis.partners|mailto:mmendis@partners.org]\]
Sent: Thursday, December 29, 2011 7:51 AM
To: Peter Beninato; [i2b2 AUG Members|mailto:members@i2b2aug.org]
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|mailto:beninato@ohsu.edu]> 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/|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/|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 |mailto:beninato@ohsu.edu]<[beninato.ohsu|mailto:beninato@ohsu.edu]>
\\
\\
\\
\\
*NEXT EMAIL*
\\
From: [Lisa Miao \[lmiao.regenstrief\] |mailto:lmiao@regenstrief.org]
Sent:Friday, January 27, 2012 12:00 PM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:lmiao@regenstrief.org]
Sent: Friday, January 27, 2012 11:05 AM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:darren.henderson@uky.edu]
Sent:Friday, January 27, 2012 11:20 AM
To: [Peter Beninato; Lisa Miao; i2b2 AUG Members|mailto:members@i2b2aug.org]
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.oh|mailto:beninato@ohsu.edu]s[u\]|mailto:beninato@ohsu.edu]
Sent: Friday, January 27, 2012 11:16 AM
To: Lisa Mi[ao; i2b2 AUG Members|mailto:members@i2b2aug.org]
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.r|mailto:lmiao@regenstrief.org]e[genstrief\]|mailto:lmiao@regenstrief.org]
Sent: Friday, January 27, 2012 8:05 AM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:darren.henderson@uky.edu]
Sent:Friday, January 27, 2012 11:16 AM
To: [Lisa Miao; i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:lmiao@regenstrief.org]
Sent: Friday, January 27, 2012 11:05 AM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:darren.henderson@uky.edu]
Sent:Thursday, January 26, 2012 3:11 PM
To: [Murphy, Shawn N.; i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:SNMURPHY@PARTNERS.ORG]
Sent: Thursday, January 26, 2012 2:51 PM
To: Henderson, Darren W; [i2b2 AUG Members|mailto:members@i2b2aug.org]
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 [\[dar|mailto:darren.henderson@uky.edu]r[en.henderson.uky|mailto:darren.henderson@uky.edu]\]
Sent: Thursday, January 26, 2012 2:37 PM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:darren.henderson@uky.edu]
Sent:Thursday, January 26, 2012 2:40 PM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:rwaitman@kumc.edu]
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|mailto:rwaitman@kumc.edu]
[http://informatics.kumc.edu|http://informatics.kumc.edu/]
\\
\\
"Churchill, Susanne E." <[SCHURCHILL.PARTNERS|mailto:SCHURCHILL@PARTNERS.ORG]> 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|mailto:members@i2b2aug.org]
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\]|mailto:Jack.London@KimmelCancerCenter.org] Sent: Tuesday, January 24, 2012 8:04 PM
[To: i2b2 AUG Members|mailto:members@i2b2aug.org]
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.|mailto:travis.nagler@ucdmc.ucdavis.edu][nagler.ucdmc.ucdavis\]|mailto:nagler@ucdmc.ucdavis.edu]
Sent:Tuesday, January 24, 2012 1:35 PM
To:Peter Beninato
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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|http://www.ucdmc.ucdavis.edu/]
[travis.nagler.ucdmc.ucdavis|mailto:travis.nagler@ucdmc.ucdavis.edu]
\\
\\
From:Peter Beninato <[beninato.ohsu|mailto:beninato@ohsu.edu]>
To:Travis Nagler <[travis.nagler.ucdmc.ucdavis|mailto:travis.nagler@ucdmc.ucdavis.edu]>, "[i2b2 AUG Members|mailto:members@i2b2aug.org]" <[i2b2 AUG Members|mailto:members@i2b2aug.org]> 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|mailto:travis.nagler@ucdmc.ucdavis.edu]\]
Sent: Monday, January 23, 2012 3:51 PM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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|http://www.ucdmc.ucdavis.edu/]
[travis.nagler.ucdmc.ucdavis|mailto:travis.nagler@ucdmc.ucdavis.edu]
\\
\\
\\
\\
*NEXT EMAIL*
\\
From: [Henderson, Darren W \[darren.henderson.uky\]|mailto:darren.henderson@uky.edu]
Sent:Tuesday, January 24, 2012 11:10 AM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:darren.henderson@uky.edu]
Sent:Tuesday, January 24, 2012 10:11 AM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:jack.london@KimmelCancerCenter.org]
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|mailto:mauro.bucalo@gmail.com]
web-page: [http://it.linkedin.com/in/maurobucalo|http://it.linkedin.com/in/maurobucalo]
\\
\\
\\
2012/1/20 Jack London <[jack.london.kimmelcancercenter|mailto:jack.london@kimmelcancercenter.org]>:
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\]|mailto:daniel.harris@uky.edu]
Sent:Monday, January 23, 2012 4:43 PM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:darren.henderson@uky.edu]
Sent: Saturday, January 21, 2012 1:05 PM
To: [Dan Connolly; beninato.ohsu|mailto:beninato@ohsu.edu]
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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 C[onnolly \[dconn|mailto:dconnolly@kumc.edu][olly.kumc\]|mailto:lly@kumc.edu]
Sent: Friday, January 20, 2012 4:37 PM
To: [beninato.ohsu|mailto:beninato@ohsu.edu]
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:dconnolly@kumc.edu]
Sent: Friday, January 20, 2012 3:47 PM
To: [Nandan.Patibandla.childrens.harvard|mailto:Nandan.Patibandla@childrens.harvard.edu]
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:dconnolly@kumc.edu]
Sent: Friday, January 20, 2012 11:53 AM
To: [Nandan.Patibandla.childrens.harvard|mailto:Nandan.Patibandla@childrens.harvard.edu]
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:dconnolly@kumc.edu]
Sent: Friday, January 20, 2012 10:59 AM
To: [darren.henderson.uky|mailto:darren.henderson@uky.edu]
Cc: [me|mailto:members@i2b2aug.org][mbers.i2b2aug|mailto:mbers@i2b2aug.org]
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\]|mailto:beninato@ohsu.edu]
Sent:Friday, January 20, 2012 11:53 AM
To: [Henderson, Darren W; i2b2 AUG Members|mailto:members@i2b2aug.org]
Subject:RE: control over style of GENERATED_SQL?
\\
Might want to check statistics are current too...
\\
\\
From: Henderson, Darren [W \[darren.henderson.uky|mailto:darren.henderson@uky.edu]\]
Sent: Friday, January 20, 2012 8:35 AM
To: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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 C[onnolly \[dconn|mailto:dconnolly@kumc.edu][olly.kumc\]|mailto:lly@kumc.edu]
Sent: Friday, January 20, 2012 10:58 AM
To: Henderson, Darren W
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:Nandan.Patibandla@childrens.harvard.edu]
Sent:Friday, January 20, 2012 11:17 AM
To:Dan Connolly
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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 C[onnolly \[dconn|mailto:dconnolly@kumc.edu][olly.kumc\]|mailto:lly@kumc.edu]
Sent: Friday, January 20, 2012 10:59 AM
To: [darren.henderson.uky|mailto:darren.henderson@uky.edu]
Cc: [i2b2 AUG Members|mailto:members@i2b2aug.org]
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\]|mailto:beninato@ohsu.edu]
Sent:Friday, January 20, 2012 10:54 AM
To: [Dan Connolly; i2b2 AUG Members|mailto:members@i2b2aug.org]
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:
\###############################################################