SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporarily disable users access to cubes


Temporarily disable users access to cubes

Author
Message
shannonjk
shannonjk
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 840
Greetings all :-D

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

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/
bulbanos
bulbanos
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1747 Visits: 4495


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


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
shannonjk
shannonjk
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 840
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/
stevefromOZ
stevefromOZ
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: Moderators
Points: 3632 Visits: 3757
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.
avi9nash
avi9nash
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Hi Steve,
Could you please attach sample script.
Brandon Carl Goodman
Brandon Carl Goodman
SSC Eights!
SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)

Group: General Forum Members
Points: 895 Visits: 2718
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search