Add a Google Cloud SQL for PostgreSQL connection

To connect to Google Cloud SQL for PostgreSQL, follow these steps:

  1. Click the app switcher menu spotter app switcher and then click Spotter.

  2. On the left side of the screen, select Manage data > Add data source.

  3. On the Add a data source page, click Connect data.

    connection spotter set up new connection

  4. Create a name for your connection, a description (optional), then select the Google Cloud SQL for PostgreSQL connection type, and click Continue.

    Add a Google Cloud SQL for PostgreSQL connection

  5. Enter the connection details for your Google Cloud SQL for PostgreSQL data source.

    Enter connection details

    Refer to the Google Cloud SQL for PostgreSQL connection reference for more information on each of the specific attributes you must enter for your connection.

  6. (Optional) Configure proxy for your Google Cloud SQL for PostgreSQL connection by doing the following:

    1. Select the Advanced Config menu to reveal the Key and Value fields.

    2. For Key, enter httpProxy, and for Value, enter <proxyHost>:<proxyPort>.

      Example: 40.142.32.22:5050

  7. (Optional) Provide additional key-value pairs that you need to set up your connection to Google Cloud SQL for PostgreSQL or customize additional JDBC parameters supported by Google Cloud SQL for PostgreSQL, by doing the following:

    1. Select the Advanced Config menu to reveal the Key and Value fields.

    2. Enter your key and value information.

    3. To add more keys and values, select the plus sign (+), and enter them.

    Any key-value pairs that you enter must be defined in your Google Cloud SQL for PostgreSQL data source. Key-value pairs are case-sensitive.

  8. Select Continue.

  9. Select tables (on the left) and the columns from each table (on the right), and then select Create connection.

    Select tables and columns for your connection

    A message appears indicating the number of tables and columns that will be added to your connection.

  10. Select Create.

  11. On the Add a data source page, click Model your data.

    connection created

Model your data

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.

  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

    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.

  4. Click Save changes.

  5. In the Describe your model window, name and describe your model (if you haven’t already), and click Save.

  6. Click Exit data model.

  7. On the Add a data source page, click Coach Spotter.

    connection spotter set up new connection 3
    You can skip this step by clicking Skip. To coach Spotter later, go to Manage data > Coach Spotter, then click Add coaching. If you skip coaching, you can start chatting with your data now. For details, see Chat with your data.

Coach Spotter

You can teach Spotter how your organization uses your data by asking some questions, viewing the answers and refining them through your feedback. This helps Spotter to provide the kinds of answers you are looking for based on how you ask your questions.

To coach Spotter, do the following:

  1. Select your dataset, if it is not already selected.

  2. In the field to the right of the dataset name, enter a business question and click Next.

    spotter coach 1

    The AI-generated Answer appears.

    spotter coach 2

  3. Modify the answer, as needed, by adding columns using the list on the left, or by entering the column name next to the existing search tokens in the search field next to the name of the dataset.

  4. When you are satisfied with the answer, click Next.

    spotter coach 3

  5. Review the business terms generated from the search phrase you entered and their mapped search tokens.

  6. Approve or reject mapped search tokens by doing the following:

    • To approve, click the green check mark spotter approve mapping.

    • To reject, click the red X spotter reject mapping.

  7. To ask another question, click Ask another question.

  8. When you are finished, click Done.

  9. On the Add a data source page, click Chat.

    connection spotter set up new connection 5

Chat with your data

Now you are ready to use Spotter with your dataset.

  1. On the Spotter page, enter a question in the Ask a question field, and click the submit button submit chat button.

    chat box

  2. Edit the answers, if needed, by clicking the Edit link next to the search tokens of the Spotter answer.

    spotter answer

  3. Continue using the chat field to ask questions of your data.

  4. If you want to save your chat history, click Save above the search field. After you save your chat once, each additional question you ask, and the answer, are saved automatically.

    You can view any of your saved chats and continue asking questions by going to the Saved Chats list on the Spotter page, clicking the name of the dataset you chatted with, and then clicking the name of the saved chat.

Related information

Was this page helpful?
×