Microsoft Master Data Services

Master Data Services, initially released as part of SQL Server 2008 R2, is Microsofts answer to the Master Data Management challenge. Master Data Management (MDM) is a set of tools and processes that aim to deliver a single clean and consistent view of each master data entity (product, customer, employee, financial accounts, etc.) that exists within the organization.

Master Data Services (MDS) was already present in SQL Server2008 R2 and was the result of the integration of the acquired technology of Stratature into SQL Server. With SQL Server 2012, we got a more robust and mature solution because of the progress made both on usability via a new MS-Excel Add-in and on the performance of the product with enhancements in the Business Rules modules.

The most important modules of Master Data Services are:

  • System Administration : In this module an administrator can create a new entity, define attributes specifying whether those attributes are of "free form type or derived from another entity. On can also activate the possibility to build a parent-child hierarchy (so called "explicit hierarchy) on top of an existing entity. Last but not least, there is also the possibility to define "business rules (IF Conditions THEN Actions) to ensure that the content of an attribute meet existing business rules.
  • Explorer Module : It allows data stewards, business users responsible for a particular data set, to edit members of an entity and to manage the explicit hierarchies.
  • The MS-Excel Add-in : It will allow data stewards to edit members of an entity leveraging all the familiar functionality of Excel. New entities can be created directly from Excel.
  • Integration Management : One can also automate data loads via SQL Server SSIS into predefined staging tables, for every entity corresponding staging tables are created automatically. Via the "Integration Management menu, the end-user gets the possibility to transfer the data from the staging tables into the Master Data entity table. To share MDS data with other systems, you can create subscription views. Subscription views are SQL views in the MDS database.
  • Version Management : In this module we can create different versions of our master data, which can be useful for archiving, testing, what-if, or auditing purposes.
  • User and Group Permissions : This module is used to assign permission to master data using Active Directory.

One of the many BI scenarios for which this product will add value is the management of user defined hierarchies. Now there is a built-in easy to use (integrated with Excel) solution for the end-users to directly manage the updates in a controlled way. This will be a great relief for all users who previously had to build ad-hoc solutions in order to load Excel files with master data-logic or mapping tables directly into the Data Warehouse data integration layer.

With Master Data Services 2016, several enhancements have been made to the product:

  • Improved performance. This is especially noticeable when using the Excel add-in with an entity consisting of thousands of members. MDS also adds data compression to the underlying tables and you have the possibility to add extra indexes.
  • The security model has been refined. You can now assign read, update, delete, create permissions. New roles have been introduced: the Super User and Model Admin.
  • You can sync entities between models.
  • You can merge conflicts when an entity has been updated by multiple persons. You can also get a detailed look of the history of a member. Its possible to create a view on top of this history that resembles a type 2 slowly changing dimension.
  • Its easier to delete members (purge) from an entity.
  • Business rules and derived hierarchies have been improved.

Contact us for further evaluating Microsoft Master Data Services or learning more about our skills in this area.