Limiting access to cube via Pivot Table

  • Brad Rhine

    SSC Journeyman

    Points: 75

    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.

     

  • Brad Rhine

    SSC Journeyman

    Points: 75

  • Martin Parker

    Newbie

    Points: 9

    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 Journeyman

    Points: 75

    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

    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 Journeyman

    Points: 75

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

  • Michael Earl-395764

    SSC Guru

    Points: 53873

    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: 20587

    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

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

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