> For the complete documentation index, see [llms.txt](https://gchandra.gitbook.io/data-warehousing/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://gchandra.gitbook.io/data-warehousing/data-warehousing-concepts/types-of-dimensions/step-dimension.md).

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

```sql

CREATE TABLE OrderStatusDimension (
    StatusID INT PRIMARY KEY,
    StatusName VARCHAR(100),
    Description VARCHAR(255)
    -- Other attributes related to the status
);

```

<table><thead><tr><th width="141.33333333333331">StatusID</th><th width="230">StatusName</th><th>Description</th></tr></thead><tbody><tr><td>1</td><td>Order Placed</td><td>Order has been placed</td></tr><tr><td>2</td><td>Payment Processed</td><td>Payment has been received</td></tr><tr><td>3</td><td>Shipped</td><td>Order has been shipped</td></tr><tr><td>4</td><td>Delivered</td><td>Order has been delivered</td></tr></tbody></table>

**Fact Table: OrderFact**

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

```sql

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

```

#### SQL Query Example

To analyze the number of orders at each status:

```sql
sqlCopy codeSELECT 
    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.

```sql

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)
    -- You can add more columns as needed for your analysis
);
```

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