Options to Visualize Web API Data in Power BI Reports (using DirectQuery mode)

[oliverlintner]

Have you ever come across a situation where you needed to load data from a web API into a Power BI report (hosted in Power BI Service)? Power BI comes with a set of data connectors which includes a connector to load data from web resources (e.g. see Tutorial: Import and analyze data from a webpage - Power BI | Microsoft Docs). Whilst, the existing web connector works great for many scenarios, it is not always suitable for all use cases / scenarios. For example:

• The built-in connector requires that Power BI's Import mode is used. Some scenarios will require support for DirectQuery (see Power BI Connectivity Modes| Microsoft Docs).
• There are limited customization options. There are cases where the actual web API request/flow requires a higher level of customization (e.g. authentication, request headers, support for different content-types, etc.) in which case this built-in connector may not be appropriate.

The purpose of this article is to outline alternative approaches to querying data from web API’s using the DirectQuery mode. There is no particular order of preference to these options and the choice would be determined by the use case as well as licensing in Power BI.

Option 1: Paginated Reports with Custom Logic/Code

SSRS reports enables report designers to run custom code that is part of custom assemblies. Being able to run custom code as part of a report enables lots of interesting opportunities. However, paginated reports in Power BI Service don't support custom assemblies as of today.

The GitHub repository (Paginated Reports in Power BI Service) describes a workaround (including samples). The main idea is to embed VB code into the report (RDL) file which then calls for example another web API. This web API would contain the custom/integration logic required to call the actual target web API.

Paginated Reports with Custom Logic/Code

Considerations for this approach are:

• Managing custom embedded VB code can be difficult (e.g. dev experience, re-using code in multiple reports, CI/CD integration, automated testing, etc.)

• Requires a proxy web API (containing integration/logic).

• Ensure access to web API is properly secured (e.g. API key, limit accessibility, etc.)

Option 2: Use T-SQL to Proxy Web API in Paginated Reports

Since Power BI paginated reports support querying data directly from a SQL server, another option is to use T-SQL to proxy a web API. In this scenario, a SQL Stored Procedure must be implemented that internally calls the web API and, ideally, parses the response (for an example click on this link: sample).

Use T-SQL to Proxy Web API in Paginated Reports

Considerations for this approach

• A Power BI gateway is required when using SQL Server as a data source (see document). Also refer to the Early Adoption Program announcement if you plan using Azure SQL Database (Platform as a Service).

• Refer to the following Power BI community question when working with standard (interactive) Power BI reports.

Option 3: Custom Data Connector

Chris Webb published a blog article describing how to build a Power BI DirectQuery dataset on top of a REST API.

The basic idea is to build a custom data connector (using the M language) which connects to a configured ODBC target (using DirectQuery). In this scenario a custom ODBC driver is needed; it proxies the target web API.

Custom Data Connector

Although, it is possible to implement custom data connectors that aren't based on ODBC (but for example on ODATA), ODBC is currently the only feasible way to support DirectQuery mode.

Considerations for this approach

• Managing custom M code requires M skills required and dev experience.

• An ODBC driver (ODBC to web API) is required. This would require either custom development or the use of 3rd party solution (which may incur additional costs).

• A proxy compute node (for example a virtual machine) would be required to host the custom ODBC driver. One should also ensure that this configuration would satisfy scalability requirements.

• When developing custom connectors, it is important to plan on how to make a custom connector available to end-users. Depending on the requirements, the connector developer may choose to

  • provide all the information needed for end-users to securely load it (for more information see Trusted third-party connectors).

  • certify the custom connector (following Microsoft's Connector Certification Program) and make it publicly available (distributed by Microsoft). For more information see Power Query Connector Certification.

Resources

• Develop custom Power BI data connector(GitHub)

• Paginated Reports with custom logic (GitHub)

• How to call API by using TSQL (by Dat Nguyen) (blog)