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

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

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

Key varchar2(8)
StartDate date или date/tme
EndDate date или date/tme

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

Key StartDate EndDate
xmas99 25Nov99 06Jan00
1qrtr99 01Jan99 31Mar00
01Jan00 01Jan00 01Jan00

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

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

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

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

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

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

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

  • Ключ_времени (суррогатный ключ (surrogate key); простые целые числа от 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, в котором я описал предпочтительную структуру измерения “Время” и сказал, что первичным ключом (primary key) в этом измерении должно быть целое число, а не настоящая временная отметка.

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

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

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


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

Читайте также: