Using CDM tables for Achilles (ATLAS)

[hui_xing_tan (mintmountain89)]

Hi there!

I am thinking to set up Achilles (and eventually Atlas) on my local computer. and have been reading the documentation on github (GitHub - OHDSI/Achilles: Automated Characterization of Health Information at Large-scale Longitudinal Evidence Systems (ACHILLES) - descriptive statistics about a OMOP CDM database). From what I undertand, we should have a data tables in CDM format stored in a database server. Following which,

1. we can run a command in R to create cdmDatabaseSchema, resultsDatabaseSchema and vocabDatabaseSchema schemas from the data tables
2. the three schemas are exported to json in a ‘data’ directory in the root of the AchillesWeb folder
3. we create a file in the AchillesWeb ‘data’ directory named ‘datasources.json’
   and the data should load on Achilles web.

My plan to use the sample tables (SynPUF 1000 person) from Downloads - LTS Computing LLC as the data in CDM format.

However I was wondering:

1. Are the sample tables mentioned above sufficient for setting up and testing out Achilles/Atlas? Also do we have to name the tables in a certain fashion (all lower case, name has to match CDM table names exactly, etc) for Achilles to work?
2. Earlier when we configured WebAPI for Atlas (http://www.ohdsi.org/web/wiki/doku.php?id=documentation:software:webapi:webapi_installation_guide), we created a schema called WebAPI under a database called OHDSI in database server of our choice. Do we store the tables in the same database (OHDSI) or schema(webapi) that was created then?
3. Also, under which database should we store dmDatabaseSchema, resultsDatabaseSchema and vocabDatabaseSchema that was created by the R command?

I would appreciate any advice on this. Thanks very much!

Regards,
Hui Xing

[Ajit_Londhe]

Hi Hui Xing,

My responses are below:

Are the sample tables mentioned above sufficient for setting up and testing out Achilles/Atlas? Also do we have to name the tables in a certain fashion (all lower case, name has to match CDM table names exactly, etc) for Achilles to work?

The tables in the Synpuf sample are sufficient, yes. The table names are not case sensitive, however, they must be the same names as described in the CDM specification.

Earlier when we configured WebAPI for Atlas (http://www.ohdsi.org/web/wiki/doku.php?id=documentation:software:webapi:webapi_installation_guide), we created a schema called WebAPI under a database called OHDSI in database server of our choice. Do we store the tables in the same database (OHDSI) or schema(webapi) that was created then? Also, under which database should we store cdmDatabaseSchema, resultsDatabaseSchema and vocabDatabaseSchema that was created by the R command?

No, that is the WebAPI repository, it is used to manage all of the Atlas assets (cohort definitions, concept sets, generation statistics). I would recommend creating the CDM, results, and Vocab in a different database. All three can be part of the same schema, or you can separate them into 3 different schemas.

Happy Achilles-ing,
Ajit

[Chris_Knoll]

Actually 1 correction here:

All three can be part of the same schema, or you can separate them into 3 different schemas.

You should separate the results schema from the cdm+vocabulary schemas, because there are tables in the results schema that collide with the cdm+vocabulary schema. So, when you set this up, on your server hosting your CDM tables, put your CDM + vocabulary in 1 schema (call it cdm) and create the results schema in a different schema (call it results). You can get the DDL for the results schema directly from the WebAPI service by going to your webapi host /WebAPI/ddl/results

[Ajit_Londhe]

Ah good call. Listen to Chris

[hui_xing_tan (mintmountain89)]

Thanks Ajit and Chris! Will definitely take note when adding in the cdm tables to the database

[t_abdul_basser (Taha Abdul-Basser)]

@hui_xing_tan In addition to what @Chris_Knoll and @Ajit_Londhe mentioned I just want to point out that although the use case that consists of running Achilles to generate results, exporting results to JSON and using Achilles Web (standalone HTML5 application), to view summaries and profile data is still supported, it is no longer the preferred path. Features have already diverge between Achilles Web and Atlas and future functionality will continue to be added to latter rather than the former. I apologize for that not being reflected in the docs. We will change that soon.

All things being equal you should instead

1. Generate profiling results via Achilles and
2. View the results directly from the database via Atlas

[hui_xing_tan (mintmountain89)]

Okay, thank you

[hui_xing_tan (mintmountain89)]

@Ajit_Londhe @Chris_Knoll @t_abdul_basser

Hi, would like to check if the cdm and results schema should be located in the same database as the webapi schema i.e OHDSI. Looking at the R-code for running the Achilles analysis, the database isn’t specified so I’m a bit confused as to how Achilles knows which database to find the cdm and results schema, especially if the cdm and results schema are locates in a different database from the webapi schema:

library(Achilles)
connectionDetails <- createConnectionDetails(
  dbms="redshift", 
  server="server.com", 
  user="secret", 
  password='secret', 
  port="5439")

achilles(connectionDetails, 
  cdmDatabaseSchema = "cdm5_inst", 
  resultsDatabaseSchema="results",
  vocabDatabaseSchema = "vocab",
  numThreads = 1,
  sourceName = "My Source Name", 
  cdmVersion = "5.3.0",
  runHeel = TRUE,
  runCostAnalysis = TRUE)

Also,

1. should the cdm and results schema be created by the default user (i.e. postgres)?
2. what are the privileges to grant ohdsi_admin_user and ohdsi_app_user with regards to this schemas? Looking at the above, it seems that we have to change the user and password to that of ohdsi_app_user, or ohdsi_admin_user which means that we first need to allow them to access the tables…

I’ve already imported the SynPUF CDM tables into a schema called cdm, under a database called CDM – all of these were done by the default user ‘Postgres’. I haven’t granted any privileges on the cdm table/schema/database to the ohdsi_admin_user or ohdsi_app_user at the moment. If I have used the wrong location or login then should I delete these tables, login as ohdsi_admin user and create them in the correct location and with the correct rights?

Thanks in advance and sorry for the spam ~
Hui Xing

[Chris_Knoll]

The cdm schema and the results schema must be co-located on the same database. It is common to select from CDM and insert into results in the same query.

The webAPI schema is a completely different. Achilles as nothing to do with the WebAPI schema.

You should keep your webAPI schema separate from your cdm/results schemas.

1. should the cdm and results schema be created by the default user (i.e. postgres)?

It doesn’t matter who created those schemas. Just as long as the login that is accessing the CDM (specified in your connectionSettings in your Achilles call) as proper rights to select/insert.

2. what are the privileges to grant ohdsi_admin_user and ohdsi_app_user with regards to this schemas? Looking at the above, it seems that we have to change the user and password to that of ohdsi_app_user, or ohdsi_admin_user which means that we first need to allow them to access the tables…

ohsdi_admin_user and ohdsi_app_user were accounts that the WebAPI guide suggests to create to allow the WebAPI service (Java application) to manage the webAPI schema (created by the WebAPI service).

I think there’s a bit of confusion between the WebAPI database and the CDM/Results database. Keep those things completely separate in your mind. For purposes of running Achilles: the createConnectionDetails() should use your database that contains the CDM and Results schema.
From the info you gave, the Database is CDM, the cdmSchema is ‘cdm’, the results schema is ‘results’. that should let you run the Achilles process.

[hui_xing_tan (mintmountain89)]

@Chris_Knoll I see, thanks for the clear explanation!

my overall schema structure is as below

CDM database
– cdm schema which contains the cdm and vocab tables
– results schema, which doesn’t have any tables

OHDSI database
– webapi schema

At the moment am having problems loading Achilles in R (Error in installing R-Packages - #6 by hui_xing_tan), but I’ve written down the connection details to run on RStudio once I’ve figured out the other issue (fingers crossed).

Would greatly appreciate any advice on whether I’ve filled in correctly…Thank you!

connectionDetails ← createConnectionDetails(
dbms=“postgresql”,
server=“localhost/CDM”,
user=“postgres”,
password=’****’,
schema=‘cdm’,
port=“5433”)

achilles(connectionDetails,
cdmDatabaseSchema = “cdm”,
resultsDatabaseSchema=“results”,
vocabDatabaseSchema = “cdm”,
numThreads = 1,
sourceName = “Can_this_be_anything?”,
cdmVersion = “5”,
runHeel = TRUE,
runCostAnalysis = TRUE)

[t_abdul_basser (Taha Abdul-Basser)]

1 Yes, this looks fine to me based on the info that I have about your setup.

2 The sourceName can be anything.

[Chris_Knoll]

I agree with @t_abdul_basser that it looks proper. The source name parameter is just a value that gets inserted into the results so that you can label the achilles results with some name. It can be anything, but make it short and without any special characters.

[hui_xing_tan (mintmountain89)]

I see, very much appreciated the advice. Thank you!