Master Data Management with SQL Server: A Primer – Part 1

Organizations, especially those that have multiple departments, and those with heterogeneous software systems in place often have a problem. This problem is synonymous, as in Pinal Dave’s excellent post, to what a married couple might have: A trivial one of that having an overlapping list of phone contacts, each list listing the same names differently. So when a need arises, and I need to send an urgent message to Supun from the wife’s phone, since the battery on mine is dead, I would find the 10 different Supun’s on her phone named with the organization name as a prefix, whereas in mine I have the contacts listed on a first name, last name basis, with the organization name going into its own field. Hence, you could imagine the frustration of looking for the correct number, especially when the need is urgent. Magnify this issue for your organization’s base of customers. And maybe for entities such as employees, products and vendors. Imagine.

The type of data that I’ve mentioned above is called master data. Master Data are nouns that are critical or important to the organization, and need to be well managed, but yet are not often done so. Not doing so can lead to data issues, which in turn become the organization’s business issues; which as the count of systems in place increase, and as the organization grows with mergers and more departments, could get out of hand.

Master data as explained in this Microsoft article, can fall into four general categories:

People Things Places Concepts
Customer Product Office location Contract
Employee Store Country Warranty
Sales person Asset Region License

The article explains an example of how a credit card company can lose a customer, just by not having an updated customer record throughout each department of  the organization.

I’ve known about MDM for some time, but have not used it in any of my projects, and thought of experimenting with it after I noticed discrepancies in employee information. When employee information is required, say to analyze the results of how employees have performed in a recent training, where would one go to get this information? Good question. This of course depends on each organization and how and where their data is stored, and who manages it. Well, I first thought of getting it off the CRM system, however, accessibility to all of the required data was an issue. Next, I tried consulting HR, but their data was on Excel files; keeping them up to date was a problem; and I needed up to date information at a lesser interval than the interval that these files were updated. I heard that Active Directory had up to date information, but here the names of some employees were spelt wrong. So we can see the problem here. So I had to settle for partially static data obtained from various sources and mashed up to fit my needs. But it won’t solve my problem in the long run, plus there are sure to be more scenarios where up to date and correct employee information is required. There is a need that employee information is central, up to date and correct. Similarly, there surely is and will be a need for other entities in the organization as well. Enter: Master Data Management (MDM).

Master Data Management is the management of master data, which should  be driven by business policies (such as who owns the data, where it is stored etc.) and processes (how data is stored and updated), while using technology to facilitate it. Most put technology in the fore front of managing master data, but it is only the facilitator, while it is the business that is the main factor. Hence, it is important when doing MDM, that a data steward is involved. This person should be someone influential within the organization, ideally someone who knows the ins and outs of the master data and the systems that are in place, be technical to some extent, and be the champion of the cause. This is the business aspect of it.

Looking at tools; SQL Server introduced Master Data Services (MDS) with SQL Server 2008 R2, an offering for MDM. It was the entry for Microsoft into the MDM market. Mind you, an MDM solution could be built from scratch using database technology such as SQL Server, yet Microsoft chose to incorporate it as a feature, probably because it foresaw how important MDM could be when it came to enterprise business intelligence. And also since organizations are looking for ways and means to consolidate disparate master data into an up to date,  centralized and managed environment.

My next post on MDM will take a look at installing and the main features of MDS.

One thought on “Master Data Management with SQL Server: A Primer – Part 1

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s