Olap Cube excessive processing time

B

Ben Tetreault

Hello, we are experiencing excessively long processing
time for the portfolio analyzer cube to regenerate. The
cube is scheduled to start daily at 1AM but finishes close
to 3PM!
The date range used is past 6 months with the next 24
months (ya I know it's a lot but that's what the users
want).
Can someone help me with understanding this long process
and why when regenerating it using analysis manager the
process only takes minutes?

Thanks
Benoit
 
L

Lars Hammarberg

Hi!

There's a step prior to letting the OLAP server grab the data in the
process:

The Views Notification service (VWNOTIFY.EXE in TaskManager) is responsible
for a lot of things - one of which is to retrieve data from the
MSP_VIEW_xxxxx tables and parcel that data out to the cube staging tables
MSP_CUBE_xxxx in time-phased, day-by-day form. This can take quite a long
time - if:
1. The date range is wide with lots of tasks - or more to the point - a
large number of assignments.
2. There's a LOT of time-phased data specified - e.g.: baselines 1-10 all
used, actual data for every assignment, lots of assignments spanning the
entire date range, etc.
3. The service is being interrupted by projects being saved/published during
the process (vwnotify is also responsible for the Publish to MSP_VIEW_xxx
bits...)
4. All services are running on the same machine - specifically the Views
Notification Service not running on a dedicated server, as is quite
possible - you should look into this - check Technet for more info)
5. The database server is running other applications or db-apps hogging cpu
time.

When the Views Notification service is done, the OLAP server finally gets to
do its stuff with the data in the MSP_CUBE_xxx staging tables - this part
will probably not take many minutes and can just as easily be done from
within the Analysis Manager if you want to tweak the process or simply to
check up on the data interactively.
 
M

Matt S.

You can see poor performance with OLAP cubes when you hardware is not
up to par for the amount of work it needs to accomplish. Also if everything
is installed on the same box, you will see times like that.

If your SQL server is anything less the 1.0 GHz Dual, then cubes will take a
LONG time to process. I have done a number of performance testing
against this product in various environments so I know what needs to be
upgraded and what doesn't. And Project Server on the SQL side, Microsoft
always recommends a Quad proc that is 1.0 GHz or higher.

Plus the hardware being used on the server running the Views Notification
Service! This process needs to be big! as the VWNOTIFY service hogs the
majority of the process. But be warned this process is only a single
threaded service, so having a dual proc is not going to help you.

Plus, it's usually not best practice to build OLAP cubes every day. There just
isn't any point, unless your users are constantly updating time and the PM's
are accepting time on a daily basis. If people do these tasks once a week,
then there is no need to do daily cube builds, because you'll just be
displaying the same information, until that one day of week that everybody
does their updates.

Hardware plays a big role with Project Server performance!

if you want to go deeper, shoot me out an email and we can discuss off to
the side.

Matt S.
Project Server Specialist
[email protected]
 
B

Ben Tetreault

Thanks for your answer Lars.
I know that the date range doesn't help in reducing the
processing time but still, should it take that long when
there is nothing else running on the project server at 1AM
in the morning and the fact the SQL server is also
dedicated to MSP?

Ben
 
B

Ben Tetreault

Hello Matts.
The 2 servers (project server and SQL server) are Proliant
G3s with quad cpus at 2.4 Ghz.

I agree with you that the generation could, and should be
done weekly even if our PM change their projects often and
actuals are entered regularly.

My problem will be to convience the users of a weekly
generation.

Thanks.
Ben
 
M

Matt S.

Actually the date range does help in reducing the processing time. The
larger the date range the more data needs to be process and transferred to
the cube.

You really need to find where the bottleneck is on these builds. I suggest
doing performance monitoring on the two machines. Monitor CPU usage,
monitor the NICS.

I have seen with some of my clients where when you do a schedule OLAP
cube build, the scheduling engine for Project created two XML files (two
cube builds) which was causing the process to take so long. You might
want to check how many XML files are getting generated during a schedule
build.

But I would do some monitoring of the two boxes during a cube build, to
see where the bottlenecks are. It might be the NIC card, I know I always
recommend dual (full-duplex) 100 Mbps cards in the SQL box. Setup for
inbound and outbound traffic.

Matt S.
Project Server Specialist
[email protected]
 
Top