SSAS CUBE Filter Data By Membership Users

Question

SSAS CUBE Filter Data By Membership Users

d_gomes2

SSC-Addicted

Points: 406
More actions
June 7, 2017 at 3:55 am

#151520

Hi All,
I am starting now work in the SSAS so I have very poor knowledge yet.
So can anyone could help me with the SSAS cubeshow the Data with a user’s restrictions.
What Ipretend to know if is possible is to filter Data to display a user that is in acertain membership.
E.g
I have data sales, stocks and other setup by Regions and each regions include a couple of stores. So every time a user that is under my membership of the Region A only can see data from the Region A and so on.
Thank you a lot

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

https://sqlserverbi.blog/2016/06/13/row-level-security-just-got-better-passing-report-credentials-to-analysis-services-in-sql-server-2016/

https://docs.microsoft.com/en-us/sql/analysis-services/supplemental-lesson-implement-dynamic-security-by-using-row-filters

d_gomes2 SSC-Addicted Points: 406 More actions · Answer 2

Hi Martin,
Thank you very much for your reply.
Unfortunately I am using the Sql 2014 Standard.
I don't know if is possible with the 2014 to create a redistrict layer only data, basic the user of the membership1 can only see data on the entire cube that belows to
Region1 .
Thank you

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

d_gomes2 - Wednesday, June 7, 2017 10:25 AM
Hi Martin,
Thank you very much for your reply.
Unfortunately I am using the Sql 2014 Standard.
I don't know if is possible with the 2014 to create a redistrict layer only data, basic the user of the membership1 can only see data on the entire cube that belows to
Region1 .
Thank you

You should be able to. Look at Row-Level-Security (RLS) for SSAS 2014.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

Another option for SSAS would be what's called Role-based Security:
https://www.mssqltips.com/sqlservertip/1834/introduction-to-dimension-security-in-sql-server-analysis-services-ssas-2005/
That will ensure no matter how the user is connecting to the cube, they will only see what they are supposed to see.

d_gomes2 SSC-Addicted Points: 406 More actions · Answer 5

Hi all,
Thank you very much I will give a try on the examples and check if this solve my Problem.
I will post here if works.
Thank you a lot

d_gomes2 SSC-Addicted Points: 406 More actions · Answer 6

Hi All,
I had workaround in the solution below.
Another option for SSAS would be what's called Role-based Security:
https://www.mssqltips.com/sqlservertip/1834/introduction-to-dimension-security-in-sql-server-analysis-services-ssas-2005/
That will ensure no matter how the user is connecting to the cube, they will only see what they are supposed to see.

But When I do this and process the cube is work fine and I able to saw only the Data I want, the only problem and I am not understand is when Sql srver job execute a SSIS PKG to process the Cube looks like a reset is made and when I try to browse the cube with the filter user I can saw all data as all the stores and not the ones I selected.
Anyone knows why are this happen.
Thank you a lot