cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to Connect PowerBI to HopexGraphQL?

imran_khatyan
MEGA Partner
MEGA Partner

I recently tried to connect Microsoft Power BI to HopexGraphQL, I am putting my experience here that it may benefit others, having said that there may be a better way to do this which I am not aware of so feel free to drop in your comments or questions: 

 

Why Power BI? For now it's my favorite analytical tool

Step 1: Create a GetToken Power Query to connect to UAS

Code for the query: 

let
base_url="http://localhost/UAS",
extension = "/connect/token",
url = base_url &extension,
content1= "grant_type=password&scope=hopex%20openid%20read%20write%20offline_access&client_id=HopexAPI&client_secret=secret&username={GraphQL Username}&password={password}&environmentId=M6LC(Gp3VzSP",
webdata1 = Web.Contents(url, [Headers=[
#"Content-Type" = "application/x-www-form-urlencoded"],Content = Text.ToBinary(content1)]),
response1 = Json.Document(webdata1),
#"Converted to Table" = Record.ToTable(response1),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"access_token", type text}, {"expires_in", Int64.Type}, {"token_type", type text}, {"refresh_token", type text}})
in
#"Changed Type"

 

Step 2: Turn the above Query to function such as GetToken

 

Step 3: Use the above function to retrieve token and connect to your favorite module in GraphQL for me it's ITPM

 

let
token = GetToken(),
#"tbl" = token,
gottoken = #"tbl"[access_token]{0},
Source = Web.Contents(
"http://localhost/HOPEXGraphQL/api/ITPM",
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Bearer " & gottoken,
#"X-Hopex-Context" = "{""EnvironmentId"":""M6LC(Gp3VzSP"",""RepositoryId"":""ihd7)1IVRXR9"",""ProfileId"":""757wuc(SGjpJ""}"
],
// Notice the quote escaping here
Content=Text.ToBinary("{""query"":""query{ application{ id name applicationType businessProcess{ id name} applicationOwner_PersonSystem{ id name } sentApplicationFlow_ApplicationParticipant{ id name } receivedApplicationFlow_ApplicationParticipant{ id name }} }""}")
]
),
#"JSON" = Json.Document(Source),
data = JSON[data],
application = data[application],
#"Converted to Table" = Table.FromList(application, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "applicationType", "businessProcess", "applicationOwner_PersonSystem", "sentApplicationFlow_ApplicationParticipant", "receivedApplicationFlow_ApplicationParticipant"}, {"Column1.id", "Column1.name", "Column1.applicationType", "Column1.businessProcess", "Column1.applicationOwner_PersonSystem", "Column1.sentApplicationFlow_ApplicationParticipant", "Column1.receivedApplicationFlow_ApplicationParticipant"}),
#"Expanded Column1.businessProcess" = Table.ExpandListColumn(#"Expanded Column1", "Column1.businessProcess"),
#"Expanded Column1.businessProcess1" = Table.ExpandRecordColumn(#"Expanded Column1.businessProcess", "Column1.businessProcess", {"id", "name"}, {"Column1.businessProcess.id", "Column1.businessProcess.name"}),
#"Expanded Column1.applicationOwner_PersonSystem" = Table.ExpandListColumn(#"Expanded Column1.businessProcess1", "Column1.applicationOwner_PersonSystem"),
#"Expanded Column1.applicationOwner_PersonSystem1" = Table.ExpandRecordColumn(#"Expanded Column1.applicationOwner_PersonSystem", "Column1.applicationOwner_PersonSystem", {"id", "name"}, {"Column1.applicationOwner_PersonSystem.id", "Column1.applicationOwner_PersonSystem.name"}),
#"Expanded Column1.sentApplicationFlow_ApplicationParticipant" = Table.ExpandListColumn(#"Expanded Column1.applicationOwner_PersonSystem1", "Column1.sentApplicationFlow_ApplicationParticipant"),
#"Expanded Column1.sentApplicationFlow_ApplicationParticipant1" = Table.ExpandRecordColumn(#"Expanded Column1.sentApplicationFlow_ApplicationParticipant", "Column1.sentApplicationFlow_ApplicationParticipant", {"id", "name"}, {"Column1.sentApplicationFlow_ApplicationParticipant.id", "Column1.sentApplicationFlow_ApplicationParticipant.name"}),
#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant" = Table.ExpandListColumn(#"Expanded Column1.sentApplicationFlow_ApplicationParticipant1", "Column1.receivedApplicationFlow_ApplicationParticipant"),
#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant1" = Table.ExpandRecordColumn(#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant", "Column1.receivedApplicationFlow_ApplicationParticipant", {"id", "name"}, {"Column1.receivedApplicationFlow_ApplicationParticipant.id", "Column1.receivedApplicationFlow_ApplicationParticipant.name"})
in
#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant1"

 

Step4: Turn the last repeated code lines into a function as not to repeat in every query

 

Step5: Remove or Add any unnecessary columns, and create visualizations to your hearts content, or run more queries and create relationships between multiple mega objects for dynamic and real time analysis.  

 

4 Replies

In V5 this is different.

 

You don't need to de first a call to get a Bearer.

 

First you need to create an API Key in the HAS Console.

 

oguimard_0-1671789443489.png

 

 

Then you can directly use this API Key in the call.

 

oguimard_1-1671789487892.png

 

Read the updated postman libraries  here  https://community.mega.com/t5/REST-API/bd-p/api 

 

 

 

marjoqako
MEGA Partner
MEGA Partner

Hello Imran,

Thank you for sharing it! I tried to use it and unfortunately it didnt work. I am currently testing it in Hopex V5 and I get as a response (400) Bad Request in PowerBI. Do you still use it in Hopex V5? Authentication with bearer token is since Hopex V5 outdated, is it right? Does this mean we can try another authentication method and try to connect GraphQL and PowerBI through that? And if there is one that you use, what is it?

Thank you for your response!

ssiebenmorgen
Retired

very helpful ! thank you !! 

imran_khatyan
MEGA Partner
MEGA Partner