Custom variables

Variables allow you to substitute values for specific properties of a metadata object and enable dynamic data propagation across Orgs in ThoughtSpot. Using variables, you can parameterize the properties of data connections, table references, and formulas per Org without changing the underlying data or creating duplicate objects. Variable values are assigned at runtime to ensure that customized configuration is available to users of different Orgs in ThoughtSpot.

Overview🔗

ThoughtSpot provides predefined system variables such as ts_username and ts_groups, which can be used in formulas for data security. Additionally, you can configure variables programmatically via REST APIs, and later use these variables for the following purposes:

To parameterize metadata object properties and publish artifacts across Orgs.
To implement Row Level Security (RLS) rules using formula variables and customize data for a specific Org, Model, or user. You can also pass variable attributes as security entitlements in JWT tokens for ABAC implementation.
To update properties of a Connection or Table directly in the TML.

Variable types🔗

ThoughtSpot supports creating the following types of variables via APIs:

TABLE_MAPPING
Can be used for table mapping properties such as schema name, database name, and table name.
CONNECTION_PROPERTY
Can be used for data connection properties such as accountName, warehouse, user, password, role, and so on.
CONNECTION_PROPERTY_PER_PRINCIPAL
Can be used for modifying connection properties for a specific principal object, such as a user or user group. This means you can set different values for connection properties, such as warehouse, role, user, and password, depending on the user or group accessing the connection. The CONNECTION_PROPERTY_PER_PRINCIPAL variable does not allow parameterizing core connection properties such as accountName, host, or port. These properties must be derived from the connection configuration and cannot be set per user or user group.

Note

This feature is disabled by default. To enable this option, contact ThoughtSpot Support.
FORMULA_VARIABLE
Formula variables allow you to parameterize logic in formulas and rules, and can be used in different contexts to dynamically populate values. Formula variables can be set for an Org, user, or Model and can be used in RLS rules with the ts_var function.

The Variable API allows administrators to define formula variables for the VARCHAR, BIGINT, INT, FLOAT, DOUBLE, BOOLEAN, DATE, DATE_TIME, and TIME data types.

APIs for Variable creation and management🔗

The following REST API endpoints are available for variable creation and management:

POST /api/rest/2.0/template/variables/create
Creates a variable.
POST /api/rest/2.0/template/variables/update
Allows updating the properties of a specific variable.
POST /api/rest/2.0/template/variables/update-values
Allows assigning values to one or several variables.
POST /api/rest/2.0/template/variables/search
Retrieves the variables available in your Org context.
POST /api/rest/2.0/template/variables/{identifier}/delete
Deletes the variable specified in the API request.

Note	Variable operations through the REST API are in beta. To enable this feature on your instance, contact ThoughtSpot Support.

Before you begin🔗

Ensure that you have administration privileges to create and manage variables. If creating variables within a specific Org context, ensure your ThoughtSpot account has Org administration permissions or the CAN_MANAGE_VARIABLES (Can manage variables) privilege to create and manage variables.
Ensure that you have edit access to the metadata objects to which you want to assign variables.

Create a variable🔗

To create a variable, send a POST request to the /api/rest/2.0/template/variables/create API endpoint, with the following parameters in the request body.

Request parameters🔗

In your POST request body, include the following parameters:

Parameter Description

type

String. Type of the variable. The API supports the following types of variables:

TABLE_MAPPING
To create variables for Table properties such as databaseName, schemaName, and tableName.
CONNECTION_PROPERTY
To define variables for connection properties. This variable allows editing connection properties such as accountName, warehouse, user, password, role, and so on.
CONNECTION_PROPERTY_PER_PRINCIPAL
To define variables for connection properties per user or user group. This variable allows modifying connection properties such as warehouse, role, user, and password. The CONNECTION_PROPERTY_PER_PRINCIPAL variable does not support modifying core connection properties such as accountName, host, or port. These properties must be derived from the connection configuration and cannot be set per user or user group.
FORMULA_VARIABLE
For formula variables. For formula variables, you must specify the data type.

name

String. Name of the variable. For example, schema_var. Note that the name must be unique across all Orgs within the instance.

is_sensitive Optional

Boolean. Indicates if the variable contains sensitive values such as passwords.

data_type

String. Variable data type. It’s a required parameter for formula variables. The data type defined during variable creation determines the format of the variable values. For example, if you specify the data_type as DATE, the value for this variable must be in epoch format.

Supported data types for formula variable values are:

VARCHAR
String. For example, if you are creating variables for different regions, set the data type as VARCHAR to allow string values such as EAST, WEST, and so on.
INT32
32-bit integer. If you are creating a formula variable to specify a threshold for the quantity of the products purchased, or for columns such as customer or product IDs, set the type as INT32 to allow numbers within the 32-bit range. For example, 100, 123456, 3005.
INT64
64-bit integer. For variable values that exceed the 32-bit range, set the data type as INT64. For example, transaction numbers, sales IDs, or discount values.
DOUBLE
In ThoughtSpot, the DOUBLE data type is used for columns that require floating-point arithmetic or need to store numbers such as latitude, longitude, percentages, monetary values, margin threshold, measurements, and so on.
DATE
Date in the epoch format. If you are creating a formula variable for a specific start date, set the data type as DATE and configure the date value in epoch format. For example, 1711933200 for 1 April 2024 (GMT).
DATE_TIME
Date with time stamp. If you are creating a formula variable for specific timestamp, specify the data type as DATE_TIME and assign a timestanp in epoch format as variable value. For example, 1711933200000 for 1 April 2024 01:00:00 (GMT).

Note
The data_type is required only for formula variables and is not supported for other variable types.

Example request🔗

The following example shows the request body for the table mapping variable type:

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/create'  \
  -H 'Accept: application/json' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
 --data-raw '{
  "type": "TABLE_MAPPING",
  "name": "schema_var",
  "is_sensitive": true,
}'

The following example shows the request body for the formula variable creation:

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/create' \
  -H 'Accept: application/json' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
  --data-raw '{
  "type": "FORMULA_VARIABLE",
  "name": "region_var",
  "is_sensitive": false,
  "data_type": "VARCHAR"
}'

The following example shows the request body for the connection property variable:

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/]api/rest/2.0/template/variables/create' \
  -H 'Accept: application/json' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
  --data-raw '{
  "type": "CONNECTION_PROPERTY",
  "name": "accountNameVar",
  "is_sensitive": false,
}'

Example response🔗

If the API request is successful, ThoughtSpot returns the variable details in the response.

{
  "id": "3242b54c-69bc-4ff0-97cf-f99a2216b616",
  "name": "schema_var",
  "variable_type": "TABLE_MAPPING",
  "sensitive": true,
  "values": []
}

{
  "id": "a033c855-dc49-4936-a82f-7fd29e57ab6b",
  "name": "country",
  "variable_type": "FORMULA_VARIABLE",
  "sensitive": false,
  "values": []
}

{
  "id": "a033c855-dc49-4936-a82f-7fd29e57ab6b",
  "name": "accountNameVar",
  "variable_type": "CONNECTION_PROPERTY",
  "sensitive": false,
  "values": []
}

Note the variable ID and name for variable edits. The API returns an empty array for values because the values are not assigned to the variable.

To assign values, use the POST /api/rest/2.0/template/variables/update-values API endpoint.
To include formula variables in a JSON Web Token (JWT) token for ABAC implementation, use the /api/rest/2.0/auth/token/custom API endpoint.

Update properties of a variable🔗

To update the properties of a variable, send a POST request to the api/rest/2.0/template/variables/{identifier}/update API endpoint with the following parameters in the request body. In your API request, specify the variable ID in the {identifier} path parameter.

Request parameters🔗

In your POST request body, include the following parameters:

Parameter Type Description

Parameter	Type	Description
`identifier`	Path	String. Name or ID of the variable to update.
`name`	Form parameter	String. Name of the variable.

identifier

Path

String. Name or ID of the variable to update.

name

Form parameter

String. Name of the variable.

Example request🔗

The following example shows the request body for updating the name of a variable:

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/3242b54c-69bc-4ff0-97cf-f99a2216b616/update'  \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
  --data-raw '{
  "name": "demo_schema_var"
}'

If the update operation is successful, the API returns a 204 response to indicate that the variable was updated successfully.

Define values and scope for variables🔗

To assign values to variables, use the /api/rest/2.0/template/variables/update-values API endpoint.

The API allows you to configure variable properties based on the type of variable you are trying to edit. For example, you can assign formula variables for a specific Org, Model, or user context. Similarly, for the CONNECTION_PROPERTY_PER_PRINCIPAL variable type, you can specify the principal type as user or user group and the ID of the principal object.

The API also allows you to edit, replace, or reset the values and scope assigned to a variable.

Note	In ABAC implementation, you can assign the variable values during token generation. Before configuring the token properties and variable values, verify the variables and their assignment status using the variable search API request.

Request parameters🔗

In your POST request body, you can include the following parameters:

Parameter Properties Description

Parameter	Properties	Description
`variable_assignment`	Includes parameters for setting values for a variable . This allows the same variable to have different values depending on which entity is being referenced.
`variable_identifier`	Array of strings. Specify the name or ID of the variable to which you want to assign values.
`variable_values`	Array of strings. Specify the values to assign. For example, `staging1`.
`operation`	Specify the update operation type. The following values are available: `ADD` Adds new values. Use this operation type to assign values to the variable. `REPLACE` Replaces the existing attributes with new values. `REMOVE` Removes the values assigned to the variable. For example, you can remove the values assigned to a variable configured for an Org. `RESET Resets all values at the variable level. For example, if a variable is assigned to multiple entities such as Org, user, or user group, the reset operation clears the values assigned to the variable for all entities.
`variable_value_scope`	Set the scope for variable values. These properties determine the entity level, such as Org, user, or user-group, for which the values will apply.
`org_identifier`	String ID or name of the Org. For primary Org, specify `primaryOrg` or Org 0. Applicable to `TABLE_MAPPING`, `FORMULA_VARIABLE`, and `CONNECTION_PROPERTY` variable types.
`principal_type` and `principal_identifier` Optional	String. Principal attributes such as user and user group. These attributes are applicable to the `CONNECTION_PROPERTY_PER_PRINCIPAL` variable type.
`model_identifier`	ID or name of the Model to which the variables configuration must be applied. Applicable to formula variables.
`priority` Optional	The priority assigned to this value. Applicable to the `CONNECTION_PROPERTY_PER_PRINCIPAL` variable type. Priority refers to the order of precedence when updating variable values for multiple entities in a single operation. If more than one entity matches the conditions during variable resolution,the system determines which entity’s value takes effect based on the value assigned to the `priority` parameter. For example, if a variable is configured for both the user and their group, the system determines which value to based on the assigned priority.

variable_assignment

Includes parameters for setting values for a variable . This allows the same variable to have different values depending on which entity is being referenced.

variable_identifier

Array of strings. Specify the name or ID of the variable to which you want to assign values.

variable_values

Array of strings. Specify the values to assign. For example, staging1.

operation

Specify the update operation type. The following values are available:

ADD
Adds new values. Use this operation type to assign values to the variable.
REPLACE
Replaces the existing attributes with new values.
REMOVE
Removes the values assigned to the variable. For example, you can remove the values assigned to a variable configured for an Org.
`RESET
Resets all values at the variable level. For example, if a variable is assigned to multiple entities such as Org, user, or user group, the reset operation clears the values assigned to the variable for all entities.

variable_value_scope

Set the scope for variable values. These properties determine the entity level, such as Org, user, or user-group, for which the values will apply.

org_identifier

String
ID or name of the Org. For primary Org, specify primaryOrg or Org 0. Applicable to TABLE_MAPPING, FORMULA_VARIABLE, and CONNECTION_PROPERTY variable types.

principal_type and principal_identifier
Optional

String. Principal attributes such as user and user group. These attributes are applicable to the CONNECTION_PROPERTY_PER_PRINCIPAL variable type.

model_identifier

ID or name of the Model to which the variables configuration must be applied. Applicable to formula variables.

priority
Optional

The priority assigned to this value. Applicable to the CONNECTION_PROPERTY_PER_PRINCIPAL variable type.
Priority refers to the order of precedence when updating variable values for multiple entities in a single operation. If more than one entity matches the conditions during variable resolution,the system determines which entity’s value takes effect based on the value assigned to the priority parameter. For example, if a variable is configured for both the user and their group, the system determines which value to based on the assigned priority.

Example request🔗

The following sections show the examples for assigning values to different types of variables and configuring the Org scope. Note that the variable values and Org identifiers are case-sensitive.

Table mapping variables🔗

