relationship between several i2b2 components for new projects

[Chen, Wei]

I am trying to create a new project, populate new data for the project and let users access it. Sounds like a really simple task. However, I am a little confused with a few things which prevent me from moving on. Hope someone can help me… Thanks in advance.

So the demo project after a complete install is under the domain i2b2demo and project Demo. Is this correct? To add a new project, we don’t need to modify either web client code or admin code as it is ok to be under the same domain. I assume?

The default i2b2 server only hosts one project called Demo.
I first created a new project in Admin called Demo2 as:
id: Demo2
name: i2b2 Demo2
path: /Demo2
After this, the pm_project_data table has been affected.

Then, I created a new user called Demo2user in the admin. Then, under manager projects we associated the new user to the new project Demo2.

So far, everything should be all ok?

At this point, I am not sure about how to add new / update existing data tables in the i2b2 database (under 6 schemes of course) and associate them with the new project. I think I should update the table_access table to control the view of the concept tree in the web client as well as qt_breakdown_path? Beside, how does a specific project know which table and records in the table to access for a query. Is this done through the level 0 word in the path of c_fullname or through the field sourcesystem_cd?

During the cell install of the JBoss deployment step, there is a configuration step for the bypass project Demo. Do I need to set this for the new project and rerun the JBoss deployment and cell install?

Could you provide some guidance on this? I have read both the design documents and online install guide.

[Phillips, Lori C.]

This is somewhat out of date but should help you .. where it refers to PM/Gridsphere substitute admin tool features.

Lori

-----Original Message-----
From: i2b2-ins...@googlegroups.com [mailto:i2b2-ins...@googlegroups.com] On Behalf Of Chen, Wei
Sent: Thursday, April 24, 2014 4:22 PM
To: i2b2-ins...@googlegroups.com
Cc: mem...@i2b2aug.org
Subject: relationship between several i2b2 components for new projects

I am trying to create a new project, populate new data for the project and let users access it. Sounds like a really simple task. However, I am a little confused with a few things which prevent me from moving on. Hope someone can help me... Thanks in advance.

So the demo project after a complete install is under the domain i2b2demo and project Demo. Is this correct? To add a new project, we don't need to modify either web client code or admin code as it is ok to be under the same domain. I assume?

The default i2b2 server only hosts one project called Demo.
I first created a new project in Admin called Demo2 as:
id: Demo2
name: i2b2 Demo2
path: /Demo2
After this, the pm_project_data table has been affected.

Then, I created a new user called Demo2user in the admin. Then, under manager projects we associated the new user to the new project Demo2.

So far, everything should be all ok?

At this point, I am not sure about how to add new / update existing data tables in the i2b2 database (under 6 schemes of course) and associate them with the new project. I think I should update the table_access table to control the view of the concept tree in the web client as well as qt_breakdown_path? Beside, how does a specific project know which table and records in the table to access for a query. Is this done through the level 0 word in the path of c_fullname or through the field sourcesystem_cd?

During the cell install of the JBoss deployment step, there is a configuration step for the bypass project Demo. Do I need to set this for the new project and rerun the JBoss deployment and cell install?

Could you provide some guidance on this? I have read both the design documents and online install guide.

--
You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-h...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


The information in this e-mail is intended only for the person to whom it is
addressed. If you believe this e-mail was sent to you in error and the e-mail
contains patient information, please contact the Partners Compliance HelpLine at
http://www.partners.org/complianceline . If the e-mail was sent to you in error
but does not contain patient information, please contact the sender and properly
dispose of the e-mail.

[Chen, Wei]

