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

View previous topic View next topic Go down

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

Post  samimusleh on Thu Dec 11, 2014 3:13 am

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

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 50
Location : Saudi Arabia

View user profile

Back to top Go down

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

Post  nick_white on Thu Dec 11, 2014 3:36 am

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,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

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

Post  samimusleh on Thu Dec 11, 2014 6:48 am

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.


samimusleh

Posts : 23
Join date : 2013-03-03
Age : 50
Location : Saudi Arabia

View user profile

Back to top Go down

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

Post  ngalemmo on Sat Dec 13, 2014 1:53 am

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  samimusleh on Sat Dec 13, 2014 10:15 am

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

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 50
Location : Saudi Arabia

View user profile

Back to top Go down

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

Post  ngalemmo on Sat Dec 13, 2014 2:55 pm

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  samimusleh on Sun Dec 14, 2014 1:41 am

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

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 50
Location : Saudi Arabia

View user profile

Back to top Go down

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

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