Introduction to MDS Configuration
Master Data Services (MDS) is a non-transactional system for managing master data. This section provides a collection of sample configurations and scripts to help you implement and optimize MDS in your environment.
Common Configuration Scenarios
- Setting up security roles and permissions.
- Configuring hierarchical structures for master data.
- Integrating MDS with other SQL Server components (SSIS, SSRS).
- Implementing data quality rules and validations.
- Managing master data versions and lifecycles.
Sample Configurations
1. Basic MDS Model and Hierarchy Setup
This sample demonstrates creating a simple model for 'Products' and defining a 'Category' hierarchy.
T-SQL Script Example:
-- Create a new model
EXEC mdm.usp_CreateModel @ModelName = 'Products', @DatabaseName = 'MDW';
-- Add a hierarchy to the Product model
EXEC mdm.usp_AddHierarchy @ModelName = 'Products', @HierarchyName = 'Category', @HierarchyDescription = 'Product Category Hierarchy';
-- Add a leaf member to the hierarchy
EXEC mdm.usp_AddLeafMember @ModelName = 'Products', @HierarchyName = 'Category', @MemberName = 'Electronics', @MemberSortOrder = 1;
EXEC mdm.usp_AddLeafMember @ModelName = 'Products', @HierarchyName = 'Category', @MemberName = 'Clothing', @MemberSortOrder = 2;
-- Create a subscription view for the model
EXEC mdm.usp_CreateSubscriptionView @ModelName = 'Products';
2. Implementing Data Validation Rule (Uniqueness)
This example shows how to create a rule to ensure product names are unique within the 'Products' model.
T-SQL Script Example:
-- Assume 'ProductCode' attribute exists in the 'Products' model
-- Create a validation rule for uniqueness on 'ProductCode'
EXEC mdm.usp_AddValidationRule @ModelName = 'Products', @RuleName = 'ProductCodeUniqueness', @RuleType = 'AttributeUniqueness', @RuleJSON = '{"AttributeName": "ProductCode"}';
-- Assign the rule to the 'ProductCode' attribute
EXEC mdm.usp_AssignValidationRule @ModelName = 'Products', @AttributeName = 'ProductCode', @RuleName = 'ProductCodeUniqueness';
3. Setting Up MDS Security Roles
This script outlines the steps to create a 'Data Steward' role and assign permissions.
T-SQL Script Example:
-- Create a new security role
EXEC mdm.usp_CreateSecurityRole @RoleName = 'Data Steward', @Description = 'Role for managing Product data';
-- Assign the role to a user or group (replace 'YourDomain\YourUser' with actual principal)
EXEC mdm.usp_AssignUserRole @UserName = 'YourDomain\YourUser', @RoleName = 'Data Steward';
-- Grant permissions on the 'Products' model to the 'Data Steward' role
EXEC mdm.usp_GrantModelPermission @ModelName = 'Products', @RoleName = 'Data Steward', @PermissionScope = 'Model', @PermissionType = 'Read';
EXEC mdm.usp_GrantModelPermission @ModelName = 'Products', @RoleName = 'Data Steward', @PermissionScope = 'Model', @PermissionType = 'Update';
EXEC mdm.usp_GrantModelPermission @ModelName = 'Products', @RoleName = 'Data Steward', @PermissionScope = 'Model', @PermissionType = 'Approve';