Dynamic RLS from a SSAS Tabular Model Data Source

Anonymous · ‎04-17-2020

Hi All

I have spent may too much trying to figure this out and keep getting stumped and would appreciate any help I can get in getting the RLS working.

My user table, called Query look like the one below but with over 10000 rows.

Row No	Pathd	EmployeeID	EmployeeHRContactID	UPN2	FullName	PthCntns	GUID	GUIDUPN
1	124072\|288975\|160261\|193612\|251905	251905	193612	sjasper001	Nola Viau	FALSE	nviau001	[email protected]
2	124072\|288975\|160261\|193612\|251905\|120345	120345	251905	sjasper001	Bernadine Gulledge	FALSE	bgulledge001	[email protected]
3	124072\|288975\|160261\|193612\|251905\|120345\|116884	116884	120345	sjasper001	Joey Warne	FALSE	jwarne001	[email protected]
4	124072\|288975\|160261\|193612\|251905\|120345\|127392	127392	120345	sjasper001	Keva Dow	FALSE	kdow001	[email protected]
5	124072\|288975\|160261\|193612\|251905\|120345\|138799	138799	120345	sjasper001	Sophia Corsi	FALSE	scorsi001	[email protected]
6	124072\|288975\|160261\|193612\|251905\|120345\|1395908	1395908	120345	sjasper001	Dalton Mcmillan	FALSE	dmcmillan001	[email protected]
7	124072\|288975\|160261\|193612\|251905\|120345\|2262522	2262522	120345	sjasper001	Karmen Sharer	FALSE	ksharer001	[email protected]
8	124072\|288975\|160261\|193612\|251905\|120345\|2476227	2476227	120345	sjasper001	Isidra Wurst	FALSE	iwurst001	[email protected]
9	124072\|288975\|160261\|193612\|251905\|120345\|252840	252840	120345	sjasper001	Berniece Fellers	FALSE	bfellers001	[email protected]
10	124072\|288975\|160261\|193612\|251905\|120345\|276943	276943	120345	sjasper001	Migdalia Elliott	FALSE	melliott001	[email protected]
11	124072\|288975\|160261\|193612\|251905\|120345\|314882	314882	120345	sjasper001	Normand Zertuche	FALSE	nzertuche001	[email protected]
12	124072\|288975\|160261\|193612\|251905\|120345\|473396	473396	120345	sjasper001	Flossie Bloyd	FALSE	fbloyd001	[email protected]
13	124072\|288975\|160261\|193612\|251905\|155682	155682	251905	sjasper001	Alita Blackwood	FALSE	ablackwoo001	[email protected]
14	124072\|288975\|160261\|193612\|251905\|192923	192923	251905	sjasper001	Siobhan Jasper	TRUE	sjasper001	[email protected]
15	124072\|288975\|160261\|193612\|251905\|192923\|118052	118052	192923	sjasper001	Dannielle Hamman	FALSE	dhamman001	[email protected]
16	124072\|288975\|160261\|193612\|251905\|192923\|137052	137052	192923	sjasper001	Alona Platter	FALSE	aplatter001	[email protected]
17	124072\|288975\|160261\|193612\|251905\|192923\|194383	194383	192923	sjasper001	Wallace Brazeal	FALSE	wbrazeal001	[email protected]
18	124072\|288975\|160261\|193612\|251905\|192923\|201697	201697	192923	sjasper001	Garnet Nilges	FALSE	gnilges001	[email protected]
19	124072\|288975\|160261\|193612\|251905\|192923\|216452	216452	192923	sjasper001	Karrie Seyler	FALSE	kseyler001	[email protected]
20	124072\|288975\|160261\|193612\|251905\|192923\|221116	221116	192923	sjasper001	Sofia Andersen	FALSE	sandersen001	[email protected]
21	124072\|288975\|160261\|193612\|251905\|192923\|279296	279296	192923	sjasper001	Steven Feathers	FALSE	sfeathers001	[email protected]
22	124072\|288975\|160261\|193612\|251905\|192923\|3830032	3830032	192923	sjasper001	Polly Teel	FALSE	pteel001	[email protected]
23	124072\|288975\|160261\|193612\|251905\|254767	254767	251905	sjasper001	Tabitha Mullinix	FALSE	tmullinix001	[email protected]
24	124072\|288975\|160261\|193612\|251905\|936324	936324	251905	sjasper001	Janae Pechacek	FALSE	jpechacek001	[email protected]
25	124072\|288975\|160261\|193612\|251905\|936324\|167089	167089	936324	sjasper001	Ariel Tempel	FALSE	atempel001	[email protected]
26	124072\|288975\|160261\|193612\|251905\|936324\|210530	210530	936324	sjasper001	Yi Mumford	FALSE	ymumford001	[email protected]

The Pathd column is defined as PATH(Query[EmployeeID],Query[EmployeeHRContactID])

The UPN2 is an extract of the USERPRINCIPALNAME excluding the domain of the user access the model in the service

The GUIDUPN is a combination of the GUID and the domain.

The PthCntns is TRUE where the incoming USERPRINCIPALName equals the GUIDUPN.

What I need it to display is all the users who report to a user. If the user is Siobhan Jasper (row 14), then I would need to see all the records where Siobhan's EmployeeID appears in the PathD (rows 14 to 22)

The rule on the Role in SSAS is defined as:

=if(
PATHCONTAINS(
'Query'[Pathd],
MAXX(
FILTER(
Query,'Query'[GUIDUPN]=USERPRINCIPALNAME()),
'Query'[EmployeeID])),
'Query'[Display]=1,
'Query'[Display]=0
)

My Gateway will parse the incoming USERPRINCIPALNAME as [email protected] if Siobhan Jasper is the user viewing the dashboard.

I hope I have explaned this well enough for everyone to understand and hopefully be able to assist me?

Kind Regards

Greg_Deckler · ‎04-17-2020

http://aboutpowerbi.com/index.php/2020/01/04/hierarchical-dynamic-row-level-security-rls/

See if that helps.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Anonymous · ‎04-17-2020

Thanks for this, it works perfectly, as long as SSAS is not involved.

Maybe I should of mentioned that I am running as a direct query model.

v-kelly-msft · ‎04-20-2020

Hi @Anonymous ，

If I didnt misunderstand wrong,you wanna set a RLS with a SSAS model,Right?

Here is a limitation about RLS that is ,only Import and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.

Here the link for the reference.

https://docs.microsoft.com/en-us/power-bi/service-admin-rls

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!