Applies To: SQL Server 2016 Preview
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
CREATE FUNCTION [usr].[IsDateEmpty]
SET @Value = NULLIF(LTRIM(RTRIM(@Value)), N'')
IF @Value IS NULL
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)
CREATE PROCEDURE [usr].[SetCurrentDate]
@MemberIdList mdm.[MemberId] READONLY,
INSERT INTO stg.Product_Leaf (ImportType, BatchTag,Code,CreatedDTM)
SELECT 0, N'BR', Code, GETDATE()
@VersionName = @VersionName,
@BatchTag = N'BR'
Script actions will be executed as mds_br_user which has following permissions
SELECT, UPDATE, DELETE, EXECUTE, INSERT
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.
Then click Edit button, we can find IsDateEmpty and SetCurrentDate are shown on the components panel.
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.
In order to create the action, just drag SetCurrentDate to Then panel and click save on the Edit Action Panel.
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.