TML for Models

Use TML to modify a ThoughtSpot object in a flat-file format. Then, migrate the object to a different cluster, or restore it to the same cluster.

To work with TML files for Models, you can download these objects to flat files in .model.tml format, modify the files, and subsequently upload the files either to the same cluster, or to a different cluster. To learn how to export, change, and update Models, see Import and export TML files.

The syntax examples in this article contain every possible parameter in TML files for Models. Some of these parameters are not in these files by default. If you want to use them, you must add them yourself. For example, the fqn parameter is not present in any TML file by default, but you can add it to differentiate a table from another table with the same name.

As you work with TML files, keep in mind that changing elements of the TML file, such as the name of a column or table, may affect dependents. This is specifically true if you are editing TML files outside ThoughtSpot. When you change the name of a table in a TML file, and then import that file into ThoughtSpot, ThoughtSpot automatically updates that table name in any dependents, such as Answers that use the table as a data source. However, if you download multiple TML files from one ThoughtSpot cluster, then change the table name in TML, and upload all the files to a brand-new cluster, ThoughtSpot doesn’t know that the dependents should use that table. You must also change the table name in the dependents.

Data panel column groups

Analysts can now set definitions in a Model’s TML file to group columns together in the Data panel for Search Data, rather than having them grouped by default, as measures and attributes. You can also use TML properties to enable or disable alphabetical sorting of the groups.

To use the data panel column groups properties, you first add a property to each column properties definition, allocating each column to a group. This property, data_panel_column_groups, defines which group(s) a column belongs to. To assign a column to multiple groups, use a comma-separated list.

Use the following syntax:

- name: Transactiondate
    column_id: DIMDATE::TRANSACTIONDATE
    properties:
      column_type: ATTRIBUTE
      index_type: DONT_INDEX
    data_panel_column_groups: {Dates:}

To enable data panel column groups as a whole, add a section with the following syntax to your TML file:

column_groups:
  - type: DATA_PANEL
    properties:
      status: ENABLE
      default_sort: ENABLE
    column_group_info:
    - name: Dates
      include_ungrouped_columns: false
    - name: KPIs
      include_ungrouped_columns: false
    - name: Stores
      include_ungrouped_columns: false
    - name: ungrouped columns
      include_ungrouped_columns: true
    - name: Customers
      include_ungrouped_columns: false

In the above syntax, the status: Enable property enables the column groups feature. You can change the status to Disable to remove the column groups from the data panel without deleting the definition in the TML file.

The default_sort property keeps the order set in the TML file when disabled. When enabled, the column groups will be listed in alphabetical order in the Data panel.

Define the column group names and contents using the column_group_info property. The name property defines the group name. The include_ungrouped_columns property defines which group any columns you did not define with the data_panel_column_groups property are allocated to. There must be one column group with the include_ungrouped_columns property set to true.

Syntax of the Models TML file

The TML file for Models has a specific syntax.

See the TML parameters for Models for details about the keywords used in this example.

See Limitations of working with TML files for more information about actions you can’t perform using TML.

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

To reduce ambiguity, you may need to add the optional fqn parameter to your TML file when you reference source tables or connections. This is necessary if you have multiple connections or tables with the same name. If you do not add the fqn parameter, and the connection or table you reference does not have a unique name, the file import fails.

Refer to TML for joins for more information on the functionality and syntax of Model joins in TML.

If you edit the joins in the Model TML file, you are only editing the joins for that specific Model. You are not editing the joins at the table level. To modify table-level joins, you must edit the source table’s TML file.

