Filtering caching related queries from on-prem SSAS trace

[DaveRuijter]

Hi there!

We have a very nice dashboard providing usage statistics of an on-prem SSAS instance.

The instance is being used in the Power BI Service, exposing it using the On-Prem Data Gateway.

However we have a slight issue because the caching-related queries from the Power BI Service are cluttering the data.

For performance reasons, the Service caches some info, per user.

And maybe some events are from Mobile apps, I haven't checked that.

The problem is, as far as I can see, there is nothing identifying those events.

There does not seem to be any distinction between regular-user Power BI events, and the events related to caching Power BI objects.

They all have the same effectiveusername in the Query Begin event for instance.

Hopefully someone from the Product Team is able to point me in the direction?

Help very much appreciated as always!

Cheers,
Dave

[v-shex-msft]

Hi @DaveRuijter,

---

They all have the same effectiveusername in the Query Begin event for instance.

---

I found that the report use the share people‘s certificate to load data. (even if you add the users to data source user list, it still load the report owner's certificate to access the database)

Perhaps you can post this requirement to ideas.

Regards,

Xiaoxin Sheng

[DaveRuijter]

Hi!

I'm not completely sure what you mean, the 'certificate' term you're using is a bit confusing :).

In my experience - for SSAS live connections - it's always the report user effectiveusername that's send to SSAS (not the owner or user who shared the report).

But maybe you're not talking about SSAS connections?

However, this is not related to my question? Or maybe I completely misunderstand you.

I see query events in the trace, and can't distinguish between actual user interactions and cache-related queries..

[v-shex-msft]

Hi @DaveRuijter,

>>However, this is not related to my question? Or maybe I completely misunderstand you.
Sorry, it seems like I have misunderstand your requirement.

>>I see query events in the trace, and can't distinguish between actual user interactions and cache-related queries..
I think these queries are generate by power bi desktop.

Regards,

Xiaoxin Sheng

[DaveRuijter]

>> I think these queries are generate by power bi desktop.

Since Power BI Desktop is not 'in scope' of this situation, I don't think so.

Has anyone found a solution yet?

Please reply here if you also encounter this issue, so the MSFT team sees how big this issue is :).