Transform Basics

After you have created or selected your dataset, the Transformer page is opened, where you begin your wrangling tasks on a sample of the dataset. Through this interface, you build your transformation recipe and see the results in real-time as applied to the sample. When you are satisfied with what you see, you can execute a job against the entire dataset.

Goal

Your data transformation is complete when you have done the following:

• Cleansed your data of invalid, missing, or inaccurate values
• Modified your dataset to constrain its values to meet the target schema
• Enhanced your dataset as needed with data from other datasets
• Exported the results from your dataset and recipe for use in downstream systems

Recommended Methods for Building Recipes

Designer Cloud Enterprise Edition supports the following methods for building recipes. These methods are listed in order of ease of use:

1. Select something. When you make a selection in the Transformer page, you are prompted with a set of suggestions for steps that you can take on the selection or patterns matching the selection. You can select columns or one or more values within columns.

   Tip: The easiest method for building recipes is to select items in the application. Over time, the application learns from your selections and prompts you with suggestions based on your previous use. For more information, see Overview of Predictive Transformation.

2. Toolbar and column menus: In the Transformer page, you can access pre-configured transformations through the Transformer toolbar or through the context menus for individual columns. 

   Tip: Use the toolbar for global transformations across your dataset and the column menu for transformations on an individual column.

   1. When a toolbar item is selected, the Transform Builder is pre-populated with settings and values to get you started. As needed you can modify the step to meet your needs. For more information, see Transformer Toolbar.
   2. If you need to apply a transformation to an individual column, use the menu items in the column menu. This menu contains the most common column transformations. For more information, see Column Menus. 

3. Search and browse for transformations. Using the Search panel and the Transform Builder, you can rapidly assemble recipe steps through a simple, menu-driven interface. When you choose to add a step, you search for your preferred transformation in the Search panel. When one is selected, the transformation is pre-populated in the Transform Builder with parameter values based on your selections in the data. See Search Panel.

   Tip: Use the Transform Builder for performing modifications to the transform you selected from the Search panel or a suggestion card. See Transform Builder.

Sample

Loading very large datasets in Designer Cloud Enterprise Edition can overload your browser or otherwise impact performance, so the application is designed to work on a sample of data. After you have finished your recipe working on a sample, you execute the recipe across the entire dataset.

The default sample is the first set of rows of source data in the dataset, the number of which is determined by the platform. For smaller datasets, the entire dataset can be used as your sample. In the Transformer page, it's listed as Full Data in the upper-left corner.

In some cases, the default sample might be inadequate or of the wrong type. To generate a new sample, click the name of the sample in the upper-left corner.

On the right side of the screen, you can launch a new sampling job on your dataset. For more information, see Samples Panel.

Cleanse

Data cleansing tasks address issues in data quality, which can be broadly categorized as follows:

• Consistency. Values that describe the same thing should agree with each other. Numeric values should have the same precision. String values should be consistently structured to mean the same thing. 
• Validity. Values should be constrained to the requirements of each field's data type. For example, a DateOfSale field should be a valid date. 
• Reliability. Values in the same field in different records should mean the same thing. For example, the value 15 in the Temperature field of two different records should not mean Centigrade in one record and Fahrenheit in the other record. 

When data is initially imported, it can contain multiple columns, rows, or specific values that you don't need for your final output. Specifically, this phase can involve the following basic activities:

• Remove unused columns
• Address missing and mismatched data
• Change data types
• Improve consistency, validity, and reliability of the data

First recipe steps:

When a dataset sample is first loaded into the Transformer page, Designer Cloud Enterprise Edition attempts to split out the raw data to form regular, tabular data. If your data appears to contain a header row, it can be used for the titles of the columns.

In the above image, some initial parsing steps have been applied to structure the data in tabular, but these steps are not added as formal parts of the recipe.

• By default, these steps are automatically added to the recipe when you permit the application to detect the structure of the imported data.
• For more information, see Initial Parsing Steps.

The data resulting from these initial transforms is displayed in the data grid. See Data Grid Panel.

• Your recipe is displayed in the Recipe panel on the right side. You might have to open this panel to see it. See Recipe Panel.
• When you select items in the data grid, suggestion cards are displayed for you to begin building transform steps. See Suggestion Cards Panel.
• These suggestions can be modified to build more complex or subtle commands in the Transform Builder. See Transform Builder.
• Don't forget to use the Transformer toolbar, which pre-configures the Transform Builder with the configuration required for a useful transformation. See Transformer Toolbar.
• You can use the column context menu to apply changes to an individual column. See Column Menus.

Create a header row:

In most cases, the names of your columns are inferred from the first row of the data in the dataset.

If you need to specify a different row, please complete the following:

1. Click the Search icon in the menu bar.

2. In the Search panel textbox, type: header

3. The transformation is displayed in the Transform Builder. Specify the following properties:
   

   Transformation Name	Rename columns
   Parameter: Option	Use row as header
   Parameter: Row	1

4. If you need to specify a different row to use, you can specify a specific row number to use in the Row textbox.
5. To add this or any transform in development to your recipe, click Add. This button is disabled if the step is invalid. 

Generate row numbers:

On the left side of the data grid, you might notice a set of black dots. If you hover over one of these, the original row number from the source data is listed. Since the data transformation process can change the number of rows or their order, you might want to retain the original order of the rows. To retain the original row numbers in a column called, rowId, please complete the following:

1. In the Transformer toolbar above the data grid, select the Function menu. 
2. From the menu, select Metadata > SOURCEROWNUMBER.
3. In the Transform Builder, enter rowId in the New column name textbox.
4. Click Add.

