Tosca BI Reconciliation testing

You can use Reconciliation tests if you want to perform an in-depth comparison of each individual source row with each target row. While aggregated tests are much faster, Reconciliation tests are more precise. They provide you with a detailed report that identifies cell differences in two datasets.

These datasets can be one of the following types:

  • Database tables

  • Files, including files on a Hadoop system or on a Linux/Unix environment connected via SSH

  • SSAS OLAP cubes

  • Other sources - such as Microsoft® Excel files - if you have installed an appropriate ODBC driver

Depending on the size of your datasets, the comparison can be time and resource consuming.

For information on how to run a Reconciliation test, see chapter "Run Reconciliation tests manually".

For information on how to use the Row by Row Comparison (SSAS OLAP) Wizard, if you have data in SQL Server Analysis Services OLAP cubes, see chapter "Row by Row Comparison (SSAS OLAP) Wizard".

Use Reconciliation tests

The main goal of reconciliation testing is to confirm that the source data matches the target data. Ideally, source and target are at minimum similar, both in content and in sorting. Tosca BI then helps you to find mismatches.

Possible mismatches are:

  • a row in the source dataset is not present in the target dataset

  • a row in the target dataset is not present in the source dataset

  • a source row matches a target row by RowKey, but not on all other columns

Tosca BI uses RowKeys to find matching rows in source and target.

RowKeys identify a row uniquely. For information on how to define a RowKey, see chapter "Define a RowKey".

The system processes source and target from top to bottom.

It takes the first source row and tries to find a matching RowKey within the target.

Once the system has found a matching RowKey in the target, it matches the rest of the row data. Only if both RowKey and row data match does Tosca BI consider it a true match.

If the RowKey matches, but the data does not, the system reports an error for the source and target row.

By default, Tosca BI takes a source row and searches for a match in the target. It concludes the Row by Row Comparison once all source rows have been processed.

However, in some cases, the target may contain additional rows, for instance duplicate rows. By default, Tosca BI ignores them.

If you want to report on unmatched target rows, such as duplicates, enter the value True for the XTestStepValue Report Unmatched Target Rows.

Best practices for Reconciliation tests

Tricentis recommends that you follow the best practices outlined below to ensure smooth Reconciliation tests.

Make sure that source and target are sorted in the same way.

This speeds up the comparison and saves memory (RAM).

Define a meaningful RowKey.

This example in chapter "Define a RowKey" illustrates the importance of a suitable RowKey.

If you also want to test transformations, add the transformation logic to your SQL statement.

This allows you to perform the transformation and the comparison within the same test.