My article “XML, API, CSV, SOAP! Understanding the Alphabet Soup of Data Exchange” is up at idealware.org. This is intended as a primer for those of you trying to make sense of all of this talk about Application Programming Interfaces (APIs) and data integration. It discusses, with examples, the practical application of some of the acronyms, and suggests some recommended practices around data system selection and deployment. Credit has to go to Laura Quinn, webmaster at Idealware, who really co-wrote the article with me, but didn’t take much credit, and our reviewers, Paul Hagan, Steve Anderson and Stephen Backman, who added great insights to a pretty heady topic.
The article went through a lot of rewrites, and we had to cut out a fair amount in order to turn it into something cohesive, so I hope to blog a bit on some of the worthwhile omissions soon, but my day job at Earthjustice has been keeping me pretty busy.
Repeat this mantra: I will not pay a vendor to lock me out of my own data. Sadly, this is what a lot of data management systems do, either by maintaining poor reporting and exporting interfaces or by including license clauses that void the contract if you interact with your data in unapproved ways.
The software you choose has an enormous impact on whether you can effectively get data in or pull it out to integrate with other packages. If you only look at the front end features, you’re only conducting half an evaluation. It’s also critical to determine how you can — or if you can — access the data.
To avoid lock-in and ensure the greatest amount of flexibility when looking to buy any new application — particularly the ones that store your data off-site and give you web-based access to it — ask the following questions:
Can I do mass imports and updates on my data? If the vendor doesn’t allow you to add or update the system in bulk with data from other systems, or their warrantee prohibits mass updates, then you will have difficulty smoothly integrating data into this system.
Can I take a report or export file; make a simple change to it, and save my changes? The majority of customized formats are small variations on the standard formats that come with a system. But it’s shocking how many web-based platforms don’t allow you to save your modifications.
Can I create the complex data views that are useful to me? Most modern donor, client/case management and other databases are relational. They store data in separate tables. That’s good – it allows these systems to be powerful and dynamic. But it complicates the process of extracting data and creating customized reports. A donor’s name, address, and amount that they have donated might be stored in three different, but related tables. If that’s the case, and your reporting or export interface doesn’t allow you to report on multiple tables in one report, then you won’t be able to do a report that extracts names and addresses of all donors who contributed a certain amount or more. You don’t want to come up with a need for information and find that, although you’ve input all the data, you can’t get it out of the system in a useful fashion.
Does the vendor provide a data dictionary? A data dictionary is a chart identifying exactly how the database is laid out. If you don’t have this, and you don’t have ways of mapping the database, you will again be very limited in reporting on and extracting data from the application.
What data formats can I export data to? As discussed, there are a number of formats that data can be stored in, such as CSV (Comma Separated Values – a great format for manual imports and exports) and XML (eXtensible Markup Language – better for automatic integration). You want a variety of options for industry standard formats.
Can I connect to the database itself? Particularly if the application is installed on your own local network, you might be access the database directly. The ability to establish an ODBC connection to the data, for instance, can provide a comparatively easy way to extract or update data. Consider, however, what will happen to your interface if the vendor upgrades the database structure.
Can I initiate data exports without human intervention? Check to see if there are ways to schedule exports, using built-in scheduling features or by saving queries that can be run by the Windows Scheduler (or something similar). Don’t allow a vendor to lock you out of the database administrator functions for a system installed on your own network. If you want to integrate data in real time, determine what user actions can kick off a process (for instance, clicking Submit on a web form).
Is there an API? APIs can provide a very useful set of functions for importing, exporting, and moving data programmatically. For some systems, an API may be the only way to get data in or out without human intervention. Don’t assume any API is a good API, however – make sure it has the functions that will be useful to you.
Is there a data exchange ecosystem? Are there consultants who have experience working with the software? Does the software support third party packages that specialize in extracting data from one system, transforming it, and loading it into another? Is there an active community developing add-ons and extensions to the application that might serve some of your needs?
Evaluating software packages for data exchange capabilities can’t be an afterthought. It’s too important. Buying into systems that over-complicate or restrict your access to data will limit your ability to manage your business, both today and as long as you own the package.