Create Models

Introducing Models

Models are a new, easier way of creating what previously were called Worksheets. You can create Models from the Data tab, just like Worksheets. You can still create and use Worksheets, but ThoughtSpot plans to deprecate and remove Worksheets in a future release. All worksheets can be migrated to Models in a future release.

Models are created in the new Model editor which provides a more visual and intuitive experience for creating data models.

Benefits of the Model editor include:

  • Building a data model visually, by dragging and dropping tables and columns

  • Creating joins between tables is faster and easier

  • Using the tabbed interface allows you to quickly switch between table and column views, as well as formulas, filters, parameters, and settings.

Limitations

You can create either Models or Worksheets, however you cannot do the following:

  • Switch an individual Worksheet to a Model

  • Switch all Worksheets to Models in a cluster

    When the Model feature is available to all customers by default in a future release, you will have the option to request ThoughtSpot to migrate all the Worksheets on your cluster to Models.

Creating a Model

To create a data model:

  1. Select Data on the top navigation bar.

  2. Select the + Create new button in the side navigation bar.

  3. Select Model.

    Choose how to model your data

  4. Choose how you want to model your data, using one of the following options:

    1. To create your own model, select Build your own with cloud data and click Next.

      1. To create a new connection, refer to About connections

      2. To use an existing connection, select it from the list, and select Connect.

    2. To build a model from tables originally created from dbt models, select dbt and click Next.

      1. To create a new dbt connection, select Add dbt connection and refer to step 5 of Integrating with dbt.

      2. To use an existing connection, select it from the list, and select Connect.

    3. To import the TML for a model, select TML and click Next.

      1. Upload your tml files. For more information, refer to TML for Worksheets.

        The information at this link also applies to Models.

  5. Build your data model.

Early Access

If you are not able to see the Models feature, contact your ThoughtSpot administrator to enable it. For more about Early Access features, see ThoughtSpot Cloud release life cycle.

Building your Model

After creating your Model, you need to add the sources that contain the data. A source is another name for a table. You do that using the Model editor. If you chose an existing model, you can make changes to your model.

Create a Model

The sources you choose are typically related to one another by foreign keys.

The first step to building your Model, is to add tables.

Adding tables

To add tables to your Model, do the following:

  1. Drag tables from the list on the left, and drop them into the right pane.

    Model populated with tables

Suggested joins

If the tables you added to your Model were joined previously, a join suggestion appears.

Suggested join between tables

You can choose whether to keep, modify or remove the suggested join.

  • Click the join icon join icon, then select one of the following options:

    1. To keep the join, click Accept Join.

    2. To modify the join, click Edit Join.

      1. Make your changes and click Save. For more information, see step 2 of Adding joins.

    3. To remove the join, click Clear Join.

Adding joins

  1. To add a new join between tables, do one of the following:

    • Select the More menu Model more menu in the upper right of a table, and select Add Join.

    • Hover over the table until you see a + appear on the edge of the table, then click the + and drag to the table you want to join.

  2. In the Create Join window for Table 2, select the table you want to join to the first table (Table 1).

  3. For Column from Table 1, select the column you want to join to another column in Table 2.

  4. For Column from Table 2, select the column in Table 2 which you want to join to the selected column in Table 1.

  5. For Join Type, select a join type. Options include:

    • INNER inner join

    • FULL OUTER outer join

    • LEFT OUTER left outer join

    • RIGHT OUTER right outer join

  6. For Cardinality, select the cardinality. Options include:

    • Many:1

    • 1:Many

    • 1:1

  7. Click Save.

    The join is created.

    Join created

  8. Add additional tables and joins, as needed.

  9. (Recommended) Name your Model by clicking Add name and entering a name.

  10. (Optional) Add a description of your Model by clicking Add description and entering a description.

  11. When you are finished adding tables and joins to your Model, the next step is to add columns.

Global and local joins

Models support both global and local joins. It’s helpful to understand the difference.

  • Global joins are created between two tables in a connection and they are available to any Model created in that connection. If you see a join suggestion when you create a model, it means that join is a global join.

  • Local joins are created in a specific Model and they are only available in that Model.

Adding columns