Drop unused columns:

Your data might contain columns that are not of use to you, so it's in your interest to remove them to simplify the dataset. To drop a column, click the caret next to the column's title and select Delete.

Check column data types:

When a dataset is imported,  Designer Cloud Enterprise Edition attempts to identify the data type of the column from the first set of rows in the column. At times, however, type inference can be incorrect. 

Display only columns of interest:

You can choose which columns you want to display in the data grid, which can be useful to narrow your focus to problematic areas.

In the Transformer toolbar at the top of the screen, click the Column View icon.

For more information, see Column Browser Panel.

Review data quality:

After you have removed unused data, you can examine the quality of data within each column just below the column title. 

The horizontal bar, known, as the data quality bar, identifies the quality of the data in the column by the following colors:

For more information, see Data Quality Bars.

Suggestion Cards:

Designer Cloud Enterprise Edition examines your data based on your selections to prompt you with suggested transformations.

For more background information, see Overview of Predictive Transformation.

In the following example, the missing values in the SUBSCRIBER_AGE column have been selected, and a set of suggestion cards is displayed. 

Depending on the nature of the data, you might want to keep, delete, or modify the values. Since the data is missing, the Delete card has been selected.

• To accept this suggest, click Add.
• You can modify the step if needed. An example is provided later.

Change data types:

If a column contains a high concentration of mismatched data (red), the column might have been identified as the wrong data type. For example, your dataset includes internal identifiers that are primarily numeric data (e.g. 10000022) but have occasional alphabetical characters in some values (e.g.  1000002A). The column for this data might be typed for integer values, when it should be treated as string values. 

1. To change a column's data type, click the icon to the left of the column title. 
2. Select the new data type. 
3. Review the mismatched values for the column to verify that their count has dropped. For more information, see Supported Data Types.

Explore column details:

• From the caret drop-down next to a column name, select Column Details. See Column Details Panel.
• For more information on column stats, see Column Statistics Reference.

Review histograms:

Just below a column's data quality bar, you can review a histogram of the values found in the column. In the following example, the data histogram on the left applies to the ZIP column, while the one on the right applies the WEB_CHAT_ID column.

When you mouse over the categories in the histogram, you can see the corresponding value, the count of instances in the sample's column, and the percentage of affected rows. In the left one, the bar with the greatest number of instances has been selected; the value 21202 occurs 506 times (21.28%) in the dataset. On the right, the darker shading indicates how rows with ZIP=21202 map to values in the WEB_CHAT_ID column.

For a list of common tasks to cleanse your data, see Cleanse Tasks.

Modify

After you have performed initial cleansing of your data, you might need to perform modifications to the data to properly format it for the target system, specify the appropriate level of aggregation, or perform some other modification. When you select data, suggested transformations are presented to you as suggestion cards. Select one, or create your own transformation as needed.

In the following example, the improperly capitalized word BALTIMORE has been selected, so that you can change it to its propercase spelling (Baltimore). Those rows are highlighted in the row data, and a set of suggestions for how to fix has been provided in the cards at the bottom of the screen. See Suggestion Cards Panel.

Depending on the nature of your data, you might want to keep or change the values, or you can remove the problematic rows altogether. 

In this case, select the replace transform. However, there are a couple of minor issues with the provided suggestion.

• Since the platform has no idea about the meaning of the selection, it might initially suggest removing the text altogether. In this case, you want to change the spelling.
• In the transformation, the Find parameter value contains the pattern used to identify the selection. In this case, it is selecting all values that are capitalized. For now, you only want to fix BALTIMORE.

So, some aspects of this transform must be changed. Click Edit.

Transform Builder:

When you modify a transform step, you can make changes in the Transform Builder, which is a simple, menu-driven interface for modifying your transformations:

In the Transform Builder, you can replace the pattern with the specific string to locate: BALTIMORE. The new value, which is currently blank, can be populated with the replacement value: Baltimore. Click Add.

The step is added to the recipe and automatically applied to the data sample displayed in the Transformer page. For more information, see Transform Builder.

Enhance

Before you deliver your data to the target system, you might need to enhance or augment the dataset with new columns or values from other datasets. These multi-dataset operations can greatly expand the capabilities of your wrangling workflows. 

Union datasets:

You can append a dataset of identical structure to your currently loaded one to expand the data volume. For example, you can string together daily log data to build weeks of log information. See Union Page.  

Join datasets:

You can also join together two or more datasets based on a common set of values. For example, you are using raw sales data to build a sales commission dataset:

• Your sales transaction dataset contains a column for the salesman's identifier, which indicates the employee who should receive the commission. 
• You might want to join your sales transaction dataset to the employee dataset, which provides information on the employee's name and commission rate by the internal identifier. 
• If there is no corresponding record in the employee dataset, a commission is not rewarded, and the sales transaction record should not be present in the commission dataset.

This commission dataset is created by performing an inner join between the sales transaction dataset and the employee dataset. In the Search panel, enter join. See Join Panel.

Lookup values:

In some cases, you might need to include or replace values in your dataset with other columns from another dataset. For example, transactional data can reference product and customer by internal identifiers. You can create lookups into your master data set to retrieve user-friendly versions of customer and product IDs.

To perform a lookup for a column of values, click the caret drop-down next to the column title and select Lookup.... See Lookup Wizard.

For a list of common workflows to enhance your dataset, see Enrichment Tasks.

Profile

As part of the transformation process, you can generate and review visual profiles of individual columns and your entire dataset. These interactive profiles can be very helpful in identifying anomalies, outliers, and other issues with your data. For more information, see Profiling Basics.