Achilles in R stuck at "Connecting using SQL Server driver"

hui_xing_tan · March 21, 2019, 9:20am

Hi,

Has anyone encountered the issue whereby the Achilles call in RStudio stops at “Connecting using SQL Server driver”? Recently ran the commands below , and the process has been stuck at the connection part. It seems to have connected and inserted one table, because I can see the ACHILLES_analysis tables in the results schema, but the rest of the tables are not there yet.

> library(Achilles)
Loading required package: SqlRender
Loading required package: DatabaseConnector
Loading required package: rjson
Loading required package: OhdsiRTools
Loading required package: openxlsx
Loading required package: httr
> 
> connectionDetails <- createConnectionDetails(
+   dbms="sql server",
+   server="localhost",
+   user= "****",
+   password="****",
+   port="1433"
+ )
> 
> achilles(connectionDetails,
+          cdmDatabaseSchema = "cdm.dbo",
+          resultsDatabaseSchema = "cdm.results",
+          vocabDatabaseSchema = "cdm.dbo",
+          numThreads="10",
+          sourceName = "My Source Name",
+          cdmVersion = "5",
+          runHeel = TRUE,
+          runCostAnalysis = FALSE)

Results:

Connecting using SQL Server driver
Connecting using SQL Server driver
|================================================================================================| 100%
Executing SQL took 0.0963 secs
Dropping scratch Achilles tables from schema cdm.results
Connecting using SQL Server driver (stuck here)

Appreciate any advice on how to troubleshoot, thank you

hui_xing_tan · March 21, 2019, 9:22am

To add on, is there a way to run the Achilles analysis in SQL server management studio or other Database Manager instead of in RStudio? Thanks!

Vojtech_Huser · March 21, 2019, 5:09pm

To trouble shoot - try running just one or few analysis_id.

see here

github.com

OHDSI/Achilles/blob/master/extras/notes.md#execute-only-few-new-analyses


# How to run Achilles Heel only: 

Execution of all analyses computations is not necessary if all you want to do is to run new data quality measures in a revised version of Heel. Instead of 10+ hours, you can be done in few minutes with running just heel
```
#initialize your connectionDetails as usual
#set your schema names for where is data and where to store results
  myCdm='cdm5_inst';resDb='results'

#run heel only like this
  heel<-achillesHeel(connectionDetails,cdmDatabaseSchema = myCdm, resultsDatabaseSchema = resDb,cdmVersion = "5")

#optionally - get heel errors and warnings as a small CSV file
  heelRes<-fetchAchillesHeelResults(connectionDetails,resDb)
  write.csv(heelRes,paste0(myCdm,'-01-heel-res.csv'),row.names = F,na = '')

```

# How to save time on running full Achilles - make it finish much earlier
If you are willing to skip cost analyses (not used very often), this smaller set of analyses will finish much earlier

This file has been truncated. show original

hui_xing_tan · March 25, 2019, 2:21am

Thank you @Vojtech_Huser . I ran the codes for Achilles heel only, but encountered the following error because the achilles_results table had not yet been created

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'cdm.results.achilles_results'.

Subsequently what I did was to
-> change the number of threads to 1 and added in conceptHierarchy = FALSE to the achilles statement
-> separately create concept hierarchy in using the ddl from WebAPI

And the analysis can complete now