To add columns to your Model, do the following:

  1. Click the Columns tab.

    Model columns tab

    Table names are highlighted in blue, and their associated columns appear underneath them.

  2. Add columns by doing either of the following:

    • To add all the columns from a table, drag the table name from the list on the left and drop it into the right pane.

    • To add an individual column from a table, drag the column name from the list on the left and drop it into the right pane.

    Columns added to Model

  3. (Optional) Edit column properties, using either of the following methods:

    1. For an individual column:

      1. Click the column property you want to edit, and make your changes.

    2. For multiple columns:

      1. Click the checkbox next to the columns you want to edit.

      2. Click the Edit menu, and select the property you want to edit.

      3. In the window that opens, select the option you want and click Update.

  4. To save the columns in your Model, select Save changes.

    You are prompted to edit the title and description of your Model.

  5. Edit the title and description if needed.

  6. Click Save.

    Your model is saved, and you have the option to add formulas, filters, parameters, and adjust settings.

  7. If you don’t want to add those now, select Exit data model.

Sorting and filtering columns

You have the option to sort any column in ascending or descending order, as well as apply a filter to a column by selecting possible values of the column.

Sort columns

To sort a column, do the following:

  1. Click a column heading

  2. From the pop-up menu, select either Sort ascending or Sort descending.

To filter a column, do the following:

  1. Click a column heading

  2. From the pop-up menu, select Filter.

  3. In the Edit filter window, select the values you want to filter by and click Apply.

    A label appears above the table indicating the values used to filter the column.

    Filtered values label

    You can delete the filter by hovering over the label and clicking the (X).

    In the Edit filter window, you also have the following options to use when editing or adding a filter:

    • Select all

    • Clear

    • Search

    • Show selected

    Sort and filter states are not saved when you save your Model, however they do persist when you switch between tabs of the Model editor.

Adding formulas

To add formulas to your Model, do the following:

  1. Click the Formulas tab.

  2. Click Add Formula.

  3. Create a name for your formula.

  4. Enter a formula, using the formula assistant (at right) if needed.

    Enter a formula

    With the formula assistant, you can see a list of formula operators with examples.

    If you want to change what your formula returns, select the Advanced settings button Formula advanced settings.

    Depending on your formula, you may be able to change:

    • Data type: Determines the type of data that the formula generates. For example, use date for an add_days formula.

    • Measure or attribute: Determines if the output of the formula is a measure or an attribute. For example, choose attribute for a formula that generates age groups, and choose measure for a formula that generates profit.

    • Aggregation: Determines the default aggregation of the formula output. For example, choose min to see minimum profit.

  5. Click Save.

  6. Click Save changes.

Adding filters

To add filters to your Model, do the following:

  1. Click the Filters tab.

  2. Click Add filter.

  3. Choose the column you want to filter on.

    Filter a column

  4. Select the values to include in your Answer.

    Select values

  5. If you want to exclude values, select Exclude and choose values to exclude.

    You can also include values in bulk, by clicking + Add values in bulk, and adding values separated by a comma (,) or a semicolon (;) or by putting them on separate lines.

  6. Click Submit.

  7. Click Save changes.

ThoughtSpot recommends to not use more than 1,000 filter values in a Model.

Adding parameters

To add parameters to your Model, do the following:

  1. Click the Parameters tab.

  2. Click Add Parameter.

    Add a parameter

  3. In the Parameter name text box, enter a name for your Parameter. This name should be unique for the object, and easy for users to understand.

  4. Optionally, enter a description for the Parameter in the Add a description text box. Use this field to add additional information users might need before using this Parameter in their analysis. It is a best practice to use this field to specify the formulas this Parameter is used in, in addition to any other information you add.

  5. Under Data settings, specify the data type the Parameter must be: Integer, Double, String, Boolean, or Date. For more information about data types, refer to Data types.

  6. For integer, double, or date data types, specify the allowed values: Any, List, or Range. For the string data type, specify either Any or List.

    Any

    Allows any value within the data type’s constraints.
    List

    Allows values only in the list you create. Enter a value in the Enter value…​ text box, and optionally enter what the value should display as in the Display as…​ text box. To add more values, select + Add row. To delete values, select the x to the right of the row the value is in.

    Create a list of allowed values
    Range

    Allows values only within the range you specify. In the Minimum value and Maximum value text boxes, specify the values the range begins and ends with.

    Create a range of allowed values

  7. For the boolean data type, optionally set custom names for true and false; for example, 0 and 1.

  8. Set the default value for your Parameter. In the default value text box, enter the value this Parameter should default to. For the boolean data type, the Default value option is a dropdown. true and false are the only options.

  9. Select Save.

