In this chapter of the exMon walkthrough we go through how a user creates an exMon compare query that compares salesprices between two servers.

First thing we do is create the compare query task in exMon

  1. Open exMon Administrator
  2. On the left side you see a treelist. Right click the node that says „Tests“, select New... and the Compare Query
  3. In the popup type Sales Order Check

 

Now we want to create our SQL queries, which will retrieve all sales orders and summerize for 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 a lot.

  1. We want to start by setting up our left SQL query
  2. Select the data provider AdventureWorks. If the data provider has not been created, follow the Data Provider Walkthrough and then select it
  3. In the textbox that says name write Detail
  4. Paste the following SQL query into the textbox

SELECT [SalesOrderID]
      ,sum([LineTotal]) as total
  FROM [Sales].[SalesOrderDetail]
group by [SalesOrderID]

image

 

  1. We now want to set up the right SQL query
  2. Select the data provider AdventureWorks. If the data provider has not been created, follow the Data Provider Walkthrough and then select it
  3. In the textbox that says name write Header
  4. Paste the following SQL query into the textbox

SELECT [SalesOrderID]
      ,[SubTotal]
      ,[CustomerID]
      ,[SalesPersonID]
FROM [Sales].[SalesOrderHeader]

 

image

 

Now we have a very basic exMon compare query set up. 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, either by focusing each textbox and pressing F5 or pressing the Start All button
  2. Press the Add Column button and select the row that was created in the gridview
  3. Set the Column name of the new row as SalesOrderID, it‘s type KEY, Dataset 1‘s column SalesOrderID and Dataset 2‘s column SalesOrderID
  4. Create second row, with the name Total, the type VALUE, the Value comparison set to default(both top options selected in popup), dataset 1‘s column total and dataset 2‘s column SubTotal
  5. Create 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 deeper understanding of the results.

image

 

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

  1. In the Properties window on the right, press the Editt... button next to Column Formatting
  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 and check User 1000 seperator
  5. On the left panel, select the column CustomerID
  6. In the Visibility dropdowns for both Email and Portal, select DS2
  7. In the Caption textbox type Customer
  8. On the left panel, select the column SalesPersonID
  9. In the Visibility dropdowns for both Email and Portal, select DS2
  10. 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 seperator 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.

image

 

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. In the Description field, paste: Compares all our sales orders to the summerized sales orders
  2. In the Action field, paste: 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.“

image

 

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

  1. In the properties window on the right, select recipients
  2. In recipients select your own exMon 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 exMon will send an email with details about compare query when it‘s ran and it ends with either an key error or variance error.

image

Summary emails are especially good when Exception Manager is not enabled.

 

Our next job is to enable and configure the exception manager. When a query is ran and it returns results, the exception manager creates an exception for each row and keeps track of the exception until query results no longer contains 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 exMon tries to map the column‘s value to an exMon user. If the mapping fails, exMon by default makes query owner the exception owner. This can be overridden to automatically create a new exMon 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 exMon user
  4. In the User Mapping Type dropdown, select AdentureWorks Sales. If the type is not in the dropdown, please follow the External System walkthrough to create it
  5. In the User Mapping Column, select SalesPersonID

We‘ve now enabled user mapping, so the exception manager always tries to map the SalesPersonID column to an exMon 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.

image

 

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

  1. Open the Exception Manager popup with the Emails tab open
  2. In the line Send exceptions email on, check New
  3. In the line Include these exceptions in email, check Open. New should be checked and disabled
  4. In the Email subject field, type Exceptions – Sales Order Check
  5. In the email template field, select Exception Manager Compare Query

We‘ve configured the query‘s exception manager to send emails only when 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.

Notice: Similar to the user mapping to map who is the owner of an exception, you can also map who should receive emails as a CC using the CC mapping.

image

 

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 lets 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.

Category –

Unique Id –

Start Date – The column mapped to 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 an 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 Sales Order ID
  4. For the Customers dimension mapping, select the column CustomerID
  5. For the Sales Persons dimension mapping, select the column SalesPersonID

image

 

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

By default, primary key is a composition key made up from 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.

image

 

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

By setting up a detailed data type schema in exMon, the portal‘s overview will use the schema to create a easily readable treeview in the portal which groups queries and compare queries by data types. That way if you have fx. 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 it‘s field to Sales
  3. If Data Type dropdown doesn‘t contain Sales, please check the Data Type walkthrough to create it

 

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

  1. Press the deploy button
  2. If Test enviroment is enabled, select the correct enviroment deployment
  3. Write a comment, detailing the changes being deployed
  4. Make sure to check Sales Order Check in the popup and press deploy

image