ACHILLES "Error loading report"

katy-sadowski · December 21, 2020, 10:44pm

Hi there,

I am running into an issue with ACHILLES in ATLAS and could use some help diagnosing the root cause. The Condition Occurrence, Drug Exposure, and Procedure reports do not show up; instead I get an error message “Error loading report”. I’ve done some digging on the forums and don’t see an obvious solution right now.

Some info about my instance:

OMOP CDM is in Redshift
ATLAS & WebAPI v2.7.7
CDM contains the MedDRA vocabulary
concept_hierarchy table has been generated
I ran ACHILLES in R and the achilles_results table has been populated, containing over 1 million rows
I ran this SQL query - https://github.com/OHDSI/Achilles/blob/80e6ae9363dd76992c73360c61e3b6afb9a9e5df/inst/sql/sql_server/export/condition/sqlConditionTreemap.sql - and it returns ~10k rows
I ran exportConditionToJson and it successfully generated the treemap file and a bunch of files in the conditions folder which are full of data. I was unable to get AchillesWeb to work, but that might be an unrelated issue

My theory right now is that this has to do with the fact that our condition_occurrence, procedure_occurrence, and drug_exposure tables contain events dated outside patients’ observation periods. Our measurement and visit_occurrence tables do not contain data outside the OP, and those 2 reports work fine in ATLAS. Based on other forum posts, it does not seem like this should prevent the reports from running, but maybe I’m wrong.

Finally - the RC/DRC counts in ATLAS are not populating for us. It seems that this feature is linked to ACHILLES so I wanted to cite that as a potentially related issue.

Thanks in advance for any help!

Katy

katy-sadowski · January 11, 2021, 5:48pm

I just wanted to bump this thread in case it got lost in the shuffle. Would really appreciate any insight if any folks have experienced a similar issue; thanks!

ssaranathan · January 21, 2021, 4:19pm

Hi @katy-sadowski,
I am running into the same issue.

Also, I don’t see concept_hierarchy table in my schema. How is this generated ?

I am running WebAPI v2.8 and CDM is 5.3. If you find any fixes, please post here.

Thanks,

Chris_Knoll · January 21, 2021, 4:44pm

This is the sourcecode for that table:

github.com

OHDSI/WebAPI/blob/master/src/main/resources/ddl/results/concept_hierarchy.sql


/*********************************************************************/
/***** Create hierarchy lookup table for the treemap hierarchies *****/
/*********************************************************************/

IF OBJECT_ID('@results_schema.concept_hierarchy', 'U') IS NULL
CREATE TABLE @results_schema.concept_hierarchy
(
  concept_id             INT,
  concept_name           VARCHAR(400),
  treemap                VARCHAR(20),
  concept_hierarchy_type VARCHAR(20),
  level1_concept_name    VARCHAR(255),
  level2_concept_name    VARCHAR(255),
  level3_concept_name    VARCHAR(255),
  level4_concept_name    VARCHAR(255)
);

The documentation describes how to set up a CDM results schema for Webapi:

katy-sadowski · January 21, 2021, 6:13pm

@Chris_Knoll could you please confirm whether or not ACHILLES requires all events to be within the observation period in order for a report to load? Thanks

Chris_Knoll · January 21, 2021, 6:21pm

I don’t think so: this is the query for the persons with condition by condition_concept_id:

github.com

OHDSI/Achilles/blob/master/inst/sql/sql_server/analyses/1000.sql

-- 1000	Number of persons with at least one condition occurrence, by condition_concept_id

--HINT DISTRIBUTE_ON_KEY(stratum_1)
select 1000 as analysis_id, 
	CAST(ce1.condition_CONCEPT_ID AS VARCHAR(255)) as stratum_1,
	cast(null as varchar(255)) as stratum_2, cast(null as varchar(255)) as stratum_3, cast(null as varchar(255)) as stratum_4, cast(null as varchar(255)) as stratum_5,
	COUNT_BIG(distinct ce1.PERSON_ID) as count_value
into @scratchDatabaseSchema@schemaDelim@tempAchillesPrefix_1000
from
	@cdmDatabaseSchema.condition_era ce1
group by ce1.condition_CONCEPT_ID
;

There’s no reference to observation period here, just a call to the condition-era table.

The “Error Loading Report” could be related to a few different things…but I don’t think it’s from a UI error (like a javascript problem). Do you know how to bring up the dev console in your browser? There may be a hint there as to the actual error, and you can also take a look in your WebAPI logs to see if there’s a query that failed.

The exporting JSON files is not related to Atlas: Atlas (WebAPI) reads the results tables directly for the reports in Atlas, while the JSON exported files is used in a different application (AchillesWeb) where no database server is required. In AchillesWeb, the process is to run the achilles analysis (which populates the database tables) and then you export JSON files which are consumed in AchillesWeb. If you’re only talking about Atlas, then ignore all the exported JSON.

