Fact table changes

Question

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:

• purchasing_price
• selling_price
• margin

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

1. I order a product on 3rd July.
2. This product is only delivered on the 7th August
3. Then I will pay the product the 15th September based on the exchange rate of this date.

SOLUTIONS

So far, I find only 3 solutions:

1. 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.

2. 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.

3. 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,

Answer 1

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.

Answer 2

I actually choose to use a currency table and to add a field payment_date which allow me to match each row of the fact table with right exchange rate in the currency table.

Nevertheless, I have to UPDATE each row of the fact table to add the payment_date once I know it.

I couldn't find a better solution so far.

Thx everyone.

Answer 3

I am assuming that for purchasing price, you have a componet which is fixed and another which is variable and depends on exchange rate. (say p*x where x is exchange rate)

I would suggest to create a new fact table, say exchange_rates with different countries exchange rates and timestamp. Now the column purchasing_price in your fact table, should be a result of join from exchange_rates table for that country.

Lets say current_values column hold exchange rate.

    EXCHANGE_RATES
    +-------------+---------------+---------------+-----------+
    | EXCHANGE_ID | EXCHANGE_NAME | CURRENT_VALUE | TIMESTAMP |
    +-------------+---------------+---------------+-----------+
    |           1 | JAPAN         |           100 |  20151021 |
    +-------------+---------------+---------------+-----------+     

While populating purchasing price, use this value from EXCHANGE_RATES to get correct values.

    ORDER_DETAIL_FACT       
    +----------+---------+------------------+---------------+--------+
    | ORDER_ID | PRODUCT | PURCHASING_PRICE | SELLING_PRICE | MARGIN |
    +----------+---------+------------------+---------------+--------+
    |      101 |      11 | 50*100           |          6000 |   1000 |
    +----------+---------+------------------+---------------+--------+

Let me know if you need help with its implementation.