MS SQL Server OLAP - Dynamic Cell security

Author

Dynamic Cell security

Talheedin

2006-03-29, 9:30 am

Hi All,

I need to implement cell level security on a model so that the windows
username is checked against a username property asigned to the relevent
member of a dimension before the data in the cell is displayed. I have
used the following code in the cell security advanced box of the cube
role:

UserName = [Dim1].CurrentMember.Properties("UserName")

and this allows the user to only see the relevent data. However the
user can't see any of the agregated ansestor nodes in the dimension
either. In the Test Role box on Cube Roles everything has a #N/A value
apart from the few cells that relate to the permitted user.

I need the users to be able to see the entire dataset at a high level
and only be restricted after drilling into the bottom level of the
dimension. How do I do this.

I can't use dimension security because we have a potential user base of
2500 most of whom are in various windows groups (which gives me enough
of a headache!) and I'm not adding hundreds of roles to each of the
approximately 60 models we have. Also, I'm not sure that our poor
server would apreciate having to store so many virtual dimensions in
memory all the time.

Please help.

Deepak Puri

2006-03-29, 11:30 am

Why don't you use dynamic dimension security instead of cell security -
this would automatically permit the ancestor data to be visible?
Assuming that you're using AS 2000, the Allowed Members expression could
be like:

Filter([Dim1].Members,
UserName = [Dim1].CurrentMember.Properties("UserName"))

This MS Support Webcast covers Dynamic Dimension Security:

http://support.microsoft.com/defaul...kb;en-us;828343

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Talheedin

2006-03-30, 3:26 am

Thanks Deepak,

That worked a treat!

Sponsored Links