Analysis Services 2000 Cube Optimization

[Chase_Idol]

Hi:

I'm attempting to build my first cube with a large amount of data - all based upon a MSSQL Server DB. My cube seems to want to run forever - the last try was over eighteen hours!

The dimension seems to build properly, but the actual cube processing / building seems to be the part that is taking so long. Whaty can I do to make this whole cube process in 2 - 3 hours?

A simple fix will be appreciated ...

Chase

[rmiao]

There is no simple fix. How much data involved? How many dimensions, elements, aggregations? Any index in tables? Run AS on same machine with sql? What kind of hardware? Memory, cpu, disk placement? ...

[Island1]

Here is what I would do to get started diagnosing this in your position. There are, indeed, myriad variables, but this simple approach tends to help one at least get started isolating the issues in a model. (I recommend it to clients that call with similar "initial start" issues, where multiple dims are involved, etc. ... )

1. Create a copy the cube you wish to optimize.

2. Cut out all except one dimension: the one you least suspect of inefficient / otherwise encumbered processing. ("Time" / "Date" might be good bet ...)

3. Perform a full cube process.

4. Gradually adding back the dimensions, each within an independent processing cycle, process until you find inefficiencies / other issues.

(Remember that you can simply move dims out of and into the cube - without modification - as you progress, so no worries as to loss of work, etc.)

5. Remove inefficient / otherwise encumbered dims from the production cube and work to resolve them separately, if that is helpful - perhaps even in another clone of the cube, where you can isolate the details and stats you need to resolve the issues ...

As a fairly reliable (although not infallible) rule of thumb, dims with large member populations / numerous hierarchical levels / underlying intake SQL calculations, etc., tend to warrant closer examination from the outset.

Keep in mind the appropriate settings within the space-time optimizer (I have an article in my AS series on that ... see below ...). Make sure you have selected a sufficiently high-space option, etc.

These are just places to start, of course, and will likely raise more questions as you progress. Let us know what you find out … drop us a note on your specific questions as you isolate them, and we'll try to help you along.

Oh, and as Malcom Forbes said "With all thy getting, get understanding ..." Tons of info awaits you out there (a good start was the expertise concentrated at Database Journal!): if you can gradually assimilate it, you can develop in greater and greater confidence!

Best of Luck!

Bill