An overview of Master Data Services – MDS in SQL Server 2008 R2
The key new features of SQL Server 2008 R2 relate to the
management of organizations’ master data (Master Data Services – MDS ),
administration of multiple servers (SQL Server Utility), and processing large
amounts of data (PowerPivot for Excel, formerly known as Project Gemini).
One of the main concerns of any organization is the
management of its master data, various departments have their own databases (and
often times different systems) containing data that they work with on a regular
basis. Each department has a set of master data (if they are using different
systems then it is often in a different format) for e.g. the Sales department
will have Customer master, the Purchase Department would have a Vendor master,
and there might be a Product master in the Inventory department. At the
management level, if somebody wants to generate a report from the master data,
or do some analysis on it, they would require an interfacing tool that
interacts with these disparate systems. In such as scenario, it would be very helpful
to maintain a central database of master data where all the master data is
available for reporting and analysis. Master Data Services (MDS) is a
component in SQL 2008 R2 that makes this possible. It is a component that
allows storing of Master Data in a central repository called the Master Data
Services Database and provides for its further use in business intelligence, reporting,
and data warehousing. As of the CTP, MDS is available only on 64-bit systems.
When you install MDS, the following components and tools are
- Master Data Services Configuration Manger –used to create
and configure MDS Databases and web applications
- Master Data Manager – where users manage the master data.
- Master Data Services Web Service – a service for developers
to develop or extend custom solutions for MDS.
There are three steps to using the MDS application:
(1) Create the MDS database.
(2) Create the website and application, enable web services.
(3) Create models, entities, attributes and start using the
MDS application to manage your master data.
There are three pages available within MDS Configuration
Manager, the Server Configuration Page, the Database Page and the Web
Configuration Page. When you launch the Configuration Manager, the first screen
you will be presented with is the Server Configuration Page as shown below.
There are two options in the left pane: Databases where you create
the MDS Database, and Web Configuration where you create and edit the MDS Web Site
and Web Applications. First, we will look at the Databases page and how to
create a Master Database. Click on the Databases option and the Databases page
opens up with two sections – Current Database and System Settings as shown
If you click on Create Database, you will be taken to
the Create Database Wizard where in stages, you will be asked to provide
information for s the Database Server on which you want to create the database,
the Database name, its collation, the Service and Administrator account, before
finally your MDS database will be created.
Once you’ve created the database, you establish a connection
to the database using the Select Database button.
In the System Settings section, the following options
- Fixed levels in Subscription Views: specify whether there
is a fixed number of hierarchy levels while exporting data.
- Web Services time-out threshold: time out value for web
services before the master data manager web application returns a timeout
- Staging Tansaction Logging: whether or not to log
transactions when loading data from staging tables to the master data
- Levels in Subscription Views: specify the number of
hierarchy levels in subscription views.
- Copy only Committed Transactions: specify whether users
can copy only committed transactions into the master data services
- Rows per batch: indicate the number of rows per batch to
- Email frequency: frequency, in seconds, of emails to be
sent in case of a business rule validation failure
- Master Data Services root URL: the root url under which
the master data services web site will be placed.
Database Mail Profile: specify the database mail profile to
be used for sending emails in case of business rule validation failure. There
is a button Create Profile, which will open up the Database Mail Profile
window, when clicked. Here you can create a new Mail profile with options as
shown in the screen below. You will be required to supply the profile name to
be created, and the account name to be associated with the profile. You will
also need to configure the SMTP Mail server options for outgoing emails and the
authentication mode. As a best practice, you should create a specific user
account with minimal permissions for using with database mail profile.
Turning now to the Web Configuration page (shown below),
here you can create a web site and web application and enable Web Services to
be able to use MDS Web Service.
Click the Create Web Site button to open the create
web site window as shown below where you can create the website. SQL Server
doesn’t create the Master Data Manager web application by default (Master Data
Manager Web application is the place where users manage the Master Data).
In this interface, you either enter your website name in the
Web site name field provided or accept the default. You have two options
to specify the protocol: http for Hyper Text Transfer Protocol or https
if you want the web server and client to exchange information over an encrypted
channel. Unless you are using specific IPv4 or IPv6 IP addresses, leave the
default All unassigned and port number values be selected. Host
header is an optional value to specify a domain name for the server that is
hosting the web site. If you give this a value, then users have to use this
value rather than the server’s IP address. In the Application pool
section, specify the name of the application pool and the user account details
that will have administrator privileges for that application pool. This
application pool holds all the MDS web applications and web sites.
The Create Application button pops up the Create
Web Application window (as shown below) where you provide the Virtual
path for the application. Your web application will be created under this
virtual path. After specifying the application pool details, click OK,
and then select the MDS database for this web application in the Database
section. Finally, click on the check box in the Web Services section to allow
the web services to be able to use the MDS application.
After creating the database and the web site, and enabling
web services, when you click the Apply button, your settings will be
saved and you will be taken to the Master Data Services Web Application site. After
providing the login credentials, you see the Getting Started page where
you click on the Open the master data manager home page link which
directs to the Master Data Manager (MDM) home page as shown in the figure below:
As you can see, the MDM home page has five options:
Explorer, Version Management, Integration Management, System Administration,
and User and Group Permissions.
First, create and manage models objects in System
Administration section. Once you have created the models, use the Explorer page
(shown below) to manage hierarchies, entities, members and attributes for a
For managing different versions of models, you use the
Version Management section: .
In User and Group Permissions, you can assign permissions to
users and groups to maintain security of the master data:
Finally, you can integrate your master data from different
sources and make it available to subscribing systems in the Integration
In the Staging batches pane of the Integration
Management section, you can view the status of all existing staging batches
that were created to load the master data. In the Unbatched Staging Records
pane, you can view information about records in the staging tables. Staging
tables are tables where your data is loaded prior to importing it into the
That’s quik lap around the new Master Data Services of SQL
Server 2008 R2, in subsequent articles we will turn our attention to SQL Server
Utility and PowerPivot.