February 3, 2012 at 4:48 pm
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/
February 6, 2012 at 3:53 am
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.
February 6, 2012 at 4:12 am
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
February 6, 2012 at 8:58 am
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/
February 6, 2012 at 9:46 am
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.
June 24, 2013 at 11:39 pm
Hi Steve,
Could you please attach sample script.
July 26, 2013 at 8:07 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply