Data integration is a fact of life for those of us who are in the information technology business. Since we are yet to invent a single system that does everything for everyone in an enterprise, it is inevitable that we have to support and deal with multiple systems. It is equally true that unless the data from these diverse systems are integrated, we will not be able to understand the data in a coherent fashion. With the proliferation of “best of breed” solutions we have a complicated mess in hand.

Most institutions have a large administrative system like Banner or PeopleSoft that is considered to hold the authoritative data. In addition, for the purpose of reporting, we all have a data warehouse or a data mart into which data from the central systems is inserted typically on a nightly basis. The general premise of this is that the administrative systems were originally designed to take in the transactional data and therefore optimized for that purpose. They were not designed for complex reporting. Combining both of these in one system means a drain in resources and everyone suffers. Modern systems like Workday claims to have designed their system in ways that you can do all activities in one system. When you have multiple systems, generally, the data is integrated with the administrative systems, though in some cases, directly into the data warehouse.

It is also the case, predominantly the integration is two-way, in that information from the central administrative system is sent to the external system and data from that system is brought into the administrative system. It is rare that we have one way integration where the data flows in one direction, if so, it is typically from the external system into the administrative system.

There are many many complications associated with data integration. I will mention a few here. One of the major headache is making sure that the same data does not get “maintained” in two places. For eg. a user may be allowed to change her address in the external system as well as the internal system (or the office has the ability to change it). If so, we need to worry about how to handle that case. One typically looks at the date of recent change to make the decision. As trivial as it may sound, dates in the two systems may not be consistent sometimes. Timezone differences and lack of implementation of daylight savings cause a big headache and one needs to force standardization of time in the software that is doing the integration. The other issue in the case of addresses is standardization – do both the systems use the same standards for collecting addresses? Do they account for variations in addresses across the globe? If they don’t, how does one deal with it?

We are currently working on several integrations and in one case, the external system is not forcing a date field as a date field, instead treating it as plain text. Go figure! And the cost of figuring out how to deal with this can be enormous and after all of that you may not even get it right.

And how about special characters and multilingual support? What if one of the systems supports more information than the administrative system can accommodate? For example, number of addresses that one can have and their purposes (“I want you to send printed materials to this address during September-May and to my summer home at this address from June-Aug”). Typically, the integration woes grow and we tend to accommodate these in our datawarehouses because we can.

Lack of standards is the root cause of this issue and having such data standards is a pipe dream.

Typically, the administrative offices fall in love with a product because it seems to do the job! In the process, the rest of the matters such as integration with other systems become secondary. The vendors also throw promises like “We have great APIs (Application Program Interface) and we provide WebServices” which impresses everyone (but the techies) who think that these will solve the integration problems. Sure!

WebServices is a great method that has vastly simplified the task of integration, but a method does not solve fundamental and structural issues like the ones I mentioned. And the examples I provided are rather simple and there are much harder issues we need to contend with. In my long career in the field, I am yet to see product that has kept the promises of integration!

In fact, we use the WebServices and published APIs for a product and one such integration failed. After four weeks of working with the vendor, they simply turned around and said “You are doing something custom that we don’t support”. Mind you, we are using published APIs.

So, though everyone throws around fancy words such as WebServices and APIs, don’t fall in love with a product and get married to it too fast. Date (Probe) it first and you might get a more balanced view of things.