WebAPI/ CIRCE release

Developers
1

All,
WebAPI and CIRCE has been released: http://www.ohdsi.org/web/circe/.

New features:

  • New Generate Button: you can now generate cohorts (after saving them first).
  • New database table: cohort_definition_info: contains information about the cohort generation (if it exists). Currently consists of isValid (false if there were errors), start time, duration, and status.
  • New Jobs: GenerateChort: will generate a cohort definition in a background task.

Thank you.

-Chris

2

Chris,

I created a test cohort definition called “Females with Hairy Cell Leukemia” and “Preterm Infant”. When I click the “Show SQL” button, the MSSQL and Postgres look identical and the Redshift SQL looks a lot like Postgres, but will not execute on a my local Postgres 9.2.

Bill

3

Hi, Bill.

Glad you are trying it! As it turns out, I had the wrong dialect key for postgresql in the app. I’ve updated it and pushed it out. The temp tables are now being created with ‘Create TEMP table’ instead of leaving it as # for mssql. Thank you for catching this!

I’ve generated the sql but unfortunately I do not have a CDM to test the query on.

The other thing to note is that the generated SQL has @tokens left in the text that needs to be replaced. If you click the ‘add options’ button next to the Show SQL button, you will be provided with text boxes to input the replacement values. If you do that, you should get runnable sql right out of the gate.

The values are:
CDM Schema: this is prefixed in front of the cdm tables (for example @CDM_schema.CONCEPT). Set this value to the value of your cdm schema.
Target Schema: Not used! I need to take this out.
Target Table: The fully qualified table name that you want to write the cohort results to. soemthing like {schema}.{cohortTable}
Cohort Def Id: A number you want to assign to this cohort.

Note: when clicking on generate, it gets these values from the configuration of the WebAPI. The ShowSQL (and Options) are just for testing/preview purposes. Setting these values in ‘Add Options’ has no impact on what happens when you click ‘Generate’.

-Chris

4

Chris,

Thanks for the fast turn around. Most of the Postgres SQL is working now. The new issue is exracting parts of dates:

– generated code
JOIN public.PERSON P on C.person_id = P.person_id
WHERE DATEPART(year, C.condition_start_date) - P.year_of_birth <= 2

It appears that SQLRender may not be replacing the MSSQL datepart with the Postgres equivalent function:

Either of these makes the SQL run:

WHERE DATE_PART(‘year’, C.condition_start_date) - P.year_of_birth <= 2

WHERE EXTRACT(year from C.condition_start_date) - P.year_of_birth <= 2

Bill

5

Yeah, in SqlRender we handle converting the MSSQL function ‘YEAR’ (ex;
YEAR(C.CONDITION_START_DATE) ), but we never had a use for the DATEPART
function so I don’t think it’s in there. We could just change it from
DATEPART to YEAR and it’ll work cross-platform.

6

Ok, I swtiched from using datepart to year() and it converts to EXTRACT(year from X) on postgres.

This was just pushed, you should see it on the public site in a few minutes.

-Chris

7

@Chris_Knoll

It’s ALIVE!!! I’ll try some more complicated cohorts soon.

Bill