Search data error reference
This reference identifies the error codes that can appear when you use Search data in ThoughtSpot, including the cause and suggested resolution.
Search data error codes
11028
- Description
-
Data samples are not supported for models/Worksheets with chasm traps. Use Search to get sample data.
- Details
-
When a user selects the Data Samples tab from a Worksheet or model, ThoughtSpot attempts to display 50 rows of data. If a chasm trap query plan is detected, then a warning message is displayed to the user.
- Cause
-
Schemas that may have multiple fact tables are joined through shared dimensional tables. This is known as a chasm trap. ThoughtSpot supports chasm traps for analysis. However, these are not supported for Worksheet data samples. Refer to the Chasm trap article for further details.
- Resolution
-
Leverage Search Data to review columns and their values.
11035
- Description
-
Unsupported query: unique count and a rank-related function coming from the same fact table.
- Details
-
last_value
is a ThoughtSpot formula function introduced in 9.12.0.cl . This function is designed to provide analysts the capabilities to create formulas for semi-additive measures. An example could be inventory balances.For example: "How much stock does Acme Corp maintain for each Region?"
A secondary metric could be Average Stock on Hand. A common definition for this is: balance of stock divided by the unique count of locations.
In ThoughtSpot this formula could be defined as:
last_value(sum(balance),query_groups(),{transaction date}) / unique count(location id)
An alternative example could be a user asking the question as separate measures. For example: "Show me the balance and unique count of locations for each region".
In this scenario the search would return two measure columns: Balance & Unique Count Locations.
- Cause
-
In the example defined, the last value aggregate column is ‘balance’ and the unique count column is ‘location id’. Error 11035 is returned when the unique count column, ‘location id’, is derived from either the same fact table as balance or as a dimensional table that is joined to the fact table.
- Resolution
-
Unique count can be combined with last_value across a chasm trap query. Therefore pending resolution for this limitation an analyst can leverage group_aggregate to force a chasm trap query.
For example: The group_aggregate definition ensures a chasm trap is generated and the inclusion of query_groups()+{} ensures that this query plan is not optimized.
group_aggregate(unique count(location id), query_groups()+{}, query_filters())
11036
- Description
-
Unsupported query: nested aggregate function, for example, fx = max(avg(c1)).
- Details
-
This error is displayed in the formula editor when an aggregate formula is defined directly within another aggregate formula, for example,
average(sum(sales))
. Note that this error may be displayed when an aggregate is wrapped around a column, such asaverage(total sales)
. In this scenario, the worksheet column would be defined with an aggregate formula, such astotal sales = sum(sales)
. - Cause
-
can’t use an aggregate function within an aggregate function.
- Resolution
-
To resolve this, group functions should be used to force the query generation to create a CTE query plan. That is, assume the business requirement is to return the average daily sales for each product. the formula would be defined as the following:
average daily sales = average(group_aggregate(sum(sales),query_groups()+{date(transactionDate)}, query_filters()))
Where the following logic is applied
group_aggregate(sum(sales),query_groups()+{date(transactionDate)}, query_filters()) → calculate the daily sum of sales
.average() → calculate the average of the daily sales for the level of detail of the search
.The resulting query would be:
[Product] [Average Daily Sales]
.
11037
- Description
-
Formulas that mix aggregates and non-aggregate expressions are not supported.
- Details
-
This error is displayed in the formula editor when a formula incorrectly includes a combination of an aggregate expression and a non-aggregate expression (non-group-by).
The following is an example of a formula that results in an error. Where
-
region='apac' is the non-aggregate
-
sum(sales) is an aggregate
-
fx = if(region='apac' then sum(sales) else 0
.
- Cause
-
Historically these formulas could be written in ThoughtSpot. However, these often resulted in SQL generation queries. In the example above, if the region column is not included in the search, then a group by sql query would be returned. This is because the:
-
aggregate function is calculated across all rows of data : sum(sales).
-
non-aggregate is a calculation at the row level : region='apac'.
-
- Resolution
-
The formula must be re-written with the following as examples:
Option 1: wrap the non-aggregate column in an aggregate function.
fx = if(max(region)='apac' then sum(sales) else 0
Option 2: re-write the function as an aggregate if formula.
fx = sum_if(region='apac', sales)