Using technology to improve the way you do business

Integration woes

By Michael Burns

Integration still is the No. 1 problem facing many organizations that require more than one internal system. They could have separate billing and CRM systems; financial systems not connected to their manufacturing systems; or their warehouse disconnected from order processing — the list goes on. Some organizations pay big bucks to get the systems integrated and to keep them that way should changes occur in one of the systems. Many others go the manual route, rekeying the information for their own purposes — which, of course, is error prone and inefficient.

There are a number of reasons why integration can be problematic.

1. It might be impossible to access the data because of proprietary databases.
2. You may not know how the data is defined. You will need something like a data dictionary, which includes field names, field descriptions and data type (character, numeric, etc.).
3. The two systems could have different data structures, requiring a mapping process to convert data.
4. It’s not advisable to update a system from an external file without going through validation and integrity checks; otherwise the system might get corrupted. For example, you could import an inventory code that was not set up in the inventory file.
5. Integration can be more complicated than just importing a transaction file such as an invoice. It could also involve synchronizing master files between two systems.

Fortunately, there have been improvements in the integration tools available. First, it’s a lot easier to get at data because most systems use what are called “open” databases that allow for easier access, such as Microsoft SQL Server or Oracle. Also, some databases are open database connectivity (ODBC) compliant. ODBC provides a standard database access method to retrieve the data.

The second and third problems (data definition and mapping) have long been a thorn in the side of technology. Web services and extensible markup language (XML) are now considered the way to solve them. We will look at Web services and XML in next month’s article.

The fourth problem involves validation and integrity of data from external sources. Today, many systems provide an application programming interface (API), which effectively allows you to apply the same validation and integrity checks as if the data was keyed into the system.

The fifth problem — synchronization — has traditionally been solved with programming, which is expensive and can be risky. Programmers have many tools at their disposal to do this work, but these are best left in their hands. They might also use Web services and XML .

Now, imagine you want to go beyond your own company and integrate your systems with those of your trading partners. For example, you might want to automatically convert their purchase orders into customer orders without rekeying. It’s difficult enough to build integration programs for two systems, much less several.

Electronic data interchange (EDI) was developed in the late 1960s to overcome the hurdles of integration between trading partners.

 Although it has become the primary way for suppliers to communicate with larger retail operations, EDI is perceived as difficult and expensive to implement. Web services and XML are now held up as the solution for any data exchange between trading partners.

Clearly, integration can be difficult even with the latest technology. So beware of anyone who tells you integration is easy — whether they are touting an open database, ODBC, API or any other technology.

Michael Burns, MBA, CA, is president of 180 Systems (www.180systems.com/), which provides independent consulting services, including business process review, system selection and IT audit. Contact: 416-485-2200 or mburns@180systems.com.