Repository navigation
person_count not present in ohdsi_results.achilles_result_concept_count in 2.13.0 #2239
Description
Perhaps either person_count and descendant_person_count need to be added to Achilles or removed from WebAPI. WebAPI throws an error because these fields are not generated by Achilles. Is an Achilles update is imminent?
Expected behavior
No error in log
Actual behavior
broadsea-webtools2 | 2023-03-30 09:34:40.274 INFO taskExecutor-3 org.springframework.batch.core.job.SimpleStepHandler - [] - Executing step: [warming cache: OHDSI-CDMV5-OMOP_DEID-B1 counts]
broadsea-webtools2 | 2023-03-30 09:34:47.006 ERROR taskExecutor-3 org.ohdsi.webapi.cdmresults.service.CDMCacheService - [] - Failed to warm cache OHDSI-CDMV5-OMOP_DEID-B1. Exception: PreparedStatementCallback; bad SQL grammar [select cc.concept_id, record_count, descendant_record_count, person_count, descendant_person_count
broadsea-webtools2 | from ohdsi_results.achilles_result_concept_count cc
broadsea-webtools2 | join omop.concept c on cc.concept_id = c.concept_id
broadsea-webtools2 | WHERE cc.concept_id >= ? and cc.concept_id <= ?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'person_count'.
Steps to reproduce behavior
Update Atlas/WebAPI to 2.13.0
Run Achilles 1.7 with the optimizeAtlasCache = TRUE option to generate the achilles_result_concept_count table:
achilles(connectionDetails,
cdmDatabaseSchema = "OMOP_DEID.omop",
resultsDatabaseSchema = "OMOP_DEID.ohdsi_results",
scratchDatabaseSchema = "OMOP_DEID.ohdsi_temp",
numThreads = 1,
outputFolder = "./achilles_output2",
cdmVersion = "5.3.1",
optimizeAtlasCache = TRUE,
sqlOnly = TRUE
)
generates the following at the tail end of the achilles.sql:
IF OBJECT_ID('OMOP_DEID.ohdsi_results.achilles_result_concept_count', 'U') IS NOT NULL
drop table OMOP_DEID.ohdsi_results.achilles_result_concept_count;
WITH concepts AS (
SELECT
CAST(ancestor_concept_id AS VARCHAR) ancestor_id,
CAST(descendant_concept_id AS VARCHAR) descendant_id
FROM OMOP_DEID.omop.concept_ancestor ca
UNION
SELECT
CAST(concept_id AS VARCHAR) ancestor_id,
CAST(concept_id AS VARCHAR) descendant_id
FROM OMOP_DEID.omop.concept c
), counts AS (
SELECT stratum_1 concept_id, MAX (count_value) agg_count_value
FROM OMOP_DEID.ohdsi_results.achilles_results
WHERE analysis_id IN (2, 4, 5, 201, 225, 301, 325, 401, 425, 501, 505, 525, 601, 625, 701, 725, 801, 825,
826, 827, 901, 1001, 1201, 1425, 1801, 1825, 1826, 1827, 2101, 2125, 2301)
GROUP BY stratum_1
UNION
SELECT stratum_2 AS concept_id, SUM (count_value) AS agg_count_value
FROM OMOP_DEID.ohdsi_results.achilles_results
WHERE analysis_id IN (405, 605, 705, 805, 807, 1805, 1807, 2105)
GROUP BY stratum_2
)
select concept_id, record_count, descendant_record_count
into OMOP_DEID.ohdsi_results.achilles_result_concept_count
from
(
SELECT
concepts.ancestor_id concept_id,
isnull(max(c1.agg_count_value), 0) record_count,
isnull(sum(c2.agg_count_value), 0) descendant_record_count
FROM concepts
LEFT JOIN counts c1 ON concepts.ancestor_id = c1.concept_id
LEFT JOIN counts c2 ON concepts.descendant_id = c2.concept_id
GROUP BY concepts.ancestor_id
) Q
;
Activity
chrisknoll commentedon Mar 30, 2023
The
achilles_result_concept_counttable that is required for 2.13 is not the same as the table produced from Achilles whenoptimizeAtlasCache=TRUE. This was a missed detail when the record count caching was implemented in Atlas in 2.13...and this is something that should probably be removed from Achilles (or achilles should provide the person_count fields).The actual script you should run to initialize this table is here.
billsanto commentedon Mar 31, 2023
Thanks for pointing to the script--I did get it to run after updating the variables.