topic Re: Hilti API in Developer

Hilti API

Jimmy801 — Mon, 28 Aug 2023 07:57:09 GMT

Dear members

I'm looking for help for getting a token for the Hilti-API ON!Track. On the homepage of hilti, there is the specification for postman, but there is no way I'm able to write the M-code properly. Here you can find the specifciation: https://unite.ontrack3.hilti.com/developer/guides/confedit . I have ClientID, ClientSecret, Username and Password. Can somebody help me?

Many thanks

Jimmy

Re: Hilti API

Martin_D — Tue, 29 Aug 2023 18:38:31 GMT

Hi @Jimmy801 ,

Unfortunatelly, Power BI does not support the OAuth Client Credential authentication out of the box.

If you only want to get data into Power BI Desktop (not refresh in the service), then this article is a good starting point how to implement this in Power Query, but it might not work with scheduled refresh: https://pbi-guy.com/2023/03/25/connect-with-power-bi-to-the-power-bi-rest-api-with-a-service-principal/

If you want a scheduled refresh in the Power BI service, then you can implement a custom connector (might be worth sharing the idea of providing one with Hilti). This might be a good starting point https://jussiroine.com/2019/02/building-a-custom-connector-for-power-bi-that-supports-oauth2-to-visualize-my-wellness-data/ or the Microsoft documentation.

If you want to use scheduled refresh, I would try copying the required data to a database or datalake using Azure Data Factory and loading it from there into Power BI because Azure Data Factory supports OAuth Client Credential login out of the box. But it's a bit special that the Hilti API requires you to provide ClientID and ClientSecret and Username and Password in one token request and this is not supported in Azure Data Factory. So either it works with ClientID and ClientSecret only, or you need to implement the custom connector, or any custom script (PowerQuery, Python, C#, ...) that gets your data from the Hilti API to an intermediate storage of your choice (datalake, database, SharePoint, ...).

Martin

Re: Hilti API

Martin_D — Tue, 29 Aug 2023 18:42:26 GMT

At least the Hilti API is definitely a good test case for how universal an ETL tool is.

Re: Hilti API

Jimmy801 — Mon, 04 Sep 2023 06:34:41 GMT

Hello

i was now able to make Power Query work to access the Hilti App. I will post the code used here, if somebody has the same problem

let client_id = "Client_ID", client_secret = "Client_Secret", token_uri = "https://cloudsso.hilti.com/hc/token", username = "UserName", password = "Password", scope = "HC.Request.AllScopes", requestPayload = [ username = username, password = password, grant_type = "password", scope = scope ], requestHeaders = [ #"Content-Type" = "application/x-www-form-urlencoded", #"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(client_id & ":" & client_secret), BinaryEncoding.Base64) ], tokenResponse = Json.Document( Web.Contents( token_uri, [ Content = Text.ToBinary(Uri.BuildQueryString(requestPayload)), Headers = requestHeaders, ManualStatusHandling = {400, 401, 402, 403, 404, 405, 406, 500} ] ) ) in tokenResponse

Re: Hilti API

Martin_D — Mon, 04 Sep 2023 09:52:41 GMT

Hi @Jimmy801 ,

Great solution! Did you try whether it works with scheduled refresh in the service, and did it work there as well? The concern was that your query returns a token, dynamically, i.e. potentially a new one with every refresh, and then the actual data query uses this token, which might be considered an insecure dynamic query by the Power BI service.

Martin

Re: Hilti API

Jimmy801 — Tue, 05 Sep 2023 05:33:12 GMT

Hello

I just needed it to connect Excel to this API - no scheduled refresh. But as you wrote, it might not be allowed in service. What would be needed to fix that? How is the process to establish a better query?
BR Jimmy

Re: Hilti API

Martin_D — Tue, 05 Sep 2023 07:00:27 GMT

For Excel the solution is totally fine. For Power BI scheduled refresh, the next step would either be to implement a custom connector or to put the API access into an external script that puts the data in e.g. a datalake for Power BI to fetch it from there.