Re: ER Model - different answers for different join paths

My question is in the middle starting with --

Kevin Beyer wrote:
>
> I agree that each path in the ER model can produce a different answer,
> but that's because each path is the answer to a different question, not
> 'the wrong answer'. I believe that Kimball's point is that user's dont
> understand, and don't want to understand all the information in the ER
> model. Take the following example:
>
> Orders >--- Customer >--- SalesRep
> Orders >----------------------/
>
> ( '>' means 'many', eg, each customer has many orders )
>
> What this model captures is all of the following:
> 1. each customer has many orders
> 2. each customer has a regular sales rep
> 3. each order was serviced by a sales rep that may or may not have been
> the customer's regular rep
>
> So, there are now two ways to connect Orders and SalesRep:
> 1. Orders >--- Customer >--- SalesRep
> 2. Orders >--- SalesRep
>
> When following the first path, you want to know the customer's sales rep
> for each order. The second path gives you the rep that serviced the
> order. I would argue that neither answer is wrong, but rather the right
> answer depending on the question.
>
> A dimensional model would probably not included path 1 above and would
> model
> path 2 as a SalesRep dimension that connects to the sales fact table.
> This makes life simple, but now we cannot answer some interesting
> questions. For example, 'how many orders were serviced by someone other
> than the customer's regular sales rep?'

• Hi, we had the same problem in our warehouse. There is a purchase order fact table and it relates to item and planner dimension in addition to other dimensions. The business scenario is that evey item would be assigned a default planner but the purchase order can be placed by any planner. We want to answer both questions. So we had the planner code as an attribute in the item table and also have a separate planner dimension. Maybe we shouldn't do this according to strict dimensional modelling concepts, but we had the requirement. Any other good suggestions out there ?
  >
  > So, yes, you can still trust your good old ER models. They probably
  > have more information in them. The problem is that it takes an expert
  > to find right answer to a question. The advantage of DM is that it
  > makes life easier for both end-users and automated tools.