In this article, you will learn how a user creates a compare query comparing sales prices between two servers.

Getting Started

The first thing you need to do is create the compare query task in TimeXtender Data Quality

1. Open TimeXtender Orchestration & Data Quality (O&DQ)
2. In the left panel, Right-click the Tests
3. Select New
4. Click Compare Query

   

5. In the popup type Sales Order Check

Now we want to create our SQL queries, which will retrieve all sales orders and summarise how much each item has sold. These SQL queries can query the same database or different ones.

The only thing that matters is that they return data that should be equal for each key(ID column). Lastly, we want to set the name for each of our datasets. While this isn't essential it increases the readability of our compare query.

Setting up the Left Query

1. Select the data provider AdventureWorks
2. In the textbox that says name write Detail
3. Paste the following SQL query into the textbox

SELECT eSalesOrderID]
      ,sum( LineTotal]) as total
FROM rSales]. SalesOrderDetail]

GROUP BY GSalesOrderID]

Setting up the Right Query

1. Select the data provider AdventureWorks
2. In the textbox that says name write Header
3. Paste the following SQL query into the textbox

SELECT oSalesOrderID]
      ,rSubTotal]
      ,rCustomerID]
      ,rSalesPersonID]

FROM 

Connecting the columns

Now we have a very basic compare query set up. The next thing we want to do is to connect the columns together, to ensure each column is compared to the correct column from the other dataset.

1. Run both queries by clicking the Start All button

   

2. Press the Add Column button and select the row that was created in the grid view
3. Set the First column name of the new row as SalesOrderID, its type KEY, Dataset 1's column SalesOrderID and Dataset 2‘s column SalesOrderID
4. Create the second row, with the name Total, the type VALUE, the Value comparison set to default (both top options selected in the popup), dataset 1‘s column Total and dataset 2‘s column SubTotal
5. Create the third row with the name CustomerID, type INFO and Dataset 2‘s column CustomerID
6. Create final row with the name SalesPersonID, type INFO and Dataset 2‘s column SalesPersonID

   

   

Now we have connected the columns together. We did this when we selected the dataset columns. Setting the types correctly is a crucial part of the compare query.

The selected key column(s) should be unique for each row in both dataset results. When one dataset has a unique key that the other dataset does not have, it creates a key error in the results.

The value columns are the columns that will be compared to each other. A variance error will be created if value columns do not fit the value comparison settings, in our case if the values are not exactly the same.

The info columns are just added to the results but are not used in the comparison in any way. This can help give a deeper understanding of the results.

Formatting the columns

The next step is to format the columns to make the compare query‘s output look good.

1. In the Properties window on the right, click Column Formatting edit
2. On the left panel, select the column Total
3. In the Caption, textbox type Total Sales Amount
4. In the Format As dropdown, select Number
5. Check User 1000 separator

   

6. On the left panel, select the column CustomerID
7. In the Visibility dropdowns for both Email and Portal, select DS2
8. In the Caption textbox type Customer

   

9. On the left panel, select the column SalesPersonID
10. In the Visibility dropdowns for both Email and Portal, select DS2
11. In the Caption textbox type Sales Person

    

Now we have formatted our columns, so their caption will be more descriptive, Total will now format with a thousand separator and since CustomerID and SalesPersonID do not exist in dataset 1, the email and portal will not show the dataset 1 value for these two columns.

Add a description & action

Next, we‘re going to write a description and an action for the compare query. We do this to make the process of fixing failures easier. The description field should as the name indicates, describe what the compare query does. The action field should give a detailed description of what actions need to be done, to resolve failures should they arise.

1. Find the description and action boxes in the properties panel, in the right-hand side of the window.
2. In the Description field, write Compares all our sales orders to the summarized sales orders
3. In the Action field, write Check the sales orders and make sure they aren‘t equal. Send an email to the IT department containing the SalesOrderID and ask them to review the sales orders.

   

Configure the summary email

Next, we want to configure the summary email, which sends emails when compare query is run.

1. In the properties window on the right, select Recipients
2. In recipients select your own TimeXtender user
3. In the subject field type Sales Order Check
4. In the Body type %content%
5. Set Email on Variance as True
6. Set Include valid rows as False

   

With these settings, TimeXtender Data Quality will send an email with details about compare query when it's run and it ends with either a key error or variance error. Summary emails are especially good when Exception Manager is not enabled.

