Examples - PowerBI

Goal

Use the API to build a simple dashboard in PowerBI showing me the areas by department and how many rooms have the various RDS statuses associated with them.

Figuring out what data to export using the documentation and get request URL

First thing to do in order to figure out what data to export is to log into the open API documentation GUI for my database. Since the list of available attributes will vary depending on my database setup I have to login in. I then look into the Schemas section and expand Rooms and also the "more" properties below "< * >".

In this case I would like to report over "main" department only (root grouping) and the programmed and designed area so I note the following attributes:

^{room_function_0_no
room_function_0_name
programmed_area
designed_area
rds_status_short} 

Here I can then copy the Request URL directly that I can use in Power BI. In this case:

^{https://api-no.drofus.com/api/rooms?$select=room_function_0_no,room_function_0_name,programmed_area,designed_area,rds_status_short}

Getting the data in PowerBI Desktop

1. Get Data → From Web

2. Paste the URL copied from above

3. Basic Authentication - choose basic (see Authentication for how to build username)

4. Click "To Table"

5. Click "Split columns"

6. Click Apply and close

TIP - Changing data types

Sometimes PowerBI does not understand the datatype we get - so you have to tell it what it is. Right click on the query and select "Edit query". Right click on the columns in question and select "Change Type"

Example: Column chart with programmed vs designed area by department:

Example 2: Adding number of rooms by RDS status and programmed are by department