Your First ORQA Reconciliation In Three Minutes
Using This Tutorial
This tutorial assumes you have already have the skills taught in the previous tutorial here. You can enhance this tutorial by looking at the examples in the model window by default when ORQA is first downloaded. These examples also walk you though a data reconciliation.
Creating Your First ORQA Reconciliation
1.Creating A New Task
Using the same steps you followed in the previous tutorial, make a new task for this tutorial.
2.Downloading The Example Data Files
In this tutorial we will reconcile data stored in two different formats, one will be an Excel file (.xlsx) and the other a CSV file (.csv). Start by downloading the Excel file here and the CSV file here (if you have the Google doc’s app installed right click the link and select “Save as” to save the file).
3.Loading Your Data Files
Loading a CSV file follows the same pattern as loading an Excel file. In the toolbox window select the CSV Reader and then add it to your task. In the parameters for that file select the file path for the file you downloaded in the previous step. You will see that there are various other parameters which already have their values filled in. These are the default settings for CSV files and can be left alone.
Load the excel file in the same way as you did in the first tutorial.
4. Assigning Names To Your Data
In ORQA you can name the output of an operation and then use that name in a later operation to get the data again. This is necessary when using some operations, such as the “Stream Comparer” operation for reconciling two data sources. You can name the output of an operation in the “Assign to” parameter for that operation.
In the task editor, select the CSV Reader operation and then go to the Properties window. Select the parameter called “Assign to” and enter the Name “CSV”. Now, you can use the name “CSV” to get the output of this operation later in the task.
Next, select the Excel file and in the Properties window set the “Assign to” parameter as “Excel”.
5. Loading The Stream Comparer
In the ‘Toolbox’ window select the ‘Data Reconciliation’ folder, then add the Stream Comparer operation to your task.
Stream comparer will have the following parameters compares two streams of data, The streams of data are called the left and right. In this operation will we call the data we named earlier. Fill in your the parameters like this.
Left: CSV (this is getting the data you named CSV earlier)
Right: Excel (this is getting the data you named Excel earlier)
Key Columns: ID Number (this is case sensitive)
Output Format: Flattened
Left Title:From CSV (this what the left data stream (i.e. CSV) will be called in the output of this operation)
Right Title:From Excel (this what the right data stream (i.e. Excel) will be called in the output of this operation)
6. Run The Task
After running the task and Looking at the preview window, you will see the output of the stream comparer. You will see that the first column is a new column called RESULT. This column informs you as to whether that row reconciled correctly. This output is determined by the “Output Format” parameter. There are multiple options for “Output Format”. The “Flattened” output allows you to quickly and easily read and understand your reconciliation visually, however, the other formats help with enhancing flexibility of your data. This helps if you want to use the data for further analysis. Try rerunning the task with the other options so that you get an idea for possible outputs for the stream comparer.