Integration with Power BI, part 1: Introduction

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 directly publishing data into Power BI, 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.

Introduction

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 publish data directly into Power BI, use R or use Microsoft Azure database, you'll need to read the REST API documentation, and follow instructions in those parts of this guide.

An example

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.

Connection options

The most direct and simple way to connect both applications is to use SurveyCTO REST API to directly publish data into PowerBI. Although, if you are using third party applications in your data collection process, you may prefer to include those in this connection. SurveyCTO-Power BI connection is also possible via a third party application that can 1) push data via RESTful API 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 five parts, each focused on a different integration method:

1. [API Request] Connect SurveyCTO to Power BI directly
2. [Publishing into the Cloud] Connect SurveyCTO to Power BI via Google Sheets
3. [Publishing into the Cloud] Connect SurveyCTO to Power BI via Microsoft Excel
4. [API Request] Pulling SurveyCTO Data into Power BI with an R script
5. [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. Not using any kind of third application will be the most straightforward approach, but if you would prefer to use any of the other methods, which is perfectly reasonable, here are some recommendations.

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 publishing data directly to Power BI.

Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.