Key figures as a result
Further transformations are necessary for the data mart.
Creation of the fact table
The creation of the fact table with the key figures:
- Quantity:
The quantity of products ordered per product. This key figure is taken directly from the order item. - Revenue:
The amount of money to be paid for the quantity of product ordered. For this purpose, the quantity and price from the order item are multiplied and then the discount is deducted. - Open order:
A 1 to briefly indicate whether this order is still open. - Completely delivered order:
A 1 to briefly indicate whether this order has already been completely delivered. - Deviation between requested date and delivery date in days:
If each item has been delivered, the deviation is calculated from the order.requested.date and the last delivery.delivery.date. If the delivery was made before the requested date, the deviation is negative. Willibald wants to deliver on time as early deliveries also cause problems for the customer (care of seedlings). - Orders without customers are always from the roadshow, therefore a dummy customer ‘Roadshow’ must be inserted.
Necessary transformations through business rules
- Standardise the orders of the association partners
The orders of the roadshow are directly linked to the association partner. This must now be done for the orders of the association partner from the web shop. To do this, all orders for this customer are linked directly to the club partner using the club partner.customerIDclub. - Roadshow Assign orders to customers
Using the credit card and the GueltigBis, the relationships from order to customer are completed.
Historicised reference table for the on-time delivery dimension
A historised reference table is still required for the data mart. Start with the following values:
Then the value must change at the beginning of period 2:
