Какова наилучшая практика для представления временных интервалов в хранилище данных?

Question

В частности, я имею дело с медленно изменяющимся измерением типа 2 и должен представлять временной интервал, для которого была активна конкретная запись, то есть для каждой записи у меня есть StartDate и EndDate. Мой вопрос заключается в том, использовать ли закрытый ([StartDate, EndDate]) или полуоткрытый ([StartDate, EndDate)) интервал для представления этого, т.е. включать ли последнюю дату в интервал или нет. В качестве конкретного примера, скажем, запись 1 была активной с 1 по 5 день, а с 6 дня запись 2 стала активной. Должен ли я сделать EndDate для записи 1 равным 5 или 6?

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

• EndDate-StartDate дает время, когда запись была активной
• Проверка: начальная дата следующей записи будет равна конечной дате предыдущей записи, которую легко проверить.
• Проверка будущего: если позже я решу изменить гранулярность с ежедневной на более короткую, тогда дата переключения будет оставаться точной. Если я использую закрытый интервал и сохраняю конечную дату с отметкой времени в полночь, то мне придется скорректировать эти записи, чтобы учесть это.

Поэтому я предпочел бы использовать методологию полуоткрытого интервала. Однако, если бы существовало какое-то широко распространенное в отрасли соглашение об использовании метода с закрытым интервалом, то я мог бы склониться к этому, особенно если оно основано на практическом опыте реализации таких систем, а не на моем абстрактном теоретизировании.

Заранее спасибо за любые идеи или комментарии.

Answer 1

Я видел как закрытые, так и полуоткрытые версии в использовании. Я предпочитаю полуоткрытый по причинам, которые вы указали.

