none
Mask Dimension Attributes based on user

    Question

  • We are implementing a new sales cube using SSAS 2008 R2. One of the business requirements is that users can only view identifiable customer information that belongs to them. In other words, the requirement is to mask customer data that doesn’t belong to the user but not actually hide it. For instance, a sample dataset might look as follows:

    Sales Group

    Customer

    Revenue

    ABC

    $8,900

    Customer 1

    $1,500

    Customer 2

    $5,000

    Customer 3

    $2,400

    XYZ

    $2,000

    Customer 4

    $500

    Customer 5

    $1,500

    If the user only has access to “Customer 1” the other customer attribute data should be masked and aggregated as follows:

    Sales Group

    Customer

    Revenue

    ABC

    $8,900

    Customer 1

    $1,500

    HIDDEN

    $7,400

    XYZ

    $2,000

    HIDDEN

    $2,000

    So, in sales group “ABC” “Customer 1” is still visible but “Customer 2” and “Customer 3” have been merged into a masked Value (i.e. “HIDDEN”), the same is done in sales group “XYZ”. This way their revenue is still visible but the customer name has been masked. The legacy, non-OLAP, reporting environment that we are replacing currently provides this functionality. However, I am currently as a loss as to how to accomplish this is SSAS. How can this be done? TIA.

    Saturday, May 05, 2012 7:31 AM

Answers

All replies

  • I believe I can start you in the right direction. You're going to need to begin the solution by creating a role. http://msdn.microsoft.com/en-US/library/ms189696(v=sql.90).aspx Assuming that each customer is assigned to a one and only one employee that is defined as an attribute of a customer, you can use dynamic security to restrict the members that a user sees, http://richardlees.blogspot.com/2010/10/ssas-dynamic-security.html. If you do not check the Enable Visual Total checkbox, the All Customer total will contain the sum of all customers and not the totals of just the customers that the user has directly assigned. The amount for the Hidden member is the difference between the total for the customers that the user has access to and the All Customer level.

    HTH, Martin


    http://martinmason.wordpress.com

    Saturday, May 05, 2012 11:53 AM
  • Thank you for the reply. Note, it is possible for an employee to have multiple assigned customers and they can be in different sales groups as well.

    I had originally went down the road of role security and perhaps it is the right road to be on. However, the roadblock that I kept running into was still displying the "HIDDEN" customers. My MDX isn't great, any pointers on how I can added the "HIDDEN" into the result set by calculating the difference between the All and the displayed customers?

    Saturday, May 05, 2012 2:29 PM
  • I believe you can use the VisualTotals function to determine the totals for the children the user has access to....but I haven't ever tried this. Will play with it later and confirm.

    HTH, Martin


    http://martinmason.wordpress.com

    Friday, May 11, 2012 12:34 PM