Worksheet YAML specification

ThoughtSpot Worksheet specification may be exported as a YAML 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 yaml format, 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 Scriptable Worksheets are written in 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 .yaml or .tsl extension, to 6.3 and later environments with no issue.

Syntax of the Worksheet YAML file

The YAML file for Scriptable Worksheets has a specific syntax.

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

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>
  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 Worksheet YAML 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_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

The name of destination table or View of the join.

expr

The definition of the formula.

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:

  • latitude : 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:

    • country: <country_name>

    • region_name: <region_name_in_UI>, which can be State, Postal Code, District, and so on.

      See xref:model-geo-data.adoc[Add a geographical data setting].
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.

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

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 YAML files

There are certain limitations to the changes you can apply be editing a Worksheet through YAML.

  • 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 YAML script.

  • It is not possible to include Worksheet filters in the YAML script.

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