In Sales system how to design dimensions where city describes Order and Customer

Dear All
I have a case for Sales system where the SALES ORDER table have the following attributes ( CUSTOMS , LOCATION , QTY , VALUE ) where LOCATION represents city where the sales order generated , and the CUSTOMER table have the following attributes (NAME , LOCATION ) where LOCATION represents the city of the Customer . Of course there are other attributes but I am concerning the CITY .
In My design I have the SALES ORDER as a FACT table described by the dimensions CUSTOMER and LOCATION .
My question is in the CUSTOMER dimension a do not need to relate it to the LOCATION dimension , so I well keep all his location information at the same record of the CUSTOMER dimension .
Furthermore in the case where I need to know sales for all customers located at certain city at certain time, can I add a new dimension for the fact table with the name CUSTOMER_LOCATION , This is to avoid having a parent for the CUSTOMER dimension and also for the case where the customer may change his location , so I do not need the case of (TYPE 2) for the customer location where actually his location at the time of creation of the sales order is kept with the fact table (surrogate key), i.e. this will always give the actual sales of a customer at a certain city at certain time.
This is useful for the case where i need to measure for each city (sales order city ) a customer activity for each location of his own .

Thanks
Sami Musleh

Hi,
I would say it depends on many scenarios you have where you want to query by customer location.
If this is the only (or one of a few) fact tables where you need customer location then I would add a new FK to your fact table referencing the Location Dim and use that for Customer location.
If, however, you need to know Customer location whenever you also query by other Customer attributes then rather than add a new Location FK to every Fact table that references Customer I would denormalise the Location attributes into the Customer Dim and make the Dim SCD2.
A couple of other points you might want to bear in mind:
1. I assume that Location only contains generic attributes (city, state, etc.) rather than the full postal address? If you are holding the full postal address then it will be (almost) 1:1 with your customer and there is no point creating a separate Dim for it as each record would only ever be referenced once in conjunction with each customer
2. In most implementations, Customer is the one Dim that can almost always be guaranteed to require some level of history tracking (SCD) as most reporting wants to know the state of a customer at the point a fact occurred. If you are not planning to make it SCD then I would validate that decision very carefully as going back and changing it at a later date will be a lot of work

Regards,

Thank you Mr. Nick
what you have assumed is correct , actually the other solution from one of my friends is to have a FK for LOCATION at CUSTOMER dimension which will drive me to SNOWFLAKE not STAR schema solution.

An alternative to snowflaking is to have a location dimension that has two references from the fact: sales location & customer location.

You can store the FK in the customer dimension but only use it to populate the customer location key in the fact.

Dear Mr. ngalemmo

regarding to the phrase (store the FK in the customer dimension ) , It seems to me this is a snowflake , since I have to join the CUSTOMER dimension with the CUSTOMER_LOCATION dimension even if my query does not need CUSTOMER attributes other than his location

Its a snowflake if you use it in queries. But that is not what I am suggesting.

The fact should have a FK for the customer location to the location dimension. The issue is, how do you populate it? A typical sales transaction feed is not going to have customer location information, but it will have the customer. If you store the location FK on the customer dimension, you can retrieve it for loading into the fact when you do the lookup to get the customer SK.

Mr. ngalemmo , now it is clear for me and I think this is a very good solution. Thank you