Apply dimension security across the full cube, how to

[eo]

Hi,

I hope my confidence in SSAS 2005 does not let me down here


I have a fact table with a large dimension table (Policy dimension on the one side, and Broker dimensionon the other side), and a number of snowflake dimensions off the large Policy dimension table. I will try and visually represent this:

                                 > Channel
Broker < Fact > Policy dimension > Management Structure
                                 > Broker

One of the attributes in the Policy dimension is Legal Entity, and one of the attributes in the Management Structure dimension is Division. I must restrict user access on two dimensions:
a) on the XYZ member of the Legal Enity hierarchy in the Policy dimension, and
b) on the MARINE member of the Division attribute in the Management Structure snowflake dimension

It is quite easy to implement this using cube roles and allowed or denied sets, and if the user views the facts by these dimensions they only have access to the XYZ Legal Entity member, and the MARINE Division member, BUT when these restricted attributes are not used when querying the cube, then the facts totals still includes the values which belongs to XYZ and MARINE. For example if the facts are sliced by Broker, and not by Legal Entity and Division, then all the facts are shown, even those belonging to XYZ and MARINE.

I hope I am explaning this clearly, but in short, can dimension data security settings apply to the WHOLE cube?? I really hope so, otherwise this security module within SSAS 2005 seems pretty pointless




EO
Hertfordshire, England

 

[MDXer]

this is a complicated topic that is not easily answered in a couple of replies. you would be better served t google SSAS Dimension Security or any other keywords around the subject as there are numerous blogs and whitepapers on the subject.

this link should get you started
Introduction to Dimension Security in Analysis Services 2005

 

[eo]

My understanding is that cell security should give me what I want, but the web is notoriously sparce with examples or descriptions of the MDX to apply

EO
Hertfordshire, England