Azure Stream Analytics Query: Send json array data to PowerBI

Question

I´m trying to send my IoT Hub data to Power BI via Stream Analytics. This works fine as long as there are no arrays included in the json data from the IoT Device.

My questions are: How do I have to modify my Stream Analytics Query so that PowerBI is able to interpret the data as an array/table? How do I TIMESTAMP BY each array element (using "timecreated")?

The json string I send to IoT Hub looks like this:

{"sensordata":[{"name":"Temp_0","value":3,"timecreated":"2016-11-09T11:08:00Z"},
{"name":"Temp_0","value":7,"timecreated":"2016-11-09T11:08:02Z"},
{"name":"Temp_1","value":2,"timecreated":"2016-11-09T11:08:04Z"}]}

What Stream Analytics receives from IoT Hub:

[{"sensordata":[{"name":"Temp_0","value":3,"timecreated":"2016-11-09T11:08:00.0000000Z"},
{"name":"Temp_0","value":7,"timecreated":"2016-11-09T11:08:02.0000000Z"},
{"name":"Temp_1","value":2,"timecreated":"2016-11-09T11:08:04.0000000Z"}],    
"EventProcessedUtcTime":"2016-11-09T10:08:57.9325156Z","PartitionId":0,
 "EventEnqueuedUtcTime":"2016-11-09T10:08:47.8050000Z","IoTHub":
{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"toCloudDevice",
"ConnectionDeviceGenerationId":"607350268321425367",
"EnqueuedTime":"0001-01-01T00:00:00.0000000","StreamId":null}}]

My Stream Analytics Query looks like this:

SELECT sensordata.ArrayValue AS data,
  COUNT(*)
INTO
  [transmit-data]
FROM
  [receive-data] AS e
CROSS APPLY GetArrayElements(e.sensordata) AS sensordata
GROUP BY
  sensordata,
  tumblingWindow(Second, 10)

What IoT Hub sends to Power BI:

[{"data":{"name":"Temp_1","value":2,"timecreated":"2016-11-09T11:08:04.0000000Z"},
"count":1},{"data":{"name":"Temp_0","value":7,"timecreated":"2016-11-09T11:08:02.0000000Z"},
"count":1},{"data":{"name":"Temp_0","value":3,"timecreated":"2016-11-09T11:08:00.0000000Z"},
"count":1}]

PowerBI is unable to interpret this data in a sensible way.

I would like to be able to generate graphs, for example having the time on the x-axis and having the values for Temp_0 on the y-axis.

Any ideas on the subject? Your help would be greatly appreciated.

(btw: json strings from IoT device have varying number of array elements!)

Konstantin Zoryn · Accepted Answer · 2016-11-09 19:28:08Z

1

Power BI does not allow complex object like arrays or records. The problem in your case is that "data" field is a record.

Please try the following query instead:

SELECT 
    sensordata.ArrayValue.data.Name,
    sensordata.ArrayValue.data.Value,
    sensordata.ArrayValue.data.Timecreated,
    COUNT(*)
INTO
  [transmit-data]
FROM
  [receive-data] AS e
CROSS APPLY GetArrayElements(e.sensordata) AS sensordata
GROUP BY
  sensordata,
  tumblingWindow(Second, 10)

You can not apply TIMESTAMP BY on individual array elements today unfortunately. There is a limitation that single event can only have one timestamp. However, you can split your job into two, where first job does CROSS APPLY and inserts events into intermediate event hub and the second job does TIMESTAMP BY and aggregation

answered Nov 9, 2016 at 19:28

Konstantin Zoryn

6214 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy M. Over a year ago

This is super useful, thx! (I had to leave out the '.data' in the SELECT statement though..) PowerBI is now able to digest the data and generate graphs. I can´t separate "Temp_0" and "Temp_1" yet and will try to follow your idea to use an intermediate event hub.

Andy M. Over a year ago

(An easy way to separete "Temp_0" and "Temp_1" in a PowerBI Services graph is to use a filter on "name".)

Collectives™ on Stack Overflow

Azure Stream Analytics Query: Send json array data to PowerBI

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related