Atlas not finding the vocabulary to initiate

[RaphaelNogueira]

Hello everyone. I would like to get some assistance related to setting up ATLAS as I am not sure what would be the issue occurring now, i.e. I am stuck!
Below you can see that ATLAS is running and calling most of its functionalities fine, but it does not recognize the vocabulary, therefore it won’t run Achilles to populate the results schema and allow ATLAS to show the data.

When I try to run Achilles to populate the results schema I am getting the error below, I am quite sure that the connection is correct as I could create the ddls using Achilles with the same connection, although I doubt what is the correct user to set for the connection, the one that has access to the whole DB, or the ohdsi_admin(that also goes to the URL JDBC, I mean which one is the correct one please?).

This is how I populated the source table, the source_ID is populated using the SEQUENCE that if my understanding is right works with other tables in the webapi schema allowing to create users and define their privileges to access ATLAS.

The CDM_SOURCE is populated with the CDM OMOP v6.0 with a constrain with vocabulary schema

ALTER TABLE webapi.CDM_SOURCE ADD CONSTRAINT fpk_CDM_SOURCE_cdm_version_concept_id FOREIGN KEY (cdm_version_concept_id) REFERENCES vocab.CONCEPT (CONCEPT_ID);

The SOURCE_DAIMON below:

Considering that the model is not finding the vocabulary, I have gathered as much information as possible to help anyone understand what the issue could be.
The vocabulary table has the CDM v6.0 and its concept in the concept table too, in fact, all the constraints are in place, as the indexes and all the concepts were mapped. The ETL was done with Python and I have a decent function that will collect all the concepts and create the vocabulary tables, including source_to_concept_map.

The tomcat log seems that everything works fine, except when it comes to finding the vocabulary and subsequently the OMOP tables(person, observation_period,…)

Error from the browser’s console

After reading a few similar questions in the forum I believe that it could be that I need to deploy a new war file where the datasource.schema is set to “cdm”, as I just did to the webapi, could it be the solution?

[jcabrerazuniga (Jose Cabrera Zuniga)]

The account ohdsi_admin is by default created to manage the database used by Atlas to configure atlas itself. This is not to be used with Achilles, by default.

Where is the database where you place your OMOP tables? this one can be in a different server. At the server where this database is placed you need to have another account that will have enough privileges to allow Achilles to run. It is inside this database where you need to place the Vocabulary tables (maybe within a separated schema) or at a place that will allow Achilles to run.
For example, in the next picture:

omop_20220331 is a schema with the vocabulary tables and mdcr2003_2021 is another schema with an omop database. The schema ar_mdcr2003_20021 was used to have the tables that represent the achilles and atlas result tables:

Hope this will help you to clarify your ideas.

[RaphaelNogueira]

Thanks for your reply and help.
It is at cdm schema, but I deployed the war file on webapi schema, but I was not able to get any data into Atlas either. So I created a schema for vocabulary and one for the OMOP tables(cdm).
I followed the tutorial by @anthonysena

CREATE ROLE ohdsi_admin
  CREATEDB REPLICATION
   VALID UNTIL 'infinity';
COMMENT ON ROLE ohdsi_admin
  IS 'Administration group for OHDSI applications';
  

CREATE ROLE ohdsi_app
   VALID UNTIL 'infinity';
COMMENT ON ROLE ohdsi_app
  IS 'Application groupfor OHDSI applications';
 


CREATE ROLE ohdsi_admin_user LOGIN ENCRYPTED PASSWORD ''
   VALID UNTIL 'infinity';
GRANT ohdsi_admin TO ohdsi_admin_user;
COMMENT ON ROLE ohdsi_admin_user
  IS 'Admin user account for OHDSI applications';
 
 
CREATE ROLE ohdsi_app_user LOGIN ENCRYPTED PASSWORD ''
   VALID UNTIL 'infinity';
GRANT ohdsi_app TO ohdsi_app_user;
COMMENT ON ROLE ohdsi_app_user
  IS 'Application user account for OHDSI applications';

CREATE DATABASE "OHDSI"
  WITH ENCODING='UTF8'
       OWNER=ohdsi_admin
       CONNECTION LIMIT=-1;
COMMENT ON DATABASE "OHDSI"
  IS 'OHDSI database';
