Accessing the Results API using Power Pivot

The Power Pivot add-in for Microsoft Excel 2010 can be downloaded from Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010.

Power Pivot comes automatically installed with the Professional Plus versions of Excel 2013 and 2016.

The following instructions demonstrate how to connect to the Results API using Power Pivot in Excel 2016:

1. Open Excel 2016.
2. Click the Power Pivot tab in the ribbon.
   
3. Click Manage. A new Power Pivot book opens.
   
4. In the Get External Data section, click From Data Service, and then click From OData Data Feed. The Table Import Wizard opens.
   
5. Enter a Friendly connection name and the Data Feed URL (the URL of the Results API service for the Questionmark account you want to access).
   
6. Click Advanced. The Advanced window opens.
7. Change Integrated Security from SSPI to Basic.
   

   Due to a bug with Power Pivot, sometimes the Advanced window is blank when opened. If this happens, click on the Providers drop-down list and reselect Microsoft Data Feed Provider. This should refresh the window and display the information.

   

8. Enter your username and password of an administrator with reporting permissions for the Questionmark account you are trying to access in the User ID and Password fields.
   
9. Change Persist Security Info from False to True.
   

   You should not send spreadsheets that have persisting security credentials to other users, as the spreadsheets can be used to access information without requiring the user to reenter a username and password.

   

10. Click OK to close the Advanced window.
11. Click Next. The Select Tables and Views section opens.
12. Choose the top checkbox to select all Results API feeds, or choose just the specific feeds you want to access.
    
13. Click Finish. Because Power Pivot downloads all of the result data from each feed so it has it locally, the import process may take a while if you have large amounts of data in your Results Warehouse.

The Results API feeds you selected have now been imported into Power Pivot. You can create reports from the result data you have downloaded using Power Pivot.

For more information about the Power Pivot add-in and Microsoft Excel, please refer to their respective documentation.

Example video

We have put together a video demonstrating how to connect to the Results API via Power Pivot and create a simple assessment outcome analysis report. To view the video, click OData and the Results API for Analytics: An Overview.