How to get the correct table as a Fact Table with relevant keys?(Star Schema)

Question

I have a problem to select the suitable table for the fact table. I have problem with following two tables

OrderData Table :

1. OrderID
2. CustomerID
3. OrderStatus
4. OrderPurchaseAt
5. OrderAprovedAt
6. OrderDeliveredCarrier
7. OrderDeliveredCustomer
8. OrderestimatedDelivered

OrderItems Table :

1. OrderID
2. OrderItemID
3. ProductID
4. SellerID
5. ShippingLimitDate
6. Price
7. Frieghtweight

What is the suitable table for fact Table? my data source is https://www.kaggle.com/olistbr/brazilian-ecommerce

Please give me a support.

Answer 1

Let's imagine that you have CustomerID=200 who ordered 2 products ProductID=15 and ProductID=18 with different Sellers, Prices and FreightWeight and same ShippingLimitDates.

Your will have two fact tables one for purchasing :

OrderID CustomerSK OrderItemID ProductSK SellerSK ShippingLimitDate Price Frieghtweight
100     200        1           15        1001     15/02/2020        100   12.20
100     200        2           18        1001     15/02/2020        100   12.20

and one for shipping :

OrderID CustomerSK OrderStatus OrderPurchaseAt OrderAprovedAt OrderDeliveredCarrier OrderDeliveredCustomer OrderestimatedDelivered
100     100        Delivered   14/02/2020      14/02/2020     15/02/2020            16/02/2020             16/02/2020

You model will be like below :