| OHDSI Home | Forums | Wiki | Github |
Hi everyone,
After following the quick start installation guide for Broadsea and changing the ‘BROADSEA_HOST’ under the ‘.env’ file, we have deployed Atlas for our environment.
We would like to update the default data source to point to our CDM database, stored on an MS SQL database. Had a look under advanced usage and could see anything on this. Any help on this would be greatly appreciated.
Many thanks,
Solmaz
Hi @solmaz.eradat – Adding data sources is not in scope for Broadsea, but rather is something covered in the Atlas documentation. If you’ve set up Atlas with security, then simply use the “Add Data Source” button in the Configuration module. If not, then you’ll need to use some SQL:
Hi @Ajit_Londhe . Thank for the message, Still a bit unclear on the steps needed to step-up Atlas with a external MS SQL database.
You mention above If you’ve set up Atlas with security, then simply use the “Add Data Source” button in the Configuration module - are the steps to setup Atlas with Security and adding the data source outlines in the documentation?
Many thanks,
Solmaz
When using Broadsea to stand up Atlas and WebAPI, the quick start approach does not include security enabled Atlas (meaning, having logins for Atlas). You’d need to get into the “advanced” approach to enable this.
Based on your post, it sounds like you don’t have security enabled, so to add CDM sources into your Atlas, you’d need to use the SQL scripts I linked to from the WebAPI wiki.
Hi @Ajit_Londhe . Thanks again.
For the easiest setup with additional CDM databases and thinking of future maintenance, looks like we need to setup “Atlas with Security”. Looking at the .env file are the additional sections we need to change:
section 2 ATLAS_USER_AUTH_ENABLED="true"
section 4 and section 5.
Are there any other sections in the .env file which need to be completed that I might have missing?
Also, you mentioned to use the “Add Data Source” button in the Configuration module. Does this button appear in the Atlas application once security is enabled?
Many thanks,
Solmaz
I think for a quick implementation, use the queries at the wiki link I shared above.
But, if you’re looking to set up a more production-grade environment, you’d need to have some authentication system available that Atlas can work with (e.g. LDAP, AD, SAML, etc). If you have this, then you’d need to fill out the appropriate settings in Section 4-5:
In our upcoming Broadsea 3.1 release, we will offer an OpenLDAP service that you can use for a simple LDAP setup.
Thank you @Ajit_Londhe .
With the changes that we need to carryout to the .env file to enable security, I wanted to check is the security setup designed to work with Window Authentication?
This week I also joined the Atlas monthly call and had a chance to talk to @Chris_Knoll who was very helpful in providing a bit more detail around:
PostgreSQL DB setup guide - additional changes we may need to consider for the instance of the PostgreSQL Db for WebAPI
CDM configuration - setting up the additional results tables
Many thanks,
Solmaz
All of the variables needed for setting up AD (edited) should be in the env sections I linked to above; have a look at the AD auth section variables.
After you set up security and launch the WebAPI and Atlas containers successfully, you can then add an admin account, using these steps:
And then you can add new CDM sources via the Configuration → Add new source button.
Curios question here: is Docker executing on a linux host, and if so, does it integrate with Windows authentication? If so, that’s awesome 
Ugh, @Chris_Knoll, I didn’t consider the Windows Auth vs AD distinction!
@solmaz.eradat - I can say we can do AD, but Windows Auth, unclear at this point. I’ll raise an issue in our Github to explore this further: https://github.com/OHDSI/Broadsea/issues/105
Hi @Ajit_Londhe and @Chris_Knoll,
Appologies for delay in getting back. Using a test account we have manged to login to the Atlas application using Active Directory.
The following message appear when selecting each of the section on the left hand side “You do not have access to this feature.For more information on how to get access please contact the System Administrator”
Any ideas on how to resloving this message?
Many thanks,
Solmaz
When you log in for the first time, it will assign some default groups (I think public is one), but I don’t believe any of them give access to the app functions. You will need to assign the role to the user. You can do this under the configuration tab, and going into ‘manage permissions’ you can assign users to roles. But, this requires admin privs, so, to get that first permission, you’ll have to manually insert a record into sec_user_role where you find the user ID of yourself in the sec_user table, and then insert the appropriate row into sec_user_role to make the association to the admin role. After you have that, you can go into atlas and administer permis using the UI.
Specific instructions and documentation about this is found here.
Note, these tables have a sequence based primary key, so you’ll insert rows using something like this:
insert into webapi.sec_user_role (id, user_id, role_id, origin)
VALUES (nextval('webapi.sec_user_role_seqence'), {your_id}, {admin role id}, 'SYSTEM')
The important bit is to use the sequence webapi.sec_user_role_sequence to generate the auto-numbered id for this row.
Thank you @Chris_Knoll . We ran the code snippet above as you suggested and now we see the following in the configurtion tab:
The access denied is removed. From the Data Sources tab can Select a Source and Select a Report (example shown below):
I had a look under Configuration for documentation outlining steps for adding different sources and couldn’t find anything.
We are trying to add an MS SQL database hosted on Azure. If there is documentation on how to do this and you are able to share that would be greatly appreciated.
Many thanks,
Solmaz
Hi @Chris_Knoll,
Had a go at adding our data source as shown below:
When checking the conneciton as shown bleow:
the logs produce the following result
2024-01-22 17:27:03.039 INFO http-nio-8080-exec-1 com.odysseusinc.logging.LoggingService - [] -
Could not get JDBC Connection; nested exception is java.sql.SQLException: No suitable driver found for
Server=tcp:xxxx-database.windows.net,1433;Initial Catalog=<db-name>;Persist Security
Info=False;User ID=<username>;Password=<password>;
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Any ideas on how to resolve this?
Many thanks,
Solmaz
That does not look like a JDBC URL.
For MS SQL, it should look something like this:
jdbc:sqlserver://serverName[\instanceName][:portNumber];property=value;property=value
When I’m testing DB connectivity, I will make a connection through an app like Sql Workbench/J from the same host (ie your WebAPI server) that is making the connection to the CDM to ensure that there’s no network connectivity issues.
JDBC documentation for connecting to Azure is here.
Thanks @Chris_Knoll . Updated the connecion string as you suggested and added the relevent schemas as shown below:
jdbc:sqlserver://server-name.database.windows.net:1433;database=db-name;user=azuresql_admin@server-name;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
checked the conneciton to the database and looks like a successful connection is made
Able to select the data source and look at some of the summery level details. However, when selecting Condition Occurence get the follwoing error
Insepecting the logs gives the follwoing error :
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT
concept_hierarchy.concept_id,
CONCAT(
isNull(concept_hierarchy.level4_concept_name, 'NA'), '||',
isNull(concept_hierarchy.level3_concept_name, 'NA'), '||',
isNull(concept_hierarchy.level2_concept_name, 'NA'), '||',
isNull(concept_hierarchy.level2_concept_name, 'NA'), '||',
isNull(concept_hierarchy.concept_name, 'NA')
) AS concept_path,
ar1.count_value AS num_persons,
round(1.0 * ar1.count_value / denom.count_value, 5) AS percent_persons,
round(1.0 * ar2.count_value / ar1.count_value, 5) AS records_per_person
FROM (SELECT *
FROM results.achilles_results WHERE analysis_id = 400) ar1
INNER JOIN
(SELECT *
FROM results.achilles_results WHERE analysis_id = 401) ar2
ON ar1.stratum_1 = ar2.stratum_1
INNER JOIN
results.concept_hierarchy concept_hierarchy
ON CAST(CASE WHEN ar1.analysis_id = 400 THEN ar1.stratum_1 ELSE null END AS INT) = concept_hierarchy.concept_id
AND concept_hierarchy.treemap='Condition'
,
(SELECT count_value
FROM results.achilles_results WHERE analysis_id = 1) denom
ORDER BY ar1.count_value DESC]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'results.concept_hierarchy'.
I had a look for the following error message on the forum
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'results.concept_hierarchy'.
Found the following forum post General OMOP + WebAPI + Atlas setup - #9 by Chris_Knoll
Will the two scripts as mentioned on the post need to be run on the WebAPI database?
Many thanks,
Solmaz
Not on the WebAPI database. It’s important to understand the topology. There’s the WebAPI database which stores security, source connection info, cohort definitions, generation info (but not the actual results).
Then there is the CDM database which has your patient level data (in your cdm schema) and also a separate writable schema calld the results schema. YOu can download the DDL for setting up the results schema from WebAPI, but it’s a schema that sits along your CDM schema (you refrenced this when you created your new SOURCE in the atlasUI: it asked you for the results schema and cdm schema).
So, once you create the results schema you’ll be able to perform analytical tasks like cohort generation and characterization against your CDM. The WebAPI database just maintains a record of what you did (ie: you generated a cohort, you performed a characterization) but the results data is saved within the CDM database in the results schema. WebAPI will fetch the results of an analysis from the source.
Likewise, if you want to see achilles results, weapi will fecth the data from the results schema on your CDM…so make sure that you create your results schema tables in the same schema as you are putting your achilles results tables: WebAPI assumes the achilles results (ie: the stuff you see under the DataSources nav) is in the results schema.
Hi @Chris_Knoll.
Thanks for the explanation above.
We had an issue running Achilles a while back using production data Error in running Achilles - query processor ran out of internal resources - #5 by solmaz.eradat
Adding the excludeAnalysisIds = c(2004) as shown below:
Resolved the error and resulted in the generation of the follwoing 3 Achilles tables:
Will follow the next steps under setting up the Results schema using the following URL
http://<server:port>/WebAPI/ddl/results?dialect=<your_cdm_database_dialect>&schema=<your_results_schema>&vocabSchema=<your_vocab_schema>&tempSchema=<your_temp_schema>&initConceptHierarchy=true
Many thanks,
Solmaz
