Clash API with Power BI — how to…
For everyone interested in setting up an iTwin clash monitoring dashboard in Power BI, here’s part 2 where we investigate the process in detail.
This part will cover how we can connect the clash result data to Power BI using the API.
Let’s go: Get data -> from Web ….
Hold on! What about security and authentication? Not everyone can and should freely query against a sensitive project. To ensure data security we will need to cover a few fundamentals first.
Authorization
Some public APIs can be accessed without authorization, but to work with any Bentley API, authorization is always required. This is typically achieved through a short-lived access token, generated out of a client ID and a secure passcode generally referred to as a secret. To learn more about this, have a look at this short tutorial:
https://developer.bentley.com/tutorials/authorize-service/
Client ID and secret
To register application head over to — https://developer.bentley.com/my-apps.
Pick an application name and select validation and administration as API associations. This will preselect the allowed scopes. Some scopes such as user:read or clashdetection:modify would not be required for our dashboard and can be removed. Please refer to the API documentation (links below) to check which scopes are required and ensure to not give the app more permissions than necessary.
When registering the application…the application type we need to create will be a Service type.
Once you click save, you will be given a secret passcode. Note that one down and keep it somewhere save. This service is tied to your user account, and only you should be using it.
###
UPDATE!
This step was previously omitted. To enable the authentication, the clientId must be added as a project team member to your connected project.
Go to https://connect.bentley.com/ and add the clientId as a user, including @apps.imsoidc.bentley.com
This applies to all APIs using a service application such as the users or iModel APIs.
###
Back in Power BI create 3 new parameters for the client ID, the secret and the OIDCScopes. While you’re at it also create a parameter for your project ID.
Creating an authentication token
The authentication token can be created in many applications. I used Postman and Microsoft’s PowerAutomate but today we want to create the token directly in Power BI.
- In Power BI, create a blank query, use the advanced editor, and add:
let
imsoidc_url = "https://ims.bentley.com/connect/token"
in
imsoidc_urlDone — rename the query from Query1 to Token
- Next, create a step after (right-click on the imsoidc_url), call it imsoidc_body and use the advanced editor to highlight imsoidc_url and replace it with:
"grant_type=client_credentials&client_id=" & ClientId & "&client_secret=" & Uri.EscapeDataString(Secret) & "&scope=" & Uri.EscapeDataString(OIDCScopes)
This is where your previously created parameters come into play. Look out for the ClientId, Secret and OIDCScopes. Make sure you match the spelling here or it won’t work.
As a result, you should get a nice long string containing the contents of the parameters.
- This looks a little hacky, but it works perfectly well. Add a new step after imsoidc_body and name it GetJson. Again, use the advanced editor highlight and replace imsoidc_body with:
Json.Document(Web.Contents(imsoidc_url, [Headers=[#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"], Content=Text.ToBinary(imsoidc_body)]))
This will provide the access_token, token_type and the expiration time in seconds.
- Finally, we want to extract only the access_token. All to do here is to insert a new step after, name it AccessToken use the advanced editor and change the 5th line to:
AccessToken = GetJson[access_token]
Just in case you got lost somewhere down the line, here’s the entire query:
let
imsoidc_url = "https://ims.bentley.com/connect/token",
imsoidc_body = "grant_type=client_credentials&client_id=" & ClientId & "&client_secret=" & Uri.EscapeDataString(Secret) & "&scope=" & Uri.EscapeDataString(OIDCScopes),
GetJson = Json.Document(Web.Contents(imsoidc_url, [Headers=[#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"], Content=Text.ToBinary(imsoidc_body)])),
AccessToken = GetJson[access_token]
in
AccessTokenThis will provide a token which can be used for the clash detection API and with the correct OIDC scopes for other available APIs as well. A simple refresh will provide an up-to-date access token.
Get clash detection runs
GET https://api.bentley.com/clashdetection/runs?projectIdA great way to explore the available clash detection API is to use the try it out function. This is an easy way to look at what can be expected later in Power BI. This also helps to understand the requirements for the clash API requests.
Let’s have a look at this:
All which is required is the project ID and the authorization code. The rest is prefilled. You might want to set the Prefer to return=representation to get the complete result, including the count of clashes, dates, usernames and of course the very important result IDs. Set the authorization code but go ahead and feel free to test out the token we generated earlier. Just copy and paste that token and add Bearer in front of it.
The resulting HTTP request can be almost entirely copied across to Power BI.
Using a new web source in Power BI, the connection would look like the below image. As you can see in the URL and authorization fields, I am using the hardcoded token and project ID. It is sometimes easier to start with clear text and ensure it works before working with parameters.
Now we can edit the source with the advanced editor and replace the project ID and the token with the parameters. Using parameters makes this more dynamic as replacing hard-coded data manually would be too onerous. The use of parameters makes it easier to use the dashboard for other projects. Simply replace the project ID and you can roll this out in no time.
After this, it is most likely that there will be a pop-up asking for access to the web content. Always use Anonymous as an access method. The token handles all required authentication.
Latest Filter
As mentioned in the 1st part of this article, I used a filter to display only the latest clash run IDs. This helps to avoid querying an excessive number of rows and makes working with Power BI far more efficient and reduces processing times. Unless you have the need to have all details of all individual clashes, consider applying the filter.
You can either choose to include that filter directly into the query or create a ‘copy’ of the first query itself. To do this, simply create a new blank query and add the formula shown below using your naming of the query.
The time filter itself is created by adding a custom column into the get clash runs-query with a formula like this.
Depending on how your columns have been named, you might need to tweak this a bit afterwards. Additionally, I created a column where I brought the date and time back together.
What’s great here is that every time the data refreshes the filter will always be working. It’s a one-time setup — just like the entire dashboard and requires very little maintenance.
Get clash detection result details
Now that we extracted the clash detection runs, we are going to hoover up the details of individual clash results.
GET https://api.bentley.com/clashdetection/results/{id}The above request in combination with the result IDs which we got from the previous query, provides more detail on each individual clash result. Now, it wouldn't be practical to create a request for every ID separately as this would require a lot of manual effort. Here we must be a bit more creative. There might be other methods to achieve this, but Power BI’s custom functions work great for this purpose.
Result IDs
Create a blank query and pull in the result IDs. Just like what we have done to create our filter. This time we only want to have the [resultId].
Convert this to a table and rename it to clashResultIds. Now that we have a nice clean table, we can start creating that function.
Create a blank query and use the advanced editor.
…this should make it easier to read:
let
Source = (resultId as text) => Json.Document(Web.Contents("https://api.bentley.com/clashdetection/results/" & resultId , [Headers=[Accept="application/vnd.bentley.itwin-platform.v1+json", Authorization= "Bearer " & #"Token" ]]))
in
SourceThis function basically uses the API request and then applies this request automatically to each ID in our table.
You can test the function by simply copying and pasting a single result ID into the text entry box and clicking Invoke.
Invoke custom function
Head back to the query with the latest clash run IDs. In the Add Column tab, you can find the Invoke Custom Function command.
If you start from scratch, there will be a limited choice and you can’t go wrong on the settings.
Power BI will now fetch the data and add a new column. Now it’s time to expand all columns to unveil our data. Remove the tick at Use original column name as prefix. This avoids excessively long column names.
Expand all columns to new rows until there is nothing more to expand. You will have to do this only once. Power BI is smart enough to do this later automatically.
That’s it. Back to build more visualizations!
