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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 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
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4323 Visits: 4514


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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 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
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: Moderators
Points: 9764 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
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 10
Hi Steve,
Could you please attach sample script.
Brandon Carl Goodman
Brandon Carl Goodman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1154 Visits: 2731
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