I'am having a disagreement with a colleague over the data warehouse modeling.
We have an entity dimension that has a "default" currency, and currency dimension.
I suggested that the fact table (ex sales) would be linked to the currency dimension and that we would have the currency code as an attribute in the entity dimension (for information purpose only)
My colleague decided to link the fact table to the currency dimension but also to link the entity dimension to the currency dimension. He says that it would help him having information about the entity's currency (exchange rate etc...)
I don't agree with this, and he doesn't seem to agree with me.
What do you think ?
Thank you !
You are right and your colleague is wrong.
In a proper dimensional model dimensions interact with each other through fact tables only, never directly. The same is true for the fact tables - you never link them directly, only through the shared dimensions.
The key idea behind star schema is to have a set of dimensional tables in 2NF (second normal form) resolving their relations via fact tables in 3NF. Linking dimensions directly violates this principle.
Besides, I don't get what he is trying to gain with the direct linking. Information your colleague seeks can be easily queried from a normally designed star schema. There is simply no need to complicate your data model with strange designs.