OLAP Build Goes from 5 minutes to 6 hours once a month

[davidroybush]

We are running Project Server 2003 SP2a on server A, and SQL 2000 SP4
and Analysis Services on server B. The cube is scheduled to build at
3:30am daily. Normally the OLAP cube builds in 5-10 minutes. But for
the last 3 months at the end of the month the cube suddenly takes 3-6
hours to build! It will do that for about 3-5 days, then the build
time will go back down.

I have combed through the Event Logs and have found nothing. I set up
Perf Mon on both the app server and the database server. Performance
seems fine.

There have been no configuration changes to the Project Server 2003.

Any ideas, clues or suggestions would be greatly appreciated.

Thanks in advance for taking the time to read this,
Dave

 

[Gary L. Chefetz]

David:

This may be a problem with SQL choosing a bad execution path, but I haven't
heard this with SQL 2000. What are your cube build settings? Are you using
earliest project start and latest Project finish date? If not, these
settings might avoid this problem.

--
----------
Gary L. Chefetz, MVP
MSProjectExperts
Project Server Consulting: http://www.msprojectexperts.com
Project Server Training: http://www.projectservertraining.com
Project Server FAQS: http://www.projectserverexperts.com
Project Server Help Blog: http://www.projectserverhelp.com

 

[davidroybush]

David:

This may be a problem with SQL choosing a bad execution path, but I haven't
heard this with SQL 2000. What are your cube build settings? Are you using
earliest project start and latest Project finish date? If not, these
settings might avoid this problem.

--
----------
Gary L. Chefetz, MVP
MSProjectExperts
Project Server Consulting:http://www.msprojectexperts.com
Project Server Training:http://www.projectservertraining.com
Project Server FAQS:http://www.projectserverexperts.com
Project Server Help Blog:http://www.projectserverhelp.com










- Show quoted text -

Gary,
Many thanks for the thoughts. Indeed it seems the build settings are
not set for earliest project start and latest project finish date. The
settings are hard coded for 1/1/2008 to 12/31/2011. I will experiment
with adjusting those settings. Also, I have been advised to run SQL
Profiler to get a trace and see if that sheds any light on the issue,
so I am working with the DBA to make that happen.

After talking to the PMs and some users it seems that they scamble at
the end of the month to update timesheets for the month so PMs can
update schedules so someone in another department can get a Portfolio
Analyzer report that is needed. So all the updates for a month happen
during a few days at the end of the month and lots of data is
changing. I am beginning to think that pattern of usage is leading to
long build time at the end of the month. Then for the next 3 weeks
there is very little activity and the cube builds in 10 minutes. Do
you think that sounds possible?

Thanks again,
Dave

 

[Gary L. Chefetz]

The usage pattern certainly could be part of the explanation. What happened
when you changed the build settings?

--
----------
Gary L. Chefetz, MVP
MSProjectExperts
Project Server Consulting: http://www.msprojectexperts.com
Project Server Training: http://www.projectservertraining.com
Project Server FAQS: http://www.projectserverexperts.com
Project Server Help Blog: http://www.projectserverhelp.com

David:

This may be a problem with SQL choosing a bad execution path, but I
haven't
heard this with SQL 2000. What are your cube build settings? Are you using
earliest project start and latest Project finish date? If not, these
settings might avoid this problem.

--
----------
Gary L. Chefetz, MVP
MSProjectExperts
Project Server Consulting:http://www.msprojectexperts.com
Project Server Training:http://www.projectservertraining.com
Project Server FAQS:http://www.projectserverexperts.com
Project Server Help Blog:http://www.projectserverhelp.com










- Show quoted text -

Gary,
Many thanks for the thoughts. Indeed it seems the build settings are
not set for earliest project start and latest project finish date. The
settings are hard coded for 1/1/2008 to 12/31/2011. I will experiment
with adjusting those settings. Also, I have been advised to run SQL
Profiler to get a trace and see if that sheds any light on the issue,
so I am working with the DBA to make that happen.

After talking to the PMs and some users it seems that they scamble at
the end of the month to update timesheets for the month so PMs can
update schedules so someone in another department can get a Portfolio
Analyzer report that is needed. So all the updates for a month happen
during a few days at the end of the month and lots of data is
changing. I am beginning to think that pattern of usage is leading to
long build time at the end of the month. Then for the next 3 weeks
there is very little activity and the cube builds in 10 minutes. Do
you think that sounds possible?

Thanks again,
Dave