Размерность времени

Йорам Левин, SQL Server Magazine #6/2001

Аналитик с 10-летним стажем рассказывает о парадоксах этой размерности и полезных функциях, облегчающих построение кубов OLAP

С понятием размерности времени Time связано немало различного рода недоразумений. Одним из самых распространенных заблуждений является утверждение, что любой куб OLAP обязательно должен иметь размерность времени. На самом же деле в некоторых приложениях OLAP, связанных, к примеру, с ценообразованием на основе активности, размерность времени отсутствует вообще. Для ценообразования на базе активности размерность времени не требуется, поскольку цена определяется исходя из активности, а не из времени. Второе серьезное заблуждение заключается в том, что в кубе OLAP, как многие полагают, может существовать только одна размерность времени. В приложениях, предназначенных для страховых компаний, используется несколько временных размерностей, базирующихся как на дате подписания страхового договора Underwriting Date, так и на дате наступления страхового случая Accident Date. В кассовых приложениях также обычно используется несколько временных размерностей.

Базовым правилом, принятым для моделей OLAP, является равенство всех размерностей как по структуре, так и по функциональным возможностям. Однако большинство инструментальных средств OLAP, включая и SQL Server 2000 Analysis Services, и SQL Server 7.0 OLAP Services, допускают одно исключение. Оно состоит в том, что можно определить более одной временной размерности, причем любая временная размерность Time обладает только ей присущими атрибутами. Как только какая-либо размерность будет названа временной размерностью Time, Analysis Services и OLAP Services могут автоматически создать в ней такие уровни как год Year, квартал Quarter, месяц Month, а также сгенерировать названия членов этой размерности. Помимо этого существует множество функций MDX, специально предназначенных для обработки интервалов времени, такие как функция Ytd(). Давайте посмотрим, как можно создавать временные размерности Time в Analysis Services и OLAP Services и как с ними работать.

Создание временной размерности

Некоторые аналитики создают на основе фактографической таблицы представление наподобие следующего:

SELECT [Fact_Table].[Date]
FROM [Fact_Table]
GROUP BY [Fact_Table].[Date]

а затем используют его в качестве источника данных для размерности времени Time. Такой метод имеет несколько существенных недостатков. Во-первых, он неэффективен. Обычно фактографическая таблица во много раз больше таблиц размерностей. Строить представление исходя из фактографической таблицы - это все равно что обращаться к лежащей в его основе базовой таблице. Другой недостаток использования фактографической таблицы в качестве источника для размерности времени заключается в том, что в полученной размерности будут отсутствовать даты, когда не было зарегистрировано никакой активности (для этих дней отсутствуют записи в фактографической таблице). Поэтому такой метод приводит к появлению пробелов во временной последовательности (выходные и праздничные дни и т.п.) Если же наличие таких пробелов необходимо, не забудьте исключить соответствующие даты из своей таблицы размерности Time.

Для создания размерности времени лучше всего сформировать специальную таблицу размерности Time в хранилище данных, которая будет содержать все необходимые даты. Для этого достаточно просто создать таблицу в Microsoft Excel и с помощью Data Transformation Services (DTS) импортировать ее в хранилище данных. Такой подход к созданию размерности Time существенно улучшает обработку как этой размерности, так и всего куба. Это обусловлено тем, что отпадает необходимость обращаться к фактографической таблице для получения членов размерности времени. Если для поля даты такой таблицы выбран один из типов данных времени (например, smalldatetime), то входящий в состав Analysis Services и OLAP Services мастер построения размерностей Dimension Wizard, которым обычно пользуются в таких случаях, обнаружит, что размерность может относиться к временным размерностям. Как показано на рисунке 1, мастер выведет на экран соответствующую подсказку и попросит подтвердить свое предположение.

Получив подтверждение, что создаваемая размерность действительно является размерностью времени, мастер Dimension Wizard поможет построить уровни этой размерности (к примеру, год Year, квартал Quarter, месяц Month, и день Day), как показано на рисунке 2.

При этом можно назначить первый день и месяц года (по умолчанию это 1 января, но во многих компаниях финансовый год начинается с другого месяца, например, с мая).

