-1

Our setup is SQL Server + SSAS + multidimensional OLAP + PowerBI on top of it.

Recent issue we noticed is that when user loads PowerBI report, from time to time forementioned event occurs. This leads to really long waiting time until the report loads (event takes up to 45-60 seconds).

Our cube has ~20 dimensions and ~50 measures, 2-3 million rows in 3 partitions, MOLAP storage.

What can we do about it? How can we debug it? We don't have SSAS experts on board and googling this event didn't help much. Where can we search for reasons of such behavior?

Marcin
  • 137
  • 1
  • 10
  • Is there an error visible in SQL Server profiler log? Anything related to https://support.microsoft.com/en-us/topic/kb4540731-fix-unexpected-exception-error-occurs-when-you-run-discover-csdl-metadata-in-power-bi-desktop-in-sql-server-2016-3c0cff98-f5e7-5392-3abb-606ed52e3147 or https://support.microsoft.com/en-us/topic/kb4487957-fix-power-bi-desktop-queries-may-fail-with-error-code-3238002710-in-ssas-2014-54e36498-d773-5749-67d0-4abe748112e0 ? – Gigga May 08 '21 at 10:14
  • Thank you for your comment. (un)fortunately, there are no errors. Just long DISCOVER_BEGIN and DISCOVER_END and in between DISCOVER_CSDL_METADATA that just took me almost a minute. Longest duration today was over 90 seconds for one of users... – Marcin May 10 '21 at 06:38

1 Answers1

0

It turns out that in our case it was probably caused by cache being dropped each time the cube was processed.

Our solution would be creating SSIS Package that would run certain DMV queries to populate cache every time we process our cubes, so end-users would be able to use the cache instead of generating it themself.

Marcin
  • 137
  • 1
  • 10