Postgres connection for Olympus

jon_duke · June 10, 2015, 12:57pm

This thread has been moved to the Developers Forum

Chris_Knoll · June 10, 2015, 1:24pm

Marcel,
One of the advantages of how we used the software is that you don’t have to start from scratch between releases. You should be able to just get the new OLYMPUS and run it again pointing it at the same database. It will detect that it already did a database setup, and skip creating tables. I think the main fix that they are making is the value of the postgresql dialect code, so in the worst case you’ll have to recreate your configuration, but the configuration isn’t store din the database, so you can leave your current database and just configure olympus.

@alfranke: Correct me if I am wrong on this. I’d hate to see everyone start from an empty OHDSI schema between builds. Do you have instructions for people who have an existing OLYMPUS install who want to update their version to the newest build?

-Chris

Chris_Knoll · June 10, 2015, 1:27pm

Right, perfect, Marcel. This is the query that will be generated by CIRCE once the correct dialect code is set in the OLYMPUS app. But, if you want to do a quick test, you can copy that sql and replace the following tokens:

@cdm_database_schema: cdm
@cohort_definition_id: (make up some dummy value here we’ll remove the INSERT in next step)
Delete the INSERT INTO @target_database_Schema.@target_cohort_table line. This will change the query from a INSRT INTO to a SELECT FROM.

After making this modification, you can run the query, and you should get the rows you expect.

-Chris

mdewilde · June 10, 2015, 9:17pm

Hi Chris,

Since the databas migration is also part of Olympus and Olympus gets a new build, it seemed logic to me that I go back to a milestone where Olympus did not touch the datbase yet. So in this case I just want to start with a reproducable test situation without side effects of previous runs. I’m in test mode now.

Marcel

alfranke · June 11, 2015, 8:00pm

To run a new version of Olympus you just need to point to the new exec-war.

For example:
java -jar Olympus-1.0.0-exec.war

after receiving update:

java -jar Olympus-1.1.0-exec.war

Chris_Knoll · June 11, 2015, 9:34pm

Hey, Marcel. This is good that you are keeping that fresh start around, we’d like to make updates to the DDL for postgres to explicity set the schema that the tables are created in, so If you are willing I’d like you to test out the change if you have a spare cycles. THe change isn’t available yet, but I’ll put it on the top of my list.

-Chris

mdewilde · June 11, 2015, 10:38pm

Hi Chris,

I had problems restoring the database. Normally the restore was done in 1-2 hours. Yesterdays restore attempt took more than 16 hours and failed. The postgres process was completely frozen. No CPU activity and not HD activity. Postgres was in recovery mode or something like that. At least taht was the only sign of life I received. After some hours I dicided to reset the computer, delete the database and retry again on a new emoty dayabase. This looks succesfull. Have no clue what went wrong. Lets hope the restore of the postgres database is correct now. Ready to test what you want to test.

Take your time. Friday is my day-off so Monday is the first day I can try it out.

Because I have a couple of meetings next week (one of them for the whole day) I will only have about 2 days to spend on this before the demo in Belgium on June 22th. If we manage to get it up-and-running and also move it over to the laptop (without the patientdata) super!!! But I don’t think we should expect miracles after this long journey. If we cannot finish it on time I suggest to continue this mission afterwards.

I already have a backup plan for the demo n Belgium. I created a seperate cdm containing only the data in our cohort. Running the standalone Achilles on this database gives or less give is the same results as Hercules would give on the cohort. The only difference is that the prevalences calculations will not “see” the diagnosis codes before the cohort time. For example diabetes patients that have the DM code before the cohort period (that is now deleted because its before the cohort/observationtime) will not be recognised. But this is as close as we could get and good enough for the demo. We are not doing real science yet… Already moved this standalone Achilles environment over to the laptop. So this is ready to be demonstrated.

Have a nice weekend,
Marcel

jon_duke · June 12, 2015, 2:05pm

The latest OLYMPUS version has been posted up on ohdis.org.

Please test for correction of the postgresql dialect issue. i.e., Heracles and Circe should now work in Postgres.

As it is a new version (1.1.1), so the correct command would be:

mdewilde · June 12, 2015, 6:13pm

Hi Jon,

Thanks for the update. Monday I will try it out.

Have a nice weekend,
Marcel

mdewilde · June 15, 2015, 12:06pm

Hi everyone,

just tried the new version (v1.1.1). I started with a rollback of the database as was before I ran the first Olympus. So a filled cdm schema and an empty public schema. I started Olympus and let it create the tables in the public schema. After this I filled the cohort and cohort_definition tables using the specs Chris gave me, including the EXTERNAL_SOURCED tag.

After starting Olympus again I went to Heracles and there is the IPCI cohort. YES!!!

But… when I try to build the reports I got stuck. I clicked all Visualization packs and after “Begin Analysis” I recieve the message "Please selected at least one analysis to run. In the big box in the middle of the screen I expected items to be selected (under “Run Cohort Analyses (0)”. But this box is completely empty. If I remembered correctly I saw a demo one where you had to select items here.

Does anybody know whats going on here??? I hope its just simple user error on my side.

See attachment for logfile. 2015-06-15 - Olympus (rename tot txt).xls (66.5 KB)

Thanks,
Marcel

cahilton · June 15, 2015, 1:14pm

If you click on a checkbox in the main section, does it work? vs. clicking on the visualization packs. Those should work though. They should scroll to that section and check the required analyses.

What browser are you using?

I don’t think they have any web dependencies but I can double check.

Also, if there are errors, they may be on the browser console log instead of the server side logs.

mdewilde · June 15, 2015, 1:33pm

Hi Charity,

Problem is that there is nothing in the main section. I expected checkboxes here as well. Its empty.

I’m using Chrome and have it on incognito mode to prevent caching problems.

See screenshot below fro whats on screen now. I hope its readable and this was the console log you were referring to.

Noticed a couple of fonts we wants to download from the internet. This was in every new page I select. Probably this is the reason why I keep getting these “Resolving host…” delays. Or can it be that the labels of the checkboxes are not showing because of this?

Thanks,
Marcel

mdewilde · June 15, 2015, 1:36pm

It turns out you cannot click on the picture to zoom in this forum. But I just found out that if you zoom the page in the browser itself you can read the log perfectly.

Marcel

cahilton · June 15, 2015, 1:49pm

It looks like it is erroring on using external fonts, but I don’t think that would block the loading of the analysis types. Since I have to connect to the network to connect to our database, I can’t fully test it out, but I can remove those in the next release.

However, it looks like the HERACLES_ANALYSIS table is empty. That script runs as part of the flyway setup. (https://github.com/OHDSI/WebAPI/blob/master/src/main/resources/db/migration/oracle/V1.0.0.4__cohort_analysis_results.sql)

Can you see if there is anything in that table?

cahilton · June 15, 2015, 2:02pm

Actually, the font errors are coming from Olympus, so they shouldn’t affect Heracles.

mdewilde · June 15, 2015, 2:45pm

The table heracles_analysis is located in the public schema and has 167 records. Looks normal to me with all kind of “Numer of …” records and Analysis types like “PERSON” ,“OBSERVATION”, “VISITS” etc.

The Olympus log shows a null pointer exception on line 255. Can this have something to do with it???

cahilton · June 15, 2015, 3:03pm

That could be. It looks like the cause is line 114 on CohortDefinitionService:

github.com

OHDSI/WebAPI/blob/master/src/main/java/org/ohdsi/webapi/service/CohortDefinitionService.java#L114

    
      
          import java.util.Set;
          import java.util.stream.Collectors;
          import javax.ws.rs.core.Response.ResponseBuilder;
          
          
import static org.ohdsi.webapi.Constants.Params.COHORT_DEFINITION_ID;
          import static org.ohdsi.webapi.Constants.Params.JOB_NAME;
          import static org.ohdsi.webapi.Constants.Params.SOURCE_ID;
          import org.ohdsi.webapi.source.SourceService;
          import static org.ohdsi.webapi.util.SecurityUtils.whitelist;
          
          
/**
           *
           * @author cknoll1
           */
          @Path("/cohortdefinition")
          @Component
          public class CohortDefinitionService extends AbstractDaoService {
          
          
	private static final CohortExpressionQueryBuilder queryBuilder = new CohortExpressionQueryBuilder();
          
          
	@Autowired

It looks like you’re missing a row for your cohort in COHORT_DEFINITION_DETAILS (this would normally be created by Circe I think).

I think you may need to insert the cohort_definition_id and the expression (for us we just use:
{ PrimaryCriteria: {} }
as I don’t think it can be empty.

Here’s a sample of that table. You can tell the difference between the ones that were manually created and the ones created by Circe. I think inserting the row will get you past this error:

Chris_Knoll · June 15, 2015, 4:39pm

Hi,
that’s a bug on my part, I need to accommodate for those cohort definitions which do not have a details. I can checkin a fix for this. You shouldn’t need to add garbage data in to the CohortDefinitionDetails table to get this to work, but for NOW, you can do that so that it gets past this error, but long term, you shouldn’t need to do this.

-Chris

Chris_Knoll · June 15, 2015, 4:45pm

You can insert a empty string (note: ‘’ not NULL) in the cohot_definition_details. It won’t be parsed into JSON or anything, so the simplest insert is just an empty string.

-Chris

Chris_Knoll · June 15, 2015, 4:52pm

Ok, I’ve just pushed the fix for this (a simple null check) and tested internally in the EXTERNAL_SOURCED case where there is no record inserted into the cohort_definitoin_details table. Looks good. This will be available in Olympus on next refresh.