Integration for Small Business ERP solutions. SAP - MYOB - Microsoft


Application Programming Interfaces (APIs)

Following on from our introduction to the topic of Integration - What about APIs you cry! How do they fit in? APIs are a fuzzy concept that might encompass all the aspects of data exchange required, or some of them, or none of them at all. Don't assume!!

An API is exactly what it says – an interface, or set of instructions, for interacting with an application via a programming language.

Originally, APIs were built so that third party developers could create integrating functions more easily. For instance, a phone system vendor might write specific functions into their operating system so that a programmer for a voice mail company could easily import, extract, and otherwise work with the phone system data. This would usually be written in the same programming logic as the operating system, and the assumption was that the third-party programmer knew that language. Operating systems like Unix and Windows have long had APIs, allowing third parties to develop hardware drivers and business applications that use OS functions, such as Windows’ file/open dialog boxes.

Image result for programming languages

APIs are written to support one or more programming languages – such as PHP, Java or .Net – and require a programmer skilled in one of these languages. An API is also likely to be geared around specific data format and transfer standards – for instance, it may only accept data in a particular XML format, and only via a SOAP interface. In most cases, you’ll be limited to working with the supported standards for that API.

Choose Your Own Data Exchange Adventure

The type of data exchange that makes sense and how complex it will be varies widely. Indeed, a number of factors come into play: the applications you would like to integrate, the available tools, the location of the data, and the platform (i.e. Windows, Linux, web) you’re using. Integration methods vary widely. For instance:

  1. Stripped all the way down to the basics, manual data exchange is always an option. In this case, an administrator (a manual Kick-off initiating action) might download a file into CSV, save it to the network, perform some manual transformations to put it into the appropriate file format, and upload it into a different system.

    Image result for csv
  2. For two applications on the same network, the process might not be too much more complex. In this case, a Scheduler initiating action might prompt one application to export a set of data as a CSV file and save it to a network drive. A transformation program might then manipulate the file and tell the destination application to upload the new data.

    Image result for http
  3. Many web-based tools offer simple ways to extract data. For instance, to get your blog’s statistics from a popular tracking service you could use a scheduled initiating action to simply request a page via HTTP, which would then provide you the statistics on a XML page. Your program could then parse and transform the data in order to load into your own reporting application or show it on your own website. Many public applications, such as Google Maps, offer similarly easy functionality to allow you to interact with them, leading to the popularity of Mashups- applications that pull data (generally via APIs) from two or more website.
    Image result for xml

In our example If we are using a Project Management System which is separate to our BP system, we may find ourselves with two silos containing Customer data – Customer Data entered through the Project Management System and Customers entered via the BP system. In this circumstance, you might setup a process that kicks off whenever someone in the team submits the Create Customer. This process could write the data for the new Client into an XML file, transfer that file to your server, and from there kick-off a new process that imports the new Client while checking for duplicates. Clearly, we would have defined the process for setting up a customer and considering the billing types, credit facilities etc so the BP, certainly  in our case would always be the “Master” as part of a defined business process.

Finding Data-Exchange-Friendly Software

As is likely clear by now, the methods you can use to exchange data depend enormously on the software packages that you chose. The average inclination when evaluating software is to look for the features that you require. That’s an important step in the process, but it’s only half of the evaluation. It’s also critical to determine how you can – or if you can – access the data. Buying into systems that over-complicate or restrict this access will limit your ability to manage your business.

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 try to interact with your data in unapproved ways (including leaving the vendor).

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:8-steps-to-finding-data-exchange-friendly-software

  1. 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.
  2. Can I take a report or export file; make a simple change to it, and save my changes? The majority of customised 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.
  3. Can I create the complex data views that are useful to me? Most modern applications and 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 customised reports. A Customers Name, address, and billing transactions 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 customers who were billed for a particular date range. 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.
  4. 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. It must be said that very few platforms can provide for this through the sheer size of the database structure.
  5. What data formats can I export data to? As discussed, there are a number of formats that data can be stored in. You want a variety of options for industry standard formats.
  6. Can I connect to the database itself? Legacy on-premise systems access the underlying database directly. The ability to establish at the very least 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.
  7. 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). If you want to integrate data in real time, determine what user actions you can use to kick off a process. Don’t allow a vendor to lock you out of the database administrator functions for a system installed on your own network.
  8. Is there an API? APIs can save a lot of time if you’re building a complex data exchange. For some systems, it 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.

As mentioned elsewhere Cloud Factory concentrate on the vendor supported Tools and we don't look for further solutions to act as the integration engine.  Apart from teh data dictionary - this is a challenge :

Dynamics 365 Business Central - Microsoft Dataverse

SAP Business One - Service Layer

SAP Business One - Integration Framework

MYOB Advanced - Web Services API

We hope you found this follow up article helpful.  If you have any questions on the topic of Integration, please give us a call or click below to contacts us.


Free Initial Assessment

Book a free initial assessment with us where we understand your business problem and suggest a solution which suits you best and ensures growth for your business.