This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
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.
Considerations for this approach are:
-
-
Requires a proxy web API (containing integration/logic).
-
Considerations for this approach
-
). Also refer to the Early Adoption Program announcement if you plan using Azure SQL Database (Platform as a Service).
- Refer to the following when working with standard (interactive) Power BI reports.
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.
DirectQuery
-
-
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).
-
-
)
-
Paginated Reports with custom logic ()
-
How to call API by using TSQL (by Dat Nguyen) (