Skip to main content
Gainsight Inc.

Preparation Details in Data Designer

Overview

This article will walk you through the functionalities in the Preparation tab, and also focuses on how to make use of them while preparing a Dataset for Explore.

In the preparation step, you can transform, merge, and union two different objects and create a Dataset. This document explains the functionalities in the Preparation tab, assuming that you have already created a minimum of two Datasets on two different objects. For more information on how to create a Dataset, refer to the Create Datasets in Data Designer article.

Following functionalities can be used while preparing a dataset:

To access the Preparation tab in Data Designer, navigate to Administration Data Designer, and open an existing design you wish to edit (or) click New Design to create a new one, enter the details and navigate to the Preparation tab. 

Edit Dataset

To edit a Dataset:

Click the pencil icon (or) click Options, and then click Edit to edit a dataset. You will be landed on the following page, where you can see Details & SummaryFields, and Filters tabs.

Details & Summary

You can see the following options in the Details & Summary tab:

  • Object Name: Displays the Original Name of the Object that specific Dataset is built on. 
  • Data Source Name: Displays the name of the Data Source.
  • Output Dataset Name: In the Output Dataset Name textbox, type In the Dataset Name, if you want to edit the Dataset Name. 
  • Description: Enter the Description of the Dataset as required.
  • Fields & Filters: You can view a list of all Fields (including Group By, if any) and Filters in the Dataset.

Fields

You can see the following options in the Fields tab:

  • Search Fields: In the Search Fields textbox, type in the Field Name you want to search for. Search operation happens on the fields available in the Dataset.
    Note: When users type in the Field name, the search functionality fetches the fields from the base object. To further extend your search on lookup objects, ensure that you expand the lookup object before you make a search.
  • Add Fields: Click Add Fields to select the required fields you want to add to the dataset, and then click Select.
  • Group By: You can select the required fields to Group By, to slice and dice the data in the dataset. Once you select a Field to Group By, all the other fields in the dataset will become aggregated.
  • Aggregation: You can select the required aggregation type from the Aggregation dropdown list.
  • Display Name: By default, the Field Name name appears in the Display Name textbox, if required you can modify it. Display Names are shown in the Datasets/reports. 
  • Settings:
  • For the numeric data type fields, you can set the decimal places. Click the Settings icon to enter the decimal places you want to set.
  • If the Salesforce Picklist/Multi-picklist fields you wish to set-up has already been added to the dataset:
  1. Click the Settings icon in the right corner.
  2. Enable the Convert to Picklist toggle button.
  3. Select the required dropdown list from the Map to MDA Dropdown List. 

Notes:

  • Before you map the Salesforce Picklist to Gainsight’s Dropdown list, ensure that you have already created an equivalent Dropdown list in Gainsight. For more information on how to create a Dropdown/Multi-select Dropdown list, refer to the Dropdown List and Multi Select Dropdown List article.
  • If you have not created an equivalent MDA Dropdown in Gainsight and mapped to the required Salesforce Picklist, the values are shown as null.

Once the setup is complete, users can now see the Picklists in Gainsight’s Filters and Global Filters.

  • Delete: Click the Delete icon to delete a field from the dataset.
  • Save: Click Save to save the changes made in the dataset.

Cascade Add

Cascade Add functionality allows you to add a field to a dataset, and thereafter this field automatically gets added to all of the next datasets (end tasks) in that design.

Notes:

  • The default aggregation of the Measure type field added using Cascade Add functionality is Sum. Once the field is added, you can change the aggregation based on your requirement.
  • The default aggregation of the Dimension type field added using Cascade Add functionality is Count. Once the field is added, you can change the aggregation based on your requirement.

Assume, you have a design as shown in the following image, and you might want to add a new field to the end task. Instead of adding the new field to each of the tasks separately from the starting, using Cascade Add functionality, you can add the new field to all the tasks at a time by adding that field to the parent dataset.

Cascade View Multiple sets.png

To use this functionality:

  1. Click Add Fields.
  2. Select the required fields.
  3. Click Select.
  4. Click Save. The Newly added fields window appears.
    Note: You can see the list of all newly added fields in this window. 
  5. Click Yes Add, to add the selected new fields to the end tasks.

Filters

To apply Filters:

  1. Navigate to the Filters tab.
  2. Click Add Filter.
  3. Select the field you want to filter on.
  4. Choose the operator and then input the data in the Value text box.
    Notes:
    • You can also Add more filters by clicking the + icon next to Value text box.
    • You can Delete a filter by clicking the x icon.
    • You can add advanced filters such as (A OR B) AND C, type in your desired expression in the Advanced Logic text box.
  5. Click Save.

Mappings

Data Designer auto imports the mapping information present in Gainsight Data Management. Mapping is used by other functionalities of Gainsight, such as Reports, Rules, JO, etc. to identify core fields such as Company Name and treat it specially. (Hyperlinks, Filters autosuggest, etc.)

