Adding a new MS SQL CDM database to Atlas using Broadsea

Implementers
16

Thanks @Chris_Knoll . Updated the connecion string as you suggested and added the relevent schemas as shown below:

jdbc:sqlserver://server-name.database.windows.net:1433;database=db-name;user=azuresql_admin@server-name;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

image
image1919×666 46.2 KB

checked the conneciton to the database and looks like a successful connection is made

image
image1754×139 14.3 KB

Able to select the data source and look at some of the summery level details. However, when selecting Condition Occurence get the follwoing error

image
image1928×398 20.8 KB

Insepecting the logs gives the follwoing error :

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT
  concept_hierarchy.concept_id,
  CONCAT(
    isNull(concept_hierarchy.level4_concept_name, 'NA'), '||',
    isNull(concept_hierarchy.level3_concept_name, 'NA'), '||',
    isNull(concept_hierarchy.level2_concept_name, 'NA'), '||',
    isNull(concept_hierarchy.level2_concept_name, 'NA'), '||',
    isNull(concept_hierarchy.concept_name, 'NA')
  ) AS concept_path,
  ar1.count_value                                     AS num_persons,
  round(1.0 * ar1.count_value / denom.count_value, 5) AS percent_persons,
  round(1.0 * ar2.count_value / ar1.count_value, 5)   AS records_per_person
FROM (SELECT *
      FROM results.achilles_results WHERE analysis_id = 400) ar1
  INNER JOIN
  (SELECT *
   FROM results.achilles_results WHERE analysis_id = 401) ar2
    ON ar1.stratum_1 = ar2.stratum_1
  INNER JOIN
  results.concept_hierarchy concept_hierarchy
    ON CAST(CASE WHEN ar1.analysis_id = 400 THEN ar1.stratum_1 ELSE null END AS INT) = concept_hierarchy.concept_id
    AND concept_hierarchy.treemap='Condition'
,
  (SELECT count_value
   FROM results.achilles_results WHERE analysis_id = 1) denom
ORDER BY ar1.count_value DESC]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'results.concept_hierarchy'.

I had a look for the following error message on the forum

nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'results.concept_hierarchy'.

Found the following forum post General OMOP + WebAPI + Atlas setup - #9 by Chris_Knoll

Will the two scripts as mentioned on the post need to be run on the WebAPI database?

Many thanks,
Solmaz