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 2:30 pm.
  • Use the exMon Portal to see an overview of open exceptions and work with the exceptions.


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 insales 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 SalesOrderDetailID 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

 

Run your query


Now if we would like to simulate the execution of the query we can try running the query. 

  1. Press the Run button in the top panel.



  2. In the Run Task popup, unselect Mute all emails and choose which user you would like to receive the email in this demo run. Just to see how they would look like.

     
  3. Finally press Run Task. You can see if it was successful or not in the output window at the bottom panel.




Add To schedule


Schedule Groups are used in exMon to schedule automatic executions of objects. You can easily create your own custom schedule group but in this demo, we will use a pre consisting Schedule Group which is located in the properties pane within the query.


  1.  Find the Query Properties panel to the right
  2.  Press the Schedule dropdown > select option  Every Day
  3.  Press Save



This means that our query will be run every day (at 14:30 which is the default).


Use exMon Continuous Monitoring Portal to see and work with open exceptions


In the exMon Portal users get an overview of open exceptions, work with exceptions and analyze exceptions trends.


Getting started with the Portal


To open the portal Navigate to the URL provided by your exMon administrator or simply press the Open exMon Portal in browser from the exMon Start Page.



  1. Select the Exception Manager option  from the module selector
     

  2.  Select the system Demo Sales from the system selector.


 

Exception Overview


The main entry point into the portal is the Overview pageThe overview displays information about all queries with open exceptions in the current system.


Columns in the overview are the following:

  • Title:  Names of queries (Tests) categorized into Data Types.
  • Open Exceptions: Number of open exceptions, exceptions that need to be acted on.
  • Last Update: Date of latest update of the Query.
  • Max Age: Age of the oldest open exception of the Query.
  • Amount: Sum of the “Amount at Risk” column for all open exceptions.
  • Weeks: Distribution of open exception by age.


Exceptions

If you click the Query title of our new Query Monitor sales with discounts 30% or larger on the Exception Overview page you will get a list of all open exceptions for a Query.



At the top, we have summary information about the selected Query and its exceptions.

  • Description: Description of what the Query is monitoring.
  • Action: Description of what actions should be taken to fix the data quality issue.
  • Total Amount: Sum of “Amount at Risk” for open exceptions.
  • Open Exceptions:  Number of open exceptions.
  • Snoozed Exceptions: Number of snoozed exceptions. That is exceptions that are open but temporarily hidden from the exception page. Snooze is covered later in this article.
  • Oldest Exception: Age of the oldest open exception in the Query.
  • Last Run | Rerun: The date when Query was last executed. If you have enough privileges, you can choose to execute the Query with the Rerun button.
  • Owner: Query Owner as defined for the Query, the person who is responsible for the Query.
  • Data Type: The data type the Query is in.


Exception Actions


You can perform four actions on each exception:

  • Mark as Fixed
  • Snooze
  • Assign To
  • Not an Exception.



When you select an exception the action buttons are enabled.


Mark as Fixed

By marking an exception as fixed, exMon treats the exception as it has been fixed and hides it from the list of open exceptions. When the Query is executed next, the exception will stay closed if not found, or reopened if it is still found in the source system.


Snooze

Snoozing an exception temporarily hides the exception until a specific date. This can be useful if solving the exception depends on an external person or events.

If the Query is executed while an exception is in the snoozed state, and the exception is not found in the source system, then the exception will be closed.


Assign To

In exMon, all exceptions are assigned to a specific user or a group, which is then responsible to work on the exception.
You can assign exceptions to another user, or take ownership of an exception, with the Assign To action.

 

Not an Exception

When marking an exception as "Not an Exceptions", exMon will close the exception and never open it again, even if it is still present in the source system.


Your task is to play around with these buttons and make sure to at least:


  1. Mark one exception as Fixed
  2. Snooze one exception
  3. Assign one Exception to someone else
  4. Mark one as not an exception
  5. Observe how 


By default, the exceptions grid only displays open exceptions. You can use the Show Filters button to view exceptions in other states.

Select between Open, Closed, Snoozed and Not an Exceptions.

  1. Refresh the portal
  2. Try to play around with the filters to see different data based on your filtered option.



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 2:30 pm.

When the query had been deployed we used the exMon Portal to get an overview of open exceptions and work with the exceptions.