To work with Thoughtspot target instance transform in data flow, follow below steps.

Note:

Selecting the Thoughtspot Data Object to be added as target instance transform

Option I: Selecting the Thoughtspot Data Object to be added as target instance transform.

In the data flow canvas move to Data Flow pane and navigate to Target menu. Here, you can either select or drag and drop the Thoughtspot  target to canvas.

If the Thoughtspot  data objects are available then the Create New Thoughtspot Target window prompts you to select a Thoughtspot  data object else 'No Data Object exists in Thoughtspot ' message is displayed.

  • To search for a specific project, enter the keyword in the Project search bar, and the drop down displays the search result list. Select the required project and click Ok.
  • Select the Thoughtspot  data object from local object else if there is any Global project that user has access to, then the Project drop-down will list them. Select the Global project from the list if the data object must be chosen from there.

Note: The Data object to be used as target should already be available in Diyotta for it to appear in the list. For creating or importing the data object, refer Creating new data object.

After selection, click OK and the data flow canvas displays the selected Thoughtspot data object as target instance.

Link the source instance transform to Thoughtspot target instance transform.

Option II: You can also create a target, by using Create As Target option.

In the canvas, select the source data object and click ellipses, a drop down lists available options, from the list select Create As Target.

Likewise, you can also select the option Create As Target from Actions menu.

In both the scenarios, Create As Target window appears and prompts you to provide the details for Thoughtspot target instance transform and upon confirmation the target instance transform appears in the canvas. For more information, refer Creating a target from transform in Data flow.

After selection, click OK and the data flow canvas displays the selected Thoughtspot  data object as target instance.

Configuring Thoughtspot target instance transform

In the General Tab edit the basic details associated with the Thoughtspot target instance transform.

Note:  In general you can only view Source/Target properties and Script tabs but in target instance by default you can also view Data tab used for debugging purpose.

1. The Name field auto populates the target instance name which is prefixed with TGT and the name is editable.

2. In the Description text-box, provide a description and is optional.

3. The Data Object Name displays the associated data object name and upon clicking the associated name you are navigated to respective data object canvas.

  • To change the associated data object, click Change, then the Change Target data Object window appears and lists the Thoughtspot data objects. From the list, click on required data object and click OK.

  • To search for a specific project, enter the keyword in the Project search bar, and the drop down displays the search result list. Select the required project and click Ok.
  • If there is any Global project that user has access to, then the Project drop-down will list them. Select the Global project from the list if the data objects need to be chosen from there.

4. The Data Point field displays the associated data point with the source instance transform and when you click the associated name you are navigated to respective data point canvas.

5. The Transient Object field displays whether its a transient object or not.

6. The Database Type field displays the type of database to which the target is associated 

7. The Schema Name field displays target associated schema name.

Configuring properties

The properties tab displays the target level options that can be applied to load the data.

1. Truncate Before Load:

Select this option if the database table associated with the target instance transform needs to be truncated before loading the data. 

2. Create Target:

Select this option to create table before loading data in the database based on the attributes in the associated data object. The table is created only if it does not exist in the database.

3. Pre-SQL: .

The Pre-SQL option can be used to run a SQL on the target system before the load process begins. The SQL will execute on the data point that is associated with the target instance transform.

  • To add Pre-SQL query, click on the Expression Editor icon beside the field.
  • The Expression Editor window opens and you can add the SQL here.
  • After adding the Pre-SQL, to verify that there are no syntax errors, click Validate. Upon successful validation success message appears. Once done, click OK.

4. Post-SQL: 

The Post-SQL option can be used to run a SQL on the target system after the load process completes. The SQL will execute on the data point that is associated with the target instance transform.

  • To add Post-SQL query, click on the Expression Editor icon beside the field.
  • The Expression Editor window opens and you can add the SQL here.

  • After adding the Post-SQL, to verify that there are no syntax errors, click Validate. Upon successful validation success message appears. Once done, click OK.

The expression defined in Pre-SQL and Post-SQL can include Thoughtspot database functions, Parameters, Functions, UDF, and Reusable expressions.

  • The list of functions can be seen by selecting Functions from the drop down. The functions that can be used in the SQL is not limited by the list shown. All the Thoughtspot database functions can be used in the SQL.
  • The list of parameters can be seen by selecting Parameters from the drop-down. It will show those that can be used in data flow - Data Flow Parameter, Data Flow SQL Parameter, Project Parameter, Pseudo columns, and System Parameter can be used in the SQL For more information, refer Working with Data Flow ParametersWorking with Data Flow SQL ParametersWorking with Project ParametersDiyotta System Parameters.
  • List of expressions and UDFs can be seen under corresponding header in drop-down.

Viewing the Attributes in target Instance Data Flow

You can view the attributes that are in the associated data object. The attributes listed are not editable. The attributes can be edited only in the associated data object.

Mapping the Attributes in target Instance Data Flow

In the grid, the attributes in the associated data object are displayed under Target Attribute and you can either manually or automatically map these to attributes from the connected transforms. The mapped attributes will be displayed under the Source Attribute.

Manual mapping: You can manually map the attributes to the target attribute by selecting it from the source Attributes drop down against the specific target attribute. The list will display all the attributes that can be mapped.

Auto mapping: You can automatically map all the target attributes by selecting the Auto Map icon.

All the target attributes are mapped by matching the name of the target attribute with the attributes in the linked transforms. If there are multiple transforms prior to the target then the attribute with the same name and which is closest to the target instance will be mapped. If there are no attributes that match the name of the target attribute then the source attribute corresponding to it will remain blank. This will have to be mapped manually.

To modify or remove a mapping, you can select a specific attribute and click Unlink target attribute icon.

You can also unmap all the associated source attributes by clicking Unlink all target Attributes icon.

Configuring the load type

You can specify what type of operation needs to be performed when loading data into the target table. Choose the operation from the Load Type drop-down.

Note: 

  • The load type drop-down will list only Insert operation if the target instance transform is external to the data flow.
  • For native target instance transforms, the load type will display Insert, Update, Upsert, Delete, and SCD operations.

Load Type: Insert

To insert the data into the target table based on the mapped attributes, select the load type as Insert. During execution the Insert Into statement is generated against the target instance transform. If a target attribute is not mapped in the attribute mapper then, in the insert statement the attribute is excluded and database NULL value will be loaded into it.

You can view the generated SQL in Script tab.

Editing Extract Properties in Target Instance

The extract properties are displayed only when the Thoughtspot target instance is external. The Extract properties are displayed for the native data point used to extract data flow.

To change the extract properties for the Thoughtspot target, click Extract Properties tab.

By default these properties are set to recommended/default values from data point and the values can be overridden here. To work with extract properties of specific data flow native data point type, refer Working with Data Point and view the extract properties for the specific data point.

Note: To revert the changes to the default values, click Reset All to Default.

Editing Load Properties in Thoughtspot target Instance

The load properties are displayed only when the target instance is external and the data flow is not generic. The load properties are displayed for the target data point used to create target data object.

To change the load properties for the Thoughtspot target, click Load Properties tab.

By default these properties are set to recommended/default values from data point and the values can be overridden here. To work with load properties, refer Editing Load Properties in Thoughtspot Data Point.

Note: To revert the changes to the default values, click Reset All to Default.

Data tab: 

Data section displays the output of the logic implemented in the data flow. When Data is clicked then, the transformations starting from source instance transform is executed and the data that would be loaded in the target instance transform selected is displayed. The data is not loaded in the target.