THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Master Data Services Team

Blog for the Microsoft SQL Server Master Data Services (MDS) team. Blog posts are contributed by various team members.

What's New in Master Data Services - SQL2016 CTP2.4 (Sep) Release

SQL Server 2016 Community Technology Preview 2.4 is publicly released and the latest SQL Server bits can be found at http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.

MDS Excel Add-in can be found at : https://www.microsoft.com/en-us/download/details.aspx?id=47343

In CTP2.4 MDS introduced feature to extend Business Rules conditions and actions.

Business Rules Extension

Business rule is a rule that you use to ensure the quality and accuracy of your master data. You can use a business rule to automatically update data, to send email, or to start a business process or workflow. The system only provides a certain number of pre-defined conditions and actions. We are introducing Business Rule Extension in SQL Server 2016, which enables customers to define more flexible and powerful Business Rules. Users can create their own rules for both conditions and actions.

Create your own conditions

SQL functions that match following criteria can be used as Business Rule conditions.

  • Must be defined under [usr] schema.
  • The return value type must be BIT.
  • Only following types are supported for parameter types.
    • NVARCHAR
    • DATETIME2
    • DECIMAL (precision, scale)
      • precision must be 38
      • scale must be a value from 0 to 7

Example

CREATE FUNCTION [usr].[IsDateEmpty]

(

@Value DateTime2

)

RETURNS BIT

AS BEGIN

SET @Value = NULLIF(LTRIM(RTRIM(@Value)), N'')

IF @Value IS NULL

BEGIN

RETURN 1

END

RETURN 0

END

 

Create your own actions

SQL stored procedures that match following criteria can be used as Business Rule actions.

  • Must be defined under [usr] schema.
  • Only contain following parameters
    • @MemberIdList mdm.[MemberId] READONLY
    • @ModelName NVARCHAR(MAX)
    • @VersionName NVARCHAR(MAX)
    • @EntityName NVARCHAR(MAX)
    • @BusinessRuleName NVARCHAR(MAX)

Example

CREATE PROCEDURE [usr].[SetCurrentDate]

(

@MemberIdList mdm.[MemberId] READONLY,

@ModelName NVARCHAR(MAX),

@VersionName NVARCHAR(MAX),

@EntityName NVARCHAR(MAX),

@BusinessRuleName NVARCHAR(MAX)

)

AS BEGIN

INSERT INTO stg.Product_Leaf (ImportType, BatchTag,Code,CreatedDTM)

SELECT 0, N'BR', Code, GETDATE()

FROM @MemberIdList

EXEC[stg].[udp_Product_Leaf]

@VersionName = @VersionName,

@BatchTag = N'BR'

END

 Script actions will be executed as mds_br_user which has following permissions  

Schema

Permissions

mdm

SELECT

stg

SELECT, UPDATE, DELETE, EXECUTE, INSERT

usr

FULL

 

Create a Business Rule with script conditions and script actions

Let’s create a business rule against Product entity that updates the CreatedDTM to current date if the value is not set. You can get the Product entity from Sample Packages.

Navigate to Business Rule Manage page after create script condition and action using sample code above. Choose Product model and Product entity and then click Add Button, now a new empty Business Rule is created.

image

Then click Edit button, we can find IsDateEmpty and SetCurrentDate are shown on the components panel.

image

Now let’s drag “ IsDateEmpty” to IF panel on the right, and then drag CreatedDTM from Attributes Panel to Edit Condition Panel. Click Save button to save the condition.

image

In order to create the action, just drag SetCurrentDate to Then panel and click save on the Edit Action Panel.

image

After creating the Business Rule, click top left Back button to go back to the management page and click “Publish business rules” button to activate the rule.

Now let’s test the Business Rule we just created.

1. Choose Product model and an open version in the main page

2. Click Explorer Task button.

3. Choose Product entity from Entities menu.

4. Click Marketing attribute group

5. Click Add Member and enter “BB-1111” in Code field.

6. Click Ok button

7. Now we can see the CreatedDTM for Product “BB-1111” is set to current date.

image

For more information, see Business Rules Extension (Master Data Services).

Published Thursday, October 15, 2015 1:57 PM by MDS Team

Comments

No Comments
New Comments to this post are disabled
Privacy Statement