Limiting access to cube via Pivot Table

Question

Post reply

Limiting access to cube via Pivot Table

Brad Rhine

SSC Enthusiast

Points: 105
More actions
September 17, 2007 at 6:45 am

#197843

Hello,
I would like to limit access to a particular cube by forcing the user to enter a particular account number, instead of allowing the default of all accounts.
i believe that the InstanceSelection element is the key, but I can only find eveidence that suggests that it works in Report Builder. Would this work in an Excel Pivot table, as well?
I am looking for verification and any advanced insights.
Thank you, in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Brad Rhine SSC Enthusiast Points: 105 More actions · Answer 1

Brad Rhine

SSC Enthusiast

Points: 105

September 18, 2007 at 6:14 am

#734273

Martin Parker Newbie Points: 9 More actions · Answer 2

Not entirely sure, but The way I've achieved similar results is to create a Role or Roles in the AS project. Then set the permissions on either:

The entire Dimension
Specific Dimension members
Just specific cell Data

Because all of this is configured in the Cube it makes no difference what client you use, members of the Role will only be able to see the data you specify.

Brad Rhine SSC Enthusiast Points: 105 More actions · Answer 3

I may not have fully explained the concept. I would like to force a user to enter a specific account number. This will, in effect, prevent them from "accidentally" returning every account number.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

This would need to be a feature of your pivot table tool. AS will not allow you to force a user to always have a particular dimension filtered unless you restrict a user or role to a particular member (clearly not what you want).

Excel does not do this. With some VBA, you could create a workbook with the pivot table pre-defined that would prompt the user for an account number when it opens, but they would still be able to remove the dimension from the page section later. I am not sure if you could really prevent that.

We use Panorama Novaview here and with it, you can create a dashboard with a combobox or textbox and wire it to a pivot table to enforce what you are doing. It would take a very small piece of javascript - I have had to do this with a KPI and it would be the same thing. Of course, we paid $80k for the Novaview license.

Brad Rhine SSC Enthusiast Points: 105 More actions · Answer 5

Much thanks, as this is the feeback that verifies my testing to date.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

When Microsoft bought Proclarity last year, they picked up a product that will do this for you. It is the same kind of thing as Novaview in which you will have to use a bit of code to wire it together, but it would solve the problem.

I have no idea how they are pricing it these days, when it was Proclarity it was in the $80k range for the full suite. MS may have dropped that price significantly as part of their BI solution. It is a good product - we just liked the usability of Novaview a lot more.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 7

I agree that security should start back at the Cube. Secure Data at the source whenever possible. It's your first chance to implement security.

Be aware that granting access to Everyone or Anonymous, and depending on some user input, might lead to problems. Even if you coded something into Excel to try and do this, maybe someone would create their own workbook (or Data Source) to get at what you'd like to protect.

Greg E