guid: <model_guid>
model:
  name: <model_name>
  description:
    This is a multi-line description of the model
    Description line 2
  model_tables:
  - name: <table_name_1>
    id : <optional_table_id>
    fqn : <optional_GUID_of_table_name>
    joins:
    - with: Alias
      on: <join_expression_string>
      type: [RIGHT_OUTER | LEFT_OUTER | INNER | OUTER]
      cardinality: [MANY_TO_ONE | ONE_TO_ONE | ONE_TO_MANY]
  - name: <table_name_2>
    alias: Alias
  - name: <table_name_3>
    joins:
    - with: <table_name_1>
      referencing_join: <join_expression_string>
  formulas:
  - name: <formula_name_1>
    expr: <formula_definition_1>
    id: <optional_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>
  columns:
  - name: <column_name_1>
    description: <optional_column_description>
    column_id: <table_path>::<column_id_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 ]
      index_priority: <index_priority>
      synonyms :
      - value 1
      - value 2
      - value n
      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>
      is_hidden: [ true | false ]
      geo_config:
        latitude : true
          OR
        longitude : true
          OR
        country : true
          OR
        region_name:
        - country : <name_supported_country>
        - region_name : <region_name_in_UI>
          OR
        custom_file_guid: <custom_map_guid>
        geometryType: <custom_map_geometry_type>
      spotiq_preference: <spotiq_preference_string>
      data_panel_column_groups: {Name_1:}
    name: <column_name_2>
    description: <column_description>
    column_id: <table_path>::<column_id_2>
    ...
  properties:
    is_bypass_rls: [ true | false ]
    join_progressive: [ true | false ]
  lesson_plans
  - lesson_id: <lesson_id_number_1>
    lesson_plan_string <lesson_plan_string_1>
  - lesson_id: <lesson_id_number_2>
    lesson_plan_string <lesson_plan_string_2>
  - lesson_id: <lesson_id_number_n>
    lesson_plan_string <lesson_plan_string_n>
  parameters:
  - id: <parameter_1_id>
    name: <parameter_1_name>
    data_type: <parameter_1_data_type>
    default_value: <parameter_1_default_value>
    description: “parameter description”
    range_config:
      range_min: <parameter_1_range_minimum>
      range_max: <parameter_1_range_maximum>
      include_min: [true | false]
      include_max: [true | false]
  - id: <parameter_2_id>
    name: <parameter_2_name>
    data_type: <parameter_2_data_type>
    default_value: <parameter_1_default_value>
    list_config:
      list_choice:
      - value: <list_value_1>
        display_name: <value_1_display_name>
      - value: <list_value_2>
        display_name: <value_2_display_name>
      - value: <list_value_n>
        display_name: <value_n_display_name>
        linked_parameters: <table_name>::<parameter_name_in_ThoughtSpot>
  - id: <parameter_n_id>
    name: <parameter_n_name>
    data_type: <parameter_n_data_type>
    default_value: <parameter_n_default_value>
    range_config:
      range_min: <parameter_n_range_minimum>
      range_max: <parameter_n_range_maximum>
      include_min: [true | false]
      include_max: [true | false]
 column_groups:
 - type: DATA_PANEL
   properties:
     status: ENABLE
     default_sort: ENABLE
    column_group_info:
    - name: Name_1
      include_ungrouped_columns: false
    - name: Name_2
      include_ungrouped_columns: false
    - name: Name_3
      include_ungrouped_columns: false
    - name: Name_4
      include_ungrouped_columns: false
    - name: Name_5
      include_ungrouped_columns: true

TML parameters for Models

These parameters and their definitions are specific to Models; this list doesn’t contain every parameter in every TML file. Similarly, the parameter definitions may vary from object to object, since the parameters may have different roles in different objects.

a - g	i - m	n - w
aggregation alias calendar column column_id column_group_info column_groups column_type columns currency_type custom_file_guid data_panel_column_groups data_type default_value default_sort description destination display_name expr filters format_pattern formulas fqn geo_config geometryType guid	id include_max include_min include_ungrouped_columns index_priority index_type is_additive is_attribution_dimension is_bypass_rls is_hidden is_one_to_one join join_path join_progressive joins joins_with lesson_id lesson_plan_string lesson_plans linked_parameters list_choice list_config model	name on oper parameters properties range_config range_max range_min referencing_join source spotiq_preference status synonyms table table_paths tables type value values

a - g

i - m

n - w

data_panel_column_groups

include_ungrouped_columns

index_priority

index_type

is_additive

is_attribution_dimension

aggregation: The default aggregation of the column.

Aggregation options depend on the data type.

Possible values are SUM, COUNT, AVERAGE, MAX, MIN, COUNT_DISTINCT, NONE, STD_DEVIATION, and VARIANCE

Default: SUM.

