Whenever you start a new analytics inquiry, the most important question to ask is “What use case does this support?”
We recommend that you keep the following points in mind as you select your use case, and then design a worksheet to support it.
Good use cases support:
- Actionable data, and measurable value
- Time value of answers
- Variability of questions
- Large addressable audience
- Large data volume, and need for granular analysis at variable levels
- Relational data that is modeled for analytical use
Worksheets provide a simplified view of your data for users to search. We designed them to directly support use cases, so they have the following advantages when compared to searching directly across tables:
- Optimize the Search experience
- Present only relevant data columns, to reduce information overload
- View flat presentation of combined data tables
- Rename columns and terms
- Enable change of numerical columns from measurement (default) to attribute
- Specify default aggregation functions
- Specify formatting and currency symbols
- Identify columns that contain geographical data
- Match the user vocabulary by mapping data to recognizable business concepts
- Standardize formulas for consistency and governance
- Give a user or group access to only part of the underlying data
- Give users a filtered set of data to search
Typically, you create one worksheet for each set of fact and dimension tables. For example, you may have a sales fact table and an inventory fact table. Each of these fact tables shares common dimensions like date, region, and store. In this scenario, you would create two worksheets: sales and inventory. The following diagram depicts the workflow for creating the sales worksheet.
To simplify data modeling, ThoughtSpot supports automated worksheet creation during Setup. When creating a worksheet through the Setup tab, you can view suggested joins, and take advantage of data modeling best practices, making your data easier to search.
Create a worksheet through Setup
To generate a worksheet through the Setup tab, follow these steps:
Complete Step 1 of Setup, setting up your connection.
Click Step 2, Model data and create a Worksheet. Select Create Worksheet in the lower left corner. A list of the available fact tables from your connection appears.
By default, ThoughtSpot suggests the fact table with the largest number of rows. Select the box next to the fact table from which you would like to create your worksheet and click Next.Note: After the worksheet is created, you will have the option to add more fact tables to its schema.
A list of the available dimension tables from your connection appears. Choose the dimension table(s) to join to your fact table and click Next. You can choose up to four dimension tables.
The joins interface appears, showing a join based on a column of data both tables contain. You can proceed with the suggested join, or click the column names to change the join.Note: The default join type is Inner, which will yield all search results with matching values from the fact table and the dimension table. You can click the Venn diagram icon to change the join type.
Click Next. The columns view of your worksheet appears. You may notice that the column names have been changed to make them more easily searchable (for example, underscores may be replaced with spaces).
[Optional] Click My Worksheet to change the name of your worksheet.
[Optional] Click the column titles to change the names of your searchable columns. You can also click the blue check-box next to a column name to remove it from your worksheet.
[Optional] Click the data type under Type to change the recorded data type. For example, you can change a data type like Zipcode from ‘Measure’ to ‘Attribute’.
Click Save worksheet. Your worksheet is now available to search and share.
Create a worksheet manually
Follow these steps to create worksheets:
Under Data, see Sources. Click +.
The Choose sources interface appears.
It contains the list of tables that in the connection.
Click on each table you plan to include in the worksheet.
At the top right corner of the interface, click Close.
Back on the Data interface, under Sources, note that the list of tables you selected appears.
Expand one of the tables by clicking the toggle icon.
Select the columns to include in the worksheet.
Click + Add columns.
Notice that the table and its columns appear on the main pane of the interface.
Repeat for each table you plan to include in the worksheet.
You can always navigate to a table you identified, and change the column selections.
After you finish selecting the tables and columns, click the More menu at the top right corner of the interface.
In the Save Worksheet interface, enter the following values:
Name is the name of the worksheet. You can select it as a data source in your searches.
Description is optional; we recommend that you identify the supported use case here.
Congratulations! You now have a worksheet to use as a data source for Searching and building standard formulas.
We recommend that you customize the worksheet in the following manner:
- Rename columns to make them user-readable.
- Change column type default settings of numeric fields from measurement to attribute, when these numbers represent categorical information instead of measurements that are aggregated.
- Change aggregation function for measurement columns. For example, columns that track life expectancy should use
MAX, and never use
Next, you can proceed to Visualize Search results as Answers.
Introduction to formulas
As you develop your expertise with developing and customizing worksheets, we recommend the following ThoughtSpot U courses: