Reconcile Transformation

Reconcile transformation in Astera Centerprise enables 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 a user to identify changes in multiple data records and capture them efficiently to drive critical business decisions.

Sample Use Case

Consider an example where we have a sample data of complaints filed by customers 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 so, 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 appropriate source objects and point them towards the files that you want to reconcile. In this example, we will be working with Excel Workbook Source.

2. Drag and drop the Reconcile transformation object from Toolbox> Transformations> Reconcile on the data flow designer.

This is what a Reconcile transformation object looks like:

You can see the transformation object contains three child nodes (Output, Input_1, and Input_2) under the parent node Reconcile.

3. Expand the Input nodes to map fields from the source files.

4. Map the data fields from the source objects that you want to reconcile to the respective input node in the Reconcile transformation object.

5. Right click on the Reconcile transformation object’s header and go to Properties.

6. This will open the Reconcile Transformation Properties screen where you will see the following options:

• Case Sensitive-check this option, if you want to derive a case sensitive output
• Sort Input 1 – check this option, if the incoming data from source 1 is not sorted
• Sort Input 2– check this option, if the incoming data from source 2 is not sorted

You can 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 in the output 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.

7. Click Next to proceed to the Layout Builder screen. Here you will have to specify a Key. It 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 select Complaint_ID as our Key.

8. Now go to the Survivor Value drop-down list to set the survivor value for each data field. Survivor values are the values from your source datasets which you want to retain in the 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

Note: You will only need to specify the Survivor Value if you want to get the Original Layout or Original Layout With Changed Element Collection as output. The Survivor Value option does not apply if you want to get Side by Side Element with Change Flag as your output since both of the source values are retained when this option is selected.

9. Click Next to proceed to the General Options window, then click OK.

• General Options screen - this screen shares the options common to most objects on the dataflow.
• Clear Incoming Record Messages - when this option is on, any messages coming in from objects preceding the current object will be cleared. This is useful when you need to capture record messages in the log generated by the current object and filter out any record messages generated earlier in the dataflow.
• Do Not Process Records with Errors - when this option is on, records with errors will not be output by the object. When this option is off, records with errors will be output by the object, and a record message will be attached to the record. This record message can then feed into downstream objects on the dataflow, for example a destination file that will capture record messages, or a log that will capture the messages and as well as collect their statistics.

10. Now, right click on the Reconcile transformation object’s header and select Preview Output to get the reconciled output.

You will get one of the following outputs according to the output type selected in Reconcile Transformation Properties.

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