Data Quality in SQL Server 2012 Part 5: Master Data Services Excel Add-In

In the previous post in this series (sorry for the delay), I talked about the Client for DQS. It seems appropriate then that I introduce the desktop client for MDS. In this case, the client is Excel, through a freely-downloadable add-in that runs in Excel 2007 or 2010. You can download the add-in from the Microsoft downloads site.

Once installed, which shouldn’t take more than a couple of minutes, a new tab appears in the ribbon of Excel. This is the Master Data tab.

 

Moving from left to right, you first need to connect to the server running Master Data Services. In the screenshot, most of the functionality is greyed out because there is no connection. Once you’ve connected to the MDS server, you can pull in data models and the members stored within the database.

The explorer pane lets you see the various models on the MDS server, the versions of those models and the entities within them. You can select a particular entity within a model and Excel will connect to the server and pull down the members of that entity, displaying them as a table within the Excel sheet.

The refresh button is a fairly obvious one. This connects to the server and pulls down any changes to the data, any new members and any updates to the entity, based on the query.

By default, when you connect to the MDS database, the add-in will retrieve all the members of the selected entity. The filter button allows you to choose specific columns to pull down or filters to apply to the data, so you don’t download everything. Applying filters builds up a query which allows you to see certain elements of the data within the MDS database, such as only those members requiring validation, or filtering customer contact information based on the internal account manager to delegate the maintenance of these elements.

Once you’ve built your query using the filter button, you can save it so you can get access to key data more quickly next time. You can also package up the query and send it by email, so others can view the same data. This means that someone who understands the whole of the data could create these queries and then share portions of the data with those delegated the tasks of administrating it.

Within the Excel add-in, you can make changes to the data in the MDS database and add new members. You can even make changes to the model by adding new attributes. When you’ve made these changes, you need to push them back to the server. This is done with the publish button. Clicking on this will send your changes to the server to be validated and processed.

Delete is another button whose purpose should be obvious. Click this button and the selected row(s) will be deleted, not just from Excel but from the MDS database. The hover over hint for this button includes the warning that this action can’t be undone, so be sure you mean it before you hit this button.

The Combine Data button allows you to bring data in from an ordinary Excel worksheet and combine it with the active data from MDS. You can map columns between the MDS and external data to create an expanded table containing the joint data.

If you have business rules defined on the MDS server, then the Apply Rules button will, as the label implies, run them on the data in Excel.

Show Status will display information on the validation status of the members and whether the input has been changed. This can be useful if you want to filter data and only show ones that required validation actions.

The next two buttons are around managing the data model itself, instead of the members of an entity. Create Entity allows you to add an entity to the selected model on the MDS server. Similarly, the Attribute Properties lets you create an attribute. If you want to create a new attribute, you first create a column in the Excel table and give it a heading. Once that’s selected, you click on the Attribute Properties button to determine the type of attribute you wish you have.

The Settings button gives you options for the add-in, including warnings when large amounts of data are going to be retrieved or options around the formatting of different values.

The final button on the tab is Help. Guess what it does.

Comments

• Anonymous
  October 21, 2014
  This addin does not install to Excel 2013. Furthermore, all of the available information on workarounds is vague and useless. Users are advised to download the SQL Server 2012 SP1 Feature Pack to resolve this issue, but the Feature Pack is a collection of about 20 or so pieces, none of which point to or resolve the issue (I installed them all). This is a ridiculous oversight on the part of Microsoft.