Key mapping, the linking of unique identifiers from multiple systems, is a widely used technique to address data integration challenges brought about by having common information scattered across multiple applications. The typical enterprise uses key mapping today for data integration because databases employ local unique identifiers as the most common navigation path to a piece of data and because most enterprises have the unfortunate need to store identical, similar or related facts in many places. Some optimists claim that this scattering of data will subside as monolithic enterprise applications deliver required business functionality. However, most realists understand that the scattering may subside but will never disappear. Others fantasize that the panacea of master data management (MDM) will bring us to the more enlightened state of one fact in one place. Great stuff, but during this slow and painful journey to paradise, we mere mortals must continue to rely on key mapping to rationalize native identifiers of common legacy data.
The key mapping opportunity exists for many data subjects, including customer, product, person, place, machine, state and even simple domains such as activity status and unit of measure. Regardless of subject, it turns out to be the very same logical problem. We need to know that this unique identifier over here corresponds to that unique identifier over there to meet the everyday requirement of integrating this common data appearing in multiple applications.
We all understand the underlying business need to consolidate, integrate or synchronize common data from multiple applications. After all, this is what IT does. Overall, integration could be the most common and expensive requirement in all of IT. I am sure that examples of data integration opportunities abound in every organization and need not be listed here. It probably would not be too risky to assert that 80 percent of all IT pain comes from this business requirement alone. At the same time, data integration may be what provides 80 percent of the business value, and it may also be the source of 80 percent of business dissatisfaction with IT. Sure, it’s painful, but we gotta have it.
However, integration is not really the problem addressed here. Sure, it is an opportunity, but one that has always existed, and IT continues to provide data consolidation, integration and synchronization as best it can. Key mapping design is not necessarily the problem, either. It is pretty straightforward - certainly not rocket science - although there is often some complexity arising from semantic dissonance and from schema reconciliation required for subtly different implementations of a common concept, such as physical address. There are many ways to solve key mapping, and most solution architects have found a way to address it one way or another.
The much larger business problem is the more fundamental issue that IT is wasting important resources (time, money, talent) solving this same challenge repeatedly in different ways and with limited scope. Not surprisingly, this results in duplicated effort, unnecessary complexity, inaccessible translations, redundant and conflicting mappings and maintenance spread across multiple areas of expertise. The sucking sound of important resources going down the drain should be deafening, but no one hears it because the key mapping design effort is repeated quietly within every project that has integration requirements (ah, wouldn’t that be every project ever?). IT is applying small amounts of time, money and talent on key mapping design so frequently that it adds up to large amounts of time, money and talent. Now that’s a problem.
Previous Options
To address this ubiquitous key mapping opportunity, the typical enterprise has the full gamut of solutions, developed independently over the years, each with its scope limited to a particular type of data. Each implementation may be dedicated to a particular data flow, application, database, subject area, development platform or integration technology. The scope is always limited. Consequently, multiple independent solutions exist for essentially the same problem, sometimes right next to each other at the same data integration point.
Perhaps the key mapping is hard coded in a data interface using if-then-else or extract, transform and load (ETL) transformation logic (IF source.status_key = “A”, THEN target.status_ID = “2”). Or maybe a column has been added to a local database table to store another system’s key (SAP_VENDOR_ID). Another approach is to dedicate a local table to a particular mapping (VENDOR_XREF) or a generic table for all local mappings (XREF table with a XREF_TYPE column). Perhaps mappings are managed within integration middleware. Each of these solutions bites off a larger piece handling a broader scope, but there is the potential for overlap of solutions and key mapping discrepancies when many solutions coexist.
Be the first to comment on this post using the section below.