CODEWITHSUNDEEP
azurepowerbipowerbi-desktopazure-stream-analytics
Emiko Hourston
Emiko Hourston

Reputation: 13

Azure stream analytics - can you query for most recent non null values?

I have an Azure stream analytics job with input data from an IoT Hub and I'm sending this data to Power BI. The data is being updated using pub/sub, so only one value gets updated per message. For this reason, my input data looks something like this (MessageID 5 is the most recent message):

MessageID RPM Temperature Pressure
5 800 null null
4 null 50 null
3 null null 4
2 null 23 null
1 900 null null

I would like to show the latest non-null value on a power bi dashboard and on a report pinned live to a dashboard. With this example, I'd like a card or gauge to display the most recent not null value for each variable, so

RPM = 800,
Temperature = 50, and
Pressure = 4.

By default, the Power BI dashboard reads the most recent value, so if the value was not just updated (and was null in the table), the dashboard displays (Blank) as the value.

Is there a SQL query to write from my Azure Stream Analytics job to get the most recent non-null value of each variable? Or some other workaround?

Thanks

Upvotes: 0

Views: 693

Answers (2)

Florian Eiden
Florian Eiden

Reputation: 842

One of the benefits of ASA is that you can group events on a time window:

Illustration of a tumbling window

Here, if I understand correctly, when you look at event one at time, you only get 1 data point. The way to solve this is to look at a window of time instead, and project all the data points in a single output event.

Let's say you should receive 1 data point each every 10 seconds. You can then write a query that will GROUP BY all events on that time window, and output a result with all 4:


WITH dataPrep AS (
SELECT
    CAST(GetMetadataPropertyValue(IoTHub, 'IoTHub.EnqueuedTime') AS datetime) AS eventDateTime,
    GetMetadataPropertyValue(IoTHub, 'IoTHub.ConnectionDeviceId')  AS DeviceId,
    *
FROM IoTHub
)

SELECT 
    System.Timestamp() AS DateTime,
    DeviceId,
    LAST(RPM) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN RPM IS NOT NULL),
    LAST(FuelRate) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN FuelRate IS NOT NULL),
    LAST(DischargePressure) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN DischargePressureIS NOT NULL),  
    LAST(SystemPressure) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN SystemPressure IS NOT NULL)   

INTO
    powerbioutput
FROM
    dataPrep TIMESTAMP BY eventDateTime
GROUP BY
    DeviceId,
    TumblingWindow(second,10)
WHERE
    DeviceId = 'rpi1'

Here I used LAST, the syntax may look a bit complicated but it's straightforward. Since it requires PARTITION BY, I had to make it explicit, which is a good practice anyway if you ever want to scale in the future.

You can make it simpler by using MAX, MIN or AVG, depending on your requirements.

You can look here for other ASA query patterns.

Upvotes: 2

Neeraj
Neeraj

Reputation: 168

In this case you can use the Coalesce. It evaluates the arguments in order and returns the value of the first expression that initially does not evaluate to NULL.

Refer to the link below-

https://learn.microsoft.com/en-us/stream-analytics-query/coalesce-azure-stream-analytics

Upvotes: 0

Related Questions