Microsoft SQL Server 2012 Master Data Services 2/E, 2nd Edition

Microsoft SQL Server 2012 Master Data Services 2/E, 2nd Edition

by Tyler Graham
Released October 2012
Publisher(s): McGraw-Hill
ISBN: 9780071797863

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

Deploy and Maintain an Integrated MDS Architecture

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

  • Install Microsoft SQL Server 2012 Master Data Services
  • Build custom MDS models and entityspecific staging tables
  • Load and cleanse data from disparate sources
  • Logically group assets into collections and hierarchies
  • Ensure integrity using versioning and business rules
  • Configure security at functional, object, and attribute levels
  • Extend functionality with SOA and Web services
  • Facilitate collaboration using the MDS Excel Add-In
  • Export data to subscribing systems through SQL views

Table of contents

  1. Cover 
  2. About the Author
  3. Copyright
  4. Contents at a Glance
  5. Contents 
  6. Foreword
  7. Acknowledgments
  8. Introduction
  9. Chapter 1 Introduction to Master Data Services
    1. What Is Master Data Management?
    2. History of Master Data Management
    3. Overview of Other Master Data Management Solutions
      1. 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. New Features of MDS in SQL Server 2012
      2. What Master Data Services Still Delivers
      3. The Value Proposition
    5. About the Sample Company
    6. Summary
  10. 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. About MDS Configuration Manager
      2. Procedure: How to Install Master Data Services
    4. Creating an MDS Database
      1. Procedure: How to Create an MDS Database
      2. About System Settings
    5. Creating the Master Data Manager Web Application
      1. Procedure: How to Create a Master Data Manager Website (Optional)
      2. Procedure: How to Create a Master Data Manager Web Application
      3. Procedure: How to Associate the MDS Database with the Web Application
    6. Securing Your Web Application
    7. Getting the Latest Version of MDS
    8. Upgrading 2008 R2 MDS
    9. Repairing the MDS Database
    10. Summary
  11. 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. Leveraging the MDS Excel Add-In to Manage Your Project
    9. Summary
  12. Chapter 4 Creating Your Model
    1. MDS Modeling Concepts
    2. Building a Model
    3. Deploying a Model
      1. Procedure: How to Deploy a Model Using ModelDeploy.exe
    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
  13. Chapter 5 Integrating Master Data Services with Other Systems
    1. Staging Architecture
      1. Benefits of Entity-Based Staging
      2. Batch Tags
      3. Security in Staging
      4. System Settings
      5. About Main Street Clothing Company’s Staging Process
      6. Standard Leaf and Parent Staging Fields
      7. Staging Import Types
      8. Member Staging Examples
      9. Using Staging to Deactivate Members
    2. Loading Relationships
      1. Relationship Table Fields
      2. Relationship Staging Examples
    3. Importing Data into the Staging Tables
      1. Procedure: How to Import Data into SQL Server by Using SSIS
    4. 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
      3. The End of Staging Sweep
    5. Using Web Services to Stage Data
    6. Errors That Occur During Staging
      1. Procedure: How to View Staging Errors in Management Studio
      2. Staging Errors View
    7. Viewing the Staging Batch Table
      1. Procedure: How to Clear the Staging Queue
    8. Summary
  14. Chapter 6 Working with Hierarchies and Collections
    1. Ragged vsLevel-Based Hierarchies
      1. Derived Hierarchies
      2. Explicit Hierarchies
      3. Derived vsExplicit: 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
    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 Explicit Cap
    7. Collections
      1. Weighting of Collection Members
      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
  15. Chapter 7 Working with Master Data
    1. Changes in SQL Server 2012
    2. Viewing the Base Entity
    3. Finding Data by Filtering
      1. Operators
      2. Using Matching
    4. Sorting and Pagination
      1. Sorting
      2. Pagination and Other Settings
    5. New Explorer Format
      1. Edit One Attribute for One Member
      2. Edit One Attribute for Multiple Members
    6. Exporting Members
    7. 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
    8. Transactions
      1. Reviewing Transactions
      2. Reversing Transactions
    9. Annotations
      1. Annotating Transactions on Update
    10. Navigating Related Entities
      1. Navigating Attributes
      2. Navigating Many-to-Many Relationships
    11. Metadata
    12. Using Web Services to Work with Members
      1. Retrieving Members
      2. Creating and Updating Members
    13. Summary
  16. Chapter 8 Using Business Rules
    1. Business Rules Overview
      1. Business Rule Structure
      2. Business Rule Workflow
    2. Creating a Business Rule
    3. Editing a Business Rule and 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. Viewing Change Tracking Group Reports
      3. Triggering an External Workflow
    9. Using Web Services to Manage Business Rules
      1. Getting a List of Rules
      2. Creating a Business Rule using Web Services
    10. Summary
  17. 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
  18. Chapter 10 SQL Server 2012 MDS Add-in for Excel
    1. Installing the Add-in
      1. Procedure: How to Install the Add-in for Excel
    2. Working with MDS Data in Excel
      1. Procedure: How to Connect to the MDS Server and Load an Entity
      2. Publishing Changes
      3. Refreshing Data from the Server
      4. Reviewing Transactions
      5. Making Deletions in Managed Sheets
      6. Combining Data
      7. Applying Rules
      8. Filtering Entity Results Members
      9. Procedure: How to Load a Filtered Entity
      10. Saving and Sharing Queries
      11. Procedure: How to Save a Filter Query and Share It Through Outlook
    3. Creating and Modifying Entities Rapidly
      1. Using the Excel Add-In at the Main Street Clothing Company
      2. Procedure: How to Create an MDS Entity from Excel Data
      3. Making Changes to an Existing Entity
      4. Procedure: How to Modify an Existing Entity’s Structure
    4. Configuring Add-in for Excel Settings
      1. Configuring Data Settings
      2. Configuring Behavior Settings
    5. Leveraging Data Quality Matching in Excel
    6. Summary
  19. Chapter 11 Implementing Security
    1. Security Overview
    2. Security Changes in SQL Server 2012
    3. Users and Groups
      1. Procedure: How to Add a Group
    4. Administrators
      1. Procedure: How to Change the Administrator Account
    5. Testing Permissions
    6. Functional Area Permissions
      1. Procedure: How to Assign Functional Area Permissions
    7. 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
    8. Deleting Permissions
    9. Hierarchy Member Permissions
      1. Quick Facts About Hierarchy Member Permissions
      2. Procedure: How to Assign Hierarchy Member Permissions
    10. Determining Which Permissions Apply
      1. Combining Permissions
      2. Combining Users and Groups
    11. 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
    12. Summary
  20. 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 View8
      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
  21. Chapter 13 Extending MDS with Web Services
    1. Exposing 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
      13. Entity-Based Staging Operations
      14. Data Quality 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 MD
      6. Managing Transactions
    7. Handling Errors in the Web Service
    8. Summary
  22. Chapter 14 Advanced Modeling
    1. Common Modeling Mistakes
      1. Building Outside Limitations into MDS
      2. Trouble Identifying Common Attributes
      3. Engineering Dead Scenarios
    2. Rapid Model Development
    3. Managing Slowly Changing Dimensions
      1. Overwriting Rows (Type 1)
      2. Adding Rows (Type 2)
      3. Adding Columns (Type 3)
      4. Using the Power of MDS to Simplify the Problem
      5. Managing Dimension for Main Street Clothing Company
    4. Limiting Unneeded Complexity
      1. Designing for Exceptions
      2. Designing for N
    5. Summary
  23. Index

Product information

  • Title: Microsoft SQL Server 2012 Master Data Services 2/E, 2nd Edition
  • Author(s): Tyler Graham
  • Release date: October 2012
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071797863