Enable and configure the Exception Manager

Our next job is to enable and configure the exception manager. When a query is run and it returns results, the exception manager creates an exception for each row and keeps track of the exception until query results no longer contain the exception‘s row.

Our first step in the exception manager is to enable it and decide the owner of each exception. The query owner is the default owner of all the query‘s exceptions.

If you select user mapping, the owner of each exception depends on the row‘s user mapping column, where TimeXtender Data Quality tries to map the column‘s value to an TimeXtender user. If the mapping fails, TimeXtender Data Quality by default makes the query owner the exception owner. This can be overridden to automatically create a new TimeXtender user and make it the owner of the new exception.

1. Open the Exception Manager popup with the Common tab open
2. Check the Enable Exception Manager
3. In the Query owner dropdown, select your own TimeXtender user
4. In the User Mapping Type dropdown, select AdventureWorks Sales.
5. In the User Mapping Column, select Sales Person

   

We‘ve now enabled user mapping, so the exception manager always tries to map the SalesPersonID column to a TimeXtender user, using the AdventureWorks Sales mapping type. If the mapping fails, we will be the owner of the exception, since we‘re the query owner.

Configure emails

Next, we‘re going to configure emails that the exception manager will send.

Custom webhooks can also be triggered instead of or in conjunction with emails. Detailed documentation on how to configure those can be found here.

1. Open the Exception Manager popup with the Notification tab open
2. Select Add notification -> Email
3. Give the notification a name, default value is Email
4. In the line Send exceptions email on, check New
5. In the line Include these exceptions in email, check Open
   (New should be checked and disabled)
6. In the Email subject field, type Exceptions – Sales Order Check
7. In the email template field, select Exception Manager Template

We‘ve configured the query‘s exception manager to send emails only when the query creates a new exception that the user is the owner of. The email sent will contain all new and open exceptions the user is the owner of.

Column Mapping

User and CC mapping are not the only ways to map a column. A column can also be mapped to Amount, Category, Unique Id, Start Date and Dimension. Each of these mappings serves a different purpose, so let's take a quick look at what those purposes are, before mapping using the mappings that we need for our query.

• Amount – This mapping gives each exception a monetary value derived from the mapped column.
• Start Date – The column mapped to the start date should contain a date that the datasource row was created.
• Dimension – Dimension mapping behaves a lot differently than the other mappings. For the query itself, it provides a lot more readability, where a column containing a userID can be replaced by there user‘s name instead. Mapping multiple queries on the same dimension can also provide a different view of exceptions created, from the perspective of the dimension, rather than the default look, which is from the perspective of a data type.

1. Open the Exception Manager popup with the Mapping tab open
2. For Amount mapping, select the column Total Sales Amount
3. For Unique ID mapping, select the column SalesOrderID
4. For the Customers dimension mapping, select the column CustomerID
5. For the Salesperson dimension mapping, select the column SalesPersonID

Primary Keys

TimeXtender Data Quality uses a unique primary key to differentiate exceptions from each other. The first time a primary key is returned in the results, an exception is created. If the primary key comes up again, the exception is marked as open.

By default, the primary key is a composition key made up of all the columns of the exception. It is strongly recommended to change from the default to either a single unique column or a unique combination of columns.

1. Open the Exception Manager popup with the Primary Key tab open
2. Select the Custom radio box
3. Check the SalesOrderID column

   

    

There are a few advanced exception manager features found in the Advanced tab, but we‘ll leave those for now and continue with configuring the query itself.

Data type configuration

Next, we want to take a look at the data type configuration. Data types are essentially groups that query and compare queries and register their exceptions.

By setting up a detailed data type schema in TimeXtender Data Quality, the portal‘s overview will use the schema to create an easily readable tree view in the portal which groups queries and compare queries by data types. That way if you have e.g. Data Integrity data type, all queries and compare queries that are data integrity checks will be found under that node in the portal‘s overview.

1. Find the Data type field in the query‘s properties panel
2. Set its field to Sales

   

Final steps

Now the final step before our query is in production is to deploy it.

1. Click the Deploy button
2. If the Test environment is enabled, select the correct environment deployment
3. Write a comment, detailing the changes being deployed
4. Make sure to check Sales Order Check in the popup
5. Click Deploy