Extract data from iObeya to Power BI

Integration Uses Cases - Data from iObeya

Note

This kind of integration can be imagined with any system, any tool. This document describes use cases by using Power BI but it is provided as an example only; iObeya is not a Power BI expert and will not maintain and support the code blocks in this document.

1. Pre-requisites

An iObeya platform with the Facade API deployed
- For online platforms, since the version 4.10, it is automatically managed by iObeya
- For on-premise platforms, this feature is not yet available
Power BI Online or Power BI Desktop
An available iObeya JWT Token
- Power BI provides different ways to access an external data source. None of them allows to request an API directly thanks to a JWT Token. So one have to use the Anonymous type and the token will be manually provided in the header of the requests.

Follow the documentation iObeya - Machine to machine requests to the APIs that explains how to get a “JWT Token”

2. Configure a dataset

In Power BI, Select Get Data > Blank Query

Go to “Manage Parameters”

Then add 3 new parameters

Name	Description	Value
JWT Token	The token that owes the iObeya permissions
iObeyaBaseUrl	Base URL of the iObeya platform	https://your_platform.iobeya.com or your OnPremise url
iObeyaApiUrl	Base URL of the Facade API. By default, will be the same as the iObeya base URL + ".api" inserted in the middle	https://your_platform.api.iobeya.com or your OnPremise facade api url

Click on “OK”

3. Get data

It is possible to request any services available in the iObeya APIs (Facade and Legacy). Refer to the associated documentation to know more.

Note

Everything in this section required advanced Power BI skills (DAX) to be fully controlled and maintained. iObeya provides this information for illustrative purposes.

The instructions (code blocks) need to be inserted in Power BI via the Advanced editor.

Right click on a query > Advanced Editor

Copy paste a code block provided

Click on "Done"

You will be ask for credentials, select “Anonymous” as mentioned above.
- Note: If you have a credential error, please check if your token is still valid, or if you missed adding “page & size” on paginated API. This generates a 303 response that Power BI handles as “Invalid credentials” instead of following it.

Get the results

3.1 Get the rooms

Get a table with all rooms available to the user associated to the JWT Token:

let
    // Request iObeya by giving the JWT Token and expose it as a table    
    source = Json.Document(Web.Contents(iObeyaApiUrl & "/v1/rooms?page=1&size=50", [Headers=[Authorization="Bearer " & #"JWT Token"]])),
    data = source[data],
    #"table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"output" = Table.ExpandRecordColumn(#"table", "Column1", {"self", "kind", "id", "name", "domainId"}, {"Column1.self", "Column1.kind", "Column1.id", "Column1.name", "Column1.domainId"})
in
    #"output"

Here is an example of the result in a table:

3.2. Get the rooms - paginated version

Some of the iObeya API endpoints are paginated. In order to retrieve all the pages, it's possible to use such code block in a blank query.

let
    // Global method to retrieve all the data from nextlink
    iObeya.Feed = (url as text) as table => GetAllPagesByNextLink(url),

    // Return a single page
    GetPage = (url as text) as table =>
      let
        Source = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & #"JWT Token"]])),
        nextLink = GetNextLink(Source),
        data = Table.FromRecords(Source[data])
    in
        data meta [NextLink = nextLink],

    // Read all pages of data.
    // After every page, we check the "NextLink" record on the metadata of the previous request.
    // Table.GenerateByPage will keep asking for more pages until we return null.
    GetAllPagesByNextLink = (url as text) as table =>
        Table.GenerateByPage((previous) => 
        let
            // if previous is null, then this is our first page of data
            nextLink = if (previous = null) then url else Value.Metadata(previous)[NextLink]?,
            // if NextLink was set to null by the previous call, we know we have no more data
            page = if (nextLink <> null) then GetPage(nextLink) else null
        in
            page
    ),

    // The getNextPage function takes a single argument and is expected to return a nullable table
    Table.GenerateByPage = (getNextPage as function) as table =>
    let        
        listOfPages = List.Generate(
            () => getNextPage(null),            // get the first page of data
            (lastPage) => lastPage <> null,     // stop when the function returns null
            (lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
        ),
        // concatenate the pages together
        tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"data"}),
        firstRow = tableOfPages{0}?
    in
        // if we didn't get back any pages of data, return an empty table
        // otherwise set the table type based on the columns of the first page
        if (firstRow = null) then
            Table.FromRows({})
        else        
            Value.ReplaceType(
                Table.ExpandTableColumn(tableOfPages, "data", Table.ColumnNames(firstRow[data])),
                Value.Type(firstRow[data])
            ),

    // In this implementation, 'response' will be the parsed body of the response after the call to Json.Document.
    // Look for the 'next' field and simply return null if it doesn't exist.
    GetNextLink = (response) as nullable text => Record.FieldOrDefault(response, "next"),
        source = iObeya.Feed(iObeyaAPiUrl & "/v1/rooms/?page=1&size=200")
    in
        source

With this, you can handle the pagination automatically
Change this line with another API call if needed

iObeya.Feed(iObeyaApiUrl & "/v1/rooms/?page=1&size=200")

3.3. Get activity cards on a planning board

Here is an example that gets the activity cards of a planning board placed between two given dates:

let
    Source = Json.Document(Web.Contents(iObeya_platform & "/v1/cards/activity?boardId=" & boardId & "&from=" & Date.ToText(StartDatePlanning, "yyyy-MM-dd") & "&to=" & Date.ToText(EndDatePlanning, "yyyy-MM-dd") & "&page=1&size=100", [Headers=[Authorization="Bearer " & #"JWT Token" ]])),
    #"Convert in table" = Table.FromRecords({Source}),
    #"data developped" = Table.ExpandListColumn(#"Convert in table", "data"),
    #"data developped1" = Table.ExpandRecordColumn(#"data developped", "data", {"self", "kind", "id", "title", "startDate", "endDate"}, {"data.self", "data.kind", "data.id", "data.title", "data.startDate", "data.endDate"}),
    #"Type modified" = Table.TransformColumnTypes(#"data developped1",{{"self", type text}, {"kind", type text}, {"totalCount", Int64.Type}, {"data.self", type text}, {"data.kind", type text}, {"data.id", type text}, {"data.title", type text}, {"data.startDate", type date}, {"data.endDate", type date}}),
    #"Get card_detail" = Table.AddColumn(#"Type modified", "card_details", each card_details([data.id])),
    #"Expanded card_details" = Table.ExpandTableColumn(#"Get card_detail", "card_details", {"self", "kind", "id", "title", "style.color", "toolset.name", "toolset.category", "board.self", "board.kind", "board.type", "board.id", "board.name", "description", "startDate", "endDate", "row", "value", "assignees", "checklist"}, {"card_details.self", "card_details.kind", "card_details.id", "card_details.title", "card_details.style.color", "card_details.toolset.name", "card_details.toolset.category", "card_details.board.self", "card_details.board.kind", "card_details.board.type", "card_details.board.id", "card_details.board.name", "card_details.description", "card_details.startDate", "card_details.endDate", "card_details.row", "card_details.value", "card_details.assignees", "card_details.checklist"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded card_details",{{"card_details.row", Int64.Type}})
in
    #"Changed Type"

And the custom function card_details used in the previous code block:

= (ID as text) => let
    Source = Json.Document(Web.Contents(iObeya_platform & "/v1/cards/" & ID, [Headers=[Authorization="Bearer " & #"JWT Token"]])),
    #"Convert in table" = Table.FromRecords({Source}),
    #"style developped" = Table.ExpandRecordColumn(#"Convert in table", "style", {"color"}, {"style.color"}),
    #"toolset developped" = Table.ExpandRecordColumn(#"style developped", "toolset", {"name", "category"}, {"toolset.name", "toolset.category"}),
    #"board developped" = Table.ExpandRecordColumn(#"toolset developped", "board", {"self", "kind", "type", "id", "name"}, {"board.self", "board.kind", "board.type", "board.id", "board.name"}),
    #"Type modified" = Table.TransformColumnTypes(#"board developped",{{"self", type text}, {"kind", type text}, {"id", type text}, {"title", type text}, {"style.color", type text}, {"toolset.name", type text}, {"toolset.category", type text}, {"board.self", type text}, {"board.kind", type text}, {"board.type", type text}, {"board.id", type text}, {"board.name", type text}, {"description", type text}, {"startDate", type date}, {"endDate", type date}, {"row", Int64.Type}, {"value", type text}, {"assignees", type any}, {"checklist", type any}})
in
    #"Type developped"

Then, by using:

Activity cards as Tasks
Rows of the planning as Projects

And a board like this one:

We are able to build such dashboard that can be refreshed in few seconds based on the content of the corresponding iObeya board.

Type of integration

Facade API