Cleaning and Transforming Data for Optimal Business Performance

The better quality data you have at your disposal, the better the actions and analyses you perform on that data.

However, the data you have is not always in a state ready for analysis. The files may contain redundant or incorrect data. There may also be duplicate or missing values.

To make sure that the data you want to use in your activities and analyses is properly prepared, it is a good idea to clean data before using it. Automation Hub comes to your aid. It gives you the ability to perform a large number of operations on any data you need to modify.

This use case describes the process of transformation of a CSV file with electronic card transactions. The data transformations performed in this use case consist of:

• removing columns with missing data
• adding missing data
• removing structural errors by adding suffixes
• filtering rows

After transformation, the file will be transferred to an SFTP server.

Prerequisites

• Save the file which you want to transform to your computer.
• You must have a target resource with which you transfer the data (in this use case, an SFTP server is used).
• Make a copy of the data file and remove rows from the copy until 10 are left. This copy will be used only as a sample for configuring the Data Transformation rules.

Process

1. Prepare transformation to modify the data to meet the requirements of the external resource data structure.
2. Prepare a workflow that sends the data of customers from Synerise to the external resource.

Create transformation

In this part of the process, you define the rules of modifying data before sending it to the SFTP server, so the data is consistent. Each of the following sub-steps describes the individual changes performed on the file.

The data transformation diagram which is the output of this part of the process is used later to automate sending the data.

1. Go to Automation Hub > Transformations > New transformation.
2. Enter the name of the transformation.
3. Click Upload a new file or drag one here.

Add file with sample data

This step allows you to add a data sample. In further steps, you define how the data must be modified. Later, when this transformation is used in the workflow, the system uses the rules created with the sample data as a pattern for modifying actual data.

1. Upload the file with the sample data.
2. Provide Delimiter, Quotation mark and Escape character.
3. Click Apply.

Remove irrelevant data

In this part, use the Remove Columns step, to remove the columns that do not contain data, these are the columns named: Series_title_3 and Series_title_4.

1. On the right panel, click App step.
2. From the dropdown list, select Remove Columns.
3. In the configuration of the step:
   1. Leave the Remove option selected in the dropdown menu.
   2. Leave the default value in the dropdown as Equal.
   3. In the text field, enter the name of the empty column you want to remove - Series_title_3.
   4. Click Add condition.
   5. Repeat steps 3.b-d to define all the columns you want to delete.

      

      This resulted in the removal of columns Series_title_3 and Series_title_4:

      

   6. Confirm by clicking Apply.

Handle missing data

Use the Edit Values step that allows you to perform basic actions on the dataset.
In this example, handle missing data in the Suppressed column by replacing its contents (in this example, the column is empty) with the value false.

1. On the right panel, click the Add step.
2. From the dropdown list, select Edit Values.
3. Click the Edit Values step.
4. In the configuration of the step:
   1. Select the Suppressed column.
   2. Under Edit values by, from the dropdown list, select Replacing.
   3. In the dropdown below, leave the Static value option at default.
   4. In the text field, enter false.
      

      

      As a result, you will get an updated file:

      

   5. Confirm by clicking Apply.

Fix structural errors

Structural errors are when you notice strange naming conventions, typos, or incorrect capitalization when measuring or transmitting data.
Use Edit Values step to make the format of the data in the column Data_value consistent. Currently, in the Data_value column, the data appears both as number 36422 and float 33317.4. To make the data consistent, unify the data to float.

1. On the right panel, click the Add step.
2. From the dropdown list, select Edit Values.
3. Click the Edit Values step.
4. In the configuration of the step:
   1. Select the Data_value column.
   2. Under Edit values by, from the dropdown list, select Adding suffix.
   3. In the text field, enter .0.
   4. Click If current value.
   5. From the dropdown list select Not contain.
   6. In the text field provide ..

      

      As a result, you will get an updated file:

      

   7. Confirm by clicking Apply.

Filter records

To filter records from the data, use the Filter Rows step.
In this case, keep only records where the value of the UNIT column is equal to Dollars.

1. On the right panel, click Add step.
2. From the dropdown list, select Filter Rows.
3. Click the Filter Rows step.
4. In the configuration of the step:
   1. Select the UNITS column.
   2. Under Where value, from the dropdown list, select Contain.
   3. In the text field, enter Dollars.

      

      As a result, you will get an updated file:

      

   4. Confirm by clicking Apply.

Add the finishing step

This step lets you preview the output of the modifications to the sample data.

1. Preview the results in the Output tab.

   

2. In the upper right corner, click Save.
   Result:

   

Prepare a workflow

The scenario for this use case involves a one-time transformation of a file uploaded from the user's local storage. The transformed data will be exported to the external source using the SFTP protocol.

1. Go to Automation Hub > Workflows > New workflow.
2. Enter the name of the workflow.

Define the launch date

1. As the trigger node, add Scheduled Run.
2. In the configuration of the node:
   1. Select the Run once option.
   2. Select Immediately.

      

   3. Confirm by clicking Apply.

Select file to export

1. Add a Local File node.
2. In the configuration of the node:
   1. Upload the file in which you want to perform the transformation.

      

   2. Confirm by clicking Apply.

Select the transformation

1. Add a Data Transformation Studio node.
2. In the configuration of the node, keep the default From workflow context option.
3. Click Templates library:
   1. Select the transformation you have created before.

      

   2. Click the Use template button.

4. Confirm by clicking Apply.

Configure settings for SFTP protocol

1. Add the Send File node by clicking THEN > SFTP.
2. In the configuration of the node:
   1. Choose SFTP connection type:
      1. Select Public-key.
      2. Below, choose desired item in the select connection field.

         

      3. Click Next.
   2. Enter the path to your server.
   3. Select the port.
   4. Enter the path to the directory.
   5. Enter the name of the file that will be created.
   6. If needed, in the File name suffix, select the suffix of the file name.
   7. From the File format dropdown list, select the CSV format.
   8. Verify and modify the delimiters if needed.

      

   9. Confirm by clicking Apply.

Add the finishing node

1. Add the End node.
2. In the upper right corner, click Save & Run.

   

Check the use case set up on the Synerise Demo workspace

You can check the transformation and workflow directly in Synerise Demo workspace.

If you’re our partner or client, you already have automatic access to the Synerise Demo workspace (1590), where you can explore all the configured elements of this use case and copy them to your workspace.

If you’re not a partner or client yet, we encourage you to fill out the contact form to schedule a meeting with our representatives. They’ll be happy to show you how our demo works and discuss how you can apply this use case in your business.

Generated events

This use case generates approximately 5 events per workflow execution: automation.clientStartPath (~1), automation.clientPathStep (~3), automation.clientEndPath (~1).

Read more

• Data Transformation
• Workflow