nerdexam
Microsoft

70-467 · Question #81

You need to implement security in the cube to limit the sites visible to each user. What should you do?

The correct answer is C. Create an SSAS database role and define a Multidimensional Expressions (MDX) calculation. SSAS cube security requires MDX-based member security expressions defined within a database role to dynamically restrict which dimension members each user can see.

Design BI data models

Question

You need to implement security in the cube to limit the sites visible to each user. What should you do?

Options

  • ACreate an SSAS database role in the cube for each user and assign the sites each user can
  • BCreate an SSAS server role for each user and assign the sites each user can access to his
  • CCreate an SSAS database role and define a Multidimensional Expressions (MDX) calculation
  • DCreate a view on the SalesTransactions table that uses the SecurityFilter and User table data

How the community answered

(39 responses)
  • A
    13% (5)
  • B
    8% (3)
  • C
    49% (19)
  • D
    31% (12)

Why each option

SSAS cube security requires MDX-based member security expressions defined within a database role to dynamically restrict which dimension members each user can see.

ACreate an SSAS database role in the cube for each user and assign the sites each user can

Creating one SSAS database role per user is not scalable and does not leverage MDX dynamic security, making administration unmanageable as users grow.

BCreate an SSAS server role for each user and assign the sites each user can access to his

SSAS server roles grant administrative privileges to the entire SSAS instance and cannot be used to restrict dimension member visibility for end users.

CCreate an SSAS database role and define a Multidimensional Expressions (MDX) calculationCorrect

An SSAS database role with an MDX security expression (defined on the dimension or measure group) allows dynamic, set-based filtering of visible members based on user identity or group membership. This approach is scalable because a single role with a calculated expression can serve many users, returning only the site members each user is authorized to see without requiring one role per user.

DCreate a view on the SalesTransactions table that uses the SecurityFilter and User table data

A view on the source relational table restricts data at the ETL or query layer but does not enforce security within the already-processed cube, so cube users could still see all members.

Concept tested: SSAS cube dimension security using MDX expressions

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/grant-custom-access-to-dimension-data-analysis-services

Topics

#SSAS dynamic security#MDX security expression#dimension security#database roles

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice