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.

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.

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.

Analytical MDM is the focus of delivering clean, conformed and consistent master data to data warehouses, cubes, and other business intelligence applications. The process is to extract data from the various operational source systems, and integrate that data into the MDM master entities that have been defined. As with any robust form of data integration, rules are applied to only allow valid data to enter the MDM environment.

Once in the MDM environment, users may interact with the master data via a suitable front-end. One of the key analytical maintenance tasks that users may be required to do is to supply additional entity attribute values for reporting purposes. It's quite likely that if, for example, Customer data has been sourced from the ERP system, then the master data entity that gets populated will contain Customer attributes such as names, address and telephone numbers. But what about a customer attribute such as 'Industry Classification'? In our example, senior management wish to see a report of customer sales that must include Customer Industry Classification, but as it happens there is no such customer attribute in the ERP.

Forgetting MDM for the moment, and depending on how mature the organization's Business Intelligence actually is, Excel may currently be involved to solve the above problem. It's not unheard of for someone to take a data feed from somewhere, and then manually adjust in Excel before delivering some sort of manual excel dashboard to the executive in question.

Another solution to this problem could be to create a new attribute in the ERP. This may be a good choice, but it could take time to implement and could be technically difficult, depending on which ERP product the organization has implemented.

MDM tools can offer a quicker and more robust solution, as they're highly specialized to deal with this kind of situation. As each new customer is detected by the MDM tool, a user can be alerted, and then prompted to fill in the Industry Classification for the new Customer. As you would expect, it's also possible to alter the new attribute value for existing Customers as well.

Due to the huge data volumes involved in some data warehouses and BI tools, the delivery of master data to these systems tends to happen in batch, for example, over-night.

Operational MDM is a little more complex than Analytical MDM. With Analytical MDM, the data travels one way, from the source, via MDM in the middle, into the data warehouse. Operational MDM is altogether different, as the members of the master data entities that are controlled and improved by the MDM process actually become the members that get used directly by the source systems.

By addressing the overall data quality in the operational systems via a central program, the issues outlined earlier in this chapter can be addressed. For example, a situation could occur whereby users of a marketing system need to send out a promotion to a group of existing customers. They take a feed of customer contacts from the Sales system, which to them seems sensible enough. However, the salespeople who frequently call a range of existing customer contacts have discovered that a few contacts have recently left. This means that the marketing users will be sending promotions to out-of-date contacts and is a waste.

With operational MDM in place, updates to a central repository of customers would ensure that the Marketing users in the example above can send their promotional advertising to a list of customers that will be more likely to respond.

Often, in order to prevent the kind of problems that can occur at the operational level, data needs to be made available to the operational systems in near real time. This is in stark contrast to the kind of latency that is often acceptable in a data warehouse. There are several architectural design patterns that can help address this situation, as we will see in the next section of this chapter.

A final point is that there is some debate on Operational versus Analytical MDM in the industry. This centers around the point that if an entity is to be managed by MDM, then all systems, whether they are analytical or operational, should be using the very same master data.

One thing is certain: the tasks involved getting the operational and analytical systems to participate in the MDM program are different. Operational MDM may need to operate in real time, in order to detect source system changes, deal with them, and then synchronize them back to get users using the correct data as soon as possible. The normally over-night batch loading of a data warehouse is easier to undertake, as no-one will be using the system, and the integration will likely happen into a set of de-normalized tables, which are easier to understand for a developer when compared to the entire normalized structure of an off-the-shelf ERP database, for example.

The Transaction Hub approach is the simplest of the three approaches to explain, but it's probably fair to say that it's probably the hardest to be able to implement.

The idea behind the Transaction Hub approach is to have a single repository of master data that all applications can connect to directly in order to retrieve the master data that's needed. A diagram of this approach is shown below:

Allowing each application to directly retrieve from a single database involves altering all applications to make their database connections to the central database. For example, the CRM application may be set up to take customer data (such as Full Name, Street Name, State, and other attributes) from a SQL table called 'Cust' in a database called 'CRM'. Implementing a Transaction Hub approach to MDM would mean that the application would have to take customer data in a potentially different structure (perhaps First Name and Surname instead of Full Name) from a table called 'Customer' in a Transaction Hub MDM repository database called 'MDM', for example.

Prior to the existence of the MDM program, the business applications would have been able to insert, update, and delete records as much as they wanted. This is still allowed with the Transaction Hub approach, with the original application user interface facilitating the change, but the data part of the change being sent to the central database. A locking strategy is often employed to ensure that users cannot update the same record at the same time.

