Что такое факты и измерения в хранилище данных?

Факты и измерения в хранилище данных

Хранилище данных (Data Warehouse) использует звездообразную архитектуру (Star Schema), которая разделяет данные на две основные категории: таблицы фактов и таблицы измерений.

Таблица фактов (Fact Table)

Таблица фактов содержит количественные данные — метрики, которые мы анализируем.

Характеристики:

• Строки представляют события или транзакции
• Столбцы содержат числовые значения (метрики) и иностранные ключи
• Обычно содержит больше всего строк в схеме
• Часто самая тяжёлая таблица
• Может иметь несколько уровней агрегации

Пример таблицы sales_fact:

CREATE TABLE sales_fact (
  sale_id BIGINT PRIMARY KEY,
  date_id INT NOT NULL,           -- FK на таблицу дат
  customer_id INT NOT NULL,       -- FK на таблицу клиентов
  product_id INT NOT NULL,        -- FK на таблицу товаров
  store_id INT NOT NULL,          -- FK на таблицу магазинов
  
  quantity INT,                    -- МЕТРИКА
  revenue DECIMAL(10, 2),          -- МЕТРИКА
  discount DECIMAL(10, 2),         -- МЕТРИКА
  profit DECIMAL(10, 2),           -- МЕТРИКА
  
  FOREIGN KEY (date_id) REFERENCES date_dim(date_id),
  FOREIGN KEY (customer_id) REFERENCES customer_dim(customer_id),
  FOREIGN KEY (product_id) REFERENCES product_dim(product_id),
  FOREIGN KEY (store_id) REFERENCES store_dim(store_id)
);

Таблица измерений (Dimension Table)

Таблица измерений содержит описательные данные — контекст для фактов.

Характеристики:

• Строки содержат атрибуты и описания
• Обычно обновляются медленно (SCD — Slowly Changing Dimensions)
• Меньше строк чем таблица фактов
• Часто содержат текстовые и категориальные данные
• Используются для GROUP BY и фильтрации

Примеры таблиц измерений:

-- Таблица дат
CREATE TABLE date_dim (
  date_id INT PRIMARY KEY,
  calendar_date DATE,
  year INT,
  quarter INT,
  month INT,
  day_of_month INT,
  day_of_week VARCHAR(10),
  is_holiday BOOLEAN,
  is_weekend BOOLEAN
);

-- Таблица клиентов
CREATE TABLE customer_dim (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255),
  email VARCHAR(255),
  city VARCHAR(100),
  country VARCHAR(100),
  segment VARCHAR(50),  -- Premium, Regular, Budget
  signup_date DATE
);

-- Таблица товаров
CREATE TABLE product_dim (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255),
  category VARCHAR(100),
  subcategory VARCHAR(100),
  brand VARCHAR(100),
  price DECIMAL(10, 2),
  color VARCHAR(50)
);

-- Таблица магазинов
CREATE TABLE store_dim (
  store_id INT PRIMARY KEY,
  store_name VARCHAR(255),
  city VARCHAR(100),
  region VARCHAR(100),
  manager_name VARCHAR(255)
);

Архитектура: звездообразная схема

            date_dim
              |
              |
  customer_dim - sales_fact - product_dim
              |
              |
            store_dim

Это звёздная схема (Star Schema). Более сложная версия называется снежинка (Snowflake Schema), где измерения нормализованы дополнительно.

Практический пример запроса

SELECT
  d.year,
  d.quarter,
  c.segment,
  p.category,
  SUM(sf.revenue) as total_revenue,
  SUM(sf.quantity) as total_quantity,
  AVG(sf.profit) as avg_profit
FROM sales_fact sf
JOIN date_dim d ON sf.date_id = d.date_id
JOIN customer_dim c ON sf.customer_id = c.customer_id
JOIN product_dim p ON sf.product_id = p.product_id
WHERE d.year = 2024
  AND c.segment = "Premium"
  AND p.category IN ("Electronics", "Clothing")
GROUP BY d.year, d.quarter, c.segment, p.category
ORDER BY d.year, d.quarter;

Медленно меняющиеся измерения (SCD)

Таблицы измерений обновляются редко, но иногда требуется отслеживать историю:

SCD Type 1: Перезаписать старые значения

-- Обновляем текущий статус клиента
UPDATE customer_dim
SET status = "Gold"
WHERE customer_id = 123;
-- История теряется

SCD Type 2: Сохранить историю с датами

CREATE TABLE customer_dim_scd2 (
  customer_dim_id INT PRIMARY KEY,  -- Суррогатный ключ
  customer_id INT,                   -- Бизнес-ключ
  status VARCHAR(50),
  start_date DATE,
  end_date DATE,
  is_current BOOLEAN
);

-- Когда статус меняется
UPDATE customer_dim_scd2
SET end_date = CURRENT_DATE - 1, is_current = FALSE
WHERE customer_id = 123 AND is_current = TRUE;

INSERT INTO customer_dim_scd2
VALUES (NULL, 123, "Gold", CURRENT_DATE, NULL, TRUE);

Типы таблиц фактов

1. Транзакционные факты (Transaction Facts)

-- Каждая строка = одна транзакция
CREATE TABLE order_facts (
  order_id BIGINT PRIMARY KEY,
  date_id INT,
  customer_id INT,
  product_id INT,
  quantity INT,
  revenue DECIMAL(10, 2)
);

2. Периодические снимки (Periodic Snapshot)

-- Снимок конца дня
CREATE TABLE daily_inventory_facts (
  date_id INT,
  product_id INT,
  warehouse_id INT,
  quantity_on_hand INT,
  PRIMARY KEY (date_id, product_id, warehouse_id)
);

3. Накопительные факты (Accumulating Snapshot)

-- Отслеживание процесса заказа
CREATE TABLE order_process_facts (
  order_id BIGINT PRIMARY KEY,
  customer_id INT,
  order_date_id INT,
  ship_date_id INT,
  delivery_date_id INT,
  return_date_id INT,
  revenue DECIMAL(10, 2),
  status VARCHAR(50)
);

Преимущества звёздной схемы

1. Производительность — меньше JOIN операций, лучше кэшируется
2. Простота запросов — интуитивное построение аналитических запросов
3. Масштабируемость — факты отделены от измерений
4. Денормализация — таблицы измерений содержат избыточные данные для быстрого доступа
5. Понятность — бизнес-пользователи легко ориентируются в схеме

Vs. Снежинка (Snowflake Schema)

Снежинка нормализует измерения дальше:

        calendar_dim
           |
        date_dim
           |
sales_fact - customer_dim - geography_dim
           |
        product_dim - category_dim

Преимущество: меньше хранилища, но медленнее запросы (больше JOIN).

Факты и измерения — основа аналитического хранилища, позволяющая эффективно анализировать данные.