GRANT ALL ON DATABASE "OHDSI" TO GROUP ohdsi_admin;
GRANT CONNECT, TEMPORARY ON DATABASE "OHDSI" TO GROUP ohdsi_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES
TO ohdsi_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
GRANT SELECT, USAGE ON SEQUENCES
TO ohdsi_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
GRANT EXECUTE ON FUNCTIONS
TO ohdsi_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
GRANT USAGE ON TYPES
TO ohdsi_app;


--CDM_schema, Vocabulary_schema, CDM_schema, Results_schema, temp_schema

--GRANT CONNECT ON SCHEMA CDM_schema TO webapi_sa;

CREATE SCHEMA cdm
	AUTHORIZATION ohdsi_admin;
COMMENT ON SCHEMA cdm
  IS 'Schema containing tables to support CDM_OMOP';
GRANT USAGE ON SCHEMA cdm TO ohdsi_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE ohdsi_admin_user IN SCHEMA cdm
GRANT SELECT ON TABLES TO ohdsi_admin;



CREATE SCHEMA vocab
   AUTHORIZATION ohdsi_admin;
COMMENT ON SCHEMA vocab
  IS 'Schema containing tables to support CDM_OMOP';
 GRANT USAGE ON SCHEMA vocab TO ohdsi_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE ohdsi_admin_user IN SCHEMA vocab
GRANT SELECT ON TABLES TO ohdsi_admin;


CREATE SCHEMA temp
   AUTHORIZATION ohdsi_admin;
COMMENT ON SCHEMA temp
  IS 'Schema containing tables to support CDM_OMOP';
  REVOKE ALL ON SCHEMA temp FROM ohdsi_admin;
GRANT ALL ON SCHEMA temp TO ohdsi_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE ohdsi_admin_user IN SCHEMA temp
GRANT ALL ON TABLES TO ohdsi_admin;



CREATE SCHEMA results
   AUTHORIZATION ohdsi_admin;
COMMENT ON SCHEMA results
  IS 'Schema containing tables to support CDM_OMOP';
  REVOKE ALL ON SCHEMA results FROM ohdsi_admin;
GRANT ALL ON SCHEMA results TO ohdsi_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE ohdsi_admin_user IN SCHEMA results
GRANT INSERT, SELECT, DELETE, UPDATE ON TABLES TO ohdsi_admin;

Now that you mentioned an user to access those schemas, a webapi_sa was created and I got a bit confused, could be it? So I need to create a new role only for ATLAS? If so could I get some guidance please.

[RaphaelNogueira]

@Chris_Knoll, @MaximMoina, @Prof_Henrique_Neves any help?

[Chris_Knoll]

Only that you should not be making any custom additions to the WebAPI schema, as this will possibly conflict with future DDL migrations in new versions of WebAPI. Referring tho this:

@RaphaelNogueira

ALTER TABLE webapi.CDM_SOURCE ADD CONSTRAINT fpk_CDM_SOURCE_cdm_version_concept_id FOREIGN KEY (cdm_version_concept_id) REFERENCES vocab.CONCEPT (CONCEPT_ID);

Update: in reading this more closely, it looks like you are adding a FK to a CDM table CDM_SOURCE and the CDM Table CONCEPT. The schema you referenced was ‘webapi’ which is confusing because webapi schema is not a CDM.

Next, it’s clear that the connection to the CDM DB (with the results schema) is failing: password authentication failed for user ‘ohdsi_admin’. While other things may appear to be working in Atlas, Atlas (WebAPI specifically) is using the credentials defined in your settings.xml to connect to the web API database, and will only use the SOURCE tables when trying to connect out to your CDM source. They may be one-in-the-same, but the connection details are coming from differnt places.

@RaphaelNogueira

After reading a few similar questions in the forum I believe that it could be that I need to deploy a new war file where the datasource.schema is set to “cdm”, as I just did to the webapi, could it be the solution?

No, as referenced above, settings.xml is where WebAPI finds the connection information to the WebAPI database. The datasource ohdsi schema is the schema where WebAPI tables live, and naming it ‘cdm’ would be extremely confusing.

As stated in several other threads: while it’s possible to put your WebAPI and CDM into the same database (this may be the way broadsea sets it up as a simple starting point), I’ve argued that this is not the optimal configruation because you should have a clean separation between databases and their purpose: there is one WebAPI database, there are one or more CDM databases. The WebAPI uses credentials for genaral operations and another for DB migration work (flyway.datasource.* settings). The CDM database contains a CDM scheama (that you ETL’d your source data from), a results schema (that Achilles writes into, and WebAPI provides DDL to create additional tables), and the credentials to connect to this database is in the SOURCE table.

