SQL Server Master Data Services series
12 things you’re missing out on if you’re not using SQL Server MDS.
Steve Moss | July 9, 2015
As you know, SQL Server comes with several major components, including the Database Engine, Integration Services, Analysis Services, and Reporting Services.
One component you might not know: SQL Server Master Data Services, or MDS. MDS can be used to solve important business problems related to data, particularly how data can empower non-IT business users.
Even if you don’t have a full-fledged Master Data project, there are use cases where Master Data Services brings helpful tools to the table. For example, if you have data in Excel that your business wants to manage for compliance and/or wants to integrate, or have a requirement to augment data in a data warehouse dimension (such as adding and maintaining an attribute or grouping that isn’t present in the upstream system), MDS has functionality that can help you solve the problem.
This first post in a four-part series will outline some of the core MDS functionality. Future posts will provide detailed walk-throughs of each functionality.
Here are 12 things you’re missing out on if you’re not using Master Data Services to help solve these or similar business problems.
1. The ability to use Excel to insert and edit multiple rows of data and save them to a secure database/repository (a free add-in within Excel)—without any coding or SQL required!
After installing the Excel add-in the ribbon looks like this:
This enables simple data entry, editing, and publishing to a secure database with full change tracking:
2. The ability for Data Stewards or Business Partners to add Entities, Attributes, and change the metadata and datatypes in Excel, again, with no coding or SQL required.
Here’s a view of how easy it is to create an Entity (table) using the MDS Excel add-in:
It will bring up the following dialog:
Besides looking differently in Excel with the metadata, you have actually put the data in Master Data Services where the data, changes/transactions, and metadata are logged. You can add/edit in Excel as shown previously.
3. A system that exposes data, metadata, and change history so that you can export it and sync up other systems. Yup, you guessed it: No coding or SQL required.
4. Built-in features that can help you pass your Change Control/Compliance audits about data changes. Without coding, it can track every change data, including the before and after values.
5. Built-in interfaces to import and merge data, allowing you to control the behavior, whether it is an insert, merge (either insert or update), soft-delete, or more.
6. A Master Data Platform that comes with a SQL Server license (Enterprise, BI Editions), assuming that you’re installing an existing SQL Server, instead of a huge standalone price tag.
7. A very robust and configurable security model that can edit data including hierarchies.
(More on this in a future blog post. This is something that isn’t always easy to implement and maintain if you code it yourself, but it’s already been “plumbed” for you in the MDS platform.)
8. A web front-end to manage metadata and data.
9. A WCF object model that enables integration with other systems, metadata, data, and attribute management, and APIs for import/export needs. For example: http://mdsutilities.codeplex.com/.
10. Basic, built-in workflow/approval that is programmable and configurable. More here on how to create a custom workflow (Master Data Services).
11. A web UI in which business users can configure business rules for data entry.
12. Seamless integration with Data Quality Services to find possible duplicates or other data quality issues.
If you’re not using Master Data Services, you’re missing out on a richly-featured platform and functionality to help you solve business problems. This series will work through solving a business problem using Master Data Services to illustrate these functions of MDS and provide detailed explanations of how each can be set up and work effectively.
Steve Moss is a data and business intelligence consultant in Slalom Portland’s Information Management and Analytics practice. Steve is passionate about data quality, the performance of data systems, and delivering enterprise solutions for data architecture.