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!)

Answer 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