Search code examples
azurepowerbiazure-stream-analytics

Azure stream analytics - Last() function always equals timestamp

I'm using the last function to create a flag that identifies the latest record received (so I can dashboard only the latest values in Power BI).

SELECT
    events.deviceId
    ,events.[timestamp]
    ,events.externaltemp
    ,LAST(events.[timestamp]) OVER (PARTITION BY events.DeviceId LIMIT DURATION(day, 1) ) as latest
    ,case when LAST(events.[timestamp]) OVER (PARTITION BY events.DeviceId LIMIT DURATION(day, 1) ) = [timestamp] then 1 else 0 end as LastestFlag
    INTO
[powerBI]
     FROM
    [EventHub] as events timestamp by [timestamp]

However I am seeing that the last function is always returning the same datetime as my timestamp, so all rows in my query look like they are the last one that was received. My data has a record for every second.

(alternatively, I was hoping to use the row_number function to give me something to filter on to identify the latest record - but this doesn't appear to exist in stream analytics yet)

Solution

  • The best way to accomplish this at the moment is inside Power BI with a Q&A question as mentioned in this similar question: Latest value in PowerBi from ASA

    Your approach won't work. If you ran that query against SQL Server it would flag one row per device and call it the latest since SQL Server is more batch oriented. Stream Analytics is temporal and operates in real time. So the row it is working on is always the latest row at that moment. Plus, once an output row is sent to Power BI it cannot be updated. So every row in Power BI will be flagged as the latest since at the time it was processed by Stream Analytics it was the latest.