OHDSI Home | Forums | Wiki | Github

Atlas CDM Setup: Can CDM and Vocabulary be in Separate Databases on the Same RDBMS Instance?

Implementers
, ,
#1

Hi there,
I have an instance of Atlas set up with a SynPuf database (PostgreSQL) instance in which the CDM data and the Vocabulary are in separate schemas in the same database, i.e. dbUrl:database:cdmSchema and dbUrl:database:vocabSchema, with the schemas set in the source_daimon table. This works well.
Now I have been tasked to add my institution’s own database to Atlas (MS SQL). Its setup is slightly different, with all data being on the same RDBMS instance, but in separate databases, i.e. dbUrl:cdmDatabase:cdmSchema and dbUrl:vocabDatabase:vocabSchema.
Is this possible to set up for the WebAPI (source_daimons) as is or will I have to ask the database administrator to move the vocabulary tables to a new schema in the cdmDatabase?
Thanks,
Kai

#2

If you can test doing a join between the two databases

databaseName.cdmSchema.condition_occurrence co join otherDatabaseName.vocabSchema.concept c on c.concept_id = co.condition_concept_id

Then you should be fine. MSSQL lets you reference a database as if it was a schema on the same host. Postgresql may allow that as well.

1 Like
#3

Postgres does but you have to set DBLink up to do so. *Nix mentality (which I agree with).

Edit:
I forgot that now there is foreign data wrapper that one can use instead of DBLink.
https://www.akinjide.me/2017/cross-database-querying-in-postgresql/

It was new when I moved jobs and now use SQL Server. I was working with the foreign data wrapper to query flat files right before the move. Powerful tool.

1 Like
#4

Thanks. That might work then. I will give it a try!

t