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
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..
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