Mapping is the mechanism that Gainsight applications use to identify special fields like Company Name, Relationship Name, User Name, CTA, Timeline Subject, etc. across Gainsight (Reporting, Rules, etc.).

IMPORTANT: To support mappings for a Name field, you must also add the corresponding ID field and carry it to the last step of Data Preparation. 

Refer to the following table to see how to make mapping work.

Object The field for which mapping is required Mandatory Field to carry forward until the last task 
Account Account Name Account ID
GS Relationship Name Record ID
Company  Name SFDC Account ID
User Name GSID

You can apply a Global Filter on Company Name on reports created on Company Object and Data Space from Data Designer.

Global Filters.gif

You can see hyperlinks in reports created on a Data Space. For example, you can see Company Name, Relationship Name, etc. records in reports as hyperlinks and can also see auto-suggest in Reporting filters.

AutoSuggest.gif

Transform

Transform allows you to apply Formula Fields and Case Expressions on the fields added to the dataset.

To apply Transformation on a dataset:

Click Options on the dataset you want to apply the transformation, and then click Transform. A new window with the dataset name ‘Transform’ appears, where you can see Details & Summary, Fields, and Filters tabs.

Details & Summary

Summary details in Transform are the same as Details & Summary details in Edit Dataset. For more information, refer to the Details & Summary section.

Fields

Once you apply Transformation on a dataset, you can now create Formula Fields and Case Fields upon the fields added to the transformed dataset.

Formula Fields and Case Fields in the Preparation Details of Data Designer work as the Formula Fields and Case Fields in Bionic Rules. For more information on how to create Formula Fields and Case Fields, refer to the Formula Fields and Case Fields article.

For more information on other options in the Transform > Fields tab work as the options in the Fields > Edit Dataset and Fields > Merge.

Add Formula Field

To create a Formula Field:

  1. Click Add Formula Field.
  2. Enter the Formula Field name in the Label textbox.
  3. Select the required Data Type (String or Date or Number).
    Notes:
  • Select the data type, based on the output field you want. For example, if you want to see the output as the number of days/weeks/months, then select the output data type as a number.
  • Functions and Fields may differ based on the selection of the data type.
  • For the Number data type, you can select the Expression Type as Function or Expression from the Expression Type dropdown list, based on your business requirements.
  1. Select the required Function of your choice, and then select/enter the Values and/or Fields as per your requirement.
  2. Click Save to save the formula field.
  3. Click Save to save the Transform dataset.

You can preview the records of the newly created formula fields, by clicking Preview on the Transform dataset.

Add Case Field

You can use the Case Expression formula field to categorize data, based on a specific set of requirements. You can use this Formula function to create an output Field. This output field has values for the records that match your specific set of requirements. 

Case Fields in the Preparation Details of Data Designer work as the Case Fields in Bionic Rules. For more information on how to create Case Fields, refer to the Case Fields article.

To create a Case Field: 

  1. Click Add Formula Field.
  2. Label: Enter the Formula Field name in the Label textbox.
  3. Select the required Data Type (Number/String/Boolean). The options present in Case Output depend on the Output data type.
    Note: If you select Number in the Data Type, select the number of decimal places up to which the output must be displayed, for floating point numbers.
  4. Case: Case consists of the Criteria(s). You can add multiple criteria by clicking +
  5. Criteria. The various fields present in the Criteria are:   
    1. Field: Select the field on which the Criteria must be applied. All the fields included in the Dataset are displayed here.
    2. Operator: Select an Operator to be applied in the field. The operators are displayed based on the field selected.
    3. Matching Criteria: Select the matching criteria which must be satisfied, for these criteria to be successful. You can either select:
      1. Value: Enter a value that must match the field value.
      2. Field: Select a field whose value must match the value of the field, selected in the first field.
  1. Advanced Logic: When you have multiple criteria, select the logical operator to be applied between those criteria(s). By Default, the AND logic is applied.
  2. Then: If all the criteria(s) are matched, the action specified in this field is executed. The options available in this field depend on the value selected in the Output Data Type field.
  3. Default: This field represents the default case. There is no criteria for this case. It only has action. If all the cases fail, this case is executed.
  4. Click Save to save the case expression.
  5. Click Save to Transform dataset.

Execution of the Case Expression in detail:

  1. Execution of the Case Expression begins with the evaluation of the first case on a record. If all the criteria, in this case, are satisfied by the record, the action associated with this case is executed. The execution of Case expression halts here for this record and none of the other cases are evaluated.
  2. However, if the first case is not satisfied, the system evaluates the second case on the same record, and so on. If none of the available cases are satisfied by the record, the default case is executed.

Filters

Filters in Transform work as Filters in Edit Dataset. For more details, refer to the Filters section.

