Use the REST API with Power BI Part I
This tutorial is about using the REST-API for retrieval of Automatic Passenger Counting Data together with Power BI Desktop from Microsoft. Connecting to the REST-API allows you to pull actual Stops data from your fleet into the Power BI backend. The Stops data is consisting of geolocations, time information and of course APC data as the number of passengers boarded and alighted.
You will learn how to pull data from the REST-API by providing a vehicle identifier and an operation date. This approach is good for loading new data on a daily basis into a production system. Part II of the tutorial is all about gathering the complete dataset of your fleet across a custom time range for doing one-time static analysis.
Loading data into Power BI Desktop requires the latest version of the software, which is available as free download at the website of Microsoft. At the time of writing the version is 2.58 (May 2018).
When working with the IRMA onAir REST-API you need to make sure you have the following information at hand.
- Your API credentials consisting of username and password, originally provided by your iris project manager
- You will need the unique operator identifier that was specified during project setup
- You will need to know at least one unique vehicle identifier from the fleet.
It is recommended to use the latest REST-API version available. This tutorial uses the version v1.6. For further details please have a look at the API reference documentation.
The Power BI template file containing all single steps explained within this tutorial can be downloaded from the repo. Make sure you change the getdata query to contain your definition of a unique operator, a unique vehicle Id and your credentials.
The APC data is loaded into Power BI using the Web import module reachable at the Get Data ribbon. When the data source is set up correctly, you will need to shape the data using the built-in Query Editor. The original data source will not be affected by the changes you make, only the particular view you are working with. Shaping the data means:
- transforming XML to table structures
- setting data types
- removing unnecessary data
The following sections describe these steps in detail.
At first please open the Web dialog to set up the data source.
It is required to open the Advanced mode of the dialog, especially for entering credentials and content-type. At first you are going to enter the URL. Please copy the sample URL from below and replace demoOperator by the unique operator identifier that was specified for your project. A valid URL also requires a unique vehicle identifier and an operation date, reflecting the calendar day for which data shall be retrieved. Please replace both values by the ones specified for your project.
https://api.irmaonair.com/services/REST/apc/v1/r6/stops/demoOperator?vehicleId=demoVeh&opdate=2018-04-02Please put your URL into the URL parts text fields of the From Web dialog. For readability reasons the URL is split into several parts in the tutorial.
For handling authorization and selecting XML as desired data format, there are two parameters that need to be taken care of.
- Data format: Add a new parameter in the section HTTP request header parameters (optional) and choose Accept from the predefined list on the left. Type application/xml as a value into the other field.
- Authorization: Please generate a base64 authorization code using your username and password. Here is a link to a website doing this job for you. You just need to copy the basic authentication header back to Power BI into a new Request parameter field. The type of parameter is Authorization and can be selected from the predefined list as well.
Once all settings for the data source are done, hit the OK button to proceed. If you receive an HTTP 404 error message (Not Found), then there is propably no data available for the operation date that was put in the request.
When the data source was set up correctly, you are asked to load the data into Power BI. But before confirming, the view of the data has to be adjusted. This is because the underlying XML structure of the data source will be mapped to a flat table structure. This requires a few clicks, mainly because XML has a hierarchical structure that does not natively map to table structures.
Please select Edit instead of loading the data.
If the dialog slightly differs and Edit is not available in your context, please press OK and convert the data to a table by selecting the To Table button at the very left top of the ribbon bar.
At this time there are only three different columns available which correspond to the three XML values of the outer XML structure of the source file.
By expanding the third column VEHICLES, you will be able to extract the next layer of XML fields and map them to table columns. The expand button is located on the right side of the column name, please have a look at the red circle within the image below.
You need to confirm that all available columns shall be expanded.
The column name has changed from VEHICLES to VEHICLES.VEHICLE, which means the vehicle records are still wrapped within the underlying XML structure. Therefore the column has to be expanded another time.
This time you are asked to confirm that a few new columns are added to the table.
These fields are
- operator
- time
- stops (which is actually a list not a field)
- vehicleId
The fields operator, time and vehicleId contain actual values from the data source. Only the _stops _column is another abstraction of a list structure, which needs to be expanded, too. Please repeat the procedure of expanding columns two more times for those columns containing the APC data and all other columns where you still see the little expand button. At the end all columns have to be expanded to work with the full variety of data provided by the IRMA onAir REST-API.
As of now most of the job is done already. A preview of the data was loaded into the table. The last things that need to be done here is to check all the data types, because not in every case Power BI chose the right data type for each of the columns. The data type can be changed by right-clicking on the column name, then hit Change Type and select the correct data type for that specific column. For example, all columns containing timestamps, shall be converted to Date/Time/Timezone data type.
Here is a full list about how the columns are mapped to Power BI data types. This is important due to the fact the the values shall be treated in the right way by Power BI. When decimals or integer values are falsely detected as text, then the creation of new reports is very limited.
| Table | Power Bi Datatype |
|---|---|
| timestamp | Date/Time/Timezone |
| version | Text |
| VEHICLES.VEHICLE.operator | Text |
| VEHICLES.VEHICLE.time | Date/Time/Timezone |
| VEHICLES.VEHICLE.stops.stop.type | Whole Number |
| VEHICLES.VEHICLE.stops.stop.timeStart | Date/Time/Timezone |
| VEHICLES.VEHICLE.stops.stop.timeStop | Date/Time/Timezone |
| VEHICLES.VEHICLE.stops.stop.lon | Text |
| VEHICLES.VEHICLE.stops.stop.lat | Text |
| VEHICLES.VEHICLE.stops.stop.id | Text |
| VEHICLES.VEHICLE.stops.stop.VehicleId | Text |
| VEHICLES.VEHICLE.stops.stop.apcData.apc.door | Whole Number |
| VEHICLES.VEHICLE.stops.stop.apcData.apc.catId | Whole Number |
| VEHICLES.VEHICLE.stops.stop.apcData.apc.in | Whole Number |
| VEHICLES.VEHICLE.stops.stop.apcData.apc.out | Whole Number |
When all shaping is done, the changes are applied by hitting the Close & Apply button, located at the very top left of the window.
At this time the data is ready for building any kind of reports from it. This is also the step where our expertise ends. But here is a short preview of a stacked chart showing the departures (y-axis) together with the number of boardings (x-axis).
