IIR IDB API Example
Fetching and Processing Data from the API
Visualize Florida Projects TIV Heatmap

Last Updated:


Download Files
This example shows how to create a Power BI report that accesses the IIR IDB API and pulls an arbitrary amount of data from multiple project endpoints.

Live Demo

Note: This visual displays data between 2020-09-17 and 2020-10-01. The report discussed herein and available for download should look similar, but uses a rolling date range instead.

1. Setup & Overview

1.1 System Requirements

This example assumes you are creating Power BI reports in Power BI Desktop and (optionally) publishing them to a Power BI Workspace. Some portions of this example may not be compatible with the Power BI Web Editor.

You can get Power BI Desktop from the Microsoft Store or the Microsoft Download Center.
Get PowerBI Desktop from Microsoft Store Microsoft Download Center
1.2 API Overview

To make a successful API call, the following 3 items are required:

  1. A bearer token to authorize the API call
  2. An API endpoint of a specific data set
  3. A set of query parameters to identify a subset of the data

The bearer token is required for all API calls. To generate a token go to the Generate Access Token method on our API documentation, enter your IIR user credentials and desired Token Lifetime, and click TRY. If successful you will get a response similar to this:

The token can be found by clicking the "Response Headers" tab and copying the text after "authorization: Bearer ", as highlighted in the adjacent image.

Illustration of finding the API token in the header of the Generate Access Token return.
Warning

The IIR API does not support token lifetimes of more than 30 days. Power BI will cache results and prevent you from suddenly having a blank report, but if you need it to always be up-to-date you'll need to keep that token current. Make sure that you have a plan in place to update the access token on a monthly basis.

You can handle this yourself or utilize the Update-PowerBIDatasetAPIToken.ps1 PowerShell script we included with the other example files for this purpose. See the Scheduling Access Token Replacement section for more details on this script.

1.3 Create an Access Token Parameter in Power BI

While you can hardcode the Access Token into the URL or as code variable, a better approach is to use an updatable Parameter. This also permits easier token reuse in the event you want to make several independent queries to the IIR API for different parts of a report.

Steps to Create a Parameter in Power BI Desktop:

  1. On the Home tab of the ribbon in Power BI Desktop click on the icon of the "Transform Data" button.
  2. On the Home tab of the PowerQuery Editor click the text portion of the "Manage Parameters" button.
  3. In the dropdown-list click "New parameter"
  4. Name the parameter access_token, set Type = "Text", and paste your API Token as the current value.
  5. Click OK.
Picture of how the access_token parameter should look when setup in Power BI.

2. Querying the IIR API

While Power BI supports the IIR API, at this time the Power BI GUI does not support generating the code to do so. We'll start off with a Blank Query and use the Advanced Editor to add the proper code.

For more information on the coding language used by PowerQuery, see M Formula Language Documentation.

2.1 Basic API Call

A basic API Call is suitable for returning small result-sets of a known size. For summary or reference list endpoints the upper limit is 1000, for detail endpoints the upper limit is 50. This example is going to query the projects/summary endpoint to get a list of summarized offline events based on the query parameters sent to the endpoint.

Query parameters being used in this example include:

  1. physicalAddressCountryName=U.S.A.
  2. physicalAddressStateName=Florida
  3. liveDateMin= Two Weeks Ago (rolling value)
Important

All URL strings must pass static analysis or the dataset will not be refreshable when published. If any query parameters are dynamic, use the Query option of Web.Contents() to include them.

M Formula Code:

				

			


			


			

				

					
2.2 Paginated Queries

					

						If you need to retrieve an arbitrary number of records, or a number of records greater than the limit on returns in a single query, you will need loop through the paginated results.  To do this you will need to:
					

					
    
						
  1. Retrieve the total number records matching your query parameters.  This can be found in the totalCount attribute of all API Returns and is unaffected by limit.
    2. 
						
  2. Find how many pages of results there are by dividing this number by the maximum permitted limit value for the endpoint you are querying, rounding up.
    3. 
						
  3. Use the offset query parameter to loop through the result set.  In each query offset = iteration × limit, where iteration is 0-based.
						
    4. 
						
  4. Union all query results together.
    5. 
						
  5. Expand nested data columns and perform normalization.
    6. 
					

					
To aid in debugging, I've also added an EntityCount variable that you can use to override the results of GetEntityCount().

				

				

					
M Formula Code:

					

				

			


			


			

				

					
2.3 Getting The Details

					
Among the values returned by the Summary query are a set of record IDs, in this case ProjectIds.  You can use these IDs to query details endpoints and retrieve additional fields.  Note however, that the maximum limit of detail endpoints is different than that of summary endpoints.

					
The process of retrieving details is very similar to retrieving summary data, with the exception that we already know how many records will be returned (one for every summary record).

					
    
						
  1. Determine how many pages the results will be split into
    2. 
						
  2. Assemble the list of ProjectIds
    3. 
						
  3. Loop through that list until details are returned for all summary results
    4. 
						
  4. Union all query results together
    5. 
						
  5. Expand nested data columns and perform normalization
    6. 
					

				

				

					
