When would you use a shared dimension instead of a cube-specific dimension?
If I have multiple partitions, would the shared dimension make things
faster?

Thanks-
Malcolm

Malcolm,

You may want to use shared dimensions to reduce the number of similar/
duplicate dimensions, in AS 2000 each dimension takes up memory, so if you
have duplicate dimensions you reduce the amount of free memory. Shared
dimensions also reduce maintenance, e.g. you only have to process it once.

On the other hand remember that if you need to do a full re-process you will
take down all cubes that it is shared among. Effectivly increasing
maintenance.

AS for shared dimensions making things faster if you have multiple
dimensions you would have to specify 'things' but in the main, no, they don't.

An important factor is that cube schemas can only be optimized for
shared dimensions. This can significantly speed up cube processing for
large fact tables:

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agoptimizing_1qr7.asp

In many situations Microsoft® SQL Server™ 2000 Analysis Services can
optimize a cube's schema to significantly reduce cube processing time by
eliminating joins between dimension tables and fact tables.
...
Certain conditions must be met for Analysis Services to eliminate a join
between a dimension and the fact table. These are:

The dimension must be a shared dimension, and must have been processed
before you optimize the cube schema.

The member key column for the lowest level of the dimension must contain
the keys that relate the fact table and the dimension table. This must
be the only key necessary to relate the fact table to the dimension
table.

The keys in the member key column for the lowest level of the dimension
must be unique.

The lowest level of the dimension must be represented in the cube, that
is, the level's Disabled property must be set to No. The level can be
hidden.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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