July 31, 2003 at 11:21 am
We have developed a cube on our customer data for internal use. Currently it is used by marketing, and that group can view all customer information. Now our customers are interested in getting at some of the data. Is there any way to restrict cube information at the row level?
ie Customer Jim logs on and want to see his orders, but (no matter how much he wants to) should not see Larry's orders.
Typically this would be done with a simple WHERE clause, but can such a thing be done if the cube is already built? If not what is typically done in such circumstances?
We also have a need where only certain people can see certain orders?
ie Jim's subordinate logs on and should only see the orders for their department. Their subordinate logs on and can only see the orders that they placed!
I know that I will need more training in this area, but if you can tell me that I am thinking rightly (or wrongly depending) then point me in the right direction, I can run with it!
Thanks in Advance
Jeff
Edited by - jwt on 07/31/2003 11:26:45 AM
August 4, 2003 at 12:00 pm
This was removed by the editor as SPAM
August 6, 2003 at 7:26 am
Hi Jeff,
I think what you're looking for here can be found in the cube roles. AS access is driven solely by Windows authentication, so each role can have members of windows users and/or groups. So this is where you add 'Jim' to a role (say 'My Best Customer Jim'). You then limit the members viewable (within say the orders dim) to this role. This is where it's likely to get interesting, as you'll prob want to use an MDX statement to do this, the alternative is that you specify by 'checking/ticking' each members checkbox, but this is not sustainable, as I imagine new orders get added with sme frequency and who wants to maintain that on a daily basis. What you *may* be able to do is add a member proeprty to the orders dimension (at the order number level) and then either try to write your dimension filtering MDX to utilise this member property, or maybe use the mem prop to create a virtual dimension, and use this in the mdx statement that filters the orders dimension.
HTH (and above all makes some semblance of sense!),
Steve.
Steve.
August 22, 2003 at 2:15 pm
Jeff,
I think Steve was on the right track with his idea of using the member properties with MDX to provide the solution. On my first attempt at dimension-level security, I tried using cube roles (without MDX) to restrict information and slowly changing dimensions hosed my cube.
However, I found a good article on SQLMag.com written by Russ Whitney that deals with this issue. You can find it by looking up InstantDoc # 27040. I have implemented this solution and it seems to work well.
Eric
quote:
Hi Jeff,I think what you're looking for here can be found in the cube roles. AS access is driven solely by Windows authentication, so each role can have members of windows users and/or groups. So this is where you add 'Jim' to a role (say 'My Best Customer Jim'). You then limit the members viewable (within say the orders dim) to this role. This is where it's likely to get interesting, as you'll prob want to use an MDX statement to do this, the alternative is that you specify by 'checking/ticking' each members checkbox, but this is not sustainable, as I imagine new orders get added with sme frequency and who wants to maintain that on a daily basis. What you *may* be able to do is add a member proeprty to the orders dimension (at the order number level) and then either try to write your dimension filtering MDX to utilise this member property, or maybe use the mem prop to create a virtual dimension, and use this in the mdx statement that filters the orders dimension.
HTH (and above all makes some semblance of sense!),
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply