Clever Geek Handbook

How can I programmatically check if an SSAS database / cube is being processed?

I have an application that will update a small dimension and rebuild a group of measures that are part of a larger cube.

Cleaning in the morning can take an hour or two. I want to check the application if there is any processing in that particular database and send a message to the user that they will need to wait.

The AMOs I have studied have a State property , but this appears to reflect the current status and does not seem to know what processing might occur. I assume this is because the objects are replaced when processing is complete.

The only possibility I can see right now is to run a custom trace behind this MSDN article , have it run a bit and check if there are any progress events.

I think, for obvious reasons, this is not a reliable solution to my problem. Is there a better way? I was hoping for something as simple as the IsProcessing flag on the database object.

Another potential idea is to query the active DMV session and see if the command has the text "Process". I still don't feel solid.

All advice is appreciated.

+3


source to share


1 answer


Probably the best way is to see if there is a processing lock. I haven't tested the various LOCK_TYPE values ​​a ton, but I think if this query returns any rows, your cube is processed.



    SELECT *
    FROM $SYSTEM.DISCOVER_LOCKS
    WHERE LOCK_STATUS = 1
    AND LOCK_OBJECT_ID = '<Object><DatabaseID>YourDatabaseID</DatabaseID></Object>'
    AND (
     LOCK_TYPE = 2
     OR LOCK_TYPE = 4
     OR LOCK_TYPE = 10
     OR LOCK_TYPE = 16
    )
+2


source






More articles:


All Articles