Setting role security in SSAS for a role-playing dimension

Here is a common got-ya.  You are modifying the cube dimension security for a role-playing dimension.  You make the changes and the cube processes successfully.  But when you try to connect to the cube, you get the following error message: The ‘XXX’ attribute in the ‘YYY’ dimension has a generated dimension security expression that is not valid.

The likely cause: You set the security on the database dimension, but should have set it on the cube dimension.  In the dimensions drop down on the Dimension Data tab of the Roles editor, database dimensions are listed first, then cube dimensions second.  So it’s a common mistake to choose the database dimension since it’s listed first and has the same name as the cube dimension.

A cube dimension is an instance of a database dimension within a cube.  A database dimension (also called a shared dimension) can be used in multiple cubes, and multiple cube dimensions can be based on a single database dimension (when this happens the cube dimensions are called a role-playing dimensions).

More info:

Securing Role Playing Dimensions in Analysis Services

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in Security, SQLServerPedia Syndication, SSAS. Bookmark the permalink.

2 Responses to Setting role security in SSAS for a role-playing dimension

  1. Pingback: Role-playing Dimensions | James Serra's Blog

  2. Simon Doubt says:

    Do role-playing dimensions share role security?
    Consider a database dimension called DimDate, and two role-playing cube dimensions, DimOrderDate and DimShipDate.
    If I secure a DimOrderDate using a role, are the restrictions also applied to DimShipDate?
    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>