Phase 2: Data Exploration

This section covers how to use a Windows workspace to write SQL queries to explore the data and save data subsets for analysis.

Creating a Windows workspace

Step 1: Navigate to the Studies page and click on the Organization tab. Mount your study(ies) to your workspace.

The organization studies are linked to Amazon S3 secure storage. This means that anything saved in these study folders will be securely saved and accessible through any workspace the study is mounted to.

To mount a study to your workspace, check the box next to the study name.

For more information about studies, view the documentation here.

Step 2: Select the Windows compute platform.

Step 3: Set your workspace parameters.

Name: Any name. Note that the Name can contain only alphanumeric characters (case sensitive) and hyphens. It must start with an alphabetic character and cannot be longer than 128 characters.

Restricted CIDR: No change necessary

Project ID: Select your AIM AHEAD affiliation, for example Research-Fellowship or Consortium-Development-Project

Configuration: windows-small

Description: Any description. Note that the Description must be at least 3 characters.

Step 4: Provision your workspace.

This may take 12-20 minutes.

Once your workspace is listed as AVAILABLE, you can connect to it.

When you are finished working on your workspace for the day, please STOP the workspace to avoid incurring excess cloud costs.

Connecting to a Windows workspace

The following steps use Microsoft Remote Desktop, a free app available for Mac or Windows, as an example. If you have a different application to connect to remote desktops, your workflow may look different.

Step 1: Click Connections -> Get Password

Once your workspace is listed as AVAILABLE, you should be able to click the Connections button and then click "Get Password" to view your credentials for connecting to the Windows workspace.

Step 2: Add a new connection to your Remote Desktop Application.

The following steps use Microsoft Remote Desktop, a free app available for Mac or Windows, as an example. If you have a different application to connect to remote desktops, your workflow may look different.

Step 3: Use the public host address, username, and password shown in Service Workbench to connect to the Windows workspace.

Connecting to the SQL server

Step 1: Open the SQL Server Application Studio Application

Step 2: Open your DB-Credentials.txt file (located on your Desktop)

This file contains all the information you need to connect to the SQL server.

Note that your password will refresh every 24 hours; make sure you close the file when you are done working with it so the refresh is not interrupted.

Step 3: Enter your credentials into the SQL Server login

Note: If you see an "Encryption" dropdown menu, set it to Optional.

Running a SQL query

To run a query, click the database in the Object Explorer on the left, and then click the New Query button near the top of the window. That opens a tab where you can enter SQL code. A tutorial introducing SQL can be found at https://www.w3schools.com/sql/sql_intro.asp.

The version of SQL used by Microsoft SQL Server is called T-SQL. Microsoft’s reference guide for T-SQL is at https://learn.microsoft.com/en-us/sql/t-sql/language-reference.

Because you will be working with multiple databases and schemas, it is important to understand how to use “fully qualified” names for tables and views, which have this format:

[DatabaseName].[ViewName].[TableName]

As a basic example, we can view the first 10 rows of the CONCEPT_DIMENSION table like so:

select top 10 * from AAOCHIN2023.common.CONCEPT_DIMENSION;

In this example, we are querying the AAOCHIN2023 database, the common data view, and the CONCEPT_DIMENSION table.

For more explanation on how the data is organized and additional example queries, visit the Understanding the OCHIN Data page.

Saving data subsets

Previously, we simply ran the following query to view the first 10 rows of the CONCEPT_DIMENSION table:

select top 10 * from AAOCHIN2023.common.CONCEPT_DIMENSION;

To save the results of this query into our project database, we modify it like so:

select top 10 * into S000.dbo.test from AAOCHIN2023.common.CONCEPT_DIMENSION;

This will take the results of the previous query and create a new table called test inside our project database, in this case S000.