How to streamline APIs with Xpert BI

Xpert BI is an advanced data automation and data governance tool. It automates both the process of getting data from APIs and sharing data through APIs. As a result, the time spent on accessing, managing, and changing APIs for data and analytics is reduced from weeks to hours. It means getting analytics projects done faster and cheaper, using your data and analytics resources.  

API ecosystems are becoming the new normal when accessing data for analytics. When business applications are moved to the cloud, data is typically accessible through APIs rather than the traditional, direct database access. Curated data from the centralized data platform may be requested by operational systems or 3rd parties, and an API will be a natural choice. In addition, data fabrics usually depend on APIs for metadata exchange, while decentralization concepts such as data mesh sometimes rely on APIs for data exchange.  

However, APIs also pose a set of challenges such as:

1. Getting data from APIs  

• How do you get data sets from APIs that are usable for analytics?  
• How do you work with complex data hierarchies? 

2. Sharing data through APIs

• How do you expose data through APIs with minimal effort?
• How can you make data available from databases using APIs?
• How can you share metadata in an enterprise context?
  

APIs are traditionally not designed for data and analytics purposes, making it challenging to get consistent, complete, and usable datasets. In addition, you often need to work with complex data hierarchies in JSON and XML which is time consuming and needs lots of coding to become more user friendly to a business analyst or data scientist.  

The challenge of sharing data via APIs is related to efficient development and maintenance of APIs. It typically requires software developers to code each API manually in .NET, Java, Python, Scala etc which in our experience is massively time-consuming, inefficient, and potentially prone to errors.  As a result, organizations ask us how they can standardize and automate their work with APIs for data and analytics.  

Brief background and definition 
An application programming interface (API) is a way for two or more computer programs to communicate with each other.  (Wikipedia )

There are many types of APIs, however, when most technical people talk about an API today they mean a Web API, that accesses/communicates through the HTTP request(s). When talking about APIs in this blogpost, we mean Web APIs. When we talk about Data Platforms, we mean both warehouses and lakes and lake houses and potential combos. 

Answering challenge 1: Getting data from APIs 
Xpert BI utilizes metadata and high level configurations to automate, standardize and structure the way data is ingested into the raw data layer of the data platform. Every data engineer who has worked with ingesting data from APIs knows that even though the API follows a standard (or “standard”), there is a large variance on how to implement the integration and the request to get the data you need.  However, with the technical features and configurations available in Xpert BI, data can be ingested from both REST and SOAP APIs without any coding, only configuration. There are is a lot of different settings which means that even though it is standardized, you still have the features you need in the different scenarios of ingesting API data sources. If – in the odd case where the standard does not work, you always have a ‘get out of jail’ card to include what is called a ‘pre-process’ which can be Python or PowerShell to have all the flexibility you need.  

The destination can be either a table in a database or just the raw JSON or XML file. When loading data into a table in a database, the structure of the file must match a table format. This is of course rarely the case, which is why Xpert BI has a ‘Design Transform’ option- where you can flatten the file by an easy to use drag-and-drop interface. Xpert BI finds the node structure and you simply just select the nodes (columns) you want, and Xpert BI flattens the file (using a auto-generated XSLT stylesheet) to a table format. 

Answering challenge 2: Sharing data through APIs 
With more use cases for data integration and consumption organizations might want to have Web APIs as a standard for integrations and for sharing data regardless of data origin. However, developing this can be very costly and time-consuming.  With the API automation module in Xpert BI the data engineer or data integration specialist can configure a Web API (REST) supporting various security options such as authentication with bearer token or MS Oauth2 in a few minutes (!) 




Simply define the API name, select source tables, and publish*.  The data is now available through a Web URL, documented with Swagger, and ready to consume for another application or user.  Xpert BI can also perform data exports from the data platform into other applications (APIs) or other data platforms by configuration only. 

Different scenarios for APIs 
•    API interface on a data mart or a defined set of tables in the data warehouse 
•    API interface on a business application/data source database  
•    Reverse ETL – i.e. Push/Export/Post data into a destination API 
 *Xpert BI needs to be connected to the Xpert BI Web Service on-prem or in Azure for this to work.  

The latest release of Xpert BI introduces improved API automation. To learn more about the release make sure to watch our release webinar.