none
SSAS Tabular Model 2012 with Excel as user interface, limiting what users can do

    Question

  • Basically we want users be able to see the data but not export the data our of the warehouse. The initial thought was use Excel as front end but not sure how we can meet this requirement of restricting exports.

    Thank you


    Gokhan Varol

    Thursday, July 18, 2013 6:13 PM

Answers

  • Basically we want users be able to see the data but not export the data our of the warehouse. The initial thought was use Excel as front end but not sure how we can meet this requirement of restricting exports.

    Thank you


    Gokhan Varol

    Hi Gokhan,

    You can consider using dynamic security in Tabular mode. For detail information, please refer to the following article:
    Dynamic Security: http://technet.microsoft.com/en-us/library/hh213165.aspx#bkmk_rowfliters

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, July 19, 2013 6:14 AM
    Moderator
  • I can suggest an approach to allow people to only see Grand totals as opposed to more granular data.

    You can create a perspective on the cube and on the perspective only expose the higher level hierarchy levels. Then only allow these users to connect to the perspective and not the cube itself.


    My Blog: http://ditchiecubeblog.wordpress.com/

    using perspectives is definitely a good idea in this case (since OP is working with over 150 dimensions) but it won't actually prevent a user from connecting directly to the cube (as opposed to the perspective) and querying more detailed data.

    --

    unrelated sidenote: I've often wondered why msft doesn't add the ability to secure through perspectives.


    BI Developer and lover of data (Blog | Twitter)

    Friday, July 19, 2013 8:56 PM

All replies

  • Do you need to restrict all exports?  Or are you trying to limit "large" exports?

    BI Developer and lover of data (Blog | Twitter)

    Thursday, July 18, 2013 7:05 PM
  • That's a discussion going on, the requirements are not clear but I would guess if there can be a size limit put into this somehow that could certainly open up ideas. The things is we have over 150 dimensions and growing, I am not sure how we can put a single number for size limitation.

    The fact table has less than 200 million rows, we want some users be able to pull raw data of the fact table (row level data) and some people only see grand totals etc and if they see more data than grand total or so we want to make sure they cannot extract that data.


    Gokhan Varol

    Thursday, July 18, 2013 7:09 PM
  • I can suggest an approach to allow people to only see Grand totals as opposed to more granular data.

    You can create a perspective on the cube and on the perspective only expose the higher level hierarchy levels. Then only allow these users to connect to the perspective and not the cube itself.


    My Blog: http://ditchiecubeblog.wordpress.com/

    Thursday, July 18, 2013 7:46 PM
  • As far as limiting what some users see versus other users see when accessing the tabular model... that will have to be done via row-level security.  

    When connecting to the cube via excel pivot table, the default drillthrough action limits the rows to 1000.  But you can create additional drillthrough actions and customize this number:

    http://www.sqlservergeeks.com/blogs/raunak.jhawar/sql-server-bi/686/sql-server-2012-ssas-tabular-model-actions

    Also, with PowerPivot, users will be able to pull wide open queries against the tabular model...so that's probably going to need to be off-limits.

    The more I think about it, you're probably going to want to stick with SSRS (or something similar) where you can fully control what the users can pull back (or more precisely - the load the users can put on the system).  The downside is the loss of "ad-hoc".



    BI Developer and lover of data (Blog | Twitter)

    Thursday, July 18, 2013 8:00 PM
  • Basically we want users be able to see the data but not export the data our of the warehouse. The initial thought was use Excel as front end but not sure how we can meet this requirement of restricting exports.

    Thank you


    Gokhan Varol

    Hi Gokhan,

    You can consider using dynamic security in Tabular mode. For detail information, please refer to the following article:
    Dynamic Security: http://technet.microsoft.com/en-us/library/hh213165.aspx#bkmk_rowfliters

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, July 19, 2013 6:14 AM
    Moderator
  • I can suggest an approach to allow people to only see Grand totals as opposed to more granular data.

    You can create a perspective on the cube and on the perspective only expose the higher level hierarchy levels. Then only allow these users to connect to the perspective and not the cube itself.


    My Blog: http://ditchiecubeblog.wordpress.com/

    using perspectives is definitely a good idea in this case (since OP is working with over 150 dimensions) but it won't actually prevent a user from connecting directly to the cube (as opposed to the perspective) and querying more detailed data.

    --

    unrelated sidenote: I've often wondered why msft doesn't add the ability to secure through perspectives.


    BI Developer and lover of data (Blog | Twitter)

    Friday, July 19, 2013 8:56 PM
  • Good point. After checking your comment, I confirmed that you can't set security at perspective level, I assumed that you could and like you I also wonder why it can't be done at perspective level.

    My Blog: http://ditchiecubeblog.wordpress.com/

    Saturday, July 20, 2013 7:59 AM