Совет №5
Суррогатные ключи для измерения "Время"

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

Ежедневно я получаю несколько вопросов, касающихся проектирования хранилищ данных. Поскольку многие из них являются серьёзными и интересными, я пытаюсь на них ответить. Но если получается так, что они являются домашними заданиями преподавателей колледжа, я вежливо отказываю!

А вот и вопрос:
Консультант, работавший недавно у нас, предложил измерение "Время", которое отличается от тех, которые разрабатываете Вы.

Структура его измерения "Время" была следующей:

Прмерные данные выглядели следующим образом:

Как Вы смотрите на подобную структуру измерения "Время"? Для какого типа сценария/бизнеса Вы сочли бы это хорошей, имеющей право на существование альтернативой?

Вот как я ответил:

Я не думаю, что мне нравится такое измерение "Время", если вообще это можно назвать измерением.
Я ожидаю, что измерение "Время" будет описывать временной контекст величины, выраженной в виде значения в таблице фактов. В терминах базы данных это означает, что в каждой записи таблицы фактов должен быть внешний ключ со значением времени, который указывает на определённую запись в измерении "Время".

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

В предложенном Вами измерении "Время" есть записи с различным уровнем детализации, которые отражают перекрывающиеся интервалы времени. Если у вас есть запись с величиной, измеренной в определённую дату, а записи этого "измерения Время" перекрываются, то какую из записей вы выберите в качестве ссылки для конкретной записи в таблице фактов?

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

Типичная таблица измерения "Время" со степенью детализации на уровне дня и с перспективой использования в США (а с некоторыми модификациями и в России - прим. перев.) могла бы иметь следующую структуру:

• Ключ_времени (суррогатный ключ; простые целые числа от 0 до N)
• Тип_времени (Нормальное; Неприменимо; Ещё_не_произошло; Повреждено)
• Метка_времени_SQL (временнАя отметка длиной 8 байт для Тип=Нормальное иначе Null)
• Номер_дня_в_месяце (1..31)
• Номер_дня_в_году (1..366)
• Номер_дня_в_эпохе (положительное или отрицательное число)
• Номер_недели_в_году (1..53)
• Номер_недели_в_эпохе (положительное или отрицательное число)
• Номер_месяца_в_году (1..12)
• Номер_месяца_в_эпохе (положительное или отрицательное число)
• Название_месяца (может быть получено из поля Метка_времени_SQL)
• Год (может быть получен из поля Метка_времени_SQL)
• Квартал (1 кв. .. 4 кв.)
• Полугодие (1, 2)
• Финансовый_период (названия или числа в зависимости от вашего финансового подразделения)
• Государственный_праздник (Новый год, День независимости, День благодарения, Рождество)
• Рабочий_день (Д, Н)
• Уикенд (Д, Н)
• Сезон_продаж (зимняя распродажа, назад в школу, Рождественский сезон)
• Бедствие (ураган Хьюго, землетрясение)

В этой таблице вы создаёте по одной записи для каждого дня в году и записываете в каждое поле (описанное выше) значения, относящиеся к этому дню. Все специальные поля, предназначенные для навигации, такие как Финансовый_период и Сезон_продаж, дают вам возможность произвольно определять любые промежутки времени. Например, вы можете ввести условие Сезон_продаж="Назад в школу" и автоматически получить все дни с 15 августа по 10 сентября.

В предложенном Вами дизайне Вы показываете ключи таблицы измерения "Время" со значениями, подобными "xmas99" и "1qtr99". Это - интеллектуальные ключи. Интеллектуальные ключи по нескольким причинам представляют опасность в таблице измерения хранилища данных. Процесс генерации таких ключей становится заложником синтаксических правил их формирования. Существует искушение писать приложения и пользовательские интерфейсы, которые сделают эти ключи видимыми для кого-то. Но, если есть значение "1qtr99", гарантируете ли Вы, что имеется также и "2qtr99"? И что вы будете делать, в случае если вам понадобится отразить ситуацию, когда временная отметка должна иметь значение "Неприменимо"?

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

Дополнение к совету №5: суррогатные ключи для измерения "Время"

Мне хотелось бы поделиться с вами некоторыми полезными комментариями, которые я получил по поводу совета №5, в котором я описал предпочтительную структуру измерения "Время" и сказал, что первичным ключом в этом измерении должно быть целое число, а не настоящая временная отметка.

Несколько человек, которые в остальном согласились с этим подходом, сказали, тем не менее, что может оказаться полезным присвоение суррогатным ключам измерения "Время" корректного порядка в соответствии с датами в каждой записи таблицы измерения. Это позволяет провести физическое секционирование (partitioning) любой таблицы фактов на основе значений суррогатного ключа времени. Физическое секционирование большой таблицы фактов по времени является очень естественным подходом в любом случае, поскольку он позволяет элегантно удалить устаревшие записи, а также проиндексировать вновь поступившие записи, не затрагивая оставшейся части таблицы фактов, если вы используете возможности секционирования, предоставляемые вашей СУБД.

Также, поскольку я по случаю как-то упомянул о том, что Microsoft SQL Server является единственной СУБД класса high end, не поддерживающей физическое секционирование таблиц, я был рад узнать, что в SQL Server 2000 секционирование таблиц является штатным средством.

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

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

• Совет №2. Множественные временные отметки
• Совет №35. Моделирование промежутков времени
• Суррогатные ключи. Контролируйте идентификаторы строк формированием суррогатных ключей в хранилищах данных
• Суррогатные ключи. Конвейерная обработка суррогатных ключей
• Статьи из раздела Использование суррогатных ключей

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

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