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

Question

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

The morning build can sometimes take an hour or two. I want to check from the app if there is any processing going on in this particular database and send a message back to the user that they will need to wait.

The AMO objects I've examined all have a State property, but that only seems to reflect the current status, and seems ignorant of processing that may be going on. I assume this is because the objects are replaced when processing is complete.

The only option I am seeing right now is to start a custom trace per this MSDN article, let it run for a bit, and check if there are any progress events.

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

Another potential idea is to query the active session DMV and see if any have a "Process" text in the command. Still doesn't feel solid to me.

Any and all advice appreciated.

Answer 1

Probably the best way is to see if there's a processing lock. I haven't tested the different LOCK_TYPE values a ton, but I think if this query returns any rows, your cube is being 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
    )