Manage suggestion indexing

When a user searches in the Search bar, ThoughtSpot supplies the user with suggestions for column names and their column values. The COLUMN NAME and any SYNONYMS appear in Search suggestions. For tables and views, a column’s INDEX TYPE controls whether and how ThoughtSpot suggests column values. For Worksheets, the SUGGEST VALUES IN SEARCH section for a column determines whether ThoughtSpot suggests column values.

Additionally, ThoughtSpot uses a column’s INDEX PRIORITY value to determine where to rank a column’s name and values in the search suggestions. These values impact the dynamically calculated usage based ranking (UBR).

To configure certain aspects of ThoughtSpot’s indexing behavior, refer to Managing search and SpotIQ settings.

Example of Search suggestion behavior

The following example illustrates how searching for promotion_last_name t causes the system to suggest several ways of completing the t in the search:

Typing 't' results in several search suggestions

The system is suggesting the synonym type (callout A) for a column in the Promotion table. It is also suggesting a value of theil (callout B) for the Promotion_Last Name column. If you look in the Data  Tables page, you can see that there is a type synonym for the Promotion_Type column which is using default indexing.

Type

Managing search suggestions through INDEX TYPE and INDEX PRIORITY is important. Properly configured suggestions can decrease "noise" in the suggestion list. Increasing the visibility of important columns is helpful for new or intermittent ThoughtSpot users.

Understand the default indexing behavior for tables and views

ThoughtSpot has a system default INDEX TYPE behavior for search suggestions for table and view values. This system default is configured on your cluster and applies to all tables. You can override this default behavior on a per-column basis.

Table and View search indexing

The system behavior when the INDEX TYPE is DEFAULT is as follows:

  • With two exceptions, the system indexes all columns using their COLUMN NAME value. The exceptions are columns with COLUMN TYPE of MEASURE and columns with DATA TYPE of DATE.

  • Columns that contain data values with large amount of free-form strings, that is, a length is greater than 50 words, are indexed as PREFIX_ONLY by default.

    If a column has a very large free text value, ThoughtSpot recommends you keep DEFAULT or set DONT_INDEX. Other settings indexing on these values may generate confusing suggestions.

  • Short strings (like a firstname column) are indexed using PREFIX_AND_WORD_SUBSTRING by default, which indexes prefixes for each individual word in the column value.

  • If a column has a cardinality - the number of unique column values - greater than 100,000, and the index type is DEFAULT, ThoughtSpot does not index it. You can index columns with a greater cardinality than 100,000, but it may impact performance. To index a column with a cardinality greater than 100,000, set the index type to PREFIX_ONLY. If you must index a column with high cardinality using a type other than PREFIX_ONLY, contact ThoughtSpot Support.

If a column’s INDEX TYPE is not DEFAULT, and the column’s cardinality is greater than 10 million, ThoughtSpot does not index the column.

High cardinality and performance

A column’s cardinality can impact indexing. If you have a column with a very high cardinality and a very high number of rows, indexing these values can impact your ThoughtSpot performance. ThoughtSpot considers columns with over 100,000 unique values to have high cardinality. ThoughtSpot Support recommends you turn off indexing of primary key columns on extremely large tables (unlimited number of rows but < 100,000 unique values) in your cluster.

High cardinality is relative to other considerations. In some cases, columns with fewer than 100,000 rows but with columns containing long strings can cause performance problems with memory. If you have concerns or questions, your ThoughtSpot contact can help you determine appropriate cardinality thresholds for your ThoughtSpot installation.

To index a column with a cardinality greater than 100,000, set the index type to PREFIX_ONLY, PREFIX_AND_WORD_SUBSTRING, or PREFIX_AND_SUBSTRING. If you must change the cardinality limit for DEFAULT columns, contact ThoughtSpot Support.

Configure your own cluster defaults

If you need to, you can work with ThoughtSpot Support to configure new cluster defaults.

Additionally, you can configure certain aspects of ThoughtSpot’s indexing behavior from the Admin Console. Refer to Managing search and SpotIQ settings.

Understand the indexing behavior for Worksheets

For Worksheets, you can only choose whether to index the column. You cannot choose an index type. When viewing a Worksheet, toggle the SUGGEST VALUES IN SEARCH option on or off.

Worksheet search indexing
Indexing only works at the table level. If the underlying table column’s INDEX TYPE is set to 'DONT INDEX', the respective Worksheet column won’t be indexed and be able to 'suggest values'.

Override the system default on a column

You can change a column’s INDEX TYPE in the Data  Tables  Columns page or in the Index value in the modeling file.

The values you can set for INDEX TYPE are:

Index type Description

DEFAULT

The default behavior applies to all ATTRIBUTE columns that are not DATE types. PREFIX_AND_SUBSTRING for short values and PREFIX_ONLY for long values and free-form text.

DONT_INDEX

Prevents indexing on the column values. The column doesn’t appear in search suggestions.

PREFIX_AND_SUBSTRING

Allows full indexing such that prefix and sub-string search both work for the column values.

PREFIX_AND_WORD_SUBSTRING

Allows indexing such that only prefix search works for each word of a multi-word string, for the column values.

PREFIX_ONLY

Allows indexing such that only prefix search works for the column values.

Consider a column in which there are four values ThoughtSpot, Thought, Spot and Thought Spot. If you search for sp, depending on the setting for indexing, the column value search result suggestions will vary:

Index field value Search bar suggestions

DEFAULT

ThoughtSpot, Spot and Thought Spot

DONT_INDEX

No suggestions.

PREFIX_AND_SUBSTRING

ThoughtSpot, Spot and Thought Spot

PREFIX_ONLY

Spot

PREFIX_AND_WORD_SUBSTRING

Spot and Thought Spot

To change a value in the application UI:

  1. Open a Worksheet or table from the Data page.

  2. Find the column whose index type you want to modify.

  3. Set its INDEX TYPE.

  4. Save your changes.

If you are using the model file, locate the Index cell, and enter the INDEX TYPE you want to use.

Change a column’s suggestion priority

A column’s INDEX PRIORITY determines the order or rank in which it and its values appear in the search suggestions dropdown list. It also affects which columns ThoughtSpot prioritizes when generating AI Answers.

High priority columns appear higher in the search suggestions

By default, the INDEX PRIORITY value is set to 1 for all columns. You can push a column up in the order (increase the rank) by increasing its INDEX PRIORITY value. A higher value (like 2) will cause the corresponding column and its values to appear higher up in the search dropdown list than columns with lower value (like 1).

Set the index priority

You should only use numbers between 1-10 in the INDEX PRIORITY field. Use a value between 8-10 for important columns to improve their search ranking. Use 1-3 for low priority columns.

To change a value in the application UI:

  1. Open a Worksheet or table from the Data page.

  2. Find the column whose index type you want to modify.

  3. Change the INDEX PRIORITY to a number between 1 and 10.

  4. Save your changes.

If you are using the model file, locate the Index cell, and enter the priority you want to use.

Related information

Was this page helpful?
×