question

Roushan K. avatar image
Roushan K. asked ·

DT and PowerBI integration

Hi-

Has anyone integrated DT with PowerBI for environment api reports?

I want to use PowerBI for continuous reporting. Means Timeseries and Problem API chart should be feeding continuously to the PowerBI and we could easily customize the time frame for all metrics available.

reportstechnologiespaas
10 |2000000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

Gil G. avatar image
Gil G. answered ·

Hi @Roushan K.,

It's a bit complicated to explain all in writing but I'll try to summarize the steps though.

Before hand, note there is a performance clinic I relied upon as far as I recall at https://www.youtube.com/watch?v=LpH8IlUeBSU

Now for the steps:

1. in your powerBI report define a web data source with the url for the API you need (for each query you need a different data source on the report).

In this data source you can also use parameters such as host name and token so that the report may be reusable for other tenants if you need it.

This example gets a list of the hosts in the environment.

DON'T FORGET to add the http headers required such as the "Authorization" for the token and the "Accept" to specify you expect json data.

2. Once the data source is defined, because it is in JSON format, you need to convert it to a tabular format. In the example above it's simple, but it could be that you'll need to traverse through the JSON result first until you get to the array part that includes the result. Once there, "Convert To Table"

The above shows the results of the initial data source results. Use the "To Table" button to convert it to a table. Once pressed, you'll that the yellow header now shows Column1 instead of List.

3. After your data is in a "table format" you need to create the columns to refer to in your reports. Just use the Add Column and refer to the field name you need. Codewise in PowerBI it is (for example):

= Table.AddColumn(#"Converted to Table", "Host Name", each Record.Field([Column1],"displayName"))

Repeat these steps as necessary. If you go to advanced query editor you may end up with the following query code:

let

Source = Json.Document(Web.Contents("https://" & HostName & ".live.dynatrace.com/api/v1/entity/infrastructure/hosts?showMonitoringCandidates=false&includeDetails=true";, [Headers=[Accept="application/json", Authorization="Api-Token XXXXXX-XXXXX-XXXXX"]])),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Added Custom" = Table.AddColumn(#"Converted to Table", "Host Name", each Record.Field([Column1],"displayName")),

#"Added Custom1" = Table.AddColumn(#"Added Custom", "OsType", each Record.Field([Column1],"osType")),

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "OsVersion", each Record.Field([Column1],"osVersion")),

#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Host Group", each Record.Field(Record.Field([Column1],"hostGroup"),"name"))

in

#"Added Custom3"

4. Once you apply and save the query you are ready to build your report. Just choose your visualizations and drg the columns from the right part to the visualizations.


For example:

or

In the last example you can see on the right many queries used for the reports and green/black columns in charts represent time periods comparisons.


Hope this helps.

gil.


1587793023625.png (21.1 KiB)
1587793435335.png (73.2 KiB)
1587793945817.png (141.9 KiB)
1587794056482.png (206.8 KiB)
1 comment Share
10 |2000000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

Thanks a lot Gil for taking out time and writing this. I will try it and will let you know.

2 Likes 2 · ·
Roushan K. avatar image
Roushan K. answered ·

@Gil G Thanks for your help

it was very useful and worked for me.

Share
10 |2000000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

Yos N. avatar image
Yos N. answered ·

Hi @Roushan K.

Yes @Gil G. integrate them together

Yos


1 comment Share
10 |2000000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

can you please help me the way out.

0 Likes 0 · ·