topic Re: Row Level Security in Power BI Embedded problems. in Developer

Row Level Security in Power BI Embedded problems.

joemensor — Wed, 17 Aug 2016 15:20:03 GMT

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.

Re: Row Level Security in Power BI Embedded problems.

Eric_Zhang — Thu, 18 Aug 2016 06:36:32 GMT

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

Re: Row Level Security in Power BI Embedded problems.

joemensor — Thu, 18 Aug 2016 07:13:27 GMT

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.

Re: Row Level Security in Power BI Embedded problems.

Eric_Zhang — Thu, 18 Aug 2016 08:03:37 GMT

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

Re: Row Level Security in Power BI Embedded problems.

chakravarthi_l — Tue, 07 Mar 2017 13:28:38 GMT

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