Merge Transformation¶
Merge transformation in Centerprise is designed to merge data fragments from disparate sources, based on some predefined logic, and present it in a consolidated form to draw actionable insights.
Sample Use Case¶
Let’s assume that there is an organization that maintains customers’ data in two different departments – Marketing and Sales. Marketing department stores information in a database table and Sales department maintains an Excel sheet for storing customers information. We want to merge the information from both the sources, so that we have consolidated data.
Steps to Use Merge Transformation in Centerprise Data Integrator¶
1. Drag-and-drop the relevant source objects from the Toolbox to the designer. (Click here to find how to set up sources.)
(Note: In this case, the marketing department has the customer information stored in a database, whereas the sales department records customer information in an Excel file. Therefore, we will use a Database Table Source and an Excel Workbook Source as source objects.)
2. The Merge transformation object merges data from a single source only, we will first combine both the records using the Union transformation object. We will then map fields from the data sources to the Union transformation object and add a new field DataSource to keep track of which information is coming from which source.
3. Drag the Merge transformation object from the transformations section in the Toolbox and drop it on the dataflow designer.
This is what a Merge transformation object looks like:
4. Map the Union transformation object’s output to the Merge transformation object.
5. Right-click on the Merge transformation object to set up transformation properties in the Layout Builder window. This is what the Layout Builder window looks like:
6. In the Layout builder window, specify the Primary Key. This is a common identifier that identifies similar records from various sources and merges the information against these records.
(Since we are consolidating different customer records, we will set up CustomerID as the Primary Key in this case.)
7. Next, you have to specify the field to be used as Version. If your data is coming from multiple sources, the Version field shows which source the data is coming from in the final merged output. In this case, we will use the Data Source field we added in the Union transformation object as the Version field.
8. Next, specify the Survivor Type for each field. Survivor Type allows you to choose the survivor values – the values you want to retain from your data sources – for each field. Survivor Types are set as First by default. However, depending on your case, you can choose the Survivor Type from the following options:
| Survivor Type | Description |
|---|---|
| First | Returns data from the first data source for that field |
| Last | Returns data from the last data source for that field |
| Maximum | Returns the maximum value from all available input data sources |
| Minimum | Returns the minimum value from all available input data sources |
| Count | Returns the total count number of all values that exist in the field |
| Sum | Aggregates the values that exist in that field in all the input sources and then returns the arithmetic sum of those values |
| Comma Separated Values | Separates the values that exist in that field in all the input sources with a comma and then return that representation. This option is only available when the output field is assigned the 'String' Data Type. |
Since CustomerID, CompanyName, and ContactName records are common in both the source files (Customers_Marketing and Customers_Sales), we will set the Survivor Type as First for these fields. For the other fields with missing records, the Survivor Type will be set as follows:
| Field | Survivor Type |
|---|---|
| ContactTitle | First |
| Address | First |
| City | First |
| Region | Last |
| PostalCode | First |
| Country | First |
| Phone | Last |
| Fax | Last |
| DataSource | Comma Separated Values |
9. Once you have set the Survivor Type, specify Precedence for each field. Precedence is the order in which you want the source data to be assessed. For instance, we have common data fields in both the sources, but different and missing records. We can set appropriate Precedence values to bring data from the desired data source.
10. Next, you can set a specific Condition, and the Merge transformation will process records based on the criteria specified for a particular field.
(In this case, we have specified ‘IsNotNull’ for Address and Region fields since we want none of these fields to be empty or have missing records.)
11. Depending on the requirements of the business case, you can add a logical expression in the Expression field to process the incoming data value and transform it into the output according to the logic defined. The Expression field can be used for mathematical and financial calculations, date and time manipulations, comparisons and conversion functions.
12. Click Next to proceed to the Merge Transformation Properties window. Here, you will see the following three checkboxes:
- Case Sensitive – Check if data is to be assessed on a case-sensitive basis
- Sort Input – Check if the incoming data is not already sorted
- Version Order Descending – Check if you want the data to be sorted in a descending version order
13. Click Next to proceed to the General Options window. Here, you can add Comments, instructions, or any relevant information about the transformation. This will not change or alter your transformation action in any way.
You may also skip this step by clicking OK in the previous step (on the Merge Transformation window) to close the Transformation Properties window.
14. To get the output, right-click on the Merge transformation object, and click on Preview Output. You will get the merged records based on your specified transformation properties.
Data Preview before applying Merge transformation:
Data Preview after applying Merge transformation:
Usage and Benefits¶
Merge transformations can be applied in cases where data is sorted into different records. Centerprise makes it extremely convenient for users to get consolidated data that is stored in different sources, while also allowing them the flexibility to choose how the output should appear, through the various transformation properties.