A walkthrough on how to develop advanced dashboard for Market research study by feeding live data to BI software. The goal is to develop advanced dashboard with live data and keep the dashboard running without the need of manually loading the data. Usually using live data feed will be beneficial for studies that are carried out for longer duration such as trackers, diary studies. For a research that is one-time, it is better to load the data directly to BI software as periodic data import is not required ratherr the live data will be refreshed in the BI services gateway at certain intervals.
For this example, we will use data source publicly provided by Kaggle.com, decipher survey platform for API and Microsoft Power BI for dashboard development.
To showcase feeding live data to dashboard, we will use Decipher survey platform API (note: this can be done with any survey software that have API connectivity or database).
Note: We will skip the process of data collection, data validation, cleaning of the data as generally these are done prior to dashboard work.
In order to develop dashboard, we need dataset and KPI (key parameter indicator).
So, the first step will be acquiring data through API. Here are the steps to connecting and pulling data from decipher through API.
Connecting, testing, data retrieval from decipher API
1. Verifying the endpoints
Before connecting the API with Power BI, we check if the endpoints of the API are working correctly by using Postman (postman is an application where we can test API endpoints).
The steps carried out in Postman are as follows-
a. Creating a new environment in postman:
We define the host, value, that is the link to the portal from which the API key is to be taken which will be generated at the portal.
b. Type the endpoint from decipher API Doc and replace the {survey} with survey id from the portal.
c. Header: In request headers, it contains key-value of the application, Key— Describes the format of object data.
API key from the portal— An authorization token, included with requests, is used to identify the requester.
In order for the api to be authenticate with decipher we need to put an authorization header x-apikey with the api key from the portal.
d. Sending request: If the end point is working, we will get Status:200. After this the we can connect the API to Power Bi directly.
There are many status code, from which we can verify the response.
Few of them are mentioned below:
1. 200 — For Successful request.
2. 201 — For successful request and data was created.
3. 204 — For Empty Response.
4. 400 — For Bad Request. The request could not be understood or was missing any required parameters.
5. 401 — For Unauthorized access. Authentication failed or user does not have permissions for the requested operation.
6. 403 — For Forbidden, Access denied.
7. 404 — For data not found.
8. 405 — For Method Not Allowed or Requested method is not supported.
9. 500 — For Internal Server Error.
10. 503 — For Service Unavailable.
We will request the whole data for this purpose by passing the below request (blurred survey url, you can replace the url with your survey url). Once the data is loaded, it means our API setup and data retrieval is successful. Now, we can move on to next step of connecting to Power BI.
2. Connecting decipher API to Power BI: Head to Power BI Pro account and do the below steps to connect to decipher API. Testing the connection before connecting directly to Power BI is essential to ensure its error free.
Create a new file
Get data, select Web
Enter the API url along with the required header and the API key.
Load the data
We have successfully loaded the data in Power BI via API.
3. Dashboard development:
Now that the data is loaded in Power BI, the next step is to work on dashboard.
In order to proceed with the dashboard development, we need KPI (key performance indicator). KPIs are usually provided by researchers. In case this is not provided, our analyst can derive such KPI by understanding the data and discussing with researchers, end clients to narrow down on the output they want. Once this step is completed, our BI developers team will start developing the data visualisation as below.
KPI for this example are created for demo purpose
1. Overall characteristics of participants in the study: This section gives a brief overview of the participants that took part in the study
2. Food associated: The participants are shown pictures of foods in the study for which they have been asked to associate which foods that come to their mind (Top of mind) in association with keywords like breakfast, fries, drink etc
3. Eating Habits: This section gives an overview of the lifestyle and eating behaviours of the students
4. Comfort foods: The section assess the different types of comfort foods that are consumed by the participants when they are in different state of mind/mood like stressed, bored, sad or happy etc
4. Data visualisation example:
The end result as we want is to publish the dashboard and continue to receive data through API.