nerdexam
Microsoft

70-467 · Question #136

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 empl

The correct answer is C. Add a row filter that implements the LOOKUPVALUE and USERNAME functions.. In an SSAS tabular model, territory-based row-level security requires USERNAME() to identify the logged-in AD DS user and LOOKUPVALUE() to retrieve that user's territory from the employee dimension.

Design BI data models

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?

Options

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

How the community answered

(50 responses)
  • A
    10% (5)
  • B
    24% (12)
  • C
    60% (30)
  • D
    6% (3)

Why each option

In an SSAS tabular model, territory-based row-level security requires USERNAME() to identify the logged-in AD DS user and LOOKUPVALUE() to retrieve that user's territory from the employee dimension.

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

CUSTOMDATA() returns the value of the CustomData property passed explicitly in the connection string rather than the authenticated Windows user identity, so it cannot reliably match users against AD DS domain accounts stored in EmployeeAlias.

BAdd a row filter that implements only the USERNAME function.

USERNAME() alone returns the current user's logon name but provides no mechanism to look up which sales territory belongs to that user, so the row filter cannot filter by territory.

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

USERNAME() returns the current user's Windows domain and logon name in the format matching the EmployeeAlias column (domain\logon). LOOKUPVALUE() then uses that returned value to look up the corresponding SalesTerritory in DimEmployee, allowing the DAX row filter to compare the row's territory against the result and restrict each user to only their own territory's data.

DAdd a row filter that implements only the CUSTOMDATA function.

CUSTOMDATA() depends on a client application explicitly passing a custom string in the connection string and does not automatically resolve to the authenticated Windows identity, making it unsuitable for enforcing AD DS-based row-level security.

Concept tested: SSAS tabular row-level security using USERNAME and LOOKUPVALUE DAX functions

Source: https://learn.microsoft.com/en-us/analysis-services/tabular-models/roles-ssas-tabular

Topics

#SSAS tabular#row-level security#USERNAME function#LOOKUPVALUE

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice