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
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
2 Comments
- 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.
- Status added:New
