SQL Server Master Data Services Business Rules




By:
Overview

Once the data is stored and organized as per the structure and requirements of the business, the data can be published to the subscribers and made available for consuming in client applications. But before the data is published, an important step in the entire process is to validate the data against business rules. For example, when a product is created certain attributes cannot be blank. If a product has been added with certain mandatory attributes having blank values, the business process may want to mandate certain actions in response to this. In order to have such a repository of business rules, the rules have to be created in Master Data Services (MDS). In this chapter we will look at how to create business rules and how these are used during validation.

Explanation

We installed the ProductsSample model after the MDS installation. We will be using this model to discuss business rules. Follow the below steps.

In Master Data Manager, open System Administration, click on Manage menu and select the Business Rules menu item. Select the ProductsSample model, Product entity and you should be able to see a list of business rules defined for this entity.

Business Rules defined for an Entity in SQL Server Master Data Services

Click on the Add button to create a new business rule for the Product entity. This should bring up a pop-up window as shown below. The structure of a business rule in MDS is if-then-else. We need to specify conditions as well as actions based on the condition outcome. You can also opt to send email notifications to any individual user or a group of users. The Excluded option signifies whether this business rule would be excluded during validation.

Add Business Rule in SQL Server Master Data Services

Click on the Add link near the If-Then-Else to add conditions and actions. Clicking on this button should bring up a pop-up window as shown below. Let's say we want to create a condition where a particular set of fields are required. In this case you can select the field name in the Attribute section, "is not equal to" in the Operator section, and "Blank" in the Is equal to section. In similar ways you can add multiple conditions for each set of fields which are required. Once the rule is created you can click the Publish All button to publish the rule and make it active.

Create Condition in SQL Server Master Data Services

We can move a rule up or down in the list of rules. This defines the order in which the rule is applied during validation. If you select the RequiredFields rule that is already created for the Product entity in the ProductsSample model, you will be able to see the definition of this rule. You can learn how to create a rule for mandatory fields by looking at the definition of this rule.

IF THEN ELSE Logic in SQL Server Master Data Services

In order to validate data against the rules, click on Version Management from the Master Data Manager home page. Click on the Validate Version menu item. Select the ProductsSample model and Version_1 in the respective dropdown boxes and click on Validate button. After the validation process is complete, you should be able to find the validation results. If you carefully study the results, you will be able to find the details of the members that failed validation along with the attribute and the associated rule.

SQL Server Master Data Services Validation Summary

In this way we can create the required business rules to validate data before the data is published to subscribers. At times you may want an approval workflow as a part of the business rule. Consider reading this tip to learn about approval workflow in MDS. In the next chapter we will look at publishing data to subscribers, which will complete the data flow cycle in MDS.

Additional Information
  • Consider studying various options available to create business rules and try to create complex business rules to gain more experience in this area. Business rules and data validation is one of the most important steps in the entire MDS data management life cycle.

Last Update: 7/18/2016




More SQL Server Solutions











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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools