Post reply

How can we lock out users during cube processing?

  • November 9, 2005 at 8:05 am

    #152615

    Every morning we process an incremental update into our production cube that includes the activity from the prior day.  Sometimes the analysts log into the cube before it finishes processing the prior day's activity, which causes problems.  We want to effectively prevent access to the cube until the processing has completed so that when the analysts log into the system they know it includes the latest data.

    We are using SQL Server 2004, SP4.  We also use our own VB executable to process the partitions in the cube.  Finally, we have 2 'actual' cubes and a virtual cube linking the two actual cubes together.  The analysts access 1 actual cube and some analysts access the virtual cube at this time.

    My thoughts are to build an executable that turns the visibility property of the actual cube to 'off' until we have finished processing the cube.  This property does not apply to virtual cubes, however, I found that when the actual cube is invisible we cannot see the data as long as the connection to the cube did not exist prior to changing the visibility property.  Then after we finish processing we would reset the visibility property to 'on' and this would allow the users to see the data.

    Questions are:

    1) Has anyone else solved this problem, and if so how have you done it?

    2) How can we disable the virtual cube, i.e., make it appear as if it has not been processed?  (I would be willing to reprocess the virtual cube since it only takes about 1 to 2 minutes tops.)

    Thanks in advance for your help.

     

    Thanks for your help.  We solved our problem a little bit differently.  We are going to use two virtual cubes versus one actual cube and one virtual cube.  Using DSO we are going to add a bogus measure to the virtual cubes and then delete the measure from the virtual cubes.  When a measure is deleted it appears that the virtual cube is no longer processed.  This means no additional queries will hit the cubes.  I'd still like to hear any other alternatives if someone has them.

     

    Dave

    Regards,

    Dave Doyle

  • November 10, 2005 at 3:03 am

    #603179

    Dave, you haven't said how the analysts log into the cubes.  I don't have direct MS DW experience but am familiar with others. 

    Some of the following may be of use to you.

    If you are using any sort of inhouse developed front-end, could you not amend that front-end to interrogate some control data/process and allow/disallow access via that.

    Alternatively, suspend/cancel access rights for the analysts before processing and reenable/create access rights after processing.

    3rd option....rename the cubes/database to somewhere safe while processing and move back afterwards.

     

    Alternatively...let their bosses know of the cost of their impatience, both in terms of operating with imperfect/incomplete data and/or send them the bill for the recovery tasks!  Strange thing - money...we all want it...except getting a bill for money has the opposite effect!

    Good luck with the quest.

  • November 10, 2005 at 3:34 am

    #603191

    Have two network cards in the machine, one external facing for users and one internal facing for administration.

    When you want to prevent access write a script to shutdown the client facing card

    Mark


    mark hill

  • November 12, 2005 at 12:28 pm

    #603550

    The way we do it is to have 2 (two) servers. One for processing/reconciling and the other for end-user querying. In AS2000 it means having 2 servers, however in SSAS 2005 you can have more than one instance running in one server (make sure that your server can cope with running both instances!).

    Hope it helps.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply