Liveboard schedule gating conditions in practice
When you schedule a Liveboard job, you can add a gating condition that triggers the Liveboard email. If the condition evaluates to true at the scheduled time, ThoughtSpot emails the Liveboard to the specified recipients. If it evaluates to false, ThoughtSpot does not send the Liveboard.
This article walks you through an example scenario in which you use a gating condition to determine whether to email the Liveboard, sending an alert to specified users.
Gating condition functionality
A gating condition is a statement that returns a single boolean value (true or false). For example, sum (revenue) > 100 is a valid condition, but is_weekend (commit_date) is not, since it returns a result per row of data. Use any data source (table, worksheet, or view) that you have access to for the gating condition.
However, note that for reports sent to ThoughtSpot users, ThoughtSpot executes the query as that specific user so that it respects row-level security (RLS) and ensures data security. If you schedule the Liveboard for 3 users, and 1 user does not have access to the data source in the gating condition, the gating condition fails and ThoughtSpot does not send the Liveboard to that 1 user. It does send the Liveboard to the 2 users who do have data access. For reports sent to people who are not ThoughtSpot users, ThoughtSpot executes the query as the author of the Liveboard schedule.
The gating condition will still work if you are using passthrough security for your external data warehouse connection. The gating condition formula and any tables you use in it do not need to be related to the Liveboard the gating condition is for. You can use any valid formula in your statement. ThoughtSpot checks the formula syntax, but does not validate if the formula returns a valid single boolean.
At the scheduled time, ThoughtSpot executes the gating condition query. If the condition evaluates to true, ThoughtSpot processes the Liveboard.
For a list of valid formulas, see Formula function reference.
Gating condition example
You can leverage gating conditions to send alerts. For example, you begin to notice invalid data appearing in your recent data loads, even though they seem to be successful.
You can validate the data you load by setting several business rules. For example:
-
Sales validation: The sum of sales today versus the sum of sales yesterday should not vary by more than 20%. A higher amount of variance is unlikely, and probably the result of invalid data.
-
Product validation: The unique product count should be greater than 4000 but less than 5500. Your company’s product count is within that range; any more or less is the result of invalid data.
-
Customer validation: The unique customer count should be greater than 20000. Your company has more than 20000 customers; any fewer is the result of invalid data.
You can create a view with these business rules, and then create a Liveboard schedule with a gating condition that references these rules. Then, the Liveboard schedule notifies the specified recipients if any of these rules is not met, suggesting a problem with invalid data.
Create a view
After you determine your business rules, create a view that includes these rules. This makes the gating condition formula much simpler and ensures that the formula returns a single result, rather than multiple rows of data.
This answer, saved as a view, contains multiple formulas that help determine if a sales, product, or customer exception occurred. The sales exception formula uses the delta sales % formula, which in turn uses the max sales and max sales -1 formulas. Because of the complexity of these formulas, it is easier to create a view that you can reference for this gating condition (or multiple gating conditions), rather than trying to create one complicated formula defining the 3 exceptions in the gating condition itself.
In the preceding image, the sales and customer exceptions are true, which should result in an alert after you create the Liveboard schedule with the gating condition.
Create a Liveboard to schedule
Now you have a view where you define the three exceptions that suggest your data loads have invalid data. Next, you must create the Liveboard ThoughtSpot sends out at the scheduled time if the gating condition is met.
This Liveboard contains optional answers to provide information about which exceptions failed. The Liveboard can contain any information relevant to the alert.
Note that the Liveboard title, ThoughtSpot Validation Alert - Data Validation is the automatic subject of the Liveboard schedule email.
Create the Liveboard schedule
After you create your view and Liveboard, follow the directions in Schedule a Liveboard job to create the Liveboard schedule.
The Repeats value, which determines how frequently ThoughtSpot emails the Liveboard, is set to send the email every Monday at 9 A.M by default. You may want to set this value to a more or less frequent cadence, depending on your business needs.
Note that the description in the Liveboard schedule is the automatic body of the Liveboard schedule email.
Define the gating condition
The last step of creating the Liveboard schedule is to define the gating condition. If this condition resolves to true, ThoughtSpot sends the Liveboard schedule email at the specified time, to the specified recipients.
In this scenario, you want ThoughtSpot to send the alert if any of the three exceptions is true. If they are all false, there is no need to send the alert, since that means that your data loads are successful and the data involved is valid.
Because the view already defines the 3 exceptions, the formula is simple:
If (sales exception or product exception or customer exception) then true else false
In the gating condition formula editor, it looks like this:
The operators (if…then…else) are in blue, and the columns, defined by the view, are in purple.
This formula returns a single boolean value. The gating condition must return a single boolean value. It must not return a result per row of data.
Save the gating condition. To create the Liveboard schedule, select Schedule.
Now, at the specified time(s) and day(s), ThoughtSpot determines if any of the 3 business exceptions that suggest invalid data are met, and if any of them are true, ThoughtSpot sends the alert to the specified recipients, who can fix the issue.
