Processing SSAS 2005 Database Hangs

Hi,

After making changes to our development SSAS 2005 Database, I try to reporcess but it just seems to hang. Rebooting the server seems to fix the issue because once the server comes back up, I'm able to reprocess and see the progress immediately.

This server has SP2 installed.

Has anyone else run across this?

Thanks,

Brian

Maybe the was an lock on the database by you or an other user.

If there is an lock on the database this is the intended behavior.

In the analysis services samples there is an activity viewer where you could check for locks and sessions on the server.

Best Regards, HANNES

|||Thank you for the reply. Can you explain more on where to find the activity viewer that displays locks and instances?|||

It is a C# Sample Code Program you have to manually setup with SQL Server 2005 Samples.

By default the samples install to C:\Program Files\Microsoft SQL Server\90\Samples with the special location of \Analysis Services\Administrator\ActivityViewer for the analysis activitiy viewer.

HANNES

Processing Cube hangs

Hi,

I have a problem with processing my cube. My fact table (with telephone data) contains about 400,000 records... which is increasing rapidly (400,000 records is about 8 months of data)...
I have a few dimensions:
Dimension User: about 200 records
Dimension Line: about 200 records
Dimension Direction: 4 records
Dimension Date: 365 records for each year
Dimension TimeInterval: with 24 intervals

So far so good... when I process this dimension I have no problem....
However, when I add a dimension (CalledNumber, with exactly 101 records) the processing hangs as soon as it starts...

The SQL performed when processing the cube looks like this:
[CODE]
SELECT field1, field2,... fieldn
FROM table1, table2,.... tablem
WHERE
(table1.id=table2.table1id)
AND
(table2.id=table3.table2id)
...
[/CODE]

When I execute above SQL in the Query Analyser from SQL Server Enterprise Manager, it ALSO hangs...

I am not really suprised by that, because this SQL first create a huge table of 400,000 x 200 x 200 x 4 x 365 x 24 x 101 records and after that works through the WHERE statements to filter out the appropriate records.

for me it would be more logical to use the following code to process the cube, but that cannot be changed in Analysis Manager:
[CODE]
SELECT field1, field2,... fieldn
FROM table1
LEFT JOIN table2 ON (table1.id=table2.table1id)
....
LEFT JOIN tablem ON (tablem.id = tablem-1.tablemid)
[/CODE]

When I execute above SQL in the Query Analyser from SQL Servel Enterprise Manager, it does NOT hang, but performs the query in about 35 seconds....
But Analysis Manager does not allow me to change the SQL used for processing the cube...

What can I do to add more dimensions to my cube... (It will be more anyway after adding the CalledNumber dimension)?
any suggestions?

PS. forgot to mention: I use Sql Server 2000

For people who have the same problem... on another forum I got an answer (Thanks Darren Gosbell) which gave me the solution:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agoptimizing_1qr7.asp

ProcessAdd processing hangs

I'm trying to do an incremental process of a cube that has 3 measure groups. The first measure group is fairly straightforward, run-of-the-mill measures. The other 2 are distinct counts of an ID.

When running the incremental process (ProcessAdd), the status seems to complete, and then everything just hangs. I can't find anything that the server is doing, but it won't return. This happens in BIDS using debug, management studio wizard, and management studio XMLA execution windows.

Sometimes I can get the first measure group to return... Once the 3rd returned. The 2nd has never worked.

Thoughts on what's happeneing? Does anyone know how to see what's going on in the background? I have a SQL trace going and a trace on the SSAS server, and nothing seems to be going on...

Thanks!
-DougI have a similar issue occurring. I have an SSIS package that uses an analysis services task to incrementally update the most recent partition in three measure groups. It hangs everytime when run under the SQL agent Job engine, however, it processes fine through the SSIS debugger in BIDS.

I think I'm going to try submitting the XMLA statement directly from the job rather than going through SSIS. Did you ever figure out the issue you were seeing?|||

Try and run Profiler against Analysis Server and see if there is any activity is goign on.

Make sure you've got latest service pack installed. And lastly: if the problem still persists, contact product support services and report it.

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

ProcessAdd processing hangs

