Sales Rep <--> Customer relationship with Sales Fact Table

Hi

I'm new to the Dimensional Modeling and have some Problems with a Design.

Following Situation

Sales Fact
Customer Dim
SalesRep Dim

In the source (ERP) each Customer have an associated SalesRep (which will change over time)
At the time when a fact happen there is a associated Customer and an associated SalesRep to the fact. So i'm able to sum up the facts for a given SalesRep or a Customer or in combination for the time when the fact happen.
But now the SalesRep for the Customer changes and i'd like to sum up all all facts for a Customer and his associated SalesRep even when another SalesRep was associated to the fact in the past.

So i don't have a Problem to model this in a normal 3nf Database and then query that, but i don't know how to model this in a Star-DWH when i don't like to snowflake it.

I hope you will understand what i mean, because english is not my native language.
Thx

Hi,

it depends upon how are you planning to update the fact data for all the old transactions for that customer or the new rep will be for new transaction for that customer when the salesrep for a customer changes.


thanks

You store sales rep as a dimension of the fact and associated sales rep as an attribute of the customer.

A snowflake is appropriate in this case, but if you don't like that, you can store the sales rep attributes in the customer dimension.  This will, however, complicate updating.

Another approach is to have a customer/sales rep bridge, but that is worse than a snowflake.

Never re-key facts.

Thx a lot.

I think a snowflake will be the right way in this case.