When we use the Optimize Schema tool in the cube editor, a number of joins
between the fact table and the dimension tables are eliminated during
processing.

The SQL statements generated during cube processing (and Profiler shows no
other SQL statements being generated) seem to use only the fact table, with
no joins. However, detail from the dimension tables show in the cube -- but
we didn't join those to the fact tables!

From where is the dimension detail coming if we don't join to tables
(dimension) that contain the details?

TIA

"Optimize Schema" only applies to Shared Dimensions, and these are
processed separately from a cube by the Analysis Server. At that time,
the server stores necessary dimension detail info, which is identified
by unique member keys.

SQL Server BOL explain it this way:

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agoptimizing_1qr7.asp
During dimension processing, the Analysis server creates an internal
representation of the dimension data and hierarchy. When processing a
cube, the dimension member keys identified in the member key column
property are used to access the information in the internal
representation of the processed dimension. Under certain conditions, the
dimension member's foreign key in the fact table can be used for this
lookup, thereby eliminating the need to join the dimension table to the
fact table in the database query. This significantly reduces the
complexity of the query, the amount of data accessed in the relational
database, and network traffic between the Analysis server and the
relational database.
...

- Deepak

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

Deepak, that is an excellent answer.

Thank you so much.


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Also check out the Analysis Services Performance Guide - we explain the
optimize schema feature in some detail in this paper.
http://www.microsoft.com/technet/tre...e/ansvcspg.asp

HTH
Carl


"Rick" <b@b.net> wrote