ssaranathan · January 21, 2021, 8:03pm

Thanks @Chris_Knoll. I missed this step. I will run it now. Is it okay to run this script after the Syn 1K data is loaded? I am asking because, the script has been running for past couple of hours.

Thanks,

katy-sadowski · January 21, 2021, 8:09pm

That’s good to know; thanks. I will try inspecting the dev console and the WebAPI logs to see if the issue is apparent there. I’ll report back

Chris_Knoll · January 21, 2021, 9:24pm

Yes, it’s normal to load the data first, then create the results schema after.

katy-sadowski · February 5, 2021, 9:06pm

I inspected the dev console and the following error appears 2x each time I try to open one of the failing reports:

It appears that no queries are being run against my CDM database when I try to open these reports (nothing in the query logs).

For the WebAPI database, I get ~100 identical rows in the query log with this: execute <unnamed>: SELECT JOB_EXECUTION_ID, KEY_NAME, TYPE_CD, STRING_VAL, DATE_VAL, LONG_VAL, DOUBLE_VAL, IDENTIFYING from webapi.BATCH_JOB_EXECUTION_PARAMS where JOB_EXECUTION_ID = $1

Any idea what’s up here? The “bad SQL grammar” error seems it might indicate an issue with something that checks the syntax of the query that’s going to be run against Redshift (our CDM database).

Thanks so much!

Chris_Knoll · February 6, 2021, 3:32am

The query against webapi.batch_job_execution_params is not related to your achilles problem. The batch_job table is just polling for information about batch jobs.

The bad SQL grammar is a query against your CDM, and it’s when it’s trying to pull up the drug summary report. You will need to go to your WebAPI logs (ie: your tomcat logs) and find the exception, and then surrounding the exception should show what query is attempted that resulted in the error.

katy-sadowski · February 9, 2021, 8:09pm

Aha, thanks so much! I found it! Here is the offending query:

SELECT
   concept_hierarchy.concept_id AS concept_id,
   CONCAT(
     COALESCE(concept_hierarchy.level4_concept_name,'NA'), '||',
     COALESCE(concept_hierarchy.level3_concept_name,'NA'), '||',
     COALESCE(concept_hierarchy.level2_concept_name,'NA'), '||',
     COALESCE(concept_hierarchy.level1_concept_name,'NA'), '||',
     concept_hierarchy.concept_name
   ) AS concept_path,
   ar1.count_value AS num_persons,
   ROUND(CAST(1.0 * ar1.count_value / denom.count_value AS NUMERIC),5) AS percent_persons,
   ROUND(CAST(1.0 * ar2.count_value / ar1.count_value AS NUMERIC),5)   AS records_per_person
 FROM (SELECT *
       FROM results.achilles_results WHERE analysis_id = 700) ar1
   INNER JOIN
   (SELECT *
    FROM results.achilles_results WHERE analysis_id = 701) ar2
     ON ar1.stratum_1 = ar2.stratum_1
   INNER JOIN
   results.concept_hierarchy concept_hierarchy
     ON CAST(CASE WHEN CASE WHEN (CAST(ar1.stratum_1 AS VARCHAR) ~ '^([0-9]+\.?[0-9]*|\.[0-9]+)$') THEN 1 ELSE 0 END = 1 THEN ar1.stratum_1 ELSE null END AS INT) = concept_hierarchy.concept_id
   AND concept_hierarchy.treemap='Drug'
   ,
   (SELECT count_value
    FROM results.achilles_results WHERE analysis_id = 1) denom

 ORDER BY ar1.count_value DESC

And here is the error: Invalid operation function concat(character varying, "unknown", character varying, "unknown", character varying, "unknown", character varying, "unknown", character varying) does not exist

The reason for this error is that the CONCAT function in Redshift only accepts 2 arguments (CONCAT - Amazon Redshift).

…and guess what. This caused me to check our ATLAS source configuration page, and looks like for some reason the Redshift source’s dialect had been listed as postgresql. I’m guessing perhaps a copy/paste error by an engineer as we have 2 other Postgres sources.

I am going to get this changed to redshift and will report back

Chris_Knoll · February 10, 2021, 6:04am

yes, specifying the right dialect should fix it. i believe there’s some rule in SqlRender which will expand CONCAT(a,b,c) into CONCAT(a,CONCAT(b,c))

katy-sadowski · February 10, 2021, 1:52pm

It worked! Thanks very much for your help @Chris_Knoll - sorry that turned out to be a bit of a silly error. But now I am empowered to debug next time something comes up