SQL Server Master Data Services Business Rules Notifications

By: Svetlana Golovko   |   Comments   |   Related: > Master Data Services

Problem

In our last tip about Master Data Services (MDS) Business Rules we provided step by step instructions for creating a Business Rule.  Business Rules can be validated using several different methods such as version validation, by applying rules, etc.  So how can we make sure we don’t miss a failed Business Rule validation? Also, how are notifications for the Business Rules setup and configured?

Solution

You can validate Business Rules manually using MDS Explorer, Version Validation or the MDS Excel add-in. You can also automate validation of the Business Rules by using the mdm.udpValidateModel stored procedure. In this case we don’t have information about the validation results right away (as the validation process might run at night), but we can setup email notifications to review the Business Rules validation status later or without using the MDS Administrator site.  Read more about validating a version against Business Rules here.

In this tip we will be providing steps required for the notifications setup.

Master Data Services Business Rules Notifications Prerequisites

Here are the prerequisites for the Business Rules notifications setup:

1. MDS Configuration Manager settings
2. User properties
3. Business Rules notification settings
4. User permissions.

Notification Settings in MDS Configuration Manager

Before you can send any notifications an email profile has to be created using MDS Configuration Manager (item #1 on the screenshot below):

Other MDS Configuration Manager settings related to the notifications are:

• MDS URL for notifications (item #2 on the screenshot above). Use this setting if you want to navigate to the item with Business Rule validation issue right from a notification email.
• Number of notifications per single email (item #3). We will demonstrate an effect of this setting later in our tip. Just note that we have a default of 100 notifications per email.

MDS User General Settings

An MDS User needs to have an email property populated in order to receive notifications. It’s empty by default if the user’s email is not setup in Active Directory and you may need to set it up manually in MDS 2014:

You can see all users’ emails in MDS under “User and Group Permissions”:

Setting up Business Rules Notifications

For every Business Rule you can setup a user in the “Notification” field and this could be a different user for each rule.

In order to get a list of MDS groups and users for a Business Rule notification in MDS 2014 or earlier double-click the “Notification” field:

In MDS 2016 or higher you need to edit a Business Rule. Check “Send Notifications” check-box after a Business Rule is opened for editing:

 

Select a user or a group from the drop-down box for this particular Business Rule:

Note, that you have a choice to select a user or a group, but keep in mind that even though you can select a Security Group and set it up in MDS you can’t actually setup email for the group in MDS:

You might be able to use a distribution group with the email, but you will need to test it. Currently you may get one of these errors in MDS when you try to use a Security Group:

After you selected a user/group for the notification you need to publish a Business Rule. Note the status of the Business Rule below:

If a user doesn’t have minimum permissions on the Model you will get the following error:

Note, that you won’t get this error if you select a group (it looks like there is no validation here for groups).

The minimum permissions that a user must have in order to get notifications are:

• One or more function has to be assigned to the group or user:

• A user or a group user to must have at least “Read” Model Permission:

Read more about setting up Business Rules notifications here.

MDS Business Rules Notifications Testing

To trigger Business Rules notifications in our example we will validate the Product Model.

Under the MDS Administration web site go to the “Version Management”, select “Product” as the Model and click “Validate Version”:

Click “Validate” on the next screen:

Here is an example of the email that will be sent after the validation is complete:

Note that we have modified this email for demonstration purposes to show the message at the bottom. We have removed 95 records between the first and the last set of the records. You can see that this email only sent a list of 100 issues. There were 201 issues in total, but 101 of them have been truncated.

If you expect to get more than 100 issues in an email notification you can setup the number of notifications per email in MDS Configuration Manager (item #3 on the first image above in this tip – “Number of notifications in a single email”).

Email’s subject is:

• “MDS Notification : Validation Issue” in MDS 2017
• “MDS Notification” in MDS 2014.

The email has a list of validation issues with the following information:

• Model name
• Model Version
• (Business Rule) Entity
• Member Code
• Message (containing Business Rule condition and URL to the member having issue)
• Date issued (when a Business Rule validation failed).

You may notice a different behavior in MDS 2014 and MDS 2016 after validating a version. In MDS 2014 when a notification is sent the “Notification Status” column has a value of “True”:

In MDS 2017 it’s “False” (even though the notification was sent):

This might be a bug, so be aware of this.

Next Steps

• Read more about when notifications are sent here.
• Find other Master Data Services tips here.
• The steps for the sample models deployment could be found here.
• Check Microsoft resources about MDS.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips