Analyzing Clinical Trials Data using Snowflake and ThoughtSpot – Part 2
ThoughtSpot and Snowflake recently announced tight integration of their offerings. In the recently concluded Beyond 2019 (ThoughtSpot annual conference) in Dallas, ThoughtSpot shows how data stored in Snowflake can be analyzed in ThoughtSpot.
In Part 1 of this blog, we focused on ingesting the data (in XML format) into Snowflake and preparing it for analysis in ThoughtSpot.
In this blog (Part 2), we will focus on how to connect to from ThoughtSpot to Snowflake and perform the search.
ThoughtSpot released Embrace as part of version 5.3. Embrace allows you to directly query that data and use ThoughtSpot’s analysis and visualization features, without moving the data into ThoughtSpot.
Embrace supports two modes:
- Linked: Embrace runs the query on SnowFlake
- Synced: Embrace runs the query upon bringing the data into ThoughtSpot
In this blog, we will show you the Linked mode example.
For this demo, we have used Clinical Trials Data which is publicly available on https://clinicaltrials.gov/ct2/search/advanced
- Database: healthcaresolution
- Schema: ClinicalTrials
- View: clinical_trial_summary
- View: clinical_trial_conditions
- Warehouse: LoadWH
Under the Data option, click on Connections and then the Add Connection button.
Select Snowflake and click Next
Enter the requesting connection details
Select the tables you will need for analysis; in our case, we will select the following:
Click Create Connections and ThouhtSpot will do the following:
- Create sfHealth connection
- Create clinical_trail_conditions and clinical_trial_summary tables
Create a ThoughtSpot Worksheet
As a good practice, we will create a Worksheet called Clinical Trials on the Snowflake view Clinical Trial Summary. In the screenshot below, you will notice column names are more business-friendly and we have created required synonyms.
For reference in how to create a ThoughtSpot Worksheet, refer to https://docs.thoughtspot.com/5.3/admin/worksheets/about-worksheets.html
For searching, click on Search and click on the database button.
In order to choose the table, view or worksheet, click on Choose sources button.
To make it easier, put in “clinical” in the filter text box. Check the Worksheet Clinical Trials.
Now, you will see all the columns and you are ready to search.
Examples of Search
ThoughtSpot allows users to query the data using keywords.
Example 1: Get a count of all trails grouped by min and max age criteria
Example 2: Analysis of trails by Start and Completion Date
Example 3: Time taken by Trial