Skip to main content

Power BI

New

Support Direct Query SQL Result cache

Vote (7) Share
Mimoune Djouallah's profile image

Mimoune Djouallah on 16 Aug 2021 00:05:51

with the introduction of M Parameter, a new class of use cases became Possible in PowerBI, Currently it is possible to send an M Parameter and get back some heavy calculation done by the Source Database for Example



GIS calculation, (Clustering, Area size, length etc).

ML calculation.

but there is a General performance Problem, PowerBI as far as I can see don't cache any SQL Query results, which make the whole experience less than Optimum, let's say the First Query will take 10 second, I expect if the user run again the same Query he will get instantaneous cache result.


Another expectation if the user clicks refresh in the report, I expect the result cache to expire.

I appreciate the Product team is investing a lot in automatic aggregation, but I am afraid, it does not help when using M Parameter.

Here is an example using M Parameter, I can see in my Database Console, a hundred of the same SQL Queries sent again and again by PowerBI, Import does not work as I can not import all the possible combination of results

https://app.powerbi.com/view?r=eyJrIjoiZjA3ZGQ5NjctZGQxNS00NmMxLTgwZDktY2RlMjM1ZDRkNzJjIiwidCI6IjdmNjhjN2QwLTc0ZTgtNGE2Ni04NGI3LWZhMjdlMzYyNTFiOCJ9

 

Comments (1)
Mimoune Djouallah's profile image Profile Picture

on 09 Dec 2021 21:31:43

RE: Support Direct Query SQL Result cache

Currently, the Power BI service may temporarily cache result sets from queries in DirectQuery mode. This behavior is not user-controlled, and may lead to inconsistent results when the underlying data is changing rapidly. It would be desirable to make the caching behavior user-configurable on a per-dataset basis to provide the benefits of caching when they are acceptable and provide greater consistency when it is required.

Merged Idea (2)