microsoft/ohdsionazure

Automation code and documentation for deploying OHDSI CDM in Azure

Introduction

OHDSI on Azure GitHub repository is designed to ease deployment of tools provided by the Observational Health Data Sciences and Informatics (OHDSI, pronounced "Odyssey") community on to Azure. We are guided by our Hypothesis and core objectives.

Hypothesis - “OHDSI on Azure will empower IT department and operations teams to support researchers, thus increasing researchers' motivation to act on new ideas”

Objectives

1. Decreased deployment challenges
2. Increased access to funding
3. Simplified adoption strategy

OHDSI on Azure is a set of scripts and templates designed to automate the deployment of the solution in the Microsoft Azure cloud using Bicep & PaaS services. It is designed to facilitate standardized scalable deployments within customer managed Azure subscriptions. Provide best practices for running OHDSI on Azure. Ease the burden of management and cost monitoring of research projects.

OHDSI on Azure has taken a container-based approach to operating OHDSI tools. Therefore, OHDSI on Azure does its best to not host code developed by the OHDSI community. Our deployment templates pull containers from Docker Hub.

We invite you and your organization to participate in the continued feature expansion of OHDSI on Azure.

This repository assumes the end user is familiar with the OHDSI community, OMOP, Azure, and Bicep.

Some of the OHDSI projects included:

• Common Data Model (CDM), including Vocabulary
• Atlas - OSS tool used to conduct analyses on standardized observational data converted to the OMOP Common Data Model V5
• WebApi - contains all OHDSI RESTful services that can be called from OHDSI applications
• Achilles - provides descriptive statistics on an OMOP CDM database
• ETL-Synthea - Conversion from Synthea CSV to OMOP CDM

Overview

• You can host your CDM in Azure PostgreSQL. You can load your cdm and vocabularies into Azure Storage Container as cs.gz files, and pass as a paramater in your custom deployment.

CDM Version

This setup is based on the CDM v5.4.0 and supports both PostgreSQL and Azure Synapse.

Getting Started

To get started, click on deploy to Azure button. To get more detailed instructions, please refer to the Deployment Guide.

Create a synthetic OMOP CDM

This solution comes with a prebuilt synthetic CDM of 1,000 patients. If you wish to create your own (maybe larger) synthetic dataset you can follow a similar process described here.

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.

ohdsionazure's Issues

Discuss pain points from manual deployment and brainstorm approaches for resolving them

• Document all pain points
• Provide actionable item for each pain point

Investigate github actions for TF Plan

• Github action workflow can use terraform plan but it needs an Azure Subscription (az login) context
  • Setup test Azure subscription for TF plan
  • Look into using github secrets

Investigate and create design documentation for automating application deployment (zeus)

Leverage Zeus CLI to automate (as much as possible) the deployment of Broadsea.

This involves completing a design doc that will highlight strategy and implementation for automating application deployment using Zeus. This can involve:

• kicking off Broadsea build and release pipelines using CLI (i.e. zeus deploy broadsea)

Update Documentation Based on Testing

• Update terraform apply instructions to include example variables for omop_password
• Update azure cli instructions to include installing Azure DevOps extension
• Update vocabulary notes to include public demo Azure Storage Account
• Update omop readme prerequisites

Create documentation on deploying infrastructure (initial draft)

Create a README that will cover the logistics of using Terraform to deploy resources in Azure to support OHDSI CDM.

Create documentation on how OHDSI Applications (WebAPI, Atlas, Achilles) will be deployed

• Using Azure DevOps pipelines
• Broadsea Docker image
• CICD

Investigate using forked github repos versus importing repo into Azure DevOps

• Come up with pros and cons for each approach
• Gather feedback from team and test users

Should we allow the flexibility to have both options? Or stick to one approach for simplicity?

If using forked repos in Github:

• Remove the need to import repos and have them be in sync in two different places (AzDo and GitHub)
• User will still need to manage forked repos and ensure that it is fetching from official microsoft/OHDSIonAzure repo (i.e. git fetch)
• Update bootstrap/azure_devops_build_definitions.tf repository block:

    repo_type   = "GitHub"
    repo_id     = "yradsmikham/OHDSIonAzure"
    branch_name = "main"
    yml_path    = "pipelines/environments/TF-OMOP.yaml"
    service_connection_id = "<service-connection-id>"
  }

• Refactor pipeline yaml files to refer to forked repo (?)

If importing repos into Azure DevOps:

• Stick to current design
• User will still need to manage forked repos and ensure that it is fetching from official microsoft/OHDSIonAzure repo (i.e. git fetch)

Docs Update - include TF marketplace extension

• Include one-time setup for TF marketplace extension in docs (e.g. in setup infra or known errors in omop readme)
  

• https://marketplace.visualstudio.com/items?itemName=charleszipp.azure-pipelines-tasks-terraform&targetId=4832b63f-ef3c-4cbb-9196-d0f56f73bbb6&utm_source=vstsproduct&utm_medium=ExtHubManageList

• Extra credit: Look into automating adding the extension in Azure DevOps

Document approach for automating demo environment

• Document approach related to building demo environment with github actions (see #61 and #60)

Investigate using Azurite for Data Loading in SQL Test Suite

• Investigate using Azurite as part of SQL test suite to stand in for Azure Storage.
  • Document learnings / approach
  • Posted issue: Azure/Azurite#1474 to see if there's other information for this approach.

Investigate Authorizing Environments and Queues

• Investigate authorizing ADO environments and ADO queues
  • these are currently not included in the Azure DevOps TF provider, currently using script as a workaround.

Documentation

Include documentation around the following:

• Best practices for scaling OHDSI CDM
• "Enterprise-friendly" private networking architecture
• Best practices for secrets management (i.e. Azure SQL admin pwd (managed using TF and KeyVault), rotating passwords and more)
• Environment promotion/management

Investigate Data loading for SQL Test Suite

Related to #22 and #47:

• Investigate loading local data as part of SQL test suite

Investigate Creating new Azure DevOps Project / Repo for environment with bootstrap tf

• Document bootstrap TF setup for creating a new Azure DevOps project and Azure DevOps repository for an environment

Docs Update: TF Destroy Pipeline

• Update readmes for TF destroy pipeline usage (variables, workflow etc.):
  • pipeline readme
  • Include changes needed for updating your variables

Automate installing TF extension to Azure DevOps

Continuation from #38.

May need to create bash script and leverage TF null resource to deploy.

Move Work in progress into OHDSI on Azure repo

Move WIP from ado repo into github repo.

Investigate automating backend state settings for local development

• The bootstrap TF sets up an Azure Blob Storage account and container for the omop TF.
  • Investigate if there's a way to include passing the storage account settings into the omop TF workspace (for now, this assumes that the same person is running both the bootstrap TF project and omop TF project)
  • Store Terraform state in Azure Storage | Microsoft Docs, this is handled through terraform init once you have configured your backend

Link Demo videos to Youtube

• Link setup videos to Youtube

Investigate CI Validation with Github Actions for SQL Testing

• Incorporate approach from #22 as part of CI validation in Github Actions workflow

Investigate validation for pipeline error handling

• Look for ways to reduce false positives with error message handling:
  e.g. Running sqlcmd scripts will output errors but the task still appears as successful: https://dev.azure.com/US-HLS-AppInnovations/OHDSIonAzure/_build/results?buildId=442&view=logs&j=3b0e7125-c95f-52dd-42d7-523b984e00aa&t=c2c30411-82fa-5356-a77e-b34ee18dcc1e

Investigate github actions for TF init and validate

• TF plan for infra/terraform/bootstrap and infra/terroform/omop
• Create Github action POC for CI validation
• Are there other options to use for CI validation?

Investigate building demo environment with github actions

• Investigate storing terraform backend state for bootstrap for use with demo environment. This will allow for tear down and tear up for bootstrap environment.

  • One place could be as a github action pipeline artifact
  • Or you could use a designated azure storage account

• Investigate calling ADO pipelines from workflow

• Investigate using a designated vocabulary for the demo environment

  • Potentially store in demo Azure Storage Account

Investigate Adding Terraform Destroy for Environment Pipeline

• Investigate automating TF destroy option for TF Environment Pipeline

Update support.md

• Update support.md

Investigate Building CI Environment using github actions

• Build a CI environment based on PRs to main using github actions workflow
  • Potentially call ADO pipelines
  • Can you use random generated prefix/environment settings
  • Be sure to spin down once orchestration is finished
  • Can use test suite vocabulary as a starting point

Investigate options for automating vocabulary versioning

Option: https://docs.pachyderm.com/latest/concepts/data-concepts/repo/

Update Documentation for Troubleshooting VMSS Agents

• Revisit VMSS Agent documentation

Investigate using build completion trigger for vocabulary pipeline

• The Vocabulary Release pipeline can be triggered off the vocabulary build pipeline.
  • You can hardcode the name of the Vocabulary Build pipeline as part of the trigger, but flexibility through using pipeline parameters / variables would be preferred as there isn't a guarantee that the pipeline names are unique in a project
  • See https://github.com/microsoft/OHDSIonAzure/blob/main/pipelines/vocabulary_release_pipeline.yaml#L85

Docs Update - Add notes around switching location for OMOP environment

• You may run into quota issues for a given region for azure resources (e.g. Azure SQL Server, Azure App Service Plan).
  • Update notes to include switching location to a different region

Investigate Elastic Pool Setup

• The TF provider doesn't currently support using Elastic Pools.
  • investigate setting up elastic pools (Azure VMSS) for ADO
  • investigate authorizing VMSS
  • Current workaround uses the Azure DevOps API

Investigate Testing Suite Approach for SQL

• Approach for unit testing SQL DB
• Link test suite to github actions for CI validation

Work on E2E demo videos

• Setup E2E demo videos for setting up an OHDSI on Azure:
  • Assume this is importing from github into Azure DevOps repo
  • Run application pipelines including vocabulary setup
  • Extra credit: Captions for steps, no voice needed

Investigate Cohort Definition / Generation

• Create a cohort definition
  • Point to a concept set (you can create one on the fly)
  • Set your initial event criteria
  • Save the cohort definition
  • Attempt to generate while pointing to your data source

This works in the demo site, but need to investigate how this works

Docs Update: Tear Down Environment Guide

• Docs update for environment tear down
  • OMOP TF Project (using TF destroy pipeline)
  • Bootstrap TF project (using terraform destroy)

Investigate bootstrap tf import repository using git repo

• Bootstrap TF currently has a path for importing a repository from an existing git repository, and this can be changed to point to https://github.com/microsoft/OHDSIonAzure
  • Assume that the github repository is public (e.g. no PAT required)
• Document approach for new users in the bootstrap readme

Investigate Atlas Dashboard Errors

Some of the Atlas dashboards aren't getting populated (post Synthea-ETL and Achilles Run). See if it's possible to fill in other Atlas dashboards based on missing data.

Example: Check the condition occurrence dashboard, and you will see an error in the logs:

        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:696)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638)```

Investigate Static Analysis for Terraform

• See if static analysis can be included in github actions for CI validation: https://docs.microsoft.com/en-us/azure/developer/terraform/best-practices-integration-testing#:~:text=Automate%20Integration%20Testing%20Using%20Azure%20Pipeline%201%20Using,that%20reads%2C%20Step%201%3A%20run%20the...%20See%20More.

Errors with running `terraform destroy` for bootstrap

1. Add documentation around executing terraform state rm on project name if it was imported initially rather than created through terraform. Or does current documentation suffice? Double check.

2. For certain users (i.e. Mac OS users), permission issues may occur on Terraform modules directories.

Error: local-exec provisioner error
│
│   with module.azure_devops_environment_vocabulary_release_pipeline_assignment.null_resource.azure_devops_environment_pipeline_assignment_remove,
│   on ../modules/azure_devops_environment_pipeline_assignment/azure_devops_environment_pipeline_assignment.tf line 28, in resource "null_resource" "azure_devops_environment_pipeline_assignment_remove":
│   28:   provisioner "local-exec" {
│
│ Error running command
│ '../modules/azure_devops_environment_pipeline_assignment/azure_devops_environment_pipeline_assignment.sh >
│ azure_devops_environment_pipeline_assignment.txt': exit status 126. Output: /bin/sh:
│ ../modules/azure_devops_environment_pipeline_assignment/azure_devops_environment_pipeline_assignment.sh:
│ Permission denied

Run chmod -R +x path/to/module

3. Issue with "Extensions" property. Had to manually uninstall 'Microsoft.Azure.DevOps.Pipelines.Agent' in Azure portal, then run terraform destroy again.

Error: deleting Extension "yvonne-test-build-agent-dependencies" (Virtual Machine Scale Set "yvonne-test-ado-build-windows-vmss-agent" / Resource Group "yvonne-test-ado-bootstrap-omop-rg"): compute.VirtualMachineScaleSetExtensionsClient#Delete: Failure sending request: StatusCode=400 -- Original Error: Code="BadRequest" Message="On resource 'yvonne-test-ado-build-windows-vmss-agent', extension 'Microsoft.Azure.DevOps.Pipelines.Agent' specifies 'yvonne-test-build-agent-dependencies' in its provisionAfterExtensions property, but the extension 'yvonne-test-build-agent-dependencies' will no longer exist. First, remove the extension 'Microsoft.Azure.DevOps.Pipelines.Agent' or remove 'yvonne-test-build-agent-dependencies' from the provisionAfterExtensions property of 'Microsoft.Azure.DevOps.Pipelines.Agent

No sound for videos on set up page

The videos on this page don't seem to have audio.
https://github.com/microsoft/OHDSIonAzure/blob/main/docs/creating_your_environment.md

Investigate using Azure App Insights with Azure App Service

• Application logging in the App Service container can be sent to Azure App Insights, more information can be found here

Investigate Linting as part of CI Validation

• Investigate Linting options as part of CI Validation:
  • https://github.com/github/super-linter
• Linting could include coverage for:
  • SQL
  • Terraform
  • Shell (bash / PowerShell)
  • Dockerfile
  • Markdown
  • YAML
• POC: linter as part of github action

Add Notes for cleaning up OMOP TF project

• Add notes for running terraform destroy with the OMOP TF project

Investigate and create design documentation for system observability (zeus)

Question: In addition to Application Insights, how else can we expand observability for the OMOP environment?

Create design documentation to summarize approach. Implement as part of zeus observe.

Docs Update - Unique Key Vault FQDN required for Bootstrap

• Add known error notes for working with Azure Key Vault in the bootstrap docs.
  • Azure Key Vault needs a unique FQDN, so advise around using unique prefix / environment combinations

Investigate Tag Approach for Release

• Investigate tag approach for release strategy
  • Use branches that are tagged to a specific release
  • Consider how to rollback to a prior tag
  • How to publish a release
  • What artifact should be released
  • Release notes
  • Capture dacpacs and other artifacts
• Workaround: document approach to get 'latest' from repo:

git remote add github https://github.com/microsoft/OHDSIonAzure
git fetch github
git merge github/main

Investigate Release Strategy

• Investigate release strategy
  • How to publish a release
  • What artifact should be released
  • Release notes

Documentation Update: Troubleshooting notes for Atlas / WebApi

• Include note around post TF deployment steps

Investigate and create design documentation for automating infrastructure deployment (zeus)

Development repo for: https://github.com/yradsmikham/zeus

Leverage Zeus CLI to automate (as much as possible) deployment for infrastructure. This design doc will highlight implementation and reasoning for this approach. For example,

zeus infra create dev

will create directories for TF bootstrap and TF OMOP env.

This implies user will need to provide .tfvars -> run terraform apply for bootstrap -> kick off TF OMOP pipeline -> Upload Vocab -> Kick off rest of pipelines. Can we automate this more?

Investigate upgrading TF Azure DevOps provider

• Check version from 0.2.0 to 0.2.1 for TF Azure DevOps provider works with bootstrap
  • Update bootstrap documentation to use new provider