Master Data Services Excel Add-in: What to Know and Alternatives

In a previous article, we provided an overview of Master Data Management (MDM). In this article, we’re going to look at Microsoft’s platform for supporting the discipline of MDM: “Master Data Services”. Specifically, we’re going to look at how the Master Data Services Excel Add-in can be used to organize your MDM data models and manage the data update processes. We’ll also look at a simple alternative to MDS using the SQL Spreads Excel Add-In.

Master Data Services (MDS): An overview

What is Master Data Services?

Master Data Services (MDS) is Microsoft’s platform for supporting the discipline of Master Data Management (MDM). MDS allows you to manage a master set of your organizations’ data, which typically involves the following:

• Organizing master data using a model data structure
• Creating rules for updating the data
• Controlling who updates the data
• Sharing the master data set with key stakeholders

We’ll look at these in more detail below:

MDS Models

In MDS, the model is the highest-level container in the structure of your master data. You create a model to manage groups of similar data, for example, to manage product data.

A model contains one or more entities, and entities contain members that are the data records. A model is therefore like a database, and entities are like tables.

Attributes are objects that are contained in MDS entities. Attribute values describe the members of the entity. Members are the physical master data and are like rows in a table.

The diagram below shows part of a model to manage product data. Note that the ProductSubCategory attribute uses the SubCategory entity.

MDS Business Rules

Business rules are used to ensure the quality and accuracy of your master data. They are used to validate data before updates are made to the master data set. Validation typically involves checking to see if entered values meet specific criteria for an attribute (eg Cost must be > 0) and then taking an action (eg flagging an error or setting a default).

MDS Permissions and Roles

Permissions and roles in MDS are used to ensure that users have access to the specific master data necessary to do their jobs and to prevent them from accessing data that should not be available to them. They are also used to define who can create and edit models, and who can approve data updates.

Where do I get Master Data Services?

MDS ships as a part of the Microsoft SQL Server relational database management system. It can be enabled as a feature when you run the SQL Server setup.exe.

At this point, I need to warn you that installing and configuring MDS is not as simple as you might hope. At some stage, you may well end up cursing as yet another obscure missing pre-requisite is flagged at some stage of the process.

The main steps in the process are summarized below and detailed instructions can be found here.

1. Install Master Data Services using SQL Server Setup. In the installation wizard, select Master Data Services on the Feature Selection page under Shared Features. This will install Master Data Services Configuration Manager, various assemblies, folders, and files for Web applications and services.

2. Run the Master Data Services Configuration Manager. Here you’ll create and configure the MDS database and Web application. Once this is done, you can use the web UI to manage models and business rules.

2. Deploy the Sample Models and Data. The MDS installation includes sample models and data files which you should install as they’ll help you understand the model structure. To install the samples, you need to deploy the model package files using a command-line utility called MDSModelDeploy.

The web UI screenshot below shows the Supply Chain group of attributes in the Product entity in the Product model.

What is the Master Data Services Add-In for Excel?

There are two ways to interact with master data in MDS: the web UI or the MDS Add-in for Excel. There is some overlap in the capabilities of each, but generally, the web UI is aimed at administrators that need to create models or business rules, whilst the Excel Add-in is for users to make updates to master data using Excel.

Using the MDS Add-in for Excel

The installation of the Master Data Services Excel Add-in is not included in the main installation process.  Installation is, however, a simple process and you can download it here.

Once installed, the Master Data menu will be available on the ribbon in Excel.

The next step is to create a connection to the MDS database that you created in the MDS Configuration Manager. Click on the ‘Connect’ button and then click the ‘Manage Connections’ button to bring up the Manage Connections dialog. Click on ‘Create a new connection’ and enter a name for the connection and the MDS Server address – this is the address you created in the configuration manager.

Once the connection has been created, the Master Data Explorer is displayed on the right-hand side.

You can now select a Model from the drop-down list to load the data into Excel.  When you select a model, the entities within the model are displayed in the data explorer. You can now select the entity you want to work on and the data will load into the active sheet in Excel.  Note that once you have loaded data into a sheet in Excel, the data explorer is disabled. To view data in other entities you need to create a new sheet in Excel and select the Model > Entity from the data explorer.

Ok, you now have your Master Data in Excel – what next? The most common use cases for the MDS Excel Add-in are:

• Updating Master Data: this could include making updates to attribute values or adding in additional members (rows).
• Viewing Master Data: sometimes you need to look up or reference master data when preparing reports or other documents.
• Auditing Master Data: this could involve checking when and by whom data was updated, or the validation status of attributes.

Updating Master Data

Making updates to master data is the most common use case. Let’s look at an example to see how the process works.

In the screenshot below you can see the data for the Product entity.  We’re going to update the values for the StandardCost attribute.

As you make changes to data in Excel, the updated cells are highlighted. These will remain highlighted until you publish the data back to the MDS database in SQL Server.

Once you have made all your changes, you can click the ‘Publish’ button. When you do this, the following happens:

• You are required to enter annotations for the changes. You can either enter one annotation to cover all the changes or specific annotations for each change.

