Worksheet TSL specification

ThoughtSpot worksheet specification may be exported as a TSL file, modified, and imported into the same or different cluster.

To work with Scriptable Worksheets in ThoughtSpot, you can download Worksheets to a flat file in TSL, ThoughtSpot’s Scripting Language, modify it, and subsequently upload this file either to the same cluster, or to a different cluster. To learn how to export, change, and update Worksheets, see Migrate or restore Worksheets.

Starting with ThoughtSpot release 6.3, ThoughtSpot Scripting Language (TSL) is called ThoughtSpot Modeling Language (TML). Files from version 6.3 and later have a .tml extension. However, you can import files from 6.2.1 and earlier, with a .tsl extension, to 6.3 and later environments with no issue.

Syntax of the Worksheet TSL file

The TSL file for Scriptable Worksheets has a specific syntax.

See the Parameters section for details about the keywords used in this example.

You may not see each of these parameters in your own TSL file, depending on whether each variable is explicitly defined. For example, if you do not have any filters on your Worksheet, the filters parameter does not appear. You can add that variable to the TSL file to specify filters for your Worksheet.

worksheet :
  name : <worksheet_name>
  description : |-
    This is a multi-line description of the worksheet
    Description line 2
  tables :
  - name : <table_name_1>
  - id : <optional_table_id_1>
  - fqn : <optional_GUID_of_table_name_1>
  - name : <table_name_2>
  - name : <table_name_3>
  joins :
  - name : <join_name_1>
    source : <source_table_name>
    destination : <destination_table_name>
    type : [RIGHT_OUTER | LEFT_OUTER | INNER | OUTER]
    is_one_to_one : [ false | true ]
  - ...
  table_paths :
  - id : <table_path_name_1>
    table : <table_name_1>
    join_path :
    - join :
      - <join_name_1>
  - id : <table_path_name_2>
    table : <table_name_2>
    join_path :
    - {}
  - id : <table_path_name_3>
    table : <table_name_3>
    join_path :
    - join :
      - <join_name_1>
    - join :
      - <join_name_2>
      - <join_name_3>
    - join :
      - <join_name_4>
      - <join_name_5>
      - <join_name_6>
  formulas :
  - name : <formula_name_1>
    expr : <formula_definition_1>
    [id]: <unique_identifier>
  - name : <formula_name_2>
    expr : <formula_definition_2>
  - name : <formula_name_3>
    expr : <formula_definition_3>
  filters :
  - column : <filtered_column_name_1>
    oper : <filter_operator>
    values : <filtered_values>
    - value 1
    - value 2
    - value n
  - column : <filtered_column_name_2>
  worksheet_columns :
  - name : <column_name_1>
    description : <column_description>
    formula_id : <formula_name_1>
    properties :
      column_type : [ MEASURE | ATTRIBUTE ]
      aggregation : [ SUM | COUNT | AVERAGE | MAX | MIN |
                     COUNT_DISTINCT | NONE | STD_DEVIATION | VARIANCE]
      index_type : [ DONT_INDEX | DEFAULT | PREFIX_ONLY |
                    PREFIX_AND_SUBSTRING | PREFIX_AND_WORD_SUBSTRING ]
      is_hidden : [ true | false ]
 	        index_priority : <index_priority>
      synonyms :
             <synonym_1>
             <synonym_2>
      is_attribution_dimension : [true | false]
      is_additive : [ true | false ]
      calendar : [ default | calendar_name ]
      format_pattern : <format_pattern_string>
      currency_type :
        is_browser : true
          OR
        column : <column_name>
          OR
        iso_code : <valid_ISO_code>
    geo_config :
        latitude : true
          OR
        longitude : true
          OR
        country : true
          OR
        region_name:
        - country : <name_supported_country>
        - region_name : <region_name_in_UI>
    name : <column_name_2>
    description : <column_description>
    formula_id : <formula_name_2>
    ...
  properties :
    is_bypass_rls : [ true | false ]
    join_progressive : [ true | false ]

Parameters of the Worksheet TSL file

aggregation

The default aggregation of the worksheet column + Aggregation options depend on the data type.

Possible values:

SUM, COUNT, AVERAGE, MAX, MIN, COUNT_DISTINCT, NONE, STD_DEVIATION, and VARIANCE

Default:

SUM

calendar

Specifies the calendar used by a date column
Can be the Gregorian calendar (default), a fiscal calendar, or any custom calendar. See Set up a custom calendar

column

The name of the column being filtered on.

column_type

The type of data the column represents

Possible values:

MEASURE or ATTRIBUTE

Default:

MEASURE

currency_type

The source of currency type
One of:

  • is_browser : true infer the currency data from the locale of your browser

  • column : <column_name> extracts the currency information from a specified column

  • iso_code : <valid_ISO_code> applies currency based on the ISO code; see ISO 4217 Currency Codes
    See Set currency type

description

The text that describes an object: a worksheet, a worksheet_column, and so on.

destination

Name of destination table or view of the join

expr

The definition of the formula

filters

Contains specifications for Worksheet filters.

format_pattern

The format pattern string that controls the display of a number, date, or currency column
See Set number, date, and currency formats

formula_id

The id of the formula that defines the worksheet column

formulas

The list of formulas in the worksheet
Each formula is identified by name, the expr (expression), and an optional id attribute.

fqn

The table’s GUID. You can find this string of letters and numbers at the end of the URL for that table. For example, in https://<company>.thoughtspot.com/#/data/tables/34226aaa-4bcf-4d6b-9045-24cb1e9437cb, the GUID is 34226aaa-4bcf-4d6b-9045-24cb1e9437cb.

geo_config

Specifies the geographic information of a column
One of:

  • latitue : true for columns that specify the latitude

  • longitude : true for columns that specify the longitude

  • country : true for columns that specify the country

  • region_name for specifying a region in a country
    Uses two paired parameters:

id

Specifies the id of an object, such as table_paths, formula.

index_priority

A value (1-10) that determines where to rank a column’s name and values in the search suggestions
ThoughtSpot prioritizes columns with higher values. See Change a column’s suggestion priority.

index_type

The indexing option of the worksheet column

Possible values:

DONT_INDEX, DEFAULT (see Understand the default indexing behavior), PREFIX_ONLY, PREFIX_AND_SUBSTRING, and PREFIX_AND_WORD_SUBSTRING

Default:

DEFAULT
See Index Type Values

is_additive

Controls extended aggregate options for attribute columns
For attribute columns that have a numeric data type (FLOAT, DOUBLE, or INTEGER) or a date data type (DATE, DATETIME, TIMESTAMP, or TIME)

Possible values:

true or false

Default:

true
See Making an ATTRIBUTE column ADDITIVE

is_attribution_dimension

Controls if the column is an attribution dimension
Used in managing chasm traps.

Possible values:

true by default, false to designate a column as not producing meaningful attributions across a chasm trap

Default:

true
See Change the attribution dimension

is_bypass_rls

Specifies if the worksheet supports bypass of Row-level security (RLS)

Possible values:

true or false

Default:

false
See Privileges that allow users to set, or be exempt from, RLS

is_hidden

The visibility of the column

Possible values:

true to hide the column, false not to hide the column

Default:

false See Hide a column

is_one_to_one

Specifies the cardinality of the join

Possible values:

true, false

Default:

false

join

Specific join, used in defining higher-level objects, such as table paths
Defined as name within joins definition

join_path

Specification of a composite join as a list of distinct join attributes
These join attributes list relevant joins, previously defined in the joins, by name.

Default:

{}

join_progressive

Specifies when to apply joins on a worksheet

Possible values:

true when joins are applied only for tables whose columns are included in the search, and false for all possible joins

Default:

true
See How the worksheet join rule works

joins

List of joins between tables and views, used by the worksheet
Each join is identified by name, and the additional attributes of source, destination, type, and is_one_to_one.

name

The name of an object. Applies to worksheet, table,join, formula, and so on.

oper

The operator of the Worksheet filter. Accepted operators are "in", "not in", "between", =<, !=, <=, >=, >, or <.

properties

The list of properties of the worksheet column + Each column can have the following properties, depending on its definition: column_type, aggregation, index_type, is_hidden, index_priority, synonyms, is_attribution_dimension, is_additive, calendar, format_pattern, currency_type, and geo_config.

source

Name of source table or view of the join

synonyms

Alternate names for the column, used in search
See Create synonyms for a column

table

Specific table, used in defining higher-level objects, such as table paths
Defined as name within tables definition

table_paths

The list of table paths
Each table path is identified by the id, and additional attributes of table and join_path.

tables

List of tables used by the worksheet + Each table is identified by name.

type

Join type

Possible values:

LEFT_OUTER for left outer join, RIGHT_OUTER for right outer join, INNER for inner join, OUTER for full outer join

Default:

RIGHT_OUTER

values

The values being filtered (excluded or included) in a Worksheet.

worksheet

Top-level container for all object definitions within the worksheet

worksheet_columns

The list of columns in the worksheet
Each worksheet is identified by name, description, formula_id, and properties.

Limitations of working with Worksheet TSL files

There are certain limitations to the changes you can apply by editing a Worksheet through TSL.

  • Formulas and columns can either have a new name, or a new expression. You cannot change both, unless migrating or updating the worksheet two times.

  • It is not possible to reverse the join direction in the TSL script.

  • You cannot create Scriptable representations of R- or Python-powered visualizations.


Related information