topic Re: Row-Level Security with Direct Query in Developer

Row-Level Security with Direct Query

Anonymous — Thu, 20 Oct 2016 16:27:14 GMT

I am working in the Project which will go to PowerBI embedded finally .We did not upload to AZURE yet

At this moment

Connection is in power BI desktop with DIRECT QUERY to 2016 SSAS Tubular .

I am working and testing in Row-leverl security portion because we only allow customer look at their own purchase history.

I try to follow

https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-rls/

But the Manage Roles is grey out in Direct Query mode.

All the customer ID is outside domain.

What should I do?

Thank-you

Re: Row-Level Security with Direct Query

v-sihou-msft — Mon, 24 Oct 2016 02:59:31 GMT

@Anonymous

In Power BI, when connecting SSAS tabular data source in DIRECT QUERY mode, it will directly take the role security defined within Tabular model. We can't "Manage Roles" for Tabular source in Power BI Desktop. To edit roles and corresponding row filters, you have to do it in either SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Please refer to tutorial below:

Tutorial: Dynamic row level security with Analysis services tabular model

Regards,

Re: Row-Level Security with Direct Query

Anonymous — Mon, 24 Oct 2016 14:40:00 GMT

Hi Simon

I have two question .

First

All the customer is not a domin user . What can I do .

Second

On this

https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-rls/

It must use import data mode . It would not able to use direct query

we can not refresh the data at this point if we are usin import data model

Please confirm. It was becasue we are planning using PowerBI Embedded to our appication.

Thank-you

Re: Row-Level Security with Direct Query

Anonymous — Sun, 08 Oct 2017 11:38:18 GMT

Hi,

I m using Impala for Data Source in Direct Query Mode.

But seeing an error message "Function LOOKUPVALUE is not allowed as part of row level security expression on DirectQuery models" when I try creating an expression.

I have enabled "Allow unrestricted measures in Direct Query mode"

1. Is it possible to implement Dynamic RLS with Impala for Data Source?

2. Numbers of users in the company is large (runs into millions) and number of levels are 7 (Region, Country, Division, BU, Group, Local Group, Plant). It is not practical to maintain separate rows at the granular level (plant) for each user. We will then have around 10,000,000 rows for CEO and people with upper management.

I have two tables -->

1. Master with columns for each field

2. Security with email and matching fields from Master table

While it works in import mode, it fails in Direct Query mode with the error specified.

DAX expression I had created made use of LOOKUPVALUE as below:

OR(
OR(
OR(
Master[rc]=LOOKUPVALUE('Security'[rc],'Security'[email],USERPRINCIPALNAME(),'Security'[rc],Master[rc])
,
Master[region]=LOOKUPVALUE('Security'[region],'Security'[email],USERPRINCIPALNAME(),'Security'[region],Master[region])
)
,
OR(
Master[rcd]=LOOKUPVALUE('Security'[rcd],'Security'[email],USERPRINCIPALNAME(),'Security'[rcd],Master[rcd])
,
Master[rcdb]=LOOKUPVALUE('Security'[rcdb],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdb],Master[rcdb])
)
)
,
OR(
OR(
Master[rcdbp]=LOOKUPVALUE('Security'[rcdbp],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdbp],Master[rcdbp])
,
Master[rcdbpl]=LOOKUPVALUE('Security'[rcdbpl],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdbpl],Master[rcdbpl])
)
,
Master[rcdbplp]=LOOKUPVALUE('Security'[rcdbplp],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdbplp],Master[rcdbplp])
)
)

Thanks,

Mannu