Alex Whittles on 05 Apr 2018 18:43:02
There needs to be more control over the automated cache refresh for Direct Query connections, specifically to SSAS.
At present Power BI reruns all recent queries (for each individual user, when there's row level security) as soon as it detects any processing on the cube.
If a cube is processed in separate batches then this adds an incredible load on the SSAS box when we're trying to continue processing.
Additional options are required including:
- Disable auto cache refreshing
- Disable auto cache refreshing between certain times
- Only auto refresh the cache when measure groups are processed, not dimensions (on multidimensional)
- Comments (5)
RE: More control of SSAS direct query cache refresh
This is a big problem, as the cache refresh in our case can use a lot of CPU. Another issue is that it something causes the dataset to revert back to an hourly refresh. I thought it was when a user publishs a report but i tested and thats not the case so not sure what triggers it yet. This is a big issue as it is causing our servers to max out cpu.
RE: More control of SSAS direct query cache refresh
This is really missing feature when it comes to enterprise-grade architecture. We have plenty of Direct Query reports made by users which by default have caching feature turned on as it can't be turned off. After every refresh of the tabular cube, PowerBI triggers enormous number of cache refreshes which then are killing the SSAS tabular for a few minutes. The worst thing is that we can't do anything about that except deleting reports. Please at least add PowerShell cmd to disable it for certain list of reports. The best option would be to disable it globally for entire tenant.
RE: More control of SSAS direct query cache refresh
This should include Live Connections as well
RE: More control of SSAS direct query cache refresh
I agree with Dan. We need to turn the ability to turn off the caching mechanism as when we have hundreds of customers accessing, this will squash our AAS S4 instance then. We need better control over this.
RE: More control of SSAS direct query cache refresh
I would like to add on to this. Having more granular level of control along with being able to disable would be great. Right now the options are 15min, 30min, hourly, daily, or weekly. We have no ability to try and stagger and specify the time of day for these either, so if we have say 5 models it is possible that all of the refreshes could occur after all of the processing is over which would not be great on managing memory on our Azure Analysis Services. We are seeing well over 300 queries being sent for just 9 users that accessed a single report that has a lot of visuals on the report. The caching doesn't seem to work as expected like with SSRS because even after this we see queries being sent to the model when the reports are accessed.