SQL Server Master Data Services

The estimated reading time for this post is 5 minutes

The pain that organizations are experiencing around consistent reporting, regulatory compliance, strong interest in Service-Oriented Architecture (SOA), and Software as a Service (SaaS) has prompted a great deal of interest in Master Data Management (MDM). This blog post explains what MDM is and why it is important, while identifying some of the key MDM management patterns and best practices that are emerging. This blog post is a high-level treatment of the problem space. In subsequent blog posts, we will drill down into the technical and procedural issues involved in Master Data Management.

Organizations typically use different platforms to store different types of data. For example, sales data might be stored in an online transactional processing (OLTP) database, customer data in a dedicated customer relations management (CRM) system, and so on. Storing data across multiple, heterogeneous platforms can make it difficult to ensure that the data representing a single instance of a specific business entity is consistent and accurate across the enterprise.  Master Data Services provides a way for organizations to standardize and improve the quality, consistency, and reliability of the data that guides key business decisions. This blog post introduces Master Data Services and explains the benefits of using it.

What Is Master Data?

Most software systems have lists of data that are shared and used by several of the applications that make up the system. For example, a typical ERP system as a minimum will have a Customer Master, an Item Master, and an Account Master. This master data is often one of the key assets of a company. It’s not unusual for a company to be acquired primarily for access to its Customer Master data.

SQL Server Master Data Services

Master data management can represent a major challenge for organizations. Data representations of a single business entity, such as a specific individual customer, might be recorded in multiple locations in multiple formats. When you consider the number of different types of data a company might own, the potential scale of this problem can be huge. SQL Server Master Data Services enables organizations to standardize data, which improves the consistency of their key business data, and ultimately, the quality of the decisions that they make.

master data 1

Data owned by an organization is one of its most valuable assets, and businesses rely on it for a variety of different reasons. For example, individuals in a company might use data to develop marketing strategies, plan new product lines, or identify areas where they can make efficiency savings. Report writers and data analysts interact directly with this data and decisionmakers use the data to guide them when they make key choices about future business strategy. However, companies often struggle to maintain the quality, consistency, and accuracy of their data across the enterprise for a number of reasons, including:

  1. Decentralized data storage: In modern, complex information ecosystems, data may be stored in multiple systems and formats, perhaps because of departmental differences or as a result of company mergers or acquisitions. This approach makes it difficult to identify where duplicate data exists, and if it does, how to identify a ‘master’ version. 
  2. Different methods of handling data changes: Different applications might handle data changes, such as additions, updates, and deletions, by using different rules, and this can result in inconsistencies. For example, if one application records addresses without requiring a postal code but others do, the formats of stored addresses will be inconsistent.
  3. Human error: Errors in the insertion and updating of data can lead to inaccuracies. For example, if a user misspells a customer name, applications will accept the input as long as the format is correct, and the error will not be identified.
  4. Latency and non-propagation of changes: Changes to data in one system may not propagate to others where the same data is held, or it may do so with a time delay. Running reports against these diverse systems will yield different results.

Poorly managed data directly affects the quality of reporting and data analysis, and can ultimately result in inefficient procedures, missed opportunities, revenue loss, customer dissatisfaction, and increased time spent managing the data to try to solve these problems.

master data 2

SQL Server Master Data Services is a master data management technology that enables organizations to handle the challenges of data management. SQL Server Master Data Services can serve both as a system of entry for creating and updating master data, and a system of record for making authoritative data available to other applications. With SQL Server Master Data Services, you can create a master data hub to consolidate and ensure consistency of key business entity data representations across the enterprise. SQL Server Master Data Services enables you to enforce data validation rules and track changes to master data through an audit trail that shows the time, date, and author of each change. This promotes accountability and helps organizations to comply with data regulation requirements.

Who Manages Master Data?

A data steward is an individual charged with managing master data. Usually a data steward is a business user with a detailed knowledge of the entities used in a particular area, and who is responsible for ensuring the integrity of data relating to those entities. Data stewards must ensure that each item of data is unambiguous, has a clear definition, and is used consistently across all systems. A data steward will typically use a master data management system, such as Master Data Services, to perform these tasks.

Implementing a successful master data management initiative involves establishing extensive cooperation among the various stakeholders and the owners of the data, and this may not always be straightforward.

SQL Server Master Data Services Components

Master Data Services includes the following components: 

Master Data Services database: This database stores all the database objects that support Master Data Services. This database contains staging tables for processing imported data, views that enable client access to master data, and tables that store the master data itself. The database also supports additional functionality, including versioning, business rule validation, and email notification.

Web application: You use this application to perform the tasks associated with managing Master Data Services, such as creating models, entities, hierarchies, subscription views, and business rules. You configure the Master Data Manager Web application by using the Explorer, Version Management, Integration Management, System Administration, and User and Group Permissions functional areas. Apart from the Explorer, all functional areas are restricted to Master Data Services administrators. Data stewards can use the Explorer functional area to manage the master data for which they have responsibility.

Configuration Manager: You use this tool to create and manage the Master Data Services database and Web application, and to enable the Web service. You can also use it to create a Database Mail profile for Master Data Services to use. 

Add-In for Microsoft Excel: Business users and administrators can use this add-in to manage Master Data Services objects, and to work with master data in a Microsoft Excel workbook.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz