If you are planning to purchase new software, you naturally first look at how well the software achieves its purpose. An accounting package must be easy and reliable, an ERP package must support all your business processes, and analytics software must be privacy-friendly and insightful. Many organisations overlook one other, very important aspect: how well can the data be extracted from the application?
Many modern applications nowadays offer built-in tooling to create a nice, clear dashboard. We at Datalab applaud this enormously. But often it is also insufficient: insight frequently only comes after data sources are combined. For example, by comparing stock levels from your ERP package with your accounting, you can sketch a good estimate of your company's financial situation. Without the accounting, you only have stock levels, and without the ERP package, you only have the financial situation (as it was in the past - accounting packages virtually exclusively look backwards).
In other words: combining data is essential. For this, two things are important:
- Being able to access the data - from all relevant source systems
- Standardising that data into a good data model
A specific blog about the second point - standardising data - will follow later.
Extracting data from a source: the possibilities
Broadly speaking, there are two ways to access data from an application:
- Via an API. This is an application programmable interface, a way to access data - often according to somewhat awkward standards. APIs can be used to extract data but also to add data.
- Via direct access to the database behind the application. This is a nice but error-prone way of extraction. The software is constantly being developed and a developer will not weigh your interests in how they design the database. They will change the database structure as functionality or versions of the underlying software require it.
If you are buying a new application and want to extract data via the API, check a few things carefully. Of course whether an API is present (that is almost always the case), but also the following:
- Is an API available? And are there additional costs attached?
- How is security (authentication) arranged? Is there proper security at all (we have encountered APIs that spat out customer data unsecured…)? Can you create access tokens yourself, or do you need to contact the developers? Are modern standards (e.g. OAuth 2.0) used?
- In what format does the API output data? Usually this is JSON, sometimes XML. Our preference is for the former.
- Is the data complete?
- Are all records provided with a primary key (= a unique key that identifies a record)? Unfortunately, we encounter APIs far too often that have no unique key or do not return one. The consequence is that data is very difficult - or impossible - to link.
- Are records provided with foreign keys (= references to other records, for example a product record in a webshop that refers to the product category)? Another sore point that is often poorly implemented in practice.
If the above matters are not properly arranged, extracting data becomes difficult or far less valuable.
Keeping data up to date - often forgotten
Data extraction is not a one-off thing. After all, we want to keep data in the source system up to date with data in an analytics data warehouse. A poorly constructed API often only offers the ability to retrieve all data (or even more annoyingly: all records individually). This means you retrieve all data each time, which is exceptionally costly. With large systems, your data is therefore never fully up to date, simply because it takes too long for all data to arrive.
It is therefore handy if the API supports incremental loads. This is often implemented in one of two ways. The first, less elegant method is loading data using a filter. You filter, for example, on 'all records modified after date X', where X is the date of the newest record in your data warehouse. This seems handy but is not entirely: records that have been deleted often do not come back, so a time-consuming full load must still be performed.
Better is when an API supports sync or batch logic. In this case, an API also returns what has been deleted after a certain date. That makes it considerably easier to keep your analytical data properly maintained.
An example of a very solidly designed API is that of accounting package Exact Online.
Our preference
At Datalab, despite the stability that APIs provide, we prefer extraction by 'speaking' directly to the database. Although this sometimes involves extra work with new software versions, it is faster and easier to implement. Unfortunately, this rarely works with cloud software, because the capability is lacking - so you are dependent on APIs regardless.