Master Data Services

Recently a client asked me the following question: What is Master Data Services, What is it for and what has it to do with SQL Server? I answered as succinctly as I could. It then occurred to me that the answer would make a good blog posting, as others may well be asking themselves the same question.
Master Data Services was first released with SQL Server 2008 R2. It is also part of SQL Server 2012, which adds some additional capabilities.

Master Data Services (MDS) is managed central data repository. It defines data schemas, applies business rules to validate the data and provides interfaces for external systems to use the data. Important to note is that it is not just a data dictionary it also stores the actual data.

Data can be versioned so that applications using the data can reference a particular version and are not forced to continually update to the latest version when the schema of the data changes.

Consider a simple example to put all this in to perspective: A centralised calendar to tracks the days when core business processes jobs run. MDS would contain:

·         Entity and attribute definitions to define the schema for the calendar.

·         Central business rules defining the validations to apply to the data. In this simple example there will be one validation rule that prevents calendar entries prior to 01/01/2012

·         The data for the calendar

·         The methods by which external applications can access the shared calendar data. In this case this will be a SQL Server database view, although other methods such as Web Services are allowed

To get started with MDS the first step is to create a database and the Master Data Manager web site for MDS. This is done using the Master Data Services Configuration Manager which comes as part of SQL Server. The resulting Master Data Manager web site is shown below:

The System Administration menu in the Master Data Manager web site is used to define:

·         A new Model called Calendar. This will hold the entity definitions and data

·         The JobType entity. This is a very simple entity that holds the Names of the types of jobs that are allowed: StockCheck, ProcessDirectDebits and IssueStatements

·         The Calendar Entity. Thus will consist primarily of a Name, Date and JobType

·         A business rule that prevents calendar entries before 01/01/2012

The various screens are easy to use. The one for defining the validation rule is:

The Explorer menu in the Master Data Manager web site is used to define the actual data. It is relatively straightforward to use, just pick an entity and add the data and apply the validation rules. The example below shows how one of the entries, the 30/04/1999 one, has failed the validation rule:

The last step is to use the Integration Management menu in the Master Data Manager to create the SQL Server views that will expose the Calendar for use by the external applications. In this case I created a view called CalendarView. The following SQL query uses the view to display the Calendar data in MDS:

SELECT *  FROM [MDS].[mdm].[CalendarView] ORDER BY Code

As well as the actual data in the entity, the view includes lots of other information, such as version number, entry date time, author,  and so on. Note that not all of the fields are shown in the above screen for space reasons.    

Admittedly lot of details have been left out of this simple example including: security, web service interfaces, APIs, interfaces to Excel, data imports, versioning and so on. However this information is readily available in the SQL Server Documentation and the MDS system as a whole is easy to use and manage.

So if MDS provides a service that you need then it should be relatively easy to get started.