Accessing your Data Warehouse with REST API's

Written by: Di Truong

June 29, 2020

Background

Data is an increasingly important asset for virtually all organisations. The value of data increases when it is cleaned, conformed and integrated. A good way of thinking about the Data Warehouse process is by separating the data into three main layers, Bronze, Silver and Gold where the Gold layer contains subject oriented high quality data.

BI applications have been the only, or at least the major consumer and stakeholder of the Data Warehouse. However, there is an increasing demand to access the data, not only by Data Analysts and Business Users, but also by Data Engineers and external applications such as E-commerce platforms, CRM systems, etc.

Traditionally, access to the Data Warehouse (DWH) has been via direct access SQL (via OLEDB, ODBC or JDBC) to query or extract. Direct access in this way is not always optimal, especially for external applications. Therefore these additional stakeholders require an alternative interface which has created new challenges.

Challenges

  • Being able to guarantee low latency and availability.

  • Providing access for each system/user and controlling privileges

  • Some business users lack experience with SQL

  • Backward compatibility - DWH technology can differ (i.e. moving from Postgres to MySQL) and may require different SQL syntax.

Solution

To solve these challenges, we recommend using REST API’s to encapsulate DWH objects. This creates an abstraction layer that exposes the data to the various consumers.

Why REST API?

  • Objects in REST are always manipulated from the URI. It is the URI and no other element that is the sole identifier of each resource in this REST system. The URI allows us to access the information in order to change or delete it, or for example to share its exact location with third parties. This means we can efficiently control what DWH objects are exposed in a programmatic and dynamic approach.

  • Uniform interface: to transfer data, the REST system applies specific actions (POST, GET, PUT and DELETE) on the resources, provided they are identified with a URI. This makes it easier to obtain a uniform interface that systematises the process with the information.

  • The REST API is always independent of the type of platform or languages: the REST API always adapts to the type of syntax or platforms being used, which gives considerable freedom when changing or testing new environments within the development. With a REST API you can use PHP, Java, Python or Node.js servers, to name just a few. The only thing is that it is indispensable is that the responses to the requests should always take place in the language used for the information exchange, normally XML or JSON. 

 

REST API Architecture

Below is an example REST API architecture, comprising of an API gateway for handling and controlling all requests and (in the case of Microsoft Azure), Functions and/or Logic apps for processing the requests

Summary

In this article we have explored the challenges faced by organizations who have growing demands from users and external applications for access valuable data in their Data Warehouse. Using REST API's can solve many of these issues when carefully planned and designed.

BizOne has extensive experience building modern cloud data platforms, reach out to us for a free consultation if your business is facing the same challenges!