Using Azure Synapse Analytics to Ingest Data from APIs

Written by Sumesh Dayaram

 

Azure Synapse Analytics is a nimble service that can help a business extract data out of multiple systems and gain insights from it.

 

Azure Synapse Analytics is a tool that is part of the Azure platform, that, amongst many other things, can ingest data from multiple sources into one central location. This helps reporting tools use a single source of truth to give you insights and overviews into your business. It also facilitates retrieval of data at scale depending on a business’s needs. You can find out more about the service at https://azure.microsoft.com/en-us/services/synapse-analytics/#overview

 

The two storage architectures in Azure Synapse are either SQL serverless pool or a dedicated SQL pool. Depending on businesses requirements such as project budget, the size of data consumed, system uptime and other factors, one of the architectures will stand out. You can read more on this here https://www.hitachi-solutions.co.uk/blog/2021/05/azure-synapse-analytics—serverless-sql-pool-dv-ss/

 

Recently we have used this service to ingest data from Business central using the OData URLs from Dynamics 365 Business Central and store it in a SQL serverless pool for consumption by Power BI.

 

Azure Key Vault was used to store the web access key, which is an extra layer of security to protect the access of the businesses data within the Azure Platform. It’s important to provision for Synapse to be able to access the secret that contains the web access key in the vault.

 

A blob storage instance was also set up to house the data extracted from Business Central.

 

Connections must be set up to extract the source data from the source system (in my case Business Central) and store them in flat files within the Blob Storage instance. These connections use the secret from the key vault and are stored under the integration datasets tab.

 

The movement of data from source to flat file is done by creating a pipeline and using the copy data activity.

 

Thereafter views are created using SQL and referring to the flat files directly, cutting out steps that would previously have to be done in traditional data warehousing to access the ingested data. Once these are created you can use your favourite reporting tool that can connect to Synapse (I used Power BI) to create reports based on this data.

 

Using this tool and its features allows us to move data out of source systems and create reports much quicker with less effort compared to previous methods for data warehousing and reporting. This allows us to unlock insights quicker and be aware of changes within a business.