Book Image

Microsoft SQL Server 2008 R2 Master Data Services

Book Image

Microsoft SQL Server 2008 R2 Master Data Services

Overview of this book

Table of Contents (18 chapters)
Microsoft SQL Server 2008 R2 Master Data Services
Credits
Foreword
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

The need for Master Data Management


Unfortunately, no business of any significant size can run on just one piece of software—businesses need different software to carry out specific tasks. There is never just one user interface and one database to deal with the different functions that need to be carried out.

Large enterprises will always have more than one of the following list of systems that will require the business master data in some way:

  • Financial / Accounting

  • Sales / e-commerce

  • Enterprise Resource Planning (ERP)

  • Customer Relationship Management (CRM)

  • Human Resources (HR)

  • Budgeting and Forecasting

  • Admin systems

  • Data warehouses

The problems, and the need for MDM, arise because all of the above systems need some or all of the business master data. As these systems generally aren’t explicitly linked, it means that each system normally maintains its own copy of master data in order to function correctly. All the systems have their own local database, each containing tables that hold the master data entities needed by the individual application. Therefore, separate copies of an organization's master data are scattered around the enterprise.

This creates a challenge as to how the master data can be passed from system to system. For example, if the Sales Order Processing system is the place where customers get created, then how are these customers made available to the CRM system, which needs to log new sales opportunities for existing customers? Furthermore, what if the CRM system needs to create its own set of potential customers? A situation could arise whereby the CRM creates a potential customer, who, after a few months, becomes a real customer. How is this new customer now entered into the sales system when they place an order? A likely scenario is that the new customer will simply be manually entered into the Sales Order Processing system, with different information than what was entered in the CRM system.

Situations like the one above can cause serious problems within an organization. We may now have a customer called 'Contoso' in our Sales Order Processing system, but our CRM system holds the very same customer as "Contoso Systems".

All this makes it very difficult to obtain a single version of the truth for customer data, meaning in this case that the analysis of the combined sale and cost of sale for reporting purposes will be difficult to obtain.

The overriding problem is that master data changes slowly over time. Inserts, updates and deletes should be applied across all systems at the same time, but due to the way that the individual systems were implemented this is not easily achieved, or in some cases it may actually not be possible. The result is that master data existing across disparate systems in the organization can very quickly become out of sync if not managed correctly. Further examples of how this can happen are as follows:

Manual updates

Sometimes master data can be initially fed from the original system of record to another system, but then no effort is made to keep the two systems automatically synchronized. This approach is typically used when the data that needs to be taken from the system of record is not volatile. An example of this is replicating Cost Centers from the Accounting system to the Budgeting and Planning system. The creation of a new Cost Center may be such an infrequent event that it's acceptable for the Planning administrator to manually re-create the cost center in the Budgeting and Planning system. Accountants and Planners tend to know the Cost Center codes well, meaning it's quite easy for them to make the change. The problem arises of course when other more subtle attributes of the Cost Center, such as perhaps the Cost Center category, get updated in the Accounting system; then the updates wouldn't be passed to the Budgeting and Planning system, which could cause problems.

Different descriptions for the same attributes

It's quite possible that different systems may need the same attributes of a given entity, but that there is no standardization of the possible attribute values across the different systems. For example, the sales system for a clothing company will have some sort of Product entity, which could have an attribute of Product Category. The sales system may hold the Product Categories of 'Jeans', 'Chinos', 'Sweatshirts', 'Belts', and so on. However, when the marketing system was implemented, the categories were slightly different, being entered as 'Casual Pants' instead of 'Jeans' and just 'Sweaters' for 'Sweatshirts'. When sales figures and marketing costs by Product Category are needed on the same report, there will be no alignment between the Categories of the products.

Mergers and acquisitions

If an organization has grown by mergers or acquisitions then there's a good chance that the once separate companies that now form a group all sell to the same customers. This means it's possible that one of the companies holds a customer called "Contoso USA" and that another company holds a customer called "Contoso North America", which in fact in the fictitious example happens to be exactly the same customer. They've both historically sold to the customer, but have just internally given the customer a different name and have probably assigned different attributes. The end result is that any group level reporting on customer data will not be accurate due to the duplicates that exist.