Dimensional Level Security

Question

Dimensional Level Security

Aneal roney

SSC-Addicted

Points: 469
More actions
October 18, 2005 at 5:28 pm

#110628

NOTE: I have posted this in both the SQL Reporting Services and Analysis Services thread. Not sure where I need the help
Hello,
I have a cube that has a "company" dimension which tracks a corporate hierarchy (i.e. branch --> Sub Region --> Region --> Country etc). The fact in this cube is a count of orders.
In "Manager Roles" in the cube, I have set dimensional level security for a user to only see a certain level down the hierarchy (i.e. NT user can only see a region and branches rolling up to it. Cannot see the country level for example).
I create an OLAP Data Source via excel, and supply the NT User as the login credentials. However, I can see ALL the cube data. Any ideas what I am doing wrong?
Thanks in advance for your help.
aroney

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Is your windows account a member of the OLAP Administrators? Most likely it is (unless you use 2 different accounts,one for dev, one for testing). If so, by definition, you see it all. A little known feature for testing is to add the 'Role=<role_name_here>" to your connection string. For Excel, this (the conxn string) is stored in the *.oqy file/s in your 'Documents and Settings' directory (ie for your account). Do a search for *.oqy, they'll show up.

Alternatively, get yourself another windows account for testing. Another alternative is to use the Roles dialog/window in Analysis Manager, and use the 'Test Role' button, but this means you'll only get to test it in the cludgy AS querying interface.

HTH,

Steve.

Aneal roney SSC-Addicted Points: 469 More actions · Answer 2

Thanks Steve!

I have a single NT test account I have setup, and assigned the user to a role in Analysis Services that only can see a slice of the corporate hierarchy dimension.

1) When I "test role" in analysis services, I see the data I would expect (only a subset of the total cube data based on dimensional security).

2) The user is NOT in the OLAP administrator's group on the Analysis Server I am connecting to.

3) I found a .odc file I used to connect to the cube via excel. I added the "Role=<role_name>" to the connection string and tested on a new excel spreadsheet. I am still able to see ALL the cube data.

Any other thoughts? Anyone?

aroney