I’d suggest you make a logical diagram of your datasource, with a box for your WebAPI server (tomcat?), a box for your postgres database that hosts WebAPI tables, a box for each of your CDM sources, and draw lines between the boxes indicating where the credentaials are comming from (the WebAPI service box to WebAPI database comes from settings.xml for exaple). But make this diagram to reflect your own environment. Then you will have a clear picture of where servers are living and how they are connecting to each other, which may help you isolate where the problem lives.

In addition, you should check your WebAPI logs for errors, it’s likely that if your R session is getting a auth failure, and it is the same credentails in your Source table, then you should probably see the same auth failure in the WebAPI logs.

[RaphaelNogueira]

Hi Chris and thanks. I honestly find it a bit confusing, mainly as the OMOP guides suggest you create separate schemas for the OMOP tables, vocabulary tables, and the results/temp. I initially did it all in one and it did not work, then I decided to set them as it is at the moment. I will redo it to set all for the Webapi schema and see how it goes. I keep tagging @anthonysena as he was the one giving the tutorial, but no answer so far, as he surely would clarify what I am missing, or not fully understanding.

[Chris_Knoll]

@RaphaelNogueira

Hi Chris and thanks. I honestly find it a bit confusing, mainly as the OMOP guides suggest you create separate schemas for the OMOP tables, vocabulary tables, and the results/temp.

In this context, they are talking about the CDM database, and only the CDM database.

@RaphaelNogueira

I initially did it all in one and it did not work, then I decided to set them as it is at the moment.

Doing it all together introduces confusion about what database WebAPI uses, and what database is used for CDM purposes. Just keep those things separate in your mind (and in your physical topology of your infrastructure) and things are a lot easier to manage. WebAPI will completely manage table creation in your WebAPI database when you start the WebAPI service (ie: the WAR in tomcat). Everything else like ETL, creating your cdm, results annd temp schema, running Achilles, creating the results schema tables, is all done manually on a CDM-by-CDM basis.

[jcabrerazuniga (Jose Cabrera Zuniga)]

Simplify your life…Have the OHDSI database containing just the information to configure atlas. Use the atlas interface to connect to other databases (possibly at other servers) where your OMOP tables reside. Avoid using Achilles against the OHDSI database.

[RaphaelNogueira]

Hey Jose, I was reading last night, and I probably missed one role, and that is what is causing the issue, I have double-checked all the tables that allow Atlas to run as the settings while setting it up.
I believe that I missed the role that will access Atlas, in the tutorial it was name webapi_sa, I did not create that one. I didn’t think that one was relevant, but it seems to be, does not matter which schema you choose to set the cdm, vocabulary if you give the right privileges to the webapi schema and they are all in one DB. Now it is work toward it and see how it goes, but you are right that would be an easier way to set it up before bring the actual data.

[RaphaelNogueira]

@anthonysena could you explain to me the webapi_sa role and why did you create a new db named cdm in your tutorial? I still did not get exactly why and I believe the issue lies there as I have set all the tables for webapi and the problem persists, if you run through the cdm_source, source, and source_daimon look good to go.

[RaphaelNogueira]

cdm configuration
here I found myself a bit confused as there is a db for webapi under OHDSI, then a second is created for CDM that contains the results/temp tables, so far it does make sense as the webapi will do all the work in the back-end and a third role will read and create the results tables using the temp to do so.
Now if someone could explain to me if I do need a new db, or if I can just create a new schema, please, sorry about the inconvenience guys, I am getting a bit frustrated with it at this point.

[RaphaelNogueira]

@UltraXiaoZi I saw your page instruction, and it seemed very neat and objective, any chance you have a quick look at my current challenge?

[RaphaelNogueira]

I managed to create the achilles_results table as the achilles_results_dist, but still, ATLAS can not find the vocabulary version. Does it have anything to do with the fact I am working with version 6.0?

I already cleaned the cache, refreshed the source and the result is the same.

ANY HELP PLEASE?

[Chris_Knoll]

Vocabulary version is done by querying the CDM’s vocabulary table, looking for a specific record in it. You can see the query that is used here. Run that query on your CDM to see if it finds a record.

While Atlas/WebAPI does not work with the CDM changes that were made in version 6, none of the startup operations that you are having issue with should be impacted by your source being on CDM 6.

[Chris_Knoll]

@RaphaelNogueira

cdm configuration
here I found myself a bit confused as there is a db for webapi under OHDSI, then a second is created for CDM that contains the results/temp tables

