|
|
|
# Installation
|
|
|
|
## R package
|
|
|
|
|
|
|
|
Achilles is a R package available [here](https://github.com/OHDSI/Achilles).
|
|
|
|
To install Achilles package, you need to use `devtools::install_github()` function.
|
|
|
|
Achilles is a R package available at https://github.com/OHDSI/Achilles.
|
|
|
|
The repository was cloned and the package was installed manually in Rstudio.
|
|
|
|
The modifications we made are available at https://github.com/scossin/Achilles/commits/cdw_bordeaux and discussed below.
|
|
|
|
|
|
|
|
```r
|
|
|
|
devtools::install_github("OHDSI/Achilles")
|
|
|
|
```
|
|
|
|
## SQL results tables
|
|
|
|
## Oracle issues
|
|
|
|
There are currently 2 open issues with Oracle database:
|
|
|
|
|
|
|
|
The output table of the Achilles statistic computation need to be created before run Achilles analysis:
|
|
|
|
- ACHILLES_RESULTS
|
|
|
|
- ACHILLES_HEEL
|
|
|
|
- ACHILLES_RESULTS_CONCEPT_COUNT
|
|
|
|
- ACHILLES_RESULTS_DIST
|
|
|
|
- https://github.com/OHDSI/Achilles/issues/598: Achilles create table doesn't work. [Our fix](https://github.com/scossin/Achilles/commit/f918ba5fb58dbea0e2e19cb6a279ce1d3dc41022) was to replace '' as by cast('' as varchar(255)) in the table creation queries.
|
|
|
|
- https://github.com/OHDSI/Achilles/issues/600: query 109 fails because SUBSTRING doesn't exist in Oracle. [Our fix](https://github.com/scossin/Achilles/commit/75008f4a6326a8f1a3db92dc260df8c037496573) was to replace substring by substr
|
|
|
|
|
|
|
|
The scripts are available bellow. They must be run in the OMOP result scheme.
|
|
|
|
## Package execution
|
|
|
|
The script we executed to run Achilles is available here: https://github.com/scossin/Achilles/blob/cdw_bordeaux/codeToRun.R
|
|
|
|
|
|
|
|
```sql
|
|
|
|
CREATE TABLE ACHILLES_RESULTS
|
|
|
|
(
|
|
|
|
ANALYSIS_ID NUMBER(38)
|
|
|
|
, STRATUM_1 VARCHAR2(255)
|
|
|
|
, STRATUM_2 VARCHAR2(255)
|
|
|
|
, STRATUM_3 VARCHAR2(255)
|
|
|
|
, STRATUM_4 VARCHAR2(255)
|
|
|
|
, STRATUM_5 VARCHAR2(255)
|
|
|
|
, COUNT_VALUE NUMBER(38)
|
|
|
|
);
|
|
|
|
```
|
|
|
|
The script ""./getConnectionDetails.R" (not in the repository). returns a connectionDetails object to connect to the database.
|
|
|
|
The password is stored and encrypted by the [keyring](https://cran.r-project.org/web/packages/keyring/index.html) package.
|
|
|
|
|
|
|
|
```sql
|
|
|
|
CREATE TABLE ACHILLES_HEEL
|
|
|
|
(
|
|
|
|
ANALYSIS_ID NUMBER(38)
|
|
|
|
, ACHILLES_HEEL_WARNING VARCHAR2(255)
|
|
|
|
, RULE_ID NUMBER(38)
|
|
|
|
, RECORD_COUNT NUMBER(38)
|
|
|
|
);
|
|
|
|
```
|
|
|
|
**All the default analysis run successfully**.
|
|
|
|
|
|
|
|
```sql
|
|
|
|
CREATE TABLE ACHILLES_RESULTS_CONCEPT_COUNT
|
|
|
|
(
|
|
|
|
CONCEPT_ID NUMBER(38)
|
|
|
|
, RECORD_COUNT NUMBER(38)
|
|
|
|
, DESCENDANT_RECORD_COUNT NUMBER(38)
|
|
|
|
);
|
|
|
|
```
|
|
|
|
We have a ORA-01652 error (unable to extend temp segment in tablespace) when running Achilles analysis id 424, 624, 724, 1824: the Oracle database run out of memory with 256 go avaivable in our TEMP tablespace.
|
|
|
|
These queries find and rank the top 10 co-occurring condition (424) / procedures (624) / drugs (724) / measurement (1824).
|
|
|
|
To do so, these queries perform an inner join on person_id within the same_table. This inner join takes a lot of memory since these tables contain million of rows. The limited memory resource in TEMP_IAM (256 go) can't store the results of this inner join.
|
|
|
|
This issue happened in other databases and was discussed here: https://github.com/OHDSI/Achilles/issues/425
|
|
|
|
Because these queries are computationally expensive, they don't belong to the default Achilles analysis.
|
|
|
|
|
|
|
|
```sql
|
|
|
|
CREATE TABLE ACHILLES_RESULTS_DIST
|
|
|
|
(
|
|
|
|
ANALYSIS_ID NUMBER(38)
|
|
|
|
, STRATUM_1 VARCHAR2(255)
|
|
|
|
, STRATUM_2 VARCHAR2(255)
|
|
|
|
, STRATUM_3 VARCHAR2(255)
|
|
|
|
, STRATUM_4 VARCHAR2(255)
|
|
|
|
, STRATUM_5 VARCHAR2(255)
|
|
|
|
, COUNT_VALUE NUMBER(38)
|
|
|
|
, MIN_VALUE FLOAT
|
|
|
|
, MAX_VALUE FLOAT
|
|
|
|
, AVG_VALUE FLOAT
|
|
|
|
, STDEV_VALUE FLOAT
|
|
|
|
, MEDIAN_VALUE FLOAT
|
|
|
|
, P10_VALUE FLOAT
|
|
|
|
, P25_VALUE FLOAT
|
|
|
|
, P75_VALUE FLOAT
|
|
|
|
, P90_VALUE FLOAT
|
|
|
|
);
|
|
|
|
```
|
|
|
|
|
|
|
|
# Statistic computation
|
|
|
|
## Connexion
|
|
|
|
|
|
|
|
A connexion to the OMOP database is created by the `DatabaseConnector::createConnectionDetails()` function.
|
|
|
|
An example of connexion is avalable bellow.
|
|
|
|
|
|
|
|
```r
|
|
|
|
connectionDetails <- DatabaseConnector::createConnectionDetails(
|
|
|
|
dbms = 'oracle',
|
|
|
|
user = '[USER]',
|
|
|
|
password = '[PASSWORD]',
|
|
|
|
connectionString = 'jdbc:oracle:thin:@[SID]:[PORT]:[DBNAME]'
|
|
|
|
)
|
|
|
|
```
|
|
|
|
|
|
|
|
## Analysis
|
|
|
|
|
|
|
|
Statistic computation are generated by the `Achilles::achilles()` function.
|
|
|
|
|
|
|
|
```r
|
|
|
|
Achilles::achilles(
|
|
|
|
connectionDetails = connectionDetails,
|
|
|
|
cdmDatabaseSchema = 'omop',
|
|
|
|
resultsDatabaseSchema = 'omop_temp',
|
|
|
|
vocabDatabaseSchema = 'omop',
|
|
|
|
numThreads = 1,
|
|
|
|
sourceName = 'Achilles',
|
|
|
|
cdmVersion = '5.3.0',
|
|
|
|
runHeel = FALSE,
|
|
|
|
optimizeAtlasCache = TRUE,
|
|
|
|
createTable = FALSE
|
|
|
|
)
|
|
|
|
```
|
|
|
|
|
|
|
|
## Query exclusion
|
|
|
|
|
|
|
|
Some Achilles queries are not working on Oracle database and were excluded (IDs: 105, 106, 107, 110, 117, 211, 212, 220 and 1824) |
