Joins in Framework and addition of Query Item to fact

[koolbachi]

Hi All,

I have four dimension and one fact

Example :

F_Test
D_item
D_item_brand
D_Business_strat
D_Sale_company

The relation is

F_test 1-1 to 1-n D_item
D_item 1-N to 1-1 D_Item_Brand
D_Item_Brand 1-1 to 1-n D_Business_Strat
D_Business_Strat 1-n to 1-1 D_Sale_Company
D_Sale_Company 1-1 to 1-n to F_Test

All the above are shorcut from Physical layer.

I am not able to get the relation working correctly.

Also I want to know Is it possible to add the Query Item from D_Business_Strat to F_Test (Fact)?

Thanks in advance

[blom0344]

I'd say cardinalities are impromperly set (1:n should be on the fact side) and it looks you have one big loop to work with, which is no good.
Item Brand / Item should be merged into one dimension to reduce snowflaking or you should add the brand key to the fact.
Business_Strat should also have it's own FK in the fact..

[IceTea]

Yes, two major things:

1.) Relation on the fact side is always 1-n. On Dimension side 1-1
2.) You build a loop, that's not good.

Suggestion:
Don't try to handle the multiple relations in one Dimension, go for two Dimension-hierarchies.

a) F_test 1-n  --- 1-1 D_item 1-n --- 1-1 D_item_brand
b) F_test 1-n  --- 1-1 D_Business_Strat 1-n --- 1-1 D_Sale_Company

This requires - as blom0344 already said - to have the D_Business_Strat Key directly in the Fact table.

Good luck ;o