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.

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>
  - [alias] : <table_alias>
  - [fqn] : <GUID_of_table_name>
  - 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:
    - joinjoin:
      - <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
alias
An alternate name for the table
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
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
A GUID for the table name
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:
    - country: <country_name>
    - region_name: <region_name_in_UI>, which can be State, Postal Code, District, and so on.
See 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 is 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.