Skip to main content

Using MDS Excel Add-In to Model and Manage your Master Data


About This Video:

The Master Data Services Excel Add-in is newly introduced in SQL Server 2012. It empowers business users / data experts to create and manage their models. This video presents the MDS Excel Add-in feature set and demonstrates how an information worker can leverage the power of Excel to manage master data in an agile and effective manner.

This video available with closed captions. To view closed captions, click CC on the video control bar.


Hi, my name is Tyler Graham and I'm a Senior Program Manager with Master Data Services.

I'm here to show you the power of the new Master Data Services Excel Add-in to provide information workers across your organization better control of the data stored in Master Data Services.

I've installed the Master Data Services Excel Add-in and I have a server in the background that's storing data about my locations.

So I'm going to click on the Master Data Services tab, which you can see up here in the top, and I'm going to start from scratch.

I have no connections so I'm going to Manage a Connection and I'm going to create a new connection.

All I have to do for this connection is give it a name and then write the simple URL to the website.

Very quickly I now have access.

I can see all of the models I have access to.

I'm going to deal with Location today.

I'm going to grab my data for locations.

I can do this in two ways.

The first one is I can just load the data into the system.

This loads all locations and in this case, there are not really a lot of locations.

So I can load all these locations into an Excel sheet.

Then it becomes an Excel table and I've all the features and functionality that I might want within the system.

So I can do any of these types of things to see my data across the system.

Now what I'd like to show you is that I can make changes within the system.

So any of this data that's stored within the system can be managed.

Now the first thing I notice is that I have Division here and it's not actually connected to my Division entity.

I can make changes to the Attribute Properties.

So I can Constrain List, I can pick my list Division, and I can say OK.

It now links these two things together so that I can only pick from the list of divisions in the system.

This is a very valuable capability in the fact that I now limit what people can put into the system.

Before I could have put any type of thing in there and it would be managed in the system and just published into the system.

But now it's going to publish those changes and give me an error saying that that attribute value is not valid.

I also have the ability to annotate any of my changes.

You can see here that there are really two types of Validation Status changes.

One is an Input Status and one is a Validation Status.

So as I corrected the Input Status it re-did the row and I was able to change that value, but it also found that Division is required, so validation failed for that member.

So what I'd like to do is be able to fix that issue, to come in here, pick a division and publish that change in.

I can also see that for any member I can view the transactions.

I can see all of the different things that have been changed within the system.

I can see at what time they were done, who made the changes, all of those things about each part of that member.

I can see any of the annotations that were made into the system.

Now we can see that I got Validation succeeded and all the rest are Waiting for revalidation, so I want to apply the rules to all of my system.

You can see now that all of these have Validation failed.

Something is wrong with these members.

If I hover over this little comment I can see the square feet must be greater than six hundred, division is required, other things.

Oh, and I also got an email notification that shows all of the issues that occurred in the system.

So I see all of the notifications of things that I need to fix.

If I click on any of these links, it'll take me into the WebUI to the specific member that has a problem.

So now I can make the changes within the WebUI.

Now, this is about Excel Add-in, so how would I do that if I wanted to do that in the Excel Add-in?

Well I have a number of features.

One is that I can come in here and I can filter these records.

So, I could obviously do it on the screen as inside my table, but I don't want to bring back every record.

Let's say that I had a million locations as opposed to just the couple of hundred that I have here, so I can add in a new filter that says Validation failed.

If I update the summary you can see that it's going to bring back twenty-one rows instead of the total seventy.

It's going to delete the data on the workbook, but that doesn't matter because it's being stored in my system.

So you now see all of the issues that I have here.

It's interesting because some of them are no Division, but then other ones are the fact that square feet must be greater than six hundred.

So we have this ability to manage all of this data in the system.

So, let's filter once more and filter out that Division_old column because we don't want to bring that into our system.

It's just showing us the old data we had in the system, so let's load data. Again, it's going to over-write my data.

Now, one of the values that we have here is that I can fix all of these things, but perhaps I'm not the right person to fix all the issues that we have here.

I know what the answer is for the square feet because if I look over here, I can see that square feet must be less than or equal to 600,000 and it's obviously 900,000.

If I go and evaluate that and go back and figure out the square footage, I find out that they've just added a zero here, that it's actually a ninety-two thousand square foot place.

This one it must be greater than six hundred and I found that they've missed the zero here and so we do that.

If I publish those changes into the system, it's going to see Validation succeeded.

Then, if I refresh my view, you're going to see that they disappear because my filter is that I only want to see the Validation failed.

Now, for the rest of these divisions I have a bunch of people that work for me and they have security on this so they can only see the divisions and update the divisions they have access to.

Well, I'd like to save this query so that I can use it at a later time for myself.

This is going to allow me to use this query whenever I want, but I also want to send this query out.

What this is going to allow me to do is send this query to all my direct reports.

When they open this query, they're only going to see those locations they have access to.

All the locations they don't have access to won't be there, but it's just going to be a link and when they double-click on it, as long as they have the Excel Add-in it's going to open up in their tabs.

So, that's going to give them the ability to make these changes, I'll delete that right now, and bring them back into the system. As they fix them, the Validation failed will go away and this will be gone.

You can also apply rules to my entire set or change settings in here for any of the data settings that are in here.

So the maximum rows of return, maximum columns, this is just going to give a filter warning saying that, the amount that I've selected to pull back is going to be rather large for my organization and, do I want to filter it down before I bring it back?

This really gives me the ability to manage all of the data across the system.

I can also bring in any other entities that I have by using Explorer again.

Go in here and look at Location and I can look at the Division entity and it will show me all the different divisions and, I can hide the status since it's not important at this point.

You can see here that I have all of my divisions and the regions that they're associated with.

Now, in Location, perhaps I want to add a location, so I'm going to pull another Location table.

Again, I can have as many sheets as I want with the same type of data.

Let's go to Location.

Location; I'm going to grab all the locations in the system and I just want to add one location.

So, it's a hundred thousand square foot place and I can pick my Manager from the list and I don't need to worry about the old.

So, you can see that I did not put in my code and that's because if I look at this entity in the system I've managed this code so it creates codes automatically.

This is going to allow me to manage this code directly in the system.

So if I pass this in, I'll publish that member into the system.

You can see as soon as I publish that into the system, it's been added and it was given the new code.

So my system is able to do that and give it the code next in line throughout the system.

If there were any issues here, let's say, that I had messed up this and had made it ten; it's going to publish the change into the system and then let me know that that member has failed.

This gives me full control of the entity and I can also see any changes to that record since it's been managed in the system.

Full control, full ability to see this and I can add any annotations or see any annotations that were added beforehand.

Hopefully, this has given you a good idea of what the power of the Excel Add-in can bring to managing data across your organization and helping leverage the already existing Excel features for managing data within Master Data Services.

Thank you.


Presented by: Tyler Graham



Video: WMV(Zip) | MP4 | WMV


Microsoft führt eine Onlineumfrage durch, um Ihre Meinung zur -Website zu erfahren. Wenn Sie sich zur Teilnahme entscheiden, wird Ihnen die Onlineumfrage angezeigt, sobald Sie die -Website verlassen.

Möchten Sie teilnehmen?