How to import REST API in Power BI (Load JSON / SOAP XML)

Contents

Introduction

Introduction icon json to power bi

In this article, we will learn how to import REST API in Power BI.

Power BI is a very popular Business Analytic tool used to get business information. It is very popular because it is easy to install, simple to learn and very intuitive. Also, REST API is very popular these days and we wanted to show you a way to integrate them in Power BI with the help of the ZappySys ODBC PowerPack. This software includes powerful drivers to query REST API URL, Local XML / JSON files and XML SOAP Web Service with simple SQL queries.

The following video will show how to import REST API in Power BI (JSON or XML Data):

Requirements

  1. First, you will need to have Power BI Desktop (FREE) installed
  2. In the second place, you will need to have internet access
  3. On third place, our ZappySys ODBC Power Pack

Step-by-Step: Import REST API into Power BI

We’ll walk through the steps to import a REST API into Power BI. The steps outlined below focus on working with JSON APIs, but the same principles can be applied when importing SOAP XML Web Services or local JSON/XML files.

For demonstration purposes, we will utilize a REST API to retrieve data in JSON format. The example URL we’ll use is:

This URL leverages OData and presents information in JSON format. Our objective is to use the ZappySys ODBC Power Pack to establish a connection to this URL and retrieve the information seamlessly into Power BI.

Create ODBC DSN – JSON Driver

Once ZappySys ODBC PowerPack is installed our next step is to Create and configure ODBC DSN. For example purpose, we will use ZappySys JSON Driver but steps are identical for most of ZappySys ODBC Drivers (e.g. XML Driver or CSV Driver)
  1. Search for "odbc" in your start menu and click on ODBC (64 bits). * If you cant find this then you can also go to Start Menu > ZappySys > ODBC PowerPack > Click on ODBC Data Sources (64-Bit). If you don't see ODBC 64 bit then most likely you are running 32-bit OS (So just click first ODBC Data Source)
    Open ODBC Data Source

    Open ODBC Data Source

  2. Go to User DSN Tab and press Add. If your DSN needs to be accessed by all users or some service account (like SQL Task Scheduler) then click on System Tab rather than User Tab.
    Create new ODBC DSN (User DSN Tab)

    Create new ODBC DSN (User DSN Tab)

  3. Add the ZappySys JSON Driver. It is installed with the ZappySys ODBC PowerPack.
    Add ZappySys JSON / REST Driver Connection

    Add ZappySys JSON / REST Driver Connection

  4. Enter a name for the Data source and configure other necessary properties (e.g. URL / Credentials). You can configure properties in two different modes (Simple Mode (Default) or Advanced Mode). For Our JSON Driver Example enter URL and Set Data Format as OData (For Simple Mode Check Input / Output Format Tab, For Advanced Mode Check. HTTP Advanced Settings).  If your API is not OData compliant or you are not sure then keep it DEFAULT.
    https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json
     
    Configure API Connection - Simple Mode (User Interface Mode)

    Configure API Connection - Simple Mode (User Interface Mode)

     
  5. Here is the Advanced view with all properties in Grid mode.
    Configure API Connection - Advanced Mode (Property Grid Mode)

    Configure API Connection - Advanced Mode (Property Grid Mode)

  6. Finally, you can go to preview tab. Click on the select table to generate your default SQL Query for API service and press Preview data to see the magic :).When you click Preview data it parses your SQL Query and sends HTTP Request to fetch Data from JSON service. Once the response is returned it parse nested JSON structure and turns into rows/columns.
    Select Table for preview

    Select Table for preview

     
  7. Once you select Table name from drop down, UI generates default query for you with all possible column names for selected table like below.
    Sample Query
    select 
    	"CustomerID",
    	"CompanyName",
    	"ContactName",
    	"ContactTitle",
    	"Address",
    	"City",
    	"Region",
    	"PostalCode",
    	"Country",
    	"Phone",
    	"Fax"
     from [value]
     
  8. To review more examples, make sure to click on View Examples button to see many more ways to call API services and extract/transform data
    ZappySys ODBC Driver Examples - Call JSON / REST API

    ZappySys ODBC Driver Examples - Call JSON / REST API

  9. Now last thing you can try is use Query Builder. Query Builder is an easy way to Build Queries by Overriding certain setting defined on DSN. ZappySys API SQL Query language support WITH Clause which can override settings defined on DSN UI. Usually you can Define Connection related settings on DSN and override Dynamic Settings in your SQL Query so you dont have to create many DSN for each API URL.
    Using API SQL Query Builder

    Using API SQL Query Builder

