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.

Answer 1

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

Answer 2

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?

Answer 3

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