Hi,

I am having a problem where I seem to be forced to process every row in
my Date Dimension regardless of whether it is joined to my fact, and
this is also exploding the number of records needing to be processed,
as though there is some sort of Cartesian join. But when I paste the
query Analysis Services is genererating into Oracle, it produces the
correct record count. Additionally I have tried filtering within the
Date Dimension, by doing an explicit join in the Dimension to the Fact
table and specifying in the Dimension filter where the
Date_Dim.Date_Key is not null. This was the only way I was able to
solve this problem in another cube I designed, but it's not working in
the current one.

My understanding is that the cube's execution should be sparsely
populating the cube, and not trying to process dimension rows for which
there are no fact table joins. Am I misunderstanding? How can I filter
these empty rows (I know I can filter in mdx after populated) from my
resulting cube, and reduce processing time by not processing those
irrelevant rows?

Thanks in advance for your assistance!

Assuming that you are using AS 2000, is your Date dimension a private or
shared dimension? And how many rows are in the dimension - if the
leaf-level is at the day level, the processing shouldn't take too long
(unless the dimension spans centuries)? You can use a range in a view to
limit this, rather than joining to the fact table.

Also ensure that the cube schema is optimized for the Date Dimension, to
minimize cube processing time:

http://www.microsoft.com/technet/pro...ntain/ansvcspg.
mspx

Published: June 1, 2003
By Carl Rabeler, Len Wyatt, Dave Wickert

Summary: This paper describes techniques you can use to optimize query
responsiveness and processing performance in Microsoft® SQL Server™ 2000
Analysis Services.
...
Eliminate Joins between Fact and Dimension Tables

However, if a dimension member's foreign key in the fact table can be
used rather than the dimension member key in the dimension table, much
simpler queries can be generated that can speed the retrieval of data
from the relational tables. A dimension member's foreign key in the fact
table can be used in the processing query if the dimension meets the
following criteria:

• The dimension is shared and has been processed.

• The member key column for the lowest level of the dimension contains
the keys that relate the fact table and the dimension table, and is the
only key column necessary to relate the fact table to the dimension
table.

• The keys in the member key column for the lowest level of the
dimension are unique within the dimension. (Use a surrogate key to
ensure this.)

• The lowest level of the dimension is represented in the cube (it does
not need to be visible).

If these criteria are met, you can use the Optimize Schema option in
Analysis Manager to simplify the queries issued against the relational
tables and increase their execution speed.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***