На мой взгляд, полуоткрытая версия делает понятное предполагаемое поведение и "безопаснее". Предикат ( a <= x

Установите для последней даты окончания по умолчанию наибольшую дату, которую поддерживает ваша СУБД, а не ноль.

Answer 2

Обычно я согласен с ответом Дэвида (проголосовал), поэтому я не буду повторять эту информацию. В дополнение к этому:

Вы действительно имели в виду полуоткрытое ([StartDate,EndDate])

Даже в этом "полуоткрытом" есть две ошибки. Одна из них - это прямая ошибка нормализации, которая, конечно, реализует дубликаты данных, которые вы идентифицируете в обсуждении, которые доступны как производные данные и которые должны быть удалены.

• Для меня Half Open - это (StartDate)
• EndDate выводится из следующей строки.
• это лучшая практика
• это не обычное использование, потому что (а) обычные разработчики не знают в наши дни и (б) они слишком ленивы или не знают, как кодировать необходимый простой подзапрос
• основано на опыте, в крупных банковских базах

Обратитесь к этому для деталей:

Ссылка на недавний очень похожий вопрос и модель данных

Ответы на комментарии

Вы, кажется, явно предпочитаете нормализованные дизайны с естественными, значимыми ключами. Есть ли основания отклоняться от этого в хранилище данных отчетности? Насколько я понимаю, дополнительное пространство, выделенное для суррогатных ключей и дублирующих столбцов (например, EndDate), является компромиссом для повышения производительности запросов. Однако некоторые ваши комментарии по поводу использования кэша и увеличения дискового ввода-вывода заставляют меня усомниться в этом. Мне было бы очень интересно ваше мнение по этому вопросу.

1. Да. Абсолютно. Любой здравомыслящий человек (который не изучает информатику из вики) должен задаться этим вопросом. Это просто противоречит законам физики.

2. Можете ли вы понять, что многие люди, не понимая Нормализацию или базы данных (вам нужен 5NF), создают ненормализованные кучи медленных данных, и их знаменитое оправдание (написанное "гуру") "денормализовано для производительности"? Теперь вы знаете, что это экскременты.

3. Те же люди, не понимая Нормализацию или хранилища данных (вам нужен 6NF), (а) создают копию базы данных и (б) всевозможные странные и замечательные структуры для "улучшения" запросов, включая (в) еще большее дублирование. И угадайте, что их оправдание? "денормализовано для производительности".

   • Это преступно, и "гуру" не лучше, они подтверждают это.

   • Я бы сказал, что эти "гуру" являются только "гуру", потому что они обеспечивают псевдонаучную основу, которая оправдывает ненаучность большинства.

   • ложная информация не становится правдивее, повторяя ее, и Бог знает, что они повторяют ее до бесконечности.

4. Простая истина (не достаточно сложная для людей, которые оправдывают хранилища данных с помощью (1) (2) (3)), состоит в том, что 6NF, правильно выполненное, является хранилищем данных. Я предоставляю базу данных и хранилище данных из одних и тех же данных со скоростью хранилища. Нет второй системы; нет второй платформы; нет копий; нет ETL; не хранить копии синхронизированы; нет пользователей, чтобы перейти к двум источникам. Конечно, для преодоления ограничений SQL требуется умение и понимание производительности, а также немного специального кода (вы не можете указать 6NF в DDL, вам нужно реализовать каталог).

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

5. Даже если вы этого не сделаете, если вы просто сделаете традиционную вещь и ETL поместите эту базу данных в отдельную систему хранилища данных, в ней, если вы устраните дублирование, уменьшите размер строки, уменьшите индексы, конечно, она будет работать быстрее. В противном случае это противоречит законам физики: толстые люди бегут быстрее, чем худые; корова будет бегать быстрее лошади.

   • Если честно, если у вас нет нормализованной структуры, то, пожалуйста, помогите. Таким образом, они придумали StarSchemas, SnowFlakes и всевозможные проекты Dimension-Fact.

И, пожалуйста, поймите, что только эти неквалифицированные, неопытные люди верят во все эти мифы и магию. Образованные опытные люди имеют свои с трудом заработанные истины, они не нанимают колдунов. Эти "гуру" только подтверждают, что толстый человек не выигрывает гонку из-за погоды или звезд; все, кроме вещи, которая решит проблему. Несколько человек получают свои трусики в узел, потому что я прям, я говорю толстяку, чтобы сбросить вес; но настоящая причина, по которой они расстраиваются, состоит в том, что я прокалываю их заветные мифы, которые заставляют их оправдываться, будучи толстыми. Люди не любят меняться.

• Одна вещь. Это когда-либо оправдано отклоняться. Правила не черно-белые; они не единичные правила в изоляции. Мыслящий человек должен рассмотреть их все вместе; расставить приоритеты для контекста. Вы не найдете ни все Id ни ключи, ни ноль Id в моих базах данных, но каждый Id Ключ был тщательно продуман и обоснован.

  • Во что бы то ни стало, используйте самые короткие ключи, но используйте значимые реляционные ключи вместо суррогатов; и использовать суррогаты, когда ключ становится слишком большим для переноски.

  • Но никогда не начинай с суррогатов. Это серьезно затрудняет вашу способность понимать данные; Нормализация; смоделировать данные.

    • Вот один ▶ вопрос / ответ ◀ (из многих!), Где человек застрял в процессе, не в состоянии определить даже основные сущности и отношения, потому что он застрял Id в начале все ключи. Задача решена без обсуждения, в первой итерации.
      ,
• Хорошо, еще одна вещь. Изучите этот предмет, получите опыт и развивайте себя. Но не пытайтесь учить этому или обращать других, даже если зажегся свет, и вы жаждете. Особенно, если вы полны энтузиазма. Зачем? Потому что, когда вы сомневаетесь в совете колдуна, вся деревня будет линчевать вас, потому что вы нападаете на их заветные мифы, их утешение; и вам нужен мой опыт, чтобы поймать колдунов (просто проверьте его в комментариях!). Дайте ему несколько лет, получите свой реальный с трудом завоеванный опыт, а затем примите его.

Если вам интересно, следуйте этому ▶ вопрос / ответ ◀ в течение нескольких дней, это будет отличным примером того, как следовать методологии IDEF1X, как выявлять и использовать эти идентификаторы.

Answer 3

Ну и стандартный sql where my_field between date1 and date2 включительно, поэтому я предпочитаю инклюзивную форму, а не то, что другая неправильна.

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

Они в основном нужны во время загрузки (здесь речь идет о SCD ​​типа 2), чтобы найти наиболее актуальный первичный ключ для соответствующего бизнес-ключа. На данный момент у вас есть что-то вроде:

select ProductKey
from dimProduct
where ProductName = 'unique_name_of_some_product'
  and rowValidTo > current_date ;

Или, если вы предпочитаете создать ключевой конвейер перед загрузкой:

insert into keys_dimProduct (ProductName, ProductKey)  -- here ProductName is PK
select ProductName, ProductKey 
from dimProduct
where rowValidTo > current_date ;

Это помогает при загрузке, поскольку перед загрузкой легко кэшировать таблицу ключей в память. Например, если ProductName является varchar(40) и ProductKey целое число, таблица ключей составляет менее 0,5 ГБ на 10 миллионов строк, легко кешируется для поиска.

Другие часто встречающиеся варианты включают were rowIsCurrent = 'yes' а также where rowValidTo is null,

Обычно используется одно или несколько из следующих полей:

• rowValidFrom
• rowValidTo
• rowIsCurrent
• rowVersion

в зависимости от дизайнера DW и иногда используемого инструмента ETL, потому что большинство инструментов имеют блоки загрузки типа SCD 2.

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

Предположим, я использую все поля row_.

rowValidFrom date       = 3 bytes
rowValidTo   date       = 3 bytes
rowIsCurrent varchar(3) = 5 bytes
rowVersion   integer    = 4 bytes

Это составляет 15 байтов. Можно утверждать, что это 9 или даже 12 байтов слишком много - хорошо.

Для 10 миллионов строк это составляет 150 000 000 байтов ~ 0,14 ГБ

Я посмотрел цены на сайте Dell.

Memory ~ $38/GB
Disk   ~ $80/TB = 0.078 $/GB 

Я буду предполагать рейд 5 здесь (три диска), поэтому цена диска будет 0,078 $/ ГБ * 3 = 0,23 $/ ГБ

Итак, на 10 миллионов строк хранить эти 4 поля на дисковых затратах 0.23 $/GB * 0.14 GB = 0.032 $, Если вся таблица измерений должна быть кэширована в памяти, цена этих полей будет 38 $/GB * 0.14GB = 5.32 $ на 10 миллионов строк Для сравнения, пиво в моем местном пабе стоит ~ 7$.

2010 год, и я ожидаю, что у моего следующего ноутбука будет 16 ГБ памяти. Вещи и (лучшие) практики меняются со временем.

РЕДАКТИРОВАТЬ:

Делали некоторые поиски, за последние 15 лет емкость диска среднего компьютера увеличилась примерно в 1000 раз, памяти примерно в 250 раз.