So one element that may be confusing in the documentation is that they are talking about setting up the CDM database (with the cdm, results and temp schema) but then they switch about talking about the WebAPI database to insert records into the WebAPI’s SOURCE table. So, I can understand there may be some confusion there, but it seems you understand that CDM setup is done in one step, and then the WebAPI config (via SOURCE table) is done after. How else could WebAPI know that the CDM exists?

@RaphaelNogueira

Now if someone could explain to me if I do need a new db, or if I can just create a new schema, please, sorry about the inconvenience guys, I am getting a bit frustrated with it at this point.

For what? I’m not sure how to answer your question, you say you have the WebAPI doing some work, your CDM hosting the patient-level data and analysis results…what other DB would you need to create?

[RaphaelNogueira]

I don’t see the logic in searching for a ‘None’ value if the id can’t be null. I get an empty outcome, if I put = ‘CDM’ then it returns the cmd v6.0.0
What do I do here? Change the source and clean the localStorage.clear() in the console and refresh webapi to see it works? I did and nothing changed, so I went back to the default.

[RaphaelNogueira]

I finally understood what @anthonysena was doing there, showing that you can add different dbs with difference sources to it. This part now is clear to me, what is bugging my mind is how this is not finding the vocabulary version. I also have it set in Linux where I simply added ATLAS as an webapp as you can see in the printscreen I attached below, if it makes any difference(the Linux part).

After populating achilles_results and achilles_results_dist the only thing left to get it running would be identify the vocabulary I would say so.

I understood the whole flow and how you need to set it up, how the constrains work, how the privileges work for the schemas, I am just stuck with this little inconvenient.

I doubled check the cdm_source where the vocabulary has its info there to ensure that it won’t have mismatch information .

If you can let me know if there is anything that you would recommend me to change please let me know.

source

cdm_source

source_daimon

[RaphaelNogueira]

I also saw that the pom.xml file has a set version for the cdm, if it matters. I have put the 6.0.0.

I also tried to search for a dictionary and got this message

I am using the ohdsi_admin in the table source, for source_connection(jdbc URL), should I use the db user instead?

I am sorry that I am bombarding ye with so many questions, I just would like to move on with it, but this wepapp not finding the vocabulary is getting me stuck instead.

[RaphaelNogueira]

Guys thank ye all for the time and help. I figured it out, I needed to access the root in the VM and check tomcat logs, there was errors in privileges.

[Chris_Knoll]

@RaphaelNogueira

This part now is clear to me, what is bugging my mind is how this is not finding the vocabulary version.

I told you the query that it uses to determine that, and you ran the query and got this:

@RaphaelNogueira

That’s your answer: the contents of the vocabulary you have installed does not have a row that shows the actual vocabulary version, and therefore should very obviously answer the question why you are not getting the value in the UI…it does not exist.

In addition, your query shows select ... from webapi.vocabulary. it is extremely confusing to make your cdm schema called ‘webapi’.

@RaphaelNogueira

I don’t see the logic in searching for a ‘None’ value if the id can’t be null. I get an empty outcome, if I put = ‘CDM’ then it returns the cmd v6.0.0

‘None’ is not null. Looking for the vocabulary_ID = ‘CDM’ isn’t what I explained is done to find the vocabulary version, so, I’m not sure why you think changing that will get you the correct result?

@RaphaelNogueira

If you can let me know if there is anything that you would recommend me to change please let me know.

source

cdm_source

source_daimon

More confusion: you are showing the ‘source’ table (which I am assuming is the WebAPI’s database source table) and it doesn’t look like the WebAPI source table (there should be source_key in it. Then you show me a ‘CDM_SOURCE’ table, where that one DOES look like the WebAPI source table, so…not sure what is happening there unless you just switched them (and for future reference, if you could fully qualify your table names so it’s more clear, we could have overlap between cdm table names and webapi table names.

Finally, what I would suggest to you is: start over. Completely. And, build your cdm under version 5.4. I think I mentioned earlier that none of the analytics will work with CDM 6.0. When you start again, create the correct databases with the correct schema names: don’t confuse the webAPI tables with the CDM ones. Put them on separate databases to keep it clear and separated. Once you do that, see if you run into the same problems, and I’ll try to help again. I think it’s pretty clear things are so confused and twisted now, I don’t know what the ‘correct’ state of your environment can be. As you do this over, don’t add your own steps to the process. If it doesn’t work based on the instructions, assume you’re doing something wrong.