Transfer DSN settings There will be a time when you like to create similar ODBC DSN on a totally different machine. If you wish to copy settings of one DSN to different DSN then you can use Load Connection String Feature. First copy Connection string produced by UI settings on DSN configuration by clicking Copy Connection String Button (Found at the bottom of UI). Then you can go to new DSN and click Load connection string to new DSN by clicking Load Connection String Button. Understanding ODBC Connection String Format ZappySys ODBC Drivers can be called in two different ways from your Application (e.g. C#, Python, JAVA )
  1. Using Driver Name in the Connection String (You can use Copy Connection String option)
  2. Using DSN Name in the Connection String
Using Driver Name in the Connection String Syntax:
DRIVER={DRIVER-NAME-GOES-HERE}[;Prop1=xxxxxx][;Prop2=xxxxxx] .... [;PropN=xxxxxx]
Examples:
DRIVER={ZappySys JSON Driver}
DRIVER={ZappySys JSON Driver};DataPath='c:\mydata.json'
DRIVER={ZappySys XML Driver};DataPath='http://myserver/api/xml/getOrders'
DRIVER={ZappySys XML Driver};DataPath='c:\mydata.xml'
Using DSN Name in the Connection String Syntax:
DRIVER={DRIVER-NAME-GOES-HERE}[;Prop1=xxxxxx][;Prop2=xxxxxx] .... [;PropN=xxxxxx]
Examples:
DSN=MyJsonAPI_DSN
DSN=MyJsonAPI_DSN;DataPath='http://myserver/api/json/getOrders'

Connect to REST API data source in Power BI (Connect JSON / XML data)

In the previous section, we configured and added the ZappySys drivers in the ODBC Driver Administrator with information to connect to REST API. We queried the REST API data in JSON / XML format. Now let's look at how to import REST API data in Power BI using from ODBC connection.
  1. Open Power BI Desktop and select the Get data option.
    Get data using power bi

    Get data using Power BI

  1. Once Get data is clicked, Go to Other and select ODBC.
    Import ODBC data source in power bi

    Import ODBC data source in Power BI

  2. After that select ODBC DSN name from the DSN dropdown
  3. Now it's time to import data. Basically, there are two modes to import data. Table Mode and Query Mode. Query mode is the most common but we will show you both ways.
  4. Import using Power BI Query Mode:  Select your DSN and click Advanced Option to enter custom SQL Query to Import your REST API data. You can use ODBC DSN Data sources Preview tool to generate SQL Query. For example you can enter query like below. If you are not sure use Query builder (Found on Driver Preview Window)
    SELECT * FROM $
    WITH(SRC='https://my-api-url')
    Import JSON / REST API data in Power BI using SQL Query Mode

    Import JSON / REST API data in Power BI using SQL Query Mode

     
  5. Import using Power BI Table Mode:  If you don't specify SQL query under advanced options then you may get a chance to select Table name to import and Credentials. If Power BI asks for credentials then select Windows and connect.
    Select credentials for ODBC Source

    Select credentials for ODBC Source

  6. Once you enter select table, you can choose Select data to import and click OK (Below screeenshot is for the Table mode import when no SQL specified. You can pick desired table to import)
    Select Table to import in Power BI

    Select Table to import in Power BI

  7. You can also display data in map using Map visualization like below. Press the map and check Address this option will display the addresses in a map.
    see address in power bi map

    See Address data in power bi map

    Map visualization in Power BI

    Map visualization in Power BI

  8. Now, let's display data in Table Format. Select in values more columns and select the data grid to visualize the data.
    Select columns in Power BI

    Select columns in Power BI

  9. Once data is displayed, Right click on the data and select Show Data.
    show data in power bi

    Show data in Power BI

  10. The data will be displayed
    View Power BI Table data

    read json information data

Publish Power BI dashboard

  1. In order to publish the report, press Publish
  2. Press Save
    Save changes in power bi

    Save changes in Power BI:

     

  3. Select a workspace for the report
    choose power bi workplace

    Select Power BI workplace.

  4. And that is all. You have now your report ready.

Import data using ODBC ConnectionString (DSN-less)

Let’s explore the process of importing data using the ODBC Driver without the need for configuring an ODBC DSN.

When importing data, you have the option to either use an ODBC DSN or a Direct ConnectionString. This section will guide you through the steps of utilizing the ODBC ConnectionString.

After opting for the ODBC option to retrieve data, you will encounter the “Select ODBC Data Source” screen. To proceed without a DSN, follow these steps:

  1. Change the DSN dropdown to “(none).”
  2. Enter the full ODBC ConnectionString, adhering to the syntax below. You can construct your own connection string using the ODBC Driver UI and utilize the “Copy ConnectionString” option.
    Syntax:  Driver={Your Driver Name}[;Property1=value][;Property2=value]......
  3. Here is a screenshot with ConnectionString Setting
    Import Data from ODBC Driver - DSN less mode (Use Connection String)

    Import Data from ODBC Driver – DSN less mode (Use Connection String)

  4. Click “Next” to select the authentication method. Choose “Windows” and input the password value if necessary. If your connection string already includes the Password attribute, there’s no need to worry.
    Import Data from ODBC Driver - using Connection String - With Password Attribute

    Import Data from ODBC Driver – using Connection String – With Password Attribute

POST data to REST API URL

Up until now, we’ve explored the process of fetching data from URLs and importing it into Power BI. However, in certain scenarios, APIs may necessitate the use of the PUT or POST method. These methods involve submitting parameters in the body and receiving a response.

For a more in-depth understanding of how to seamlessly POST data to a URL in Power BI, watch the informative video below.

Calling XML SOAP Web Service in Power BI

So far, we have looked at examples to consume data using JSON driver. Now let’s look at an example, to call XML SOAP Web Service in Power BI.

To call SOAP API you need to know Request XML Body Structure. If you are not sure how to create SOAP Request body then no worries. Check this article to learn how to generate SOAP Request body using the Free tool SoapUI. Basically, you have to use SoapUI to generate Request XML and after that, you can replace parameters as needed in the generated body.

What is SOAP Web Service?

If you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept about SOAP Web service standard from this link There are two important aspects in SOAP Web service.
  1. Getting WSDL file or URL
  2. Knowing exact Web Service URL

What is WSDL

In very simple term WSDL (often pronounced as whiz-dull) is nothing but a document which describes Service metadata (e.g. Functions you can call, Request parameters, response structure etc). Some service simply give you WSDL as xml file you can download on local machine and then analyze or sometimes you may get direct URL (e.g. http://api.mycompany.com/hr-soap-service/?wsdl )

Example SQL Query for SOAP API call using ZappySys XML Driver

Here is an example SQL query you can write to call SOAP API. If you not sure about many details then check next few sections on how to use XML Driver User Interface to build desired SQL query to POST data to XML SOAP Web Service without any coding.
SELECT * FROM $
WITH(
	 Src='http://www.holidaywebservice.com/HolidayService_v2/HolidayService2.asmx'
	,DataConnectionType='HTTP'
	,CredentialType='Basic' --OR SoapWss
	,SoapWssPasswordType='PasswordText'
	,UserName='myuser'
	,Password='pass$$w123'
	,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'
	,ElementsToTreatAsArray='HolidayCode'	
	,RequestMethod='POST'	
	,Header='Content-Type: text/xml;charset=UTF-8 || SOAPAction: "http://www.holidaywebservice.com/HolidayService_v2/GetHolidaysAvailable"'
	,RequestData='
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:hol="http://www.holidaywebservice.com/HolidayService_v2/">
   <soapenv:Header/>
   <soapenv:Body>
      <hol:GetHolidaysAvailable>
         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->
         <hol:countryCode>UnitedStates</hol:countryCode>
      </hol:GetHolidaysAvailable>
   </soapenv:Body>
</soapenv:Envelope>'
)
Now let's look at steps to create SQL query to call SOAP API. Later we will see how to generate code for your desired programming language (e.g. C# or SQL Server)

Video Tutorial - Introduction to SOAP Web Service and SoapUI tool

Before we dive into details about calling SOAP API using ZappySys XML Driver, lets first understand what is SOAP API and how to create SOAP requests using SoapUI tool. You will learn more about this process in the later section. The video contains some fragment about using SOAP API in SSIS but just ignore that part because we will be calling Soap API using ZappySys ODBC Driver rather than SSIS Components.  

Using SoapUI to test SOAP API call / Create Request Body XML

Assuming you have downloaded and installed SoapUI from here, now we are ready to use WSDL for your SOAP Web Service Calls. If you do not have WSDL file or URL handy then contact your API provider (sometimes you just have to add ?wsdl at the end of your Service URL to get WSDL so try that. Example: http://mycompany/myservice?wsdl ). If you don't know what is WSDL then in short, WSDL is Web service Description Language (i.e. XML file which describes your SOAP Service). WSDL helps to craft SOAP API request Body for ZappySys XML Driver. So Let's get started.
  1. Open SoapUI and click SOAP button to create new SOAP Project
  2. Enter WSDL URL or File Path of WSDLFor example WSDL for our sample service can be accessed via this URL
    http://www.dneonline.com/calculator.asmx?wsdl
    Create new SOAP API Project in SoapUI tool for SOAP API Testing
    Create new SOAP API Project in SoapUI tool for SOAP API Testing
  3. Once WSDL is loaded you will see possible operations you can call for your SOAP Web Service.
  4. If your web service requires credentials then you have to configure it. There are two common credential types for public services (SOAP WSS or BASIC )
    1. To use SOAP WSS Credentials select request node and enter UserId, Password, and WSS-PasswordType (PasswordText or PasswordHash)
      Configure SOAP WSS Credentials for SoapUI (SOAP API Testing Tool)
      Configure SOAP WSS Credentials for SoapUI (SOAP API Testing Tool)
    2. To use BASIC Auth Credentials select request node and double-click it. At the bottom click on Auth (Basic) and From Authorization dropdown click Add New and Select Basic.Configure Basic Authorization for SoapUI (SOAP API Testing Tool)
      Configure Basic Authorization for SoapUI (SOAP API Testing Tool)
  5. Now you can test your request first Double-click on the request node to open request editor.
  6. Change necessary parameters, remove optional or unwanted parameters. If you want to regenerate request you can click on Recreate default request toolbar icon. Create SOAP Request XML (With Optional Parameters)
    Create SOAP Request XML (With Optional Parameters)
  7. Once your SOAP Request XML is ready, Click the Play button in the toolbar to execute SOAP API Request and Response will appear in Right side panel. Test SOAP API using SoapUI Tool (Change Default XML Body / Parameters, Execute and See Response) Test SOAP API using SoapUI Tool (Change Default XML Body / Parameters, Execute and See Response)

Create DSN using ZappySys XML Driver to call SOAP API

Once you have tested your SOAP API in SoapUI tool, we are ready to use ZappySys XML driver to call SOAP API in your preferred BI tool or Programming language.
  1. First open ODBC Data Sources (search ODBC in your start menu or go under ZappySys > ODBC PowerPack > ODBC 64 bit)
  2. Goto System DSN Tab (or User DSN which is not used by Service account)
  3. Click Add and Select ZappySys XML Driver ZappySys ODBC Driver for XML / SOAP API ZappySys ODBC Driver for XML / SOAP API
  4. Configure API URL, Request Method and Request Body as below ZappySys XML Driver - Calling SOAP API - Configure URL, Method, Body ZappySys XML Driver - Calling SOAP API - Configure URL, Method, Body
  5. (This step is Optional) If your SOAP API requires credentials then Select Connection Type to HTTP and configure as below. ZappySys XML Driver - Configure SOAP WSS Credentials or Basic Authorization (Userid, Password)
    ZappySys XML Driver - Configure SOAP WSS Credentials or Basic Authorization (Userid, Password)
  6. Configure-Request Headers as below (You can get it from Request > Raw tab from SoapUI after you test the request by clicking the Play button) Configure SOAP API Request Headers - ZappySys XML Driver Configure SOAP API Request Headers - ZappySys XML Driver
  7. Once credentials entered you can select Filter to extract data from the desired node. Make sure to select array node (see special icon) or select the node which contains all necessary columns if you don't have array node. Select Filter - Extract data from nested XML / SOAP API Response (Denormalize Hierarchy) Select Filter - Extract data from nested XML / SOAP API Response (Denormalize Hierarchy)
  8. If prompted select yes to treat selected node as Array (This is helpful when you expect one or more record for selected node) Treat selected node as XML Array Option for SOAP API Response XML Treat selected node as XML Array Option for SOAP API Response XML

Preview SOAP API Response / Generate SQL Code for SOAP API Call

Once you configure settings for XML Driver now you can preview data or generate example code for desired language (e.g. C#, Python, Java, SQL Server). Go to Preview tab and you will see default query generated based on settings you entered in previous sections. Attributes listed in WITH clause are optional. If you omit attribute in WITH clause it will use it from Properties tab.

Preview Data

Preview SOAP API Response in ZappySys XML Driver Preview SOAP API Response in ZappySys XML Driver

Generate Code Option

Generate Example Code for ZappySys Driver
Generate Example Code for ZappySys Driver

SOAP / REST API pagination in Power BI

Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that ZappySys ODBC Driver includes many options to cover virtually any pagination method.
Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):

Paginate by Response Attribute

This example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page.
SELECT * FROM $
WITH(
SRC=@'https://zappysys.com/downloads/files/test/pagination_nextlink_inarray_1.json'
,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']
)

Paginate by URL Parameter (Loop until certain StatusCode)

This example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).
SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'
,PagingMode='ByUrlParameter'
,PagingByUrlAttributeName='page'
,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'
,PagingByUrlCheckResponseStatusCode=401
,IncrementBy=1
)

Paginate by URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).
SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/cust-<%page%>.xml'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

