Pulling SurveyCTO Data into Power BI with an R script
This is the third 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:
- Install the R statistical package if it is not already installed.
- 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).
- Click on Other and choose R script, then Connect:
- Then, it will open a window to insert an R script, replacing
password. Please consult the API documentation for details on these API parameters.
Example for unencrypted form
Note: If your SurveyCTO server is running a lower version than 2.70, make sure you use digest authentication instead of basic authentication. In the GET() command, add a third parameter to the authenticate() function:
Note that you can adjust your R script, if you wish:
- 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.
- 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:
- 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:
Note that if you replaced
with 0 to return all data available, clicking on Load immediately after running the script may throw an error:
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
- 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:
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:
- Refresh a dataset from a local Power BI Desktop file
- Refresh a dataset from a cloud Power BI Desktop file
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 five 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.