Re: ER Model - different answers for different join paths

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?'

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. Received on Wed Aug 06 1997 - 00:00:00 CDT