Getting started with AiM and Microsoft Power BI (Desktop Version)

Creating an API Connection

The following steps are intended to illustrate an API connection to retrieve the list of active campuses in AiM. The full API call is listed as Active Campuses in the API Service Catalog.

Log in to Power BI (Desktop Version) and click Get Data
Choose Other -> Web
Paste in a valid REST API call (reference API Service Catalog for more examples) Example: https://uconnapp.assetworks.com:6443/fmax/api/v3/iq-reports/custom-resource?fields=multitenantId,facId,description&tableName=AeBFacE&active=Y&rowLimit=100
Enter REST API credentials and connect If needed, request an AiM IQ REST API Service Account
Power BI will display results of the REST API call.

Working with an API Response

Data returned from an API call may need to be transformed if the default data type does not reflect the expected format. In this example, the field facID was set to number and should instead be text, so as not to drop leading zeros.

To change a data type (common activity when importing to another program), right-click on the column header, then choose Change Type -> Text
Another common activity is removing uneeded columns. To do this, right-click on the column header, then choose Remove. There are several other transformations which can be optionally performed using this same contecutal options menu.
For this example, the only columns kept are facId and description, which we’ve named to Campus Code and Campus Description.
Once the data set has been transfromed to meet your requirements, click the Close & Apply button to complete the process. The dataset is now ready for use in Power BI reports, dashboards, etc.

Building a Data Model from Multiple API Responses

A comprehensive data model will often require several API calls to AiM. Power BI supports joining the results to create a relational data model. In this example, Campus data will be joined with Building data. This is necessary for a report that needs the full campus name (i.e. campus description in place of the campus code) and full building name.

Create a new connection that returns all active buildings from AiM (same process steps as outlined above for campus) Example: https://uconnapp.assetworks.com:6443/fmax/api/v3/iq-reports/custom-resource?fields=multitenantId,facId,bldg,description,bldgStatus&tableName=AeSBldC&bldgStatus=IN SERVICE,NOT IN SERVICE,RENOVATION,UNINHABITABLE&rowLimit=1000
Apply transformations, rename and/or remove columns as needed. Close and apply.
On the Data tab, we now have two tables. In this example, table names are Campus and Buildings.
On the relationships tab, create a one-to-many join on facId (drag and drop) between Campus and Buildings.
With a relationship created, user can now accurately select fields from both data sets into a single report or dashboard.