Status change fact table – Part 2 (The input data)

(See part 1 of this series here)

To make matters simpler we’ll assume that a table with a stream of order events already exists and that it’s denormalised, with each row containing all the relevant data. Either the source system already had that information or it was built in an earlier phase of the ETL.

Table order_events:
Order_id int
Order_date datetime
Customer_name string
Event_date datetime
Event_name string
Warehouse string
Carrier string

Here’s a sample of the data we’ll be dealing with:

order_id order_date customer_name event_date event_name warehouse carrier
1 01/01/16 Acme Industries 02/01/16 08:00 Preparing shipment London Deliveries express
1 01/01/16 Acme Industries 03/01/16 10:00 Shipped from warehouse London Deliveries express
1 01/01/16 Acme Industries 03/01/16 14:30 Delivery failed London Deliveries express
1 01/01/16 Acme Industries 03/01/16 18:00 Returned to warehouse London Deliveries express
1 01/01/16 Acme Industries 04/01/16 09:00 Shipped from warehouse London Deliveries express
1 01/01/16 Acme Industries 04/01/16 11:30 Delivery failed London Deliveries express
1 01/01/16 Acme Industries 04/01/16 18:00 Returned to warehouse London Deliveries express
1 01/01/16 Acme Industries 05/01/16 10:30 Shipped from warehouse London Deliveries express
1 01/01/16 Acme Industries 05/01/16 15:00 Delivery successful London Deliveries express
2 01/01/16 Boggs Corp 02/01/16 09:00 Preparing shipment London Deliveries express
2 01/01/16 Boggs Corp 03/01/16 10:00 Shipped from warehouse London Deliveries express
2 01/01/16 Boggs Corp 03/01/16 11:00 Delivery successful London Deliveries express
3 02/01/16 Boggs Corp 03/01/16 08:00 Preparing shipment Liverpool WeDeliverStuff
3 02/01/16 Boggs Corp 04/01/16 09:30 Shipped from warehouse Liverpool WeDeliverStuff
3 02/01/16 Boggs Corp 04/01/16 15:00 Delivery successful Liverpool WeDeliverStuff
3 02/01/16 Boggs Corp 08/01/16 16:30 Returned by customer Liverpool WeDeliverStuff
3 02/01/16 Boggs Corp 09/01/16 08:00 Preparing shipment Liverpool WeDeliverStuff
3 02/01/16 Boggs Corp 10/01/16 10:00 Shipped from warehouse Liverpool WeDeliverStuff
3 02/01/16 Boggs Corp 10/01/16 14:00 Delivery successful Liverpool WeDeliverStuff

Our data sample has 3 orders, from 2 different customers and that shipped from two different warehouses. One of the orders was only delivered after a couple of failed attempts and in another the products were returned and had to be replaced.

Our goal in the next article is to build a fact table that can track all events that happened to all orders and provide meaningful analytics to the client.

In the next part of this series we’re going to explore a data model that allows analysing this dataset.

Leave a Reply