Row-Level Security with Direct Query

[Anonymous]

Hi 

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 

[Anonymous]

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

[v-sihou-msft · Accepted solution]

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

[Anonymous]

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