<img alt="" src="https://secure.dump4barn.com/213374.png" style="display:none;">

How to streamline APIs with Xpert BI

Bilde av Anja Loug Helland
by Anja Loug Helland

13. Feb 2023, 7 minutes reading time

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.  
Demo API Ingest
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. 
Transform designer
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 (!) 
Rest API

API Name

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.

Watch our release webinar

Anja Loug Helland

Anja Loug Helland

Anja is Head of Advisory and co-founder of BI Builders and has worked with data warehousing and data platforms for over 15 years. With experience from a range of different clients, Anja is an expert in understanding our customers' needs and mapping those needs to data and data models to achieve business value.

Follow our blog


Our dedicated employees write professional blogs worth reading.
Follow the blog for a sneak peek at the future!

Others would also read


How data automation elevates your Power BI reports

How data automation elevates your Power BI reports

Picture of Terje Vatle

by Terje Vatle

28. Jun 2023, 10 min reading time

Adding the missing piece to Microsoft Fabric

Adding the missing piece to Microsoft Fabric

Picture of Terje Vatle

by Terje Vatle

21. Jun 2023, 13 min reading time

Seasonal shifts and exciting updates!

Seasonal shifts and exciting updates!

Picture of BI Builders

by BI Builders

03. May 2023, 8 min reading time