Step Dimension

A Step Dimension in data warehousing represents a process that involves several steps or stages, each of which might need to be analyzed separately. This type of dimension is beneficial in scenarios where a process progresses through distinct phases, and you want to track or analyze each phase individually.

Step Dimension: OrderStatusDimension

This dimension table represents the different steps in the order processing lifecycle.

CREATE TABLE OrderStatusDimension (
    StatusID INT PRIMARY KEY,
    StatusName VARCHAR(100),
    Description VARCHAR(255)
);

Fact Table: OrderFact

The fact table tracks each order along with its current status.

CREATE TABLE OrderFact (
    OrderID INT PRIMARY KEY,
    DateKey INT,
    CustomerID INT,
    ProductID INT,
    StatusID INT,  -- Foreign Key to OrderStatusDimension
    Quantity INT,
    TotalAmount DECIMAL
);

SQL Query Example

To analyze the number of orders at each status:

SELECT 
    osd.StatusName, 
    COUNT(*) AS NumberOfOrders
FROM 
    OrderFact of
JOIN 
    OrderStatusDimension osd ON of.StatusID = osd.StatusID
GROUP BY 
    osd.StatusName;

Remember the Accumulating Snapshot Fact table?

Step dimensions are closely connected to the Accumulating Snapshot Fact table.

CREATE TABLE OrderProcessFact (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE,
    PaymentDate DATE NULL,
    ShipDate DATE NULL,
    DeliveryDate DATE NULL,
    QuantityOrdered INT,
    TotalAmount DECIMAL,
    CurrentStatus VARCHAR(100)
);

• When an order is placed, a new record is inserted with the OrderDate and initial CurrentStatus.
• As the order progresses through payment, shipping, and delivery, the respective date fields and the CurrentStatus are updated.
• This table allows for analysis of the duration between different stages of the order process, identification of bottlenecks, and overall process efficiency.