|
Home > Archive > MS SQL Data Warehousing > February 2006 > SCD Type 2 -- not aggregating up the hierarchy
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SCD Type 2 -- not aggregating up the hierarchy
|
|
|
| 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?
| |
|
| [=2E..]
>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.
[=2E..]
Is your ProductID a surrogate key (as strongly recommend) or a
nonunique key that only becomes unique in conjunction with valid_from
and/or valid_to?
Regards,
J=F6rg
|
|
|
|
|