Business Rules Extension in Master Data Services 2016

By:   |   Comments (14)   |   Related: > Master Data Services


Within Master Data Services (MDS), you can create business rules to enforce certain business criteria upon your data. You can validate the data in your MDS entities by using your own business logic. In SQL Server 2016, an important addition was made to this functionality: the ability to create pre-defined conditions and actions using SQL scripts. These scripts are called Business Rules Extensions.


The concepts of business rules and how to create them are explained in the tip Create Business Rules in SQL Server Master Data Services and the tutorial SQL Server Master Data Services Business Rules. Keep in mind that the tip still uses the old business rule designer used in versions before SQL Server 2016. As with most other components in Master Data Services 2016, the business rules also have gotten an extensive layout update.

In this tip we'll focus on extending business rules by using custom SQL scripts. Using these user-defined conditions and actions, business rules become quite more powerful and flexible.

Test Set-up

Before we can create a business rule, we first need an entity. I created a simple entity in my model. It contains a text attribute called City and a date attribute called ValidFrom.

Create a new entity in Master Data Services

The idea is simple: if there is no ValidFrom date specified, the business rule will set it to the current date. Think of it as a default constraint in SQL Server. However, if we take a look at the out-of-the-box conditions, there is no check to see if an attribute is empty:

Predefined conditions in Master Data Services

Let's create such a check with a custom condition.

Creating a user defined condition in Master Data Services

A user defined business rule condition is defined in the MDS database as an scalar function in the usr schema. There are some prerequisites:

  • The function needs to be defined in the usr schema, as mentioned before
  • The return value type must be BIT
  • Only the following data types are supported for the parameters:
    • DECIMAL (precision must be 38, scale from 0 to 7)

Here, we are going to create the following function which check if the supplied date value is empty or not:

CREATE FUNCTION [usr].[IsDateEmpty] -- must be defined in usr schema
 (@Value DATETIME2) -- pass along an attribute of the datetime data type
 -- check if date value is empty by trimming it. If an empty string is returned, the date is empty and NULL will be returned.
 SET @Value = NULLIF(LTRIM(RTRIM(@Value)), N'');

 IF @Value IS NULL -- date is empty
 RETURN 0; -- date is not empty

user defined condition in MDS database

Checking if a field is empty is one thing, but we still need to act on it. For this, we need a custom action.

Creating a user defined action

A user defined business rule action takes the form of a stored procedure in the MDS database. There are also some prerequisites:

  • The stored procedure needs to be defined in the usr schema as well
  • The procedure has a fixed list of parameters:
    • @MemberIdList of the user defined table type mdm.MemberID. MDS will put all of the members selected by the business rule - for which there needs to be action taken - in this table. This allows you to perform the action on all members at once.
    • @ModelName, which is the name of the model
    • @VersionName, which is the version used of the model
    • @EntityName, which is the name of the entity
    • @BusinessRuleName, which is the name of the business rule

Since you pass along a lot of information about the business rule that called the stored procedure, it's possible to create generic actions that can be reused over different business rules and entities. In our example, the stored procedure takes the following form:

