SSAS Cube that hangs when run via SQL Agent

Question

Good day,

I have cube that is executed via SSIS -> dimensions & partition runs fine on the server for 15min.

But when i execute the SSIS pkg to process cube via SQL Agent, it hangs and dont complete.

I have nolock on all views & it runs fine, what can i do?

Please Help

Answer 1

A number of questions I guess:

1. When you say "it hangs" what is "it"? Does the job/package never complete?
2. If it is the job/package that never completes, can you see from the log what task is the last to start/end execution?
3. How are you processing the dimensions and partitions in SSIS? Are you using the dedicated task for this or have you written your own script? (i.e. Could there be an interactive prompt?)
4. You mention about NOLOCK on all the views etc. What makes you think that it is the DB that is causing the problem? (i.e. What debugging have you already done?)

---

http://bi-logger.blogspot.com/

Answer 2

Thank you for assisting.

1. it never completes in Agent, i have to stop job then run via BIDS

2. Yes, the previous job also runs a pkg that updates a cube but that is fine.

3. via ssis, done both methods: 2 process ssas task (one for dimension first then partition) and the other a full process

4. i done some optimization & only made sure that the views have nolocks added, i dont know how to debug the agent

in bids it runs fine, 10 min but agent no reason no error description.

I have added a measure to the pkg recently, but it was simply new col from existing fact table - nothing major, but now it fails every morning

Any ideas?

Answer 3

Can you run SQL Profiler against the AS server and see if it ever gets to processing or stalls during processing or maybe completes but the package stalls later or...

---

http://bi-logger.blogspot.com/

Answer 4

Im new to SQL Profiler, how would i test when it runs on the agent or do i check while the pkg runs in bids or is it query preformance as

i use database engine using advisor

Answer 5

Ah, no, you use SQL Profiler to monitor SQL Server and/or Analysis Services. In this case you will want to monitor what Analysis Services is doing, so you would start the SQL Profiler application and connect to your Analysis Services server. Choose some events to monitor and start the trace.

For a bit more info see: http://technet.microsoft.com/en-us/library/ms174946.aspx

---

http://bi-logger.blogspot.com/

Answer 6

Thank you, a bit embarrassed – I was wondering was there no tool to monitor ssas

Answer 7

such a lot of events & columns what do i look for?

Answer 8

Thank you, a bit embarrassed – I was wondering was there no tool to monitor ssas

Lol, no problem at all. We're all learning, just at different stages of that process. :-)

Well, I guess you want to select the command, error and progress events. If you can prevent queries against the cube whilst performing the test that would be best as the output will be clearer. So what you would expect to see is a "command begin" command event followed by a "command end" event for each processing command. If you don't see any of that then AS is not even getting the command. Try running a trace configured like this and manually process what you are trying to process. Stop the trace and look at the output. Then clear the trace and run the job. How do the traces differ?

---

http://bi-logger.blogspot.com/

Answer 9

The job now completes with me moving the task to the last step within the job.

The SQL Porfiler trace did not work as it monitors all cubes that users are working on, but nothing when i processed the cube the trace picked up nothing while cube process.

Thank You for helping, Last Question: Is there a tool that can analyse the processing of a cube in BIS/SSAS?