Is it possible to create caches?

[PowerWhy]

Hello,

Please could you tell me if it's possible to create caches of all the possible permutations of slicer selections? i.e. some of my visualisations run very slowly so if we could save caches for each selection permutation then the report could run almost instantly for the user.

Thank you,

CW

[AntrikshSharma]

@PowerWhy Power BI/CPU can only cache the results of Storage Engine requests also it has a limit of caching first 513 Storage Engine queries. after that point it will not consider the cache and re execute everything, so if there are 514 requests then nothing will be cached.

I would suggest you start DAX Studio and understand why the queries are slow.

[Burningsuit · Accepted solution]

Hi @PowerWhy 

From this and your other community question, it seems almost certain you are using a direct query connection to an SQL or similar data source. Using Import Mode creates a Datamodel which is exactly what byou;re asking for a chache which then allows Slicer selection to be very quick.

If you use direct query connection, every Visual on the canvas generates an SQL query to popoulate it on load. Then when you change a slicer, every visual genraerates another sql query to re-populate the visual in light of the slicer. If your SQL datadase server does not respond quickly to the many SQL queries generated by Power BI then refreshing visuals will be slow.

This "Guy in a Cube" gives more information abour Direct Query Vs Import mode.

(126) Power BI Get Data: Import vs. DirectQuery vs. Live (2021) - YouTube

Hope this helps

Stuart