Совет №37
Моделирование конвейера с помощью накопительного снимка

Материал опубликован с разрешения компании Ralph Kimball Associates
Автор оригинала: Ральф Кимбал (все статьи)
Перевод на русский язык: Антон Задорожный
Оригинальный документ располагается здесь.

В журнале Intelligent Enterprise, в статье «Фундаментальные уровни гранулярности» я описал три различных типа гранулярности, к которым, похоже, сводятся все таблицы фактов. Помните, что таблица фактов является местом, где мы храним показатели, являющиеся результатом определенного бизнес-процесса. Таблицы измерений окружают и описывают эти показатели.

Помните также, что уровень гранулярности таблицы фактов – это точное определение того, что представляет собой запись таблицы фактов. Совершенно точно, первичный ключ таблицы фактов является уровнем гранулярности, но часто самым понятным определением уровня гранулярности является бизнес-понятие, нежели технический список внешних ключей. Для примера, уровень гранулярности таблицы фактов, представляющей процесс принятия заказа может быть «строка позиции в заказе», тогда как техническое описание ключа для этой таблицы может быть «номер накладной/ продукт/ рекламная акция».

Все те тысячи таблиц фактов, которые я видел и изучал, разделяются на три фундаментальных уровня гранулярности:

1. Транзакционный уровень (TRANSACTION), представляющий точку в пространстве и времени.
2. Уровень периодического снимка (PERIODIC SNAPSHOT), представляющий регулярные промежутки времени, один за другим
3. Уровень накопительного снимка (ACCUMULATING SNAPSHOT), представляющий всю историю сущности

Уровень накопительного снимка необычен по ряду причин. В отличие от других уровней гранулярности, накопительный снимок обычно имеет ряд временных измерений, представляющих определенные шаги имевшие место в жизни «накапливаемой сущности». Например, заказ является:

1. созданным
2. подтвержденным
3. отгруженным
4. доставленным
5. оплаченным и, возможно
6. возвращенным

Таким образом, разработка таблицы фактов накопительного снимка заказов может быть начата с шести временных ключей, все являются внешними ключами, ссылающимися на представления, основанные на одной таблице измерения со значениями-датами. Эти шесть представлений называются «ролями» таблицы дат, и они являются семантическими независимыми, как если бы были отдельными физическими таблицами, так как мы определили их как отдельные представления.

Другим необычным аспектом таблицы фактов накопительного снимка является то, что мы изменяем одну и ту же запись каждый раз, физически изменяя внешние ключи и измеряемый факт, по мере того как (обычно короткая) жизнь сущности обрастает подробностями. Процесс заказов – это классический пример.

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

В случае отслеживании поступающих, абитуриенты проходят через стандартный набор препятствий или контрольных точек. Мы заинтересованы в отслеживании не менее 15 шагов процесса, включающих 1) получение результатов предварительного теста 2) запрос информации (через веб или другим способом) 3) отправка информации 4) прохождение интервью 5) посещение кампуса 6) получение заявления 7) получение копии 8) получение результатов теста 9) получение рекомендаций 10) первое прохождение приемной комиссии 11) рассмотрение заявления для финансовой помощи 12) финальное решение приемной комиссии 13) студент принят 14) студент допущен 15) студент зачислен. В каждый момент времени руководителям приемной комиссии и департамента по зачислению интересно, сколько абитуриентов находится на каждой стадии процесса. Это во многом похоже на воронку, где большое число соискателей входят в «трубу», и только небольшое число доходит до конца. Руководители также хотят анализировать пул абитуриентов по множеству характеристик. В примере с приемной комиссией мы можем быть уверены, что существует очень богатое измерение «Абитуриент» наполненное интересной демографической информацией.

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

Ключевой компонент схемы – это набор 15 численных фактов, каждый из которых принимает значение 0 или 1, отражая прохождение соискателем одного из 15 шагов описанных выше. Хотя технически эти 15 фактов 0/1 могут быть сведены к 15 датам, дополнительные численные факты делают приложение элегантным и легким в использовании в практически любом средстве отчетности.

В качестве дополнительного бонуса мы введем 4 числовых аддитивных факта, представляющих «лаги» или временные задержки между определенными важными шагами в процессе. Они включают:

Информация запрошена ==> Задержка отправки
Заявка подана ==> Задержка выполнения
Заявка подана ==> Задержка финального решения
Финальное решение ==> Задержка принятия или отклонения

Эти факты задержек хороши как для выявления застрявших заявок, так и как помощь руководителям для оптимизации процесса путем выявления узких мест.

В итоге финальная схема нашей таблицы фактов выглядит так:

Идентификатор даты получение результатов предварительного теста (FK)
Идентификатор даты запроса информации (FK)
Идентификатор даты отправки информации (FK)
Идентификатор даты проведения собеседования (FK)
Идентификатор даты посещения кампуса (FK)
Идентификатор даты представление заявления (FK)
Идентификатор даты получения копии (FK)
Идентификатор даты получения результатов теста (FK)
Идентификатор даты получения рекомендаций (FK)
Идентификатор даты первого прохождения приемной комиссии (FK)
Идентификатор даты рассмотрения для финансовой помощи (FK)
Идентификатор даты принятия финального решения (FK)
Идентификатор даты получения абитуриентом решения (FK)
Идентификатор даты допуска абитуриента (FK)
Идентификатор даты зачисления абитуриента (FK)
Идентификатор абитуриента (FK)
Идентификатор решения комиссии (FK)
Количество принятых результатов предварительного теста
Количество запросов информации
Количество отправок информации
Задержка между запросом и отправкой информации
Количество пройденных интервью
Количество визитов в кампус
Количество представленных заявлений
Количество полученных копий
Количество полученных результатов теста
Количество полученных рекомендаций
Количество выполненных заявлений
Задержка между представлением и исполнением заявления
Количество первых прохождений приемной комиссии
Количество рассмотрений для финансовой помощи
Количество финальных решений
Задержка между представлением заявления и принятием финального решения
Количество принятых
Количество отклоненных
Задержка между принятием финального решения и принятием/отклонением
Количество допущенных
Количество зачисленных

Интересный дизайн! Представьте, как легко будет рассчитать состояние конвейера в любой момент времени. Хотя записи, очевидно, являются широкими, это не особенно большая таблица. Если вы – большой государственный университет со 100’000 абитуриентов, у вас будет только около 100’000 записей в год. Представим что все 17 внешних ключей это 4-х байтовые целые числа (хорошие суррогатные ключи), и 21 количество и задержки являются маленькими 2-х байтовыми целыми числами. Записи нашей таблицы фактов тогда будут шириной 17 x 4 + 21 x 2 = 110 байт. Это даёт около 11 МБ данных в год в этой таблице фактов. Проверьте мои расчёты. На самом деле, это – типичный результат для таблиц фактов накопительного снимка. Они однозначно наименьшие, из трёх типов.

• Предыдущий совет
• Следующий совет
• Другие советы

По этой теме можно также почитать:

• Совет №42. Комбинирование периодических снимков и накопительных снимков

Для удобства отслеживания новых публикаций на сайте рекомендую подписаться на рассылку или подписаться на канал RSS.

Если вы нашли в сети интересные ссылки на ресурсы по технологиям хранилищ данных, OLAP, CRM или data mining, и хотите поделиться ими с другими, присылайте их. Я с удовольствием размещу их на этом сайте.

[AD]