alias: Aliases can be set for tables that are being used in the model. Aliases are especially useful for role playing dimension scenarios where an alias can be assigned to the same table that is being used in different roles.

calendar: Specifies the calendar used by a date column.

Can be the Gregorian calendar (default), a fiscal calendar, or any custom calendar.

See Custom calendar overview.

column: The id of the column(s) being filtered on.

column_id: The id of the column.

column_id is in the following format: column_id: <id>::<name>. id comes from Worksheet.table_path, and name comes from Table.column. For example: column_id: Customer_Dimension::Customer_Name.

column_group_info: Enter the name of the column group. Column group names must be unique.

column_groups: Create column groups to sort columns in the Search Data panel.

column_type: The type of data the column represents.

Possible values: MEASURE or ATTRIBUTE. The default is: MEASURE.

columns: The list of columns available from the data sources in the Model.

Each Model is identified by name, description, column_id, and properties.

currency_type

The source of the currency type.

One of:

is_browser : true: infers 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 and Set currency type for more information.

custom_file_guid: The GUID for a custom map uploaded by an administrator.

data_panel_column_groups: Define this property in each column to mark which column group(s) the column should be allocated to. When marking a column for multiple column groups, separate the group names using a comma.

data_type: The data type of the Parameter.

default_value: The default value for a Parameter in a Model. Specified when creating a Parameter.

default_sort: When enabled, the column groups will be sorted alphabetically by group name in the data panel. When disabled, the groups will be sorted in the data panel in the order defined in the TML.

description: The text that describes an object: a Model, a column, or a join.

destination: The name of the destination table or view for a join.

display_name: The name or value that displays in the Parameter dialog for an accepted value, if a display name was set when creating a Parameter. For example, if the Parameter accepts true and false, the display names might be yes and no.

expr: The definition of the formula.

filters: Contains specifications for Model 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

formulas: The list of formulas in the Model.

Each formula is identified by name, the expr (expression), and an optional id attribute.

fqn: The table or connection’s GUID. You can find this string of letters and numbers at the end of the URL for that table or connection.

For example, in https://<company>.thoughtspot.com/#/data/tables/34226aaa-4bcf-4d6b-9045-24cb1e9437cb, the GUID is 34226aaa-4bcf-4d6b-9045-24cb1e9437cb.

Use this optional parameter to reduce ambiguity and identify a specific table, if you have multiple tables with the same name. When exporting a TML file, you have the option to Export FQNs of referenced objects, which ensures that the TML files you export contain FQNs for the underlying tables and connections. If you do not add the fqn parameter, and the connection or table you reference does not have a unique name, the file import fails.

geo_config

Specifies the geographic information of a column. See Add a geographical data setting

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>; can be State, Postal Code, District, and so on.

custom_file_guid: <custom map GUID>

for columns that use a custom map. Must be followed by geometryType

geometryType: <custom_map_geometry_type>

for columns that use a custom map. Must be preceded by custom_file_guid. Accepts the following values: POINT, LINE_STRING, LINEAR_RING, POLYGON, MULTI_POINT, MULTI_LINE_STRING, MULTI_POLYGON, GEOMETRY_COLLECTION, CIRCLE

geometryType: Specifies the geometry type of the TopoJSON file. Required for geographical configuration of Model or table columns that use a custom map. Accepts the following values: POINT, LINE_STRING, LINEAR_RING, POLYGON, MULTI_POINT, MULTI_LINE_STRING, MULTI_POLYGON, GEOMETRY_COLLECTION, CIRCLE

guid: The GUID for the Model. You can find this string of letters and numbers at the end of the URL for an object.

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

include_max: Indicates if the maximum value in a Parameter range is a valid value. For example, if the range is 0-10, and include_max is false, the value 10 is not valid for that Parameter.

Valid values are true and false.

include_min: Indicates if the minimum value in a Parameter range is a valid value. For example, if the range is 0-10, and include_min is false, the value 0 is not valid for that Parameter.

Valid values are true and false.

include_ungrouped_columns: Set as true/false for each column group. When defined as true, the defined column group includes all columns not sorted into the other column groups.

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 type for the 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 Model supports bypass of Row-level security (RLS) rules.

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 or define a synonym.

