cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joemensor Regular Visitor
Regular Visitor

Row Level Security in Power BI Embedded problems.

I am using RLS in power bi embedded on a database using directquery which seems to be extremely slow.

 

We have tracked this down to the underlying dynamic SQL that is generated, it is using the COALESCE function.

 

(COALESCE([t4].[projectID], 0) = 49)

 

When I execute the SQL in query analyzer as expected the query is very slow, however when I modify the line with coalesce to:

 

([t4].[projectID] = 49)

 

the query executes within seconds. 

 

Why is this the case? Obviously I have very little way of influencing the dynamic SQL that is generated.

4 REPLIES 4
Moderator Eric_Zhang
Moderator

Re: Row Level Security in Power BI Embedded problems.

@joemensor

 

Regarding the performance difference, most probably there's an index on the [projectid] and the COALESCE function prevents the query using the index. How is the RLS set in your case, does it has anything to do with the [projectid]?

 

As far as I know, there's no way to influence the dynamic SQL generation. 

 

Regarding the DirectQuery Mode, did you enable the option "Allow unrestricted measures"? It would definitely affect the performance.

 

By the way, I've also observed many in-efficient dynamic SQL in other cases. You can submit your feedback on Power BI Ideas.

joemensor Regular Visitor
Regular Visitor

Re: Row Level Security in Power BI Embedded problems.

Thanks for the reply Eric!

 

Regarding the RLS we've simply used the DAX expression:

 

[ProjectID] = 49

 

Although when publishing this to Power BI Embedded we actually change it to

 

[ProjectID]=USERNAME()

 

'Allow unrestricted measures' is enabled.

 

 

 

 

Moderator Eric_Zhang
Moderator

Re: Row Level Security in Power BI Embedded problems.

@joemensor

 

Thanks for the clarification.

 

Based on my research, the dynamic query would always use COALESCE regardless of the "Allow unrestricted measures" option.  As to DirectQuery mode in Power BI Embeded, then I think you're using a Azure database? So far I can't think of any workaround to bypass the performance issue. Maybe you can try to upgrade Azure database to a higher service tier or use import mode instead.

 

Please submit an feedback on Power BI Ideas. Meanwhile I will report this internally and post back if there comes any update.

chakravarthi_l New Member
New Member

Re: Row Level Security in Power BI Embedded problems.

Hello @Eric_Zhang and @joemensor

 

We are facing the same issue. Is there any way to ged rid of the COALESCE in the dynamic query? We found that COALESCE is not used when the report is opened from Power BI desktop and its used only through Power BI Embedded. Any help regarding this is highly appreciated.

 

Thanks