Redundant Attributes in Related Dimensions

I have a model that contains two dimensions 1) Company and 2) Location. Does it make sense to have an attribute such as CompanyName in both the Company and Location dimensions?

If I'm looking at measures primarily by Location, it is convenient to be able to also see the Company Name. Not to mention, have a hierarchy Company -> Location.

I don't want to denormalize the two into one dimension because I have some facts that are at the higher Company level of granularity.

You could have the CompanyName in the Location dimension if it is strictly 1-m hierarchical, meaning one company has offices in many locations and no more than one company in each location. However I am sceptical about that, as normally you would also have many companies in one location, in which case you cannot have CompanyName in the Location dimension but have to use bridge table to sort out m-m relationship.

I think the best option is having the address details in the Company dimension and if a company have multiple addresses, the record would be repeated. However as you mentioned you don't want to have it denormalised, I think it might be good if you add a bridge table between Company and Location to handle this situation. The bridge table can have the following columns:

- Company ID
- Location ID