DirectQuery works great on laptop but unusable when on Power BI Service Embedded

[Clampazzo]

I currently have a Semantic Model that has an aggregation table which is imported and a detail table that is a direct query to our databricks source.  There is also Row Level Security setup on the model.  We are also using apps own data embedded.

When testing on the service and viewing as a customer everything works perfectly.  The aggregation model works as expected and when I drill down into data (filtering from a summarized view) the visuals return in a timely manner.  

I publish it to the service and the aggregation tables still work perfectly but the direct query becomes unusable.  

I view the queries in databricks and they run in a couple of seconds.  Yet Power BI either never loads or takes almost a minute to load a visual.  

This model is also on a capacity with several other import models that are quite large and have been setup using incremental load.  All together all models sizes are either close to the total capacity or greater than.  Not sure if that matters with DirectQuery or not.

The measures are simple.  Just CountRows or Sum.  

Model is star schema.  

[GilbertQ]

Hi @Clampazzo 

I think that trying to do RLS with DirectQuery is going to slow down the semantic model significantly. It is also suggested to potentially re-model your star schema so that it suits your RLS requirements.

[GilbertQ]

Hi @Clampazzo 

It could possibly be that your semantic models in your capacity is causing an issue. Another thing to look at is to make sure that when you're using DirectQuery query, is it going through a Gateway and if it is going through a gateway, is that potentially the bottleneck? Another option what you could do is to spin up another capacity and test out the semantic model. If it works faster on the other capacity, then you know where the issue is.

[Clampazzo]

Thanks for the reply @GilbertQ I found the issue but I'm unsure how to fix it.  

Due to the size of this model I have at least 25 dimensions with over half needing to have RLS added to them separately.  It appears that many RLS degrades the directquery.  I'm still trying to determine if one table affects it more than others.  

The problem is all RLS is simple in that it's [ID]=int(UserPrincipalName())

They all share the same RLS and in theory I could have one main RLS table that filters all of them.  However, in practice when I try to link the main RLS table to more than 1 dimension I get an ambiguity error.  

The purpose of the RLS is simply to filter the dimension down so one client only sees their dimension data.  And bi-directional filtering is greyed out.  

Any ideas that I could try on this?

For instance I have the following tables:

USER
Region
Registration

Fact

User (can be main RLS)

User > Fact
User > Fact Aggregation

Region > Fact

Region > Fact Aggregation

Registration > Fact

Registration > Fact Aggregation

Each relationship above is 1 - Many (Dimension on left is 1)

If I attempt to use User in another relationship as a master RLS it gives me ambiguity.  IE:

User > Region > Ticket

I get the below message:

There are ambiguous paths between Fact and User:  Fact > User  and Fact > Region > User

Even though that path is many to one (Fact is many the rest are one's...) even user to region is 1 to many, one user to many regions.

[Clampazzo · Accepted solution]

Found the answer that worked for me.  

Composite Model.

I imported my dimensions and everything works great for me.