Easiest ways to get from Timeseries with REST API in json format file to Power BI graphing

[runatyr]

Hello to all in the DT Community:

I’ve come across a need to export metrics data from the Casandra database.

I have been working with the REST API for purposes of extraction. It returns the data

In a JSON format.

Ultimately this data is going to end up in a POWER BI data set that is part of a long term report/Operational dashboard.

However When I attempt to import the data into power BI, I end up with a table that has only header information. I will attach sample JSON file data and and image of power BI.

I don't want to re-invent the wheel if there is already an easier way to import this into Power BI or reformat the JSON file so that Power BI can read it more readily.

Does anyone know of a means? If I need to manipulate the file format I'm most comfortable with python.

Please suggest any means you think would assist.

Kindest regards,

{

"timeseriesId": "com.dynatrace.builtin:host.nic.bytesreceived",

"displayName": "Network interface bytes received on host",

"dimensions": [

"HOST",

"NETWORK_INTERFACE"

],

"unit": "BytePerSecond (B/s)",

"detailedSource": "Infrastructure",

"types": [],

"dataResult": {

"dataPoints": {

"HOST-ABCD1234, NETWORK_INTERFACE-ABCD1234": [

[

1566323040000,

263687.34375

],

[

1566323100000,

166058.40625

],

[

1566323160000,

172781.6875

],

[

1566323220000,

253953.96875

],

[

1566323280000,

527919

],

[

1566323340000,

688350.1875

],

[

1566323400000,

904895.5

],

[

1566323460000,

267182.9375

],

[

1566323520000,

188694.296875

],

[

1566323580000,

324438.40625

]

]

},

"unit": "BytePerSecond (B/s)",

"resolutionInMillisUTC": 60000,

"aggregationType": "MIN",

"entities": {

"HOST-ABCD1234": "SERVER.com",

"NETWORK_INTERFACE-ABCD1234": "Adapter"

},

"timeseriesId": "com.dynatrace.builtin:host.nic.bytesreceived"

},

"aggregationTypes": [

"AVG",

"SUM",

"MIN",

"MAX"

],

"filter": "BUILTIN"

}

[wolfgang_beer · Accepted solution]

If you switch to our new /metrics API you can directly fetch CSV based metric data directly into PowerBI or even Excel. I guess that dramatically simplifies your import process.

You just have to specify the content type header to contain CSV instead of JSON. See our documentation here:

https://www.dynatrace.com/support/help/extend-dynatrace/dynatrace-api/environment-api/metric/get-all...

Best greetings,

Wolfgang

[skrystosik]

This info about defining header is not present in doc 🙂 At least I don't see it.

[Friederike_Thun]

I think you have to dig a little deeper into "How to import JSON data in PowerBI". This is not a Dynatrace issue but a general JSON topic, I had the same issue with other data sources and PowerBI.

You need to convert the JSON into a table while importing and extend the values where a "link" is present (marked yellow in your screenshot). Multiple guidelines can be found out there. e.g. https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/

The other option is to play with the content type like Wolfgang mentioned.