How to create a SQL Server Agent job to process IT Analytics Cubes in ITMS

Products

• IT Analytics
• IT Management Suite
• Client Management Suite

Issue/Introduction

When processing an IT Analytics cube or report, the error "Exception: A task with Guid <GUID> is already running." appears in the IT Analytics Event Viewer, and the cube or report fails to process. There is no task execution overlap, each scheduled cube processing is failing.
 

Environment

ITMS 8.x

Cause

This is a known issue which is being addressed in the next release of IT Analytics, version 7.5.  The cause of the issue is that all processing tasks use the same GUID.  So when a scheduled processing task kicks off while another one is still running or appears to be running according the Symantec CMDB, this produces the error "Exception: A task with Guid <GUID> is already running." 

Resolution

If the issue is reproducible quite often, following workaround can be applied:

Create a SQL Agent Job to process the cubes with the related dimensions:
1. Open MS SQL Studio.
2. Connect to SQL Server Analysis Service
3. Right click the cube and select “Process”.
4. Right click the cube in the list and select “Add Related Dimensions”.

 

5. Verify that the cube is listed with a process option of “Process Full” and all of the related dimensions are listed as with a “Process Update” process option.

Note:  Cubes are always set to Process Full.  Dimensions should be set to Process Update for agent jobs.  The only time they should be set to Process Full is the first time you manually process a cube.  If you fail to process the dimensions as Process Full the first time you will have data problems.  Doing a full process of the dimensions in an agent job will also cause data problems.

 

6. Click Script in the upper left.  It will open a new query with the script you need to build the agent job so that it processes both the dimensions and the cubes as below.  Keep the query tab open as the code will be copied during the creation of the SQL agent job. 

7. Verify that the SQL Server Agent service is started and is set to start automatically.
8. Create a new SQL Server Agent Job

9. General:  Add Job name.

10. Steps:  Click New to add the script

11. Populate following fields:

Step Name: Processing Cube and Dimensions
Type: SQL Server Analysis Service Command
Run as: SQL Server Agent Service Account (default)
Server: your.analysis-server.fqdn
Command: (Copy and paste the script produced in step #6)

12.  In the “New Job Step” window, under the “Advanced” tab, set "On success action" field to "Quit the job returning success”.  Click Ok.

13. Add the job execution schedule

14. Right click on the job -> Start Job at Step to test it.  If the Job is failing, make sure you have a linked server connection to Symantec_CMDB. If you don't have it, you can add it from the SMP under Console -> Settings -> Notification Server -> IT Analytics Settings -> Connections -> Symantec_CMDB.  In the External Symantec CMDB Connections section click Add.