MS Project Server 2002 and OLAP analysis Cube - performance?

[Ville Vakkilainen]

Hi,

I am running a system where we have Ms Project 2002 + SPS
+ Analysis Cube. We've been updating the cube twice a week
for a year now (approx 100 runs). So far things have been
going great, the average cube update time is somewhere in
the range of 30-40 mins.

Now suddenly, without adding a huge number of project
plans or any major changes (did change the resource pool
Enterprise Outline fields) the update is taking 2,5 hrs.
Anyone else met with a similar sort of behaviour?

I have reviewed the Server's SQL memory usage settings,
and they are set according to Microsofts recommendations.

Any suggestions where should I start looking?

Thanks,

Ville Vakkilainen

My e-mail is:
<for>ville.<nospam>vakkilainen@meridea.<remove>com

 

[Gary L. Chefetz \(MVP\)]

Adding Outline codes or a lot of Outline code values could certainly explain
this.

--

Gary L. Chefetz, MVP
"We wrote the book on Project Server
http://www.msprojectexperts.com

-

 

[Ville Vakkilainen]

Gary,

Perhaps I was not specific enough in my issue description:
I did not add _any_ new Enterprise Outline fields, just
modified Resources for an existing Outline field.

As far as I understand, the Outline code values should
also stay the same as I have moved person A from group X
to group Y and moved person B from Group Y to group X.

I have also performed similar changes earlier this year
with no significant effect on the cube update time.

Ville Vakkilainen
my e-mail:

 

[Gary L. Chefetz \(MVP\)]

Then I'd look for some cause of sudden bloat in the database. Maybe the
msp_web_assignments table, but it's difficult to say without playing
investigator and snooping around.

--

Gary L. Chefetz, MVP
"We wrote the book on Project Server
http://www.msprojectexperts.com

-

 

[Ville Vakkilainen]

Gary,

thanks for your advice.

The issue is resolved, altough I am not 100% sure what
resolved it. I went through multiple posts in this
newsgroup and found out about illegal characters in the
MSP_WEB_PROJECTS -table. Did remove some hyphens in the e-
mail field, but I am not sure whether this resolved the
issue or not. I am suspecting that this was not the case
since the cube was built succesfully 100 times with the
illegal characters.

But nevertheless, I can once again build the cube within
30 mins, which is acceptable performance.

Next thing I'll do is start digging in how to extend the
cube with weeks - I found out that you had the
instructions on your website once, but now they're gone -
any pointers for these?

Thanks,

Ville Vakkilainen

 

[Gary L. Chefetz \(MVP\)]

There's good stuff on MSDN about this.

--

Gary L. Chefetz, MVP
"We wrote the book on Project Server
http://www.msprojectexperts.com

-