Материал опубликован с разрешения компании
Автор оригинала: Ральф Кимбалл (все
статьи)
27 марта, 2002 г.
Перевод на русский язык: Константин Лисянский
Оригинальный документ располагается .
В последние два года я вижу повышенный спрос к приложениям, которым
необходимо задавать вопросы о промежутках времени. Кто-то хорошо
сказал «каждая запись в моей таблице фактов является эпизодом постоянной
величины в регионе времени». Промежутки времени могут начинаться
и заканчиваться в произвольные моменты времени. В некоторых случаях
промежутки времени соединяются вместе и формируют непрерывную цепь,
в других случаях промежутки времени изолированы, а в худших случаях
они накладываются друг на друга произвольным образом. Но, каждый
промежуток времени представляется в базе данных одной записью. Чтобы
легче визуализировать эти вариации, давайте представим, что у нас
есть база данных, заполненная атомарными транзакциями, такими как
внесение и снятие денег с банковских счетов. Мы также включим транзакции
по открытию и закрытию счетов. Каждая транзакция явным образом определяет
эпизод постоянной величины в регионе времени. Внесение или снятие
определяют новую величину остатка по счёту, который будет действительным
до момента следующей транзакции. Этот период времени может составлять
от нескольких секунд до нескольких месяцев. Транзакция открытия
счёта определяет статус счёта как постоянно открытого в течение
промежутка времени до транзакции по закрытию счёта.
Перед тем, как мы предложим дизайн базы данных, давайте вспомним
некоторые вопросы о промежутках времени, которые мы хотим задавать.
Начнём с того, что ограничим наши вопросы до уровня индивидуальных
дней, а не до частей дней, таких как минуты и секунды. Мы вернёмся
к минутам и секундам в конце. Лёгкие вопросы, на которые нам всегда
удавалось хорошо отвечать, включают:
Показать все транзакции, которые произошли в течение определённого
промежутка времени.
Определить, была ли произведена выбранная транзакции в течение
определённого промежутка времени.
Определить промежутки времени, используя сложные возможности
навигации по календарю, включая времена года, фискальные периоды,
номера дней, номера недель, дни выплаты зарплаты и праздничные
дни.
Для этих случаев всё, что нам нужно – это одна временная отметка
в записи таблицы фактов транзакций. Первый вопрос выбирает все транзакции,
временная отметка которых попадает в интервал, заданный в запросе
пользователя. Второй вопрос выбирает временную отметку выбранной
транзакции и производит её сравнение с интервалом. Третий набор
вопросов заменяет простую временную отметку измерением календарной
даты, заполненным большим количеством полезных календарных атрибутов.
Это измерение даты соединяется с таблицей фактов стандартным соединением
внешний ключ – первичный ключ. Это всё – ванильный многомерный дизайн,
который требует всего лишь одного ключа времени в записи таблицы
фактов для представления требующейся временной отметки. Пока неплохо.
Кстати, при использовании сложной навигации по календарю, запросы
становятся гораздо легче, если многословное календарное измерение
включает маркеры первого и последнего дня для каждого определённого
промежутка времени, например «последний день квартала». Это поле
будет иметь значение «N» для всех дней, кроме последнего дня кварталов.
Последний день в этом специальном поле будет иметь значение «Y».
Эти маркеры позволяют легко задавать в запросах сложные промежутки
времени бизнеса. Заметьте, что использование многословного календарного
измерения означает, что приложению не нужно обращаться к временной
отметке в таблице фактов.
Следующая умеренно сложная категория запросов к интервалам времени
включает следующие:
Показать каждого, кто был клиентом в какой-то определённый промежуток
времени.
Показать последнюю транзакцию заданного клиента в каком-то промежутке
времени.
Показать остаток на счёте в произвольно выбранный момент времени.
Мы продолжим основываться на упрощающем предположении о том, что
все промежутки времени основаны на календарных днях, а не на минутах
и секундах. На все эти вопросы можно ответить с помощью дизайна
с одной временной отметкой, который был описан выше, но этот подход
требует сложных и неэффективных запросов. Например, для того, чтобы
ответить на последний вопрос, нам понадобилось бы выполнить поиск
по множеству транзакций по счёту и найти последнюю транзакцию на
заданный момент времени или до него. В SQL это был бы коррелированный
подзапросом, встроенный в окружающий его запрос. Он не только, вероятно,
будет медленно выполняться, но такой SQL не всегда можно сгенерировать
с помощью инструментов конечного пользователя.
Для ответов на все эти вопросы о промежутках времени средней сложности
мы коренным образом упрощаем приложения путём размещения в каждой
записи таблицы фактов двух временных отметок, обозначающих начало
и конец промежутка времени, определяемого транзакцией.
С помощью этого дизайна с двумя временными отметками мы легко побеждаем
три вопроса, приведённых в примере выше:
1. Найти все транзакции открытия счёта, дата начала которых совпадает
или предшествует времени окончания заданного промежутка времени.
2. Найти транзакцию, дата начала которой совпадает или предшествует
дате окончания заданного периода времени, и дата окончания которой
совпадает или выходит за рамки окончания заданного временного периода.
3. Найти транзакцию, дата начала которой совпадает или предшествует
произвольному моменту времени, и дата окончания которой совпадает
или находится в будущем относительно произвольного момента времени.
Во всех этих случаях SQL использует простую конструкцию BETWEEN.
Синтаксическая конструкция «значение между значением двух полей»
является допустимой в SQL. Я узнал об этом недавно.
Когда мы используем подход с двумя временными отметками, мы должны
честно признать один недостаток. Практически во всех ситуациях мы
должны навестить каждую запись таблицы фактов дважды. Один раз,
когда мы вставляем её (с открытой временной отметкой времени окончания),
и ещё раз, когда происходит следующая транзакция, которая определяет
реальную временную отметку окончания. Где-то в будущем временная
отметка с открытым временем завершения, возможно, будет полезна
– приложениям не придётся путешествовать по значениям null при попытке
выполнения операции BETWEEN.
Мы оставили самые сложные вопросы до конца: промежутки времени
с точностью до секунды. В этом случае мы задаём те же основные вопросы,
что и в двух первых разделах, но мы увеличиваем точность границ
промежутков времени до ближайшей секунды. В этом случае мы поместим
те же самые две временные отметки в запись таблицы фактов, но мы
должны отказаться от нашей связи с надёжным измерением Время. Обе
наши временные отметки должны быть стандартными временными отметками
РСУБД. Мы должны делать это, так как обычно мы не можем создать
единого измерения Время, включающего все минуты и секунды за значительный
промежуток времени. Разделение временной отметки на компоненту дня
и секунды внутри дня сделало бы ужасной логику операции BETWEEN.
Так что, для этих сверхточных периодов времени мы принимаем ограничения
семантики даты/времени в SQL и отказываемся от возможности указывать
времена года или фискальные периоды с точностью до секунды.
Если вы действительно крепкий орешек, вы могли бы рассмотреть помещение
четырёх временных отметок в каждой записи таблицы фактов, если ваши
периоды времени определяются с точностью до секунды. Первые две
были бы временными отметками РСУБД, как это описано в предыдущем
абзаце. Но третья и четвёртая были бы внешними ключами со значением
(только) календарных дней, ссылающимися на многословное календарное
измерение, как это описано в первых двух разделах этой статьи. Таким
образом, вы можете получить свой пирог, а также съесть его. Вы можете
производить поиск сверхточных промежутков времени, но вы также можете
задавать вопросы вида «Покажите мне все случаи перебоев с электричеством,
которые произошли в праздничные дни».
Даже при использовании этих мощных способов, имеются, и я в этом
уверен, некоторые непростые вопросы о периодах времени, которые
я не учёл здесь. Я собираю эти головоломки. Присылайте мне новые
на адрес spm111@yandex.ru.
Если вы нашли в сети интересные ссылки на ресурсы по технологиям
хранилищ данных, OLAP, CRM или data mining, и хотите поделиться
ими с другими, присылайте их.
Я с удовольствием размещу их на этом сайте.
RSS