Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Temporarily disable users access to cubes Expand / Collapse
Author
Message
Posted Friday, February 3, 2012 4:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:36 PM
Points: 586, Visits: 822
Greetings all

This is a similar post to one I posted in the general forums however, this specifically pertains to SSAS data cubes. I have figured out how to do this for sql server and reporting services. The only hitch is the data cubes. I have tried everything I can think of inside SQL server (restricting databases, limiting the service account etc) nothing works. There does not appear to be anything in either the SSAS options and properties via SSMS or inside visual studio for implementing this.

Basically we have users who are up at all times and there is an ETL process from 2am to 5am that needs to run that is crashing because users are using it during peak load times. The 'honor' system is obviously not working ;).

I can't imagine my company is the only one existence that has run into this problem. It would be useful to limit their usage when the cubes are processing as well. Any ideas?


Link to my blog http://notyelf.com/
Post #1246821
Posted Monday, February 6, 2012 3:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 14, 2012 1:27 AM
Points: 45, Visits: 200
You could just make the cubes unavailable perhaps? As a measure of last resort if noone else comes up with a better solution...

Just do an unprocess or a process of a single dimension. I suppose your ETL runs to fill the tables that your cube uses afterwards so it's out of date at the time of your ETL anyway.






My LinkedIn profile
Post #1247184
Posted Monday, February 6, 2012 4:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:28 PM
Points: 882, Visits: 4,111


Basically we have users who are up at all times and there is an ETL process from 2am to 5am that needs to run that is crashing because users are using it during peak load times. The 'honor' system is obviously not working ;).


Go to your SSAS instance, expand Database, Expand "yourdatabase", Expand roles. You will be able to see the roles there. Right click on required roles and select properties. Go to Membership tab and remove the user from here.


----------
Ashish
Post #1247198
Posted Monday, February 6, 2012 8:58 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:36 PM
Points: 586, Visits: 822
So I am to wake up at 2am every morning and manually remove a user from the security roles and re-add them at 5am when everything is done? Doesn't sound like a very modern solution...

Unprocessing is a possibility however limiting this while the cubes are being utilized seems prohibitive.

For the interim I have issued a script that will limit the memory of the cubes down to 1GB of memory during load times that do not pertain to the cubes, and then upping that when the cubes are loading. So far this seems useful, but I am having a hard time believing there is no intuitive way to locking the Cubes out from consumers when you can do that to every other single aspect of SQL Server.


Link to my blog http://notyelf.com/
Post #1247457
Posted Monday, February 6, 2012 9:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Today @ 1:48 PM
Points: 1,824, Visits: 3,496
Maybe I missed it, but can't you remove the user group/s from the SSAS DB and script this action and then add them back in and script this action. Then schedule the removal script to execute before the ETL stuff, do the ETL then have the addition script execute post ETL.


Steve.
Post #1247507
Posted Monday, June 24, 2013 11:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 7:00 AM
Points: 1, Visits: 10
Hi Steve,
Could you please attach sample script.
Post #1466979
Posted Friday, July 26, 2013 8:07 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 798, Visits: 2,457
I realize that this is an old post, but we had the same issue as this person states. To solve our issue, we dropped the role for all who had access to our cubes, processed as normal, then added them back.

Very similar to that of stevefromOz.
Post #1478020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse