hello everyboy,

we have set up a (MOLAP) Cube, with MS analyses services,
using a Datamart on SQL Server 2000
(on the same maschine), and everything works fine, except
the time of calculating the cube.

The Maschine: Dual Xeon 1.7Ghz, 4GB RAM, W2K Server
SQL Server 2000 SP3
Analyses services with SP

Fact Table: data of 14 days, with ~3million rows, thats
not so much
22 Dimensions,
23 Mesures
11 Calculated members
no special User security

the cube holds all real estate objects of our customers of
every day.

there is one Dimesion, the "Unternehmen inkl Objekte", for
the name of companies
and their employees AND the Objectnumber of the Real
estate objects from the fact table, and therefor the
dimension is joined with the fact table
(and the time), because its necercerry to show the object
id as a level in this dimension
I think that this decreases the performance, but why?
When the cube is calulating, with 15% (140 agg.)
aggragations, it needs 62 hours !!
with 6% (thats 9 agg) aggrgations, it needs 2 hours.
The SQL Server and the number of recordsets is not the
problem.
The servers are strong maschines....so...cann anybody tell
us:

1.) is this special dimension the main problem
because ...and is there a solution?
2.) if this special dimension is ok, what performance
counters can we view to find problems of analayses
services?
(we have a fast Raid5 system, 4gb ram, xeon
CPU,.so i don't mind that we have a hardware problem)

here are 2 screenshots of the cube editor, and the
special "Unternehmen inkl Objekte"- dimension.
(you can see the joins, and the level ObjektNr)
http://www.immobilien.net/CubeEditor.gif
http://www.immobilien.net/DimensionEditor.gif

thanks for ANY help in advance, and sorry for my bad
english ;-)
Thomas Gugler.
www.immobilien.net

Thomas,
looking at your cube design, I noticed some things which
might be part of the problem.

1. dimension tables should be linked to fact tables by a
single join only.

2. You seem to have a lot of dimensions. Maybe you could
merge some of them to form a simplified star scheme.
Creating a junk dimension could be helpful. Also make
sure that, whenever you do not need an aggregation
between two similar dimensions, to use a dimension
hierarchy.

Regarding your system: Have you set the 3GB switch?
Otherwise your memory will not be used. A dual processor
system also will not help too much with speeding up
processing as far as I know.

I' d look in the dimensional design and change something
there.

HTH Lutz

Try increasing the values of Read-Ahead Buffer and Process
Buffer size in "Processing Settings" tab.

(Right Click Analysis Server Name from the Analysis
Manager and select Properties)

Cheers,
Sanka

Can you check how long cube processing takes when you remove the
"special" dimension? It could well be creating performance problems,
apart from the fact that including the fact table in a dimension is
usually not a good idea. It is not clear why the Time table is also
included in the schema. Also, how many "Object Numbers" are there, and
is the fact table indexed on "Object Number"?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Thomas Gugler" <tg (AT) immobilien (DOT) net> wrote

A couple of things to try -

These are all shared dimensions - make sure the cube is optimised and
the read will be a lot faster.

I've had similar OLAP performance hits where a dimension was built
using more than one table, especially when one table does not join
directly to the fact table.
eg: Fact Table --> Customer Dimension --> Account Attribute Dimension.
Denormalising some of your dimension tables so that each dimension is
contained in a single table ( or similar dimensions are combined )
will decrease the number of joins, and also remove the longer join
paths from the SQL extract.

Adam

Hi,

Also check:

Optimizing Analysis Services to Improve Processing Performance
from

http://www.microsoft.com/technet/tre...hNet/ProdTechn
ol/SQL/Maintain/Optimize/ANSvcsPG.asp

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."