• When you click ‘Publish’ on the dialog box, any Business Rules are applied, and data is also checked against allowed attribute values (for example, number of characters or type of data).
• If the MDS Administrator has configured an entity to require approvals before any changes are published, you’ll be prompted to save the pending changes to a change set. The relevant person will then need to approve the change set before the changes are written back to SQL Server.

Auditing Master Data

You can view when and by whom changes were made by clicking on the ‘Show Audit Info’ button.  The status of any business or validation rules can be viewed by clicking on the ‘Show Status’ button.

In the screenshot below you can see that validation against one of the business rules has failed for some of the members because the rule ‘StandardCost > 0’ has not been met.

3 Reasons to Consider an Alternative to MDS

Microsoft’s Master Data Services platform ticks plenty of boxes when it comes to Master Data Management. It does, however, also come with some negatives:

• Complexity: Despite offering an Excel Add-In to support the adoption of the platform across a range of users, MDS can still feel overly complex for organizations that are starting on their MDM journey.
• Installation & configuration: MDS has been well thought out in terms of architecture and is built on a robust SQL foundation, but the installation and configuration process is not as straightforward as it could be.
• Limited community: MDS does not seem to have a large community from which to get advice or guidance.

SQL Spreads: An alternative to MDS

SQL Spreads is an Excel add-in that allows you to use Excel as a front-end to update data in SQL Server. It includes features which support a Master Data Management initiative, including change tracking, data validation, role-based access, and more. Let’s look at a simple example to see how this could work.

We’re going to use the AdventureWorks database for our example. In the screenshot below we’ve used SQL Spreads to connect to the AdventureWorks database and import the Products table into Excel.

We’re now going to assume that we are a data steward for the Product data and we’re going to do the following:

• Set up data validation for specific fields: (1) make the values in the ProductSubCategoryID column use a look-up from the ProductSubCategory table; (2) add a data validation rule to ensure that the StandardCost column has values greater than zero.
• Enable change tracking so that we can see when values in rows were changed and by whom
• Make changes to some values and save the changes back to the Product table in SQL Server.

Data Validation

By default, SQL Spreads will always validate all entered values against the Data Types in SQL Server. There are a couple of other ways that we can enforce validation.

The first is to force the user to select from drop-down lists when entering certain data types such as categories, colors, sizes – master or reference data, in other words. You can either specify a static list in SQL Spreads or lookup values in another SQL Server table. Lookups are set up in the Columns tab in the SQL Spreads Designer. The screenshot below shows how we specify that we want to look up the ProductSubcategoryID in the ProductSubcategory table and return the Name field to display in the cell.

Once this is done, the user is limited to selecting from the values for Subcategory that are contained in the ProductSubcategory table.

The second way is to use a data post-processing script in SQL Spreads. The data post-processing script is a SQL query that will be executed as the last step in the transaction that updates the database with the changes made in Excel.

To add a Data Post-processing script in SQL Spreads, open Document Settings and click the Edit Post-Save SQL Query button.

In the Post-Save SQL Query dialog box, we can now enter our validation script.  The validation script contains the logic that we want to check and an error message that we can display to the user.

The example below shows a SQL query that checks if any of the values entered in the StandardCost column of the Product table are invalid (ie less than or equal to 0).

Now, if the user enters an invalid value in the StandardCost column, when trying to save to the database, the following message will be displayed, and the update transaction will be rolled back.

Change Tracking

To enable change tracking you need to specify the columns in your SQL table into which the username and date/time will be inserted whenever changes are made in Excel.

In the Product table in SQL, we therefore need to add the following columns:

ALTER TABLE Production.Product
ADD CreatedBy VARCHAR(255),
ModifiedBy VARCHAR(255),
CreatedOn DATETIME,
ModifiedOn DATETIME;

Once these columns have been added, 0pen the SQL Spreads Designer and click on the Settings tab.  You can now select the CreatedBy, CreatedOn, ModifiedBy, and ModifiedOn columns from the drop-downs as shown below.

Now, when we make changes and save them to the database, these columns will be populated.

Data Changes

The process of making changes to the data is obviously easy, as we are working in the familiar environment of Excel.  With the kind of validation and lookup fields that we’ve described above, users can be comfortable working with critical data in the knowledge that mistakes are unlikely to happen.

If required, additional settings can be applied to, for example, limit users to only changing data or adding rows and not allowing row deletion.  

Summary

In this article, we’ve looked at Microsoft’s MDM platform – Master Data Services.

Under the hood, MDS is a powerful enterprise-grade solution for a Master Data Management initiative. Microsoft’s release of the Master Data Services Excel Add-In makes it easy for a wide range of users to manage their master data.

As we’ve seen, however, MDS may be too much for some organizations that would like to perform a simplified version of MDM. In which case, the use of a simple tool like SQL Spreads can go a long way to instilling some of the basic MDM principles into an organization.

Click here to download  the trial version of the SQL Spreads Excel Add-in, or get in touch to find out how SQL Spreads can help you get started with Master Data Management.