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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted July 16, 2012

Getting Started with Master Data Services (MDS) In SQL Server 2012

By Arshad Ali

Introduction

Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. Master Data Services is an extensible Master Data Management (MDM) platform, which is built on top of the SQL Server database engine and Windows Communication Foundation (WCF).

MDS allows you to create a centralized hub for creating and managing enterprise master data and provides many functionalities, some of them are:

  • Transaction logging - MDS does transaction logging for all the changes made to the master data by each user; for example what changes were done, who made the changes, when the changes were made, what the value was before the change and what the value is after the change. This transaction logging feature allows users to revert to old values if the changes are no longer required. To learn more about it see  A First Look at SQL Server 2008 R2 Master Data Services.
  • Versioning - MDS also allows you to define multiple versions of your master data. For example, you can have one committed version that will be available for consumption by downstream applications and at the same time, you can have an open version of the same master data to make changes. This ensures that downstream applications get consistent data because no changes are permitted on the committed version of master data and at the same time, you are able to do changes to the open version of the master data. A version could be either in open (available for changes), locked (locked for validating master data against defined business rules) and committed (no changes are permitted and hence this version is available for downstream applications for consumption) version; to learn about how versioning works, see MDS Versioning, Permission and Security.
  • Role-based security – MDS has fine grained Active Directory (AD) role based security model to secure the functional area (the kind of operation a user can perform), model’s entities and columns (object level security) and hierarchy (row or data member level security); to learn more about Permission and Security in MDS see MDS Versioning, Permission and Security.  
  • Subscription views - MDS allows creating subscription views to consume master data from the MDS database repository. Reporting, analytical and other applications can consume these subscription views to get access to the master data from the central master data hub.

In this article, I will be talking about installing, configuring Master Data Services in 2012 and a way to deploy sample models.

To learn more about Master Data, Master Data Issues, Master Data Management, Master Data Services and its architecture see A First Look at SQL Server 2008 R2 Master Data Services.

Installation of Master Data Services in SQL Server 2012

Unlike MDS in SQL Server 2008 R2 where we had separate installer (msi) file for Master Data Services installation, in SQL Server 2012 the installation is built in with the main installation wizard of SQL Server 2012, as you can notice below under the Shared features node. Please note, unlike SQL Server database engine, MDS is a shared feature and can be installed only once, though you can create or configure multiple sites/services for MDS from single installed MDS bits:

Feature Selection
Feature Selection

Configuration of Master Data Services in SQL Server 2012

Configuration of MDS in SQL Server 2012 is not much different from the configuration in SQL Server 2008 R2; you need to launch Master Data Services Configuration Manager from the Programs menu as shown below:

Master Data Services Configuration Manager
Master Data Services Configuration Manager

From Master Data Services Configuration Manager, the first thing that you need to do is to create an MDS database or select one if already available. (You can also upgrade MDS database from earlier version from this screen):

Upgrade MDS database
Upgrade MDS database

On the second screen of the wizard, you create a MDS website and specify the database (that we created above) to store the data for that MDS application. You can also notice an option of enabling integration with Data Quality Service; another new feature in SQL Server 2012 for data cleansing and data matching:

Web Configuration
Web Configuration

To learn more about configuring Master Data Services and its pre-requisites see Getting started with SQL Server 2008 R2's Master Data Services.

On successful configuration you will see a site similar to that shown below. Again, notice the interface is similar to what we have with SQL Server 2008 R2; the difference on the main screen is the link to install MDS Add-in for Microsoft excel. To learn more about these functional areas, see MDS Versioning, Permission and Security.

Master Data Manager

Master Data Manager

Deploying Sample Models in Master Data Services in SQL Server 2012

MDS allows you to create deployment packages for exporting and importing models onto another environment (for example moving a model from development to test to production environment).

The way we do deployment in MDS in SQL Server 2012 is different from the way we do the deployment in MDS in SQL Server 2008 R2. In MDS of SQL Server 2008 R2 we have the Model Deployment Wizard, which allows you to create / deploy packages with data as well, but in MDS of SQL Server 2012 a package can only be deployed with the Model Deployment Wizard if the package does not contain data; if the package contains data we need to use the MDSModelDeploy utility from the command prompt; to learn more about deploying model with this utility see  Deploy a Model Deployment Package by Using MDSModelDeploy.

Model Deployment Wizard
Model Deployment Wizard

The MDSModelDeploy utility is available in following folder:

<Installation Drive>:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration

Launch the command prompt with administrator elevation and then go to the above folder and execute this command to list all the MDS services installed on the local machine:

MDSModelDeploy.exe istservices

MDSModelDeploy.exe listservices
MDSModelDeploy.exe listservices

By default MDS has 3 different sample models that ship with \SQL Server and these model files are available at this location:

<Installation Drive>:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages

To deploy a product sample file to the MDS1 service, you can execute this command:

MDSModelDeploy.exe deploynew -package "<Installation Drive>:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\product_en.pkg" -model Products -service MDS1

MDSModelDeploy.exe deploynew -package
MDSModelDeploy.exe deploynew -package

After deploying a model, it will not appear immediately in Master Data Manager, click on the “Refresh cached information” link available on the right side of it and it will appear in the model list:

Refresh Cache
Refresh Cache

Getting Started with Master Data Services in SQL Server 2012

There are several significant enhancements in MDS of SQL Server 2012 and the first thing that you will notice is the Silverlight based user interface on the Master Data Manager tool. The other important enhancement is MDS Add-in for Microsoft Excel; now you can create entities and load data in bulk from Excel itself or without doing it one by one in the web user interface of Master Data Manager.

Select the product model (which we deployed above) and click on explorer link:

Click on the Explorer link
Click on the Explorer link

And this is what you will see; a Silverlight based interface that is more intuitive and more responsive:

Silverlight based interface
Silverlight based interface

Conclusion

Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. In this article, I talked about installing, configuring Master Data Services in 2012, a way to deploy sample models and how to get started working with it.

Resources

Master Data Services in SQL Server 2012

Data Quality Services in SQL Server 2012

A First Look at SQL Server 2008 R2’s Master Data Services

Getting started with SQL Server 2008 R2's Master Data Services

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date