未加星标

Business Rules Extension in Master Data Services 2016

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

By:Koen Verbeeck || Related Tips:More >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 .


Business Rules Extension in Master Data Services 2016

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:


Business Rules Extension in Master Data Services 2016

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
Business Rules Extension in Master Data Services 2016

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
Business Rules Extension in Master Data Services 2016

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 .


Business Rules Extension in Master Data Services 2016

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


Business Rules Extension in Master Data Services 2016

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.


Business Rules Extension in Master Data Services 2016

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:


Business Rules Extension in Master Data Services 2016
If at some point, you w

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerTIASBRIMFUDUCTI
分页:12
转载请注明
本文标题:Business Rules Extension in Master Data Services 2016
本站链接:http://www.codesec.net/view/530796.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(49)