Dimension Table

`
-- Example dimensions supporting a sales fact table

-- Date dimension (enables temporal analysis)
CREATE TABLE dim_date (
  date_id INT PRIMARY KEY,
  calendar_date DATE,
  day_of_week VARCHAR(10),
  day_of_month INT,
  month INT,
  quarter INT,
  year INT,
  week_number INT,
  fiscal_month INT,
  fiscal_quarter INT,
  fiscal_year INT,
  is_weekend BOOLEAN,
  is_holiday BOOLEAN
);

-- Product dimension (enables product analysis)
CREATE TABLE dim_product (
  product_id INT PRIMARY KEY,
  product_code VARCHAR(50),
  product_name VARCHAR(255),
  category VARCHAR(100),  -- Hierarchy level 1
  subcategory VARCHAR(100),  -- Hierarchy level 2
  brand VARCHAR(100),
  list_price DECIMAL(10, 2),
  current_price DECIMAL(10, 2),
  supplier_id INT,
  status VARCHAR(20)
);

-- Customer dimension (enables customer analysis)
CREATE TABLE dim_customer (
  customer_id INT PRIMARY KEY,
  customer_code VARCHAR(50),
  customer_name VARCHAR(255),
  segment VARCHAR(50),  -- Hierarchy level 1
  subsegment VARCHAR(50),  -- Hierarchy level 2
  region VARCHAR(100),  -- Geographic hierarchy
  country VARCHAR(100),
  state VARCHAR(100),
  city VARCHAR(100),
  lifetime_value DECIMAL(12, 2),
  tenure_months INT
);

-- Store dimension (enables location analysis)
CREATE TABLE dim_store (
  store_id INT PRIMARY KEY,
  store_code VARCHAR(50),
  store_name VARCHAR(255),
  country VARCHAR(100),  -- Hierarchy
  state VARCHAR(100),  -- Hierarchy
  city VARCHAR(100),  -- Hierarchy
  district_manager VARCHAR(100),
  region_manager VARCHAR(100),
  store_format VARCHAR(50),
  opening_date DATE
);

-- Analytical queries use dimension attributes for filtering and grouping

-- Drill-down: Revenue by category then by subcategory
SELECT 
  p.category,
  p.subcategory,
  SUM(f.revenue) as revenue
FROM fct_sales f
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year = 2024
GROUP BY p.category, p.subcategory
ORDER BY category, revenue DESC;

-- Slicing: Compare segments for specific time period
SELECT 
  c.segment,
  c.region,
  COUNT(DISTINCT f.customer_id) as customers,
  SUM(f.revenue) as segment_revenue
FROM fct_sales f
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2024 AND d.quarter = 2
GROUP BY c.segment, c.region;
`