How to determine which SSAS processor is now being processed?

Question

There is a problem when several users can process the same cube at the same time and as a result cube processing fails. So I need to check if a specific cube is currently processing.

Answer 1

I don't think you can prevent the cube from being processed if someone else is processing it. What you can do for “help” is to run an MDX request to check when the last cube was processed:

SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES 

or check the sys.process table on the realt sql server to make sure it is running:

 select spid, ecid, blocked, cmd, loginame, db_name(dbid) Db, nt_username, net_library, hostname, physical_io, login_time, last_batch, cpu, status, open_tran, program_name from master.dbo.sysprocesses where spid > 50 and loginame <> 'sa' and program_name like '%Analysis%' order by physical_io desc go 

Answer 2

use this code to select running processes: (execute this in OLAP)

  select * from $system.discover_Sessions where session_Status = 1 

And this code to cancel the running work! Please change the PID to run SESSISONS_SPID as in the example:

 <Cancel xmlns ="http://schemas.microsoft.com/analysisservices/2003/engine"> <SPID>92436</SPID> <CancelAssociated>1</CancelAssociated> </Cancel< 

Answer 3

I had a similar problem and resolved it using the DISCOVER_LOCKS rowset provided on the SSAS server. To do this, first make the following XMLA request to the server:

 <Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DISCOVER_LOCKS</RequestType> <Restrictions> <RestrictionList> <!-- I tried using this, but it wasn't working and Microsoft documentation was little help. --> </RestrictionList> </Restrictions> <Properties> <PropertyList> <Catalog>My_SSAS_Database</Catalog> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> 

The XML returned by the server should have a bunch of row elements that look something like this:

 <row> <SPID>388303</SPID> <LOCK_ID>0CC320DB-6B71-4341-B484-8D0A6C403AB9</LOCK_ID> <LOCK_TRANSACTION_ID>335C5EE8-83C3-44D5-A653-655F933A0D2D</LOCK_TRANSACTION_ID> <LOCK_OBJECT_ID> <Object> <DatabaseID>My_SSAS_Database</DatabaseID> <CubeID>My_Cube</CubeID> <MeasureGroupID>My_Measure_Group</MeasureGroupID> <PartitionID>My_Partition</PartitionID> </Object> </LOCK_OBJECT_ID> <LOCK_STATUS>1</LOCK_STATUS> <LOCK_TYPE>4</LOCK_TYPE> <LOCK_CREATION_TIME>2014-02-04T22:22:07.71</LOCK_CREATION_TIME> <LOCK_GRANT_TIME>2014-02-04T22:22:07.71</LOCK_GRANT_TIME> </row> 

In this example, notice the Object element in LOCK_OBJECT_ID and LOCK_TYPE . According to Microsoft documentation, LOCK_TYPE of 4 indicates a write lock due to a processing task. Thus, you should find out if the cube is processed if LOCK_TYPE is 4 and LOCK_OBJECT_ID/Object/CubeID matches the identifier of the cube of interest to you for this row element in the query results.

Answer 4

Probably the best approach to those already listed would be to use the SQL Server Profiler to view activity on the analysis server. As already noted, the current popular answer has two drawbacks, the first option shows only the LAST time when the cube was processed. And the second option only shows that something is working. But it doesn’t tell you what works, and what if your cube did not process the SQL server, and another data source?

Using SQL Server Profiler will tell you not only that something is being processed, but also about the processing details. You can filter most events. Check out the Current Events Report if you want to receive real-time information ... Usually this is too much of a fire hose for data to get real information, but you know very well that at least the process is ongoing. Observe the Startup and Shutdown Report events only for more detailed information, for example, about what is currently being processed, up to the section levels. Other events with good information include Command Begin / End and Query Begin / End .

Answer 5

You will see a task running in the task manager called "MSDARCH" if the cube is being processed. Not sure how you can determine which one.