Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 20, 2010

An Overview of SQL Server Master Data Services

By Deanna Dicken

As enterprises add systems and acquire companies, they will most likely find themselves faced with a growing inconsistency amongst core data utilized by their key systems. This leads to an inability to provide quality data to management. Master Data Services for SQL Server provides a means to centrally manage and serve up this critical enterprise data.

As enterprises grow and change over time, they add systems and acquire companies. They will most likely find themselves faced with a growing inconsistency amongst core data utilized by their key systems. This leads to an inability to provide quality data to management for an overarching view of the business for proper decision making. Master Data Services for SQL Server provides a means to centrally manage and serve up this critical enterprise data.

Master Data Services

Master Data Service’s helps businesses set up and manage critical, cross-system data (such as customer lists) in a single location in order to provide one organization-wide authoritative source. Companies can be leaner and more agile when one authoritative source supplies key data to all its mission-critical systems. One source means systems can be integrated faster, data can be rolled-up cleaner, and business intelligence can provide a more enterprise-wide view.

Hub Architecture

Master Data Services provides for Master Data Management (MDM). With MDM, there are three types of hub architectures for managing master data: repository, registry, and hybrid. The repository model is used when all attributes needed for all systems regarding the master data entities are stored in the master data database. With registry architecture, only key values from the systems of record are stored in the master data database and additional information about that entity has to be looked up in that system. Hybrid is a mixed approach to storing all attributes of an entity or storing just key values.

Master Data Models

Data is setup in Master Data Services using models. Models are used to organize the entities, attributes, hierarchies and collections. Models should contain entities that can be logically grouped together such as person with its related address and phone entities.

Entities are the objects being stored in Master Data Services. The members of these entities are the master data that is managed centrally (such as customers). Members can be thought of as rows in a table. Entities are described by attributes, which can be thought of as columns in a table.

Attributes can be one of three types: free-form, domain-based attribute (DBA), or file attribute. Free-form attributes are numbers, strings, dates, and links. File attributes, as you can guess, store files associated to the entity such as documents or images. Domain-based attributes are attributes associated with the entity, which are entities themselves. Take for instance the manufacturer of a vehicle. The vehicle is an entity and the manufacturer describes the vehicle. Manufacturer is itself an entity with its own attributes. Two attributes are automatically assigned to every entity: name and code. These default attributes are used for the domain-based attribute of the containing entity.

Within a model, you can create hierarchies to group members within and across entities. A hierarchy defined within a single entity is an explicit hierarchy. One defined across entities with existing relationships is a derived hierarchy.

Collections can also be created to group members of a single entity. Collections can contain other collections and explicit hierarchies. This allows for the creation of taxonomies such as grouping your customers by type and also by region.

Master Data Services Tools

Master Data Services has a tool for setting up master data databases and web sites. This tool is called Configuration Manager, which is targeted at administrators of Master Data Services.

The master data is managed through the Master Data Services Manager web application. This user interface provides access to create, view, review, approve and modify master data. Business users could be granted access to this application to remove IT from stewardship over the master data if so desired.

Additionally there is a Master Data Services Web Service that developers can use to access or extend Master Data Services.

Security and Auditing

Fine-grain, role-based access is available to manage security to master data and its models. Administrators can grant access down to the attribute level for any user or group. In Master Data Manager, access is granted to the functional areas: Explorer, Version Management, Integration Management, System Administration, and User and Group Permissions.

The who, what, and when of every change to master data is tracked in the transaction log. In addition to providing a much needed audit trail, the transaction log serves as means to rollback any given master data change should that data’s steward deem the rollback necessary.

Data Quality

Because master data is critical to your organization and your systems, you want to be extra certain that the data captured is of the highest quality. With Master Data Services, you can define business rules around your master data to ensure the data is passing the test. The business rules can be defined by either IT or business resources.

Workflow for data change approval can also facilitate data quality efforts. Notifications can be configured to alert interested parties of unsuccessful data changes. If necessary, rollback of unwanted modifications can take place using the transaction log.

Cleansing all the master data can be quite a chore. Master Data Services provides a matching mechanism to help get the data clean from the get go. The business rules and workflow take over from there to help keep the data high quality.


Administrators of Master Data Services can lock down and version the master data models allowing consuming applications the ability to target a specific version of the model. All entities and hierarchies are versioned and new versions can be created as necessary.


Business intelligence and integration efforts have reduced complexity and increased quality when core data is shared from a single authoritative source. Master Data Services in SQL Server gives companies the capability to have one source of the truth for critical data and therefore provide consistency in this data across systems.

For More Information

Master Data Services
SQL Server R2 Master Data Services
Master Data Management Hub Architecture
Master Data
Introducing SQL Server 2008 R2 Master Data Services

» See All Articles by Columnist Deanna Dicken

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM