MDS or Master Data Services is a component of Microsoft SQL Server introduced with SQL Server 2008 R2, it was a basic component at first with some irregularities but in its later versions i.e. 2012/2014 it was a much improved inclusion in the SQL Server family.

MDS uses a Master Data Hub as a base repository that can contain data from multiple sources with each source known as Spoke. MDS allows to create discipline and make the data across multiple instances clean and consistent.

It uses a basic object to organize and structure Master Data called ‘Model’. There may be multiple models in MDS each having different versions, the default is automatically created and is named VERSION_1. Versioning in MDS helps a lot in the agile way of applying business rules to the Master Data.  It is much easier to have a live version of the model that is in use while working on upgrades to the same model in a different version, we can lock a version and after validation commit it, once a version is committed users cannot make any change to that version.

A more granular component of Master Data Service is Entity, multiple entities are contained in an MDS model, each of them carrying records just like a table in Relation Databases, entity is defined by multiple attributes that are described as the columns and members which are the records or rows in the entity. Each entity can contain a unique attribute that is used to ensure that there is no duplicate data in the entity, it can also contain lookups in its attributes to make sure relevant data is used.

Application of Business Rules is a big feature in SQL MDS, it helps cleaning of data and to make sure the data is correct. Business rules can be applied to an entity through Administration Module Master Data Manager, there are numerous rules that you can apply to an entity like making columns mandatory, making sure that correct pattern is used while entering data etc. We can also improve the Data Quality by applying Business Rules on it.

Master Data can be accessed in two ways, i.e. with the help of the Web Interface that is hosted on IIS in the Master Data Manager or we can use MDS Excel Add-In that will add a tab with Master Data operations in Excel.

We are going to explore the Web Interface for Master Data Services in this blog, we will shower some light on all the features that come with MDS, once the installation phase is complete and you have configured IIS to host the web interface, the home screen looks something like below screenshot, you can access any of the available options according to the needs.


System Administration is the core component of SQL Master Data Services, actually, this is the module where you create and modify your models, entities, business rules etc. You can create multiple models in this section, apply different business logic on them, set the hierarchy of the objects etc.



The second most important is the Integration Management, in the main page of this module, there are options to choose your model and the Batch Grid will show up the pending data in the Staging table that needs to be processed. In the grid, you can see the entity, the version etc. with respect to the staging table that has the data. You can also create subscription views through this model to specify the viewership of the data.



Version Management is also an important part of Master Data Services, you can create multiple versions of the same Model, which will help to add new business rules on the same model while also having a standard version of it. Once, the model is finalized it is Locked from the Version Management in MDS Web UI and then committed so that it’ll be used as the current LIVE version of that model.



Explorer Menu of MDS is the main module where you actually work with the master data, add new members, define hierarchies, apply business rules to your raw data that came from the staging table. Once you apply the rules, you see a dialog that shows you the validation status of your data. Also, there is a space on the right pane that shows you the description of Validation Errors, if any.



The last module to discuss is the User permissions module and it deals with the security of the data and defining that who can access your data and to what level and roles. The default user is the Administrator who has all the rights by default.


Stay tuned for the next blog post where I will be discussing how we can leverage MDS with Master Data Management (MDM) of Microsoft Dynamics AX 2012.



Leave a Reply

Recent Comments