More specifically, if we create a new project. Do we need to redeploy each Cell on Jboos? Since we have a project specific bypass setting in the spring/*.properties such as below:
queryprocessor.ws.pm.bypass.project=Demo

[Peter Beninato]

Hi,

I think the cells will need to be redeployed via the ANT scripts, since, as indicated below that the new datasources need to be setup in the ds.xml files.

The hive and pm cells are used across the application, but as projects that involve other schemas/data/tables are added, then the xml files are edited for the new datasources, and the ant targets need to be run.


-Peter

-----Original Message-----
From: Phillip Reeder [mailto:Phillip...@UTSouthwestern.edu]
Sent: Thursday, April 24, 2014 2:10 PM
To: Chen, Wei; i2b2-ins...@googlegroups.com
Cc: mem...@i2b2aug.org
Subject: Re: relationship between several i2b2 components for new projects

The cells do not need to be re-deployed.

At a minimum, you will need to create the new data/CRC tables and metadata tables. Create new connections in the .xml files. Then in the CRC_DBLOOKUP and ONT_DBLOOKUP, add new rows for the new project to point them to the new DB connections. You should be able to look at the DBLOOKUP rows for the demo project to figure out the pattern.

Hopefully that will point you in the right direction as far as adding a new project.

Phillip

On 4/24/14, 3:58 PM, "Chen, Wei" <chen...@gmail.com> wrote:

>More specifically, if we create a new project. Do we need to redeploy
>each Cell on Jboos? Since we have a project specific bypass setting in
>the spring/*.properties such as below:
>queryprocessor.ws.pm.bypass.project=Demo
>
>On Apr 24, 2014, at 4:21 PM, Chen, Wei <chen...@gmail.com> wrote:
>
>> I am trying to create a new project, populate new data for the
>>project and let users access it. Sounds like a really simple task.
>>However, I am a little confused with a few things which prevent me from moving on.

>>Hope someone can help meŠ Thanks in advance.

________________________________

UT Southwestern Medical Center
The future of medicine, today.

[David]

This is great information! So can someone explain the bypass.project part in the crc.properties? Does the "Demo" project name need be replaced or a config line for a new project should be inserted? This case will require redeployment of the cell. Please advise...

[Chen, Wei]

Many thanks, Peter and Phil. I will try this in 1.7 and let you know.

So based on what’s been discussed. Create new crc and ontology schemes for hosting new project data tables and modify *.xml to point to new user-name which is the same as the scheme. Is this correct?

That said, we can only host one project at any time? How can we host multiple projects at the same time and control user access to different projects. This is the minimum requirements of any information system, I assume.

Please give some pointers if you have done this for your projects. Thanks.

On Apr 24, 2014, at 7:06 PM, Phillip Reeder <Phillip...@UTSouthwestern.edu> wrote:

> In 1.7, Ant deploys the ds.xml files to the
> /opt/jboss/standalone/deployments folder. They can be edited there. I
> can’t remember if they are read on the fly or on started, but at most, you
> would need to restart jboss for them to be recognized. It worked for i2b2
> 1.6 so I’m assuming it will work for 1.7.
>
> Phillip

[Natus]

Hello all,

That said, we can only host one project at any time?

No, you can host muliple projects on the same database (or if you want host projects on other servers). You have to populate hive tables, pm tables (which user can access to  which project) and the crc-ds.xml / ont-ds.xml. If you want more details, in few days I have to write a pdf with instructions to make manually new projects.

QUESTION : You guys know if the webclient's "project request plugins" (made by Mike Mendis) works ? It doesn't seems to create project in the same way (manually create crc-datamarts, edit jboss file...). Can anybody explain the goal of this plugin ? (note that it's buggy with POSTGRESQL as this topic shows https://groups.google.com/forum/#!topic/i2b2-install-help/SbsE9X9mHT8)

about project request plugin :

This form allows a request for a new project data mart to be created. Please make the notations on the form by choosing from the options available or dragging items from existing patient sets or concepts in the current project into the boxes below. If these items are not yet created, please exit from the form to create them and then return to this form.

Thanks

[Chen, Wei]

Natus and Phil,

     That would be great if you can share more details with me on creating new projects. It would be better if it can touch a little on the performance discussion in terms of reducing redundancies and component sharing.  But at least based on your pointers, I have enough information to get started for now.

[ITLackey]

Natus,

I agree it would be great it you would share the information you are putting together. I have been silently following this thread and am coming to many of the same questions.

Does anyone know if it would be possible to define the datasource xml in the database instead of needing to modify the *-ds.xml files each time? ideally, the xml could be hosted inside of the *_DB_LOOKUP tables instead of just a name reference.

Thanks!

Ian 

[Peter Beninato]

Hi,

I would not assume that you only need the data and metadata schemas. I would create the whole suite of schemas for each new project, except the hive and PM schemas as these are shared.

I would also go back and check out the documentation for the data and ontology cells (at least), I think we the documentation talks about editing the ds.xml files there might be some pointers in there as well about setting up additional datasources.

-Peter

-----Original Message-----
From: Chen, Wei [mailto:chen...@gmail.com]
Sent: Friday, April 25, 2014 6:17 AM
To: Phillip Reeder
Cc: Peter Beninato; i2b2-ins...@googlegroups.com; mem...@i2b2aug.org
Subject: Re: relationship between several i2b2 components for new projects

Many thanks, Peter and Phil. I will try this in 1.7 and let you know.

So based on what’s been discussed. Create new crc and ontology schemes for hosting new project data tables and modify *.xml to point to new user-name which is the same as the scheme. Is this correct?

That said, we can only host one project at any time? How can we host multiple projects at the same time and control user access to different projects. This is the minimum requirements of any information system, I assume.

Please give some pointers if you have done this for your projects. Thanks.

On Apr 24, 2014, at 7:06 PM, Phillip Reeder <Phillip...@UTSouthwestern.edu> wrote:

> In 1.7, Ant deploys the ds.xml files to the
> /opt/jboss/standalone/deployments folder. They can be edited there.
> I can’t remember if they are read on the fly or on started, but at
> most, you would need to restart jboss for them to be recognized. It
> worked for i2b2
> 1.6 so I’m assuming it will work for 1.7.
>
> Phillip
>
>
> On 4/24/14, 5:26 PM, "Peter Beninato" <beni...@ohsu.edu> wrote:
>

[Peter Beninato]

Hi,

I'm not exactly sure, but how I interpret this if you set the first property to TRUE, then anyone with the role of ADMIN, do not have to be validated via the PM cell when logging onto that project, they will be given access.

For clarity, maybe someone who does know what those properties do will respond.

-----Original Message-----
From: David [mailto:chen...@gmail.com]
Sent: Thursday, April 24, 2014 3:56 PM
To: Peter Beninato
Cc: Phillip Reeder; i2b2-ins...@googlegroups.com; mem...@i2b2aug.org
Subject: Re: relationship between several i2b2 components for new projects

This is great information! So can someone explain the bypass.project part in the crc.properties? Does the "Demo" project name need be replaced or a config line for a new project should be inserted? This case will require redeployment of the cell. Please advise...

[Chen, Wei]

Another issue we haven’t yet touched is the configuration folder on JBoss.

Use ontologyapp as an example…
since ontology is project specific, should the segment in the OntologyApplicationContext.xml

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="org.postgresql.Driver"/>
<property name="url" value="jdbc:postgresql://localhost:5432/i2b2""/>
<property name="username" value="i2b2metadata"/>
<property name="password" value="demouser"/>
<property name="defaultAutoCommit" value="false"/>
<property name="defaultReadOnly" value="true"/>
</bean>

be replaced by

<bean id="CRCBootstrapDS" class=“org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://localhost:5432/i2b2" />
<property name="username" value="i2b2hive" />
<property name="password" value="demouser" />
</bean>

The CRCLoaderApplicationContext.xml uses

<bean id="CRCBootstrapDS" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://localhost:5432/i2b2" />
<property name="username" value="i2b2hive" />
<property name="password" value="demouser" />
</bean>

[Phillips, Lori C.]

Mike can verify but I think the bypass.project parameter is a byproduct of the days before the PM was working.. i.e its no longer necessary.

-----Original Message-----
From: i2b2-ins...@googlegroups.com [mailto:i2b2-ins...@googlegroups.com] On Behalf Of David
Sent: Thursday, April 24, 2014 6:56 PM
To: Peter Beninato

[Chen, Wei]

I did the following modifications and did everything for adding a new project as previously discussed in the tread. However, I still can’t see the ontology structure for the new project.

When I log in the demo project, I got the following “correct” JBoss log

14:22:36,608 INFO [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-4) Located DataSource for hiveId=[i2b2demo] projectId=[/Demo/]
14:22:36,608 INFO [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-4) Using datasource java:/QueryToolDemoDS
14:22:36,661 INFO [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-5) Located DataSource for hiveId=[i2b2demo] projectId=[/Demo/]
14:22:36,661 INFO [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-5) Using datasource java:/QueryToolDemoDS
14:22:36,900 INFO [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-5) Located DataSource for hiveId=[i2b2demo] projectId=[/Demo/]
14:22:36,901 INFO [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-5) Using datasource java:/QueryToolDemoDS

When I log in the SleepDemo project (which is new), I got the following “incorrect” JBoss log and the Ajax Error.
14:24:49,477 INFO [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-8) Located DataSource for hiveId=[i2b2demo] projectId=[/SleepDemo/]
14:24:49,478 INFO [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-8) Using datasource java:/QueryToolSleepDemoDS
14:24:49,494 INFO [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-9) Located DataSource for hiveId=[i2b2demo] projectId=[/SleepDemo/]
14:24:49,495 INFO [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-9) Using datasource java:/QueryToolSleepDemoDS

Weirdly, in both cases, I used postgres while the fired msg points to OracleDAOFactory? I check the java files, there is no java files for *postgres*. So I am completely lost.

I don’t know what makes it work for the demo and not work for the new project. I simply replicate everything in the db for the new project by copy and modify the demo.

This is what I did…
Created four new schemas for the new project except for pm and hive.
Configged the table table_access for showing the catalog tree.
Modified all ds.xml files except pm and hive in the JBoss dir.
Modified several ApplicationContext.xml files in the JBoss configuration dir.
Populated tables for all new schemas.
Created new users for accessing the new schema.

[Mendis, Michael E.]

that is was used back in the day when pm was not developed or in development.

[Phillips, Lori C.]

Don’t assume "OracleDAOFactory" relates to Oracle only message; it’s a misleading, misnamed class:
Did you create something analogous to OntologyDemoDS for your sleep project and configure it in both the ont-ds.xml and the ONT_DB_LOOKUP table? Also I don’t see any error from your log below , the actual error message would be helpful.

[Chen, Wei]

Fixed the problem. There was an extra space in one of the c_domain_id values for the new project. My bad.

Thanks for all the help.

[Chen, Wei]

Is there a document explaining the table relations (e.g. join relationship) for query? I have a test dataset with only two records: one numeric and another nominal. I set up the key values across tables in crc but the query is running for ever.

I read all design documents. I assume they should include all we need for using i2b2..

[Nicolas Paris]

Mike could you please give some words about the "project request plugin"  you made ?

Is the goal is to request patients/ontologie concepts, and then the i2b2 administrator manually creates crc/ont datamart (maually as described in this topic)? (Note that the pgsql version crash)

Thanks by advance.

Nicolas PARIS

--
You received this message because you are subscribed to a topic in the Google Groups "i2b2 Install Help" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/i2b2-install-help/nYI3T16IfQk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to i2b2-install-h...@googlegroups.com.

[Mahbub Guljari Alam]

Hello Wei,

I think you have solved the issues creating a new project. Do you have any documentation how to create a new project ?
If you have could you share this with me ?

Any type of help is sincerely appreciable.

Thanks in advance.
Mahbub

[Phillips, Lori C.]

Does this help ?

--
You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-h...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

The information in this e-mail is intended only for the person to whom it is

[Gul Shahzad Sarwar]

Hello everybody,

Mahbub and I are working together and facing the same problem independently. We are trying to make a project called "CIHI" and want to link data from CIHI to this Project. We want an independent user interface and demodata for this project. Our login goes fine, we see user interface and full Ontology in Navigate Terms pane, but when we run a query, we get an error message as "ERROR: ERROR". We are trying to modify an existing guide to make a project for SQL Server 2014. Below is how we modified the guide. Can someone point out the mistake/error we are doing in the following guide? 

Thanks in advance.

Gul

Guide is as follows:

This is an attempt to document the process of creating an I2B2 Project for CIHI. The “CIHI” project will exist on the I2B2 domain/install but will point to the database cihidata. 

Duplicate i2b2demodata as cihidata

Duplicate i2b2metadata as cihimetadata

Duplicate i2b2workdata as cihiworkdata

Create login cihidata with the password 'demouser' and give it ownership of cihidata

Create login cihimetadata with the password 'demouser' and give it ownership of cihimetadata

Create login cihiworkdata with the password 'demouser' and give it ownership of cihiworkdata

1) STOP JBOSS

a. Press the Windows Key + R to open “Run”.

b. Type “Services.msc” and press the Enter key to display a list of installed services.

c. Scroll to the service called “JBoss Application Server 7.0.0. Final”, right click and Stop the service.

2) CRC Cell

a. Edit  the file C:\opt\i2b2\edu.harvard.i2b2.crc\etc\jboss\crc-ds.xml 

Add a new datasource using Notepad++ (Update the jndi-name and pool-name to reflect the name of the second project):

<datasource jta="false" jndi-name="java:/QueryToolCIHIDS"

    pool-name="QueryToolCIHIDS" enabled="true" use-ccm="false">

    <connection-url>jdbc:sqlserver://localhost:1433</connection-url>

    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

    <driver>sqljdbc4.jar</driver>

    <security>

        <user-name>cihidata</user-name>

        <password>demouser</password>

    </security>

    <validation>

        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>

        <validate-on-match>false</validate-on-match>

    </validation>

    <statement>

        <share-prepared-statements>false</share-prepared-statements>

    </statement>

</datasource>

b. Run ant target

In the directory,  CD C:\opt\i2b2\edu.harvard.i2b2.crc, run the ant target:

%ANT_HOME%\bin\ant.bat -f master_build.xml clean build-all deploy

3) ONTOLOGY CELL

a. Edit  the file 

C:\opt\i2b2\edu.harvard.i2b2.ontology\etc\jboss\ont-ds.xml 

Add a new datasource using Notepad++ (Update the jndi-name and pool-name to reflect the name of the second project):

<datasource jta="false" jndi-name="java:/OntologyCIHIDS"

        pool-name="OntologyCIHIDS" enabled="true" use-ccm="false">

    <connection-url>jdbc:sqlserver://localhost:1433</connection-url>

    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

    <driver>sqljdbc4.jar</driver>

    <security>

        <user-name>cihimetadata</user-name>

        <password>demouser</password>

    </security>

    <validation>

        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>

        <validate-on-match>false</validate-on-match>

    </validation>

    <statement>

        <share-prepared-statements>false</share-prepared-statements>

    </statement>

</datasource>

b. Run ant target

In the directory,  CD C:\opt\i2b2\edu.harvard.i2b2.ontology, run the ant target:

%ANT_HOME%\bin\ant.bat -f master_build.xml clean build-all deploy

4) WORKPLACE Cell

a. Edit  the file C:\opt\i2b2\edu.harvard.i2b2.workplace\etc\jboss\work-ds.xml 

Add a new datasource using Notepad++ (Update the jndi-name and pool-name to reflect the name of the second project):

<datasource jta="false" jndi-name="java:/WorkplaceCIHIDS"

    pool-name="WorkplaceCIHIDS" enabled="true" use-ccm="false">

    <connection-url>jdbc:sqlserver://localhost:1433</connection-url>

    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

    <driver>sqljdbc4.jar</driver>

    <security>

        <user-name>cihiworkdata</user-name>

        <password>demouser</password>

    </security>

    <validation>

        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>

        <validate-on-match>false</validate-on-match>

    </validation>

    <statement>

        <share-prepared-statements>false</share-prepared-statements>

    </statement>

</datasource>

b. Run ant target

In the directory,  CD C:\opt\i2b2\edu.harvard.i2b2.workplace, run the ant target:

%ANT_HOME%\bin\ant.bat -f master_build.xml clean build-all deploy

5) SCHEMA DATA UPDATES

a. i2b2hive

In the i2b2hive database, entries need to be created for the datasources edited and deployed in the steps above. In particular the tables CRC_DB_LOOKUP, ONT_DB_LOOKUP, and WORK_DB_LOOKUP need INSERT statements.

USE i2b2hive

GO

INSERT INTO [dbo].[CRC_DB_LOOKUP] VALUES

('i2b2demo', '/CIHI/', '@', 'cihidata.dbo', 'java:/QueryToolCIHIDS', 'SQLSERVER', 'cihidata', NULL, NULL, NULL, NULL, NULL);

INSERT INTO [dbo].[ONT_DB_LOOKUP] VALUES

('i2b2demo', 'CIHI/', '@', 'cihimetadata.dbo', 'java:/OntologyCIHIDS', 'SQLSERVER', 'cihimetadata', NULL, NULL, NULL, NULL, NULL);

INSERT INTO [Dbo].[WORK_DB_LOOKUP] VALUES

('i2b2demo', 'CIHI/', '@', 'cihiworkdata.dbo', 'java:/WorkplaceCIHIDS', 'SQLSERVER', 'cihiworkdata', NULL, NULL, NULL, NULL, NULL);

b. New cihimetadata inserts: (I first inserted this, but later on deleted this entry in TABLE_ACCESS. It did not make any difference)

For “TABLE ACCCESS” INSERT

USE cihimetadata
GO
INSERT INTO [dbo].[TABLE_ACCESS] VALUES
('i2b2', 'i2b2', 'N', 0, '\i2b2\', 'Ontology', 'N', 'CA', NULL, NULL, NULL, 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\i2b2\', NULL, 'Ontology', NULL, NULL, NULL, NULL);

6) New cihiworkdata inserts: (for this I delete original C_INDEX with the value of 100 as this is a pk)

USE cihiworkdata

GO

INSERT INTO [dbo].[WORKPLACE_ACCESS] VALUES

('demo', 'WORKPLACE', 'N', 0, 'SHARED', 'shared', 'CIHI', 'Y', '100', NULL, 'CA', 'SHARED', NULL, NULL, NULL);

INSERT INTO [dbo].[WORKPLACE_ACCESS] VALUES

('demo', 'WORKPLACE', 'N', 0, 'gul', 'gul', 'CIHI', 'N', '101', NULL, 'CA', NULL, NULL, NULL, NULL);

7) START JBOSS

a. Press the Windows Key + R to open Run.

b. Type Services.msc and press the Enter key to display a list of installed services.

c. Scroll to the service called “JBoss Application Server 7.0.0. Final”, right click and Start the service.

8) CREATE PROJECT:

a. Logon to i2b2 with user that has “Adminstrator” privledge. 

b. Click on “Manage Projects”

c. Click on “Add New Project”

d. Make name of Project name the same as used in Config file for “DS”. In this case Project Id = CIHI and Project Name = CIHI.

e. Add the path = /CIHI and Save Updates

f. Add Users to new Project as needed with permissions.

9) CIHI new Project:

a. Logon as user with rights to project and run a query. Your ontology should get query results . Add Previous Query to Workplace.

[Nicolas Paris]

Hello,

Difficult to debug your procedure without trying it. As metadata works
fine, then maybe an error in chihidata.

Have you populated the star schema, and specially concept_dimension table ?
What are the jboss logs during query ?
Is the chihidata.qt_uery_master table get a new row when querying and
what is the chihidata.qt_query_master.generated_sql then ?

> You received this message because you are subscribed to a topic in the

> Google Groups "i2b2 Install Help" group.

> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/i2b2-install-help/nYI3T16IfQk/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Peter Beninato]

Hello,

I have seen ERROR ERROR sometimes, I'm not sure if it ever finds it way to qt_query_master.

My experience has been that when reusing a query, sometimes, if the query has a temporal constraint, The overall Temporal Constraint may indicate "Treat Independently", but since it was a "SAME FINANCIAL ENCOUNTER QUERY", there would be a panel that was set to "SAME FINANCIAL ENCOUNTER". This mismatch between the panels and the overarching setting would cause ERROR.

Another example, which is more along the lines as outlined by Nicolas Paris is if I had run a query for MED in a prior month, and go to re-use the query, and the formulary changed, so the path to the MED can not be found. This would also throw ERROR ERROR.
In this case, there is a disconnect between the query definition and the existing ontology (i2b2 table), and /or the concept_dimension.

As indicated below, try to run a simple query, see if it makes it to qt_query_master. And examine the JBOSS server.log.

I think with the Web compatibility mode, if there an error is detected in the webclient, it might never make it into the JBOSS log. It seems with Web compatibility, if there is an error, then the webclient stops the processing, so, you might also want to try to debug via the web client. Via f12 bring up Firebug, and put a breakpoint on, and then step through it.

-Peter

[Gul Shahzad Sarwar]

My jboss server log files says like this:

08:53:29,064 INFO  [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-7) Located DataSource for hiveId=[i2b2demo] projectId=[/CIHI/]

08:53:29,064 INFO  [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-7) Using datasource java:/QueryToolCIHIDS

08:53:29,079 INFO  [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-7) Located DataSource for hiveId=[i2b2demo] projectId=[/CIHI/]

08:53:29,079 INFO  [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-7) Using datasource java:/QueryToolCIHIDS

08:53:29,173 INFO  [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (Thread-113) Located DataSource for hiveId=[i2b2demo] projectId=[/CIHI/]

08:53:29,173 INFO  [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (Thread-113) Using datasource java:/QueryToolCIHIDS

08:53:29,189 INFO  [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (Thread-113) Located DataSource for hiveId=[i2b2demo] projectId=[/CIHI/] and ownerId =[@]

08:53:29,189 INFO  [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (Thread-113) Using datasource java:/QueryToolCIHIDS

08:53:29,282 INFO  [stdout] (Thread-113) 

08:53:29,282 INFO  [stdout] (Thread-113) <*>

08:53:29,282 INFO  [stdout] (Thread-113)  insert into #dx (  patient_num   ) select * from ( select distinct  patient_num  from #global_temp_table where panel_count = 0 ) q

08:53:29,313 INFO  [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-7) Located DataSource for hiveId=[i2b2demo] projectId=[/CIHI/]

08:53:29,313 INFO  [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-7) Using datasource java:/QueryToolCIHIDS

08:53:33,057 INFO  [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--127.0.0.1-9090-7) Located DataSource for hiveId=[i2b2demo] projectId=[/CIHI/]

08:53:33,057 INFO  [edu.harvard.i2b2.crc.dao.OracleDAOFactory] (http--127.0.0.1-9090-7) Using datasource java:/QueryToolCIHIDS

08:53:33,057 ERROR [stderr] (http--127.0.0.1-9090-7) org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update cihidata.dbo.qt_query_master set delete_flag =?,delete_date=? where query_master_id = ? and delete_flag = ?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'false' to data type int.

08:53:33,057 ERROR [stderr] (http--127.0.0.1-9090-7) at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)

08:53:33,057 ERROR [stderr] (http--127.0.0.1-9090-7) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

08:53:33,057 ERROR [stderr] (http--127.0.0.1-9090-7) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

08:53:33,057 ERROR [stderr] (http--127.0.0.1-9090-7) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

08:53:33,057 ERROR [stderr] (http--127.0.0.1-9090-7) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)

[Nicolas Paris]

ERROR [stderr] (http--127.0.0.1-9090-7)
org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback; bad SQL grammar [update
cihidata.dbo.qt_query_master set delete_flag =?,delete_date=? where
query_master_id = ? and delete_flag = ?]; nested exception is
com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed
when converting the nvarchar value 'false' to data type int.

=> looks like a cast bugs for sql server 2014. But this may not be
the main problem. this query(delete_flag =?,delete_date=? ) appens
when query has a problem
Do query works on the i2b2demodata project ?
Can you provide the generated sql for this query (as I explained in last post)

[Mendis, Michael E.]

Do you have the AGG_SERVICE_ACCOUNT on this project and with at least agg and obfuscated permissions?

-mike

[Gul Shahzad Sarwar]

Thanks mike, you are great,
Addition of AGG_SERVICE_ACCOUNT on CIHI project solved the problem.

Gul

To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-help+unsub...@googlegroups.com.

[Gul Shahzad Sarwar]

Many thanks for helping me to make an i2b2 project using SQL Server 2014. Attached is a complete guide how to make an i2b2 project.

Gul

[Phillip Reeder]

A few tips/tricks I'd like to add:

I'll often combine my crc, work, and metadata databases into a single user. If you have multiple projects, the x3 for every new project can add a bunch of users/schemas.

I don't believe you have to redeploy i2b2 to take advantage of the crc-ds.xml files.  You just have to change them in the jboss folder and i believe jboss will automatically pick up the updated connection file, or at least it will on Linux.  Just have to remember to backup xml files if you redeploy later. 

I'll sometimes create a second project with a different terminology I'm working on, and point it to the existing crc data. This can work well if you have, for example, a secondary set of data that you add to your primary crc cell, that you only want a set of people to access. 

You can also uses views/synonyms from a new crc database to the observation_fact table in your primary crc database. And create a new terminology/concept dimension. It will allow you to not have to duplicate the huge data/indexes.  I've used this to deploy a second project where the data is the same, but I need a different terminology/map of the data for a shrine network. 

These may not be the way the i2b2 developers intended it to be used, but it works for me and might work for some of you as well. 

Phillip

Sent from my iPhone

On Nov 4, 2015, at 2:41 PM, Gul Shahzad Sarwar <sarw...@gmail.com> wrote:

Many thanks for helping me to make an i2b2 project. Here is a complete guide how I successfully created a project using SQL Server 2014:

 

CREATING AN I2B2 PROJECT

This is an attempt to document the process of creating an I2B2 Project for CIHI using SQL SERVER 2014 on Windows 7. The “CIHI” project will exist on the I2B2 domain/install but will point to the database cihidata. However, there will need to be some data updates written to the installed databases that provide command and control for the installed i2b2 instance.

1.       CREATE THREE DATABASES

a.       Duplicate i2b2demodata as cihidata

b.      Duplicate i2b2metadata as cihimetadata

c.       Duplicate i2b2workdata as cihiworkdata

2.       CREATE  NEW LOGINS

a.       Create user (new login) cihidata with the password 'demouser' and give it ownership of cihidata

b.      Create user (new login) cihimetadata with the password 'demouser' and give it ownership of cihimetadata

c.       Create user (new login) cihiworkdata with the password 'demouser' and give it ownership of cihiworkdata

d.      Login each account and check if you have access to the respective database.

3.       STOP JBOSS

a.       Press the Windows Key + R to open “Run”.

b.      Type “Services.msc” and press the Enter key to display a list of installed services.

c.       Scroll to the service called “JBoss Application Server 7.0.0. Final”, right click and Stop the service.

4.       CRC Cell

a.       Edit  the file C:\opt\i2b2\edu.harvard.i2b2.crc\etc\jboss\crc-ds.xml

Add a new datasource using Notepad++ (Update the jndi-name and pool-name to reflect the name of the second project):

<datasource jta="false" jndi-name="java:/QueryToolCIHIDS"

    pool-name="QueryToolCIHIDS" enabled="true" use-ccm="false">

    <connection-url>jdbc:sqlserver://localhost:1433</connection-url>

    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

    <driver>sqljdbc4.jar</driver>

    <security>

        <user-name>cihidata</user-name>

        <password>demouser</password>

    </security>

    <validation>

        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>

        <validate-on-match>false</validate-on-match>

    </validation>

    <statement>

        <share-prepared-statements>false</share-prepared-statements>

    </statement>

</datasource>

 

 

b.      Run ANT target to deploy modified crc-ds.xml

In the directory,  CD C:\opt\i2b2\edu.harvard.i2b2.crc, run the ant target:

%ANT_HOME%\bin\ant.bat -f master_build.xml clean build-all deploy

5.       ONTOLOGY CELL

a.       Edit  the file C:\opt\i2b2\edu.harvard.i2b2.ontology\etc\jboss\ont-ds.xml

Add a new datasource using Notepad++ (Update the jndi-name and pool-name to reflect the name of the second project):

<datasource jta="false" jndi-name="java:/OntologyCIHIDS"

        pool-name="OntologyCIHIDS" enabled="true" use-ccm="false">

    <connection-url>jdbc:sqlserver://localhost:1433</connection-url>

    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

    <driver>sqljdbc4.jar</driver>

    <security>

        <user-name>cihimetadata</user-name>

        <password>demouser</password>

    </security>

    <validation>

        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>

        <validate-on-match>false</validate-on-match>

    </validation>

    <statement>

        <share-prepared-statements>false</share-prepared-statements>

    </statement>

</datasource>

b.      Run ANT target to deploy modified ont-ds.xml

In the directory,  CD C:\opt\i2b2\edu.harvard.i2b2.ontology, run the ant target:

%ANT_HOME%\bin\ant.bat -f master_build.xml clean build-all deploy

6.       WORKPLACE Cell

a.       Edit  the file C:\opt\i2b2\edu.harvard.i2b2.workplace\etc\jboss\work-ds.xml

Add a new datasource using Notepad++ (Update the jndi-name and pool-name to reflect the name of the second project):

<datasource jta="false" jndi-name="java:/WorkplaceCIHIDS"

    pool-name="WorkplaceCIHIDS" enabled="true" use-ccm="false">

    <connection-url>jdbc:sqlserver://localhost:1433</connection-url>

    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

    <driver>sqljdbc4.jar</driver>

    <security>

        <user-name>cihiworkdata</user-name>

        <password>demouser</password>

    </security>

    <validation>

        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>

        <validate-on-match>false</validate-on-match>

    </validation>

    <statement>

        <share-prepared-statements>false</share-prepared-statements>

    </statement>

</datasource>

b.      Run ANT target to deploy modified work-ds.xml

In the directory,  CD C:\opt\i2b2\edu.harvard.i2b2.workplace, run the ant target:

%ANT_HOME%\bin\ant.bat -f master_build.xml clean build-all deploy

7.       DATABASE UPDATES

a.       i2b2hive inserts:

In the i2b2hive database, entries need to be created for the datasources created and deployed in the steps above. In particular the tables CRC_DB_LOOKUP, ONT_DB_LOOKUP, and WORK_DB_LOOKUP need INSERT statements (If you are using an identity management cell for the new project, you need to create an  IM database and insert an entry in dbo.IM_DB_LOOKUP too).

USE i2b2hive

GO

INSERT INTO [dbo].[CRC_DB_LOOKUP] VALUES

('i2b2demo', '/CIHI/', '@', 'cihidata.dbo', 'java:/QueryToolCIHIDS', 'SQLSERVER', 'cihidata', NULL, NULL, NULL, NULL, NULL);

INSERT INTO [dbo].[ONT_DB_LOOKUP] VALUES

('i2b2demo', 'CIHI/', '@', 'cihimetadata.dbo', 'java:/OntologyCIHIDS', 'SQLSERVER', 'cihimetadata', NULL, NULL, NULL, NULL, NULL);

INSERT INTO [dbo].[WORK_DB_LOOKUP] VALUES

('i2b2demo', 'CIHI/', '@', 'cihiworkdata.dbo', 'java:/WorkplaceCIHIDS', 'SQLSERVER', 'cihiworkdata', NULL, NULL, NULL, NULL, NULL);

 

b.      New cihiworkdata inserts:

Note: First delete an existing C_NAME = SHARED entry with C_INDEX = 100

USE cihiworkdata

GO

INSERT INTO [dbo].[WORKPLACE_ACCESS] VALUES

('demo', 'WORKPLACE', 'N', 0, 'SHARED', 'shared', 'CIHI', 'Y', '100', NULL, 'CA', 'SHARED', NULL, NULL, NULL);

INSERT INTO [dbo].[WORKPLACE_ACCESS] VALUES

('demo', 'WORKPLACE', 'N', 0, 'gul', 'gul', 'CIHI', 'N', '101', NULL, 'CA', NULL, NULL, NULL, NULL);

 

8.       START JBOSS

a.       Press the Windows Key + R to open Run.

b.      Type Services.msc and press the Enter key to display a list of installed services.

c.       Scroll to the service called “JBoss Application Server 7.0.0. Final”, right click and Start the service.

9.       CREATE PROJECT:

a.       Logon to i2b2 with user that has “Adminstrator” privledge.

b.      Click on “Manage Projects”

c.       Click on “Add New Project”

d.      Make name of Project name the same as used in configuration file for “DS”. In this case Project Id = CIHI and Project Name = i2b2 CIHI.

e.      Add the path = /CIHI and Save Updates

f.        Add the AGG_SERVICE_ACCOUNT on CIHI project with at least “Aggregated” and “Obfuscated” permissions. You may add “Manager” and “User” permissions too. (This step is very important, otherwise you want be able to run queries.)

g.       Add Users (e.g., “gul”) to new Project as needed with permissions.

10.   CIHIDATA new Project:

a.       Logon as user with rights to project and run a query. Your ontology should get query results. Add Previous Query to Workplace.

 

EXTRA INFO

The HIVE and PM cells will be shared between your projects.

On Wednesday, 4 November 2015 11:22:27 UTC-5, Gul Shahzad Sarwar wrote: