Currently i'm in the process of developing a near real time power bi report that will query one of our critical sql servers to return data. The business requirement is a mix blend of providing both real time and cached based data. The report will be targeted to multiple users accessible concurrently where the source information will be updated very frequently. What would be the best solution to create this report e.g. Direct Query / API / Dataflow ? This report will need to be updated real time or within a maximum of 5 mins. Any other suggestions to tackle the real time but offloading source system query load ?
For this we have recently tried the following
1 . We have created a Direct Query Report - this works as expected but we are worried about the strain this is going to put on the server e.g. multiple queries being executed for each page load / user interaction, we have noticed while testing there are around 10 queries executed for each interaction this would be fine for a few users but not concurrent users
2 . We have also created a api program that will stream data to a streaming dataset, but this is very limited in the visuals that can be displayed e.g only dashboard visuals
3 . We have also created a dataflow to import the data into power bi service but there are limitations with data refreshed e.g. only every 30 mins or only 48 refreshed (in premium) via api refresh
The expected results for this is to design a report solution that will update real time without impacting the performance of our critical database servers
