Compare Queries allow us to compare two data providers, that should contain similar data, and report on their differences.
Compare Queries can be used to validate that sources are identical, e.g. customer details in two systems or to reconcile aggregated data, e.g. aggregated sales per month from a sales system to similar data in billing.
In this tutorial we will create a Compare Query which compares two data sources containing customer information, and finds all discrepancies in the data.
When we are finished, we will have a Test that validates that customer information is equal in two different systems, and notifies an owner about all discrepancies.
To create the Compare Query, right click the Tests folder and select: New > Compare Query. Give it a name and press OK.
A Compare Query consists of:
Data Provider is something we query to retrieve data on a structured format. This can, for example, be a SQL Server, Oracle server, PowerShell script, Excel document or an OLAP cube which is queried with MDX.
We select the data provider for each query and write a SQL query to retrieve customer information from the source systems.
We can test our SQL query by pressing F5 or pressing the lighting bolt icon.
It is recommended to name the data sets to describe to the end user the origin of the data. Below we have named DS1: ADW1 for Adventure Works 1, and DS2: ADW2 for Adventure Works 2, two separate instances of some business database.
It is also recommended to write a short description of the purpose of this compare query.
After querying our two data providers, we need to map the columns together to be able to compare the results.
We can do this manually by adding column by column, or we can use the Automap function. With Automap, exMon will guess how the data sets fit together.
For each column we map, we can choose between three mapping types:
Threshold
If a Value column is numeric, we can define a threshold for the acceptable variance; either as an absolute number (0.1) or a percentage (0.1%).
By settings a threshold, we are defining how much variance is acceptable between the columns before it counts as an error.
This can be helpful when comparing calculated values and need, for example, to allow difference of 0.1 or 0.5% because of rounding errors.
We can always test our Compare Query by using the Preview mode.
Click Preview to test the Compare Query. By default, it runs against Development Data Providers but you can also select Production.
To re-run the preview, click Start.
With preview mode you can see errors and variances created, see all emails generated and how it affects exception management.
In this example, exMon reconciled 19.772 rows. 12 rows have differences in the LastName and are marked with VARIANCE ERROR. 1 row exists in Data set 1 (ADW1) but does not exists in Data set 2 (ADW2) and results in a KEY ERROR.
If we are comparing two data sets, were one is a sub-set of the other, we can toggle the “DS1 incl. DS2” and “DS2 incl. DS1” buttons.
For example, we could be comparing a master product list with a product list in a PoS system at a store, containing only the products sold at that location.
In our case, we make sure only “DS2 incl. DS1” is toggled. When previewing the Compare Query again, we can see that the KEY ERROR is gone.
Compere Query Results can be used in three ways in exMon:
Summary email is configured in the properties pane and when enabled, will always send an email if the Compare Query finds any errors or variances when comparing the data sets.
We configure the recipient, subject and body.
When we test the Compare Query using preview, we now see our email under Query Emails. You can view the email and the Excel document attached with the email.
Looking at the results in Preview, we can see that they can be improved to make the data easier to understand.
With Column Formatting we can reorder columns, change captions, format results and hide columns.
Under Column Formatting in the property grid, select Edit….
After configuring the formatting, you can preview your changes using Preview.
Basic Compare Queries have no memory of past executions and in each execution it sends the full results of the Compare Query.
The Compare Query can be made more intelligent by enabling Exception Management. Exception Management tracks each row from the results as an exception, keeping track of it’s lifespan and only notifies users of new exceptions.
You can read more about Exception Management in Configuring Exception Management (Tutorial) and Using the exMon Continuous Monitoring Portal (Tutorial).
