Tag Archives: MDS

Installing Master Data Services in SQL Server 2012

Posted on by jeffprom

Master Data Services provides a central location where you can store non-transactional information. This repository serves as the primary source for accurate data within your organization. In this post I will show you how to install Master Data Services for SQL Server 2012. First launch the ‘Master Data Services Configuration Manger’ from the program menu.

MDS Config Manager

You will be presented with the main screen like the one shown below. If either of the two prerequisites, IIS and PowerShell, don’t check out you will need to install them before continuing with the installation.

MDS Config Manager Summary

Once you have PowerShell and IIS installed, select Database Configuration from the menu on the left.
On this screen you will be presented with two options. Create a new database, or select an existing MDS database. If you later decide to modify some settings on your MDS database, you can select your existing database from this screen, make your adjustments and then apply your changes. Since we are going to show a new installation let’s go ahead and select ‘Create Database’.

MDS Config Create Database

Now you will be presented with a Create Database wizard with several steps. First, we need to connect to the SQL Server instance where we want to install the MDS database. Select your authentication preference, User name and Password to connect. Click the Next button.

MDS Database Wizard Connect to Server

On the ‘Database’ tab of the wizard, enter a Database name. You can also set some settings such as collation. Once you are satisfied with the name and settings, click Next.

MDS Database Wizard Database Name

The next screen is where you enter the User name for the Administrator Account. Note: Once you click the Next button the wizard will try to authenticate the entered user account to a domain controller. You will need to make sure you can access the domain controller. If you cannot, it will provide an error and you cannot continue with the install.

MDS Database Wizard Administrator Account

Next is the ‘Summary’ screen, and finally the ‘Progress and Finish’ screen.
If everything checks out, you should see a success page like the one below.

MDS Database Wizard Success

Click Finish and you will be brought back to the Database Configuration section. However, this time you will now have a database and in the System Settings section you can make adjustments. Simply make your changes and click the Apply button.

MDS Selected Database

Next, we will configure IIS which will serve as one of the primary interfaces for MDS. Click on the Web Configuration option on the left. On this screen you can match up an IIS website with an MDS database. First we need to find a suitable IIS website for MDS. From the dropdown you can either select an existing website, or create a new one. Since we are doing a new install, we will select ‘Create new website’.

MDS Web Configuration Screen

Select a Website name and port. If MDS is the only website you plan on hosting from your server, you may want to leave it on port 80. If you host multiple sites, or are testing, feel free to enter in a new port number. In our case we are going to select port 991.

MDS Create Website

You can leave Protocol and IP Address as the default. However, you will need to add values to the Application Pool area. Select a name for your application pool, and provide a User name and Password. Once finished, click the OK button.

You should now have a Website listed. You should also have a Database listed under the ‘Associate Application with Database’ section. If you do not, simply click the Select button and find your MDS database you just created. You should now see a screen similar to the one below with an active ‘Apply’ button available to you. If you do, excellent, go ahead and click apply. This is where the leap of faith comes in. It can take about a minute to finish the install and it doesn’t give you a progress bar.

MDS Install Finish

After patiently waiting, you should finally be presented with a webpage like the one listed below. This is the Getting Started page. It offers some helpful links to get you going.

MDS Getting Started Page

Once you are ready to dig in, click the first link on the top called ‘Open the Master Data Manager home page’.
This will bring to you to the actual MDS home page where you can start working with MDS. I will cover those areas in more detail in future posts.

Even though the Getting Started page comes up, you will still have the last screen on the MDS installation open. If you have Data Quality Services installed and want to link it with MDS, this would be the time to do so. You would simply click on the now highlighted button called ‘Enable Integration with Data Quality Services’. More details about connecting MDS and DQS can be found on my other blog post here.

When you are done with the MDS configuration manager, simply click Exit and you’re ready to start using MDS!

SQL Saturday #206 – Madison, Wisconsin

Posted on by jeffprom

SQL Saturday #206

Saturday, April 6th I will be presenting at SQL Saturday #206 in Madison, Wisconsin. This event will be held at Madison Area Technical College – Truax Campus, 3550 Anderson St, Madison, WI 53704.
I will be speaking on the following topic:

Master Data Services
Does your company have similar data in multiple locations? Not really sure which set of data is accurate? With MDS, Microsoft gives you the ability to have one accurate source of non-transactional data to use with your applications or send to other databases and ETL processes. Leverage data stewards to help manage the data repository to ensure it is up-to-date and accurate. In this session we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this session, you should have a good understanding of what MDS does, how to use it, and how to get started with your own MDS project.

Check out the SQL Saturday #206 page for more information.

Hope to see you there!

Integrating MDS with DQS

Posted on by jeffprom

I’ve installed both DQS and MDS several times, but often had trouble trying to integrate Master Data Services (MDS) with Data Quality Services (DQS). By integrating MDS and DQS, you gain the ability to do data matching on your MDS data. Sounds cool enough, right? In this post I will show how to integrate these two tools and enable a couple of DQS options when using MDS.

First, let’s run through the typical worst case scenario. After installing MDS, the very last step is that long button which enables DQS Integration.

Clicking this button, the install program looks in the same SQL Server Instance which you just installed MDS into and searches for a database called DQS_MAIN. If you haven’t yet installed DQS, it will fail and you will get the following error message.

If you installed MDS before installing DQS, all hope is not lost. After installing MDS, simply install DQS and then come back into the MDS Configuration screen and enable DQS integration.
Now when you can click the ‘Enable DQS Integration’ button, you should get a success message.

Ok, so we have both installed and they have been integrated. Now let’s take a look at our new found powers. If we fire up Excel and click on the ‘Master Data’ tab, you can see that we now have access to the Data Quality tools as well!

With these tools, we can now utilize the matching rules which we can setup in DQS.

Without integrating DQS with MDS, you can see here in this ‘before’ screenshot that the DQS tools are not available.

(If you don’t have the MDS Add-in for excel, you can download it here: http://go.microsoft.com/fwlink/?LinkId=219530)

Here is a video tutorial on how to use the DQS tools with MDS:
http://msdn.microsoft.com/en-us/sqlserver/hh828790.aspx

Here is a link which describes using Data Quality Matching in the MDS Add-in for Excel:
http://msdn.microsoft.com/en-us/library/hh548681.aspx