nerdexam
Microsoft

70-466 · Question #124

You are developing a SQL Server Analysis Services (SSAS) tabular project. In the data warehouse, a table named Employee Security defines a relationship between a salesperson's name, logon ID, and assi

The correct answer is D. Add the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.. Dynamic security in SSAS tabular uses DAX row filters with USERNAME() against a security mapping table to automatically restrict each user's visible data to only their assigned territory.

Build a tabular data model

Question

You are developing a SQL Server Analysis Services (SSAS) tabular project. In the data warehouse, a table named Employee Security 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 also need to minimize the development effort. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Exhibit

70-466 question #124 exhibit

Options

  • ACreate a separate tabular project for each sales territory. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.
  • BCreate a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role with Read permission.
  • CAdd the group as a member to the role. Create a new role with Read permission and then add each salesperson's logon as a member to the role.
  • DAdd the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.

How the community answered

(28 responses)
  • A
    4% (1)
  • B
    4% (1)
  • C
    11% (3)
  • D
    82% (23)

Why each option

Dynamic security in SSAS tabular uses DAX row filters with USERNAME() against a security mapping table to automatically restrict each user's visible data to only their assigned territory.

ACreate a separate tabular project for each sales territory. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.

Creating a separate tabular project per sales territory multiplies development and maintenance effort significantly, directly violating the minimize-development-effort requirement.

BCreate a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role with Read permission.

Adding all salespeople to one AD DS group with a single Read-permission role grants every member access to all data in the model - no per-territory restriction is applied.

CAdd the group as a member to the role. Create a new role with Read permission and then add each salesperson's logon as a member to the role.

Individually adding each salesperson's logon to a single role still grants all members the same unrestricted Read access; without DAX row filters the role cannot differentiate which territory each salesperson is permitted to see.

DAdd the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.Correct

By importing the Employee Security table and defining its relationships in the model, you can apply DAX row filter expressions that call USERNAME() to match each logged-in salesperson's logon ID against their assigned territory in that table. This is the native SSAS tabular dynamic security pattern - a single role with row-level filters adapts automatically per user without requiring separate roles or projects. It minimizes development effort while enforcing precise per-user data access.

Concept tested: SSAS tabular dynamic security with DAX row filters

Source: https://learn.microsoft.com/en-us/analysis-services/tabular-models/dynamic-security-row-filters-and-members

Topics

#dynamic row-level security#DAX USERNAME#row filters#tabular model roles

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice