By Roger Light, Data & Analytics Consultant, Altis Consulting UK
Introduction
There are many different places to gather data for your Power BI reports: Excel and other files, databases on premises on in the cloud and, increasingly, APIs.
Many types of organisations provide data over APIs, including government agencies, media companies, software services providers and NGOs. They can also be used internally by companies wanting to integrate data between multiple systems, for which Power BI and other reporting platforms are a common use case.
Even though your main data source for reporting is likely to be internal, such as data warehouse or a series of source files, you can augment your existing data by accessing an API. A few example use cases:
- Take demographic and economic data from the World Bank via API to lend context to sales or other data with an international dimension.
- Take data from social media company APIs to enhance marketing analysis.
- Take current exchange rates from one of many commercial APIs to help accurately process finance data.
- Take data from one of your corporate source systems via an API.
In this blog we are going to look at a Power BI report built by querying the Wikipedia API, and then address what an API is and how Power BI can connect to one.
A Power BI report using the Wikipedia API
The embedded Power BI report below shows some selected page view data collected from the Wikipedia REST API. Several different queries have been made against the Page Views endpoint to get both total views and highest ranked pages. The report highlights some data points I came across that I think are interesting, there are surely many other stories waiting to be uncovered in the data.
The left side shows page views for some of the most popular articles, up-to-date for the previous day, with a comparison to the same period in the prior year. These figures all update as the report refreshes daily and the API is re-queried.
The second section shows some perspective on the impact of the Coronavirus pandemic that began to affect Wikipedia views in April 2020. The change is apparent in the first graphs, and some examples of relevant articles are called out in the list below.
The final section shows the changing pattern of usage across different language editions of Wikipedia as users move from desktop access to mobile devices. Majority mobile usage had already taken hold in Japanese language users prior to 2015 and by 2022 it has become the norm. In Russian language users this crossover from desktop to mobile didn’t take place until 2019, with the other language editions all falling somewhere in between.
What is an API?
While the term Application Programming Interface (API) has many uses across information technology, we are interested in web data APIs. These provide a querying interface using a web address. The API ecosystem is complex and we not going to survey it in detail here. Broadly, there are different technologies for delivering APIs, such as REST and SOAP, which have their own capabilities and mechanics. And there are different formats for returning data, such as JSON or XML. Generally, we won’t need to care too much about how the API is delivered, it should have documentation about how to use it. But we do care about the format of data returned. Power BI can work with both JSON and XML, as well as other formats, but the details will be different.
As an example, we are going to take a quick look at the Wikipedia API, used for the interactive Power BI report embedded in this blog.
The web address for accessing a web data API consists of two main sections, an endpoint and a query. Here is a full web address to query the Wikipedia API:
Now let’s break it down:
This is the endpoint. It is static, acting as the location of the API, the place we go to ask the question. An API may have multiple endpoints to support asking different types of question. This endpoint provides counts of page views.
Here are the parameters to define a query:
In this case we are asking for a count of pageviews for English Wikipedia desktop users, for every day between Jan 1st 2010 and May 15th 2020.
Knowing what endpoints are available and what parameters can be used to query them is not intuitive or particularly standard, so reading the documentation is vital. Any well maintained API will have documentation that includes definitions, allowed query values and limitations and usage examples. It will also let us know what data format we can expect to get out of it. The Wikipedia API returns JSON.
Another important element of API interaction is authentication. The Wikipedia REST API is free and public so no authentication is required. For commercial or private APIs the web address may need to include a key or token, or even a username and password.
How to connect to an API in Power BI
Connection to a web API from Power BI consists of four steps:
- Use the Web data source connector
- Enter the API end point plus parameters as the URL
- Supply any authentication details the API requires
- Format the results of the query into a table format that can be integrated with your Power BI model.
Power BI will automatically try to convert JSON results to a table format, XML takes a little more work and the specific steps required will vary from one output to another.
Considerations when working with APIs
A few things to think about when working with APIs:
- For those APIs that require authentication, some token or key systems have expiration dates after which queries will stop working until authentication is renewed.
- Most APIs have some kind of usage limit. Once breached, they may stop working for a while or start to queue requests, impacting performance.
- As with any data source, there may be restrictions on what use the data can be put to, so always check licencing. For example, some free APIs will prohibit commercial use.
Conclusion
An API connection can open many benefits for reporting in Power BI, including enriching your own data with context from external sources or providing a more flexible way to connect with your own data in the cloud.
For the curious and technically minded there is a wide playground of freely available public APIs to learn with and exploit. Others may want to seek external help if the technical resources are not available internally.
Connect with us if you’d like to learn more about how we can help you be successful with Power BI.
