CONTEXT
I'm trying to create a sales fact table. Each row represent a product from an order for a customer. Among other fields, I have these 3 specific fields:
PROBLEM
In our company, when we sell a product, I don't know exactly the purchasing price, because I will pay this product later and the purchasing price will be based on the exchange rate at the payment date. I usually know the real purchasing price between 1 week and 2 month after the order date. Indeed, my firm got an arrangement that allow us to pay our suppliers each 15th of the next month, from the month when we receive the product from our supplier. Since we have suppliers in different countries the exchange rate raise some issues.
RESULTS AWAITING
I had to generate 2 monthly reports and 1 annual report : - 1 report on the 1st of each month based on the exchange rate of the order date - 1 report on the 15th of each month based on the exchange rate of the payment date (which is the exchange rate of the current date because we pay our suppliers each 15th of the month) - 1 annual report based on the exchange rate of the payment date (which could sometimes be 2 month after the order date)
EXAMPLE
SOLUTIONS
So far, I find only 3 solutions:
a) create 1 row in the fact table and 2 fields: real_purchasing_price (which would be equal to 0) and temporary_purchasing_price (which would automatically be equal to the purchasing price based on the exchange rate of the order date). b) once I paid the product, I know the right exchange rate, therefore I can update this row an fulfill the field real_purchasing_price with the purchasing price based on the exchange rate of the payment.
a) create 1 row in the fact table with the purchasing_price based on the exchange rate of the order date. b) once I paid the product, I know the right exchange rate therefore I can create 1 new row in the fact table almost similar to the first one, but this time with the purchasing_price based on the exchange rate of the payment date.
a) create a row in the fact table with the purchasing_price based on the exchange rate of the order date b) once I paid the product I know the right exchange rate therefore I can update this row and replace the purchasing_price by the one based on the right exchange rate.
The 4th solution belongs to you.
Thx for your help. Don't hesitate to ask me about more details.
Have a good day,
It seems your order goes through three stages:
ordered
delivered
purchasing price is known
One data-warehouse design approach is the immutability (insert only,make no updates).
This approach would lead to creation of three fact records for your order
Ordered Event
with attributes
orderId, productId, orderDate and sellingPrice
Delivered Event
orderId, DeliveryDate,
Note that the order and delivery records are uniquely correlated with the OrderId (under simplified assumption of only one product per order).
Both of those events are stored in separate fact table or in a common one - it depends on the full attribute definition in you case.
The purchasing price is stored in a separate table with attributes
productId, entryDate, validFromDate, ValidToDate, purchasingPrice
The table is filled based on your rules on the 15th of following month (entryDate) with the validity interval for the preceding month.
The crucial role of this table is to support the query with productId and validDate and return either the purchasing price or unknown.
Based on this design you may setup an access view (simple view, materialize view or other solution) providing the current state of the order
orderId, productId, orderDate, sellingPrice,
DeliveryDate, -- NULL if not delivered
purchasingDate,
purchasingStaus -- 1 purchased, 0 - not yet purchased
purchasingPrice
The purchasingDate is calculated based on the delivery date based on the business rule. The purchasingStatus is a result of comparison of the reporting date and purchasing Date.
The purchasingPrice is either the estimation last known price of the product or the proper purchasing price.
You may also ask why is immutability in data-warehouse design important. It is similar to transaction concept in OLTP. In troubles you may rollback the data to some point in the past using auditing dimension and reload it again. If you update this is much more complex.
Small Example
On 15.2. you get purchasing price for product A in January
Purchasing Price Table
entryDate = 15.2.
validFrom = 1.1.
validTo = 31.1.
purchacingPrice = 100
Order of product A on 1.3. creates a record in Order Event Table
orderDate = 1.3.
sellingPrice = 200
...
You may report this event with "last know purchacing price", which is currently 100. (Lookup in Purchasing Price Table with orderDate gives no valid result, last stored value is returned)
Delivery on 10.3. creates a record in Delivery Event Table
deliveryDate = 10.3.
....
The exact purchasing price is still unknown (Lookup in Purchasing Price Table with deliveryDate gives no valid result, last stored value is returned)
On 15.4. new purchasing price is entered in Purchasing Price Table for March. From this points the real purchacing price is known.