OData with Power BI

These instructions are intended to guide you through the process of connecting to your CX Survey respondent data (responses and scores) using the OData Connector.

Connect to your data using MS Power BI

Use all the features of Power BI to analyze and visualize your CX Survey respondent data by using the OData Connector to make a direct connection.

Obtain your credentials first by following the instructions under the OData Connector section.

Logging In

Once you have your token (password), do the following:

  1. In Power BI, go to the Get Data function and select Get Data > From Other Sources > From OData Feed.
  1. Enter a URL (Basic) based on your data requirements and click OK. For information on the URLs you can use, see URLs for getting data.

    The URL in the screenshot below returns all data for all CX Surveys (https://api.foresee.com/v1/bi/cxmeasure/odata.svc/Measures).

  1. You are prompted with the following screen. Choose Basic, and then enter your User Name (Predictive Experience login ID) and Password (token).
  2. Select the level as seen in the following screen, and then click Connect.

Importing Question Response Data into Power BI

  1. Open a new project in Power BI.
  2. Go to the Get Data dropdown at the top and select OData Feed.
  3. When the following prompt displays, paste in the desired URL from above using the Measure Key of your choice.
  1. Click OK to complete the process.
    You then see a screen similar to the following image. Do NOT load, but instead click Edit.
  1. Once the data has loaded, select the columns that you would like to keep and remove the rest. In this example, we want to keep the following:
  • Respondentid
  • Responsetime
  • QuestionLabel
  • answerValues

For description of all columns, see Data Column Definitions.

  1. With the columns selected, click the menu of the Remove Columns button and select Remove Other Columns.
  1. With only the desired columns remaining, you have the following options:
  • Rename the query from Query 1 to your desired name.
  • Extract Values from the answerValues.
  • Change repsonseTime to Data Time format (Optional).
  1. When you extract values, you are asked to select a delimiter. In this example, we selected the Comma delimiter. You can select any delimiter from the list.

Pivot the Response Data

  1. On the Transform tab in Power BI, select the questionLabel column and click Pivot Column.
  1. You are prompted with a screen to select the Values Column. Choose answerValues. From the Advanced options menu, select Don't Aggregate.
**Note:** Once you click OK to these options, it may take some time for the data to be transformed.

Now you have a "Custom Extract" with respondent answers. The data set contains one respondent per row and contains values for the columns you selected to keep. In our example, we chose to keep the following columns, hence each respondent row contains data for these columns only:

  • Respondentid
  • Responsetime
  • QuestionLabel
  • answerValues

You can choose to keep other columns in your data set, for example, rename column headings, replace values, format response date/time.

  1. Now click Close and Apply to get your data set.
**TIP:** If you do this again, you can save some steps.

Many of the steps above have been recorded as a script in the Advanced Editor. This means you don't have to go through all of these steps when analyzing a different CX Survey.

You still must:

  1. Open Power BI.
  2. Go to Get Data.
  3. Enter the URL string for "responses" but change the Measure Key: https://api.foresee.com/v1/bi/cxmeasure/odata.svc/clients/11596511/Measures(8866805)/Responses.
  4. When the preview of data is loaded and you click EDIT, then you can go into the Advanced Editor and paste the following lines.
    Source = OData.Feed("https://api.foresee.com/v1/bi/cxmeasure/odata.svc/Measures(8866805)/Responses", null, [Implementation="2.0"]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"answerValues", "respondentId", "responseTime", "questionLabel"}),
    #"Extracted Values" = Table.TransformColumns(#"Reordered Columns", {"answerValues", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Values", List.Distinct(#"Extracted Values"[questionLabel]), "questionLabel", "answerValues")
    #"Pivoted Column"

Importing Respondent Score Data into Power BI

  1. Using the same project, select Get Data > From OData Feed. 
  2. When the following prompt displays, paste RespondentsScores in the URL and click OK.


  1. Once the data is loaded, remember to click Edit, select the columns that you would like to keep and remove the rest. In this example, we want to keep the following: 
  • Respondentid 
  • Responsetime 
  • latentName 
  • latentScore 
  1. With the columns selected, click the menu of the Remove Columns button and select Remove Other Columns. The result is this:
**TIP:** This may be a good time to make sure both queries are named, such as responses and scores. 
  1. Now it's time to pivot the Score Data! On the Transform tab in Power BI, select the latentName column and click the Pivot Column button.

Now you have a "Custom Extract" with respondent scores. One respondent per row with scores for all elements, SAT, and Outcomes. There is more you can do to fine-tune this data set. For example: 

  • Rename column headings 
  • Replace/format values 
  • Format response date/time 
Save some steps in the future by using this code in the Advanced Editor.
    Source = OData.Feed("https://api.foresee.com/v1/bi/cxmeasure/odata.svc/Measures(8866805)/RespondentsScores", null, [Implementation="2.0"]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"respondentId", "responseTime", "latentName", "latentScore"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[latentName]), "latentName", "latentScore")
    #"Pivoted Column"

Don't close and load yet.

Working with Both Scores and Responses (Merging Data) 

Once you have set up both scores and responses in the same workbook, you need to merge them so that they are in one table (this allows you to create visualizations using both Sat and responses to CQs).  

Use Merge Queries as New.  

For Measurement surveys, in most instances, all respondents have been asked the Model Questions (scores) but may not have been asked CQs (responses).

For Engagement Surveys, each respondent is presented with a subset of driver questions. The different subsets are rotated across respondents such that sufficient scores for each element are collected as part of partitioning. For a given respondent, scores are only generated for the questions that appeared in the subset presented to the respondent. If a question is not asked to a respondent, the score will have a Null value for that respondent.

Therefore, it is recommended to perform a left-join of scores to responses based on respondent ID.

Once the new query is created, you must open the merge table and select which columns you would like to use.  

Now you have a "Custom Extract" with Respondent Answers and Scores Together.

Close and load.