joining dimension table to dimension and again fact table

[kuldeepchitrakar]

Hi

I am analyzing one DW schema and I came to a situation where dimension table is joined to dimension and fact table. Here are the details.

Fact has joins from PersonDim and LocationDim

PersonDim also has join from LocationDim with LocationDim-PersonDim relationship type as 1-n

Is this a good design as i am seeing it as un-necessary as you can easily get Location level data from PersonDim table if Person and Location dimension table are merged.

What are your thoughts on this

[yuldashev]

Hi Kuldeep,

The implementation depends on the business definition and complexity of your Person and Location entities. Usually, Person or Employee and Location or Department are different business entities and used separately in different business processes. Sometimes, Employee and Department hierarchies are very complex. In these cases it's recommended to have two different dimensions.
One of the principles I follow when I architect a data warehouse is that business need is more important than technical perfection.

Thanks,
Alisher
www.streebo.com