Query generation and auto complete options

Contact ThoughtSpot Support to enable the following flags for common scenarios you may encounter.

Query generation and auto complete flags

Category Flag Details Example

Formulas

Formula case-sensitive quotes

The default setting is TRUE. When TRUE, the string case for formula definitions is saved as lower case. When FALSE, the case you enter in the formula definition is respected.

The ability to respect the case is important when leveraging pass-through functions. This is because functions defined in the underlying data platforms are often case-sensitive.

Example when true:

Entered formula =

if(city_name = “New York”) then ‘YES’ else ‘NO’

Saved formula =

if(city_name = “new york”) then ‘yes’ else ‘no’

Example when false:

Entered formula =

if(city_name = “New York”) then ‘YES’ else ‘NO’

Saved formula =

if(city_name = “New York”) then ‘YES’ else ‘NO’

Query Generation

Wrap all measures in a case statement to consider NULL as ZERO

The default setting is TRUE. When TRUE, all measures are wrapped in a case statement to consider NULL as ZERO. When FALSE, the underlying query is simplified.

Example when true:

SELECT CASE

  WHEN sum("ta_1"."Sales_Dollar_Amount") IS NOT NULL THEN sum("ta_1"."Sales_Dollar_Amount")

  ELSE 0

END "ca_1"

FROM "HO_RETAIL"."PUBLIC"."HO_RETAIL_SALES_FACT" "ta_1"

ORDER BY "ca_1" ASC NULLS LAST

Example when false:

SELECT sum("ta_1"."Sales_Dollar_Amount") "ca_1"

FROM "HO_RETAIL"."PUBLIC"."HO_RETAIL_SALES_FACT" "ta_1"

Query Generation

Enforce full outer join for chasm traps

This setting affects how non-shared filters are applied in a chasm trap model.

The default setting is FALSE:

The filter is only applied to one side of the chasm trap. Assume a filter is applied to the LEFT HAND side of the chasm trap.

When TRUE the query is a FULL OUTER JOIN:

When FALSE the resulting query is a LEFT OUTER JOIN. This is normally a much more performance query. Note where multiple filters are applied from both sides of the chasm trap, then ThoughtSpot will intelligently determine the join type.

For more details about when this setting is FALSE, see:

Row-level security

Include all secured tables

The default setting is TRUE. Assume a model where fact1 joins to dimension1 and an RLS rule is applied to fact.

When TRUE, if a column is selected from dimension1 then a join to fact1 is included. For example: the RLS rule is applied, and only the values from dimension1 which meet the rule on fact1 are returned.

When FALSE, if a column is selected from dimension1 then no RLS rule is applied. For example: all the values are returned as the results of the query.

For more information, see:

Pivot table

Fetch pivot summary from backend

The default setting is TRUE.

When FALSE, the pivot table calculations are calculated in the front end. These results are limited to a SUM of the numbers displayed in the pivot table. This is also limited to 100,000 rows of data.

When TRUE, the summaries are calculated as separate SQL queries. This supports the ability to define different aggregation rules. For example: Average, Min, Max.

Column-level security

Enable strict CLS

The default setting is FALSE.

To implement column-level security, this must be set to TRUE. This results in a stricter object security model. In short, all data objects must be shared with consumers rather than just worksheets.

Column-level security

UI controls

Hide one-to-one tables

The default setting is FALSE.

This setting is used in conjunction with the setting for Column Level Security. I.e. with Column Level Security the underlying tables must be shared with consumers.

When TRUE this ensures that the underlying tables are not available as data sources from the search data, source selection. For example, consumers are limited to select Worksheets or views.


Was this page helpful?