Data Permissions in a cube

I have a database with customer subscription data in it.  CusA and CusB.  I have built a cube for it and currently have it set up for internal staff to use, so they can see everything.

Could someone point me in the direction of how to restrict the data in the cube so each customer can only see their own data.  i.e.
If they log into the cube using Admin, all of the subsctiptions fact table is used.  If they log in using CusA only data where owner = CusA is displayed.  If they log in using CusB only data where owner = CusB is displayed.

Any help would be much appreciated.  I'm currently trying to find out using MDSNs website, SQL Server 2005 Analysis Services Step by Step (isbn 0-7356-2199-3) and Delivering Business Intelligence with Microsoft SQL server 2005 (isbn 0-07-226090-4), but I cant seem to work out how to apply their examples to my project and i've managed to read the information differently so it conflicts, i.e. should i be setting the permissions in the DB so the cube inherits it or set up the permissions in the cube.  Any help would be much appreciated as i'm very stuck!  Thanks.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

PFrogConnect With a Mentor Commented:
The key here is roles.

Create two roles, and make sure all facts have a link to a customer dimension. You then just need to give permissions for each role to see the appropriate members of the customer dimension.

Do this from Management Studio, create a role then edit the Dimension Data tab on the role properties to select the appropriate customer.
kintonAuthor Commented:
Thanks PFrog.  I've not forgotten this post, i'm just having trouble getting my head round roles at the moment.  My knowledge of Cubes isn't great.  I jumped in halfway through a project and I should really do a step by step guide at least so appologies for the delay.   Before your post I thought that the primary sources of the numbers in a cube came from the measures which come from the fact tables - hence I thought the permissions had to be set up there rather than dimensions.  If its okay i'll try and get my head round it a bit more before I close the question.

Sure no problem. If you need more info or help figuring things out please shout.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

kintonAuthor Commented:
I've managed to get back to this for a bit.

I've had a go at putting permissions on a dimension role and it works great. However, 1 thing concerns me.

If I have a measure such as 'total subscribers'  and drag it over without using any dimensions, surely that does its count on the whole list?  If so, how do I make it so the main fact table only shows what a user is allowed to see?

kintonAuthor Commented:
I've checked the numbers and it would seem that by adding permissions to a dimension, even if the dimension is not used, all of the data has the criteria from that permission applied to it.  Which makes sense I guess.
Yes, and no  (!)

Lets say you have a dimension called 'customer', with 3 members, one for Customer A, one for customer B and one for C. All facts have a direct link to this customer dimension.
You want each customer to only see their data.

You should create a role (Role A) set permissions on the customer dimension, using the "Deselect all members" option. This means you have to manually add any member that this role should be able to see (i.e. Customer A). This option means that when you add another customer, by default no other customers will be able to see it.

As this role stands, if the user includes the customer dimension in their query then they will only see their own data. However if they don't include the dimension they they will see the whole database - not ideal!
To fix this, go to the advanced tab of the Role's Dimension Data security window, and tick the "Enable Visual Total" box. This now means that the customer dimension security will always be applied no matter what dimensions are included in the query.

Have a play with the different options, and you'll see the different effects.
kintonAuthor Commented:
Oh yeah so it does!  You'd have thought that would have been ticked by default, I can't really think of a scenario where that would be useful.  If the user can't see half the data, including the missing stuff in the total isn't useful in most situations i'd have thought.

Had a bit of a play with the roles.  Not sure how I managed it but at one point I had the following;
Product dimension with products & clientID in it.
Deselect all members selected at database and cube level, with 1 of the client_ID memebers ticked.
Permissions left alone so they should be able to see everything.

For some reason if I just selected product from the product dimension and total subscriptions, it only displayed the products that had a client_ID of 0.  I had to bring in the Client_ID member from the product dimension to be able to see the products with a client_ID of 1.
I scrapped the role and tried to build it again and it worked as it should have, so I have no idea what happened there!

Thanks for the info PFrog, I think I now have a basic grasp of Roles.

kintonAuthor Commented:
In theory then, I should only ever have to set up permissions for 1 of the dimensions i.e.

If I have the following dimensions and those with an * have client data...
....I should only need to set the permissions up for client and the rest will fall in place automatically?
That's right in terms of the fact data, although do you want customers to be able to see other customers' dimension members?

If a user specifies a NON EMPTY clause in an MDX statement, all members will be returned even if they have no data, so one customer could see all clients belonging to all customers, even though he would not be able to see the fact data associated with the other customers.

So in short, you should really secure every dimension that contains customer specific members
kintonAuthor Commented:
Ah right I see, i'll have another look and get back to you - thanks for the warning :)
Have a good weekend - i'll post back on monday for this one.
kintonAuthor Commented:
So it does.  I was hoping that because I included Client_ID in the relationship to the other dimensions it would restrict then from seeing them too.  Glad you pointed that out as I hadn't seen that!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.