nerdexam
Microsoft

70-466 · Question #38

You are developing a SQL Server Analysis Services (SSAS) tabular project. The model includes a table named DimEmployee. The table contains employee details, including the sales territory for each…

The correct answer is B. Add a row filter that implements the LOOKUPVALUE and USERNAME functions. Dynamic row-level security that maps each logged-in AD DS user to their own sales territory requires combining USERNAME() to identify the caller with LOOKUPVALUE() to retrieve the matching territory from the DimEmployee table.

Build a tabular data model

Question

You are developing a SQL Server Analysis Services (SSAS) tabular project. The model includes a table named DimEmployee. The table contains employee details, including the sales territory for each employee. The table also defines a column named EmployeeAlias which contains the Active Directory Domain Services (AD DS) domain and logon name for each employee. You create a role named Employees. You need to configure the Employees roles so that users can query only sales orders for their respective sales territory. What should you do?

Exhibit

70-466 question #38 exhibit

Options

  • AAdd a row filter that implements the USERNAME function.
  • BAdd a row filter that implements the LOOKUPVALUE and USERNAME functions.
  • CAdd a row filter that implements only the CUSTOMDATA function.
  • DAdd a row filter that implements the LOOKUPVALUE and CUSTOMDATA functions.

How the community answered

(55 responses)
  • A
    7% (4)
  • B
    51% (28)
  • C
    16% (9)
  • D
    25% (14)

Why each option

Dynamic row-level security that maps each logged-in AD DS user to their own sales territory requires combining USERNAME() to identify the caller with LOOKUPVALUE() to retrieve the matching territory from the DimEmployee table.

AAdd a row filter that implements the USERNAME function.

USERNAME() alone returns the current user's logon name but provides no mechanism to translate that name into a SalesTerritory value, so the filter cannot restrict rows to the correct territory.

BAdd a row filter that implements the LOOKUPVALUE and USERNAME functions.Correct

USERNAME() returns the domain\logon name of the currently authenticated user, which matches the EmployeeAlias column in DimEmployee. LOOKUPVALUE() then searches that column for the matching alias and returns the corresponding SalesTerritory value, which is used in the row filter expression to restrict each user to only rows belonging to their territory. This combination is the standard DAX pattern for AD DS-based dynamic row-level security in tabular models.

CAdd a row filter that implements only the CUSTOMDATA function.

CUSTOMDATA() reads a string passed explicitly in the connection string; it is not populated from AD DS identity and therefore cannot dynamically identify the logged-in user without custom client-side configuration.

DAdd a row filter that implements the LOOKUPVALUE and CUSTOMDATA functions.

LOOKUPVALUE combined with CUSTOMDATA() would require the client application to inject a custom data string per connection rather than relying on AD DS authentication, which does not match the requirement to use the EmployeeAlias AD DS logon.

Concept tested: Dynamic row-level security using USERNAME and LOOKUPVALUE in SSAS tabular

Source: https://learn.microsoft.com/en-us/analysis-services/tabular-models/row-filters-in-tabular-models

Topics

#row-level security#USERNAME function#LOOKUPVALUE#DAX row filters

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice