Hi,

I have an SSAS 2005 cube w/a product dimension which contains the
following hierarchy:

Company --> Store --> Department --> Product

I have just converted the Product dimension to be SCD Type 2, so that
we can preserve the history of the movement of a product from one
department to another. The new dimension table has the ScdStartDate,
ScdEndDate, ScdOriginalId, and ScdStatus attributes assigned to the
correct columns.

The product dimension is joined to the fact table on the ProductID.
Since the dimension is SCD type 2, there can be multiple rows in the
dim table for a given product ID, with only one being the current.

When I browse the cube using this dimension hierarchy, I find that
aggregations are working only at the ProductID level (i.e. the lowest
level). All other higher levels in the hierarchy seem not to have any
relationship with the Fact table (so they reflect sums for the whole
database), which renders the hierarchy useless.

The relationships between the tables all exist in the DSV, and are (I
believe) correct, but for some reason, they are not being seen in the
dimension.

Any suggestions?

Shouldn't the SCD 2 dimension (ie. Product) be joined to the fact table
via a surrogate key, so that only 1 dimension table row is selcted? From
Kimball's article:

http://www.intelligententerprise.com...house1_1.jhtml
The logical foundation of dimensional modeling
by Ralph Kimball
...
I insist that the foreign keys in the fact table obey referential
integrity with respect to the primary keys in their respective
dimensions. In other words, every foreign key in the fact table has a
match to a unique primary key in the respective dimension.
...
Also, what attribute relationships are defined in the Product dimension?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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