Совет №14
Отчётность по балансам на произвольные даты по транзакционной таблице фактов

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

Совет №13 показал как присоединить таблицу медленно изменяющегося измерения (таблица банковских счетов) к быстро изменяющейся транзакционной таблице (транзакции по счетам). Мы увидели, что медленно изменяющееся измерение само по себе очень похоже на таблицу фактов, поскольку описывает транзакции, изменяющие профиль счетов.

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

Date Key (FK)
Account Key (FK)
Transaction Type Key (FK)
Transaction Sequence Number
Final Flag
Amount
Balance

Вот, что содержат поля:

Как и в случае с любой транзакционной таблицей фактов, эта таблица содержит запись только в случае, если была выполнена транзакция. Если счёт был нетронутым в течение двух недель, скажем с 1 по 14 октября, для этого счёта в этот промежуток времени будет НОЛЬ записей в таблице фактов. Но предположим, мы хотим знать все балансы счетов на 5 октября.

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

SELECT a.acctnum,f.balance
FROM fact f,account a
WHERE f.account_key =a.account_key
AND f..nal_.ag ='True '
AND f.date_key =
(SELECT MAX(g.date_key)
FROM fact g
WHERE g.account_key =f.account_key
AND g.date_key IN
(SELECT t.date_key
FROM time t
WHERE t.fulldate <='October 5,2000 '))

После изучения этого оператора SQL у вас, вероятно, возникнет несколько вопросов!

Вопрос 1. РАЛЬФ, как Вы можете использовать суррогатный ключ измерения время в качестве основы для ограничения??? Вся идея суррогатных ключей заключается в отсутствии в них семантики.

Ответ 1. Да, за исключением суррогатных ключей измерения "время", представляющих набор целых чисел от 1 до N. По совершенно другой причине мы уже ввели предсказуемый порядок в суррогатные ключи времени. Нам нужна упорядоченность суррогатных ключей времени для того, чтобы можно было осуществить физическое секционирование этой большой таблицы фактов на основе этого ключа. Это замечательным образом разбивает таблицу на сегменты, так что у нас появляется возможность выполнения дискретных административных действий над определёнными диапазонами времени, например перемещение на устройства резервного копирования, или удаление и повторное создание индексов. Это измерение "время" является ЕДИНСТВЕННЫМ измерением, в суррогатных ключах которого присутствует логика, и на которые мы осмеливаемся накладывать ограничения. Мы используем этот порядок в приведённом выше фрагменте SQL, когда ищем наиболее близкую к концу дня транзакцию.

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

Ответ 2. Введение временной отметки вместо суррогатного ключа приводит к ряду проблем, которые мы решили с помощью суррогатных ключей. А именно: пустые даты, неприменимые даты, и даты вида "ещё не произошло". Все эти вопросы мы уже обсуждали в других местах. Однако более важно, что обнажённая дата в таблице фактов не позволяет накладывать реалистичные сложные ограничения. Что если вместо 5 октября нам нужно было посчитать балансы на "3 квартал федеральной резервной отчётной даты", чем бы она ни являлась? В этом случае SQL-запрос, приведённый выше, практически не претерпевает изменений. Просто замените последнюю строку соответствующим ограничением на таблицу измерения "время" для получения даты этого календарного события.

Вопрос 3. Не является ли эта конструкция очень чувствительной к вставке транзакций в таблицу фактов "задним числом"?

Ответ 3. Хороший вопрос. Эта таблица фактов должна быть ПОЛНОЙ и ТОЧНОЙ. Каждая транзакция по счёту должна присутствовать в ней, иначе будет невозможно посчитать нарастающий баланс. Естественно, опоздавшие записи о транзакциях потребовали бы очистки с точки вставки для данного счёта и изменения всех балансов и всех номеров транзакций. Заметьте, что в нашей дискуссии мы явно не использовали номер транзакции, хотя что-то необходимо предусмотреть в этом дизайне для того, чтобы надёжно воспроизвести реальную последовательность транзакций и иметь основу для ключа таблицы фактов (account_key + date_key + sequence_number). Мне нравится последовательный номер, а не время суток, поскольку разность между последовательными номерами является корректным показателем активности счёта.

У меня есть ряд поразительных откровений по поводу этого дизайна, но вам придётся идти к Мауи чтобы их услышать. Шучу. Присылайте свои комментарии.

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

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

• Совет №5. Интервалы дат в качестве измерения "Время"
• Совет №35. Моделирование промежутков времени

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

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

[AD]