Share via

Demo Transcript: Managing Data With SQL Server 2012 Master Data Services

  • Article

SQL11UPD05-TSCRIPT-04

This wiki is a transcript of a previously recorded video.

Related content assets:

 

Managing Data With SQL Server 2012 Master Data Services

Welcome to this demonstration, Managing Data with SQL Server 2012 Master Data Services (MDS). My name is Peter Myers. I am a SQL Server MVP and a Mentor with SolidQ.

 

In this demonstration I commence by launching the Master Data Manager. This is a web application typically used by the administrators to manage the Master Data Services environment and so with the intention of this demonstration to go ahead and create a master list of the resellers that are used in the organization. So I start by selecting System Administration and in here I am able to go ahead and add in a new model, which I’ll provide the name AdventureWorksBI. And I de-select this check box because I only want a model that is empty and then I am going to go ahead and create the model content initially by working with the new Excel Add-in for MDS. So I create the model and now we have this empty model name AdventureWorksBI. And now the rest of the work that I do is typically going to happen inside Excel. So I go ahead and launch Excel and note here very quickly, and in fact it was too quick, in the splash screen we could see that the add-in for MDS was being loaded – the evidence that we have that the add-in has been installed is that we have the Master Data ribbon sitting here.

 

So the first thing that I do is go ahead and open up from my demonstration resources: an Excel workbook that contains a list of the resellers that I need to then import to create a master list in MDS. In order to work with the MDS instance here on the Master Data ribbon, I use this to connect and manage connections. I don’t have any defined by default so I click New. I provide a description and then the URL to the server itself, which on this machine is KIWI on port 82. All right, I can go ahead and test that connection and it’s having a good think about it. All right, we have success so then I can go ahead and use that connection by connecting to it. And so now what we find in Excel on the right side is that the Master Data Explorer is open. That allows me here in the dropdown list to toggle between the models so think about models like containers. In a sense like databases in the relational world. And understand also that we can have versioning within models and by default when you create a model there will be the initial version and in this case VERSION_1.

 

So it is very, very easy to create entities, these are the master lists that are stored in Master Data Services. And so what I have before me in the worksheet named Reseller is a range selected or ready that is a table consisting of the ID, the reseller and address details. So here on the Master Data ribbon, I simply click Create Entity and that range is automatically copied into the first box, and yes, the range includes headers. The model that I wish to create the entity in is the AdventureWorksBI and the first version. The name of the entity will be Reseller and then it’s mandatory that there is a unique column used as the code so the Reseller ID is the perfect candidate for this. And then optional is that there is a name attribute also. And so the name of the reseller is a perfect fit in this case. And so with this configuration, simply by clicking OK, what the add-in will do is create the entity consisting of these attributes, the columns of the table. And then it’s uploading all of this data to become the initial members of the list. Once it’s succeeded, what it does on this new worksheet in my workbook – so note the name on this worksheet is Reseller 1 – it’s very much connected to the instance and then it said to just open up the list and take a look at it. So we can see now in terms of the attributes – so there is the name and the code, both of these are mandatory attributes for an entity in Master Data Services, and then we have the custom attributes of Address, City, State and Zip. Scroll down to the very end, there is something I need to point out to you. That is that we have a reseller here by the name of World of Bikes. And for those that know their geography reasonably well they might appreciate that Honolulu is not in the state of Oregon. All right, in fact we have an error here in the data. And we are going to address that as part of the data correction that we do in this demonstration.

 

I am going to create a second entity and so what we discover is that in the State attribute, it is possible for me to come in and type any value that I want. I could type in Hawaii here, which is in fact the correct state. I am going to press Escape to undo that change because my requirement is that I do not want users or those that are managing the data in this list to choose a state that doesn’t exist. And so what I am going to do is select the state and here on the Master Data ribbon, in the Attribute Properties, I change the type to become a constrained list. In other words, the values that are provided in this list for that attribute must be defined in another attribute and constrained. We cannot choose a state that does not exist. And so what this window is allowing me to configure is the creation of a new entity that I’ll name State that will then be populated with unique values presently found in the State attribute of the Reseller entity. So I click OK, it creates the State entity and it takes those distinct values and it uses them to populate the code and the name attributes of the state.

 

Take a look now in this attribute that it is really a dropdown list, this constrained list of states that we can use to find the resellers within the organization. Now when I right click the Reseller 1 worksheet, I can go ahead and delete it. We should not be concerned, it is not actually deleting the data on the server itself. It’s just freeing up space in my workbook. Then I go ahead and create a new worksheet, and here in the Master Data Explorer I select my model and I simply double click State. And this is the way we load an entity that allows us to interrogate the members we have defined and also to insert new, delete existing, or modify the existing values that we have for members. And so one thing we see here is that when you create an entity in this way, both the name and the code are the same. It would make much more sense if the name was actually the full name of the state. So here in the State Name worksheet, I actually have the full state name value, so I copy them and I paste them in very much to simulate this data entry activity here inside Excel. So note the different color here. The background has changed to represent that the value is different from the value that was retrieved when we loaded this entity. And then what I can do is scroll to the very bottom of this table and I can bring in that I am going to add a new member named Hawaii that has the code HI. Here on the Master Data ribbon I simply click publish and we have the option then to provide annotations that describe the newer changes that we are making here. So updates to the state members. And then I click Publish and those changes are now being published to the server. At this point I can delete this worksheet as well.

 

So that is very much the capabilities of the new add-in in Excel. It is very, very convenient for users, providing that they have permission to connect to an instance of Master Data Services, to then go ahead and load entities. And maintain the entities if they have permission, they could introduce new attributes, they can change the values, they can insert new members, they can delete existing members.

 

There have been enhancements to the Master Data Manager also in the SQL Server 2012 release. So by clicking the SQL Server logo that takes me straight back to the main page, and then what I am going to do is switch across to the AdventureWorksBI model. We also have the capability to explore and manage the members of our entities using the Explorer here. Now what Explorer does is it uses Silverlight capability and allows me to then go ahead and explore the members of my different entities. By default it has opened up the Reseller entity, and of interest is that the reseller named World of Bikes that presently is defined for the state of Oregon, yet the city is Honolulu. Let’s go ahead and locate it and make a change. So I am going to go ahead and apply a filter. The filter will be with the name is like and I’ll just use a wild card match. Anything that begins with the word “world”. So I apply that filter condition and then it’s isolated simply two members and here it is, World of Bikes. Honolulu with the incorrect state. So I can come across here and use the dropdown list and then select the state of Hawaii. And for an annotation that keeps track of what I am doing, “data correction”. Quick OK and the Master List is being updated. Now in addition to this, if I view the transactions, we get to see a complete log of what has happened, by whom and when. So we can see here at this point in time the prior value of Oregon has changed to Hawaii and it was me that did this.

 

And so it is all really well to collect this information in Master Data Services but how can we make use of it generally. An improvement in SQL Server 2012 is the facilitation of exporting the data via subscription views. So back on the home page of Master Data Manager, here under Integration Management, I am going to define a subscription view that allows us to retrieve the Reseller information. And this could be of use for example by Integration Services for populating a reseller dimension table in the data warehouse. So I go ahead and create a view and the view will simply be named Reseller based on the AdventureWorksBI model and its first version. The entity it is based on is the Reseller entity. And I simply want all of the leaf members – essentially, it means all of the members of this entity. And then I go ahead and click Save. So what that has effectively done is created a view inside the Master Data Management Services database. So here in this instance it happens to be named MDS and if we take a look at the views, here is the subscription view, simply named Reseller. And so I right-click “Select Top 1000 Rows”. We get to see now that we have this simple interface, simply a view in order to access the members of the entity and in addition to the attributes – be they the fixed attributes that come with the product or the custom attributes that we have – all of the information is available here. And anyone that can write a basic SELECT statement, and has permission of course, can go ahead and connect to this database and retrieve the members from the entity.

 

So that pretty much concludes the demonstration working with the new features in Master Data Services starting with the improved Master Data Manager, the add-in for Excel, and then the improved experience of exploring data in Master Data Manager. Thank you very much for watching.

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]