Settings

You can set properties for your model, including join rules and security.

To set properties for your Model, do the following:

  1. Click the Settings tab.

  2. For Data model join rule choose one of the following:

    • Apply joins progressively (recommended for most cases)

    • Apply all joins

  3. For Security you have the option to disable row level security for your data model.

    Model settings

  4. Click Save changes.

Understanding progressive joins

Often, a Model includes several dimension tables and a fact table. With progressive joins, if your search only includes terms from the fact table, you can see all of the rows that satisfy your search. But as you add terms from dimension tables, the total number of rows shown may be reduced, as the joins to each dimension table are applied. It works like this:

  • If you choose Apply joins progressively (recommended for most cases), joins are only applied for tables whose columns are included in the search.

  • If you choose Apply all joins, all possible joins are applied, regardless of which tables are included in the search.

When using Apply joins progressively, the number of rows in a search using the Model depends on which tables are part of the search. The Model contains the results of a defined query in the form of a table. So if a particular dimension table is left out of the search, its joins are not applied.

Editing a Model

If you created a Model, or you have edit permissions on it, you can make changes such as adding sources and columns, adding or editing formulas, changing relationships, and changing column names. You can also rename a Model or change its description.

To edit a Model:

  1. Select Data on the top navigation bar.

  2. Select the name of the Model you want to edit.

  3. Select the Edit Worksheet button in the upper-right of the screen.

    Even though the button currently says Edit Worksheet, it allows you to edit a Model if you selected one in the previous step.

  4. Make your changes to the Model.

    Edit tables

    Using the More menu More menu in the upper right of a table, you can edit a table in the following ways:

    • Add Join

    • Rename table

    • Duplicate table

    • Remove table

    • Show Join Recommendation

    Edit columns

    You can edit columns in the following ways:

    • Add columns by dragging and dropping them

    • Edit fields in columns

    • Delete any column by checking it and selecting Delete

    Edit formulas, filters, parameters, and settings

    You can edit in the following ways:

    • Delete formulas, filters, and settings

    • Modify properties

  5. Select Save changes.

Making a copy of a Model

To make a copy of a Model:

  1. Select Data on the top navigation bar.

  2. Select the name of the Model you want to make a copy of.

  3. Open the More menu more menu icon and select Make a copy.

    The make a copy dialog appears.

  4. Change the name and description, as needed, and select Save.

    When you navigate back to the Worksheet page, your new Model name appears in the list.

    Even though the page currently only uses the term Worksheet, it does show both Worksheets and Models. Any models will appear in the list of Worksheets.

Role-playing dimensions

A role-playing dimension is when a single physical dimension is referenced multiple times in a fact table, with each reference linking to a logically distinct role for the dimension. ThoughtSpot supports role-playing dimensions for Models. For example, you may have a fact table joined to a dimension table more than once. This is useful for cases such as when you have a sales fact table and an employee dimension table, where the sales table may record the employee ID who created, updated, and owned a record. In this case, you would want to join these three columns to the employee dimension table on employee ID.

Implementing role-playing dimensions with Models

To implement role-playing dimensions with Models, do the following in the Model editor:

  1. Go to the Tables tab.

  2. Select the More menu More menu in the upper right of the dimension table that you want to use in a different role.

  3. Select Duplicate Table.

  4. In the Duplicate Table Name window that opens, assign an alias (since 2 tables cannot have the same name) by entering a new name (or alias) for that table.

  5. Click Save.

  6. Join this logical copy of the dimension table to the fact table with the join condition corresponding to the new role.

  7. Go to the Columns tab.

  8. Add columns from the new role-playing dimension.

    It will appear as a new table (with the alias assigned in step 4) on the left panel in the Columns tab of the model. You can tell it’s a role-playing dimension because the original table name appears in parentheses () next to the new name.

Copies/aliases of tables that are on the "many" side of a join are not supported in Models.
Was this page helpful?
×