Master Data Services (MDS) is the SQL Server solution for master data management.I use this product quite frequently at various clients. Why?
Many organizations have data residing in several departments. Sometimes the data is different. The spelling may be different, the address may be different, etc.
MDS allows a centralized location to manage strategic business data to be consumed in various internal applications and reports and dashboards.
MDS has a web front end which can be installed with the SQL Server install discs. It must be configured on a server by specifying the database to run the web and store the data.
Here are the settings for the database:
And web configurations:
And here's the web piece used by the internal clients:
The top level is called a Model, similar to a Database. Below that, are Entities, which are similar to Tables. And Attributes are similar to Fields.
You can create Hierarchies and Collections as well:
How do you get data into MDS? Four ways.
First, after your Model, Entity and Attribute are created, you can enter data on the web.
Second, there happens to be a plug in for Microsoft Excel, which allows connection to the MDS server, where you can view Entities, change data, Publish the changes. And another nice feature, you can simply create the Entity from an existing Excel file. I prefer this method for simplicity.
Third, there are Stored Procedures with the MDS database in SQL Server. Basically you load your data into Staging tables, call the appropriate Stored Procedure, and it uploads or delete or modifies the data based on which procedure you call. This is useful for automating in an SSIS package for example.
Fourth, you can do batch imports under Integration Management, however, I've never used this option:
How do you get data out of the MDS Database. I typically use a View. Which you can create in the Web console under Integration Management, under Create View:
The views appear in SQL Server in the MDS database, with a prefix of "mdm".
The best business case for using MDS is to allow the Business to control their data by adding records on demand, through a variety of consoles, in a centralized location and removing the dependency on the IT staff to run their business.