August 2, 2012 at 1:34 pm
Hello,
I am very new to MDX and I need some help from you pros...
I have a working SSAS cube. I implemented data level security on this cube by using 2 tables that join to the dimension tables. One is called users (list of all users) and assignments (shows which data the user has access to). The 3 levels of security are Country, Agency and Media Type. So, for example, if we have a user named John Doe we can assign him all of the Agencies and Media Types in the USA. So John Doe should only be able to see the country USA.
Let me try to explain my issue...
We are using excel and Performance point to access the cube. When we open the reports, using our John Doe example, we can see all of the countries, BUT we can only see data for the USA. Is there a property or something that I can change that will prevent this from happening? I need John Doe to only see countries that he has access to, and not see all countries but only see data in the countries that he has access to.
Is there a way to alter this MDX query to filter out data that he does not have access to? alternatively, is there a way to add a filter that only shows measures that are greater than 0? This may solve the issue.
NonEmpty(
[Dim Local Drilldown Cube].[Fact Key].Members,
(
STRTOMEMBER("[Security Users].[User Name].[" + UserName() + "]"),
[Measures].[CUBE SECURITY Count]
))
Thank you all in advance and please let me know if I was clear enough.
Dave
August 14, 2012 at 4:10 am
create a cube role say sales unit /sales area against unit / area dimension
then restrict the required dimension to those dimensions
eg US salesmen should have usa units /area ticked and the rest unticked.
then assign this to an Active directory group for testing
create a user and add him to that group
Open the SSMS as that user( using rightclick run as option)
check has that user he is restricted by that group.
check the help on creating cube roles to get an idea before you begin.
August 14, 2012 at 4:13 am
check out this link
this might help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply