User Access to Cubes

we currently have several cubes , applications deployed.  of our apps delivered excel frontend.  we deliver applications, typically provide training limited brief demo , document that describes application , how use it.  no problems.  no worries. 

we have not trained user - or informed them - of ability directly connect cubes using excel pivot tables or functions.  concern in doing related performance.  have pretty large, flat dimensions.  , not quite geared train people on ad hoc usage.

we use dynamic security typically allows user see specific list of departments.  there way set security can allow users access cubes via our excel applications optionally allow/prevent them direct access via pivot talbes?

thanks,

sully

 

what kind of excel applications have currently? set of excel pivot table reports? if so, want avoid users creating new reports want allow users use existing set of reports , should able change report filters?

 it should possible add specific users have access cube while others can still see published reports can't change report layout/filters etc. if want allow users change reports not create new, don't think that's feasible because if copy existing workbook , modify them create new reports/use ad-hoc reporting?

in general, in such cases, may want let users start hitting cube , create usage based optimization/aggregations based on usage patterns. also, based on first few days usage, might able come effective partition strategy in cube cube usage pattern. unless have strict limitation on hardware side, in opinion, better try performance tuning cube rather restricting set of users. other options identify user groups , publish lite cube , provide complete cube access vip users :). in ssas 2008, create perspectives can use lite cube (with key measures & dimensions exposed).

 

---

..hegde

SQL Server  >  SQL Server Analysis Services