Вступ
У світі зберігання даних та аналітики існують два основні будівельні блоки, які є основою ефективної організації даних: таблиці фактів (Fact Tables) та таблиці вимірів (Dimension Tables). Ця стаття надає всебічне розуміння цих важливих компонентів — від основних концепцій до складних реалізацій, з практичними прикладами, які будуть корисні як новачкам, так і досвідченим практикам.
Основні концепції
Що таке таблиця фактів (Fact Table)?
Таблиця фактів — це основна таблиця в багатовимірній моделі, яка зберігає кількісну інформацію про бізнес-події. Уявіть її як запис того, "що сталося" у вашому бізнесі. Факти зазвичай є:
- Числовими вимірюваннями
- Адитивними (можна підсумовувати)
- Генеруються, коли відбуваються бізнес-події
- Зв'язуються з кількома вимірами
Що таке таблиця вимірів (Dimension Table)?
Таблиці вимірів надають контекст для бізнес-подій, зафіксованих у таблицях фактів. Вони відповідають на запитання "хто, що, де, коли, чому та як" стосовно ваших даних.
Розміри:
- Містять описові атрибути
- Зазвичай менші за таблиці фактів
- Змінюються рідше
- Надають точки входу для запитів до ваших даних
Таблиці фактів (Fact Tables) детальніше
Типи таблиць фактів (Fact Tables)
1 — Таблиці фактів транзакцій (Transaction Fact Tables)
- Найпоширеніший тип
- Фіксує події на найдрібнішому рівні
- Один рядок на кожну транзакцію
Приклад: Транзакція продажу в роздріб
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
date_key INT,
product_key INT,
store_key INT,
customer_key INT,
quantity_sold INT,
unit_price DECIMAL(10,2),
total_amount DECIMAL(10,2)
);
2 — Таблиці фактів періодичних знімків (Periodic Snapshot Fact Tables)
- Фіксують регулярні, періодичні знімки
- Корисні для аналізу статусу та балансу
- Фіксовані часові інтервали
Приклад: Щоденний знімок інвентарю
CREATE TABLE fact_daily_inventory (
date_key INT,
product_key INT,
warehouse_key INT,
quantity_on_hand INT,
quantity_reserved INT,
inventory_value DECIMAL(10,2)
);
3 — Таблиці фактів накопичувальних знімків (Accumulating Snapshot Fact Tables)
- Відслідковують етапи процесів
- Оновлюються при виникненні подій
- Містять кілька стовпців з датами
Приклад: Обробка замовлення
CREATE TABLE fact_orders (
order_key INT PRIMARY KEY,
customer_key INT,
order_date_key INT,
process_date_key INT,
ship_date_key INT,
delivery_date_key INT,
order_amount DECIMAL(10,2),
shipping_cost DECIMAL(10,2)
);
Таблиці вимірів (Dimension Tables) детальніше
Типи таблиць вимірів (Dimension Tables)
1 — Виміри типу 1 (Type 1 Dimensions)
- Перезаписують старі значення новими
- Не ведеться облік історії
- Легші у реалізації
Приклад: Простий вимір продукту
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_id VARCHAR(50),
product_name VARCHAR(100),
category VARCHAR(50),
brand VARCHAR(50),
unit_price DECIMAL(10,2)
);
2 — Виміри типу 2 (Type 2 Dimensions)
- Зберігають історичні зміни
- Створюють нові рядки для змін
- Містять дати дії
Приклад: Вимір клієнта з історією
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_id VARCHAR(50),
customer_name VARCHAR(100),
address VARCHAR(200),
segment VARCHAR(50),
valid_from DATE,
valid_to DATE,
is_current BOOLEAN
);
3 — Виміри типу 3 (Type 3 Dimensions)
- Зберігають обмежену історію
- Використовують додаткові стовпці для попередніх значень
- Добре підходять для відстеження простих змін
Приклад: Історія цін на продукт
CREATE TABLE dim_product_type3 (
product_key INT PRIMARY KEY,
product_id VARCHAR(50),
product_name VARCHAR(100),
current_price DECIMAL(10,2),
previous_price DECIMAL(10,2),
price_change_date DATE
);
Приклади з реального світу
Модель даних для електронної комерції
Таблиця фактів: Продажі
CREATE TABLE fact_sales (
sale_key INT PRIMARY KEY,
date_key INT,
product_key INT,
customer_key INT,
quantity INT,
unit_price DECIMAL(10,2),
discount_amount DECIMAL(10,2),
total_amount DECIMAL(10,2),
FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
FOREIGN KEY (product_key) REFERENCES dim_product(product_key),
FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key)
);
Підтримуючі таблиці вимірів:
Вимір клієнта (Customer Dimension):
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_id VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
address VARCHAR(200),
city VARCHAR(100),
country VARCHAR(100),
customer_since DATE
);
Вимір продукту (Product Dimension):
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_id VARCHAR(50),
product_name VARCHAR(100),
category VARCHAR(50),
subcategory VARCHAR(50),
brand VARCHAR(50),
supplier VARCHAR(100),
unit_cost DECIMAL(10,2),
unit_price DECIMAL(10,2)
);
Вимір дати (Date Dimension):
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
day_of_week VARCHAR(10),
day_of_month INT,
month_name VARCHAR(10),
quarter INT,
year INT,
is_weekend BOOLEAN,
is_holiday BOOLEAN
);
## **Розширені концепції**
### **Повільно змінювані виміри (SCD)**
Крім базових типів 1, 2 та 3:
- Тип 4: Використання таблиць історії
- Тип 6: Гібридний підхід, що поєднує типи 1, 2 та 3
- Виміри, що прибувають пізніше
### **Уніфіковані виміри (Conformed Dimensions)**
- Використовуються в кількох таблицях фактів
- Забезпечують консистентність у сховищі даних
- Приклад: Вимір клієнта, що використовується як у фактах продажу, так і в фактах підтримки
### **Рольові виміри (Role-Playing Dimensions)**
- Один і той самий вимір використовується кілька разів
- Різні бізнес-контексти. Приклад: Вимір дати, що використовується як: дата замовлення, дата відправки, дата доставки, дата оплати
## **Кращі практики**
**1 — Визначення зерна (Grain Definition)**
- Чітко визначте рівень деталізації в таблицях фактів
- Документуйте зерно в описах таблиць
- Забезпечте консистентність між пов'язаними таблицями
**2 — Використання сурогатних ключів (Surrogate Keys)**
- Використовуйте штучні ключі в таблицях вимірів
- Вони незалежні від ключів джерела системи
- Спрощують обслуговування та відстеження історії
**3 — Стратегія індексації (Indexing Strategy)**
- Індексуйте зовнішні ключі в таблицях фактів
- Створюйте відповідні індекси для часто запитуваних атрибутів вимірів
- Знайдіть баланс між продуктивністю запитів і витратами на обслуговування
**4 — Правила найменування (Naming Conventions)**
- Використовуйте однакові префікси (fact_, dim_)
- Включайте бізнес-контекст у назви
- Документуйте стандарти найменувань
## **Загальні проблеми та рішення**
**1 — Обробка кількох ієрархій**
Проблема: Продукти належать до кількох категорій. Рішення: Використання таблиць-перехрестів (Bridge Tables) або альтернативні варіанти ієрархій
Приклад таблиці-перехрестя:
CREATE TABLE bridgeproductcategory (
productkey INT,
categorykey INT,
PRIMARY KEY (productkey, categorykey)
);
```
2 — Обробка пізно прибулих фактів
Проблема: Факти приходять не за порядком. Рішення:
- Спроектуйте таблиці фактів для обробки оновлень
- Реалізуйте обробку дат дії
- Використовуйте таблиці проміжного зберігання (staging tables) для перевірки якості даних
3 — Управління швидко змінюваними вимірами
Проблема: Часті оновлення атрибутів вимірів. Рішення:
- Розгляньте використання Типу 4 SCD з окремими таблицями історії
- Використовуйте міні-виміри для часто змінюваних атрибутів
- Використовуйте гібридні підходи в залежності від бізнес-потреб
Висновок
Розуміння таблиць фактів і таблиць вимірів є критичним для побудови ефективних сховищ даних та аналітичних рішень.
Оволодівши цими концепціями та правильно їх впроваджуючи, організації можуть створювати надійні, масштабовані та легкі в обслуговуванні моделі даних, які ефективно задовольняють їх аналітичні потреби.
Пам'ятайте, що вибір між різними типами таблиць фактів (Fact Tables) та таблицями вимірів (Dimension Tables) повинен ґрунтуватися на:
- Бізнес-вимогах
- Шаблонах запитів
- Потребах у продуктивності
- Можливостях обслуговування
- Вимогах до відстеження історії
Ключем є знайти правильний баланс між складністю та функціональністю, водночас забезпечуючи, щоб модель даних залишалася зрозумілою та легкою в обслуговуванні з часом.
Перекладено з: Understanding Data Analytics Building Blocks: Fact Tables and Dimension Tables