Embed Performance - Diagnostic Steps

[stusrus]

I have an embedded Power BI report on a P1 capacity that performs with sub-second filtering on the power BI workspace on a very large dataset with RLS. Our team embedded the report (in Salesforce if that matters) and the performance is painfully slow on 6 visuals and throwing Resource Exceeded on a simple table visual with the error saying the 6 gig memory limit was exceeded.

I looped a few support people into the problem but they are using a shotgun approach trying some gussignosis instead of diagnosis with no real progress on narrowing down the root cause. I have read the articles I can find, done what they said but not making much progress.

I captured the DAX using performance analyzer and captured it for the embedded report using trace from DAX Sudio and it is the same.  There was nothing in it about the RLS filtering so I think maybe I need to use another type of trace?  I even changed the simple RLS formula to return TRUE() to see if it was the RLS filtering that was responsible but no change in the results.  I get 320 ms response in studo and 54 second response and resource exceeded when embedded.

We traced the embedded page using Chrome and don't see the page generating excess traffic to power BI that would account for an overload of the power BI service.

What next steps would you recommend to help us narrow down the root cause?

[v-mengzhu-msft · Accepted solution]

Hi @stusrus ,

According to your description, you said that the error is “6 gig memory limit was exceeded because of resource exceeded”,  and when this error occurs, you are performing a filtering operation on a very large dataset.

So the most possible reason causing the error may be your dataset is too large but your SKU is just only P1.

If you want to know the detail performance of your dataset, you can refer this docs: Monitor Power BI Embedded - Power BI | Microsoft Docs , it also provide you some guidelines about how to improve performance.

You can also consider purchase SKUs that support larger capacities.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[stusrus · Accepted solution]

The final solution was implemented by updating the dataset model to replace a many-to-many relationsip with an "Intersect", "Join" or some call it ia "Bridge" table containing a single inique key field that has a 1:n relationship betwen the two other tables.  The operation of the coss filtering works exactly the same but coss-filtering with RLS is now immediate and no longer runs out of memory.   The issue was not directly related to embedding, but appears to be related to RLS cross-filtering with many-many relationships.  Example:

Team User table had multiple teams for a user, multiple users on a team.   Needed to join the team to a list of accounts the team worked on.   Instead of joining the team between TeamUser.TeamKey and Account,TeamKey, added in the "Team" table that has only the unique  list of Team.TeamKey.   Join 1-many with Account and 1-many with TeamUser and the performance issue we were encountering was resolved.  

[v-mengzhu-msft · Accepted solution]

Hi @stusrus ,

According to your description, you said that the error is “6 gig memory limit was exceeded because of resource exceeded”,  and when this error occurs, you are performing a filtering operation on a very large dataset.

So the most possible reason causing the error may be your dataset is too large but your SKU is just only P1.

If you want to know the detail performance of your dataset, you can refer this docs: Monitor Power BI Embedded - Power BI | Microsoft Docs , it also provide you some guidelines about how to improve performance.

You can also consider purchase SKUs that support larger capacities.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[stusrus]

Note this did not resolve the issue -- see my followup post below for the solution that worked for me.

[stusrus]

I did not mark this as the solution.   I have engaged our IT team to assist witht getting access to the logs so that we can monitor the server during the two different processes.   Microsoft support is also engaged and is escalating to the product support team.   A number of changes were made to the model to isolate the potential cause but they had no impact on the embed report performance.  I will post a reply to this thread as new information is discoverd.