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.
If you have any questions or issues not covered not covered on this page, please consult the FAQ page
Creating a Windows workspace
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.
For more information about studies, view the documentation here.
Step 1: Navigate to the Studies page on the left of your screen
Step 2: Click the Organizations tab
Step 3: Select the study with your project and user name attached
Step 4: Click Next
Select the Windows compute platform.
Step 5: Select the Windows Compute Platform as shown below
Step 6: Click Next
Set your workspace parameters.
Step 7: Enter a 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.
No change necessary for the Restricted CIDR field
Step 8: Select the Project Id dropdown
Step 9: Select your AIM AHEAD affiliation: For example Research-Fellowship or Consortium-Development-Project
Step 10: Select the windows-small configuration
Step 11: Enter a Description for your benefit: Any description. Note that the Description must be at least 3 characters.
Step 12: Click Create Research Workspace
Wait while your workspace is provisioned
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
You must repeat the following steps EACH time you start up your workspace in Sagemaker and it changes from Stopped to Available
Get Your Windows Workspace Credentials from the Service Workbench
Ensure your Workspace is listed as Available
Step 1: Once your workspace is listed as Available, click the Connections button
Step 2: Click "Get Password" to view your credentials for connecting to the Windows workspace.
You may see a Study Syncing message in Service Workbench when retrieving your credentials. This is not a concern, and you should proceed as normal.
Add a new connection to your Remote Desktop Application.
Make sure you have installed the Remote Desktop Application described above. This guide uses the MacOS version, so your application might look different.
Step 1: Select the dropdown to add a new PC
Step 2: Select Add PC
Step 3: Copy the Host field from Service Workbench into the PC Name field of the Remote Desktop Application
Step 4: Select the Credentials dropdown
Step 5: Select Add Credentials
Step 6: Copy the Username field from Service Workbench into the Username field of the Remote Desktop Application
Step 7: Copy the Password field from Service Workbench into the Password field of the Remote Desktop Application
Step 8: Click OK
Step 9: Click Add
Refer to the following table and images if needed to determine corresponding fields.
host
PC Name
username
Username
password
Password
Connect to the newly created PC
Step 10: Open the menu for the newly created PC
Step 11: Connect to the PC
Step 12: Wait for the Application to connect
Step 13: Click Continue
Step 14: You may have to wait for the connection again
Step 15: Copy the Password from Service Workbench to the prompted Password field
Step 16: Click Continue
Connecting to the SQL server
Open your DB-Credentials.txt file (located on your Remote Desktop)
This file contains all the information you need to connect to the SQL server.
Because you will be copying and pasting from this file, it is recommended to move the window off to the side of the screen as shown below.
Step 1: Open the DB-Credentials.txt file located on your Remote Desktop.
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.
Open the SQL Server Application Studio Application
Step 2: Click into the search box in the bottom left of the remote desktop
Step 3: Enter "SQL" into the search box
Step 4: Select the SQL Server Application Studio Application
Be sure to open the SQL Server Management Studio application, NOT the SQL Server Profiler
Enter your credentials into the SQL Server login
Step 5: Select the Authentication field
Step 6: Make sure the Authentication field is set to SQL Server Authentication
Step 7: Copy the Host field from the db_credentials.txt file into the Server name field of the SQL Server login. The Host field may appear to be a dropdown menu, but you can copy the text in as normal.
Step 8: Copy the username field from the db_credentials.txt file into the Login field of the SQL Server login
Step 9: Copy the password field from the db_credentials.txt file into the Password field of the SQL Server login
Step 10: If a dropdown field labeled "ENCRYPTION" appears, select the choice Optional
Step 11: Click Connect
Refer to the following chart an image if needed to determine which fields correspond on this page:
host
Server Name (this appears as a dropdown menu, but you can copy and paste text into the field)
username
Login
password
Password
If you see an "Encryption" dropdown menu, set it to Optional as shown. This dropdown will not appear for all users.
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.
Last updated