Автор: Mosha Pasumansky
Перевод: Андрей Бабуров
Дата публикации оригинала: 2007-06-01
Источник: Блог Mosha Pasumansky

Измерение типа «Время» находятся на особом счету в OLAP-технологиях и хранилищах данных (DWH). Классические задачи, которые приходится решать - это выборка из предыдущего периода, параллельного периода, расчет скользящего среднего (simple moving average) и нарастающего итога (running total).

В этой статье мы рассмотрим менее распространенную, но не менее интересную задачу - анализ событий, которые описываются более чем одной характеристикой времени. Например, у маркетинговой компании есть дата начала и дата окончания; полеты характеризуются временем вылета и прибытия; в примере Adventure works по каждому заказу отслеживается дата поступления, дата перевозки, дата доставки. Мы можем добавить ролевые измерения (role playing dimensions) для времени и включить их в куб (cube). Это позволит делать срезы данных по любому из таких измерений (dimension) и, например, анализировать, сколько заказов было сделано в определенную дату или период времени, сколько заказов было перевезено в определенную дату или период времени и т.д. Мы также можем загружать в куб интересующие нас метрики, такие как длительность перевозки заказа и т.д.

Решим следующую задачу: Сколько заказов находится “в процессе доставки”, т.е. уже перевезены, но еще не доставлены. Для примера возьмем куб Adventure Works. Начнем с отчета, в котором мы хотим увидеть - сколько заказов было “в процессе доставки” с 5 мая 2003 года по 9 мая 2003 года. Чтобы найти такие заказы, нужно задать следующие условия: дата перевозки (ship date) меньше 9 мая, а дата доставки (Delivery date) позже 5 мая. Оба этих условия можно написать с использованием оператора Range.

Дата перевозки ранее 9 мая - NULL : [Ship Date].[Calendar].[Date].[May 9, 2003]
Дата доставки позже 5 мая - [Delivery Date].[Calendar].[Date].[May 5, 2003] : NULL

Теперь нам нужно применить эти два условия - найти пересечение двух множеств (эквивалентно логическому оператору И). Поместим эти два условия в MDX запрос и получим требуемые данные:

SELECT Measures.[Internet Transaction Count] ON 0
FROM [Adventure Works]
WHERE (NULL : [Ship Date].[Calendar].[Date].[May 9, 2003], [Delivery Date].[Calendar].[Date].[May 5, 2003] : NULL)

Посмотрим на производительность этого запроса. При выполнении этого запроса обрабатывается большой объем данных, поскольку условия “Дата перевозки ранее 9 мая” и “Дата доставки позже 5 мая” покрывают большой набор дат. Однако, мы применяем оба условия вместе и Analysis Services использует bitmap индексы для вычисления пересечения диапазонов. В этом случае объем сканируемых данных напрямую зависит от того, насколько большой диапазон дат указан в запросе. Лучший вариант - анализ одного дня, худший - анализ нескольких лет, поскольку в этом случае может понадобиться сканирование всей таблицы фактов.

Приведенный выше запрос работает хорошо, но в нем диапазон дат жестко “зашит”. Что если мы захотим создать вычисляемую меру (calculated measure), которая будет динамически считать количество заказов “в процессе доставки”. Для этого нам нужно использовать другое измерение времени, относительно которого мы будем считать эту меру. В нашем примере возьмем измерение “дата поступления заказа”. Если пользователь выберет целый год - мы определим диапазон дат от начала до окончания года, если квартал - от начала квартала до окончания и т.п. Для навигации от начала до окончания требуемого периода используем функции MDX OpeningPeriod и ClosingPeriod. Т.е. для первого дня периода используем OpeningPeriod([Date].[Calendar].[Date]), для последнего дня периода ClosingPeriod([Date].[Calendar].[Date]). Теперь мы знаем дни интервала, но нам нужно перевести их с измерения [Date] в соответствующие дни измерений [Ship Date] и [Delivery Date]. Лучший способ сделать это в правильно настроенном измерении времени, когда ключи уникальны в пределах одного уровня (т.е. иерархия натуральная) - это использовать функцию LinkMember. LinkMember соотносит элементы измерения с использованием их ключей. Если ключи неуникальны на одном уровне (т.е. иерархия ненатуральна), результат будет непредсказуемый. К счастью, наша иерархия Calendar - в порядке и мы можем использовать функцию LinkMember(ClosingPeriod([Date].[Calendar].[Date]), [Ship Date].[Calendar]) чтобы получить последний день выбранного периода времени в измерении [Ship Date] и LinkMember(OpeningPeriod([Date].[Calendar].[Date]), [Delivery Date].[Calendar]) чтобы получить первый день выбранного периода времени в измерении [Delivery Date].

И, наконец, используем Aggregate. Наш запрос будет выглядеть следующим образом

Aggregate(
CrossJoin(
NULL:LinkMember(ClosingPeriod([Date].[Calendar].[Date]),[Ship Date].[Calendar]),
LinkMember(OpeningPeriod([Date].[Calendar].[Date]),[Delivery Date].[Calendar]):NULL
), Measures.[Internet Transaction Count]
)

Однако есть важный нюанс. Несмотря на то, что запрос выглядит, как будто мы перенесли координату в измерении [Date], на самом деле мы сделали это внутри измерений [Ship Date] и [Delivery Date], и координата внутри измерения [Date] осталась той же. Как только перевозка заказа задержится после поступления заказа, мы получим искаженные результаты. Если хотя бы один день пройдет перед началом перевозки заказа, то приведенный выше запрос на уровне дней выдаст результат NULL. Поэтому, чтобы данная формула работала корректно, нам нужно перенести измерение [Date] в нейтральное состояние - в его Root.

Aggregate(
CrossJoin(
NULL:LinkMember(ClosingPeriod([Date].[Calendar].[Date]),[Ship Date].[Calendar]),
LinkMember(OpeningPeriod([Date].[Calendar].[Date]),[Delivery Date].[Calendar]):NULL,
Root([Date])
), Measures.[Internet Transaction Count]
)

Можно протестировать это выражение с помощью следующего запроса

WITH
MEMBER Measures.ActiveOrders AS
Aggregate(
(
NULL:LinkMember(ClosingPeriod([Date].[Calendar].[Date]),[Ship Date].[Calendar]),
LinkMember(OpeningPeriod([Date].[Calendar].[Date]),[Delivery Date].[Calendar]):NULL,
Root([Date])
), Measures.[Internet Transaction Count]
)
SELECT {Measures.[Internet Transaction Count],Measures.ActiveOrders} ON 0
, Descendants([Date].[Calendar], [Date].[Calendar].[Date], BEFORE) ON 1
FROM [Adventure Works]


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

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