February 7, 2003 at 2:37 pm
Processing of the cube returns an error message after some intensive work for about two minutes:
Timeout occurred.; Time:2/7/2003 1:23:32 PM
It happens while creating aggregations. The connection timeout is set to 15 and I cannot change it: it sets back to 15. Nevertheless, it takes longer than 15 seconds.
Any help is greately appreciated.
Michael
February 7, 2003 at 3:56 pm
What service pack are you running? SP2 was supposed to correct the resetting by Analysis Services of the connection timeout property but my experience has been the same as yours...you change it to something other than 15 seconds and it resets itself back to the 15 seconds. However, installing SP2 corrected the timeout problem for me. I am now running SP3 and have no problems. Also, you could try processing all of your dimensions first before processing the cube. Last, if possible, set the connection timeout property to 0 (unlimited).
If you right click on your analysis server in Analysis Services and select properties, processing, try unchecking the server timeout checkbox (if you haven't already done so). Then I would suggest researching the read ahead buffer size and process buffer size properties as these can greatly influence processing times. If I think of anything else, I will add to this thread at that time. Hope this helps...
Michael
Michael Weiss
Michael Weiss
February 7, 2003 at 3:59 pm
Oh yeah...have you optimized your cube? By this, have you dumped the joins between the fact table and the dimension tables and set the leaf level keys to unique (assuming that you have all foreign key values in place in your fact table)? This can greatly speed up processing times...
Michael
Michael Weiss
Michael Weiss
February 7, 2003 at 4:55 pm
Thanks Michael. I am trying all these things. It seems to be helping but we are still to see the end.
What did you mean by suggesting to remove joins? Did you talk about joins in the inderlying warehouse between fact table and dimensional tables or you meant joins when creating a cube in the Analysis Services?
Michael
February 7, 2003 at 5:29 pm
I was referring to the joins between the fact table and the dimension tables in the Analysis Services Cube Editor. If all of your fact table data contains the correct foreign key values and your dimension leaf levels are set to unique keys, you can remove the joins between the tables in the Cube Editor and this will speed up the cube processing. Search Books On Line in SQL Server for 'Cube Optimization'. Good luck! Please let us know what you come up with if you hit upon a solution...
Thanks,
Michael
Michael Weiss
Michael Weiss
February 7, 2003 at 6:03 pm
WHEN I INCREASED THE TIMEOUT INTERVAL FROM 30 TO 60 SECONDS, IT STARTED TO WORK WELL. I ALSO INCREASED THE BUFFER SIZES TO GAIN PERFORMANCE.
However, if I remove a relationship in the Analysis Manager and try to save the cube, it gives an error message:
The following tables are not joined to the table dbo.tbl_fact_assessment. Please create the apropriate joins.
It seems the join in their is required. I am actually not sure how it may affect the cube calculation time (HOLAP that is) since the database is not used after the data is read from it into a mulidimensional structure. So why relationships whould matter?
February 7, 2003 at 9:28 pm
I am not sure on the relationships question but will look for the article I read regarding that. Have you checked the article(s) on this site regarding cube optimization? If not, look at the one titled 'Optimizing Your Cube's Schema' by Don Church. Also, check Microsoft Knowledge Base Article Q221539 regarding timeout settings in SQL Server 7 OLAP Services...may be that you can get some insight there too.
Michael
Michael Weiss
Michael Weiss
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply