In this tutorial, we are going to create a simple Query that monitors sales with discounts 30% or larger.
We will learn how to:

  • Format the result columns to make them more readable
  • Use the Exception Manager to track exceptions and send emails when new exceptions appear
  • Add the query to a Schedule that will make the query run every day at 1 pm.


Let's start by opening up exMon Data Governance.

  1. From the exMon Data Governance tree menu, select Tests > New… > Query



    Or you can also simply press New Query  from the starting page.



  2. In the Create a new Query popup, enter a Query name (In this example we will call it 'Monitor sales with discounts 30% or larger', and then select OK.

 

Creating the query

 

Now we can start configuring the query itself. Since we want to monitor sales with discounts 30% or larger in the database exMonDG_Demo. Our first task is to pick the Data Provider we want to be working with. 

  1. From the Data Provider dropdown, select > exMon. This is the name of the data provider which is connected to the exMonDG_Demo database that we want to be working with in this demo.   


  2. Paste the following SQL query into the textbox. This SQL does check if there are any sales with discounts 30% or larger?

 

SELECT

    header.SalesOrderID, detail.SalesOrderDetailID, 

    header.AccountNumber, product.ProductId, product.Name, product.ProductNumber,  

    detail.UnitPrice, detail.OrderQty, detail.UnitPriceDiscount, detail.LineTotal,

    header.OrderDate,

    cust.LastName as CustomerLastName,

    cust.SalesPerson

FROM [Demo].[SalesOrderDetail] detail

left join [Demo].[SalesOrderHeader] header on detail.SalesOrderId=header.SalesOrderID

left join [Demo].[Product] product on detail.ProductID=product.ProductID

left join [Demo].[Customer] cust on header.CustomerID=cust.CustomerID

where detail.unitpricediscount>=0.3

 

 

3. To run the query, we press the green button with lightning (or simply press F5) and the output will appear in the textbox at the bottom.


 

Button to run the Query


 

Results from the Query



 

Format Columns

 

As we can see the values in the results could look a bit more formatted. For example, we might want to change the names of the columns to something a little bit more readable, for example, format the date columns to a specific format or add/reduce decimal places for numbers. 

All of this, and more, you can do in the Column Formatting. In this example, we would like to do a couple of things to make our output look a bit nicer. 

  1. Start by running the query by pressing the green button with lightning (or simply press F5)
  2. To the right, in the properties panel, press Edit…


  3. To the left, we see a list of all the columns in our output. Select the column OrderDate.


  4. To the right, set the Caption for the column as 'Order Date'. The same name but with space in between to make it more readable.


  5. In the Format as dropdown, select Date and Time.


  6. Let’s select a new column, called UnitPrice 


  7. In the Format as dropdown, select Number 


  8. Finally, let’s use two decimal places and check the Use 1000 separator

 

Preview


Now we can try to preview our column formatting changes but we need to save our changes first. To save we simply press the Save icon button in the top panel.


To see the changes we just made from Column Formatting we can press the Preview button in the top panel. 



Inside the Preview, we need to press Start to update our results.


Now we see our results and the changes we have made to the columns OrderDate and UnitPrice.



Now, let's go back to Design Mode again by pressing the Design button in the top panel.



Exception Owner

 

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

  1. Open the Exception Manager popup from the top panel, with the Common tab open.



  2. Check the Enable Exception Manager


  3. Select your own user as the Query owner in the dropdown

 


Query description and action

 

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

To set these values we need to open up the Exception Manager dropdown in the top panel and have the common tab selected. At the bottom, you can see a section called Description which contains textboxes to set the description and action values.




  1. In the Description field, write 'Monitor sales with discounts 30% or larger.'
  2. In the Action field, write 'Verify the order before processing'

 

Preview before Email formatting

 

Let‘s try to preview our results again by pressing save first, then Preview in the top panel, and finally Start like before to update our results.

 

Now we can see that the Exception Summary has been updated. It used to say 'Exception Manager is disabled' but now we actually have some exceptions.


and we can select the Exceptions tab from the left panel to see a more detailed view of our exceptions.


Then we can expand the tree list to see our exceptions.


Now, let's go back to Design Mode again by pressing the Design button in the top panel.

Exception Email

Next, we‘re going to configure emails that the exception manager will send. Now we are currently collecting the exceptions but we are not sending any emails. Our goal is to send an email every time a new exception appears. 

  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. Also, New should be checked and disabled. By checking this option we include a list of all open exceptions in the email.


  4. In the Email subject field, type 'New exception in sales with discounts 30% or larger'
  5. In the Email template field, select Exception Manager Query

 

Now, we have 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.

 

Preview Email

 

Let‘s try to preview our results again by pressing save first, then Preview in the top panel, and finally Start like before to update our results.


1. Select the Exception Emails tab in the left panel


2. Now we can see a preview of the email that will be sent. It should look something like this:




Now, let's go back to Design Mode again by pressing the Design button in the top panel.


Exceptions Primary Key

 

The core of the exception manager is that each exception has a unique primary key. This unique key can be a single column or a composite key made out of multiple columns, but it can only occur once in the results of a test.

The reason primary keys need to be unique lies in the way exception manager works. If an exception occurs, a user (normally the exception owner) will have to change value columns in the underlying data source, to fix the exception.

 

Let‘s add a primary key to our query

 

  1. In the primary key tab in the exception manager deselect the all check and only check the {columnName} column. We know that this column is unique and therefore is a great column to choose as our primary key.



 

Deploy your Query

 

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

  1. Let's first make sure that we have saved our changes by pressing the Save button.
  2. Press the deploy button in the top panel.


  3. If Test environment is enabled, select the correct environment deployment, otherwise skip this step.
  4. Write a comment, detailing the changes being deployed, for example, Monitor sales with discount 30% or larger query created.



  5. Make sure to check the line Monitor sales with discounts 30% or larger and press deploy

 

Final results


Congratulations! You have now created a query that monitors sales with discounts 30% or larger. You Formatted the columns to be more readable, used the Exception Manager to track and send an email every time new exceptions are found, and finally added this newly created query to a schedule that runs this query every day at 1 pm.