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:
·
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
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.
So if MDS provides a service that you need then it should be relatively easy to get started.
