This is the first in a series of articles on integrating with Microsoft's Power BI. We recommend that you read the introduction here, but if you'd like, you can jump straight to the part about using Google Sheets, using Microsoft Excel (via Zapier), using the R statistical analysis package, or the last part on using Microsoft's Azure database platform.
SurveyCTO has a RESTful API which allows our users to automate processes and feed external platforms with data automatically, helping with ongoing data analysis and up-to-date visualization while data collection is still ongoing. However, creating these connections is not always easy.
We've noted persistent interest in Power BI for data visualization from our users, and wanted to advise on possible routes to configure a SurveyCTO-Power BI integration.
In this guide, we’ll present information on possible configurations using API requests or publishing data into the cloud. To use the R or Microsoft Azure database options, you'll need to read the REST API documentation to follow instructions in those parts of this guide.
To help illustrate what's possible with Power BI, Oxfam were kind enough to share a Power BI Dashboard they had used on a project where SurveyCTO was used to collect data: Oxfam GB Activity Report from Palu humanitarian response, Indonesia. You can also view a screenshot here:
Please note that this dashboard will not be hosted indefinitely but you will find other examples of dashboards online. In the case that the above link doesn't work, download the PDF attachment at the bottom of this article to see screenshots.
While Power BI supports getting data via an API, it doesn’t support digest authentication, which is the authentication standard used by SurveyCTO. Therefore, a SurveyCTO-Power BI connection is only possible via a third party application that can 1) push data via RESTful API using digest authentication and 2) connect with Power BI. Otherwise, if you’re not particularly interested in API Requests, there are other ways to integrate using our feature to publish data into the cloud. With this in mind, this guide is divided into four parts, each focused on a different integration method:
- [Publishing into the Cloud] Connect SurveyCTO to Power BI via Google Sheets
- [Publishing into the Cloud] Connect SurveyCTO to Power BI via Microsoft Excel
- [API Request] Pulling SurveyCTO Data into Power BI with an R script
- [API Request] Connect SurveyCTO to Power BI via Microsoft Azure
All options above allow you to refresh your Power BI reports/dashboards with incoming data from SurveyCTO just by clicking on the Refresh button in Power BI, but you can choose the method that fits best for your project specifics and organization’s requirements. Each has its own advantages and disadvantages.
Note that there may be other possible routes, however these are the few we've researched, understand, and have tested, even though we are not necessarily experts in these third party bridges into Power BI (e.g. R, Microsoft Azure).
These connections, except the Excel integration, require Power BI Desktop, which is only available for Microsoft Windows. It can be downloaded here. You can see the differences between Power BI desktop and Power BI service here. Fortunately, once a Dashboard has been configured in Power BI, it can be viewed in a browser on any system, as well as via the Power BI mobile app on Android and iOS.
Choosing a connection option
Your choice of connection option will depend on your situation. Google Sheets is probably the easiest with the fewest requirements, and involves no additional costs. But, Google Sheets is currently limited to 5 million cells, so it is not an indefinite solution.
Excel also has limitations, which you should read about before adopting it, and will also not serve as an indefinite solution. You will learn about more and less automated ways of connecting Power BI to an Excel spreadsheet in this part of our guide.
Whether you're familiar with the R statistical analysis package or not, the R option is simple enough to set up with little understanding of R and APIs. SurveyCTO's API also provides access to encrypted data. Like Google Sheets, R is free.
A connection via Microsoft Azure is the most complex to setup, but won't have the limitations of Google Sheets or Excel. Like the R option, Azure involves API queries which provide access to encrypted data. Microsoft Azure service has usage fees, proportional to usage. Think of Azure as a long-term organizational solution for warehousing data.
There are four options that we have documented for integrating with Power BI. Click here to view part two of this guide on integration using Google Sheets.