The following example shows the request parameters to assign values to TABLE_MAPPING variables and set the variable scope to specific Orgs:

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/update-values'  \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
  --data-raw '{
  "variable_assignment": [
    {
      "variable_identifier": "schema_var",
      "variable_values": [
        "SALES_SCHEMA_A"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "db_var",
      "variable_values": [
        "SALES_DB_A"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "table_var",
      "variable_values": [
        "SALES_TABLE_A"
      ],
      "operation": "ADD"
    }
  ],
  "variable_value_scope": [
    {
      "org_identifier": "OrgA"
    }
  ]
}'

If the variable update operation is successful, you can use these variables in the Table TML or to parameterize Table properties.

Connection property variables🔗

The following example shows the request parameters to assign values to the CONNECTION_PROPERTY variables and set the variable scope to a specific Org:

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/update-values'  \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
  --data-raw '{
  "variable_assignment": [
    {
      "variable_identifier": "account_name_var",
      "variable_values": [
        "orgA_account"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "warehouse_var",
      "variable_values": [
        "WH_A"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "role_var",
      "variable_values": [
        "analyst"
      ],
      "operation": "ADD"
    },
  ],
  "variable_value_scope": [
    {
      "org_identifier": "OrgA"
    }
  ]
}'

If the variable update operation is successful, you can use these variables in the Connection TML or to parameterize connection properties.

Formula variables🔗

The following example shows the request parameters to assign values to formula variables and set their scope to a specific Org and data Model:

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/update-values'  \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
  --data-raw '{
  "variable_assignment": [
    {
      "variable_identifier": "region_var",
      "variable_values": [
        "West"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "state_var",
      "variable_values": [
        "california",
        "nevada"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "category_var",
      "variable_values": [
        "all"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "min_quantity_var",
      "variable_values": [
        "10"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "start_date_var",
      "variable_values": [
        "1711933200"
      ],
      "operation": "ADD"
    },
    {
      "variable_identifier": "end_date_var",
      "variable_values": [
        "1743469200"
      ],
      "operation": "ADD"
    },
  ],
  "variable_value_scope": [
    {
      "org_identifier": "Org_NA",
      "model_identifier": "80c969e7-3a36-48b7-923e-e2fb5c3fe88f",
      "principal_type": "USER",
      "principal_identifier": "tsuser"

    },
    {
      "org_identifier": "Org_Sales",
      "model_identifier": "cd252e5c-b552-49a8-821d-3eadaa049cca"
      "principal_type": "USER",
      "principal_identifier": "tsuser"
    }
  ]
}'

To avoid invalid data type errors, ensure that the values you assign to formula variables match the data type configured during variable creation.

Formula variables assigned to Primary Org can be used across all Orgs.

If the formula variable update is successful, you can use these variables in RLS rules and ABAC via RLS with variables.

Example response🔗

If the update operation is successful, the API returns a 204 response to indicate that the variable was updated successfully.

Get variables🔗

To get a list of variables or the details of a specific variable, send a POST request to the /api/rest/2.0/template/variables/search API endpoint.

To search for a variable, specify the following parameters in your API request:

variable details
Details such as variable type, ID, and name pattern. For name pattern search, specify the partial name of the variable. For wildcard search, use %.
variable value
Variable parameters such as Org ID, Model ID, and ID and type of Principal object.
output format for response content
Specify one of the following values for output format:
- METADATA_ONLY (default)
  Returns only the variable metadata
- METADATA_AND_VALUES
  Returns the variable metadata along with the values assigned to each variable.

Example request🔗

curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/search'  \
  -H 'Accept: application/json' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer {AUTH_TOKEN}' \
  --data-raw '{
  "record_offset": 0,
  "record_size": -10,
  "output_format": "METADATA",
  "variable_details": [
    {
      "type": "TABLE_MAPPING"
    }
  ]
}'

Example response🔗

If the request is successful, the API returns the variable data in the response:

[
  {
    "id": "f658cfd5-fa6f-4c33-a12f-ea46fa799666",
    "name": "Table_var",
    "variable_type": "TABLE_MAPPING",
    "sensitive": false,
    "values": null
  },
  {
    "id": "7f52d7b1-38f1-4127-ad5a-a2a7f58064df",
    "name": "Schema_var",
    "variable_type": "TABLE_MAPPING",
    "sensitive": false,
    "values": null
  }
]

Delete a variable🔗

To delete a variable, send a POST request to the /api/rest/2.0/template/variables/{identifier}/delete</a> API endpoint, with the variable ID in the path parameter.

Note that you can delete only one variable at a time.

The API does not allow deleting formula variables if they are used in Models, RLS rules, user properties, or ABAC tokens. If the variable is used by an object in ThoughtSpot, make sure to remove the variable assignments and references before deleting it from ThoughtSpot.

Example request🔗

curl -X POST \
--url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/180a9cd3-8605-445b-8b70-aa0bcef5dfb0/delete' \
-H 'Authorization: Bearer {AUTH_TOKEN}'

If the API request is successful, ThoughtSpot returns a 204 response code.

Additional resources🔗

For more information about using formula variables in RLS rules, see Formula variables.
For information about assigning security entitlements via ABAC tokens, see ABAC via RLS with formula variables.
For information about RLS rules, see RLS rules documentation.