Мастер создания размерностей Dimension Wizard самостоятельно присвоит имена членам размерности. Хотя эти автоматически генерируемые названия вполне точны, они не всегда отражают особые периоды времени членов размерности. К примеру, если на уровне месяцев Month выбран двухгодичный период, то будет создано два члена с именем Январь, два члена с именем Февраль и так далее. При этом невозможно будет различить, к какому году из двухгодичного цикла относится тот или иной месяц. В таких случаях необходимо самим провести настройку названий членов размерности времени. Помимо прочего необходимо удостовериться в том, что на всех уровнях свойству ORDER BY присвоено значение Key. Если этого не сделать, то Analysis Services и OLAP Services будут проводить сортировку членов размерности по имени, а не по дате. Для того чтобы убедиться, что свойство ORDER BY имеет значение Key, откройте редактор размерностей Dimension Editor. Для этого следует щелкнуть правой кнопкой мыши на размерности Time в той секции окна редактора, которая содержит дерево размерностей, а затем выбрать пункт редактирования Edit. Теперь для каждого уровня выберите закладку Advanced в разделе свойств Properties и посмотрите значение свойства ORDER BY.

Даже если вы случайно перескочили через все перечисленные выше шаги работы с мастером создания размерностей (например, потому что пометили свою размерность как стандартную Standard, а не как размерность времени Time, или потому что тип данных поля не относится к датам), тем не менее, всегда можно преобразовать стандартную размерность Standard в размерность времени Time. В окне редактора размерностей Dimension Editor выберите корень дерева размерностей и в секции свойств Properties выберите закладку Advanced. Теперь замените тип размерности Standard на Time. После этого для каждого уровня иерархии размерности необходимо заменить стандартный тип уровня Standard на соответствующий временной тип (к примеру, на Month).

Чтобы упростить процесс составления отчетов, можно добавить некоторые свойства членам размерности Time уровня листьев, такие как месяц Month и день недели Day In Week. К примеру, если на уровне листьев располагается Day, то нетрудно добавить свойство Day In Week, что позволит проводить анализ данных с учетом дней недели. На рисунке 3 показано, как определять такие свойства как месяц Month и день недели Day In Week.

В редакторе размерностей Dimension Editor последовательно выберите пункты Insert, Member Property, а затем поле дат. Введите название свойства в поле Name; при необходимости подкорректируйте столбец-источник и тип данных. В таблице 1 приведены сведения о столбцах-источниках и типах данных для свойств Month и Day In Week.

Свойство (Property)	Столбец-источник (Source Column)	Тип данных (Data Type)
Месяц (Month)	DatePart(month,"dbo"."Dates"."Date")	Integer
День недели (Day In Week)	DateName(DW,"dbo"."Dates"."Date")	varchar

При использовании свойства Month можно построить виртуальную размерность, называемую Month, на базе этого свойства. Виртуальная размерность представляет собой логическую размерность, базирующуюся на свойствах членов размерностей, на столбцах таблицы физической размерности или же на столбцах самой физической размерности. Добавление в куб виртуальной размерности не приводит к увеличению размера куба, поскольку в отличие от физической размерности виртуальная размерность не содержит агрегированных данных. Виртуальные размерности также не снижают скорость обработки куба, потому что Analysis Services и OLAP Services рассчитывают виртуальные размерности в оперативной памяти по мере необходимости. Однако запросы к виртуальным размерностям могут обрабатываться медленнее, чем запросы, использующие физические размерности. Когда виртуальная размерность Month создана, можно строить диаграммы, отражающие динамику развития по месяцам для разных лет, как показано на рисунке 4.

Для построения этой диаграммы я воспользовался клиентским инструментальным пакетом Knosys ProClarity, но могло бы сгодиться практически любое клиентское инструментальное средство. На этой диаграмме по горизонтальной оси показана виртуальная размерность Month, а по вертикальной оси для каждого месяца отложены значения показателя, относящиеся к разным годам Year размерности Time. Поэтому, хотя размерность Time и является единственной, на диаграмме можно отразить сразу два уровня этой размерности (в сравнении одного с другим), используя виртуальную размерность Time.

Функции MDX, "понимающие" время

Некоторые функции MDX, такие как уже упоминавшаяся функция Ytd(), правильно реализуют логику обработки интервалов времени на разных уровнях размерности Time, то есть как бы "понимают" время. Функция Ytd() возвращает набор периодов времени (членов размерности Time), находящихся на указанном уровне иерархии этой размерности, у которых имеется один и тот же предок на уровне года Year, начиная с первого периода и заканчивая указанным членом. Если указан член размерности 5/1999, то его предком на уровне года является член размерности 1999. В этом случае функция Ytd() возвратит все месяцы (именно этот уровень иерархии указан), начиная с первого периода (1/1999) и заканчивая указанным членом (5/1999):

