Customer master table as a Dimension or Fact table?

[Rodriguez22]

Hi everyone,

I am working a data model with 4 fact tables - Sales (date level), Budget(month and product level), Salesman Route(date level), Orders (date level)- and 4 dimension tables - Customers, Products, Time, Warehouse. 

The issue that I have is in Customers table, until this month we had only one row for each customer, from the next month we'll have one row for each customer for each date. Customer's information has the name of salesman and we have to know if a certain customer has worked with one or another salesman in a specific period. With this change I don't know how to handle Customer data, because it changes according to the date (like a fact table) and I know its importance as a dimension table.

I really appreciate your help.

[justISO]

Hi, maybe you can find an approach by using period as 'monthend([Your_date])' or/and key as '[Your_date]&' - '&[Your_customer]' for table 'better' connection. Just an idea, because it is quite hard to suggest something concrete without seeing your actual data.

[Rodriguez22]

Thank you for your reply, my actual data is like this:

The customers table will have a new field (date_snapshot) in order to know what were the changes in the customers information, mainly the changes in Salesman_name to which customers belong. With this new field I'll have an ID_Address for each customer for each date.

Thanks for your help.

[Rodriguez22]

Sorry, in the Salesman route table there is a mistake, Customer_ID is ID_Address.

The problem that we have with the new Customers table is the relationship with the fact tables, mainly with sales. Now the relationship is one-to-many but later it will be many-to-many and when we will have to know how many customers do we have, it will look like we have many customers (one for each date snapshot).