Совет №23
Скользящее предсказание будущего, сейчас и в прошлом
Материал опубликован с разрешения компании
Автор оригинала: Ральф Кимбалл (все
статьи)
Перевод на русский язык: Константин Лисянский
Оригинальный документ располагается здесь.
В этом совете я описываю симпатичную и несколько замысловатую загадку,
присланную мне Ричардом Томлинсоном из компании Business Objects.
Его пользователям было необходимо выбрать произвольный день (в пределах
следующих нескольких месяцев) в будущем, а затем, смотря назад от
того дня, подготовить отчёт сегодняшнего дня до этого дня в будущем.
И этого не было достаточно, поскольку пользователям иногда нужно
было переопределить СЕГОДНЯ на произвольный день (в пределах нескольких
прошедших месяцев) в прошлом! Предельно ясно, да? Текст совета будет
представлять нашу слегка отредактированную переписку.
>>>> Исходное сообщение Ральфу от Ричарда с описанием
проблемы <<<<
Привет, Ральф и Ко.
Помогите! Я, в самом деле, застрял, но это интересный вопрос проектирования
для тех, кто чувствует в себе силы:
Как мне предсказать будущее? Мне нужно спроектировать симпатичную
простую (ха-ха) таблицу фактов...
У меня в витрине данных есть таблица измерения СЧЁТ, в которой
имеется поле 'Status', скажем статус "просрочен". Я также
знаю 'дату статуса', скажем 2 мая 2001 года.
Бизнес-пользователь хочет знать следующее: СКОЛЬКО ДНЕЙ СЧЁТ БУДЕТ
НАХОДИТЬСЯ В СОСТОЯНИИ "ПРОСРОЧЕН" В СЛЕДУЮЩЕМ МЕСЯЦЕ?
Если СЕГОДНЯ 5 мая 2001 года, пользователь хочет видеть следующее:
5 мая: 4 дня
6 мая: 5 дней
7 мая: 6 дней
и т.д. до 4 июня 2001 г. - это нарастающий месяц в будущем, начинающийся
СЕГОДНЯ.
Дальше они хотят сосчитать все счета в таблице счетов и сгруппировать
их по интервалам в днях со статусом 'ПРОСРОЧЕНО' чтобы увидеть следующее:
5 мая: 100 счетов - 4-6 дней 'ПРОСРОЧЕНО'
6 мая: 78 счетов - 4-6 дней 'ПРОСРОЧЕНО'
и т.д.
5 мая: 200 счетов - 7-9 дней 'ПРОСРОЧЕНО'
6 мая: 245 счетов - 7-9 дней 'ПРОСРОЧЕНО'
и т.д.
Мне также нужно путешествовать назад во времени и делать то же
самое для любого момента времени + 1 месяц, однако, у многих счетов
будут установлены даты 'Status_Ineffective_Date', и они уже никогда
не будут иметь статус 'ПРОСРОЧЕНО', но для них будут существовать
исторические записи в таблице счетов для того, чтобы показать, когда
они имели этот статус через медленно изменяющееся измерение типа
2.
Если у кого-либо имеется подобный опыт с такой проблемой, или
если кто-то знает, как решить её, пожалуйста, дайте мне знать...
у меня болит голова.
С уважением,
Ричард.
>>>> Ответ Ральфа Ричарду <<<<
Привет, Ричард.
Я взглянул на это. Может быть, следующее сработает.
Предположим, что твоя таблица измерения счетов имеет следующие поля
(как ты описывал):
ACCOUNT_KEY
STATUS
STATUS_EFFECTIVE_DT
Теперь построим ещё одну таблицу со следующими полями:
STATUS_EFFECTIVE_DT
STATUS_REPORTING_DT
DELTA
где DELTA - это количество дней между эффективной датой и датой
отчёта. В этой таблице должны быть данные для любых комбинаций эффективных
дат и дат отчёта, в которых могут быть заинтересованы твои пользователи.
Если эффективные даты уходят в прошлое на 1 год (365 дней), и ты
хочешь выполнять отчёты на 1 год вперёд, то в этой таблице понадобится
365*365 или около 130000 строк. Могут быть и другие предположения.
Достаточно много, но может быть работоспособно, если имеется достаточно
много памяти.
В любом случае, соедини эту вторую таблицу с первой по полю STATUS_EFFECTIVE_DT.
Тогда твой первый запрос можно удовлетворить с помощью следующего:
SELECT STATUS_REPORTING_DT, DELTA
FROM .
WHERE STATUS='OVERDUE'
AND STATUS_REPORTING_DT BETWEEN 'May 5, 2001' and 'June 4, 2001'
ORDER BY STATUS_REPORTING_DT
Для получения отчёта с разбивкой на периоды, возможно, ты построишь
третью таблицу (таблицу периодов) с полями
BAND_NAME
UPPER_DELTA
LOWER_DELTA
Соедини эту таблицу со второй по условию
DELTA<=UPPER_DELTA
DELTA>LOWER_DELTA
У тебя получится SQL наподобие этого:
SELECT BAND_NAME, COUNT(*)
FROM (все три таблицы, соединённые как было описано)
WHERE STATUS_REPORTING_DT BETWEEN 'May 5, 2001' and 'June 4, 2001'
AND DELTA<=UPPER_DELTA
AND DELTA>LOWER_DELTA
ORDER BY UPPER_DELTA
GROUP BY BAND_NAME
Я описываю этот подход к интервалам в книге Lifecycle
Toolkit на страницах 251-252. Попробуй его на небольшой тестовой
базе Access. Расскажи о том, что получится.
Удачи.
Ральф Кимбал
>>>> Последующий ответ Ричарда после того, как он опробовал
решение <<<<
Привет, Ральф.
Мне доставляет удовольствие сообщить, что твоё решение сработало!
Спасибо. Я опробовал его на нескольких тестовых строках в SQL Server
7.0, и результаты выглядели хорошо. Я продвинул его на следующий
уровень, включив Status_Ineffective_Date чтобы иметь возможность
исключить те счета, которые остановили статус 'ПРОСРОЧЕНО' где-то
в отчётном периоде. Может быть, тебе захочется опубликовать это
в одном из писем твоей рассылки?
Для начала, я несколько изменил твой SQL, приведя его к следующему
виду:
SELECT
COUNT(account.Account_Id),
days_overdue.reporting_date
FROM
account,
days_overdue
WHERE
(account.Status_Effective_Date=days_overdue.effective_date)
AND
(days_ovedue.reporting_date BETWEEN '05/05/2001' AND '06/04/2001'
AND days_overdue.delta>= 10 {произвольное число или интервал,
которые пользователь выбирает при запуске отчёта}
)
GROUP BY
days_overdue.reporting_date
Ключевым моментом запроса является ограничение по 'дельте' (которую
мы называем 'days_overdue'), в противном случае запрос вернёт каждый
счёт для каждого дня отчётного периода, а функция COUNT будет возвращать
всё время одно и то же число. Ограничение должно быть по равенству,
неравенству или вхождению в диапазон, но оно должно обязательно
присутствовать.
Во-вторых, для исключения счетов, которые более не являются просроченными
(мы предполагаем, что знаем дата, когда они будут оплачены или были
оплачены!), мы просто включаем в таблицу счетов поле status_ineffective_date,
после чего можем сделать следующее:
SELECT
COUNT(account.Account_Id),
days_overdue.reporting_date
FROM
account,
days_overdue
WHERE
(account.Status_Effective_Date=days_overdue.effective_date)
AND
(days_ovedue.reporting_date BETWEEN '05/05/2001' AND '06/04/2001'
AND days_overdue.delta>= 10
AND account.status_ineffective_date>days_overdue.reporting_date
)
GROUP BY
days_overdue.reporting_date
Естественно, вам придётся установить ineffective_date для всех
просроченных в настоящее время счетов в какую-то величину в будущем,
например 01.01.3000. Это также хорошо вписывается в концепцию SCD
типа 2, когда мы создаём новую строку в таблице измерения счёт каждый
раз при изменении статуса, чтобы предоставить возможность полного
исторического сравнения!
Спасибо большое за помощь. С наилучшими пожеланиями,
Ричард Томлинсон
>>>> Финальный постскриптум <<<<
Это была небольшая весёлая головоломка. Очень здорово получать
такие, но если шлёте мне запрос, пожалуйста, будьте понимающими!
Я, естественно, не могу рассмотреть их все, и в некоторых случаях
результаты не будут такими славными.
Если вы нашли в сети интересные ссылки на ресурсы по технологиям
хранилищ данных, OLAP, CRM или data mining, и хотите поделиться
ими с другими, присылайте их.
Я с удовольствием размещу их на этом сайте.
RSS