ThoughtSpot Tutorials for Snowflake Partner Connect

When you create a connection to Snowflake in ThoughtSpot, any data modeling or table joins are inherited automatically.

If there are no table joins in your Snowflake connection, you can easily create them in ThoughtSpot.

The following example shows how the table joins were created in the Sales table of the Retail Sales Worksheet, available in your try.thoughtspot.com account created through Snowflake Partner Connect.

Creating table joins

The joins in the Sales table were created by doing the following:

  1. Select Data in the top navigation bar.

  2. Select the Tables tab at the top of the page.

  3. Select the Sales table.

    The Columns view of the Sales table appears.

  4. Select the Joins tab.

  5. Select +Add join.

    The Add Join window appears.

  6. In the Add Join window, use the dropdown menus to make the following selections:

    • For Destination Table, select Products.

    • For Source Columns, select Product_Key.

    • For Destination Columns, select Product_Key.

    Specify source and destination tables and columns
  7. Select Next.

  8. Enter the name Product_Key - Product_Key, a description for your join (optional), and select Next.

    Name the join
    You can use any name you want. The names we’ve chosen for this tutorial match those in the actual schema for this dataset on try.thoughtspot.com.

    The first join is created. Now you will add the other joins.

  9. Select +Add join.

  10. In the Add Join window, use the dropdown menus to make the following selections:

    • For Destination Table, select Customers.

    • For Source Columns, select Customer_Key.

    • For Destination Columns, select Customer_Key.

    Specify source and destination tables and columns
  11. Select Next.

  12. Enter the name Customer_Key - Customer_Key, a description for your join (optional), and select Next.

    Name the join
  13. Select +Add join.

  14. In the Add Join window, use the dropdown menus to make the following selections:

    • For Destination Table, select Stores.

    • For Source Columns, select Store_Key.

    • For Destination Columns, select Store_Key.

    Specify source and destination tables and columns
  15. Select Next.

  16. Enter the name Store_Key-Store_Key, a description for your join (optional), and select Next.

    Name the join
  17. Select +Add join.

  18. In the Add Join window, use the dropdown menus to make the following selections:

    • For Destination Table, select Dates.

    • For Source Columns, select Date_Key.

    • For Destination Columns, select Date_Key.

    Specify source and destination tables and columns
  19. Select Next.

  20. Enter the name Date_Key - Date_Key, a description for your join (optional), and select Next.

    Name the join

    Now that all four table joins are created, the schema looks like this:

    View schema. The Sales table is joined to the Dates

Searching joined tables

You can easily search the joined tables, without having to create a Worksheet.

To search the joined tables, do the following:

  1. Select Search.

  2. Select the Retail Sales data source, and click Choose sources.

  3. Select all the tables you just joined (Customers, Dates, Products, Sales, and Stores) and select Close.

  4. In the search bar, enter sales_dollar_amount, store_region, and monthly Date in Dates.

    Enter items in the search bar

    The search results look like this:

    Line chart with sales by date
    When Monthly is a native keyword, it will work on any timestamp. For the purposes of this example, we’re using monthly as the date, from the Dates table.
  5. To confirm that the search is honoring the table joins, select the Query details icon query details icon, to the right of the chart.

    View the query details
  6. To confirm the search is bridging three different tables to create a result, select Query visualizer.

    Click on the query visualizer

Best practices for data modeling

Here are some examples of how you can model your data to enhance searchability:

  • Change column names

  • Add synonyms for columns

In the following example, the Sales_Dollar_Amount column was renamed to Sales and the synonyms of Revenue and Dollars were added.

Update data modeling settings for your tables

These are just a couple of examples of things you can do.

For more information about data modeling, see: Overview of data modeling settings

Creating a Worksheet

A Worksheet is a curated dataset built for ad hoc analysis, that allows you to translate data from a database into the language of your business users.

Examples of things you can do in a Worksheet include:

  • Removing columns that aren’t needed

  • Adding data labels and synonyms

  • Adding calculations, such as margin

The Worksheet based on the Sales table on try.thoughtspot.com was created by doing the following:

  1. Select Data.

  2. Click the + Create new button, and select Worksheet.

  3. Select the plus icon, next to Sources.

    Click the plus icon next to Sources

  4. Select the checkbox next to all five of the tables from the Retail dataset in your schema.

    Click on all your sources

  5. Make sure the default setting of Apply joins progressively is selected. This ensures that the search uses only the tables that are required.

  6. Select Close.

  7. In the Data view, select the name of the Customers table to reveal all of the columns in that table.

  8. Double-click each column from the Customers table that you want to include in the Worksheet.

    Include these columns:

    • Customer_Type

    • Customer Name

    • Customer_Gender

    • Customer Region

    • Customer State

    • Customer City

    • Customer Zip Code

    • Customer County

  9. Use the same process to select columns from the other tables to include in the Worksheet.

    From the Dates table, include this column:

    • Date

      From the Products table, include these columns:

    • Product_Description

    • Category_Description

    • Department_Description

      From the Sales table, include these columns:

    • Sales_Dollar_Amount

    • Cost_Dollar_Amount

    • Gross_Profit_Dollar_Amount

      From the Stores table, include these columns:

    • Store_Name

    • Store_Region

    • Store_State

    • Store_City

    • Store_Zip_Code

    • Store_County

      Worksheet in progress with the columns specified earlier in the article
    As a best practice, you would not select a key from a table when creating a Worksheet, because you would not want to search for the key.
  10. Select the pencil icon edit icon next to the current name of your Worksheet, enter the name Retail Sales, and select Done.

    Edit worksheet name
  11. Click the more options icon more options menu icon, and select Save.

    Save the worksheet

    Now, let’s add a percent gross margin formula to the Worksheet.

  12. Select Edit Worksheet.

  13. Next to Formulas, select the plus icon plus icon.

  14. In the formula window, do the following:

    1. In the top field, enter the formula title: % Gross Margin.

    2. In the next field, enter this formula:

      sum ( gross_profit_dollar_amount ) / sum ( sales_dollar_amount ) * 100
    3. Select Save.

      Save the formula
  15. Save the Worksheet with the formula added, by selecting the more options icon more options menu icon, and selecting Save.

  16. Select Data, and select the Retail Sales Worksheet.

  17. In the Columns view, make sure that the % Gross Margin formula has the following settings:

    • For DATA TYPE: DOUBLE

    • For COLUMN TYPE: MEASURE

    • For AGGREGATION: AVERAGE

  18. Save the Worksheet with the updated formula settings, by selecting the more options icon more options menu icon, and choosing Save.

Best practices for Worksheets

The best practices for data modeling also apply to Worksheets.

The example here includes:

  • Changed column names

  • Synonyms for columns

  • % Gross Margin formula

    Model the data in your worksheet

Adding a currency and geo map to a Worksheet

To further enhance the usability of a Worksheet, you can add a specific currency type to monetary values, and a geographic map to regions in your data.

Using the Retail Sales Worksheet example, here’s how geo maps and currency could be added:

  1. Select Data, and choose the Retail Sales Worksheet.

  2. In the Columns view, find the Sales column and select None in the Currency Type column.

  3. In the Specify Currency Type window, select Specify ISO Code and, then select USD from the dropdown menu.

    Specify ISO code to use the correct currency type
  4. In the Columns view, find the Store_State column, and select None in the Geo Config column.

  5. In the Specify Geographic Configuration window, select Specify Sub-nation region, keep the default country of United States, and then select State.

    Specify geo config
  6. Select Save Changes.

    Now that both currency and geographic types are set, you can see those changes reflected when you search the Retail Sales Worksheet.

  7. Select Search.

  8. Select Choose sources.

  9. Deselect any tables previously selected (if needed), select only the Retail Sales Worksheet, and select Close.

  10. In the search bar, enter: sales store state and press tab.

    The initial search results appear, but without labels for each state.

    Sales by state geo chart without labels

    The final step is to add the labels.

  11. Select the Edit chart configuration icon gear icon.

  12. In the Customize panel, select the Total Sales tile.

  13. In the Edit column panel, select the Data Labels checkbox.

    Now in the search results, you can see labels with the state name and total sales in US dollars.

    Sales by state geo chart with state names and sales in US dollars