Strange Behavior when tracing PowerBI Desktop execution of SSAS Multidimensional cubes

[ShawnGu]

Hi there,

we're trying to trace down an issue related to our users getting a transport error while their powerbi datasets are refreshing (PowerBI Service -> PowerBI Gateway -> OnPrem SSAS Multidimensional Server).

Once i got a copy of their PBIX file, i ran the refreshes while i had SQL Server Profiler running against the SSAS MD server and i saw something peculiar.

in SSMS, Excel or even a standard OLE script in Powershell, then i run the query, i see Query Begin -> Query End and everything looks normal.

For PowerBI Desktop, refreshing a SSAS import mode query i see

Query Begin <my query>

Query Begin <Cancel xmlns=...>

Query End  <Cancel xmlns=...>

Query End

Error Server: The operation was cancelled by the user.

this repeats several times in the Profiler trace, eventually ending successfully in PowerBI desktop loading the Query1 table with the results.

Has anyone else tried tracing PowerBI Desktop with an SSAS Multidimensional cube?

We've also tried this using different servers and different cubes on different servers and they all seem to behave this way using Windows Server 2022 & SQL Server 2019

[lbendlin]

Power Query is extremely paranoid about meta data changes and it will issue large quantities of ghost queries (in the style of  SELECT TOP 0 *) to re-fetch meta data over and over again.  That's likely what you are seeing.