Processing events in real time/near real time

[danuw]

Hi, I am trying to understand how to process incoming data from the event stream.

Take the example of geofencing... I have been thinking of the following possible routes:

• One is using KQL: I have a query that applies geofencing (as a zone name in a new column) to the data but I do not know how to get it to work. The 2 methods I tried had an issue (basically as either a table or materialised view - shouldn't either of those work?)
• Other methods I could think of would be:
  • through a data factory pipeline? I am yet to find out how this works, but would I apply a notebook process to each update/batch of updates ?
  • even stream going to a Lakehouse and creating delta files? also not sure how to make it work - what would the delay be here and would that be configurable?

    @videlalvaro

    Thanks all

    cc @pdecarlo

[edchuy]

I don't know if this will be helpful for you, but I noticed there's a preview feature that can be enabled in the Power Service that allows users to create real-time dashboards. The Synapse Real-Time Analytics says about this: "

Real-Time Dashboard (Preview)

Estimated release timeline: Q1 2024

A dashboard is a collection of tiles, optionally organized in pages, where each tile has an underlying query and a visual representation. Natively export Kusto Query Language (KQL) queries to a dashboard as visuals and later modify their underlying queries and visual formatting as needed. In addition to ease of data exploration, this fully integrated dashboard experience provides improved query and visualization performance with light, flexible modeling on high granularity low latency data."

[danuw]

---

@edchuy wrote:

[...] I noticed there's a preview feature that can be enabled in the Power Service that allows users to create real-time dashboards. [...]

I was wondering where that option is please? (I am assuming that gives the ability to create dashboard tiles like in Azure Data Explorer)

[Anonymous]

you can find the setting in the Fabric Admin portal. (need service admin or global admin rights)

This is a brand new feature that has been rolled out in the last week. possible that not all tenants have received this setting yet, but I think the rollout is complete.

[danuw]

That sounds great thank you. I will give it a go later

[edchuy]

I'm not sure if this is what you're looking for: What is Real-Time Analytics in Fabric?

Also, in Power BI there's this which seems very limited when I tried it a while back: Real-time streaming in Power BI 

[Anonymous]

without knowing what you actually are trying to do, and not knowing what exactly did not work, helping you is going to be guesswork.

You can stream real time data to a KQL or Reflex via Event Streams. You can also stream it to a lakehouse, but in the past I found that approach to be buggy. Maybe they fixed it by now. 

You can query KQL and also create calculated columns in KQL. you can also query a KQL in a pbi report and add measures. so lots of things you can then do with that real time data. 

[danuw]

Any references for the calculated columns?

I tried to create a materialized view based on a query that would produce the results I am after. When running the create query, I get an operation id but the materialised view is not created and no errors shown. Is that not meant to be what this is for?

Re calculated columns, I tried to start with new 'based-on' table but that would not let me add a column to the new table. What is the guidance on creating a new table based on another one (i.e. at least the same columns and data but will calculated columns) please?

[Anonymous]

Hi. you should be able to add calculated columns in your KQL query on the fly using the extend operator. 

extend operator - Azure Data Explorer & Real-Time Analytics | Microsoft Learn

The trick is not to try to create a new KQL table, but rather to pack all you need into the KQL query und use that on the fly.

[danuw]

Another related question I have (though do I need in a separate thread?) is if I have data coming in to say that either something has started or stopped. How do I make the link when the stop event come in to work out the duration between start and stop knowing the duration could be a few hours?

e.g. a car enter a parking garage at 10 am, I send an event, and the car leaves at 6pm, I send an event it left... (assuming this cannot be done locally such as for example being able to send the start time when I send the end time).

All I can think of right now is a scheduled job to query for any given stop event, find the crresponding start and create a new entry, but in that case, how do I make sure I have not duplicated or missed event (e.g., I say send me all events that arrived in the last 60 seconds, and there is a gap or overlap between 2 consecutive runs, or a scheduled run fails/gets skipped) please?

Thanks again

[Anonymous]

That's where the power of windows functions in KQL come in. the row_window_session is a great way to find all events 

The following will try to figure out the length of a session for each device/location, as long as it's not longer than 24h and events come in no later than every 5 minutes. 

datatable (ID:string, Timestamp:datetime) [
// ...
]
| sort by ID asc, Timestamp asc
| extend SessionStarted = row_window_session(Timestamp, 24h, 5m, ID != prev(ID))

row_window_session() - Azure Data Explorer & Real-Time Analytics | Microsoft Learn

you could also just use the prev() function to find the previous event for that car and figure out some logic. 

prev() - Azure Data Explorer & Real-Time Analytics | Microsoft Learn

or you combine the two. the window function to make sure not to count multiple events in a short time double and the prev to then compare different long time events. 

[danuw]

Thank you very much. I the training it was talking about these windows without actual links/reference to how to do it. That is very helpful and definitly seems fitted to what I was after (also good to know about the max 1 day period for the window which is relevant)

[Anonymous]

the max 1 day is just something I packed into the example. you could extend that window to your needs.

[danuw]

Thank you

[danuw]

Realise why it would have been a good idea to create a different thread for this to accept as solution to that other query