Are you implementing your first Master Data Services (MDS) model using SQL Server 2014 MDS? Do you want to ensure that users cannot enter data that does not comply with certain business requirements? In this blog post, we will explore how you can achieve this by implementing data validation rules in SQL Server Master Data Services.

Overview of SQL Server MDS Business Rules

In MDS, you can implement Business Rules and apply them to the entities. These rules are similar to constraints in SQL Server, such as default and check constraints. They provide flexibility in enforcing business requirements and preventing incorrect data entry.

The MDS Business Rules interface may not be very intuitive, but it offers powerful features for creating and managing data validation rules. Let’s take a closer look at how you can create a simple rule.

Creating a Data Validation Rule

When creating a data validation rule in MDS, you will work with the Business Rules Maintenance screen in the MDS Manager. Here are the steps to create a rule:

1. Select the model for which you need to create or modify the Business Rules.
2. Choose the entity under the selected model. Note that Business Rules are not shared between entities.
3. Click on the “Add business rule” icon to create a new rule. A new line with a generic name will be created.
4. Double-click on the rule’s name to change it to a more descriptive name.
5. On the left side of the Business Rule designer, you will find components and attributes to build the rule. On the right side, you can build the expression for the rule.
6. Specify the conditions for when the rule should be validated by dragging logical operators and conditions from the Components section.
7. Drag the attributes that need to be validated to the Edit Condition area and set the desired conditions.
8. Specify the actions to be taken if the rule is violated by selecting the appropriate action type from the Actions section.
9. Drag the attributes that need to be updated or validated to the Edit Action section and set the desired values or conditions.
10. Save the rule and repeat the steps for any additional conditions or actions.
11. Once the rule is created, activate it by clicking on the “Publish business rules” icon.

Sample Business Rules Examples

Let’s take a look at some examples of Business Rules in the sample “Product” model provided by Microsoft:

• Required fields rule: This rule ensures that certain fields are mandatory and does not have any specific conditions.
• DaysToManufacture rule: This rule validates the value of the DaysToManufacture attribute when the InHouseManufacture attribute is equal to “1”.
• Std Cost rule: This rule validates that the value of the StandardCost attribute is always greater than “0”.
• FG MSRP Cost rule: This rule is applicable to members where the FinishedGoodIndicator attribute is equal to “Y”. It validates that two other attributes have values greater than “0”.

In future blog posts, we will explore more advanced Business Rules and explain how Business Rule validations work.

By implementing data validation rules in SQL Server Master Data Services, you can ensure that your data complies with your business requirements and prevent incorrect data entry. This helps maintain data integrity and accuracy in your MDS model.