Synapse Realtime analytics

[kjellvs]

Dear,

I am working on a use case where I am using both tables from a lakehouse and from a KQL database. The data from the KQL database is injected in real-time, and also visualized as such. The data from lakehouse is updated on daily basis, and visualizations are thus less fluent.

Now, I want to combine both data in a single report; however I cannot find a way to procceed. I have tried the following options:

1) add shortcut to KQL dataset in Lakehouse (can only add as file)

2) try adding additional dataset from powerbi report (not possible in service, in desktop, this changes the query mode).

3) Edit -> we have also tried to download the report, then change the report by creating a copy of the model, adding the new KQL dataset. Publishing this report then introduces a new dataset in the workspace (which is the custom one based on the lakehouse and kql database). When my colleague then wants to make changes, they can download the file, and have to create an additional "local model", when overwritin this in the workspace, the file and connecitons then are broken. This "development" flow thus also doesn't work. 

How should this normally be done? It would be perfect to have both these datasets in a single report, preferably under live mode such that they can be easily maintained by different users.

Hope you can help as I failed to extract this from documentation.

Kind regards,
Kjell

[v-cboorla-msft]

Hi @kjellvs 

Glad that your query got resolved.
Please continue using Fabric Community for any help regarding your queries.

[v-cboorla-msft]

Hi @kjellvs 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help.

[DennesTorres]

Hi,

Your option 2, change the query mode. I understand you connect to one of the sources, then to connecto to the other you are required to add a local model, which changes the query mode. Is this a big problem?

Would you like to talk a bit more about architecture as well? You are talking about real time. In real time we usually have a hot and cold path. The hot path (which would be the KQL) would show the information exactly how it is at the moment, but it's a data intended to be discarded.

The cold path should have exactly the same data, intended to build historical reports. This is called lambda architecture, it's one of the possible architectures for that.

Are you using the lamdba architecture? Should you?

Kind Regards,

Dennes

[kjellvs]

Hi Dennes,

In terms of architecture:

In our use case, we would like to show the data coming from events streams ASAP. Its a bit of a combination of both, we are receiving events per item, and would like to store the last event per item, historically. I.e., could be a year a go, or a second ago. Storing all data historically wouldn't be a problem though.
Main importance is to display the latest event ASAP, would you recommend us against using KQL then?

In terms of adding a local model -> this is fine, but we haven't found a proper development/collaboration approach on adjusting/changing this local model afterwards, as overwriting existing models in the fabric environment resulted in unknown states where the models/datasets are no longer visualizable.

Which solution fits our described requirements above best?

kind regards,

Kjell

[DennesTorres]

Hi,

I'm not recommending against KQL, in the opposite. KQL is great for real time.

But EventStream is capable to have multiple destinations. At the same time it sends the data to KQL, it sends the data to lakehouse as well. You can use KQL for real time reporting, and use lakehouse for historical reporting without having to cross the data from one to another.

The real time data you discard, it's only for the moment. The lakehouse data you keep for historical purposes according your needs.

In this way, you don't need to have cross access between KQL and Lakehouse. Or at least will minimize it.

Kind Regards,

Dennes

[kjellvs]

Hi Dennes,

Thanks again for helping me think the architecture through. I agree on having all historical data in a lakehouse, realtime data in a KQL dataset. In terms of storage, they indeed shouldn't cross.

However, when reporting, we still would like to have both realtime and historical data in a single dashboard as mentioned in my initial question. As agreed before, requiring a local model is OK.

However, to our experience, this currently significantly degrades the development flow in the following way: If fellow colleagues download a powerbi report from PBI service (in case they would like to add measures etc.), they are prompted to create an additional "local model" (makes sense, dataset is changing). However, this "second" local model creates an unusable dataset when uploading it back to service. (i.e., visuals cannot be displayed, dataset is not accessible, ...).

So the questions still remains: how do we develop a powerbi report (incl. local model) with different developers (not simultaniously), in desktop (for the additional functionality)? 

As I mentioned, simply downloading and overwriting when uploading runs into issues.

I hope we can answer this question to speed up our development process.

Kind regards,
Kjell

[DennesTorres · Accepted solution]

Hi,

If your problem is about sharing the project with other developers, you can use the format PBIP and use a source control repository. The dataset and reports are supported on this format and this would solve your problem.

Kind Regards,

Dennes