3

I am trying to design a dimensional modeling for data warehousing for one of my project(Sales Order). I'm new to this concept.

So far, I could understand that the product, customer and date can be stored in the dimension table and the order info will be in the fact table.

Date_dimension table structure will be 

date_dim_id, date, week_number, month_number

Product_dimension table structure will be

product_dim_id, product_name, desc, sku

Order_fact table structure will be 

order_id, product_dim_id(fk), date_dim_id(fk), order_quantity, order_total_price, etc

If a order is place with 2 or more number of product, will there be repeated entry in the order_fact table for the same order_id, date_dim_id

Please help on this. I'm confused here. I know that in a relational database, order table will have one entry per order and relation between the product and order will be maintained in a different table having the order_id and product_id as the foreign key.

Thanks in advance.

saran
  • 595
  • 5
  • 17
  • 28
  • What information do you want to save from the order header table? You create a fact at the order level detail then you work out how to save order header info. For example customer only appears in the header in your source system but in your order fact table it will be repeated against all individual product orders. There's no problem with this. The only challenge is say when you have a total shopping amount on the header - you can't repeat this across multiple product records. – Nick.Mc Feb 29 '16 at 09:25
  • There are two main workarounds for this: 1. divide the shipping by number of records and spread it across all records; 2. Add another record and pretend the shipping is a product . Whatever you do, you need to preserve the original order number in the fact so you can measure things like 'average sale per order' and 'average products per order'. Anyway this header/detail dimensional modelling is discussed ad nauseum on the internet. Why don't you do some research and ask a specific question. – Nick.Mc Feb 29 '16 at 09:28
  • you should probably use Order Line Item as the grain of your fact table – Neil McGuigan Mar 01 '16 at 21:10

1 Answers1

1

This is a classic case where you should (probbaly) have two fact tables FactOrderHeader and FactOrderDetail.

FactOrderHeader will have a record for each order, storing information regarding the value of the order and any order level discounts; though they could be expressed as an OrderDetail record in some cases.

FactOrderDetail will have a record for each order line, storing information regard the product, product cost, product sale price, number of items, item discount. etc.

You may need to have a DimOrderHeader as well, if there are non-Fact pieces of information that you want to store, for example, date the order was taken, delivered, paid.

Marcus D
  • 1,074
  • 1
  • 12
  • 27