Time span accumulating fact tables design

Question

I need to design a star schema to process order processing. The progress of an order look like this:

• Customer C place an order on item I with quantity 100
• Factory F1 take the order partially with quantity 30
• Factory F2 take the order partially with quantity 20
• Buy from market 50 items
• F1 delivery 20 items
• F1 delivery 7 items
• F1 cancel the contract (we need to buy 3 more item from market)
• F2 delivery 20 items
• Buy from market 3 items
• Complete the order

How can I design a fact table in this case, since the number of step is not fixed, the data types of event is not the same.

I'm sorry for my bad English.

Answer 1

The definition of an Accumulating Snapshot Fact table according to Kimball is: summarizes the measurement events occurring at predictable steps between the beginning and the end of a process.

For this particular use case I would go with a Transaction Fact Table as the events (steps) are unpredictable, it is more like an event fact table, something similar to logs or audits.

| order_key | date_key | full_datetime       | entity_key (customer, factory, etc. varchar) | entity_type | state    | quantity |
|-----------|----------|---------------------|----------------------------------------------|-------------|----------|----------|
| 1         | 20190602 | 2019-06-02 04:30:00 | C1                                           | customer    | request  | 100      |
| 1         | 20190602 | 2019-06-02 05:30:00 | F1                                           | factory     | receive  | 30       |
| 1         | 20190602 | 2019-06-02 05:30:00 | F2                                           | factory     | receive  | 20       |
| 1         | 20190602 | 2019-06-02 05:40:00 | Company?                                     | company     | buy      | 50       |
| 1         | 20190603 | 2019-06-03 06:40:00 | F1                                           | factory     | deliver  | 20       |
| 1         | 20190603 | 2019-06-03 02:40:00 | F1                                           | factory     | deliver  | 7        |
| 1         | 20190603 | 2019-06-03 04:40:00 | F1                                           | factory     | deliver  | 3        |
| 1         | 20190603 | 2019-06-03 06:40:00 | F1                                           | factory     | cancel   |          |
| 1         | 20190604 | 2019-06-04 07:40:00 | F2                                           | factory     | deliver  | 20       |
| 1         | 20190604 | 2019-06-04 07:40:00 | Company?                                     | company     | buy      | 3        |
| 1         | 20190604 | 2019-06-04 09:40:00 | Company?                                     | company     | complete | 100      |

I'm not sure about your reporting needs as they were not specified, but assuming you need to measure lag/durations of unpredictable steps, you could PIVOT and use dynamic SQL to create the required view

SQL Server dynamic PIVOT query?

Let me know if you came up with something different as I'm interested on this particular use case. Good luck