 |
| Picture is AI generated by Bing Image Creator from the prompt “two computer systems exchanging data records in style of Vermeer” (Note #2) |
This is Part 9 of the Tectonic Speed series... (https://tectonicspeed.com)
Part 7 of this series focused on documentation (https://blog.tectonicspeed.com/2022/05/tectonic-speed-of-government-part-7.html), so I’m going for the trifecta of boring business analyst topics in this post about Interfaces and Conversions. But these less glamorous tasks can be essential for successful software implementations.
This post is written for people who don’t usually get in the weeds on these areas. I’m sharing lessons learned to help decision-makers for these projects understand what’s involved with an Interface or Conversion – and why they’re important.
After defining key words, I’ll cover some best practices on each.
******************************************************************************
All of the ideas expressed in this article are my personal statements and opinions, and do not reflect the opinions/statements of the City of Urbana.
******************************************************************************
Definitions (Note #3)
With Interfaces and Conversions, there are two systems. I’ll refer to them as a Source system where the data initiates and a Target system that receives the information.
An Interface takes data from the Source system and records it in the Target system, in detail or summary, on a recurring basis so that the Source system’s data is continually updating the Target. Examples:
- A daily summary from a cash register sent to the finance system.
- Detailed eCitation records transmitted from municipal police department to the County’s court system.
A Conversion is a one-time event, pulling the existing history from the Source into the Target. Usually, the Target system is the replacement for the Source system, so the data is brought forward for historical referencing. Examples:
- Building permits brought into a new system for historical views by parcel.
- Active employees and their histories are loaded into a new payroll system.
In both cases, it’s all about the data. Interfaces make sure that current systems stay synchronized, while Conversions carry forward information that you need or want to maintain after you move from the old system to a new one.
Best Practices for Building and Testing
Let’s set some expectations:
- Interfaces are easier to design and build, but harder to test.
- Conversions are harder to design and build, but easier to test.
Building Interfaces
Interfaces are easier to design and build because they take data from one living system (living here means “currently in use”) and send it to another living system. Because the systems are living, help is usually available for them. Also, if they’re currently in use - in the year 2023 when I’m writing this - then they probably have standard layouts for sending and receiving data. (Note #4)
With Interfaces, the key task is mapping fields from one system to another – and deciding what other information should be brought forward and where it should go. I’ve squirrelled a deep dive into this process in the very long Note #5.
Testing Interfaces
Interfaces are harder to test because of the spectrum of unexpected events that happen in real life. Thinking of normal entries to use as unit tests is a good starting point, but real life is always wackier.
The tricky thing about Interface issues is that there’s often no error; there are simply skipped entries that may not be noticed for some time… if at all. You need to know exactly what you’re processing from the Source system and then compare the results in the Target system, trying to match entries down to the last detail.
Pumping old data through the Interface into a testing environment and comparing the results is a good way to verify the logic - if that’s possible. Even with all of this testing, there will still be missed cases, so monitoring and comparing results for a few months after going live is important. (Note #6)
Building Conversions
Conversions take data from a system you are leaving or have already left. That’s the origin of the difficulties: if the system is defunct, or if you’re leaving the vendor, they’re not going to go the extra mile to help you pull information out. In my experience, some of the systems we had to pull data from lacked any kind of extract process - many of them predated such a concept. (They were designed like a roach motel: you can get in, but you can’t get out.) One vendor was going to make us go through an expensive upgrade of their software just to get to a version where we could pull the data out! We didn’t – instead we did the following…
When you don’t have good extracts, you have to open up the database and understand it from the inside out by looking at the tables that store the data and comparing it to data as presented in the online screens, which usually consolidate information from several tables. In the database, the different bits of data are probably linked by one or more record identifiers. The only saving grace here is that programmers tend to be logical, so once you get a feel for the method of their system it generally stays consistent.
Testing Conversions
Conversions can mangle the data that’s brought forward or leave some records unconverted. Unlike Interfaces, which can usually be fixed through correcting entries, it might be difficult to do this for converted records – because once the data is converted more activity may be built on top of it.
As hard as they are to build, Conversions are that much easier to test because you are working from a fixed set of data. You can run and re-run your Conversion as many times as needed to test it, and you can verify it down to the individual record if you want to. (And that’s often necessary because you might be bringing it forward at a detailed level.) Practice as many times as needed because you only get one shot to run it for real!
Who to Include in Interface and Conversion Creation
There are two kinds of people you want to include when writing Interfaces and Conversions, and getting both is ideal. The first is the expert user, who has worked there for years and uses the system frequently. This is the person other people ask for help when they have problems. They will know the business events of the system, its entry fields, and the quirks for various entries.
The second is a person who creates data reports – they will understand how the data is stored. This person might be in IT or some other group and may not be a hands-on user of the software!
You also need people who understand each of the two systems – the Target and the Source. Often, there are different people who are experts for each, especially for Interfaces which can cross functional units. Having multiple people take part is even better: a group discussion can trigger good conversations about sub-scenarios that need to be addressed.
Alternative Solutions for Interfaces and Conversions
Conversion Alternative: Data Warehousing
An entirely different approach to Conversions is to extract the data into a data warehouse, which is a reporting database that keeps the data in something close to its original state. (Note #7) There are limitations of that approach, including the need to understand the old system on an ongoing basis and the fact that the data you might need isn’t in the new system, and you have to stop and look for it in another place.
Sometimes you need an “all of the above” approach to Conversion. We recently transitioned from an older Police Records system to a new one. The Conversion focused on active records like items currently stored in the Evidence Room, but the problem was that the Conversion assumed that all Evidence had been fully processed. Some evidence records were still in a semi-processed state due to the way things work in the real world. (The evidence was part of an ongoing investigation and wasn’t stored in the Evidence Room because it was sent out for lab analysis.) After the Conversion occurred, we had to run some extracts to pull details on the un-converted Evidence Records, which were dropped into spreadsheets so that they can be added by hand to the new system as those pieces of Evidence returned from the lab.
Interface Alternative: Manual Entry
A manual Interface is when a person reviews the results from the Source system and enters the transaction in the Target system themselves by hand, attaching documentation like the report or spreadsheet used for analysis. It’s recommended that this be a summary entry; if it’s detailed and there are more than a few entries per day then please automate it.
The manual Interface can be valuable as a temporary solution when new systems are put in place because Interfaces are often one of the last things project teams consider. A more legitimate reason: it’s worthwhile to go through some iterations manually before you build an automated one. Having an expert in the Source system do the analysis and data entry in the Target system for a few weeks will help you shake out the issues and scenarios you need for the final Interface.
Final Thoughts
Making two systems “talk” to each other is an idea that sounds reasonable, and yet can be unfathomably complex. (I wrote about this in an article for GFOA's magazine: https://blog.tectonicspeed.com/2019/08/a-data-river-runs-through-it.html)
If you’re heading into a system replacement or implementation of a new system, then my recommendations are to make Interfaces and Conversions part of the planning from the specifications stage. List the touchpoints with other systems in the vendor contract and make sure that they’re part of the ongoing implementation project. Too often these are left as an afterthought and wind up delaying a project or causing it to go live without critical data.
Take the time and effort to do Interfaces and Conversions correctly and you’ll have a more successful implementation when judged from the perspective of six or twelve months after go-live.
Notes
Note #1: Now I can say that “I wrote the Book on Interfaces and Conversions!” : )
Note #2: From now on, I’m primarily using AI to generate images for my presentations and blogs. In the past, I’d find Internet images, but I’m excited about the use of AI to get away from appropriating images (I always give attribution, though) and from being limited by usage rights. I wrote about my first use of AI images here: https://blog.tectonicspeed.com/2023/11/ai-images-im-not-going-back.html
Note #3: There might be a more formal version of what these terms mean, but I don’t care – my definitions come from 30 years of experience.
At my first job implementing government finance systems, we did a lot of Interface work. Finance systems are core software for any entity, and lots of other systems that generate monetary entries need to send transactions to the finance system.
Most of my Conversion experience comes from my current role at the City, where we’ve replaced core systems for every Department - and several for some Departments. Due to my ongoing position within the City (I’d be there long after go-live, unlike a consultant) I was focused on our departments’ long-term ability to get access to our data.
Note #4: More glamorously, you’ll hear salespeople refer to these as “Application Programming Interfaces” (APIs) but really, they’re just standardized layouts of data.
Note #5: Five steps to write an Interface:
1) Understand the data you’re pulling out of the Source system. What is the business event that’s occurring and what fields are essential information? Separately, find out how the data will be recorded in the Target system and what data elements are needed to properly record it.
2) Get data extracts from the Source system that contain examples of real data for the business events to be transmitted and review them against the information you see on the screen, so that you know what the data fields represent. Sometimes data field names can be cryptic.
3) Now get the layout of the event on the Target system. It might be a layout for a specific entry (e.g., a customer billing file) or it might be a generic one (e.g., a cash receipt or a journal voucher).
4) Crosswalk the data from the Source system to the Target system. This is the step where effort pays off. It can be very useful to stash information from the Source system in unused fields in the Target system for problem solving later. For example, the Source system’s transaction identifiers or the time/date stamp of the original entry – both of which can just be dropped into text boxes on the Target system. Also be aware of conditionally required fields in the Target system that are necessary sometimes, but not others.
5) Map the fields using a tool that generates the output file and then validate your output in a test environment. A good way to verify the imported record is to simulate the Interface entry in a manual transaction and compare the updates. Repeat as needed.
Note #6: By “comparing results” I mean running reports in both systems and verifying that the expected numbers appear. This can be more challenging than you think if there’s a lag between the entry in the Source system and the Target system – so consider the selection dates (and times!) for your comparison reports carefully.
Note #7: I say “close to its original state” because a good data warehouse should denormalize the data to simplify reporting in the future. What is denormalization? This is a decent video to explain it: https://www.youtube.com/watch?v=T3cx09FINuU, but it misses the main benefit I’m concerned with: people who come later will not need to know or understand how the old database worked in order to query it.