I'm trying to do an incremental process of a cube that has 3 measure groups. The first measure group is fairly straightforward, run-of-the-mill measures. The other 2 are distinct counts of an ID.

When running the incremental process (ProcessAdd), the status seems to complete, and then everything just hangs. I can't find anything that the server is doing, but it won't return. This happens in BIDS using debug, management studio wizard, and management studio XMLA execution windows.

Sometimes I can get the first measure group to return... Once the 3rd returned. The 2nd has never worked.

Thoughts on what's happeneing? Does anyone know how to see what's going on in the background? I have a SQL trace going and a trace on the SSAS server, and nothing seems to be going on...

Thanks!
-DougI have a similar issue occurring. I have an SSIS package that uses an analysis services task to incrementally update the most recent partition in three measure groups. It hangs everytime when run under the SQL agent Job engine, however, it processes fine through the SSIS debugger in BIDS.

I think I'm going to try submitting the XMLA statement directly from the job rather than going through SSIS. Did you ever figure out the issue you were seeing?|||

Try and run Profiler against Analysis Server and see if there is any activity is goign on.

Make sure you've got latest service pack installed. And lastly: if the problem still persists, contact product support services and report it.

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

Process task hangs running bcp

We are attempting to use Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00 to run a BCP command from a Process task. We are able to do so without a problem when running it interactively, but when run as a SQL Server Agent job the bcp process never completes and the job hangs indefinitely.

We also tried using a Script task for this, with the same result.

Has anyone else run into this problem?

Thanks,

Ron

What happens if you run it from dtexec? (i.e. from the command-line).

If it executes successfully using dtexec then its most likely a security issue.

-Jamie

|||

Jamie,

Thank you for your reply. Yes, the BCP processing works fine when I run the package interactively in Management Studio. However, I don't see how it could be a security issue, because the account that SQL Server Agent is running under is in the Administrators group on the server, and the BCP output is going to a share on that same server. I double checked, and the Administrators group has full control permission on both the share and the folder.

Is there anyone that has been successful in running BCP in an SSIS Process task under SQL Server Agent?

Thanks,

Ron

|||this link might help: http://support.microsoft.com/kb/918760|||

Thank you for the suggestion, Duane. In this case, though, the package executes, it just hangs on the Process task that executes the BCP command.

The security level of the package is DontSaveSensitive, and the Agent service account has full permissions to the file system, so it does not seem likely that lack of permissions is the issue.

Ron

|||

We stumbled upon the solution for this, but it does seem that a glitch in SSIS may be the root cause.

When running BCP in a Process task, the path to the BCP executable must be provided. When running a package interactively a UNC path name to bcp.exe works fine, but when run as a job the UNC path name somehow causes the process to hang indefinitely. Changing the notation from "\\<servername>\c$\Program Files\..." to "c:\Program Files\..." fixes the problem.

Is there any valid reason that this should be the case?

Thanks,

Ron

Process Hangs

the OK status is:

I build 12 fact table point to 12 partitions, and a father-son dimention. the dimention get to the fact table by the key column(son). I processFull,processUpdate the dimention, it run well. I processFull the cube or the partition, it run well too.

The Hangs status is:

I use the Process Dimention component in the SSIS. I chose the dimention, and use an ole db Source component point to the dimention table,and in it I use a query to chose the new row:"select from AgentDim where newdata=1". In the IDE, run the package, it's OK. Then I save the package to file system, and run it by Sql Server Job Agent.

Then it hangs, the job to run the package never stop. I stop the AS service and restart it. And I get into the Management Studio, and try to Process, then found it hangs.

I tried ProcessFull, ProcessAdd, ProcessUpdate, I tried deal with cube,dimention and any object, all hangs. It seem to AS is already dead.

God save me.

thanks.

Further Info: the query to chose the new row will return 0 rows.

Found Something, I think I found a bug.

The Hangs status happened when you do this:

The query to chose the new row returns 0 rows, that will make SSIS try to put a NULL set into AS,but AS don't know how to deal with such a NULL set, so it just keeps stop there.

This is really make me angry. We will get new rows everytime if we processAdd? Ofcause not.