{1/1999,2/1999,3/1999,4/1999,5/1999}

У функции Ytd() имеется единственный необязательный параметр - член Member - который по умолчанию равен текущему члену размерности времени Time, с которым в данный момент времени работает пользователь. Если в кубе существует только одна размерность времени Time, то параметр Member можно опустить. Однако в тех случаях, когда в кубе определено несколько временных размерностей Time, необходимо определить этот параметр, чтобы сообщить Analysis Services или OLAP Services, в какой размерности содержится интересующий набор членов.

Функция Ytd() является сокращенным вариантом другой функции, PeriodsToDate(), которая возвращает набор периодов времени (членов) заданного уровня иерархии, начиная с первого периода и заканчивая указанным членом. Другими сокращенными вариантами функции PeriodsToDate() являются функции Qtd(), Mtd(), и Wtd(), которые в качестве периодов времени возвращают соответственно кварталы, месяцы и недели. Все эти функции относятся к так называемым множественным функциям, поскольку все они возвращают множества, состоящие из нескольких временных периодов. Еще одной множественной функцией является LastPeriods(), которая возвращает указанное множество периодов, предшествующих заданному члену, а также сам этот член. Для работы с функцией LastPeriods() используется следующий синтаксис:

LastPeriods(Index [, Member])

Как и для функции Ytd(), отсутствие значения необязательного параметра Member при задании функции LastPeriods() приводит к тому, что по умолчанию используется текущий член Time.CurrentMember. Если значение параметра Index положительное, то функция LastPeriods() возвращает множество членов индекса. Это множество заканчивается тем членом, который был введен в качестве значения параметра Member, а начинается с того члена, у которого значение индекса на 1 меньше заданного в параметре Index. Поясним на примере. Если текущий член размерности времени Time.CurrentMember равен маю 1999 года, то есть 5/1999, а значение индекса Index равно 3, то в результате действия этой функции будет получено множество, состоящее из трех периодов. Это множество начинается со второго (3 минус 1) периода, предшествующего текущему, и заканчивается текущим периодом:

{3/1999,4/1999,5/1999}

Если же текущим членом является второй квартал 1999 года, Q2/1999, то функция LastPeriods(3) возвратит следующее множество периодов:

{Q4/1998,Q1/1999,Q2/1999}

Обратите внимание на то, что параметр Index может принимать и отрицательные значения. В этом случае возвращаемое множество начинается с члена Member, а размер множества (количество членов в нем) равен абсолютной величине значения параметра Index. Если Index равен 0, функция LastPeriods() возвращает пустое множество.

Обычно множественные функции применяются совместно с другими функциями MDX, такими как среднее значение Avg() и сумма Sum(). К примеру, в формулу для расчета общего объема продаж за истекший период текущего года войдут функции Ytd() и Sum():

Sum({Ytd()} , ([Measures].[Sales]))

Чтобы рассчитать средний объем запасов за истекший период текущего года, используются функции Ytd() и Avg():

Avg({Ytd()} , ([Measures].[Stock]))

Помимо множественных функций, которые в качестве результата возвращают множества членов размерности, существуют функции MDX, принадлежащие другой разновидности работающих со временем функций. В отличие от множественных функций они возвращают ровно один член. Одной из таких функций MDX является ParallelPeriod(), которая возвращает член, принадлежащий предшествующему периоду, но занимающий в нем такое же относительное положение, какое заданный член занимает в своем периоде. Функция ParallelPeriod имеет следующий синтаксис:

ParallelPeriod([Level[, Numeric_Expression[, Member]]])

Далее будет использоваться специфическая терминология Microsoft, применяемая для описания иерархических отношений. В соответствии с ней параметр Level указывает уровень предка (ancestor) заданного члена Member, при этом параметр Numeric_Expression задает сдвинутую на минус единицу позицию <брата> (sibling) этого предка, то есть позицию его "старшего брата". В результате функция возвращает параллельный период для члена Member среди потомков указанного <брата>. К примеру, функция ParallelPeriod(Year,1,[05/2000]) возвратит член [05/1999]. Действительно, предком указанного члена [05/2000] является год [2000]. Сдвиг на единицу влево назад даст год [1999], а поскольку член [5/2000] является пятым <ребенком> года [2000], то функция ParallelPeriod(Year,1,[05/2000]) возвращает пятого <ребенка> предшествующего года, то есть, члена [1999]. В результате всех этих действий и получится член [5/1999].

