Getting started with Master Data Services in SQL Server 2008 R2
Editor's note: This article was written using the November
Community Technical Preview (CTP) of SQL Server 2008 R2. The configuration process may change
between now and when the product is released in May 2010.
Master Data Services, also known as MDS, is one of the key business intelligence features being introduced with SQL Server 2008 R2.
The basic goal of Master Data Services is to provide a single authoritative source for information
within your enterprise. This information can then be used by other applications and databases so
that
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in March 2010
every application in you environment is always looking at a single authoritative copy of the
same information.
Before you can use Master Data Services, you need to install it. This requires a 64-bit server
because the MDS installer in only available in a 64-bit build. You'll also need Windows PowerShell 1.0 or higher.
You can begin the installation process by opening the SQL Server 2008 R2 DVD and navigating to
the MasterDataServices\x64\1033_ENU folder. From there you can run the MasterDataServices.msi
installer. The installation of the MDS software is very straight forward, with the only question
being where exactly to install it.
Configurating Master Data Services
After you have installed the MDS service, you can open the Master Data Services Configuration
Manager from the Start Menu under Start→All Programs→Microsoft SQL Server 2008 R2 November
CTP→Master Data Services→Configuration Manager. This will allow you to configure the MDS repository
and specify where to get the master data from.
To configure Master Data Services, open the MDS Configuration Manager and select the Databases
option on the menu shown in Figure 1. Then click the Create Database button on the right-hand side.
This will open the wizard for configuring the database that will hold the meta data for the MDS
repository.
Figure 1. Master Data Services Configuration Manager
While configuring the MDS repository, you can select a local or remote database instance. After
selecting the database instance, you'll need to specify service and administrator accounts to be
used for administering the MDS database. If you already know which database you want to hold the
MDS repository, click the Select Database button.
Once the MDS repository is configured you can then setup the web configuration. This enables a
web-based configuration screen which allows you to configure where the data being managed by Master
Data Services is located.
The Master Data Services website
After you configure the database it is time to configure the Internet Information Services (IIS) website. You'll need to provide the IP
address, TCP port number, host header, and path within the site that will be used to access Master
Data Services. Additionally, you'll need to create an application pool and specify the username and password that the pool
will run under, as shown in Figure 2.
Note: While configuring Master Data Services for this article
I found that the account the application pool runs under must be a local administrator on the MDS
server. If the account doesn't have local admin rights, then not all the options are available via
the MDS website. If you install without the application pool account having admin rights, simply
add the group to the local admin group and reboot the server.
Figure 2. Creating an app pool during MDS configuration
Identifying your master data
After you have finished the web configuration for the MDS database, you can begin creating data models. These data models contain the references to what will be used as
your master data. You will need to define models, entities, attributes, business rules, and derived
hierarchies when modeling the data.
After connecting to the Master Data Services management tool, click on the System Administration
option. This will let you create a model to hold the data. Click on the Manage option at the top
and selecting Model. Then click the green plus sign to add a new model as shown in Figure 3.
When creating the model, you can opt to either have an entity with the same name as the model or
create one manually afterword with a different name. Click the save button when you are finished
(the one with the little floppy disk without the red X).
Figure 3. Data modeling for Master Data Services
After creating the model, go back to the Explorer view and select the model you just created.
This will show entities associated with the model to the right. Click on the icon to edit the
entities. Note that you can also get to this screen by clicking on the Manage drop-down menu,
selecting Entities, and clicking on the green plus sign. Type in a name for the entity and
select if you wish to enable explicit hierarchies and collections (I elected not to when writing
this article). Then click on the Save icon.
After you have created the entity, you will want to add an attribute group, also known as a
"leaf group". To do this, click on the Manage drop-down menu and select Attribute Groups.
Select the model and entity you wish to edit and click on Leaf Groups. The green plus sign
should appear, and clicking on it will allow you to create a leaf group.
To add leaf attributes you will need to navigate back to the main tree by clicking on the
Explorer menu at the top. Select the model from the model tree followed by the needed attribute
tree and click the button with the pencil on it. This will show you the available leafs and allow
you to add additional leafs to the leaf group. To add the leaf attribute, click on the green plus
sign. This will let you select the name of the attribute, data type, and maximum size of the
data.
After creating the attributes, once again click on the Manage drop-down menu at the top and
select Attribute Group. Choose the model and entity that you wish to edit (in this case
Adventure Works and Employee). Click the plus sign next to the leaf groups, followed by the group
you wish to edit, then Attributes. Select the attributes you'd like to include in this group
and click the Save button. You can also change the order of the assigned attributes.
Securing master data with MDS
After you have created your master data model, you can secure it by using Microsoft Active
Directory users and groups. This is done by selecting the User and Group Permissions option
from the Master Data Services home screen. You can then click on the green plus sign to add another
user to MDS. This is done by simply typing in the user or group name.
After you have saved the new user or group, you can edit the user by selecting it from the user
or group list and clicking the pencil icon. This will allow you to assign rights to each specific
object that we just created.
In case you are wondering, yes Master Data Services is a very complex solution when it comes to
maintaining master data. Now, you'll notice that this article doesn't include how to actually load
data into MDS. We'll address that in a later article.
ABOUT THE AUTHOR:
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's
over 175-million-user installation, one of the largest in the world. Denny's areas of expertise
include system architecture, performance tuning, replication and troubleshooting. He currently
holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his
blog at SQL Server
with Mr. Denny.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation