Different aggregation by Sensor Type for ASA

Question

I have a device with many sensors which need differing types of aggregation and my question is in two parts. The device communicates through Azure IoT Hub which then goes to Azure Stream Analytics on to a SQL DB and Power BI.

1) What is the best way to transmit the data? A column for each sensor (sensor1, sensor2,.) and DateTime or Columns for DeviceId, DateTime, SensorNumber and SensorValue? Further information such as Sensor Name, trigger value, etc. is added through a reference table. What are the advantages or disadvantages of these approaches?

2) Some aggregations required in ASA are MAX and others are AVERAGE which change depending on the Sensor Type that is linked to each channel of the device through a reference table. For example a sensor type of "Switch" needs the MAX aggregation while sensor type "Temp" requires AVERAGE aggregation. Can you change the aggregation type from the one input (the IoTHub) and to the one output (SQL) depending on a different SensorType field linked through a ref table?

Any help would be appreciated.

Answer 1

1. It is better to use SensorId, SensorValue because you might not have values from all the sensors all the time. Also, the payload doesn't change when the you have a new sensorId.
2. It is possible to do it with reference data. However, if it was just a different aggregate, you can also compute both averages and max all the time and pick the appropriate one based on sensor type on SQL side or power bi side.

If it is more complex than just the type of aggregate, reference data is better. Here is how you can do it with reference data

create table iotInput
(
    SensorId nvarchar(max),
    SensorValue bigint,
    Measurementtime datetime
)

create table refData
(
    SensorId nvarchar(max),
    IsMaxAggregate bigint
)

select
    System.Timestamp [Aggregationtime],
    iotInput.SensorId,
    refData.IsMaxAggregate,
    case when refData.IsMaxAggregate = 1 
        then max(iotInput.SensorValue) 
     else 
        avg(iotInput.SensorValue) end [Aggregate]
from
    iotInput timestamp by [MeasurementTime]
join
    refData
on
    iotInput.SensorId = refData.SensorId
group by
    iotInput.SensorId,
    refData.IsMaxAggregate,
    tumblingwindow(second,5)