none
Dynamic Security of SSAS Cube based on Users and Territory

    Question

  • Hello

    I have Dimension called as - Territory(e.g. America, Australia, China). I want to set up security in cube so Users from America can see only their own data and Users from China can see only China's data.

    Please Guide me in this..

    Thank you

    Tuesday, June 05, 2012 9:14 PM

Answers

  • Swetha,

    Check out these two links:

    http://www.rodcolledge.com/rod_colledge/2010/10/ssas-dynamic-security-kerberos-and-performancepoint.html

    http://bifuture.blogspot.com/2011/09/ssas-setup-dynamic-security-in-analysis.html

    You need to build two dimension tables for your users and territories, then build a bridge fact table with user and territory; create a count measure; create a role, add members(users) and then modify the dimension data for your territory dimension with the following code:

    exists(
    [Territory].[Territory Name].[Territory Name].Members
    , StrToSet("[User].[User].[" + Right(UserName(),Len(UserName()) - Instr(UserName(), "\")) + "]"),
    "CUBEBRIDGEUserterritory")

    Note: in this example user name is stored with first name initial and last name, domain name is not included so that's why you have this - Instr(UserName(), "\")).

    Tuesday, June 05, 2012 9:53 PM
  • Hello Skyrocket

    I guess I am doing something wrong in creating cube structure. I have Fact table, Region table, User table and Other table which has - which user have access to which region.

    RegionID is Primary key in Region table and referential integrity is in Fact table with Region ID.

    But How User table and Other table(regionUser table) should connect to Fact table?

    Swetha,

    Cube may contain multiple fact tables. So your Region and User tables are dimensions, and Other table needs to become a fact table, then create a row count measure on Other table and connect Region and User tables in Dimension Usage screen. Once you do that, then hide this measure as well as User dimension. Hope this helps.

    Michael

    Monday, June 11, 2012 8:12 PM

All replies

  • Swetha,

    Check out these two links:

    http://www.rodcolledge.com/rod_colledge/2010/10/ssas-dynamic-security-kerberos-and-performancepoint.html

    http://bifuture.blogspot.com/2011/09/ssas-setup-dynamic-security-in-analysis.html

    You need to build two dimension tables for your users and territories, then build a bridge fact table with user and territory; create a count measure; create a role, add members(users) and then modify the dimension data for your territory dimension with the following code:

    exists(
    [Territory].[Territory Name].[Territory Name].Members
    , StrToSet("[User].[User].[" + Right(UserName(),Len(UserName()) - Instr(UserName(), "\")) + "]"),
    "CUBEBRIDGEUserterritory")

    Note: in this example user name is stored with first name initial and last name, domain name is not included so that's why you have this - Instr(UserName(), "\")).

    Tuesday, June 05, 2012 9:53 PM
  • If you only have a small number of regions it may be just as easy to create a role per region and then assign users (or preferrably windows groups) to each role. And use the dimension data tab to restrict each role to only being able to see a given territory member.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, June 06, 2012 3:30 AM
  • Hello Darren

    There are around 72 Regions..so its not possible to do mannually...And I am using this cube in my SSRS report and Performancepoint dashboards.

    And region is parameter...So If user from China login into Sharepoint and see dashboard..Only china regions can be seen in the report...

    Wednesday, June 06, 2012 1:25 PM
  • Hi Swetha

    One thing I didn't understand why do you have Region as parameter. Anyway the data should get filtered according to the region of the user logged in. So why do you have region as a parameter when the user has no option to choose it? Creatin dynamic security for each region should solve it. Sorry if I got you wrong. But after reading, this is what I thought.

    Thanks


    Madhavi Pasapula

    Wednesday, June 06, 2012 2:10 PM
  • Hello Madhavi

    Even if i dont keep Region as Parameter. I am not able to create dynamic security...I created dynamic security in one role..but when i test it in visual studio as that role, it gives no region data...

    I have user table, Region table and one more table which has security defined  i.e. which user has access to which region...

    In my cube, if i select all regions on rows and put one user in where clause it works fine....but when i keep this in role, it doesnt work....

    can you please tell what exactly i should do in role? My user is domain\username.

    Thank you

    Wednesday, June 06, 2012 2:37 PM
  • Swetha,

    Did you create a measure on the table that has user security defined? In your role, you need to go to Dimension Data tab, then in Dimension drop down choose your region table, in Attribute Hierarchy pick column with territory name and in Allowed member set enter the following:

    exists(
    [Territory].[Territory Name].[Territory Name].Members
    , StrToSet("[User].[User].[" + UserName() + "]"),
    "CUBEBRIDGEUserterritory")

    Territory is dimension with your regions, User is dimension with your users, and CUBEBRIDGEUserterritory is your bridge table.

    Wednesday, June 06, 2012 5:47 PM
  • CUBEBRIDGEUserTerritory is table which contains User name and which all territory that user has access to, Am I correct?
    Wednesday, June 06, 2012 5:59 PM
  • yes, that's correct, one thing is the bride table should look like this:

    userName Region

    domain\name 1

    domain\name 2

    or you can use MDX in my first post if your go with just last name.

    Wednesday, June 06, 2012 6:02 PM
  • Hi Swetha

    I hope that the suggestion given by Skyrocket works for you. Since you said if you give the user in the where clause it works fine, I think you can add a filter to the report data, that filters the data with user's Lan Id. It can be done by using the In built fiels User Id. I did something like that in my reports and its working for me.

    Thanks


    Madhavi Pasapula

    Wednesday, June 06, 2012 7:14 PM
  • Because the dynamic security expression uses StrToSet if you have any errors in that expression you will get an empty set. If this is the case when you test this role you will not have permission to see any data.

    You can test this expression in an MDX window in SSMS by hard coding in various usernames instead of using the UserName() function to see what territories various users would get and to make sure your syntax is correct.

    SELECT {} on 0, exists(
    [Territory].[Territory Name].[Territory Name].Members
    , StrToSet("[User].[User].[" + "domain\user" + "]"),
    "CUBEBRIDGEUserterritory") on 1
    FROM <put_your_cube_name_here>


    http://darren.gosbell.com - please mark correct answers

    Wednesday, June 06, 2012 8:22 PM
  • Hello Skyrocket

    I guess I am doing something wrong in creating cube structure. I have Fact table, Region table, User table and Other table which has - which user have access to which region.

    RegionID is Primary key in Region table and referential integrity is in Fact table with Region ID.

    But How User table and Other table(regionUser table) should connect to Fact table?

    Thursday, June 07, 2012 4:56 PM
  • Hi,

    You can connect the other table's region key with the primary key in the region table and other table's userkey with the user key in user table.

    Note: the join should always be from foreign key to primary key.

    Since you are writing an mdx expression in the roles upon the measure group created out of this factless fact(from other table), it will fetch the userid from the connection and filter the records for that particular user in the other table. Hence you are filtering the regions, the user has access to from other table. Since you have a mapping between the other table and user table and region table, your records in all these dimensions is limited to that particular user for the regions he has access to. So, you are able to slice the cube for this criteria.

    Thanks,

    Srihari Thakkelapati

     


    Srihari

    Friday, June 08, 2012 7:07 AM
  • Hello Skyrocket

    I guess I am doing something wrong in creating cube structure. I have Fact table, Region table, User table and Other table which has - which user have access to which region.

    RegionID is Primary key in Region table and referential integrity is in Fact table with Region ID.

    But How User table and Other table(regionUser table) should connect to Fact table?

    Swetha,

    Cube may contain multiple fact tables. So your Region and User tables are dimensions, and Other table needs to become a fact table, then create a row count measure on Other table and connect Region and User tables in Dimension Usage screen. Once you do that, then hide this measure as well as User dimension. Hope this helps.

    Michael

    Monday, June 11, 2012 8:12 PM
  • Hello Skyrocket

    followed you...And now it's working fine..

    Thank you

    Tuesday, June 12, 2012 9:35 PM
  • You are welcome. I'm glad I was able to help.
    Thursday, June 14, 2012 4:43 PM
  • I try to implenment this solution before , that is what I need, I have 3  Cube  one is entity cube , anothers are link Cube . when I put this script into ssas database dimension (allow or deny option), it's raise error out , But when I put the script into  entity cube dimension ,it's work fine , I don't know....

    this is acceptable , now I have the link cube , when I put the script into link cube dimension , it's raise error  out too, how to solve ,do we any way to put his script into database dimention  (deny or allow option ),or better solution

    My Script:

    EXISTS( [Area].[Id].[Id].members, STRTOSET("[Dim User].[User Key].["+Username+"]"), 'Fact Reseller User' )

    ERROR:

    The browser is disabled because custom MDX expressions defined in the Advanced tab were not validated due to the following problem:

    分析字符串 [Dim User].[User Key].[PHARMA\E0159192] 时在多维数据集中找不到“[Dim User]”维度。


    Wednesday, July 11, 2012 2:40 AM
  • Hi,

    Do we have to enter one by one each user or can we use a AD security group?

    Thanks and regards,

    Jérémie

    Friday, November 08, 2013 3:46 PM
  • Hi,

    Do we have to enter one by one each user or can we use a AD security group?

    Thanks and regards,

    Jérémie

    Groups work fine. In fact my preference is to always use groups when I can so that then you don't need to constantly edit your roles, you just get people added to the right AD groups.

    http://darren.gosbell.com - please mark correct answers

    Friday, November 08, 2013 11:33 PM