Row Level Security in Power BI Embedded problems.

[joemensor]

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.

[Eric_Zhang]

@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]

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.

[Eric_Zhang]

@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]

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