Guidelines & Samples to help with Cube Build Times

[Shane]

Hi Everyone,

Im wondering if there are any Cube Building configuration recommendations or
samples available online.

We are trying to troubleshoot extraordinarily long build times for 1
instance. The instance has 400 + plans and is taking 12hours + :-(

Im curious to see if there is something we should be looking for, doing or
not doing that would help improve this performance.

Any help or direction is appreciated

Cheers,
Shane

 

[bikerjohn]

Microsoft has a downloadable booklet and a few videos for cube building. A
few things which may speed it up are:
1: try to run the cube build when nothing else is going on with the sql
server. If there are other sql jobs running, that will slow it down.
2: only add fields that you actually need to the build. If there are fields
that you don't need to report on, remove them from the build configuration.
http://technet.microsoft.com/en-us/library/cc197625.aspx

 

[Gary Chefetz]

Shane:

Does this instance have numerous task-level custom fields? How about long
project plans especially in terms of years duration? If so, this may be an
ongoing issue with SQL selecting bad execution plans. There are a number of
things that can help: 1) Recent Project Server updates 2) SQL Server should
be patched to latest SP levels, particularly 2005. 3) You should have good
maintenance plans running on the SQL databases. 4) In your cube build
settings, use earliest project start and latest project finish for the
timeframe parameters. If you've done all of these, you may need to
implement a custom execution plan.

 

[ben howard [MVP]]

In addition to both Biker's and Gary's excellent comments.... 12 hours is
big...
1. Check out msmdsrv on the sql server. It will grab memory, and decrease
it from other services... Find out what is happening on your server...
msmdrs might need more memory.

On my VM, I've got nearly 100 projects but the cube build takes 20 mins (and
this is a laptop).

SQL I/O is the big issue with MOPS so work on this first...

 

[Marc Soester [MVP]]

Hi Shane,

and at least I give you my 2 cents worth
It also depends on your hardware infranstructure. If your Analysis Services
is running on the same box than other applications this can also slow down
the build. You may want to consider investigating if your hardware requires
an update with AS having its own dedicated server.

In regards to the amounts of Project that you currently have, it seems very
slow, but it also depends on the amount of tasks that you manage within the
projects schedules. Somthing else to consider
Hope it helps

 

[Shane]

Hello Guys....

....And thanks for the input.

Here is some further information that will help answer some of the questions
in your responses.

Analysis Services and SQL Server run on seperate boxes.

There are no custom fields added to the build configuration.
The instance has 60 + task level custom fields

The project is selecting specific dates. I will run it for earliest to
latest to see if it will help.

The instance is running on SP2 + April CU
SQL is running on SP3 + CU #1

Some plans have very long durations that I will have addressed
E.g Some plans over 25 years

Thanks again for your updates. Plenty of food for thought.
We will investigate some of the points

If you have any thing else to add, please do.

Cheers,
Shane

 

[Gary Chefetz]

Shane:

Exactly as I suspected. The problem with selecting the date range rather
than earliest start to earliest finish, is the selection complexity of the
where clause in the select statement. Earliest start and Earliest finish
generates a simpler query in that it simply selects all. It is very
important that you run update database statistics on a nightly basis. Yours
is the rare kind of database that needs this high level of attention. If
you're running SQL 2005, make sure that you have at least SP3 applied.
Important performance fixes were included in CU10, which directly affect
this problem. Ultimately you may need to generate a static execution plan,
but see if any of the above helps first.

 

[Shane]

Guys -

Thanks for your comments.

We strecthed out the date range to earliest to latest and whilst this did
help (only a little) I have reiterated to the project to address the plan
durations as I believe this to be a major factor at play here (trust me some
of these are crazy). Thankfully this instance is a test drive for a big
project in the pipeline.

Thanks again for your help.
Shane