Dynamic Security Implementaion in MSAS 2005

Last Post 28 May 2006 09:49 AM by typok. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

09 Dec 2005 06:58 PM

I had used Russ Whitney's article (InstantDoc #27305) on 'Customizing Dimension Security' to implement dynamic security in Analysis Services 2000.

I am trying to implement the same in MSAS 2005. For this I created the sample project with following structure:

Fact_One (ProductID, CustomerID, Quantity, Price)

Fact_One is the fact table

Customer (CustomerID, CustomerName)

Customer is the regular dimension

Product (ProductID, ProductName)

Product is the regular dimension

CustomerAccess (UserID, CustomerID)

Database table CustomerAccess contains multiple rows for each User depending upon the list of Customers a particular User has access upon.

In MSAS 2000, Russ had used similar table in a Security Cube.

In my trial to implement it in MSAS 2005, I have created dimension based on CustomerAccess table, and in the Dimension usage, I have set its relationship type to Referenced. In Referenced relationship type, I have used Customer dimension as intermediate dimension.

After doing all above, I am successfully able to process and deploy the Cube.

I need help in developing the MDX Query that will probably use Filter Function to filter the rows based on the UserID given in the MDX Query.

Also, I would welcome any thoughts if this approach to implementing dynamic security in MSAS 2005 is the right approach. One limitation that was there in MSAS 2000 regarding number of nodes under the single parent node is no more there in MSAS 2005. Would anybody suggest that implementing CustomerAccess table as 2nd Fact Table in MSAS 2005 is more better way.


New Member
New Member

28 May 2006 09:49 AM
I tried this approach whith the same results - MDX query went through Check OK, but when browsing the cube as the user in UserAccess dimension get en error - Invalid function.
Here is MDX:

FILTER([Users].[Hierarchy].[Org ID].Members , [Users].[Hierarchy].Properties( "UserLogin" )= UserName)

Acceptable Use Policy