Integration with Power BI, part 5: Using an R script

Avatar
Marta Costa
Follow

This is the fifth part in a series of articles on integrating with Microsoft Power BI. Please read the introduction before reading this part. This part focuses on how to publish data from SurveyCTO into Power BI using R, a statistical package.

R is a popular, free programming language and statistical environment that is widely used today. Microsoft supports various integration capabilities with R and this integration option takes advantage of one of these. Fortunately, you do not need to know anything about R in order to use it as a bridge between SurveyCTO and Power BI.

We recommend that you use this integration option if you already use R and are looking to summarize data for presentation in reports or dashboards. We also recommend that you consider R as an integration option with Power BI if the limitations of Google Sheets or an Excel file as a bridge are too constraining. You'll also find the R method to be quicker and easier than first setting up a Zapier connection to publish data to an Excel file, then connecting it to Power BI (as in part three of this guide).

To publish data from your SurveyCTO server into Power BI via R, follow these steps:

  1. Install the R statistical package if it is not already installed.
  2. Open Power BI Desktop and select Get Data from the Introduction Window. Otherwise, go to the Home tab and click on Get Data (select the symbol, not where it says Get Data with the arrow).
  3. Click on Other and choose R script, then Connect:

image1.png

  1. Then, it will open a window to insert an R script, replacing servername, formid, [D], username, and password. Please consult the API documentation for details on these API parameters.

Example for unencrypted form

#installing and saving R packages needed to run the commands GET() and fromJSON()
install.packages("httr", repos = "http://cran.us.r-project.org")
library(httr)
install.packages("jsonlite", repos = "http://cran.us.r-project.org")
library(jsonlite)

#API Request with digest authentication
request <-
GET("https://servername.surveycto.com/api/v2/forms/data/wide/json/formid?date=[D]",
authenticate("username", "password"))

#retrieve the contents of a request as a character vector
data_text <- content(request, "text")

#convert from JSON data to R object
data <- fromJSON(data_text, flatten = TRUE)

Note that you can adjust your R script, if you wish:

    1. The URL link in our example queries data using our latest version of the API for downloading data in JSON format. This is the preferred and advisable URL link as it brings advantages, notably that it supports encrypted data. However, you’re also able to download form data or datasets in CSV format instead; or even download a subset of data according to the directions in our REST API documentation.
    2. We named the dataset ‘data’, but you can replace this name with any other you may see fit for your project. This will be the name displayed in the Fields menu of Power BI:

image2.png

The rsurveycto package has been developed by the Agency Fund. With this package, R users can easily access data on a SurveyCTO server using SurveyCTO’s REST API. Check out this blog post to learn more about this package and try it works.
  1. After clicking OK in the R script window, a new window will open showing all data defined in the code. Checkmark the dataset(s) you want to use and click on Load:

3.png

Note that if you replaced [D] with 0 to return all data available, clicking on Load immediately after running the script may throw an error:

image4.png

The reason is that if you're using API queries that return all data (date=0), you will be rate limited to one request each 300 seconds, and Load runs the script again. Please consult the API documentation for details on the 417 error. The short of it is that you'll have to wait 5 minutes until you can run such a request again.

Alternatively, instead of using date=0, set a date early enough to retrieve all data available. For example, if you started your data collection in August 1, 2019, then any earlier date will fetch all observations. This is the recommended approach as it will avoid any issues/errors caused by rate limiting. You can use any online converter to convert a date into Unix epoch time.
  1. From this point, you’ll be able to create Reports and Dashboards using the dataset imported via API Request. Whenever you want to refresh your tables and graphics so that they’re all using the latest data available on your SurveyCTO server, click on Refresh in the menu bar:

refresh_powerbi.png

Clicking Refresh will run your script, which will trigger a new API request. Your dataset will be updated, as well as all associated graphics and tables. You can learn more about refreshing in these help topics from Microsoft:

For more details about this integration, see the Microsoft help topic about running R scripts in Power BI Desktop.

There is one other option that we have documented for integrating with Power BI. Click here to view part sixth of this guide on integration using Microsoft Azure database.

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

0 Comments

Article is closed for comments.