Reconcile Transformation is a new addition to Centerprise’s extensive array of transformations that allows the user to identify and reconcile new, updated, or deleted information entries within the existing data source. It can be applied in a wide variety of business scenarios that require the user to identify changes in multiple data records and capture them efficiently to drive critical business decisions.
Sample Use Case
Consider the example where we have a sample data of complaints filed by consumers regarding the products and services provided by a company. Assume that the source file 1 contains the details and status of complaints on January 1st, 2018, and the source file 2 contains the details and status of complaints on February 1st, 2018. We want to track the progress of the resolved complaints during that one month. To do that, we will reconcile the information contained in the source data files and capture changes using the Reconcile transformation.
How to Use Reconcile Transformation
1. Drag and drop the source files you want to reconcile using the appropriate source object.
2. Drag and drop the Reconcile transformation object from the transformations group in the Toolbox and drop it on the data flow designer. This is what a Reconcile transformation object looks like:
3. You can see the transformation object contains 3 child nodes (Output, Input_1, and Input_2) under the parent node ‘Reconcile 1.
4. Expand the Input nodes to map fields from the source files.
5. Map the information fields from the source files you want to reconcile to the respective Input node on the reconcile transformation object.
6. Double click on the transformation object to set up reconcile properties.
7. On the Layout Builder screen, you will have to specify a primary key. The primary key is a common identifier in both the source files that will identify and reconcile records. In this case, we want to reconcile the progress on complaints made against each complaint ID; therefore, we will check the primary key checkbox next to Complaint_ID to set it up as the primary key.
8. Now click on the drop-down menu under Survivor Value to set the survivor value for each field. You will only have to specify the survivor value if you want to get the ‘Original Layout’ or ‘Original Layout With Changed Element Collection’ as output (discussed below). The Survivor value option does not apply if you want to get ‘Side by Side Element with Change Flag’ output.
You may select from the following Survivor Value options:
- Second – if you want to derive output value from the second source
- First – if you want to derive the output value from the first source
- First IF Not Null. Otherwise Second – if you want to bring output value from the first source if the record is not null, otherwise from the second source.
- Second IF not Null. Otherwise First – if you want to bring output value from the second source if it is not null, otherwise from the first source.
- Higher – if the input values are integers, and you want to choose the higher value
- Lower – if the input values are integers, and you want to select the lower value
- Expression – if you want to derive the output value based on a formula expression
9. Once you have specified the primary key and survivor values, click ‘Next’ to proceed to the ‘Reconcile Transformation Properties’ window, to specify the following properties.
- Case Sensitive – check, if you want to derive case sensitive output
- Sort Input 1 – check, if the incoming data from source 1 is not sorted
- Sort Input 2 – check, if the incoming data from source 2 is not sorted
- Next, choose the reconcile output type from the following options:
- Side By Side Element With Change Flag – if you want to get values from both sources presented side by side, with a separate column presenting the reconciled output by putting a flag – true, in case of an update, and false if it remains unchanged.
- Original Layout – if you want to get the reconciled output for each record and corresponding information in the reconciled field.
- Original Layout With Changed Element Collection – applies when working with hierarchical data, to reconcile the information contained in child nodes.
- Once you have selected the preferred output type, you can specify the records to be shown by applying the Record filter and Inner Node filter. You may choose one, multiple, or all of the following options by check marking the box.
- Click ‘Next' to proceed to the General Options window. Check the options if you want to clear incoming record messages and/or do not want Centerprise to overwrite default values with nulls and click okay. You may also skip this step by clicking okay on the previous ‘Reconcile Transformation Properties’ window.
- Right click on the Reconcile transformation object, and click ‘Preview Output’ to get the reconciled output derived from the source files. Depending on the type specified in ‘Reconcile Transformation Properties,’ you will get one of the following outputs.
- Side by Side Element with Change Flag
- Original Layout
- Original Layout With Changed Element Collection
Usage and Benefits
Reconcile transformation can be applied in a variety of business cases, particularly those where monitoring the changes in assorted data records is crucial to driving critical business decisions. Here are some of the benefits and uses of the reconcile transformation:
- Reconciles data by deriving old and new values for specific fields in the source data
- Allows users to choose from various layout options to reconcile changes in the most appropriate way
- Works effectively with structured and unstructured (hierarchical) data formats
- Offers the flexibility to select the information to be retained through different survivor value options