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:
I can also try to run the query directly in the UI to see that I get the result I want and get the request URL with the correct columns:
Here I can then copy the Request URL directly that I can use in Power BI. In this case:
Getting the data in PowerBI Desktop
- Get Data → From Web
- Paste the URL copied from above
- Basic Authentication - choose basic (see Authentication for how to build username)
- Click "To Table"
- Click "Split columns"
- 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"