Paginate by Header Link (RFC 5988)

API like GitHub / Wordpress use Next link in Headers (RFC 5988)
SELECT * FROM $
LIMIT 25
WITH(
	 Src='https://wordpress.org/news/wp-json/wp/v2/categories?per_page=10'
	,PagingMode='ByResponseHeaderRfc5988'
	,WaitTimeMs='200' --//wait 200 ms after each request
)
 

SOAP / REST API Error Handling in Power BI

Sometimes errors occur... they just do and there is nothing you can do! Or can you? Actually, in ODBC PowerPack you can handle them in two ways.

METHOD 1 - Using Error Handling Options

When to use?

You may want to use them when your source is a resource located on the Internet; e.g. a file on a website, a file on an FTP server or just a plain API HTTP response. By default, when a remote server returns an error, data retrieval is stopped, an error is raised and no data is given back to you. This might not be always desirable.

Scenario 1

Imagine a scenario, that there is a web server which each day at 12 AM releases a new JSON file with that day's date as filename, e.g. http://www.some-server.com/data/2018-06-20.json. And, of course, you want to download it and use it daily in your Power BI report. But you have a problem: Power BI report data sources are refreshed each hour and you may get HTTP 404 status code (no file was found) when a file is not released yet. Which consequentially means other data sources won't be updated as well and you will see old and cached data on the report. That's where you could use Continue on any error or Continue when Url is invalid or missing (404 Errors) to avoid an error being raised and let other data sources to be updated.

