Page tree
Skip to end of metadata
Go to start of metadata

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 

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:

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



  • No labels

3 Comments

  1. Åsmund Kveim Lie this sample should be updated with using the "PowerBI" button in the client for simplicity


    1. I agree. I could do it, but would also like customer success to take a bit "ownership" of these pages. We should discuss on the next product meeting.

      1. I wanted to do just that, send that to them and they could learn how to do it, but when the it was already to hard for them to use..

        I do not think that none-developer should learn how to use swagger at all....