Determine status of processing cube

From C# I need to determine if a cube is currently processing. We are trying to avoid running MDX against a cube while in the act - it results in an error message to our users.

Does anybody know of a way to do this? Maybe with AMO?

Thanks in advance,

John

Hello John,

There is no easy way of doing this in AMO, but you could use the DISCOVER_LOCKS schema rowset to test whether there are write locks on your cube. The LOCK_STATUS and LOCK_TYPE fields returned by this discover could help you find this out.

Hope this helps,

Artur

Detecting processing in progress

Is there an easy way through AMO to detect whether a cube (or database, or dimension...) is currently being processed?

I've tried registering for trace events, but that doesn't seem to be a very reliable way to detect an in-progress process - the gap between events are unknown, so I don't know how long I would need to listen for an event to be able to say definitively that something is being processed. Any other ideas?

I'm not aware of anyway of doing this. You can use performance counters to see if *something* is processing, but you can't see which object. You can use AMO to see when something was last processed, or if it is unprocessed and needs re-processing. But I can't see anything that would tell you if anything is currently being processed. Tracing seems to be one of the only ways of doing this, but you would almost need to write a service that could watch for the start, stop or cancelling of processing jobs in order to be able to figure this out reliably.

To look at this from another angle - why do you need to know? If something is already being processed, I would expect you should get a meaningful error stating this if you tried to process it again. And in most cases, end users should not care that something is being processed, they will just see the new data once the processing transaction has been committed.

|||Darren,
Knowing that *something* is processing is actually good enough for our needs right now. Perfmon counters are a great suggestion - I didn't think to look there. Processing Pool Busy threads seems accurate enough for our needs. I wish there were a counter for "Active Transaction", which would be even more useful for us, but it doesn't look like there is.

Our needs are somewhat unusual - we've actually built a product on SSAS, and our QA department needs to know when processing is in progress to avoid stepping on each other's toes while testing. I think you've given me what I need for now. Thanks!
|||

Well if you use a certain userid (ESP, SQLSERVER, or make a unique one) for processing, you could use the XMLA Discover command for DISCOVER_SESSIONS. If the userid is returned, something is processing.

We do something similiar. All jobs that process any cube use a specific userid.

|||

Yeah, DISCOVER_SESSIONS would be useful, there is also DISCOVER_TRANSACTIONS that might be worth a look.

<Discover xmlns="urnchemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_TRANSACTIONS</RequestType>

<Restrictions>

<RestrictionList></RestrictionList>

</Restrictions>

<Properties>

<PropertyList></PropertyList>

</Properties>

</Discover>

|||Thanks fellas. I think between DISCOVER_TRANSACTIONS and DISCOVER_SESSIONS I've got the info I need.

Designin and Processing Cubes

Does anyone have any best practices for processing cubes on a prod environment? If we have 1 server which we have the production cube on, what is the best way to get the changes into the production cube. I tried a backup and restore of the AS DB with no success. Should I just create the database on a dev server and keep prod and dev separate? Any assistance would HELP BIG TIME!!!

The ultimately the question is: what configuraion you can afford to have. The better practice many Analysis Services users have found is to have 2 servers: Staging and Production. You were to maintatain your production server for users to query and during late night you'd use Sychronization feature of Analysis Services to push a new state from the Staging server. On the Staging server you process updates into your dimensions and partitions, you do your backups and test whether the data is correct, you test your security in short you make sure you get your cubes ready before user can see them.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.