Лучшие модели данных затрудняют постановку неправильных вопросов и упрощают ответы на правильные.
Делиться
Ваша модель данных — это не технические характеристики. Это мышление с точки зрения бизнеса. Рассматривайте её как план всей вашей аналитической системы. Если план хаотичен, ваш дом рухнет. Если же он структурирован и организован, ваша команда быстро находит ценные выводы.
Вы смотрите на электронную таблицу, полную заказов клиентов, цен на продукцию и дат продаж. Она вся в беспорядке. Ваша «панель управления» работает медленно. Вы пытались ответить на простой вопрос: сколько выручки принесла пиццерия в прошлом квартале? — и получили цифры, которые не сходятся. Почему? Потому что ваша модель данных — это полный бардак.
В этой статье я расскажу вам об основных концепциях моделирования данных, которые должен знать каждый инженер-аналитик. Забудьте на секунду о Power BI и Microsoft Fabric. Речь пойдет о базовых принципах — о том, зачем нужны модели. Эти идеи работают независимо от используемого инструмента.
Давайте начнём с описания задачи. Представьте, что у вас крошечная пиццерия. Ваша «база данных» — это один лист Excel: ID заказа, Имя клиента, Адрес, Тип пиццы, Количество, Цена. Кажется, всё просто, правда? Проблема? Адрес Джона Смита повторяется в каждом заказе. Если он переедет, вам придётся редактировать 37 000 строк заказов, чтобы обновить его адрес. Не очень-то умно, правда?
Ваша модель данных — вот решение проблемы. Она буквально гласит: клиенты находятся в своей собственной таблице. Заказы связаны с клиентами без копирования адресов. Дело не в том, как вы будете визуализировать данные, а в том, как их организовать, чтобы данные имели смысл, когда вы задаете вопросы.
Однако моделирование данных начинается задолго до того, как ваши данные будут сохранены в электронной таблице или в реальной базе данных. В следующих разделах мы познакомим вас с основными концепциями моделирования данных — теми, которые следует применять в каждом сценарии моделирования данных, независимо от выбранного подхода к моделированию или инструмента, используемого для физической реализации.
Подобно тому, как архитектор не переходит от идеи к готовому зданию, специалист по моделированию данных не создает схему базы данных за один шаг. Процесс проходит три уровня с возрастающей детализацией, каждый из которых служит определенной цели и предназначен для определенной аудитории. Представьте себе, что это переход от концептуального эскиза к детальному архитектурному чертежу, а затем к окончательному строительному плану, используемому строителями.
Концептуальная модель: эскиз на салфетке
Любая отличная модель данных начинается не с кода или таблиц, а с диалога. Концептуальная модель — это самое первое, наиболее высокоуровневое представление ваших данных. Она совершенно нетехническая и сосредоточена исключительно на понимании и определении бизнес-концепций и правил, которые ими управляют.
Концептуальная модель определяет основные вещи или сущности, которые важны для бизнеса, и то, как они связаны друг с другом. Она создает словарь для общения с заинтересованными сторонами бизнеса, чтобы обеспечить взаимопонимание.
Представьте себе архитектора, встречающегося с клиентом в кафе. Клиент может сказать что-то вроде: «Я хочу семейный дом, который будет казаться открытым и взаимосвязанным». Архитектор берет салфетку и рисует несколько кружочков: кухня, гостиная, спальни, и проводит между ними линии с пометками «соединяется с» или «отдельно от». Нет ни размеров, ни материалов, ни технических деталей. Речь идет лишь о том, чтобы уловить основную идею и убедиться, что все согласны с фундаментальными концепциями. Этот эскиз на салфетке — это концептуальная модель данных.
Рассмотрим реальный пример — мероприятия на стадионе. В концептуальной модели для этого сценария вы бы выделили несколько сущностей: стадион, мероприятие, клиент, посетитель и билет. Вы также обратили бы внимание на то, как эти сущности взаимосвязаны. Этот общий обзор дает упрощенное представление о бизнес-процессе внутри организации.
Стадион имеет название и расположен в определенной стране и городе, что однозначно его идентифицирует. На этом стадионе может проводиться множество мероприятий, и на эти мероприятия может приходить много посетителей. Мероприятие не может проводиться за пределами стадиона, где оно запланировано. Мероприятие может посетить один посетитель, и на одном мероприятии может быть много посетителей. Посетитель — это субъект, который посещает мероприятие. Он также может быть клиентом стадиона — например, посещая музей стадиона или покупая что-либо в фанатском магазине, — но это не делает его посетителем конкретного мероприятия. Наконец, билет представляет собой подтверждение того, что посетитель посетит конкретное мероприятие. Каждый билет имеет уникальный идентификатор, и один посетитель может приобрести несколько билетов.
Возможно, вы сейчас задаетесь вопросом: почему это важно? Зачем кому-то тратить время и усилия на описание всех сущностей и связей между ними?
Помните, что концептуальная модель данных направлена на построение доверия между бизнес-пользователями и пользователями данных — на обеспечение того, чтобы заинтересованные стороны получали необходимую информацию, изложенную на понятном языке, чтобы они могли легко понять весь рабочий процесс. Создание концептуальной модели данных также позволяет заинтересованным сторонам определить целый ряд бизнес-вопросов, на которые необходимо ответить, прежде чем создавать что-либо материальное. Например: Являются ли Клиент и Участник одним и тем же субъектом (и почему нет)? Может ли один Участник купить несколько билетов? Что однозначно идентифицирует конкретное Мероприятие?
Кроме того, концептуальная модель данных отображает очень сложные бизнес-процессы в более доступной форме. Вместо того чтобы просматривать страницы письменной документации, вы можете взглянуть на иллюстрацию сущностей и связей, все объяснено в удобной для пользователя форме, и быстро понять основные элементы бизнес-процесса.
Логическая модель: План
После того как бизнес-подразделения и команды, работающие с данными, согласуют концептуальную модель данных, следующим шагом станет разработка логической модели данных . На этом этапе мы развиваем предыдущий шаг, определяя точную структуру сущностей и предоставляя более подробную информацию о взаимосвязях между ними. Необходимо определить все интересующие атрибуты для каждой сущности, а также мощность связей.
Обратите внимание, что, как и на этапе концептуального моделирования данных, мы по-прежнему не говорим о какой-либо конкретной платформе или решении. Основное внимание по-прежнему уделяется пониманию бизнес-требований и тому, как эти требования могут быть эффективно преобразованы в модель данных.
Для успешного преобразования концептуальной модели в логическую необходимо выполнить несколько шагов. Сначала необходимо определить атрибуты сущностей — конкретные данные, которые должна содержать каждая сущность. Затем определить потенциальные ключи — какой атрибут или набор атрибутов однозначно идентифицирует конкретную сущность. После этого, на основе результатов предыдущего шага, выбрать первичные ключи . Также необходимо применить нормализацию или денормализацию (подробнее об этом позже). Далее установить связи между сущностями , проверив, как сущности взаимосвязаны, и, при необходимости, разбив сложные сущности на несколько более простых. Затем определить кардинальность связи — количество экземпляров одной сущности, связанных с экземплярами другой. Существует три основных типа: один к одному (1:1), один ко многим (1:M) и многие ко многим (M:M). Наконец, что крайне важно, необходимо провести итерации и тонкую настройку . В реальной жизни практически невозможно сразу найти модель данных, которая бы устраивала всех. Необходимо запросить обратную связь от заинтересованных сторон и доработать логическую модель данных, прежде чем воплощать её в физической форме.
Потенциальные преимущества логической модели значительны. Во-первых, она служит лучшим тестом обеспечения качества, выявляя пробелы и проблемы в понимании бизнес-процессов, что в долгосрочной перспективе значительно экономит время и усилия. На этом этапе гораздо проще и дешевле исправлять ошибки, прежде чем привязываться к конкретной платформе. Создание логической модели данных можно рассматривать как часть гибкого цикла моделирования данных, что обеспечивает более надежные, масштабируемые и перспективные модели. И, наконец, она служит планом для окончательной физической реализации.
Физическая модель: План строительства
Физическая модель данных представляет собой завершающий этап: то, как модель данных будет фактически реализована в конкретной базе данных. В отличие от концептуальных и логических моделей данных, которые не зависят от платформы и решения, физическая реализация требует определения низкоуровневых деталей, которые могут быть специфичны для конкретного поставщика баз данных.
Для успешной реализации физической модели данных необходимо выполнить целый ряд шагов. Вам нужно выбрать платформу — это решение определяет ваши будущие принципы проектирования. Затем преобразуйте логические сущности в физические таблицы — поскольку реальная база данных не поддерживает абстрактный уровень логических сущностей, вам необходимо определить тип данных для каждого атрибута: целое число, десятичное число или простой текст. Кроме того, каждая физическая таблица должна использовать ключи (первичные, внешние, уникальные) для обеспечения целостности данных.
Также необходимо установить связи на основе ключевых столбцов. Примените нормализацию или денормализацию в зависимости от ситуации — помните, что в системах OLTP таблицы следует нормализовать (обычно до 3NF) для уменьшения избыточности и эффективной поддержки операций записи, в то время как в системах OLAP данные могут быть денормализованы для исключения объединений и повышения производительности операций чтения.
Определите ограничения для таблиц , чтобы обеспечить целостность данных — не только для ключей, но и для логических проверок. Например, если в вашей таблице хранятся оценки студентов в диапазоне от 5 до 10, почему бы не определить это ограничение для столбца, предотвращающее вставку бессмысленных значений?
Создавайте индексы и/или разделы — это специальные физические структуры данных, повышающие эффективность модели данных. Например, секционирование таблиц разделяет одну большую таблицу на несколько меньших подтаблиц, сокращая время сканирования при выполнении запросов. Классический подход — секционирование по календарному году. И наконец, расширяйте возможности с помощью программных объектов — хранимых процедур, функций, триггеров — которые де-факто являются стандартом практически в каждом решении для работы с данными.
Главное преимущество физической модели данных заключается в обеспечении эффективности, оптимальной производительности и масштабируемости. Когда мы говорим об эффективности, мы имеем в виду два самых ценных актива бизнеса: время и деньги. Если же вы считаете, что время равно деньгам, то вам следует учитывать только один актив. Чем эффективнее ваша модель данных, тем больше пользователей она может обслуживать, тем быстрее она это делает, и это в конечном итоге приносит бизнесу больше денег.
Зачем все три? Потому что исправление пробела в концептуальной модели требует обсуждения. Исправление его в физической модели требует спринта. Чем раньше вы обнаружите проблемы, тем дешевле будет их решить.
OLTP против OLAP: письмо против чтения
Системы обработки онлайн-транзакций (OLTP)
Чтобы стать успешным инженером-аналитиком, необходимо прежде всего понимать, откуда берутся данные. Подавляющее большинство бизнес-данных создается не для аналитики. Они создаются приложениями, которые обеспечивают повседневную работу бизнеса: системой продаж, инструментом управления взаимоотношениями с клиентами (CRM), базой данных на бэкэнде интернет-магазина и многими другими.
Эти исходные системы называются системами обработки онлайн-транзакций (OLTP) . Они разработаны и оптимизированы для достижения одной главной цели: быстрой и надежной обработки большого объема транзакций. Системы OLTP должны мгновенно подтверждать заказ клиента или обновлять его адрес доставки. Скорость и целостность данных при записи имеют первостепенное значение.
Для достижения этой цели в системах OLTP используется реляционная модель данных, которая в значительной степени нормализована. Давайте подробно разберемся, что это на самом деле означает.
Нормализация: Библиотечный карточный каталог
Нормализация — это процесс организации данных в базе данных с целью минимизации избыточности данных и повышения их целостности. Проще говоря, это означает, что не следует повторять информацию, если в этом нет необходимости.
Представьте себе библиотеку докомпьютерной эпохи. У каждой книги есть карточка-указатель. Если бы на каждой карточке для каждой написанной им книги нужно было писать полное имя автора, его гражданство и дату рождения, это было бы утомительно. Вы бы писали «Уильям Шекспир, английский, 1564–1616» на карточках для «Гамлета», «Макбета» и «Ромео и Джульетты». А если бы вы обнаружили ошибку в годе рождения Шекспира, вам пришлось бы искать и исправлять каждую карточку для каждой написанной им книги. Практически гарантировано, что вы бы пропустили хотя бы одну.
Грамотный библиотекарь использовал бы нормализацию. Он бы создал отдельный карточный каталог авторов. На карточке «Гамлета» было бы написано просто «ID автора: 302». Затем вы бы зашли в каталог авторов, нашли ID 302 и обнаружили бы все данные о Уильяме Шекспире в одном месте. Если вам нужно внести исправление, вам нужно сделать это только один раз.
Нормальные формы
В этом и заключается суть нормализации: разбиение данных на множество небольших дискретных таблиц, чтобы избежать повторений. Правила для этого называются нормальными формами (1NF, 2NF, 3NF…). Всего существует семь нормальных форм, хотя в большинстве реальных ситуаций нормализация данных до третьей нормальной формы (3NF) считается оптимальной.
Давайте кратко рассмотрим ключевые принципы, лежащие в основе первых трех нормальных форм. Первая нормальная форма (1НФ) исключает повторяющиеся группы. Каждая ячейка должна содержать одно значение, и каждая запись должна быть уникальной. Вторая нормальная форма (2НФ) основывается на 1НФ и гарантирует, что все атрибуты зависят от всего первичного ключа — это в основном актуально для таблиц с составными ключами. Третья нормальная форма (3НФ) основывается на 2НФ и гарантирует, что ни один атрибут не зависит от другого неключевого атрибута. Вот пример с библиотекой: AuthorNationality не зависит от книги; он зависит от автора. Поэтому AuthorNationality переносится в таблицу Authors.
Рассмотрим пример «до и после». Представьте себе ненормализованную электронную таблицу для отслеживания заказов: OrderID, OrderDate, CustomerID, CustomerName, CustomerCity, ProductID, ProductName, Qty, UnitPrice — все в одной плоской таблице. Обратите внимание на повторения? Имя и город Джона Смита повторяются. Название и цена виджета A повторяются. Чтобы обновить цену виджета A, вам нужно изменить ее в двух местах, и это только в небольшом примере.
Чтобы нормализовать эти данные до 3NF, мы разбиваем их на четыре отдельные таблицы: таблицу клиентов (CustomerID, CustomerName, CustomerCity), таблицу товаров (ProductID, ProductName, UnitPrice), таблицу заказов (OrderID, OrderDate, CustomerID) и таблицу сведений о заказе (OrderID, ProductID, Qty). Теперь, если Джон Смит переезжает в Лос-Анджелес, мы обновляем информацию о его городе ровно в одном месте. Если цена виджета A меняется, мы обновляем ее ровно в одном месте. Это идеально подходит для OLTP-системы.
Однако жизнь — это не сказка. И вот в чем загвоздка. Хотя эта нормализованная структура отлично подходит для записи данных, она неэффективна для их анализа. Чтобы ответить на простой вопрос, например: «Какова общая сумма продаж товаров в категории „Виджеты“ клиентам в Нью-Йорке?», вам пришлось бы выполнить множество сложных операций JOIN по всем этим небольшим таблицам. При наличии десятков или даже сотен таблиц эти запросы становятся невероятно медленными и превращаются в кошмар для ваших бизнес-пользователей.
Это подводит нас к основной задаче инженера-аналитика: преобразованию данных из модели, оптимизированной для записи (OLTP), в модель, оптимизированную для чтения (OLAP) .
Системы оперативной аналитической обработки (OLAP)
Если системы OLTP предназначены для управления бизнесом, то системы оперативной аналитической обработки (OLAP) — для понимания бизнеса. Наша главная цель как инженеров-аналитиков — создавать системы OLAP. Эти системы предназначены для максимально быстрого ответа на сложные бизнес-вопросы при обработке больших объемов данных.
Денормализация: стратегический разворот
Начнём с объяснения денормализации . Как вы, вероятно, правильно догадались, при денормализации мы стратегически обращаем вспять процесс нормализации, который мы рассматривали ранее. Мы намеренно объединяем множество небольших таблиц в несколько более крупных и широких таблиц, даже если это означает повторение некоторых данных и создание избыточности.
Денормализация по сути представляет собой компромисс: мы жертвуем частью места для хранения и эффективностью операций обновления ради потенциально значительного повышения производительности запросов и удобства использования. Денормализация — одна из ключевых концепций для реализации методов моделирования многомерных данных, которые будут рассмотрены далее.
Многомерное моделирование: схема «Звезда» и не только.
Многомерная модель представляет собой эталонную парадигму при проектировании OLAP-систем. Прежде чем объяснить многомерный аспект, давайте кратко рассмотрим историю. Книга Ральфа Кимбалла «Инструментарий хранилища данных» (Wiley, 1996) до сих пор считается библией многомерного моделирования. В ней Кимбалл представил совершенно новый подход к моделированию данных для аналитических задач — так называемый подход «снизу вверх». Основное внимание уделяется выявлению ключевых бизнес-процессов внутри организации и их моделированию в первую очередь, прежде чем вводить дополнительные бизнес-процессы.
Подход Кимбалла элегантен в своей простоте. Он состоит из четырех шагов, каждый из которых основан на принятии решения:
Шаг 1: Выберите бизнес-процесс. Рассмотрим пример: представьте, что продажа билетов на мероприятие — это интересующий нас бизнес-процесс. Данные, собираемые в ходе этого процесса, могут включать: Событие, Место проведения, Клиент, Количество, Сумма, Сотрудник, Тип билета, Страна и Дата.
Шаг 2: Укажите уровень детализации. Уровень детализации означает самый низкий уровень детализации, охватываемый бизнес-процессом. В нашем примере самый низкий уровень детализации — это продажа отдельного билета. Выбор правильного уровня детализации имеет первостепенное значение в многомерном моделировании — он определяет, что представляет собой каждая строка в вашей таблице фактов.
Шаг 3: Определите измерения. Измерение — это особый тип таблицы, которую мы называем справочной таблицей. В ней вы можете найти более подробную информацию об определенном объекте. Представьте себе человека: как бы вы его описали? По имени, полу, возрасту, физическим характеристикам, адресу электронной почты, номеру телефона. Похожий товар — по названию, категории, цвету, размеру. Таблицы измерений обычно отвечают на вопросы, начинающиеся с «Кто?»: Когда мы продали билет? Где мы продали билет? Какой тип билета мы продали? Кто был покупателем?
Шаг 4: Определите факты. Если рассматривать измерение как справочную таблицу, то таблица фактов хранит данные о событиях — о том, что произошло в результате бизнес-процесса. В большинстве случаев эти события представлены числовыми значениями: Сколько билетов мы продали? Какую выручку мы получили?
Представьте себе измерения как таблицы поиска — они описывают контекст. Когда мы продали билет? Где? Какой тип? Кто был покупателем? А факты представьте как события: сколько билетов, какой доход.
Преимущества размерного моделирования
Прежде чем перейти к физическим реализациям, давайте еще раз подчеркнем ключевые преимущества. Во-первых, удобная навигация по данным : пользователям проще рассматривать бизнес-процессы с точки зрения субъектов, которые в них участвуют. На какое мероприятие было продано больше всего билетов в прошлом квартале? Сколько билетов на финал Лиги чемпионов купили женщины? Какой сотрудник в США продал больше всего VIP-билетов на Суперкубок?
Во-вторых, производительность : OLAP-системы разработаны для быстрого и эффективного чтения данных, что означает меньшее количество объединений между таблицами. Именно это обеспечивает многомерное моделирование с помощью звездообразной схемы. В-третьих, гибкость : изменился ли адрес вашего клиента? Изменилась ли должность вашего сотрудника? Такие изменения можно обрабатывать с помощью медленно изменяющихся измерений — подробнее об этом чуть позже.
Многомерное моделирование, хотя и является лишь подмножеством моделирования данных, представляет собой одну из важнейших концепций для внедрения эффективных и масштабируемых решений в области аналитической инженерии в реальных условиях.
Главное преимущество многомерных моделей — их гибкость и адаптивность. Вы можете добавлять новые факты в существующую таблицу фактов, создавая новый столбец (при условии, что новые факты соответствуют существующей детализации). Вы можете добавлять новые атрибуты поиска, добавляя внешний ключ к новому измерению. Вы можете расширять существующие измерения новыми атрибутами, просто добавляя столбцы. Ни одно из этих изменений не нарушает работу существующих запросов или приложений бизнес-аналитики.
Схема «Звезда и снежинка»
Если вы окажетесь в окружении опытных специалистов по моделированию данных, вы, вероятно, услышите от них разговоры о звёздах и снежинках. Это, пожалуй, самые влиятельные концепции в мире многомерного моделирования.
Звездная схема: Все еще король
По мнению Ральфа Кимбалла, каждый фрагмент данных следует классифицировать как что, когда, где, кто, почему, или как сколько или как много. В хорошо спроектированной многомерной модели должна быть центральная таблица, содержащая все измерения и события — таблица фактов — окруженная таблицами измерений или справочными таблицами. Таблицы фактов и измерений связаны посредством отношений, установленных между первичным ключом из таблицы измерений и внешним ключом из таблицы фактов. Такая структура напоминает звезду — отсюда и название.
Несмотря на многочисленные дискуссии, ставящие под сомнение актуальность звездообразной схемы для современных решений на основе платформ данных из-за ее устарелости, справедливо будет сказать, что эта концепция по-прежнему абсолютно актуальна и, безусловно, наиболее широко используется при проектировании эффективных и масштабируемых систем бизнес-аналитики.
Схема снежинки
Схема «снежинка» очень похожа на схему «звезда». Концептуально между ними нет никакой разницы — в обоих случаях вы размещаете информацию о том, кто, что, когда, где и почему, в таблицах измерений, а информацию о том, сколько и как много, оставляете в таблице фактов. Единственное различие заключается в том, что в схеме «снежинка» измерения нормализованы и разбиты на подизмерения, поэтому она и напоминает снежинку.
Основная цель нормализации измерений — устранение избыточности данных в таблицах измерений. Хотя это может показаться желательным подходом, нормализация измерений сопряжена с рядом серьезных проблем: общая структура модели данных становится более сложной, а производительность может снизиться из-за объединений между нормализованными таблицами измерений.
Конечно, существуют специфические сценарии использования, когда нормализация размерности может быть более целесообразным вариантом, особенно когда речь идёт об уменьшении размера модели данных. Однако следует помнить, что схема «снежинка» должна быть скорее исключением, чем правилом при моделировании данных для задач аналитической инженерии.
Постепенно меняющиеся измерения: управление неизбежным
Вы знаете старую поговорку: «Единственная константа в жизни — это перемены»? Так вот, это в равной степени верно и для ваших данных, и для жизни. В реальном мире ничто не стоит на месте. Ваш клиент переезжает в другой штат. Ваш ключевой продукт получает новое название и категорию. Ваш лучший сотрудник получает повышение и назначение в новый регион.
Если мы будем просто слепо обновлять эти записи в нашем хранилище данных — например, перезаписывая старый адрес клиента новым — мы можем столкнуться с огромной проблемой: мы потеряем историю . Мы больше не сможем отвечать на такие важные исторические вопросы, как «Какой доход мы получили от этого клиента, пока он жил в Нью-Йорке?» или «Как продавался этот продукт до того, как мы провели ребрендинг?»
Здесь вступает в игру концепция медленно изменяющихся измерений (SCD) . SCD — это просто формальная стратегия управления изменениями в таблицах измерений — таблицах, описывающих, кто, что, где и как, — чтобы вы могли точно отслеживать историю и гарантировать достоверность ваших исторических отчетов.
Хотя существует семь типов медленно изменяющихся измерений, мы сосредоточимся на двух, наиболее часто используемых в сценариях моделирования данных: медленно изменяющиеся измерения типа 1 и медленно изменяющиеся измерения типа 2.
SCD Тип 1: Забывчивая перезапись
Рассматривайте SCD типа 1 как перезапись по забывчивости. Это самый простой в реализации вариант, и он также наиболее нетерпим к истории изменений. Когда атрибут измерения изменяется (например, адрес электронной почты), вы просто перезаписываете старое значение новым. Готово. Изменение происходит мгновенно и необратимо.
Представьте, что вы исправляете опечатку в статье Википедии. Вы редактируете страницу, нажимаете «Сохранить», и старая, неверная версия исчезает навсегда. Никто не вспомнит старую опечатку, если не будет копаться в истории изменений, а этого вы точно не хотите, чтобы приходилось делать вашим аналитикам!
Если для вашего бизнеса не важна история какого-либо атрибута (например, основного номера телефона клиента), то тип 1 — это простое и понятное решение.
SCD Тип 2: Золотой стандарт — путешествия во времени
Второй тип анализа — это золотой стандарт в аналитической инженерии, и это наиболее распространенный подход для всего, что вашей компании необходимо анализировать с помощью исторических данных. Когда атрибут изменяется (например, город клиента), вы никогда не обновляете старую запись. Вместо этого вы создаете совершенно новую запись для хранения новой версии измерения. Именно так достигается «путешествие во времени» в ваших отчетах.
Каждый раз, когда изменяется ключевой атрибут, появляется новая строка. Это позволяет сохранить все предыдущие версии записи в неизменном виде, каждая из которых действительна в течение определенного периода времени. Но как сделать так, чтобы у одного клиента оказалось три разные строки в таблице измерений, не создавая беспорядка в системах отчетности? Для управления временной шкалой мы используем три специальных поля:
Суррогатный ключ (сгенерированный компьютером идентификатор) — это наиболее важное поле. У вашего первоначального клиента может быть идентификатор CUST123, но его первая версия адреса получает уникальный ключ, например, DIM_CUST_ID_1. При переезде новая версия получает DIM_CUST_ID_2. Именно по этому суррогатному ключу будут выполняться соединения в таблицах фактов, гарантируя, что вы соединяете именно ту версию клиента, которая существовала на момент совершения транзакции.
Временные интервалы (дата начала и дата окончания) определяют период, в течение которого конкретная запись была действительна. А флаг «Текущий» — это простой индикатор «да/нет»: только одна запись для каждого клиента будет иметь этот флаг в значении «истина», что значительно упрощает работу аналитикам, которым нужна только текущая версия.
Позвольте мне привести конкретный пример. Представьте себе сотрудницу Сару Джонс, идентификатор сотрудника 123. Она начала работать менеджером по продажам в западном регионе. Когда в октябре 2023 года её повысили до регионального директора, мы не стали перезаписывать её старую запись. Вместо этого мы создали новую строку (суррогатный ключ 2) с её новой должностью, обновили дату окончания работы в старой строке и установили флаг текущего состояния в значение FALSE. Затем, когда в мае 2024 года она перешла из западного региона в северный, мы повторили процесс — ещё одна новая строка (суррогатный ключ 3), ещё одно обновление даты окончания работы, ещё одно изменение флага. Теперь у нас есть три строки, содержащие полную профессиональную историю Сары, и мы можем анализировать её производительность в любой должности, в любом регионе, в любой момент времени.
SCD Type 2 — это де-факто стандарт для современной аналитической инженерии, поскольку он предоставляет аналитикам возможность получения абсолютно точных исторических данных. Он позволяет «путешествовать» во времени при составлении отчетов. Хотя его создание и поддержка несколько сложнее, чем простая перезапись данных Type 1, его ценность в плане надежной, проверяемой и точной бизнес-аналитики неоспорима. Если вам нужно узнать, как обстояли дела вчера, в прошлом месяце или пять лет назад, SCD Type 2 — это тот секретный ингредиент, который делает это возможным.
Различные типы таблиц фактов
Вы уже узнали, что таблицы фактов хранят измеримую информацию и отвечают на такие вопросы, как «Сколько?» или «Как много?». Однако не все измерения одинаковы. Вы же не будете использовать один и тот же блокнот для составления списка покупок и для отслеживания хода строительного проекта в течение года. Именно поэтому у нас есть четыре основных типа таблиц фактов, каждый из которых предназначен для определенного вида бизнес-измерений.
Таблица фактов по транзакциям
Это самый простой, наиболее распространенный и, пожалуй, самый понятный тип таблиц фактов. Транзакционная таблица фактов фиксирует одно мгновенное событие. Каждая строка — это как вспышка фотоаппарата, запечатлевшая то, что произошло прямо сейчас.
Ключевые особенности заключаются в том, что каждая строка представляет собой отдельный, атомарный момент — один клик, одна позиция заказа, одна попытка входа в систему, один перевод средств — и что показатели являются полностью аддитивными , то есть вы можете безопасно суммировать их по любому из ваших параметров.
Прекрасный пример — подробная информация о товарах в кассовом чеке. Когда вы покупаете продукты, каждый отсканированный товар представляет собой отдельную строку в таблице фактов. Эта единственная строка связывает идентификатор конкретного товара, идентификатор клиента, идентификатор магазина, дату и время транзакции, а также сумму продажи. Транзакционные таблицы фактов — это основа вашего хранилища данных.
Таблица фактов периодического снимка
В некоторых ситуациях вас не интересует каждое отдельное событие; вас интересует состояние дел в определенный момент времени. Для этого существует периодическая таблица фактов-снимков . Вместо записи событий эта таблица фиксирует показатели вашего бизнеса по фиксированному, повторяющемуся графику — например, в последний день месяца или в конце каждой недели.
Ключевые характеристики заключаются в том, что строка создается только в заранее определенное время, отражая состояние многих вещей одновременно, и что показатели являются полуаддитивными . Вот тут-то и начинаются сложности: обычно можно суммировать показатели по большинству параметров (например, суммировать общий запас по всем магазинам), но нельзя суммировать их по времени. Если у вас есть данные об уровне запасов за понедельник (100 единиц) и вторник (100 единиц), то общий запас за два дня составляет не 200, а все равно 100.
Вспомните свою ежемесячную банковскую выписку. В ней не записывается каждая купленная вами чашка кофе за месяц (это данные о транзакциях). В ней просто указан баланс счета на первый и последний день месяца. Показатели, часто фиксируемые в периодических сводных таблицах, включают в себя наличие товаров на складе, текущую численность персонала, количество открытых заказов или ежемесячный баланс счета.
Накопительная таблица фактов.
Если вам необходимо отслеживать ход выполнения определенного многоэтапного процесса от начала до конца, вам понадобится таблица фактов с накопительным снимком . Эта таблица уникальна тем, что записи в ней не статичны. Строка создается в начале процесса, и эта же строка обновляется по мере прохождения процессом ключевых этапов.
Каждая строка представляет собой один полный экземпляр процесса — отдельный заказ клиента или одну страховую заявку. В отличие от других типов, эти строки намеренно изменяются со временем, чтобы зафиксировать ключевые даты. Основные выводы получаются путем расчета показателей продолжительности — времени, прошедшего между контрольными точками.
Лучшим примером может служить курьерская служба, такая как UPS или FedEx, с системой отслеживания посылок. При оформлении заказа создается строка. Затем эта строка обновляется с указанием даты заказа, даты отправки, даты отправки и даты доставки. Все данные привязаны к одному и тому же номеру отслеживания. Это позволяет задавать такие вопросы, как: «Каково среднее время между датами размещения заказа и даты отправки для всех открытых заказов?»
Таблица фактов без фактов
Подождите, таблица фактов без фактов? Да, именно так. В таблице фактов без фактов нет никаких числовых показателей. Ее единственная задача — зафиксировать взаимосвязь между измерениями. Единственный показатель, который она измеряет, — это простой подсчет строк: она показывает, что произошло или что должно было произойти.
Представьте себе таблицу, в которой указаны все студенты, посетившие все обязательные занятия в определенный день. Если студент отсутствует в таблице на обязательном занятии, можно сделать вывод о его отсутствии. Это используется для подсчета событий (или пропущенных событий), где наличие связи между ними является важным фактором.
Выбор правильного типа таблицы фактов
Вот несколько кратких советов о том, когда использовать тот или иной тип таблицы фактов. Используйте транзакционную таблицу фактов , когда вам нужно записывать каждую мельчайшую деталь текущего события — например, каждую продажу. Используйте периодическую таблицу фактов-снимков , когда вам нужно проверять статус множества вещей в повторяющемся календаре — например, подсчет запасов или состояние банковского счета. Используйте накапливающую таблицу фактов-снимков , когда вам нужно отслеживать жизненный цикл сложного процесса от начала до конца — например, выполнение заказа. И используйте таблицу фактов без фактов, когда вам нужно зафиксировать, что что-то произошло (или не произошло), и само существование связи является мерилом.
Главный вывод
В этой статье мы рассмотрели множество аспектов. Сначала мы обсудили, как должен начинаться рабочий процесс моделирования данных, с создания концептуальной модели . Это связующее звено между техническими и бизнес-пользователями, служащее шаблоном для последующих этапов процесса и объясняющее сложные технические термины на языке, понятном бизнес-пользователям. Логическая модель данных обеспечивает наилучшую проверку качества, позволяя быстро выявлять потенциальные пробелы в понимании всего бизнес-процесса. Наконец, физическая модель гарантирует эффективность, оптимальную производительность и масштабируемость.
Затем мы провели различие между системами OLTP и OLAP. В системах OLTP акцент делается на скорости записи данных, тогда как в системах OLAP нас в основном интересует скорость чтения данных. В сценариях моделирования данных в системах OLAP многомерное моделирование считается стандартом де-факто, а звездообразная схема является доминирующим методом реализации во многих аналитических решениях.
Мы исследовали медленно меняющиеся параметры — от простой перезаписи типа 1 до сохраняющей историю таблицы типа 2, которая дает вам возможность «путешествовать во времени» в ваших отчетах. И мы углубились в четыре типа таблиц фактов , каждая из которых предназначена для определенного вида бизнес-измерений, от базовой транзакционной таблицы фактов до загадочной таблицы фактов без фактов.
В этой статье мы поставили перед собой амбициозную задачу: развеять мифы и объяснить концепции и методы, которые сами по себе могли бы занять целые книги. Поэтому рассматривайте это как вводное пособие или мягкое введение в основные принципы моделирования данных, которые вам понадобятся в повседневной работе в качестве инженера-аналитика. Это определенно не конец истории моделирования данных — наоборот, мы лишь слегка затронули тему. Поэтому я настоятельно рекомендую вам продолжить изучение моделирования данных, поскольку это бесспорный навык для каждого инженера-аналитика.
Спасибо за прочтение!
Источник: towardsdatascience.com
