Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Business Rules Extension in Master Data Services 2016


By:   |   Read Comments (3)   |   Related Tips: More > Master Data Services


SQL Server Conference Giveaway - click to learn more


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.

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

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)

)
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

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

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, June 05, 2017 - 6:30:36 PM - Steven J Neumersky Back To Top

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

Hi Frederik,

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

Regards,

Koen


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

 

 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. 

https://social.technet.microsoft.com/Forums/sqlserver/en-US/cae481e2-22da-47d6-b4f4-8fbbedca1066/mds-validation-issue-waiting-for-revalidation-not-changing?forum=sqlmds

Would be interesting to hear if you have any Ideas. 

 

 


Learn more about SQL Server tools