is_one_to_one: Specifies the cardinality of the join. This is an optional parameter.

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 Model.

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: Contains a list of joins between the tables and views.

If you edit the joins in the Model TML file, you are only editing the joins for that specific Model. You are not editing the joins at the table level. To modify table-level joins, you must edit the source table’s TML file.

Each join is identified by name, and the additional attributes of source, destination, type, and is_one_to_one.

joins_with: Contains a list of external joins for which this Model is the source.
Each join is identified by name and optional description, and the additional attributes of destination, type, on, and is_one_to_one.

lesson_id: The numeric ID of the Search Assist Coach lesson created for the Model. The first lesson shown to users has an ID of 0, the second lesson has an ID of 1, and so on.

lesson_plan_string: The content of the Search Assist Coach lesson created for the Model. For example, "What were [Lo Tax] by [Lo Shipmode] in [Lo Commitdate].'2019' ?".

lesson_plans: Contains a list of Search Assist Coach lessons created for the Model.

linked_parameters

Links the Model parameter to the underlying table parameter. Takes the syntax "table_name::table_parameter_name". The table parameter name should be the name of the parameter in ThoughtSpot.

Only applicable to tables imported from SAP Hana.

list_choice: Contains the list of accepted values for a Parameter, if a list was configured when creating a Parameter.

list_config: Contains the list of accepted values for a Parameter, if a list was configured when creating a Parameter.

model: Top-level container for all object definitions within the Model.

name: The name of an object. Applies to models, joins, formula, connection, destination, and so on.

on: The join expression: the relationship definition, or the keys that your objects are joined on. For example, [sale::Sale_Last Name] = [employee::Employee_Last Name] AND [sale::Sale_First Name] = [employee::Employee_First Name].

To alter a relationship definition, edit this parameter.

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

parameters: Container for the Parameters in a Model.

properties: The list of properties of a column, a Model, or the properties of the output for a formula within a Model.

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, geo_config, and spotiq_preference.

Models themselves can have the following properties that affect query generation: is_bypass_rls, and join_progressive.

range_config: Contains the minimum and maximum values for the range of values allowed for a Parameter, if a range is specified when creating a Parameter.

range_max: The maximum value for the range of values allowed for a Parameter, if a range is specified when creating a Parameter.

range_min: The minimum value for the range of values allowed for a Parameter, if a range is specified when creating a Parameter.

referencing_join: The Global join that is being referenced in the model.

Global joins are joins created between tables and can be used across multiple worksheets.

It is not possible to edit or override a Global join in the model but any Global join can be replaced by a Local join in the model. referencing_joins field in the model does not require details of the join itself.

source: Name of the source table or view for a join.

spotiq_preference: Specifies whether to include a column in SpotIQ analysis. Specify EXCLUDE, or this property defaults to include the column in SpotIQ Analysis.

Refer to Set columns to exclude from SpotIQ analyses.

status: Use the status property to define whether to enable or disable column groups. When disabled, columns return to the default sort in the data panel, while the column properties in the TML file are unaffected.

synonyms: Alternate names for the column, used in search.

See Hide a column or define a synonym.

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 Model.

Each table is identified by name.

type: The join type. This is an optional parameter.

Possible values: LEFT_OUTER for left outer join, RIGHT_OUTER for right outer join, INNER for inner join, OUTER for full outer join.

Default: INNER

value: An accepted value for a Parameter, if a list was configured when creating a Parameter.

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

Limitations of working with TML files

There are certain limitations to the changes you can apply by editing Models through TML.

Formulas and columns can either have a new name, or a new expression. You can’t change both, unless migrating or updating the Model two times.
It isn’t possible to reverse the join direction in the TML script.
You can’t modify joins at the table level from the Model, view, or Answer TML file. You can only override the joins for that specific Model, view, or Answer. To modify table-level joins, you must edit the source table’s TML file.
When deleting columns, you only delete ThoughtSpot’s record of the column. You don’t delete the column in your external database.

Visit this page for information on Creating Models.

Was this page helpful?Give us feedback!

ThoughtSpot is the Modern Analytics Cloud company. Our Live Analytics services deliver personalized, actionable insights at the point of impact for every user, at every level.

Product

By Role

By Department

Solutions

About Us

Connect

(800) 508-7008