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.