Postgres connection for Olympus

Developers
102

Hi everybody,

I added a record to this table and all checkboxes are now shown. I selected all of them (lets give it a try…) and pressed the “Begin Analysis” button. Job is now created and running. Hope to bring good news tomorrow. So far so good… Fingers crossed… :wink:

Marcel

1 Like
103

That is great news. Yes - if you run all of them, it will take a while (depending on the size of your cohort).

Exciting!

1 Like
104

Hi all,

well the building took 9 hours to run and it look like it is finished without errors. At least there are no errors on screen.

But… most graphs show empty, just a border with a caption. And for the prevalences that show a graph it looks like the cohort time is not used. It looks more like the observation time is used, but maybe because of the missing cohort population.

See logfile below. I quickly browsed over this and it looks like there “bad SQL grammer” and java exceptions in there.

2015-06-16 - Olympus (rename to txt).xls (762.9 KB)

The good thing is: every step that is made there is progress. At least I saw some graphs passing by after all these attempts. I hope the finish line is close by now;-)

Marcel

105

Hmm. I know @Frank had to make some changes to get Heracles to work in their new environment.
@Frank, are these similar to the errors you were seeing? If so, is it possible to commit those SQL changes w/o the other changes?

106

Marcel,

The cohort index date is always used in the following reports: Cohort
Specific, Conditions by Index, Drugs by Index, and Procedure by Index.

For everything else, by default Heracles looks at your cohort but over
their entire observation period. You can choose to limit your analyses to
only the Cohort start and end dates by selecting the “Cohort Period Only”
flag on Heracles Runner.

Jon

107

Hi Jon,

Yes, I noticed this flag in the settings. And I made sure I had set this on. We only want to see statistics in our cohort period. Rest is only in the database for history usage, but nevertheless very important for our research.

Thanks,

Marcel

108 
]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer
<img src="/uploads/default/145/458a99a51f21c0eb.png" width="65" height="21">

The above error message in the SQL, I’ve seen this commonly when running a v4 query against a v5 cdm where vocabulary_id has been changed from int to varchar(). (Looking forward to this being reverted in v6! :wink: )

Is there a v4 vs v5 mode in Heracles?

109

Heracles is designed for v5, given the cohort definition component.

110

Hi all,

today I gave a short demo of Achilles in Ghent (Belgium). Unfortunately I was not able to show them Olympus/Heracles. But I managed to show Achilles on a “stripped” CDM database that is simulating what the results of Heracles should look like.

I hope in the next coming weeks we can continue trying to get Olympus/Heracles to work. I’m still very curious about this tool.

Is it clear now what the reason is why we have these empty graphs? Is it correct that I have to wait for another new build?

Thanks,
Marcel

111

Hi Marcel,

Glad to hear about your Achilles demonstration. For the moment, Heracles will continue to be dependent on being able to access a database. The reason for this is that while Achilles is designed to be run once per dataset, Heracles may be run hundreds of times for different cohorts. Thus storage as offline JSON files is not an optimal solution.

That said, we will continue to work to speed up Heracles’ queries. And if members of the community want to create an offline version, then that would be a great way to learn about OHDSI applications.

I looked over your logfile and would like to get input from @schuemie and @Patrick_Ryan as to whether this is a SQL problem or a Postgres translation problem. It seems to relate to the CASTs in the queries.

All the errors are the same:

operator does not exist: character varying < integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Example:

select 
	--hr1.cohort_definition_id,
	hr1.index_year as X_CALENDAR_YEAR,
	c1.concept_name as SERIES_NAME,
	cast(hr1.age_decile*10 as varchar) || '-' || cast((hr1.age_decile+1)*10-1 as varchar) as TRELLIS_NAME,
	hr1.count_value as NUM_PERSONS,
	ROUND(CAST(1000*(1.0*hr1.count_value / t1.count_value) AS NUMERIC),5) as Y_PREVALENCE_1000PP
from (select cohort_definition_id,
	cast(stratum_1 as integer) as index_year,
	cast(stratum_2 as integer) as gender_concept_id,
	cast(stratum_3 as integer) as age_decile,
	count_value 
	from public.heracles_results
	where analysis_id in (1814)
	and cohort_definition_id in (1)
	and stratum_2 in (8507,8532)
	and stratum_3 >= 0 
	--and stratum_4 <10
) hr1
	inner join 
(
	select cast(stratum_1 as integer) as index_year,
	cast(stratum_2 as integer) as gender_concept_id,
	cast(stratum_3 as integer) as age_decile,
	count_value 
	from public.heracles_results 
	where analysis_id = 116
	and cohort_definition_id in (1)
) t1
on hr1.index_year = t1.index_year
and hr1.gender_concept_id = t1.gender_concept_id
and hr1.age_decile = t1.age_decile
inner join
cdm.concept c1
on hr1.gender_concept_id = c1.concept_id
]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
112

Hi,
stratum_1 - stratum_5 are varchars because these could be any type of value. However looking for an int value as the above quote is causing a problem. Isn’t not enough to cast the result in the select, you need to cast it where it is used (or wrap the values in a sub-select so that the results of the inner query are casted).

-Chris

113

These implicit casts is the only thing where SQL Server is less specific than other dialects. You’ll need to make these casts explicit in the template SQL to make it work on PostgreSQL as well, so for example

AND CAST(stratum_2 AS INT) IN (8507,8532)
114

I just added a section on implicit casts to the SqlRender vignette

115

Thanks @schuemie and @Chris_Knoll. @cahilton, let’s identify where these changes need to be made in Heracles.

@Chris_Knoll, any other applications where this pattern may occur?

116

I’ve actually made a few hundred changes to the queries in Heracles in the multihomed branch. An issue related to the casts occurs on the Microsoft PDW (APS) platform currently so we ended up casting concept identifiers to varchar instead of trying to cast stratum to int since values in the table could in fact be non-integers. This might impact this discussion.

117

We can start to look at these changes - but I think it might make a nightmare when @Frank tries to merge the multihome branch back to master.

Is there any chance we can put the SQL changes in ahead of the other multihomed changes?

118

No, the multihomed branches across applications require the multihomed branch of the WebAPI.

119

I have a change for this in https://github.com/OHDSI/WebAPI/issues/46

WebAPI and Olympus will have to be rebuilt.

I fixed this one and some others I found. I’m not sure if I found them all, but I tried to search for common patterns. This works fine in Oracle, but I still don’t have a way to test in Postgres.

Thanks.

120

Hi Charity,

Thanks for this, I will wait for a new build of Olympus and check it out on postgres.

Do you know where can I find info on new releases? Is there an email list or special forum? Or a website where I can see the latest versions of all software that I can check so now and then?

Thanks,
Marcel