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

View previous topic View next topic Go down

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

Post  Vergall on Wed Jul 08, 2015 4:45 am

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

Vergall

Posts : 2
Join date : 2015-07-08

View user profile

Back to top Go down

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

Post  hkandpal on Thu Jul 09, 2015 9:12 am

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

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

Post  ngalemmo on Thu Jul 09, 2015 9:43 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  Vergall on Thu Jul 09, 2015 10:37 am

Thx a lot.

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

Vergall

Posts : 2
Join date : 2015-07-08

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum