Exception Management is a feature that can be enabled for Compare Queries and Queries. Without Exception Management, queries return and report the whole result set and has no memory of previous executions. With Exception Management, exMon treats each row from the results as a specific exception, logging it to a database and only notifies responsible users of new exceptions. All exceptions are visible on the exMon Portal, where users can work with exceptions and analyze trends. You can read all about working with exceptions on the portal in our tutorial about Using the exMon Continuous Monitoring Portal. This tutorial contains the following sections:
- Exception Types
- Enabling Exception Manager
- Exception Process types
- User Mapping
- Column Mapping
- Dimension Mapping
- Email Properties
- Primary Key
- Clearing Exceptions
There are mainly two types of exceptions:
- Actionable exceptions: Exceptions that can be fixed and the goal should be to fix all actionable exceptions. Example of actionable exceptions are: products without a price and discrepancy in customers information between systems. exMon will monitor the lifespan of exceptions, when they were created, when they were fixed and who worked on the exception. Query owners can use reports to analyze how long exceptions are open, what progress specific users are making and overall health of their system.
- Informational exceptions: Exception that can’t be fixed after they occur. For example; sales with an unusually high discount or employee that credited the fee of an invoice. The goal with informational exceptions is to monitor and look out for trends and patterns and see what is causing these exceptions. And in turn to change employee behavior.
Enabling Exception Manager
To enable Exception Management for a Query or Compare Query, open the Exception Manager Properties dialog and check Enable Exception Manager. By enabling exception management the basic functionality of exception management is active and after running the query, users can view exceptions on the exMon Portal. There are however multiple options for configuring Exception Manager. This section covers each option.
Exception Process types
- Exception Process: This is default option and is used in most queries. exMon handles results from queries as exceptions to a business rule. Exception Process will be he subject of this tutorial.
- Approval Process: With the Approval Process, exMon handles results from queries, not as an exception but as something a responsible user has to approve. An example is to notify a system owner that a user has gained administration privileges on his system. The system owner will have to approve that change and that approval is audited.
All exceptions are assigned to a user or a group, that is responsible in acting on that exceptions. By default, all exceptions are assigned to the Query Owner, selected in the properties pane. We can configure exMon to assign each exception to specific users according to information in the exception data. This could for example be, in a query returning all sales without a price, a column which specifies the sales person. To configure user mapping, choose a Mapping Type and Mapping Column. Mapping Types exMon comes with three mapping types; Active Directory, Email and Expectus User Id. You can also add your own external mapping type, as described below. After choosing a mapping type, choose the column containing the user data. After configuring user mapping, exMon will automatically map each exception to users according to the mapping. If a map is unsuccessful, that is, exMon doesn’t find the user, then the exception will be assigned to the Query Owner. When using Email and Active Directory mapping types, you can provide the optional flag “--autocreateusers”. exMon will then automatically create users that are not found, and assign the exceptions. External Users If you are not able to use Active Directory or Email mapping you can specify a custom mapping type. This can for example be used to map user ids from your ERP system to exMon. To create a new external user mapping source, open Configuration > External Users and create a new External System by clicking the Edit External Systems toolbar button. Then add each user into the list, by adding the source system’s id and map to an exMon user. After adding the external mapping type, AdventureWork Sales, we can choose it from the Mapping Type dropdown in our Query. We can test our user mapping with the Preview mode. In the Exceptions tab we can see that exMon will assign exceptions to different users according to the data in the SalesPersonID column. Person 10, Person 8 and Person 9 are all users in exMon.
With column mapping you can add optional attributes to exceptions that help users to work with them. Attributes:
- Amount: Often called “Amount at Risk”. With the Amount mapping, you can set a dollar amount to each exception. For example, if we need to fix an issue with a an invoice that has Total Due of $100, then we can use this information to help us prioritize what exceptions we should focus on, which is often the exceptions with the highest “Amount at Risk”. This column does not apply to all Queries, but is beneficial to use when possible.
- Category: Category mapping maps a column which can be used to categorize exceptions by some common attribute. Portal users can analyze exceptions by the category and this information is also often used when creating reports in exMon.
- Unique Id: Similar to the category mapping and is also used in offline analysis and reports creation.
- Start Date: When exMon finds new exceptions, it marks it’s creation date as the time when the Query is executed. If the Query includes date of creation for each row, then you can map that column to Start Date and exMon will use that column to set the creation date. This can be useful when the schedule of the Query is once a week but we would like to get precise dates when the exceptions were created in the source system.
Assigning exceptions to dimensions is used to see all exceptions assigned to an entity, like Customer, across all Queries. This is very useful when there are multiple Queries monitoring exceptions connected to customer, and we want to see how exceptions are distributed to customers. You can read more about dimensions on the portal in our guide: Using the exMon Continuous Monitoring Portal. In our example below we have defined three Dimensions; Customer, Product and Sales Persons. Dimensions are created and configured in exMon Administrator under General > Dimensions. When defining a dimension, you need to provide a Data Provider, a query that gives us a unique id and a name, and optional attributes to categorize the dimension further. The unique id is mapped to Business Key and the name to Business Value. You then need to execute the dimension or add it to a schedule. Below is an example of how a Customers dimension is defined and the columns mapped to the Business Key, Business Value and attributes. Each customer has a State Province Name which is in a Country Region. For example Carol Ann Rockne is in England, United Kingdom.
The ability to send out email with exceptions is a powerful way of notifying responsible parties about new or open exceptions. Although powerful, it is important not to overuse email notifications as they quickly become spam for the user. We encourage our customers to only use email notifications with high priority exceptions and exceptions that are time sensitive. Normal exceptions can be analyzed and worked with on the exMon Portal. Here are email notification guidelines that have worked well for our customers. Notify by email for:
- High priority exceptions: The exceptions are high priority and should be dealt with immediately.
- Time sensitive: The exceptions are time sensitive. It is too late to deal with the exceptions later. For example exceptions from a query looking for bad addresses in outgoing orders.
- Infrequent exceptions: When users only get a handful of exceptions each week and are not regular users of the portal it makes sense to notify them by email.
You can configure email configuration in the Emails tab in Exception Manager Properties. Enable emails on new exceptions by checking “Send emails on new exceptions”. By default, all exceptions are sent to the Query Owner. You can change that behavior with User Mapping, as described earlier in this guide. In our example, we have enabled “Send email on new exceptions”. With Preview, we can see the emails exMon will send out. As we have enabled User Mapping, exMon will send out three emails, to our demo users: Person 8, Person 9 and Person 10, informing them of the exceptions they have been assigned to.
You can choose an appropriate email template with the Email Template dropdown. With email templates you can customize the look and design of your emails. They can be added and edited under General > Email Templates and use HTML with certain placeholders to control the email generation. We advice you to create email templates matching your corporation’s branding.
CC Email Addresses
In cases when multiple users need to be notified of exceptions, you can use CC Email Addresses and CC Email mapping. If you have a fixed list of email addresses, you can add them in “Also send all emails to these CC addresses”. This is useful, for example, when you have supervisor that needs to be notified of all exceptions for a specific Query. If you need a more dynamic list of email addresses, you can use CC User mapping which works in the same way as User Mapping, described above.
There are few advanced email options: Allow reply to comment Allow reply to comment allows the recipient of an email, to reply to the email with a comment that will be added to the activity log of the exception. In the email, there will be two extra columns, where the user can write into on reply. exMon will interpret the email and mark the exceptions accordingly. Note: For “Allow reply to comment” to work, exMon has to be configured with a mailbox were exMon can receive emails. Link to each exception When checked, exMon will add the column “View on Portal”, with a link to the specific exception on the exMon Portal. When clicked, users will get more detail about the exception, have the ability to assign it to other uses, write a comment and make other actions. Include all open exceptions when sending email When checked, emails with new exceptions assigned to a user, exMon will also include a list of all open exceptions assigned to that user. Below is an example of an email including new and open exceptions:
exMon tracks each exception in the results from a Query by creating a unique primary key from the row. It is important that exMon creates the correct primary key so that it is able to identify each exception and track its lifespan. With a wrong primary key, marking an exception as “Not an exception”, could cause some new exceptions not to show up, as exMon thinks they have already been closed. By default, exMon selects all columns, except the ones mapped to Amount and User. We highly recommend defining a custom primary key, from the set of columns that make up a unique key. In our example SalesOrderID is sufficient. In other cases you could choose multiple columns, for example, SalesPersonID and Date column. When a Custom Key is defined, it makes it easier to add additional column to the exception at later time. If Primary Key it Automatic, then adding new columns would recreate the key of all open exceptions, resulting in closing them all and reopening them as new exceptions. With a Custom Key, you are able to add columns without changing the key.
There are few advanced options that can be useful in some cases. Severity Severity is used to prioritize the Queries into groups of High, Medium, Low and Informational exceptions. The informational priority is used to distinguish Queries that produce exceptions that are non actionable and can’t be fixed. Informational exceptions can be filtered out in the exMon Portal to view only the actionable exceptions. Exception Detail Normally each row from the Query is handled as a individual exception. With Exception Detail you can configure exMon to only create one exception if the Query returns results. This can be useful in cases were there is a single reason for all the exceptions, and it can be fixed all at once. Automatically close Exceptions Automatically close Exceptions, when they are not found in the query results is a key feature in exMon and by enabling it, exMon will automatically close (mark as fixed) exceptions that are not found anymore. This saves users a lot of time and gives a clear picture of the current status of open exceptions. This is enabled by default but can be disabled in cases were not wanted. Those cases include when the Query is monitoring only the past few days in transactional data. Exception Template When browsing exceptions across multiple Queries in the exMon Portal, an exception summary is used instead of the detailed view. The exception summary is generated from an exception template which is automatically generated. You can override the template to omit columns or add extra information.
When developing Queries you often need to “start from scratch” and clear all exceptions. To clear exceptions use the “Clear Exceptions” button in the toolbar. When clearing exceptions, all exceptions that this Query has created will be deleted from exMon and the Query will start fresh on next execution. You can choose to clear from Dev, Test or Prod.