OLTP (Online Transaction Processing) – это модель для оперативной транзакционной обработки данных. OLTP-системы обслуживают множество небольших операций (вставка, обновление, удаление) с минимальной задержкой. Главная цель – обеспечить быстрые и надежные транзакции (например, оформление заказа, банковский перевод) в режиме реального времениdecosystems.ru. Данные в OLTP носят текущий характер и хранятся в небольших объёмах, но система рассчитана на частые изменения и высокую конкурентную нагрузку. Например, интернет-магазин с OLTP-базой данных моментально регистрирует оплату клиента, обновляет остатки на складе и подтверждает заказdecosystems.ru. Преимущества OLTP: высокая скорость операций, поддержка большого числа одновременных пользователей, простое масштабирование под рост нагрузкиdecosystems.ru. Недостаток – такая система оптимизирована под транзакции, а не под сложные аналитические запросы.
OLAP (Online Analytical Processing) – модель для аналитической обработки данных. OLAP-системы предназначены не для записи транзакций, а для чтения больших массивов накопленных данных и выполнения сложных запросов, агрегирования и анализаdecosystems.ru. Их цель – поддержка бизнес-аналитики и стратегических решений. OLAP оперирует историческими данными в больших объёмах, часто денормализованными ради ускорения чтения. Например, OLAP-решение позволит проанализировать, какие товары лучше всего продавались за квартал и как спрос различается по регионамdecosystems.ru. OLAP-система обычно обновляется периодически из OLTP-источников и оптимизирована под тяжёлые выборки, построение отчётов и многомерный анализ данных (по времени, продуктам, клиентам и т.д.). Преимущества OLAP: возможность выявлять скрытые закономерности, анализировать тренды и строить прогнозы на основе больших исторических данныхdecosystems.ru. Недостатки: данные не такие актуальные (есть лаг из-за периодических загрузок), ниже скорость ответа на единичный запрос, сложность внедрения и поддержки отдельной аналитической базы.
Сравнение OLTP и OLAP: OLTP фокусируется на быстрых транзакциях и обеспечении целостности данных для повседневных операций, тогда как OLAP ориентирован на глубину анализа и гибкость сложных запросов. OLTP хранит только актуальные данные (например, текущий остаток на счёте), OLAP хранит исторические сведения за длительный период для выявления тенденцийdecosystems.ru. Структура OLTP, как правило, нормализована (чтобы избегать избыточности и обеспечивать целостность при обновлениях), а в OLAP часто используется денормализация (звездная схема и пр.) ради быстрого чтения. Пользователи тоже разные: OLTP-системой пользуются операционные сотрудники и сама система (для записи транзакций), OLAP-отчётами – аналитики и руководствоdecosystems.ru. В итоге, обе модели дополняют друг друга: OLTP – «сердце» оперативной работы, OLAP – «мозг» для анализа и отчетности. В связке они позволяют сначала надёжно собрать данные, а затем эффективно проанализировать их и поддержать принятие решенийdecosystems.ru.
В традиционных аналитических моделях (хранилищах данных) данные организуются в таблицы фактов и таблицы измерений. Эти понятия пришли из методологии Ральфа Кимбелла для моделирования данных в системах OLAP.
Таблица фактов – основная таблица хранилища, содержащая факты о бизнес-событиях или транзакциях, которые предстоит анализироватьru.wikipedia.orgru.wikipedia.org. Записи в таблице фактов обычно являются неизменяемыми фактами (например, продажа, транзакция) с максимально детальной гранулярностью – то есть каждая запись соответствует отдельному событию (например, одна строка – это покупка конкретного товара клиентом в определённое время)ru.wikipedia.orgru.wikipedia.org. Таблица фактов содержит числовые показатели (метрики), такие как количество, сумма, стоимость, продолжительность и т.д., по которым в дальнейшем вычисляются агрегаты (итоги, средние, минимумы/максимумы) при анализе. Также в таблице фактов хранятся внешние ключи на связанные таблицы измерений – обычно в виде составного ключа из нескольких ID, ссылающихся на размерыru.wikipedia.org. Например, в таблице фактов «Продажи» колонками могут быть: ID товара, ID клиента, ID даты, количество единиц, сумма продажи. Сотни тысяч и миллионы записей фактов хранятся с числовыми кодами вместо текстовых данных – подробное описание выносится в таблицы измерений, чтобы избежать дублирования текста в фактахru.wikipedia.org.
Таблица измерений – справочник, хранящий описательные атрибуты измерений (dimensions) бизнеса. Измерение представляет объект или сущность, по которой можно агрегировать и фильтровать факты. В таблице измерений хранятся свойства этой сущности. Например, измерение «Товар» будет содержать сведения о товарах: название, категория, бренд, цена, и т.п.; измерение «Дата» – атрибуты даты (год, месяц, квартал, день недели); измерение «Клиент» – данные о клиентском сегменте, регионе, пол и возраст и т.д. Таблицы измерений относительно небольшие по объему (содержат ограниченное число строк – количество уникальных элементов каждой сущности) и связаны с таблицей фактов через первичный ключ (ID), на который ссылаются фактыdocs.mybi.ru. Пример: если таблица фактов фиксирует продажи, то связанные таблицы измерений могут быть: «Товары» (описание товара по ID товара), «Клиенты» (описание клиента по ID), «Магазины» (локации магазинов по ID), «Календарь» (справочник дат). Показатели продажи (штуки, сумма) хранятся в фактах, а расшифровка что за товар или какой регион – в измерениях.
Важно, что в таблицах фактов нет текстовых описаний или категорий, по которым агрегируются данныеru.wikipedia.org. Все подобные атрибуты вынесены в таблицы измерений. Например, таблица фактов «Продажи» может содержать product_id и store_id, но не хранит название категории товара или город магазина – эти сведения берутся из соответствующих таблиц измерений («Товары» и «Магазины») по внешнему ключу. Такой подход обеспечивает целостность и компактность: описательная информация не дублируется в миллионах строк фактов, а хранится единожды в справочниках.
Модель «звезда» – это наиболее популярная схема организации аналитической базы данных. Она получила название из-за своей структуры: таблица фактов находится в центре, а вокруг нее лучами располагаются связанные таблицы измерений. Ключевой принцип звездообразной схемы – таблицы измерений не связаны друг с другом напрямую, все связи проходят только через таблицу фактовdocs.mybi.ru. Иными словами, у каждой таблицы измерений есть связь (foreign key) с фактом, но измерения между собой отношений не имеют. Такая денормализованная структура упрощает понимание данных и ускоряет выполнение запросов: достаточно соединить факт с нужными измерениями по их ключам. Звездная схема предоставляет простую и логически целостную модель, где каждый факт «окружен» своими измерениями, и запросы (например, «итоги продаж по категориям товаров и регионам за год») сводятся к агрегированию таблицы фактов с джойнами на несколько справочников.
Преимущества модели «звезда»: простота построения запросов (достаточно одной выборки из фактов с соединением с измерениями по ключам), понятность бизнес-пользователям (структура близка к привычным отчетам), производительность при больших объемах данных – за счет того, что факты уже содержат нужные для анализа ключи и не требуется множество вложенных джойновru.wikipedia.orgru.wikipedia.org. Кроме того, такую модель легче масштабировать горизонтально, разбивая по предметным областям (разные группы фактов могут разделять общие измерения, так называемые конформные измерения, что упрощает объединение данных разных фактовru.wikipedia.org).
Модель «снежинка» – это вариант аналитической схемы, являющийся расширением звезды. В снежинке допускаются связи между таблицами измерений – некоторые измерения нормализованы и разбиты на суб-таблицыdocs.mybi.ru. Графически такая схема напоминает снежинку: от факта отходят измерения, некоторые из них далее связаны с подчиненными измерениями. Например, если в звездной схеме был справочник «Товары» с атрибутом «Категория», то в снежинке его можно нормализовать: выделить отдельную таблицу «Категории» и связать ее с таблицей «Товары». Тогда факт «Продажи» связан с «Товары», а «Товары» – с «Категории». Когда применяется снежинка: когда размеры измерений очень велики или имеют явную иерархическую структуру. Разбиение на подтаблицы устраняет дублирование в измерениях и упрощает их обновление. В нашем примере, если иерархия категорий сложная, может быть оправдано вынести категории отдельно, вместо дублирования этой информации для каждого товара.
Недостатки модели «снежинка»: усложняется структура и запросы. Чем больше уровней связей между измерениями (A→B→C→D…), тем сложнее писать запросы и тем больше джойнов должен выполнить СУБД, что может негативно сказаться на производительностиdocs.mybi.ru. Также повышается риск запутаться в отношениях и получить неоднозначность, особенно если допустить циклические связи между измерениями (строго не рекомендуется). Поэтому опытные инженеры данных обычно избегают снежинок без необходимостиdocs.mybi.ru. Модель «звезда» рассматривается как предпочтительная, а «снежинка» допустима лишь в частных случаях, когда она действительно упрощает поддержку данных. Например, «снежинка» оправдана, если нужно отразить естественную многоуровневую иерархию справочников (страна → регион → город в измерении «География») или разделить очень крупный справочник на части.
Правило: если вы проектируете хранилище, старайтесь организовать данные как «звезду». Связи между разными сущностями закладывайте через факты. Прямые связи измерение-к-измерению допускайте только когда другого выхода нет (и убедитесь, что они образуют только иерархию, а не произвольный граф). Такой подход даст максимально понятную модель для аналитики и упростит написание SQL-запросовdocs.mybi.ru.
Хранилище данных – это специализированная информационная база, предназначенная для консолидации и хранения больших объёмов данных из разных источников с целью последующей аналитики и отчетностиru.wikipedia.org. В отличие от обычных операционных баз, ориентированных на конкретные приложения, хранилище является предметно-ориентированным и интегрированным хранилищем всей значимой информации организацииru.wikipedia.orgru.wikipedia.org. Проще говоря, это централизованная база знаний о бизнесе, куда стекаются данные из бухгалтерии, продаж, логистики, кадров и других систем, чтобы руководство и аналитики могли получать целостную картину деятельности компании.
В типичной компании данные изначально разрозненны: финансовые транзакции хранятся в одной базе, складские остатки – в другой, данные о сотрудниках – в третьей. Для межфункционального анализа (например, состояния компании за год) их нужно объединить в одном месте. Именно эту задачу решает корпоративное хранилище данныхselectel.ru. Data Warehouse собирает исторические данные из разных систем, очищает и структурирует их единообразно, и хранит длительный период (годами и десятилетиями) для анализа. Можно представить хранилище как библиотеку данных, где весь объём информации упорядочен и каталогизированselectel.ru. Благодаря этому аналитикам не нужно запрашивать доступы к разным отдельным базам – все нужные сведения собраны централизованно, часто в агрегированном виде за много летselectel.ru.
Использование DWH: данные из операционных баз (OLTP) регулярно выгружаются в хранилище по заранее настроенным процедурам. Обычно применяются ETL-процессы (Extract, Transform, Load): данные извлекаются из источников, преобразуются (очищаются, приводятся к общим форматам, агрегируются) и загружаются в хранилище. Обновление может выполняться пакетно, например каждую ночь или каждый час. При полной загрузке старые данные в DWH заменяются новыми, либо применяется инкрементальная загрузка измененийru.wikipedia.org. Это значит, что сведения в хранилище могут отставать от актуальных OLTP-данных на определенный интервал (lag), но взамен аналитические запросы не влияют на работу боевых системru.wikipedia.org. Пользователи хранилища, как правило, имеют доступ только на чтение: прямое редактирование данных в DWH не происходит – новые данные поступают только через процессы загрузкиru.wikipedia.org.
Архитектура DWH: существуют две основные стратегии построения хранилища – централизованное нормализованное хранилище (по Биллу Инмону) и федерация витрин данных (по Ральфу Кимбеллу). В первом случае все данные приводятся к 3НФ-модели и хранятся в большом количестве взаимосвязанных таблиц. Это обеспечивает гибкость, но затрудняет прямую аналитику – поэтому поверх нормализованных баз часто создают витрины данных (денормализованные таблицы или схемы «звезда» для конкретных отчётов)ru.wikipedia.orgru.wikipedia.org. Во втором случае сразу проектируют хранилище с измерениями: используют схемы «звезда» или «снежинка» для основных предметных областей, а общие измерения делятся между несколькими фактами (архитектура шины конформных измерений)ru.wikipedia.orgru.wikipedia.org. Такой подход ближе бизнес-пользователям – данные структурированы по понятным объектам (продажи, поставки, клиенты и т.д.), запросы выполняются быстрее благодаря меньшему числу таблиц и заранее подготовленным связямru.wikipedia.org. Минус – сложность процесса ETL: нужно заранее продумывать схемы измерений, отслеживать исторические изменения атрибутов (slowly changing dimensions) и т.д., что повышает трудоёмкость поддержки хранилищаru.wikipedia.org.