Securing Role Playing Dimensions in Analysis Services
In Analysis Services, dimension data can be secured at either Shared Dimension or Cube Dimension. A Shared Dimension is an object of Analysis Service Database which is available to all the cubes in the database. The Cube Dimension on the other hand is an object of the cube and is an instance of the Common Dimension which is available only to a specific cube
(Screen Capture 1).
(Screen Capture 1).
Usually, it’s a good idea to secure the data at Shared Dimension level as the permissions gets passed down to Cube Dimensions. This is especially helpful if the Dimension Data security is automated using AMO code – less code to maintain.
This works in most scenarios, except when the dimension is used as Role Playing Dimension. For e.g. Time, Currency, Organization Structure, Staff dimensions are commonly role played. Analysis Services does not permit securing the data of Shared Dimension if it is used as role playing dimension. Exceptions are thrown when the cube if browsed, if you attempt to do so.
If a dimension is role playing, the only way data security can be implemented is by securing the individual Cube Dimension. Cube Dimension Data Security overrides Shared Data Security.
The following screen capture shows the effect of securing the role playing dimension- using Date Dimension from AdventureWorks. Here Date Dimension (Shared Dimension) role plays as Delivery Date (Cube Dimension) and Ship Date (Cube Dimension).
Securing Cube Dimensions can be quite daunting especially if you are considering automating cube security because all the related dimensions need to be considered individually, but that’s the only way out.