How To Access A User Work Database Or Datamart From Your Enclave Project Workspace VDI

 

 

 

 

This document outlines the requirements and instructions for connecting to a User Work Database or User Work Datamart from your Enclave project workspace virtual desktop infrastructure (VDI). 

Introduction

A User Work Database is an empty database for research. A User Work Datamart contains a patient cohort pulled from the Patient Data Science Repository (PDSR). Enclave project staff members are granted full permissions to either the database or datamart and both are accessed from your Enclave project workspace VDI.

Requirements

  • Ensure that you have the Mass General Brigham (MGB) credentials (username and password) before trying to connect to the Enclave platform.
  • Ensure that you have established a MGB VPN connection. | KB0023967
  • The requested User Work Database or Datamart has been provisioned. | KB0040551
  • If accessing a User Work Patient Cohort Datamart, your access to the User Work Datamart has been submitted, approved, and provisioned. | KB0039860
  • You're logged into your Enclave Project Workspace VDI.
  • Please allow 2-3 hours after provisioning before accessing the User Work Database or Datamart.

Instructions

1. Accessing using Microsoft SQL Server Management Studio (SSMS)

  1. From your Enclave project workspace VDI, click on the Microsoft SSMS SQL icon. Please be patient, SSMS can take up to 20-30 seconds to launch.
  2. When the SSMS application opens, you will be prompted to connect to the SQL Server. The following entries should be used:
    • For Server name, type or copy & paste the following: mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net
    • From the Authentication dropdown, select: Azure Active Directory – Universal with MFA
    • For the User name field, type your full primary Mass General Brigham email address

! Make sure you type in your primary MGB email address in full

  1. Once all the entries have been filled, click on the Connect button. In the next window, expand the Databases folder.

4. All User Work Databases and Datamarts are listed using the same naming convention. The unique Project ID followed by _Userwork. (Example: PROJ1234_Userwork) Click on your User Work Database or Datamart.

2. Upload data from a file using Bulk Copy Program (bcp)

The bcp utility can be used to import large numbers of new rows into a User Work Datamart and/or Database table. To import data into a table, you must use a format file created for that table. Below provides basic instructions on how to import:

1. Create a .csv file with column headers and data matching the table columns you've created in your User Work Datamart or Database.
2. Within your Enclave project workspace, open a cmd prompt: Open the "Start" menu and type "cmd". Click "Command Prompt".
3. Type "C:\Program Files\Microsoft SQL Server\160\DAC\bin\".
4. Syntax for bcp upload command. You may exclude -F 2 if your file does not include headers.

bcp [database_name.] schema.{table_name} in {csv file location} -c -G -U -t -S tcp:mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net -F 2

Example: bcp proj1234.dbo.bulk_insert_test in C:\temp\bulk_insert_test.csv -c -G -U -t -S tcp:mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net -F 2

For more information, see Microsoft bcp utility documentation.

3. Accessing using other Enclave tools

The same as the Complete PDSR Curated Data Set, all User Work Databases and Datamarts reside on an Azure SQL Server. Multiple Enclave knowledge articles exists detailing using various tools to access the Complete PDSR Curated Data Set. These same articles can be used to connect to your User Work Database or Datamart. Remember to replace all Server article references to mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net when connecting.

  • KB0040282 How to Test Connections to the Complete PDSR Curated Data Set Using Python
  • KB0040356 How to Test Connections to the Complete PDSR Curated Data Set Using R - Enclave Linux VDI
  • KB0040279 Connect to the Complete PDSR Curated Data Set Using Azure Data Studio in the Enclave Linux VDI

Additional Resources

Introduction to SQL | Codefinity

Microsoft Educational SQL Resources | Microsoft Doc 

Microsoft bcp utility documentation | Microsoft Doc

 

Go to KB0040678 in the IS Service Desk

Related articles

Analytics Enclave

IDEA Platform