By: Koen Verbeeck | Comments (14) | Related: > Master Data Services
Problem
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.
Solution
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.
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:
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:
- NVARCHAR
- DATETIME2
- 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 RETURNS BIT AS BEGIN -- 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 BEGIN RETURN 1; END RETURN 0; -- date is not empty END
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)
)
AS
BEGIN
INSERT INTO [stg].[TestBusinessRule_Leaf]
(ImportType
,BatchTag
,Code
,ValidFrom)
SELECT
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';
END
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.
After selecting the condition, you'll get the following screen:
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.
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:
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.
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.
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.
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.
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.
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.
Let's publish the data to the MDS service.
The result looks like this:
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.
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.
Conclusion
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
- For more information on MDS business rules, please refer to the following tips:
- Create Business Rules in SQL Server Master Data Services
- SQL Server Master Data Services Business Rules
- You can find more Master Data Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
About the author
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 MSSQLTips.com. 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
