I have created a factSales table with dimDate, dimCustomer, dimProduct, dimSalesPerson tables. The dimensions are all joined with surrogate integer identity PK fields which serve as the composite key in the fact table.
It is possible for the same customer to place multiple orders for the same product from the same sales person on the same date. When this happens it seems to me that only the last order will be stored in the fact table. I want to have a row for each order. How would one design a fact table to accomplish this. The only truly unique piece of data from the OLTP is the sales order number.
factSales:
PK DateKey, int
PK CustomerKey, int
PK ProductKey, int
PK SalesPersonKey, int
Amount
UnitCost
Weight
ShippingCost
dimDate
PK DateKey, int, identity
SalesDate
dimCustomer
PK CustomerKey, int, identity
CustomerName
Address
dimProduct
PK ProductKey, int, identity
Product#
ProductName
DimSalesPerson
PK SalesPersonKey, int, idnetity
SalesPersonName
Dept
StartDate
EndDate
Hi,
You'll need to stick another key on the fact to make each row unique. Even if it is something simple like a count
e.g.
FK DateKey, int
FK CustomerKey, int
FK ProductKey, int
FK SalesPersonKey, int
FK OrderNumberOfTheDay, int
measures ...
If possible a time stamp might be another way, I am assuming though that the different orders happen at different times of the day. But basically another key in your ETL process will fix that problem.
Hope that helps,
Matt
|||Thanks Matt. I was going to just add the sales order # as it is unique but I didn't want to violate and conventions that might cause problems down the line.
Thanks again!
|||Hi John
Matt is right in that you should simply bring through the Sales Order Number then your DSV design will take care of the aggregation
Alternatively, unless you need to analyse the data by the Sales Order Number, (which some might argue you should use your OLTP for) you could aggregate the facts in your fact table ETL load process. This can increase performance both in processing the cube and queries if you have a lot of data
If you want to use any drillthrough functionality then keep the Sales Order Number in.
HTH
Tim
|||Thanks Tim.
I had also considered that possibility. My actual OLTP/OLAP is more complex than I showed (I tried to keep it simple for this example). I would have to join several dozen records in order to aggregate the data and I may have a sales order record which does not yet have a ship date which will be the key date slicer. I can load my fact table with a pointer to a 0 date key or just skip SO records without a ship date (my next task to figure out); it would be very complex to aggregate them. Plus I was already storing the SO# in my fact table on the likely probability the users will want to drill back to the original table.
