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.