Scenario 2

Another scenario is when you expect a web server to raise some kind of HTTP error when accessing a URL. You don't want ODBC Data Source to raise an error but instead, you want to get response data. That's where you can use Continue on any error or alike together with  Get response data on error to continue on an error and get the data:

METHOD 2 - Using Connection [Retry Settings]

Another scenario you may run into is a buggy web server. You ask it to give you some file or data and it, like a snotty kid, just doesn't give it to you! You have to ask twice or thrice before it does its job. If that's the case, you have to retry HTTP requests using Connection:

Security / Connection Types (Options for HTTP, OAuth, SOAP) in Power BI

If you need to authenticate or authorize your user to access a web resource, you will need to use one of the Connections:
  • HTTP
  • OAuth
ZappySys XML Driver - HTTP and OAuth Connection Types

HTTP Connection

Use HTTP Connection for simple Windows, Basic, NTLM or Kerberos authentication. Just fill in a username and a password and you are good to go!
You can also use HTTP Connection for more sophisticated authentication like:
  • SOAP WSS (when accessing a SOAP WebService)
  • Static Token / API Key (when need to pass an API key in HTTP header)
  • Dynamic Token (same as Static Token method except that each time you need to log in and retrieve a fresh API key)
  • JWT Token (As per RFC 7519)

OAuth

If you are trying to access REST API resource, it is a huge chance, you will need to use OAuth Connection. Read this article to understand how OAuth authentication and authorization works and how to use it (article originally was written for SSIS PowerPack, but the concepts and UI stay the same): https://zappysys.com/blog/rest-api-authentication-with-oauth-2-0-using-ssis
There are few settings you can coder while calling Web API

API Limit / Throttling

While calling public API or other external web services one important aspect you have to check,  how many requests are allowed by your API. Especially when you use API pagination options to pull many records you have to slow down based on API limits. For example, your API may allow you only 5 requests per second. Use Throttling Tab on Driver UI to set delay after each request.

2D Array Transformation

If you are using JSON or XML API Driver then possible you may have to transform your data using 2D array transformation feature. Check this link for more information.  

Import data into Power BI from SQL Query

If you prefer to import data from an SQL query instead of selecting a table name, you can leverage advanced options during the import steps. Here’s a step-by-step guide:

  1. After selecting the DSN, proceed to the import steps.
  2. Click on the “Advanced Options” to access the SQL Query editor.

By utilizing these advanced options, you gain the flexibility to tailor your import process by specifying custom SQL queries to retrieve the exact data you need.

Import data into Power BI using SQL Query (ODBC Data source)

Import data into Power BI using SQL Query (ODBC Data source)

Edit Query / Using Parameters in Power BI (Dynamic Query)

In the real world, many values of your REST / SOAP API call may be coming from Parameters. If that's the case for you can try to edit script manually as below. In below example its calling SQL Query with POST method and passing some parameters. Notice below where paraAPIKey is Power BI Parameter (string type). You can use parameters anywhere in your script just like the normal variable.

Import REST API in Power BI - Using parameters in SQL Query (Edit code - Advanced Mode)

 

let
    vKey=paraAPIKey,
    Source = Odbc.Query(
"dsn=ZS - OData Customers", 
"SELECT * FROM $ WITH (SRC='http://httpbin.org/post'," 
& "METHOD='POST'," 
& "HEADER='Content-Type:application/json'," 
& "BODY=@'{""CallerId"":1111, ""ApiKey"":""" & vKey & """}')")
in
    Source
 

Edit Query Settings after Import

There will be a time you need to change initial Query after dataset import in Power BI. Not to worry, just follow these steps to edit your SQL.

Edit Power BI Data Source SQL after SOAP Web Service Import

Edit Power BI Data Source SQL after REST / SOAP Web Service Import

Using DirectQuery Option rather than Import

So far we have seen how to Import REST API data into Power BI but what if you have too much data and you dont want to import but link it. Power BI Offers very useful feature for this scenario. Its  called DirectQuery Option. In this section we will explore how to use DirectQuery along with ZappySys Drivers. Out of the box ZappySys Drivers wont work in ODBC Connection Mode so you have to use SQL Server Connection rather than ODBC if you wish to use Live data using DirectQuery option. See below step by step instructions to enable DirectQuery mode in Power BI for REST API data. Basically we will use ZappySys Data Gateway its part of ODBC PowerPack. We will then use Linked Server in SQL Server to Link API Service and then we will issue OPENROWSET queries from Power BI to SQL Server and it will then call REST API via ZappySys Data Gateway.

Step-By-Step

  1. First read this article carefully how to query REST API in SQL Server.
  2. Once linked server is configured we are ready to issue API query in Power BI.
  3. Click Get Data in Power BI, select SQL Server Database
  4. Enter your server name and any database name
  5. Select Mode as DirectQuery
  6. Click on Advanced and enter query like below (we are assuming you have created JSON Data Source in Data Gateway and defined linked server (Change name below).
    select * from OPENQUERY(YOUR_LINKED_SERVER_NAME,
    'SELECT * FROM value WITH( SRC=''https://services.odata.org/V3/Northwind/Northwind.svc/Orders?$format=json''  )'
    )
    DirectQuery option for Power BI (Read REST API Data Example using SQL Server Linked Server and ZappySys Data Gateway)

    DirectQuery option for Power BI (Read REST API Data Example using SQL Server Linked Server and ZappySys Data Gateway)

  7. Click OK and Load data ... That's it. Now your REST API data is linked rather than imported.

Performance Tips for REST API / XML SOAP Calls

While calling APIs you may face some performance issues. There are a few tips you can consider to speed up things.

Use Server-side filtering if possible in URL or Body Parameters

Many API supports filtering your data by URL parameters or via Body. Whenever possible try to use such features.  Here is an example of odata API, In the below query the first query is faster than the second query because in the first query we filter at the server.
SELECT * FROM value
WITH(
	 Src='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json&$filter=Country eq ''USA'''
	,DataFormat='Odata'
)

-- Slow query - Client-side filtering
SELECT * FROM value
WHERE Country ='USA'
WITH(
	 Src='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json'
	,DataFormat='Odata'
)

Avoid Special features in SQL Query (e.g. WHERE, Group By, Order By)

ZappySys API engine triggers client-side processing if special features are used in Query. Following SQL Features will trigger Client-Side processing which is several times slower than server-side processing. So always try to use simple query (Select col1, col2 .... from mytable )
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY
  • FUNCTIONS (e.g. Math, String, DateTime, Regex... )
LIMIT clause does not trigger client-side processing.

Consider using pre-generated Metadata / Cache File

Use META option in WITH Clause to use static metadata (Pre-Generated)There are two more options to speedup query processing time. Check this article for details.
  1. select * from value WITH( meta='c:\temp\meta.txt' )
    --OR--
    select * from value WITH( meta='my-meta-name' )
    --OR--
    select * from value WITH( meta='[ {"Name": "col1",&nbsp;"Type": "String", Length: 100},&nbsp;{"Name": "col2",&nbsp;"Type": "Int32"} ...... ]' )
  2. Enable Data Caching Options (Found on Property Grid > Advanced Mode Only )

Consider using Metadata / Data Caching Option

ZappySys API drivers support Caching Metadata and Data rows to speed up query processing. If your data doesn't change often then you can enable this option to speed up processing significantly. Check this article for details how to enable Data cache / metadata cache feature for datasource level or query level. To define cache option at query level you can use like below.
SELECT * FROM $
WITH 
(  SRC='https://myhost.com/some-api'
  ,CachingMode='All'  --cache metadata and data rows both
  ,CacheStorage='File' --or Memory
  ,CacheFileLocation='c:\temp\myquery.cache'
  ,CacheEntryTtl=300 --cache for 300 seconds
)
   

Use --FAST Option to enable Stream Mode

ZappySys JSON / XML drivers support --FAST suffix for Filter. By using this suffix after Filter driver enables Stream Mode, Read this article to understand how this works.
SELECT * FROM $ 
LIMIT 10 --//add this just to test how fast you can get 10 rows
WITH(
  Filter='$.LargeArray[*]--FAST' --//Adding --FAST option turn on STREAM mode (large files)
 ,SRC='https://zappysys.com/downloads/files/test/large_file_100k_largearray_prop.json.gz'
 --,SRC='c:\data\large_file.json.gz'
 ,IncludeParentColumns='False'  --//This Must be OFF for STREAM mode (read very large files)
 ,FileCompressionType='GZip' --Zip or None (Zip format only available for Local files)
)
 

Working with Gateways in Power BI (Schedule Import)

If the data needs to be updated, it is necessary to create a gateway on-premises. In this new section, we will install a Power BI Gateway and in the next section schedule it to update the REST API information.
  1. In the last section, we Published the report. Power BI may ask you to SIGN IN.
    Sign in Power BI

    Sign in in Power BI

  2. Select the Workspace and select Datasets
    [caption id="attachment_10110" align="alignnone" width="702"]power-bi-my-workspace-dataset Go to workspace and dataset[/caption]

    Go to workspace and dataset

  3. Right-click the report and select Settings.
    Define settings for Power BI report

    Specify settings for the report

  4. The system will ask for a Gateway. Stay here.
    add power bi gateway

    Add Power BI Gateway

  5. Use the following link to install a Data Gateway:
    https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem
  6. Run the installer and press Next
    Initial gateway window for installation

    Gateway installer

  7. Select the option On-premises data gateway (recommended). This option allows access to multiple users and can be used by more applications than Power BI.
    Choose Power BI gateway

    Choose Power BI option

  8. The installer will show a warning message.
    Warning message gateway installation

    Warning message during installation

  9. Select the path to install and check the I accept the terms.
    accept terms

    Accept terms

  10. Specify the email address to use the gateway.
    Register email in gateway installation

    Register email address

  11. After entering the email, write the gateway name and a recovery key. Make sure to confirm the recovery key.
    Specify name and recovery key

    Enter recovery key

 

Manage gateways and configure the schedule

Once that the gateway is installed we will configure it and add the connection strings.
  1. The next step is to go to manage gateway
    Power BI - Manage Gateway Setting

    Power BI - Manage Gateway Setting

  2. In order to get the connection string, we will need the connection string of the ZappySys JSON Driver. In the first section of this post, we explained how to configure it. Press Copy Connection String
    ZappySys connection properties

    Connection properties

  3. Once that the data is copied, add a New data Source. In Data Source Name, enter the Data Source Name of the ZappySys JSON driver in step 13 and in Data Source Type, select ODBC. In connection string copy and paste from the clipboard of the step 13 and press Add.
    [caption id="attachment_10113" align="alignnone" width="720"]gateway-data-source-name-connection-string ZappySys connection properties in Power BI[/caption]

    ZappySys connection properties in Power BI

  4. Once added the gateway. You can see the schedule refresh to On and Add another time to add the time where you want to refresh the data.
    Schedule gateway

    Schedule gateway

 

Conclusion

In this article, we guide you through the process of configuring the ZappySys Power Pack, focusing on the ZappySys driver for JSON. This driver is instrumental in extracting data from REST APIs or JSON files. Follow these steps to seamlessly add the extracted data to Power BI and generate insightful reports.

Additionally, we’ll walk you through the configuration of the Power BI gateway, ensuring that the data is refreshed on a specified schedule for up-to-date and accurate reporting. To try this configuration yourself, download ODBC PowerPack and embark on a journey of efficient data extraction and reporting.

References

Posted in JSON File / REST API Driver, ODBC PowerPack, Reporting - Microsoft Power BI, REST API Integration and tagged , , , , , , , , .