'In a datawarehouse can a dimension be related to another dimension?

I am currently developing a Data Warehouse and I was wondering if it makes sense to have a dimension connected to another via a foreign key.

For example, let's say we have two dimensions 'Country' and 'City' should we store only the City Dimension Key in the fact table. And the City is aware of it's Country.

Or does it make more sense two store both foreign keys in the fact table.

But the City dimension will have to know which Country it belongs to (it looks like it defies the star schema, since we now also have links between dimensions)

Or is this purely a design choice and will have no impact in querying, etc?

sqlsql-serverpowerbidata-warehouse


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions