Answered by:

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.

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.

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.

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.

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".

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.

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.