Have Some Problmes About Atlas

ZHU_C · March 6, 2018, 9:41am

Hello all
I am a students at NANJING MEDICAL UNIVERSITY in China and doing some researches about OHDSI.
However when I have configured WebAPI and load the URL http://localhost:8000/WebAPI（I have already changed the tomcat server port to 8000） to check,it shows that

I wonder whether these “false” indicate that I configure something wrong and how to make it correct.

In addition,I downloaded Atlas-2.3.0 and configured the config-local.js like this

define([], function () {
var configLocal = {};

// clearing local storage otherwise source cache will obscure the override settings
localStorage.clear();

// WebAPI
configLocal.api = {
	name: 'OHDSI',
	url: 'http://localhost:8000/WebAPI/'
};



return configLocal;

});
then login Atlas,but the window is very strange in blank.

How can I sovle this problem.
Any of your help will be appreciated！
Sincerely
-ZHU

Chris_Knoll · March 7, 2018, 12:58am

From your screenshot, it appears you are tryign to load atlas from a local file (ie: file://C;/users…).

You need to store the Atlas files so that they are delivered through an HTTP server.

The URL you tested: http://localhost:8000/WebAPI isn’t a valid endpoint. You can try http://localhost:8000/WebAPI/info to see the version of WebAPI, and check your configured sources by accessing http://localhost:8000/WebAPI/source/sources

ZHU_C · March 13, 2018, 6:29am

@Chris_Knoll Thanks for your reply.Sorry for disturbing you again.
I have checked as your suggestion,the http://localhost:8000/WebAPI/info shows “{“version”:“2.3.0”}”,and the http://localhost:8000/WebAPI/source/sources shows codes below

{“sourceId”:1,“sourceName”:“My Cdm”,“sourceDialect”:“oracle”,“sourceKey”:“MY_CDM”,“daimons”: [{“sourceDaimonId”:1,“daimonType”:“CDM”,“tableQualifier”:“cdm”,“priority”:“0”},
{“sourceDaimonId”:2,“daimonType”:“Vocabulary”,“tableQualifier”:“cdm”,“priority”:“0”},{“sourceDaimonId”:3,“daimonType”:“Results”,“tableQualifier”:“ohdsi”,“priority”:“0”}
]
},
{“sourceId”:2,“sourceName”:“Default vocabulary”,“sourceDialect”:“oracle”,“sourceKey”:“vocab”,“daimons”: [
{“sourceDaimonId”:4,“daimonType”:“Vocabulary”,“tableQualifier”:“cdm”,“priority”:“1”}
]
}]

Do I configure it correctly？

I’m new here and weak in codes,DB and web.Thanks for any of your help.

Chris_Knoll · March 7, 2018, 4:06pm

WebAPI does look like it is installed correctly since the /info and /source/sources returns information.

However, I’m unable to determine if the settings are correct for your environment. It appears that you have set up 2 sources:

{"sourceId":1,"sourceName":"My Cdm","sourceDialect":"oracle","sourceKey":"MY_CDM","daimons": ....
{"sourceId":2,"sourceName":"Default vocabulary","sourceDialect":"oracle","sourceKey":"vocab","daimons": ...

These are set to be Oracle, but we hide the connection details from the /source/sources URL so I don’t know if you have the right connection info.

For source 1, you have the following table qualifiers set:

"daimons": [{"sourceDaimonId":1,"daimonType":"CDM","tableQualifier":"cdm","priority":"0"},
{"sourceDaimonId":2,"daimonType":"Vocabulary","tableQualifier":"cdm","priority":"0"},{"sourceDaimonId":3,"daimonType":"Results","tableQualifier":"ohdsi","priority":"0"}]

Do you know if your Oracle database has the schemas ‘cdm’ and ‘ohdsi’ set up, where the ‘cdm’ schema has your CDM tables (person, visit, drug_exposure, and the vocabulary tables) vs. the ohdsi schema has the set of tables to store the results?

Oracle is especially challenging to setup if you do not have DB experience, and I am sorry that there isn’t an easy way to have it install automatically, but the CDM configuration/setup is something that is outside of the WebAPI installation/configuration. I think you’ll need some DBA help to ensure that it is configured correctly.

-Chris

ZHU_C · March 8, 2018, 1:25am

@Chris_Knoll Sorry for my late reply because of the time difference.
I have already studied DB now.

I follow the steps what the OHDSI forum shown to configure these settings.I set up the ‘ohdsi’ schema only and put all tables in it including CDM tables.After reading your reply,I think I’m wrong.You mean I should create a ‘cdm’ schema single and only store CDM tables while ‘ohdsi’ schema only store results,by the way,is that CDM tables are created manually and the tables to store the results in ‘ohdsi’ schema are created automatically after configured WebAPI?.
Then should I do similar steps to deal with source 2?

It’s very kind of your help.
Sincerely.
-Zhu

Chris_Knoll · March 8, 2018, 6:07am

Ok, I’m not sure which documentation you are using, but there’s the primary WebAPI database that you create your initial ‘ohdsi’ schema in, and WebAPI automatically deploys necessary tables for WebAPI functionality. This is completely different from the database that you input into the SOURCE and SOURCE_DAIMON tables. You could have a single oracle database, but the typical approach is 1 databse for WebAPI and one database per CDM/Results schema.

Since you already have your WebAPI running and that looks good, then all you need is to create a record (your source 1) that points to your CDM database, you’ll have 2 schemas in this database: ‘cdm’ and ‘results’. Once you load your CDM data (which includes vocabulary tables) you then need to create the tables in the ‘results’ schema manually. In release 2.3.0, we added a new endpoint that will give you the DDL you need: WebAPI/ddl/results?dialect=oracle (you can remove the dialect part if you just want to see the ‘standard’ DDL for creating the tables). These are the tables you want to create in your ‘results’ schema, and then in your SOURCE_DAIMON table, your table qualifier for the ‘results’ daimon type should be set to your results schema name (which I suggest you name ‘results’).

I don’t recommend you share the same database with WebAPI and your CDMs. Things get a bit more complicated once you start adding multiple CDMs to your SOURCE table. But if you have limited resources, you can set it up with a single database with the following schemas:

ohdsi: this is configured in your settings.xml file and points to the schema that WebAPI will create tables in
cdm: this is the schema with CDM and Vocabulary tables
results: this is the schema with the results tables which you can get the DDL from WebAPI/ddl/results?dialect=oracle

ZHU_C · March 9, 2018, 2:20am

@Chris_Knoll Sorry for disturbing you again. I do don’t know how to deal with the next problems.
The ‘ohdsi’ schema has been set up with no problem.But I failed to set up the ‘cdm’ and ‘results’ schemas with your suggestion.
①.For ‘cdm’,I can’t find the Vocabulary tables, I downloaded ‘Vocabulary-v5.0’ from ‘athena’ and ran ‘java -jar cpt4.jar‘ successfully,although I don’t know whether it has relation with ‘Vocabulary tables’.Could you tell me where to find these tables?
②.For ‘results’,you say I can get the DDL from WebAPI/ddl/results?dialect=oracle,but I can’t open the page.Is that a local folde or should I add something before the ‘WebAPI/ddl/results?dialect=oracle’?

I‘m really sorry for disturbing you once again,but I can’t find someone else to help me.
Sincerely.
-ZHU

Chris_Knoll · March 9, 2018, 3:19am

Hi, @ZHU_C,
It has been a long time since I’ve downloaded those files, but @lee_evans might be able to give you specific instructions or @chen_regen has experience on oracle, maybe they can help you with importing the text files into an oracle table.

Re the @results: Sorry, I meant to say access that URL under your WebAPI service, so looking back on your prior post, it looks like: http://localhost:8000/WebAPI/ddl/results?dialect=oracle. You can remove the dialect part if you just want to see the simple CREATE TABLE statements.

-Chris

ZHU_C · March 9, 2018, 9:46am

@Chris_Knoll thank you！ I find the DDL.But,it seems that some codes don‘t match with my oracle.I just want to modify it,but I don‘t konw whether it will make bad influence.

For example:
①: I set up the ‘results’ schema and run the codes

      CREATE TABLE @results_schema.cohort
(
	COHORT_DEFINITION_ID int NOT NULL,
	SUBJECT_ID bigint NOT NULL,
	cohort_start_date date NOT NULL,
	cohort_end_date date NOT NULL
);

The error is

00903. 00000 -  "invalid table name"
*Cause:    
*Action:

If I delete “@results_schema.”,it runs correctly.Therefore,should I delete it?

②: It seems that identifier should be less than 30 characters in oracle sql developer according to the specify,but the code below:

CONSTRAINT DF_heracles_results_dist_last_update DEFAULT GETDATE()

Obviously,the identifier is more than 30 characters.I do don’t know how to deal with it.

Could you help me once again?
Sincerely.
-ZHU.

Chris_Knoll · March 9, 2018, 3:21pm

Hi,

Everywhere you see an @results_schema token, you should find-replace it with the name of your schema. It might work with you just deleting the @results_schema, but I’m not sure which schema the default table creation will go into. I would look for any tables you created to make sure they are in the correct schema. If they are not in the results schema, you need to drop the table, and re-create it with the result schema specified. To be safe, please specify the results schema: do not let it choose a default schema to create the tables into.

The long identifiers: you can manually shorten them. For the ones you find that are too long, can you please post the list here and we’ll issue an update that will make the default lengths < 30 characters for all DBMS.

Thank you!

-Chris

ZHU_C · March 10, 2018, 4:11am

HI,
This is my results shema

Is that correct?

Then,these are the two lists which identifiers are too long in my environment (oracle sql developer).
@heracles_results,

 CONSTRAINT DF_HERACLES_results_last_update DEFAULT GETDATE()

Sincerely.
-ZHU

@heracles_results_dist

CONSTRAINT DF_heracles_results_dist_last_update DEFAULT GETDATE()

Chris_Knoll · March 10, 2018, 5:23am

Yes, that looks like you have all the required tables in the results schema.

We’ll make an update so that the /ddl returns identifiers that are shorter than 30 characters.

Thank you!

ZHU_C · March 10, 2018, 8:08am

Thank you all of your help.
Thank you very much.

Chris_Knoll · March 10, 2018, 6:22pm

Please let us know if you’ve resolved your problems and we’ll close out the github issue you opened.

ZHU_C · March 11, 2018, 2:24pm

OK,you can colse out the issue.Thank you.