Merge

You can merge datasets in two possible ways:

  1. Click Options on the dataset you want to merge, and then click Merge and select the required dataset. (OR)
  2. Drag and drop the first dataset onto the second dataset.

Once you merge the datasets, A new window with the dataset name ‘Merge’ appears, where you can see Details & Summary, Join, Fields, and Filters tabs.

Details & Summary

Summary details in Merge are the same as Details & Summary details in Edit Dataset. For more information, refer to the Details & Summary section.

Join

Basic JOIN clauses are used to combine rows from two or more tables, based on a common field between them. There are four types of Joins supported in Gainsight: Inner Join, Left Join, Right Join, and Outer Join. Each join type, when used with a Merge task in Data Designer, produces a slightly different data set. For more information, refer to the Join Types document.

Inner Join: This join retains common records from both datasets. 

Left Join: This join retains all the records from the left dataset.

Right Join: This join retains all the records from the right dataset.

Outer Join: This join Retain all records from both datasets.

To Join two datasets:

  1. Navigate to the Join tab.
  2. Select the required Join type, based on your business requirement.
  3. Select a field from each dataset to set the criteria. For instance, the Company Name in the first dataset is Name, and Company Name in the second dataset is Company Name.
  4. Click + to add multiple field mappings, if required. This helps filter the records based on your business requirements. 
  5. Click Save.

Fields 

You can see the following options in the Fields tab:

  • Search Fields: In the Search Fields textbox, type in the Field Name you want to search for. Search operation happens on the fields available in the Dataset.
  • Select All: Select/Deselect the checkbox to Add/Remove the fields from the merged dataset (or) You can also select/deselect the individual field checkboxes.  
  • Display Name: By default, the Field Name name appears in the Display Name textbox, if required you can modify it. Display Names are shown in the Datasets/reports. 
  • Save: Click Save to save the changes made in the merged dataset.

Filters

Filters in Merge work as Filters in Edit Dataset. For more details, refer to the Filters section.

Union

Union function in Data Designer combines the two datasets in a way that all the similar type fields automatically get unified into one dataset. If there are any other fields in the two datasets that are not similar, you can select and add them based on your requirement.

Business Use Case: For instance, you want to combine the data of two objects (with similar fields) that have the data from Europe and US region customers.

Notes:

  • Unified fields inherit the properties of Master fields such as Data Type, Decimal Points, etc.
  • Other than Unified fields, you can also add additional fields from the datasets. If there is no data in the columns, it is considered Null.
  • You can select either to remove or not to remove the duplicate fields in the output data.

To use Union function:

  1. Click Options on the dataset you want to apply union, and then click Union and select the required dataset. You will be landed on the Union tab.
  2. Select the Union Type as required. You can select from the following options:
    • Union: Select this to automatically remove any duplicates in the output data.
    • Union All: Select this to retain the duplicates in the output data.
  3. In the Map Fields to Merge section, select a field from each dataset to set the merge criteria. 
  4. Click + to add multiple field mappings, if required. This helps you filter the records based on your business requirements. 
  5. Navigate to the Fields tab to view the Unified Fields, select the Master Field (from the datasets), select/deselect the fields, and change the Display Name of the fields.
    Note: You can also select additional fields from the dataset if required.
  6. Click Save.

Pivot

In a dataset, the Pivot task transforms the data available in a single table column into multiple columns in one go. The Pivot task creates a column for every single data value in the table column. This enhances the data summarization process to get better insights into the data.

Pivot Function: Pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

Example Business Use Case: A Customer Success Director wants to find patterns in CTA Type by CSM and optimize CTA Types, and so on.

To use the Pivot functionality, navigate to the Administration > Data Designer > Open a Dataset > click Options > click Pivot.

For more information on how to Pivot on the CTA Type Name field, refer to the Pivot Task in Data Designer article.

Auto Arrange Datasets

With the Arrange Design option, you can automatically arrange the datasets in the Preparation page, with a single click. If there are numerous datasets in your design, you can make use of the options such as Zoom In and Zoom Out, and Pan as shown in the following image.

Preview Dataset

To Preview Dataset:

Click the eye icon (or) click options, and then click Preview Dataset. In Preview, you can only view the sample data and might not contain the entire data.

Maximize: You can also view the sample data in a full screen view by clicking the Maximize icon on the Preview tab. 

Refresh: Click the Refresh icon on the Preview tab to refresh the sample data, if you have edited the dataset.

Delete

To delete a Dataset:

If you wish to delete a dataset permanently from a Data Design, click Options, and then click the Delete icon.

Preview (Output Dataset)

To Preview Output Dataset:

Click Preview on the upper right corner of the Design page (or) Navigate to the output/last dataset in the design, click the eye icon (or) click Options, and then click Preview.

 

  • Was this article helpful?