M Formula Code

					

				

			


			

				

					
2.3.1 Joining Results

					
Now that you have both Summary and Detail result sets, you'll need to join them.  You may have noticed that the projectId field was renamed to projectId2 in the preceding query.  PowerQuery requires that all column names in a join must be unique, so joining where projectId = projectId fails.  Renaming one of the projectId columns before joining acts as a workaround.

				

				

					
M Formula Code

					

				

			


			


			

				

					
2.4 Bringing It All Together

					
Combining all the steps above, you get a query that will pull all the data and fields you need via the IIR API.  It will dynamically determine the number of queries needed to retrieve all data, run those queries, combine the results, and normalize everything so it's ready for reporting.

				

				

					
M Formula Code:

					

				

			


			


			

				

					
3.0 Enabling Dataset Refresh

					
Making a Published Report Refreshable

				

				

					
Once you've published a report to Power BI, you'll need to change a few settings for it to be refreshable.

				

				

					

						Annotated screenshot of a Power BI Workspace.
						

							
Step 1

						

					

				

				

					

						Annotated screenshot of the settings for a Power BI DataSet.
						

							
Step 2

						

					

				

				

					

						Screenshot showing how to skip testing for datasource credentials.
						

							
Step 3

						

					

				

			


			


			

				

					
3.1 Scheduling Access Token Replacement

					
As part of ensuring your Power BI Datasets successfully refresh, you will need to ensure they always have a valid IIR API access token.  This can be done manually, but for the purposes of this tutorial we will be using the Update-PowerBIDatasetAPIToken.ps1 PowerShell script included with this demo.  Access documentation for it via the PowerShell command Get-Help "Update-PowerBIDatasetAPIToken.ps1" -Full or Get-Help "Update-PowerBIDatasetAPIToken.ps1" -ShowWindow.
					

					
Before you can use it though, you'll need to make sure a few requirements are met.

				

				

					
3.1.1 Software & Setup

					
This script is compatible with PowerShell 5.1+.  Windows PowerShell 5.1 comes installed on Windows 10/Windows Server 2016 by default so if you're running those or later you're good to go.  Otherwise, you'll need to download a compatible version from one of the links in the adjacent table.

				

				

					

					
						

						Install The Latest Version of PowerShell From:
						

						

						
							Get Power BI Desktop from Microsoft Store
						
						
							Multiplatform Install Instructions
						
						

					
					

				

				

					
In addition, the code requires installation of the MicrosoftPowerBIMgmt.Profile and JWTDetails modules so PowerShell can access the Power BI REST API and determine when the token expires.  You can install these from the PowerShell Gallery in a compatible version of PowerShell via the commands:
						
							
								Install-Module -Name JWTDetails -MinimumVersion "1.0.2" -Scope User
								Install-Module -Name MicrosoftPowerBIMgmt.Profile -MinimumVersion "1.2.1026" -Scope User
							
						

					

					
Finally, you'll need to ensure the user or service principal invoking the REST API calls against Power BI have the necessary permissions.  This includes Contributor access to the relevant workspaces as well as access to the Power BI Service scope Dataset.ReadWrite.All when using an application or service principal.  Setting this up is beyond the scope of this tutorial, but Microsoft has a good tutorial on Registering AAD Applications.
					

				

				

					
3.1.2 Examples

					
The simplest way to run the script (and one we recommend you do at least once to ensure everything is working) is to utilize interactive prompts to get user credentials.  In this case, the only thing you need to provide is an array of datasets.
						
							
								$Datasets = @(
									@{WorkspaceID="de285058-3253-47b2-9fb8-3e71b10d2406"; DatasetID="14bb4cae-bfa3-4e3b-9e3e-2c65914ff07c"},
									@{WorkspaceID="de285058-3253-47b2-9fb8-3e71b10d2406"; DatasetID="bd3765a1-5c39-4af2-b883-74bba349f7b6"}
								)
								. "Update-PowerBIDatasetAPIToken.ps1" -Datasets $Datasets
							
						

					

					
Further along, you'll likely want to set the script up to run without user interaction.  One way to do this is via a service principal.
						
							
								$Tenant = "Example Tenant"
								$Datasets = @(@{WorkspaceID="255a33b4-22aa-4d28-81f6-321e687e7348"; DatasetID="9c7be75f-7f63-4f2f-a444-cd45d61a7ca4"})
								# NOTE: If possible in your workflow store credentials securely instead of in in-script as plaintext.
								$IIRcred = New-Object System.Management.Automation.PSCredential 'UserName','Password'
								$PowerBICred = New-Object System.Management.Automation.PSCredential 'ApplicationID', (ConvertTo-SecureString 'AppSecret' -AsPlainText -Force)

								# Run the script
								. "Update-PowerBIDatasetAPIToken.ps1" -Datasets $Datasets -IIRcred $IIRcred -PowerBICred $PowerBICred -Tenant $Tenant -ServicePrincipal