Microsoft SQL Server 2008 R2 Master Data Services

Microsoft SQL Server 2008 R2 Master Data Services

by Tyler Graham, Suzanne Selhorn
Released March 2011
Publisher(s): McGraw-Hill
ISBN: 9780071756242

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Best Practices for Deploying and Managing Master Data Services (MDS)

Effectively manage master data and drive better decision making across your enterprise with detailed instruction from two MDS experts. Microsoft SQL Server 2008 R2 Master Data Services Implementation & Administration shows you how to use MDS to centralize the management of key data within your organization. Find out how to build an MDS model, establish hierarchies, govern data access, and enforce business rules. Legacy system integration and security are also covered. Real-world programming examples illustrate the material presented in this comprehensive guide.

  • Create a process-agnostic solution for managing your business domains
  • Learn how to take advantage of the data modeling capabilities of MDS
  • Manage hierarchies and consolidations across your organization
  • Import data by using SQL Server Integration Services and T-SQL statements
  • Ensure data accuracy and completeness by using business rules and versioning
  • Employ role-based security at functional, object, and attribute levels
  • Design export views and publish data to subscribing systems
  • Use Web services to progrmmatically interact with your implementation

Table of contents

  1. Cover Page
  2. Microsoft SQL Server 2008 R2 Master Data Services
  3. Copyright Page
  4. Contents
  5. Foreword
  6. Acknowledgments
  7. Introduction
  8. Chapter 1 Introduction to Master Data Services
    1. What Is Master Data Management?
    2. History of Master Data Management
    3. An Overview of Master Data Management Solutions
      1. Shortcomings of ERP Systems as MDM Solutions
      2. Specialized Master Data Management Solutions
      3. Catering to the Titans
    4. Microsoft’s Solution to Master Data Management
      1. What Master Data Services Delivers
      2. The Value Proposition
    5. About the Sample Company
    6. Summary
  9. Chapter 2 Installation and Configuration
    1. Determining the Initial Scope of Your Project
      1. About the Main Street Clothing Company Implementation
    2. Preparing the Web Server
      1. Windows 7 Web Application Requirements
      2. Windows Server 2008 R2 Web Application Requirements
    3. Installing Master Data Services
      1. Procedure: Install Master Data Services
      2. About MDS Configuration Manager
    4. Creating an MDS Database
      1. Procedure: Create an MDS Database
      2. About System Settings
    5. Creating the Master Data Manager Web Application
      1. Procedure: Create a Master Data Manager Web Site (Optional)
      2. Procedure: Create a Master Data Manager Web Application
      3. Procedure: Associate the MDS Database with the Web Application
    6. Securing Your Web Application
    7. Getting the Latest Version of MDS
    8. Summary
  10. Chapter 3 Starting an MDS Project
    1. How Do I Know Master Data When I See It?
    2. Business Process Models
    3. System Roles
      1. System of Entry
      2. System of Record
      3. Subscribing System
    4. Mapping the Data
      1. Determining What to Do with Duplicate Records
      2. Determining Which Attributes to Manage
    5. The Main Street Clothing Company Example
      1. Finding a Data Steward
      2. Leveraging the Business Process Models
      3. Filling in the Gaps
    6. Determining System Types
    7. Cleansing Data for Initial Import into MDS
    8. Summary
  11. Chapter 4 Creating Your Model
    1. MDS Modeling Concepts
    2. Building a Model
    3. Deploying a Model
      1. Procedure: How to Deploy a Model
    4. About Models
      1. About Main Street Clothing Company’s Models
      2. Procedure: How to Create a Model
    5. About Entities
      1. About Main Street Clothing Company’s Entities
      2. Procedure: How to Create an Entity
    6. About Members
      1. About Main Street Clothing Company’s Members
      2. Procedure: How to Add a Member
    7. About Attributes
      1. About Main Street Clothing Company’s Attributes
      2. Procedure: How to Create an Attribute
      3. Procedure: How to Create a Domain-Based Attribute
    8. About Attribute Groups
      1. About Main Street Clothing Company’s Attribute Groups
      2. Procedure: How to Create an Attribute Group
      3. Procedure: How to Reorder Attribute Groups
    9. Using Web Services
      1. Creating a Model
      2. Creating an Entity
      3. Creating an Attribute
    10. Summary
  12. Chapter 5 Integrating Master Data Services with Other Systems
    1. Staging Architecture
    2. Preparing Data for Import
      1. Security in Staging
      2. System Settings
    3. Loading Members
      1. About Main Street Clothing Company’s Staging Process
      2. tblStgMember Fields
      3. Member Staging Examples
    4. Loading Attributes
      1. tblStgMemberAttribute Fields
      2. Attribute Staging Examples
      3. Using Staging to Deactivate and Reactivate Members
    5. Loading Relationships
      1. tblStgRelationship Fields
      2. Relationship Staging Examples
    6. Importing Data into the Staging Tables
      1. Procedure: How to Import Data into SQL Server by Using SSIS
    7. Initiating the Staging Process
      1. Procedure: How to Use Master Data Manager to Initiate the Staging Process
      2. Procedure: How to Use Stored Procedures to Initiate the Staging Process
    8. Using Web Services to Stage Data
      1. Creating Batches
      2. Initiating the Staging Process
      3. Clearing Batches
    9. Errors that Occur During Staging
      1. Procedure: How to View Staging Errors in the Web Application
      2. Member Staging Errors
      3. Attribute Staging Errors
      4. Relationship Staging Errors
    10. Viewing the Staging Batch Table
      1. Procedure: How to Clear the Staging Queue
    11. Summary
  13. Chapter 6 Working with Hierarchies and Collections
    1. Ragged vs. Level-Based Hierarchies
      1. Derived Hierarchies
      2. Explicit Hierarchies
      3. Derived vs. Explicit: Which Hierarchy Is Best?
    2. Creating Derived Hierarchies
      1. Procedure: How to Create a Derived Hierarchy
      2. Procedure: How to Create a Three-Level Derived Hierarchy
      3. Hiding Levels
    3. System Settings for Hierarchies
      1. Number of Members in the Hierarchy by Default
      2. Show Names in Hierarchy by Default
    4. Creating Explicit Hierarchies
      1. Non-mandatory Explicit Hierarchies
      2. Mandatory Explicit Hierarchies
      3. Procedure: How to Create an Explicit Hierarchy
    5. Recursive Hierarchies
      1. Procedure: How to Create a Recursive Hierarchy
    6. Derived Hierarchies with Explicit Caps
      1. Procedure: How to Create a Derived Hierarchy with an Explicit Cap
    7. Collections
      1. Limitations of Collections
      2. Procedure: How to Create a Collection
    8. Creating Hierarchies by Using Web Services
      1. Creating a Derived Hierarchy with the MetadataCreate Operation
    9. Summary
  14. Chapter 7 Working with Master Data
    1. Viewing the Base Entity
    2. Finding Data by Filtering
      1. Operators
      2. Using Similarity Level
    3. Sorting and Pagination
      1. Sorting
      2. Pagination
    4. Editing Attributes
      1. Edit One Attribute for One Member
      2. Edit One Attribute for Multiple Members
      3. Edit Multiple Attributes for One Member
    5. Exporting Members
    6. Working with Data in Hierarchies
      1. Moving Members by Dragging and Dropping
      2. Moving Members by Using the Clipboard
      3. Moving Members in a Derived Hierarchy by Updating Attribute Values
    7. Transactions
      1. Reviewing Transactions
      2. Reversing Transactions
    8. Annotations
      1. Annotating Members
      2. Annotating Transactions
    9. Metadata
    10. Using Web Services to Work with Members
      1. Retrieving Members
      2. Creating and Updating Members
    11. Summary
  15. Chapter 8 Using Business Rules
    1. Business Rules Overview
      1. Business Rule Structure
      2. Business Rule Workflow
    2. Creating a Business Rule
    3. Configuring a Rule’s Expression
      1. Procedure: How to Require Attribute Values
      2. Deleting a Condition or Action
      3. More Expression Examples
      4. Creating Your Own Expressions
    4. Publishing Business Rules
      1. Procedure: How to Publish a Business Rule
      2. Business Rule Statuses
    5. Applying Business Rules
      1. Procedure: How to Apply Business Rules in Explorer
      2. Resolving Validation Issues in Explorer
      3. Procedure: How to Validate a Version
    6. Other Business Rule Tasks
      1. Procedure: How to Change the Rule Name and Description
      2. Procedure: How to Set the Rule’s Priority Order
      3. Procedure: How to Exclude Business Rules
    7. Configuring E-Mail Notifications
      1. Procedure: How to Configure E-Mail Notifications
      2. Procedure: How to Configure a Business Rule to Send E-Mail
    8. Creating and Triggering Workflows
      1. Creating a Workflow in MDS
      2. Triggering an External Workflow
    9. Using Web Services to Manage Business Rules
      1. Getting a List of Rules
      2. Creating a Business Rule
    10. Summary
  16. Chapter 9 Creating Versions of Data
    1. Versions Overview
      1. Changing the Structure of Your Model
      2. Committing Versions
      3. Versioning for Main Street Clothing Company
    2. Updating Your Version Name and Description
      1. Procedure: How to Change the Version Name and Description
    3. Version Statuses
      1. Procedure: How to Lock a Version
    4. Validating a Version
      1. Procedure: How to Validate a Version
      2. Procedure: How to Resolve Validation Issues
    5. Reviewing and Reversing Transactions
    6. Committing and Copying a Version
      1. Procedure: How to Commit a Version
      2. Procedure: How to Reopen a Committed Version
      3. Procedure: How to Copy a Version
    7. Version Flags
      1. Procedure: How to Create a Version Flag and Assign It to a Version
    8. Viewing a Version’s Ancestry
      1. Procedure: How to Delete a Version
    9. Using Web Services to Work with Versions
      1. Returning a List of Versions
      2. Changing the Version Status
    10. Summary
  17. Chapter 10 Using Metadata
    1. The Metadata Model
      1. What Happens When Model Objects Are Added or Deleted
      2. Metadata for Entities
      3. Metadata for Attributes
      4. Metadata for Attribute Groups
      5. Metadata for Hierarchies
      6. Metadata for Models
    2. Adding Metadata
    3. Extending the Metadata Model
      1. Limitations of the Metadata Model
    4. Using Web Services with Metadata
      1. Determining the Metadata Member Code
      2. Getting Metadata for a Model, Entity, or Attribute
    5. Summary
  18. Chapter 11 Implementing Security
    1. Security Overview
    2. Users and Groups
      1. Procedure: How to Add a Group
    3. Administrators
      1. Procedure: How to Change the Administrator Account
    4. Testing Permissions
    5. Functional Area Permissions
      1. Procedure: How to Assign Functional Area Permissions
    6. Model Object Permissions
      1. Quick Facts About Model Object Permissions
      2. Best Practice for Model Object Permissions
      3. Procedure: How to Assign Model Object Permissions
      4. Procedure: How to Delete Model Object Permissions
      5. Access to Entities
      6. Access to Leaf Member Attributes
      7. Access to Individual Attributes
      8. Access to an Explicit Hierarchy
      9. Access to Collections
      10. Access to Other Model Objects
      11. Deny Permissions
      12. Navigational Access
    7. Deleting Permissions
    8. Hierarchy Member Permissions
      1. Quick Facts About Hierarchy Member Permissions
      2. Procedure: How to Assign Hierarchy Member Permissions
      3. Procedure: How to Apply Hierarchy Member Security Immediately
    9. Determining Which Permissions Apply
      1. Combining Permissions
      2. Combining Users and Groups
    10. Setting Security by Using Web Services
      1. Retrieving Users and Groups
      2. Retrieving User or Group Permissions
      3. Adding Users and Groups
      4. Assigning Permissions
    11. Summary
  19. Chapter 12 Publishing Data to External Systems
    1. Exporting Data to Subscribing Systems
    2. Subscription View Formats
    3. Common View Architecture
      1. Procedure: How to Create a Subscription View
      2. Viewing a Subscription View in SQL Server
    4. Subscription Views and Model Deployment
    5. Creating a Subscription View with Web Services
      1. Creating Entity Views
      2. Creating Derived Hierarchy Views
      3. Deleting Views
    6. How Main Street Clothing Company Uses Subscription Views
      1. Procedure: How to Export Subscription View Data
    7. Summary
  20. Chapter 13 Extending MDS with Web Services
    1. Exposing the Web Service
      1. Procedure: How to Enable the Web Service
    2. Exposing the WSDL
      1. Procedure: How to Enable the WSDL
    3. Creating an MDS Project in Visual Studio
      1. Procedure: How to Create an MDS Web Service Application
    4. Creating an Abstraction Layer
    5. MDS Web Service Operations
      1. Members
      2. Bulk Operations
      3. Validation
      4. Transactions
      5. Annotations
      6. Subscription Views
      7. Metadata Structures
      8. Security
      9. Business Rules
      10. System Settings
      11. User Preferences
      12. Miscellaneous Operations
    6. The Most Frequently Used Operations
      1. Searching for Members
      2. Understanding the EntityMembers Object
      3. Summarizing Member Data
      4. Working with Metadata
      5. Validating Records in MDS
      6. Managing Transactions
    7. Handling Errors in the Web Service
    8. Summary
  21. Index

Product information

  • Title: Microsoft SQL Server 2008 R2 Master Data Services
  • Author(s): Tyler Graham, Suzanne Selhorn
  • Release date: March 2011
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071756242