The advantages and disadvantages of the Transaction Hub approach are immediately obvious. By forcing all systems to retrieve data from a single database, the problem of having duplicate entries across different databases is eradicated.

The problem with the Transaction Hub approach is that it is probably not going to be possible for most organizations to be able to alter the data tier level of their operational applications. Altering the database connection string is one thing, but having to alter the whole data tier of an off-the-shelf application is very difficult to achieve.

For these reasons, the Transaction Hub approach is seen more as an idealistic approach to MDM, whereas other methods are more realistic to achieve.

The Registry approach involves the creation of a single database that maintains a mapping system in order to return a single, unique view of a given master data entity. This can be used for reporting, or for any operational reasons that need to obtain a view of a master data entity, such as a mail shot to all customers. The solution typically includes some sort of front-end application that connects to the registry application in order to maintain the mapping.

For each entity that is managed by the Registry, the database holds the following information:

No additional attributes of the entities (for example, Name, Address in the case of a Customer) are held in the Registry system. Instead, these attributes are actually retrieved from the source systems directly, based on looking up the source system key value that is now held in the Registry. An example of how this would work for managing Customer master data derived from two Customer source systems is shown below:

When a given master data entity must be queried or extracted by a downstream system, a distributed query must be executed, which is initiated from the MDM database, as shown. This query must join each system together by using the unique member key that is maintained in the registry mapping.

Given that the non-key attributes of the entities reside in the original different line of business systems, the registry approach typically delivers a read-only master data solution. As it only maintains a mapping, there is no interface or mechanism for allowing a user to update a given attribute (such as a Product Category, for example), before distributing that update back to the source systems.

The fact that updates are not allowed simplifies the implementation of a Registry system, although the approach is not without complication. If we have two legitimate sources that update Customer data (such as the Sales Order Processing System and the CRM), then there is a possibility that we will encounter duplicates. A set of business rules should be set up to deal with this eventuality, by picking a single surviving record when a duplicate has been found. The survivorship process of removing the duplicates happens in the distributed query, which returns the single merged record, but there is no process of pushing the corrected changes back to the source systems.

The advantage to the registry approach is that it's possible to extract a real time view of a given master data entity without interfering with the various source systems. It's also relatively easy to set up, given that there are a variety of relational databases that are capable of issuing distributed queries.

The main problem with the registry approach is that the large, cross-database queries that are required in order to combine the data are often slow to execute, especially if the data sets are large.

In addition, any time a new system is created or purchased, the distributed queries will have to re-written to incorporate the new source.

The Hybrid is a blend of the other two architectural styles, including a separate database as per the Registry style, but also including all, or most, of an entity's attributes as seen with the Transaction Hub approach.

The aforementioned database is the core of the Hybrid solution, and contains the following data per entity that is to be managed:

The key concept behind the Hybrid model is that the line of business applications continue to connect directly to their original data stores, as they did before the MDM program existed. In some cases, the original applications update the local data stores directly; carrying out inserts updates and deletes for master data entities.

The role of the Hybrid MDM database is to connect to the individual application databases and to detect changes in the sources for the master data entities that are being managed. The changes that get detected are physically propagated to the MDM database, which maintains a separate, clean and conformed copy of the data.

Once data has been picked up from the application database, a number of operations may be applied to the data:

At a high level, the above will ensure that the new MDM database now contains a clean set of members that meet the data quality and structure rules as defined by the business.

Now comes the task of distributing the data out to any system that needs it, which from an operational system perspective can be somewhat of a challenge. We know that with the Hybrid approach, all applications continue to connect to their own data sources. The complication arises if the source applications are continuing to insert and update data. It may be possible to prevent, or lock, users out of updating entities directly in the source applications, but this will not always be the case. Therefore, when data gets sent back to the source applications, updates to the source application master data must be made, with the understanding that update conflicts could occur.

An overview of the Hybrid approach is shown below:

New applications that get implemented can either source master data from the MDM database, usually via web services, or simply fit into the same synchronization approach that is indicated above. The choice of whether to connect a new application directly will largely depend on whether it is developed in-house or purchased.

The Analytical MDM scenario that was presented earlier in the chapter of adding an extra Customer attribute, namely Industry Classification is dealt with elegantly by the Hybrid approach. Attributes that are needed by the source systems are sent back to the source systems, whereas in this case the Industry Classification would only be sent to the data warehouse.

Incidentally, the Transaction Hub approach also deals with the "extra attribute problem", but is likely to be less appealing to most organizations when compared to the Hybrid approach. The Hybrid approach has its challenges, but they are around pushing data into source applications, rather than re-architecting those applications.