Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Clampazzo
Resolver I
Resolver I

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

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.  

1 ACCEPTED SOLUTION

Found the answer that worked for me.  

Composite Model.

 

I imported my dimensions and everything works great for me.  

View solution in original post

4 REPLIES 4
GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

Found the answer that worked for me.  

Composite Model.

 

I imported my dimensions and everything works great for me.  

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors