You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Sales Persons and Territories defines a relationship
between a salesperson’s name, logon ID, and assigned sales territory.
You need to ensure that each salesperson has access to data from only the sales territory
assigned to that salesperson. You need to use the least amount of development effort to
achieve this goal.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)
A.
Create a new role named Sales Persons with Read permission. Add each salesperson’s
logon as a member to the role.
B.
Add the Sales Persons and Territories table to the model, define the relationships, and
then implement dynamic security by using row filters. Grant each salesperson access to the
model.
C.
Create a new Active Directory Domain Services (AD DS) security group and add each
salesperson as a member. Then create a new role named Sales Persons with Read
permission. Add the group as a member to the new role.
D.
Create a separate tabular model for each sales territory and assign each tabular model a
corresponding sales territory name. Grant each salesperson access to the corresponding
tabular model of the assigned sales territory.
https://msdn.microsoft.com/en-us/library/hh213165.aspx#bkmk_rowfliters
B as row filters are designed for this. The other solutions will cause a lot of work needs to be done…. We would use them if the row filters are not be available.
Dynamic security provides a way to define row level security based on the user name of the user currently logged on or the CustomData property returned from a connection string.
B is correct
I think the easy solution is “D” create role, add member and use row filter. and require minimum object definition change and easy to add member
But every where I can see “B” is showing as correct result. where we are crateing lot of object and we need to manage sql table if we want to add new/remove member