Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

SQL Server Master Data Services Business Rules Notifications


By:   |   Read Comments   |   Related Tips: More > 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):

Notifications Settings in MDS Configuration Manager
Database Mail Profile and Account settings

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:

Setting email in user property in MDS

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

MDS Users list with emails

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:

Business rule Notification Setup

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:

 Business rule Notification Setup in MDS 2014 - edit business rule

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

Users list drop down for notifications

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:

MDS Group management

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:

Validate Version error when using security group
Apply rules error

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

Pending business rules changes

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

Notification setting error when user does not have permissions

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:
MDS Functions
  • A user or a group user to must have at least “Read” Model Permission:

MDS Model Permissions

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”:

Validate Version

Click “Validate” on the next screen:

Validate

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

Notification email example

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”:

MDS 2014 when notification is sent the Notification Statusťcolumn has value True

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

MDS 2017 when notification is sent the Notification Status column has value True

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.


Last Update:


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools