Что такое таблицы фактов и таблицы измерений в OLAP?sql-35

1. Таблица фактов

Таблица фактов — это центральная таблица в OLAP-системе, которая содержит количественные данные (меры) для анализа и ключи для связи с таблицами измерений.

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

  • Хранит числовые данные: Основные показатели бизнеса (продажи, затраты, количества)
  • Содержит внешние ключи: Связи с таблицами измерений
  • Обычно очень большая: Может содержать миллионы/миллиарды записей
  • Денормализованная структура: Оптимизирована для чтения, а не для обновления
CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    product_key INT,          -- Ссылка на измерение продуктов
    time_key INT,             -- Ссылка на измерение времени
    customer_key INT,         -- Ссылка на измерение клиентов
    store_key INT,            -- Ссылка на измерение магазинов
    sales_amount DECIMAL(12,2), -- Мера: сумма продажи
    quantity INT,               -- Мера: количество единиц
    unit_cost DECIMAL(10,2),    -- Мера: себестоимость
    discount DECIMAL(5,2)       -- Мера: скидка
);

2. Таблица измерений

Таблица измерений — содержит описательные атрибуты, которые определяют контекст для фактов (кто, что, где, когда).

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

  • Хранит текстовые/описательные данные: Названия, категории, описания
  • Содержит первичные ключи: Для связи с факт-таблицами
  • Относительно небольшие: Обычно тысячи/десятки тысяч записей
  • Могут быть нормализованы: Особенно в схемах "снежинка"
CREATE TABLE dim_product (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_description TEXT,
    category VARCHAR(50),
    subcategory VARCHAR(50),
    brand VARCHAR(50),
    color VARCHAR(30),
    size VARCHAR(10),
    current_price DECIMAL(10,2),
    effective_date DATE,
    expiration_date DATE
);

3. Взаимодействие между таблицами

Схема "звезда" (Star Schema):

  • Одна центральная факт-таблица
  • Несколько таблиц измерений, связанных напрямую с фактами
  • Нет нормализации измерений

Схема "снежинка" (Snowflake Schema):

  • Измерения нормализованы (разбиты на подтаблицы)
  • Более сложные связи между таблицами

4. Ключевые различия

ХарактеристикаТаблица фактовТаблица измерений
НазначениеХранение мер (чисел)Хранение контекста (текст)
РазмерОчень большиеОтносительно небольшие
ИзменяемостьЧасто добавляются новые записиРедко изменяются
КлючиВнешние ключи к измерениямПервичные ключи
ИндексыНа внешние ключи и датыНа все часто используемые поля

5. Практические примеры

Типичная аналитическая модель продаж:

-- Фактовая таблица
CREATE TABLE fact_retail_sales (
    sale_id BIGINT,
    date_key INT REFERENCES dim_date(date_key),
    product_key INT REFERENCES dim_product(product_key),
    store_key INT REFERENCES dim_store(store_key),
    customer_key INT REFERENCES dim_customer(customer_key),
    sales_amount DECIMAL(12,2),
    quantity INT,
    profit DECIMAL(12,2)
);

-- Таблица измерения времени
CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,
    full_date DATE,
    day_of_week INT,
    day_name VARCHAR(10),
    month INT,
    month_name VARCHAR(10),
    quarter INT,
    year INT
);

6. Советы по проектированию

  1. Для факт-таблиц:

    • Используйте surrogate keys (суррогатные ключи)
    • Минимизируйте количество столбцов
    • Рассмотрите партиционирование по дате

  2. Для таблиц измерений:

    • Добавьте все релевантные атрибуты для анализа
    • Реализуйте SCD (медленно изменяющиеся измерения) для отслеживания истории
    • Создавайте индексы на часто используемых столбцах

Резюмируем: факт-таблицы содержат "цифры" для анализа, а таблицы измерений обеспечивают "контекст" для этих цифр. Их грамотное проектирование — основа эффективной OLAP-системы.

Вопросы

Собеседования