CREATE PROCEDURE [usr].[SetCurrentDate]
 (@MemberIdList  mdm.[MemberId] READONLY -- memberID is a user defined table data type.
            -- This parameter will pass along a list of members for which the action needs to run.
 ,@ModelName   NVARCHAR(MAX)
 ,@VersionName  NVARCHAR(MAX)
 ,@EntityName  NVARCHAR(MAX)
 ,@BusinessRuleName NVARCHAR(MAX)


INSERT INTO [stg].[TestBusinessRule_Leaf]
  0   -- import type 0 = inserting or updating members
 ,N'Business Rule Extension Test'
 ,Code  -- code of the member, supplied by @MemberIdList
 ,GETDATE() -- set ValidFrom to the current date
FROM @MemberIdList;

-- run the staging batch job to process the staging records
EXEC [stg].[udp_TestBusinessRule_Leaf]
   @VersionName = @VersionName
  ,@BatchTag  = N'Business Rule Extension Test';

user defined action in MDS database

In this example, the stored procedure inserts the members with an empty ValidFrom data into the staging table of the entity, along with the GETDATE() function to get the current date. Since the staging table is hardcoded, this business rule action would only work for one single entity. If you want more reusable actions, you'll need to incorporate more logic into the stored procedure.

We can now finally create our business rule.

Creating a business rule with custom extensions in MDS

In the System Administration panel, go to Manage and then Business Rules. Select the correct model and entity, and then select Add. Enter a name and a description for the business rule. Click on Add by the If keyword to add a condition. In the list we can now see our own custom condition IsDateEmpty in the section user defined scripts.

User defined condition in Master Data Services

After selecting the condition, you'll get the following screen:

User defined condition selected in Master Data Services

Make sure to click somewhere inside the parameter box. This will change the layout and allow you to assign an attribute to the parameter of the user defined script. It's probably a bug that this doesn't happen automatically.

User defined condition parameter configuration in Master Data Services

You have to assign a value to the parameter, otherwise a blank value is passed along and the business rule wouldn't function. There are three options for the value type:

  • Blank (the default) - You probably want to avoid this setting by explicitly passing a value to the parameter.
  • Attribute - Here we define which attribute of the entity is passed to the parameter. In the screenshot above, we configured it to be the ValidFrom attribute.
  • Attribute Value - Here you specify a hardcoded value for the parameter. Since the parameter in this example has the datetime2 data type, a valid date would be expected.

Once the parameter is configured, you can click on Save. The business rule editor now has the following form:

User defined condition configured in Master Data Services

If at some point, you want to change the configuration of the action, you can right-click on it and choose edit from the context menu.

User defined condition context menu in Master Data Services

Now it's time to configure our custom action. Click on Add by the Then keyword. In the dropdown for the operator field, you can now find the SetCurrentDate action in the user defined script section.

User defined action in Master Data Services

There is no need to configure the action, as the stored procedure has fixed parameters which are all populated by MDS itself. The business rule is now configured and we can hit Save.

Business rule with extensions configured in Master Data Services

It's possible that you receive an error message when the business rule is saved. Something like "the business rule contains corrupted rule item(s), as corresponding SQL Script has been changed". This doesn't make any sense of course, since the script hasn't changed at all. If you would edit the condition again, you'd get a warning message that you have to remap the parameter value because the function is corrupt.

Business rule bug in Master Data Services

However, this is a known bug. If you have installed SQL Server 2016 service pack 1 (but with no further updates), it's possible you encounter this bug. Make sure you upgrade to the latest CU or hotfix. You can find more information about this bug in the KB article FIX: Errors when you try to save MDS business rules in SQL Server 2016.

If the business rule is saved correctly, you can publish it.

Business rule published in Master Data Services

Let's test the rule by adding some data using the Excel add-in. I entered three cities. Two have no ValidFrom date, one has.

Enter test data in Master Data Services

Let's publish the data to the MDS service.

Publish test data in Master Data Services

The result looks like this:

Published test data in Master Data Services

As you can see, the process of publishing member from the Excel add-in will also automatically validate the members using the business rule. This means that the ValidFrom attribute is set to the current date for the members where ValidFrom was empty. In the screenshot, you can see that the validation status is now set to "Waiting for revalidation". The member where the ValidFrom field was already populated has the status "Validation Succeeded". You can manually trigger the validation of members by the business rules in Excel by clicking Apply Rules.

Apply business rules in Master Data Services

This is useful if the entity has already members before the business rule was created: the rule will go over the existing members and set the ValidFrom field if necessary. When the rule has been applied again, all members have the status "Validation succeeded", since the ValidFrom field had valid dates for all members.

Validation succeeded in Master Data Services


With business rules extensions in Master Data Services 2016, we can define custom conditions and actions for our business rules. To do this, we create user defined scripts in the MDS database. A custom condition takes the form of a scalar function, while a custom action is a stored procedure. Since an action is a procedure, we can incorporate complex business logic if we want to, such as starting SQL Server Agent jobs, SSIS packages, sending emails and so forth.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

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

View all my tips

Comments For This Article

Friday, August 5, 2022 - 8:48:44 AM - Digven Back To Top (90350)
This post is good enough to for understanding user defined script.

My Issue:
Within SetCurrentDate stored procedure, I am not able to use any other table name (for adding more logic). Only objects with STG schema let the business rule work or else Business Rule fails. Not sure on this access issue becuase only one schema works.

has anyone come across this issue ?
Inserting below code will let alter the PROC but will not let Business Rule validate records successfully.
INSERT INTO mdmTable_TEST (GoalKey1, GoalType)
values( 2022, 'New row test4 on city')

Whereas inserting below snippet do work:
INSERT INTO stg.Table_TEST (GoalKey1, GoalType)
values( 2022, 'New row test4 on city')

Friday, May 25, 2018 - 2:57:51 AM - Koen Verbeeck Back To Top (76022)

Hi Saru,

I don't think rolling back can be accomplished with business rules. I think in this scenario, change sets might be more appropriate. With a change set, an administrator has to approve any changes. The admin can then reject the change set if your conditions aren't met.

Wednesday, May 23, 2018 - 6:43:39 PM - Saravanan Radhakrishnan Back To Top (76013)

Hi Koen,

Excellent write-up.  I am using MDS 2016 and have the following requirement.  Please let me know if this requirement can be achieved by a business rules.  The requirement I have is similar to a rollback situation.

I have an entity where we store CA state zipcode and other relevant information.  Couple of attributes in this entity are (RowEffectiveDate and RowExpirationDate).  It is possible to have more than one member for a given ZipCode.  But each member will have a different value for RowEffectiveDate and RowExpirationDate.  Please refer to the example below.

ZipCode RowEffectiveDate RowExpirationDate
90049 01/01/2019 12/31/9999
90049 07/01/2015 12/31/2018

I need to be able to prevent the member from changing all attributes value if the RowExpirationDate <> '12/31/9999'.  If an attempt is made, I need to rollback that change using the business rule.

Is it possible to implement rollback kind of business rule in MDS 2016 or later?


Saravanan Radhakrishnan (Saru)

Tuesday, January 23, 2018 - 2:08:02 AM - Koen Verbeeck Back To Top (75012)

Hi Mary,

I haven't used versioning yet with user defined functions. I've got this question twice now this week, so I'm asking around if someone knows the answer.


Monday, January 22, 2018 - 9:57:56 AM - Mary Back To Top (75005)

 Hi Koen,

Thank you for this excellent write up!

First, I have a tip for Chris, who posted a question on 11/8/17...we had the same issue.  The usr schema needed to be granted to mds_br_user. 

Have you ever combined Vesioning with Business Rules that call functions?  If you are using versioning, your example of calling [usr].[IsDateEmpty] will not know which version of the data you are working with.  But the stored procedure DOES get the version passed to it by MDS, so it all works.

My example:

I have created a function that selects from the subscription view and returns either a 0 or 1, which I can then test for "is not valid" in the business rules.

But if I use versions, I need to know WHICH subscription view from which to select. 

For example, let's say that your ValidFrom Date needs to be between 2 dates that are stored on another entity.

In the function, i can select from another a subscription view:


Then, in MDS, I can test for the result of the function "is not valid" and the business rule with fail (and users see a red !)

This works great.  Until we implement versioning.

We want to use the flags in versioning to manage what the Subscription View includes.

The only way I can see around this is to have 1 subscription view for validated data to go to our ETL process and a separate subscription view for my business rules.  This is quite cumbersome.

If the function could "know" which version it is dealing with, just like a stored procedure, then I wouldn't have to do this.

Have you ever seen this issue?

Thanks in advance!


Tuesday, November 28, 2017 - 9:47:44 AM - Koen Verbeeck Back To Top (73336)

Hi Amit,

in the business rule editor, you can choose "isn't equal to" in the IF expression.
Then you can configure that the member Bank_ID shouldn't be equal to Src_System_ID.

After that you have to choose an action of course.

Tuesday, November 28, 2017 - 4:55:22 AM - Amit Back To Top (73320)


I have a Bank Entity in my MDS model which I have created in MDS 2016 recently, I need to make a business rule for a MDS user so that he shouldn't be able to enter Bank_ID and Src_System_ID as a duplicate combination.

i.e. BOA, X and BOA, Y should be allowed, but BOA,X and BOA,X should not be allowed.


I want to make these two columns as key for both scenarios:

1. While importing into MDS

2. While being added/updated by a business user


Friday, November 10, 2017 - 5:33:36 AM - Koen Verbeeck Back To Top (69511)

Maybe you can check the event log, the MDS trace or even SQL Server Profiler to see if there are any errors popping up? If not, it's time to raise a bug with Microsoft.

Wednesday, November 8, 2017 - 8:10:52 AM - Chris Reaves Back To Top (69433)

 i followed these prerequisites

  • The stored procedure needs to be defined in the usr schema as well
  • The procedure has a fixed list of parameters:
  • @MemberIdList of the user defined table type mdm.MemberID. MDS will put all of the members selected by the business rule - for which there needs to be action taken - in this table. This allows you to perform the action on all members at once.
  • @ModelName, which is the name of the model
  • @VersionName, which is the version used of the model
  • @EntityName, which is the name of the entity
  • @BusinessRuleName, which is the name of the business rule


Yes i am using MDS 2016 on SQL Server 2016 SP1 CU2, no errors on the screen, just nothing appears under user defined scripts


Tuesday, November 7, 2017 - 5:15:23 PM - Koen Verbeeck Back To Top (69392)

Hi Chris,

did you follow all of the prerequisites? (it's quite a list)
Stupid question maybe, but you are using MDS 2016 or later?

Are there any errors?

Tuesday, November 7, 2017 - 4:06:16 PM - Chris Reaves Back To Top (69387)

 When creating a new stored procedure in the USR schema and the parameters above, these user defined function and procedures do not show in the list of Conditions or Actions in Business Rules


Monday, June 5, 2017 - 6:30:36 PM - Steven J Neumersky Back To Top (56803)

Hey, it's no DataFlux......but, it's also not $500,000 either! Still got a way to go to play with the big boys, but MDS is making some strides.

Friday, February 17, 2017 - 5:10:18 AM - Koen Verbeeck Back To Top (46434)

Hi Frederik,

I saw you got your answer in the MSDN thread. I hope everything is resolved now.



Tuesday, February 14, 2017 - 9:19:39 AM - Frederick Venter Back To Top (46323)


 Hi Koen, 

I implemented a couple of business rules using the Extension functionality,  I did however have some issues with validating the model. The business rule worked fine, but I was not able to validate the model through the Web interface, which is a requirement for our data stewards. 

I have an extended explainatio of the issue on the Technet MDS forum. Don`t want to re-type everything here.

Would be interesting to hear if you have any Ideas. 



get free sql tips
agree to terms