Attribute Relationship in SCD Type 2 Dimension

I have a Dimension with a hierarchy structure as described below:

Product Business Code -- > Product Category Code --> Product Segemnt Code --> Product Code.

The Product Business Code Product Category Code and Product Segemnt Code all participate in a SCD Type 2 change. So for the same Product Code we have the the other values in hierarchy changing and this voilates the hierarchy relationship of having only 1:N relationship moving top to bottom in a hierarchy, as a ProductCode which is at lowest level now has two different SegmentCodes after the SCD Type 2 change which created a new row.

The Process Update in dimension behaves unusually and gives me incorrect data in hierarchies.

I need help to understand how this can be fixed, so as to have correct data in dimensions hierarchy.

The current Hierarchy is structured as below:

Product Business Code (Source is corresponding value in table) -- > Product Category Code ( Source is corresponding value in table) --> Product Segemnt Code ( Source is corresponding value in table ) --> Product Code (Source is SourceProductCode in table).

The data in the underlying table is as shown below:

Product_ID ProductKeyCode SourceProductCode Product Business Code Product Category Code Product Segemnt Code Current Inferred

1 AB01 | XYZ XYZ I01 I0101 I010102 0 0

2 AB01 | XYZ XYZ A02 A0202 A020201 1 0

3 AB02 | XYZ XYZ UNK UNK UNK 1 1

4 UNK | XYZ XYZ UNK UNK UNK 1 1

The above 4 records are in my table, the first two shows that there was a change in Product BusinesCode and a new row was created as it is SCD Type 2. The 3rd Record shows a change in the Key Code value and is marked inferred. The fourth record shows an inferred row again but with different product key code ( bad data), for which no data was supplied put exists in dimension as it has related fact data.

With all the above record, the hierarchy when browsed is unusual, when the product dimension is Set to processUpdate all four Product values above show under UNK Product Segemnt Code. When i Ful Process the cube all four are shown under Product Segemnt Code I020201. Now this is weired and confusing for the end users ( we have daily process Update and weekly once Full Process), when seen after Full processing and ProcessUpdate.[left][justify]

Hi vini,

It sounds like you are describing SQL Server Analysis Services? It would help if you could include that info in your post. Anyway assuming you are ...

It sounds like you have followed the "best practice" / design warnings and defined attribute relationships to follow your hierarchy? Personally I've given up on attribute relationships due to behaviour like this. I just relate all the attributes directly to the dimension key. I know, I know, this is sacrilege for the performance gurus, but I've found little difference even for dimensions with 400,000 members.

I also aim to "Process Full" on the entire SSAS database every day at a minimum. With SQL enterprise edition this runs in parallel and can be surprisingly quick. This then avoids all these internal SSAS issues and makes your cube a clean representation of your input data.

Good luck!
Mike