Data Warehousing
  • Data Warehousing
  • Readme
  • Fundamentals
    • Terms to Know
    • Jobs
    • Skills needed for DW developer
    • Application Tiers
    • Operational Database
    • What is a Data Warehouse
      • Typical Data Architecture
      • Problem Statement
      • Features of Data Warehouse
      • Need for Data Warehouse
      • Current State of the Art
    • Activities of Data Science
    • Types of Data
    • Data Storage Systems
    • Data Warehouse 1980 - Current
    • Data Warehouse vs Data Mart
    • Data Warehouse Architecture
      • Top-Down Approach
      • Bottom-Up Approach
    • Data Warehouse Characteristic
      • Subject Oriented
      • Integrated
      • Time Variant
      • Non Volatile
    • Tools
    • Cloud vs On-Premise
    • Steps to design a Data Warehouse
      • Gather Requirements
      • Environment
      • Data Modeling
      • Choosing ETL / ELT Solution
      • Online Analytic Processing
      • Front End
      • Query Optimization
    • Dataset Examples
    • Thoughts on some data
  • RDBMS
    • Data Model
      • Entity Relationship Model
      • Attributes
      • Keys
      • Transaction
      • ACID
    • Online vs Batch
    • DSL vs GPL
    • Connect to Elvis
    • SQL Concepts
      • Basic Select - 1
      • Basic Select - 2
      • UNION Operators
      • Wild Cards & Distinct
      • Group By & Having
      • Sub Queries
      • Derived Tables
      • Views
    • Practice using SQLBolt
  • Cloud
    • Overview
    • Types of Cloud Services
    • Challenges of Cloud Computing
    • AWS
      • AWS Global Infrastructure
      • EC2
      • S3
      • IAM
    • Terraform
  • Spark - Databricks
    • Storage Formats
    • File Formats
    • Medallion Architecture
    • Delta
  • Data Warehousing Concepts
    • Dimensional Modelling
      • Star Schema
      • Galaxy Schema
      • Snowflake Schema
      • Starflake Schema
      • Star vs Snowflake
      • GRAIN
      • Multi-Fact Star Schema
      • Vertabelo Tool
    • Dimension - Fact
    • Sample Excercise
    • Keys
      • Why Surrogate Keys are Important
    • More Examples
    • Master Data Management
    • Steps of Dimensional Modeling
    • Types of Dimensions
      • Date Dimension Table
      • Degenerate Dimension
      • Junk Dimension
      • Static Dimension
      • Conformed Dimensions
      • Slowly Changing Dimensions
        • SCD - Type 0
        • SCD - Type 1
        • SCD - Type 2
        • SCD - Type 3
        • SCD - Type 4
        • SCD - Type 6
        • SCD - Type 5 - Fun Fact
      • Role Playing Dimension
      • Conformed vs Role Playing
      • Shrunken Dimension
      • Swappable Dimension
      • Step Dimension
    • Types of Facts
      • Factless Fact Table
      • Transaction Fact
      • Periodic Fact
      • Accumulating Snapshot Fact Table
      • Transaction vs Periodic vs Accumulating
      • Additive, Semi-Additive, Non-Additive
      • Periodic Snapshot vs Additive
      • Conformed Fact
    • Sample Data Architecture Diagram
    • Data Pipeline Models
    • New DW Concepts
Powered by GitBook
On this page
  1. Data Warehousing Concepts
  2. Types of Dimensions

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)
    -- Other attributes related to the status
);
StatusID
StatusName
Description

1

Order Placed

Order has been placed

2

Payment Processed

Payment has been received

3

Shipped

Order has been shipped

4

Delivered

Order has been delivered

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
    -- Other order-related attributes
);

SQL Query Example

To analyze the number of orders at each status:

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.


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.

PreviousSwappable DimensionNextTypes of Facts

Last updated 1 year ago