Как и в других рассматриваемых функциях, принятое по умолчанию значение параметра Member соответствует текущему члену размерности Time. Однако если задать параметр Level, то используемый в качестве значения по умолчанию текущий член берется из той размерности времени, которой принадлежит введенное значение параметра Level. По умолчанию для параметра Numeric_Expression используется значение 1, а значение параметра Level соответствует уровню родителя заданного члена Member. Если уровнем текущего члена размерности времени является месяц Month, то для получения параллельного месяца предыдущего года следует написать:

ParallelPeriod([Time].[Year])

Если необходимо вычислить прирост объема продаж текущего года по сравнению с прошлым годом, можно воспользоваться таким расчетом:

([Measures].[Sales],[Time].CurrentMember) -
   ([Measures].[Sales], ParallelPeriod ([Time].[Year])

Еще две функции возвращают по одному члену размерности Time. Функция завершающего периода ClosingPeriod() возвращает последнего <брата> из числа потомков члена, расположенного на заданном уровне. А функция открывающего периода OpeningPeriod(), возвращает первого <брата> из числа потомков члена, находящегося на указанном уровне (необязательно для указанного члена).

Можно также создать множество, состоящее из нескольких периодов, образующих временной интервал. Для этого достаточно задать два периода, которые являются конечными точками этого интервала. Между этими периодами необходимо поставить двоеточие, как это делается в Excel при указании диапазона. К примеру, интервал {[2/1999]:[5/1999]} включает члены 2/1999, 3/1999, 4/1999 и 5/1999. Компаниям часто требуется вычислять показатели для скользящего года, то есть за последние 12 месяцев или 4 квартала к текущему моменту. Для расчетов показателей скользящего года часто пользуются формулой:

Sum(LastPeriods(12), [Measures].[Sales])

Хотя эта формула прекрасно работает на уровне Month, ни на каком другом уровне выражение LastPeriods(12) не даст нужного результата. Приходится создавать отдельные формулы для расчетов скользящего года исходя из квартала Quarter, дня Day, или даже года Year. Но теперь, когда мы познакомились с функциями MDX, возвращающими члены размерности времени, можно написать универсальное выражение для расчетов:

Sum({ParallelPeriod([Time].[Year],1).NextMember :
[Time].CurrentMember},[Measures].[Sale])

Эта формула не зависит от количества членов заданного уровня, которые составляют один год. Она дает корректный результат для любого уровня иерархии размерности времени.

Несколько других функций MDX не связаны непосредственно с вычислением членов размерности времени, однако они часто используются вместе с размерностью Time. К числу таких функций относятся: функция Head(), которая возвращает заданное количество первых элементов множества; функция Tail(), которая возвращает указанное количество последних элементов множества; функция Lag(), которая возвращает требуемое количество элементов множества, предшествующих указанному члену в его размерности; а также функция Lead(), возвращающая тот элемент множества, который располагается на заданное число позиций впереди указанного члена в его размерности.

Если в размерности Time определены только такие периоды, для которых имеются данные, а последний период размерности соответствует последнему периоду, для которого введены данные, то следующий запрос MDX возвратит сведения за последние 3 месяца по столбцам columns (их следует указать):

SELECT {Tail([Time].[Month].Members,3)} ON Columns ... 

А приведенный ниже запрос формирует срез данных за последний квартал:

WHERE (Tail([Time].[Quarter].Members,1).Item(0))

Богатство функций

Как Analysis Services, так и OLAP Services позволяют объявить любое число размерностей временными размерностями Time. Помимо этого они предоставляют разнообразные функции для работы с членами этих размерностей. Описанные возможности облегчают формирование отчетов и анализ данных.

Об авторе

Йорам Левин руководит израильской компанией Decision Systems, специализирующейся на консультациях по OLAP. Он также возглавляет подразделение OLAP в израильской группе пользователей SQL Server. Йорам более 20 лет занимается системами поддержки принятия решений, искусственным интеллектом, хранилищами данных и OLAP. С ним можно связаться по адресу: decsys@inter.net.il