Sign up for a PowerBI account or download PowerBI Desktop to create your first PowerBI dashboard right away.
Getting started with PowerBI and integration with Survalyzer
Survalyzer shares a PowerBI document template to simplify the usage of the API. Click on the Icon of your data center (and the corresponding authentication scenario) to download the PBIX template.
There are also two options for authentication:
1) using Client Id and Client Secret (account admin rights)
| EU Datacenter | Swiss Datacenter |
 | |
2) using the Client Id and the Survalyzer username and password (user rights)
| EU Datacenter | Swiss Datacenter |
| |
The first scenario is a type of a general authentication. However, if you want to impose restrictions on the visibility of downloaded data, it is worth going for second scenario – in this case, you can set a particular user limited access to particular data.
The basic privacy settings for connecting PowerBi to Survalyzer
If this is the first time you are connecting to the API using PowerBI, you will probably need to make changes to your privacy settings to avoid an error message about ‘Formula.Firewall: Query’. To do this, change the privacy settings by going to:
File => Options and Settings => Options and set ignore privacy levels
How do I connect Survalyzer to Power BI?
After Downloading the template execute the following steps:
1. Open the PBIX File.
2. Click ‘Transform data’ to open the query editor.
3. Go to the ‘Security Token’ query and open the ‘Advanced Editor’.
4. Open the Survalyzer Account settings, navigate to the Automation tab and enter the Client ID and Client Secret (or Client ID and username and password – depending on the selected scenario) in the ‘Login’ method of Power BI shown in the image and click ‘Save’. If all is correct the screen looks like this:
If you have connected to the particular data center previously using PowerBI, you can skip directly to step 5. If not – continue reading.
4a. If you are connecting to a particular Survalyzer API data center for the first time, you will be required to set up the appropriate connection method. To do this, after entering your login details, click on the ‘Edit Credentials’ button.
4b. Select the ‘Anonymous’ setting and also the level appropriate to the main address for authentication (see example in the screenshot below). Then click ‘Connect’.
At this point you should receive a response to the API request sent containing your token – as in the screenshot attached at point 4.
4c. If you have any issues with applying ‘Anonymous’ credentials to Web content, please go to ‘Data source settings’.
Then edit the permissions for all available data sources in the current file:
Click ‘Edit’, select ‘Anonymous’ and press ‘Save’:
Then save all changes by clicking ‘OK’:
5. Once your token is generated, you can go on to retrieve the data.
Select GetInterviewData and enter the desired survey Id.
You can also add data filtering – this is the last element you can add to the ReadInterviews function structure.
To do this, simply add a filter based on the possible solutions that can be found on our dedicated API page. The structure of the filters can be found in the ‘conditions’ section of the schema.
| EU Datacenter | Swiss Datacenter |
| |
TIP: Also be aware of the syntax used in PowerBI.
Example: instead of one double quotation mark, add two double quotation marks if you include content that should be treated as a string.
Then click “Refresh Preview”. Afterwards receiving the data will be successful.
From this point on, you can control the displayed table in any way you wish using the options available in the tool.
Data transformation
The next step is to extract the data into a regular table. Therefore Click on ‘List’ in line interviews and afterwards click on the button ‘To Table’, and then ‘OK’.
Then use the expand icon on the top right corner of the table.
This completes the setup procedure and the interviews are available as PowerBI table.
The functions ReadInterviews and ReadPanel can be used to read multiple surveys and Samples/Panels into PowerBI.
Use Code Plan to map values
Our template also offers a downloadable Code Plan that you can use to create a map for the extracted interviews of your survey.
This can be particularly helpful if you want to see labels for individual responses in your survey instead of numerical values.
The ReadInterviews function and its third variable, which must be set to ‘true’ if you want to load the data, are responsible for accessing the Code Plan.
In our template you will find a separate table created to extract the Code Plan. As with the GetInterviewData table, you simple change the Id of your survey in the body of the script using the Advanced Editor to do so.
If you are interested in the process of mapping values in Survalyzer surveys in PowerBI, we encourage you to read the manual we have prepared.
Connecting a Data Cube
More information about how to connect Power BI though the Survalyzer Data Cube can be found here:
https://education.survalyzer.com/knowledge-base/connect-cube-with-powerbi/
PowerBI Online Issues
The first issue is regarding authentication information and looks like this:
For PowerBI Online it’s necessary to do an explicit Login in favor of an implicit. The Login function returns a token which could be used for all subsequent calls. This is senseful for large reports where several surveys need to be read.
But for PowerBI it’s necessary to do the Login with each request. Therefore change the call in the GetInterviewData Table and change it as follows:
Default:
= ReadInterviews(SecurityToken, surveyid, 0)
For PowerBI Online (Client Id and Client Secret):
= ReadInterviews(Login("Your clientid", "Your clientsecret"), surveyid, 0)
For PowerBI Online (Client Id, username and password):
= ReadInterviews(Login("Your clientid", "Your username", "Your password"), surveyid, 0)This change will resolve the authentication issue.
The second issues is regarding the data protection and looks like this:
This issue can be mitigated by setting the privacy level to ‘Organizational’.