Введение в MDX. Часть 2

Полина Трофимова, Алексей Шуленин

Использование внешних библиотек

Преобразование строки к типам MDX

Определяемые пользователем функции

Определение наиболее актуальной даты

Уровень All. Члены по умолчанию

Работа внутри уровня

Перемещение между уровнями

Функция Generate

Упорядочение на основе агрегатов

Вычисление скользящего среднего

Имя объекта, свойства соединения и региональные установки

Свойства объекта

Операции над множествами

Именованные множества

Пользовательские свойства и упорядочивание членов уровня

Во второй части статьи мы продолжим знакомство с возможностями и сценариями применения основных функций MDX на практических примерах. На прилагаемом к журналу компакт-диске вы можете найти резервную копию реляционной БД Flights.bak (1,2 Мбайт) и архив построенной на ее основе многомерной базы Авиаперевозки.cab (21 Кбайт). Эти данные используются в рассмотренных в статье примерах. Заранее оговорим, что, хотя рассматриваемые задачи взяты из реальных проектов, в первую очередь мы ориентировались на освоение читателем материала. В связи с этим постановка рассматриваемых здесь задач и предложенные способы их решения могут отличаться от реализованных в действительности.

Использование внешних библиотек

усть на кубе статистики по авиаперевозкам, который мы рассмотрели в первой части статьи, определены роли «Аналитик» и «Старший аналитик». Первая роль должна иметь доступ только к оперативным данным, скажем, за текущий месяц, а вторая — и к оперативным, и к историческим. Спрашивается, каким образом ограничить действие роли «Аналитик» по измерению «Время», с тем чтобы ее члены могли видеть информацию по рейсам только за текущий месяц?

Очевидно, что для решения этой задачи нам необходимо научиться получать текущую дату. В MDX такой функции нет, как нет и встроенной поддержки многих других элементарных функций, присущих, казалось бы, любому современному языку программирования. Это неудивительно, поскольку в нем без особого труда можно подключить и использовать любую динамическую библиотеку объектов ActiveX. Две такие библиотеки — VBA и Excel — устанавливаются и подключаются вместе с клиентской частью Аnalysis Services (даже если Microsoft Office на компьютере не присутствует). Разработчики MDX решили не изобретать велосипед в виде базового набора общих и статистических функций, а сосредоточиться на более специфичных проблемах аналитики. Следовательно, для определения текущей даты может служить VBA-функция Now(), которую можно использовать абсолютно так же, как и «родные» функции MDX, поскольку регистрация этой библиотеки выполняется автоматически, без применения оператора USE LIBRARY.

Полученную дату требуется превратить в имя члена измерения «Время» в соответствии с определенной на нем иерархией и форматом представления имен (свойство Member Name Column в Dimension Editor — рис. 1) для каждого из уровней.

Например, полное имя члена 11 мая 2001 года равно [Время].[2-й квартал].[Май].[11/05/2001]. Также это представление можно реализовать при помощи функции Format() из библиотеки VBA. В частности, имя, соответствующее текущей дате, будет выглядеть так:

WITH MEMBER [Время].[Сегодня] AS ‘«[« + Format(Now(), 
«yyyy») + «].[«+ Format(Now(),    «q») + «-й квартал].[«+ Format(Now(), «mmmm») + «].[«+ 
Format(Now(), «dd») +    «/» + Format(Now(), «mm») + «/» + Format(Now(), «yyyy») + «]»’ 
SELECT {[Время].[Сегодня]} ON COLUMNS FROM [Учет рейсов]

[Время].[Сегодня] — это вычисляемый член (calculated member), относящийся к измерению «Время», который здесь создается на время действия запроса. Возможно также создание вычисляемых членов на время пользовательской сессии или на время жизни куба.

Вычисляемые члены могут принадлежать любому измерению, в том числе Measures. В отличие от обычных мер их величины не хранятся в кубе, а вычисляются в период выполнения. Следует заметить, что результаты VBA-функций, работающих с датами, зависят от региональных настроек клиентской рабочей станции. Например, название месяца, возвращаемое Format(Now(), «mmmm»), зависит от выбранного языка. Поэтому предварительно необходимо убедиться, что они совпадают с правилами именования членов в измерении «Время» на аналитическом сервере. В нашем случае в приложении Control Panel следует выбрать пиктограмму Regional Options, а в диалоговой панели настройки языка и стандартов — General Ф Your locale = Russian.

Преобразование строки к типам MDX

ы научились формировать дату в виде строки в соответствии с правилами именования членов в нашем измерении «Время». Чтобы получить данные о перевозках в этот день, нужно строковый тип преобразовать к типу «член измерения», что делается MDX-функцией StrToMember(). Эта замечательная функция позволяет динамически формировать имена членов измерения. Она принимает имя члена в виде строки и возвращает результат в виде полноценного члена измерения, который может использоваться везде, где это допускается синтаксисом MDX. Поскольку на момент рассмотрения нашего примера данных в модельном кубе, скорее всего, не содержится, то вместо текущей даты возьмем текущий месяц за 2000 год и получим результаты перевозок по пунктам назначения за этот период:

SELECT{[Пункт назначения].[СНГ].Children} ON ROWS, 
{[Measures].Members} ON COLUMNS FROM [Учет рейсов] 
WHERE StrToMember(«[2000].[«+ Format(Now(), «q») 
+ «-й квартал].[« + Format(Now(),«mmmm») + «]»)

Теперь нам не составит труда аналогично описать динамический диапазон действия роли «Аналитик», ограничив ее по измерению «Время» на текущий месяц (Custom Dimension Security Ф Advanced Ф Allowed Members).

Схожей функциональностью обладает функция StrToSet(), которая, как легко догадаться, конвертирует строку в множество, например:

SELECT StrToSet(«[Пункт назначения].[СНГ].Children») ON ROWS, StrToSet(«Descendants([Время],
    [Месяц])») ON COLUMNS FROM [Учет рейсов]

Обратите внимание на то, что строковое выражение должно описывать именно множество в терминах MDX, то есть

SELECT StrToSet(«{[Пункт назначения].[СНГ].[Казахстан], [Пункт назначения].[СНГ].[Узбекистан]}»)…,

а не

SELECT StrToSet({«[Пункт назначения].[СНГ].[Казахстан], [Пункт назначения].[СНГ].[Узбекистан]»})…

Последний пример можно переписать и следующим образом:

SELECT {StrToMember(«[Пункт назначения].[СНГ].[Казахстан]»), StrToMember(« [Пункт
    назначения].[СНГ].[Узбекистан]»)}…

Функция StrToTuple() аналогично преобразует строку в кортеж:

SELECT {StrToTuple(«([Пункт назначения].[Казахстан], [Авиакомпания].[Комета])»)}
    ON ROWS, StrToSet («Descendants([Время], [Месяц])») ON COLUMNS FROM [Учет рейсов]

Обратные преобразования выполняются соответственно функциями MemberToStr(), SetToStr() и TupleToStr(). На практике они применяются в основном для того, чтобы передавать объекты MDX-функциям внешних библиотек, написанных на Visual Basic, Visual C++ и т.д., которые «не понимают» типов, используемых в MDX.

Определяемые пользователем функции

ля создания внешних функций можно использовать любое средство разработки, способное строить библиотеки СОМ-объектов. Пользовательская функция может принимать любые стандартные строковые или численные типы COM, а также массивы этих типов. Для передачи в функцию множества его необходимо сначала преобразовать в массив при помощи MDX-функции SetToArray(). Опциональные параметры не поддерживаются. Массив не может быть результатом пользовательской функции. Кроме того, функция, очевидно, не может возвращать значения типа «член», «множество», «кортеж», поэтому их следует возвращать в виде строк, а в MDX преобразовывать к нужному типу, используя уже знакомые нам функции StrToMember(), StrToSet(), StrToTuple(). Загрузка библиотеки осуществляется командой USE LIBRARY, выгрузка — DROP LIBRARY.

В разных библиотеках допускается использование функций с одинаковыми именами, при этом приоритет имеет та, что была зарегистрирована раньше. Вообще, во избежание путаницы рекомендуется обращаться к внешним функциям, используя полную нотацию имени в форме <ProgID>!<InterfaceID>! <Имя функции>(<Аргумент1>, <Аргумент2> ...). В остальном синтаксис вызова внешней функции ничем не отличается от встроенной. Если внешняя функция MyUDF возвращает значение void, в MDX она должна вызываться как CALL (MyUDF).

Проиллюстрируем сказанное на примере, создав внешнюю библиотеку в Visual Basic 6. Откроем на клиенте проект типа ActiveX DLL, назовем его ExternalLibForMDX и создадим класс MDXSampleClass с единственным методом:

Public Function CurrentMonthToMember() As String  
CurrentMonthToMember = _  
    "[Время].[2000].[" + Format(Now(), "q") + "-й квартал].[" + Format(Now(), "mmmm") + "]"  
End Function  

Построение библиотеки (File Ф Make ExternalLibForMDX.dll) автоматически ее регистрирует, следовательно, из данного клиента можно вызывать ее функции при соединении с разными серверами Analysis Services 2000. Запустим тестовое клиентское приложение (например, MDX Sample Application), подключим библиотеку:

USE LIBRARY «ExternalLibForMDX.MDXSampleClass»

Протестируем возвращаемое ею значение:

WITH MEMBER [Время].[Текущий месяц] 
AS 'ExternalLibForMDX!_MDXSampleClass!CurrentMonthToMember()'  
            SELECT {[Время].[Текущий месяц]}
 ON COLUMNS FROM [Учет рейсов]  

После этого значение можно использовать, например, в запросах в качестве члена измерения, как уже было показано выше:

SELECT {StrToMember(ExternalLibForMDX!_MDXSampleClass!CurrentMonthToMember())}
    ON COLUMNS FROM [Учет рейсов]

По окончании работы с внешними функциями для экономии памяти библиотеку рекомендуется выгрузить:

DROP LIBRARY «ExternalLibForMDX.MDXSampleClass»

На внешнюю библиотеку можно ссылаться как по ее ProgID, так и по полному имени файла, в котором находится сам исполняемый модуль либо библиотека типов. Если библиотека расположена на другой машине, клиент должен иметь к ней доступ:

USE LIBRARY «\\alexeysh-lpt\d$\TechEd’2001\Advanced MDX\ExternalLibForMDX.dll»

Определение наиболее актуальной даты

еперь немного изменим постановку задачи. Пусть роли «Аналитик» требуется дать права не на текущий, а на последний содержащийся в кубе месяц. Это обусловлено тем, что погружение новых данных из транзакционной системы в хранилище на нашем предприятии может осуществляться согласно некоему расписанию (например, раз в сутки по окончании рабочего дня), и тогда в первый рабочий день месяца в кубе еще не будет информации за этот месяц. Вариант с Now()-1 не проходит, так как начало месяца может попадать на выходные и праздничные дни, когда обработка не производится. Поэтому вместо того, чтобы всякий раз считать, сколько дней отнять от текущей даты, проще научиться находить наиболее «свежую» из реально имеющихся в кубе дат. Это легко сделать с помощью функции ClosingPeriod, которая возвращает наибольшего потомка заданного члена на заданном уровне, например: ClosingPeriod([Месяц], [Время].[2000]) = Декабрь. С ее помощью наше решение будет выглядеть приблизительно так:

SELECT {ClosingPeriod([День], [Время])} ON ROWS,   
            NON EMPTY CrossJoin([Авиакомпания].Children,
 [Тип самолета].Children) ON COLUMNS FROM [Учет рейсов]  

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

Уровень All. Члены по умолчанию

аметьте, что предыдущий пример работает правильно только при наличии у измерения «Время» уровня All. Уровень All — это самый верхний уровень в иерархии измерения, состоящий из одного члена (в нашем случае он называется «Все время» — рис. 1), который характеризует совокупные результаты по всему данному измерению. Analysis Services добавляют этот уровень автоматически, но вы можете убрать его (рис. 2) в редакторе измерений (помня, что изменение структуры иерархии влечет за собой полный процессинг общего измерения и, как следствие, всех кубов, в которых оно участвует) и посмотреть, как изменится результат запроса. В некоторых ситуациях это оправданно. Например, согласно логике задачи нет смысла суммировать продажи за разные годы (инфляция и т.д.). Тогда ClosingPeriod([День], [Время]) вернет 01/12/2000, то есть последний фактический день 2000-го, а не 2001 года. Это происходит потому, что когда член измерения указан не явно — [Время], а не, скажем, [Время].[2001] — либо измерение вообще не фигурирует в запросе, Analysis Services подставляют его член по умолчанию. Таким образом, ячейка всегда определяется полным набором координат, даже если некоторые из них в кортеже опущены. Член, выбираемый по умолчанию, можно назначить через дополнительные свойства измерения (свойство Default Member — см. рис. 2), более того, он может быть свой для каждой роли, к тому же он может быть не константой, а MDX-выражением, результат которого динамически меняется в зависимости, например, от той же роли.

Если член по умолчанию не указан, в качестве него выбирается единственный член уровня All, а при отсутствии этого уровня — первый член следующего уровня. Таким образом, в нашем случае [Время] теперь интерпретируется не как [Время].[Все время], а как [Время].[2000], чем и объясняется изменившийся результат ClosingPeriod(). Вопрос остается прежним: как получить последнюю дату, не зная, какие годы входят в измерение и существует ли у него уровень All?

Работа внутри уровня

первой части мы уже обсуждали некоторые из навигационных функций, такие как FirstChild, LastChild и Children, работающие с «детьми» текущего члена. FirstSibling, LastSibling и Siblings осуществляют навигацию среди его «братьев» аналогично. Функция FirstSibling возвращает первый по порядку член, имеющий того же родителя, что и переданный ей параметр, соответственно Last-Sibling — последний. Например:

[Время].[2000].[2-й квартал].FirstSibling = 
[1-й квартал], [Время].[2000].[2-й квартал].LastSibling = [4-й квартал]. 

Для получения последней даты необходимо переписать наше решение как ClosingPeriod ([День], [Время].LastSibling). Очевидно, что оно работает независимо от уровня All. Если он есть, то [Время].LastSibling = [Время].[Все время].LastSibling = [Все время]. Этот случай рассматривался выше. Если же уровня All нет, то [Время].LastSibling = [Время].[2000].LastSibling = [2001], и ClosingPeriod возвращает последний день последнего года, то есть максимально «свежую» дату.

Функция Siblings возвращает всю коллекцию членов, находящихся на том же уровне, что и заданный, и подчиненных его непосредственному родителю:

[Время].[2000].[2-й квартал].[Май].Siblings = {[Апрель], [Май], [Июнь]}

В отличие от двух предыдущих функций, ее результатом является не член, а множество. Проверить, является ли один член «братом» другого, можно при помощи функции IsSibling:

IsSibling([Время].[2000].[2-й квартал].[Май], [Время].[2000].[3-й квартал].[Август])
    = 0 (False).

Функция Cousin позволяет перемещаться между родителями, возвращая член того же уровня и имеющий ту же относительную позицию по отношению к заданному предку, что и переданный ей в качестве параметра. Например:

Cousin([Время].[2000].[1-й квартал].[Январь], [Время].[2000].[3-й квартал]) 
   = [Время].[2000].[3-й квартал].[Июль]

Потому что подобно тому, как [Январь] является первым ребенком члена [1-й квартал], [Июль] является первым ребенком члена [3-й квартал]. Второй параметр этой функции не обязан быть непосредственным родителем первого. Он может быть предком, расположенным выше по иерархии на число уровней, большее 1. Например:

Cousin([Время].[2000].[1-й квартал].[Январь], [Время].[2001]) =
Время].[2001].[1-й квартал].[Январь].

Перемещение между уровнями

ункции Members и Children можно рассматривать как частные случаи функции Descendants (с той разницей, что аргументом функций Children и Descendants является член измерения, а аргументом функции Members — уровень или вся иерархия). Descendants имеет три параметра: член, потомков которого требуется получить, уровень, показывающий, насколько глубоко нужно погружаться в поисках потомков, и флаг — необязательный параметр, позволяющий дополнительно уточнить навигацию внутри уровней. Например, результатом выражения Descendants([Время]. [2000], [День]) является коллекция всех дней 2000 года, содержащихся в измерении «Время», а результатом выражения Descendants ([Время].[2000], [День], SELF_AND_BEFORE) будут не только все дни, но и все месяцы и кварталы 2000 года и сам 2000 год, то есть все потомки [Время].[2000] вплоть до уровня «День» плюс его самого. Полный набор значений флага имеется в документации.

Функция Ascendants, наоборот, возвращает заданный член вместе с цепочкой cвоих предков вплоть до корня иерархии (уровень All в измерении «Время» для этого примера отсутствовал):

Ascendants([Время].[2000].[1-й квартал].[Январь].[21/01/2000]) = 
{[Время].[2000].[1-й квартал].[Январь].[21/01/2000], [Время].[2000].[1-й квартал].[Январь],
    [Время].[2000].[1-й квартал], [Время].[2000]}

Функция Ancestor возвращает предка заданного члена, находящегося на заданном уровне или на заданном количестве уровней выше:

Ancestor([Время].[2000].[1-й квартал].[Январь].[21/01/2000], [Квартал]) =
Ancestor([Время].[2000].[1-й квартал].[Январь].[21/01/2000], 2) = 
[Время].[2000].[1-й квартал].

В том случае, если у члена имеется несколько предков одного уровня, их можно получить с помощью функции Ancestors, однако такая возможность должна появиться в следующей версии SQL Server — пока Analysis Services такую функцию не поддерживают.

Функция Generate

целях закрепления пройденного рассмотрим небольшую задачу. Как получить всех членов некоторого измерения, допустим, «Время», не используя функцию Members? Очевидно, что с использованием этой функции решение задачи не составляет труда: [Время].Members. Функция Members, как мы помним, в качестве аргумента принимает измерение, конкретную иерархию или отдельный уровень в ней. Но даже исходя из текущего члена можно легко получить измерение, иерархию или уровень, которым он принадлежит, при помощи функций Dimension, Hierarchy и Level соответственно и свести задачу опять-таки к функции Members. Пример: [Время].[2000].[1-й квартал].[Январь].[21/01/2000].Dimension.Members.

Чтобы решение не было столь тривиальным, мы запретили в условии использование этой функции. Тогда напрашивается очевидное решение: применить функцию Descendants вплоть до листового уровня «День» с флагом, выводящим содержимое промежуточных уровней: Descendants([Время] [День], SELF_AND_BEFORE). Проблема заключается в том, что измерение «Время» по-прежнему не имеет уровня «All», а если так, то, как мы уже знаем, [Время] возвратит лишь первый член следующего уровня — «Год» ([2000]), а потомки всех остальных лет в результат не войдут. Это легко проверить:

WITH MEMBER Measures.[Год] AS 'Ancestor([Время].CurrentMember, [Год]).Name'  
            SELECT Descendants([Время], [Месяц]) ON ROWS, 
{Measures.[Год]} ON COLUMNS FROM [Учет рейсов]  

Возможно, вы решите обойти ограничение, используя функцию Siblings, подобно тому, как мы это сделали при поиске последней актуальной даты: Descendants([Время]. Siblings, ...). Однако это решение будет неправильным, так как по своей природе Descendants может принять за один вызов только один член, а не множество. Понятно, что нужен некоторый механизм, который бы проитерировал по результатам Siblings и применил к каждому функцию Descendants. Именно это позволяет сделать функция Generate. Она применяет к каждому члену первого аргумента-множества второй аргумент, представленный функцией, возвращающей множество, либо строковым выражением, и объединяет полученные результаты. Таким образом, с ее участием:

WITH MEMBER Measures.[Год] AS 'Ancestor([Время].CurrentMember, [Год]).Name'  
            SELECT Generate([Время].Siblings, Descendants([Время], [Месяц])) 
ON ROWS, {Measures.[Год]} ON COLUMNS FROM [Учет рейсов]  

мы получаем уже не 2000 год, а список всех лет, содержащихся в измерении «Время». Следовательно, решение может выглядеть, например, так:

Generate([Время].Siblings, Descendants([Время], [День], SELF_AND_BEFORE)).

Упорядочение на основе агрегатов

первой части статьи мы уже упоминали функцию TopCount(<Множество>, <N>, <Числовое выражение>), возвращающую первые N членов множества, указанного в первом аргументе, при этом получаемые члены были упорядочены по убыванию в соответствии с выражением, задаваемым третьим аргументом. С ее помощью, например, легко определить три самых удачных дня, в которые авиакомпания «Орел» перевезла наибольшее число пассажиров:

SELECT TopCount([Время].[День].Members, 3, (Measures. [Колво пасс], [Авиакомпания].[Орел]))
    ON COLUMNS FROM [Учет рейсов]

Аналогичные результаты, но для возрастающего порядка, возвращает функция BottomCount. Уже знакомая нам функция ClosingPeriod, которую мы задействовали для получения наиболее актуальной даты, в действительности есть не что иное, как сокращенная форма записи суперпозиции функций BottomCount и Descendants:

ClosingPeriod(<Уровень>, <Член>) = BottomCount (Descendants(<Член>,    <Уровень>), 1)

Отсюда вытекает важное следствие. Несмотря на свое выраженное «временное» название, функции ClosingPeriod / OpeningPeriod могут применяться для отыскания последнего/первого члена к любым измерениям. Например:

SELECT {ClosingPeriod([Город], [Пункт назначения]. LastSibling)} ON COLUMNS
    FROM [Учет рейсов]

Следует иметь в виду, что изначально города в нашем географическом измерении отсортированы в алфавитном порядке их названий в соответствии со свойством Order By уровня «Город». Подробнее об этом свойстве мы поговорим ниже.

Среди родственных функций BottomCount можно упомянуть TopSum, BottomSum, TopPercent, BottomPercent, которые упорядочивают члены измерения в соответствии с кумулятивной суммой или процентом на основании третьего аргумента. Все эти функции «разрывают» иерархию, то есть рассматривают члены целого уровня независимо от того, к какому родителю они относятся.

Кстати, о кумулятивности. В Analysis Services для агрегирования показателей используются следующие основные аддитивные функции мер: SUM, COUNT, MIN, MAX. У начинающих программистов часто возникает вопрос, почему среди них нет функции отыскания среднего значения. Ответ очевиден: потому что результат среднего зависит от того, по какому множеству усреднять результат. В последнее время (версия 2000) наблюдается тенденция введения стандартных неаддитивных мер. В качестве примера можно привести DISTINCT COUNT, которую в предыдущей версии можно было реализовать только как вычисляемый член (calculated member). Однако следует иметь в виду, что ее произвольное применение сопряжено с ограничениями в масштабируемости приложения. По своей природе AVG является анизотропной мерой, следовательно, ее стандартизация в нынешнем понимании OLAP позволила бы добиться незначительного выигрыша в производительности по сравнению с вычисляемым членом. Разумеется, это не означает тотального запрета на подобные функции. Та же функция AVG является обычной функцией MDX и может быть использована в любом его выражении, в том числе в вычисляемом члене. Например, для определения среднего числа перевезенных пассажиров с февраля по август 2000 года имеем:

WITH MEMBER Measures.Srd as ‘Avg({[Время].[2000].[1-й квартал].[Февраль]:
[Время].[2000].[3-й квартал].[Август]}, [Measures].[Колво пасс]) 
SELECT {Measures.Srd} ON COLUMNS FROM [Учет рейсов]

Вычисление скользящего среднего

аряду с прочими агрегатными показателями вычисление скользящего среднего является не самой «приятной» задачей для OLTP-приложений. Использование OLAP позволяет существенно оптимизировать этот процесс с точки зрения быстродействия (предвычисленные агрегаты) и наличия специализированных функций. В первой части статьи нами уже упоминалась функция Lag. Так, Lag(1) = PrevMember, Lag(-1) = NextMember. В общем случае Lag(n) дает член того же уровня, отстоящий на n позиций раньше заданного при n > 0 и после него при n < 0. В принципе, как и для ClosingPeriod, сфера ее применения не ограничивается временными измерениями. Эта функция идеально подходит для нахождения скользящего среднего.

Допустим, нам необходимо посчитать скользящее среднее по пассажироперевозкам самолетов «Ту-154» авиакомпании «Летучий Голландец», причем для месячного уровня оно должно считаться из расчета трех месяцев, для квартального — из расчета двух кварталов. Вот как это можно сделать:

WITH MEMBER [Measures].[Скользящее среднее] AS ‘Avg([Время].CurrentMember.Lag(IIf ([Время].CurrentMember.Level.Name = «Месяц», 2, IIf ([Время].CurrentMember .Level.Name =

«Квартал», 1, 0))):[Время].CurrentMember, [Measures].[Колво пасс])’ SELECT Descendants ([Время]. [2001], [Месяц], SELF_AND_BEFORE) ON ROWS, {[Measures].[Колво пасс], Measures.[Скользящее среднее]} ON COLUMNS FROM [Учет рейсов] WHERE ([Авиакомпания].[Летучий голландец], [Тип самолета].[ТУ-154])

Наряду с функцией определения текущего уровня существует также функция Levels, возвращающая всю коллекцию уровней данного измерения для текущего члена. Любой уровень можно получить на основе его имени или порядкового номера (следует иметь в виду, что коллекция является zero-based, причем уровень All имеет номер 0). Модифицируем задачу нахождения последней даты в измерении «Время» для ситуации, когда название уровня не известно, но известно, что это последний уровень. Полагаем, что уровень All присутствует. В MDX ее можно решить несколькими способами, например с использованием флага LEAVES функции Descendants. Однако для максимальной эксплуатации функции Levels предлагается следующее решение:

ClosingPeriod([Время].Levels([Время].Levels.Count-1), [Время]).

Имя объекта, свойства соединения и региональные установки

спользовавшаяся в предыдущем примере функция Name позволяет получить имя уровня текущего члена:

WITH MEMBER [Measures].[Название уровня] AS ‘[Время].CurrentMember.Level.Name’
SELECT [Время].Members ON COLUMNS FROM [Учет рейсов] WHERE [Measures].[Название    уровня]

Другой пример. Обратите внимание на формулу расчета вычисляемого члена [Норма] в виртуальном кубе «План_Факт», который также содержится в модельной базе данных «Авиаперевозки» на диске. Для уровня «День» [Норма] вычисляется способом, отличным от других уровней измерения «Время», поэтому в формуле вы можете видеть следующую конструкцию:

[Measures].[План по пасс] * iif([Время]. CurrentMember.Level.Name = «День»,    <…>, 1)

Помимо уровней функция Name применима к измерениям, иерархиям и членам измерений. Имена членов измерения образуются от значения поля в записи транзакционной таблицы, соответствующей измерению на основе формулы, заданной для всего уровня в целом (см. свойство Member Name Column в редакторе измерений). Синтаксис формулы (Transact-SQL, PL-SQL, …) определяется транзакционным источником, на основе которого строится куб в Analysis Services. Например, в случае SQL Server для членов уровня «Квартал» имя может образовываться следующим образом: Сonvert(CHAR(1), DatePart (quarter,«dbo».«vwTime».«Time»)) + ‘-й квартал’, в случае Oracle - TO_CHAR(«Schema».«Tbl». «DateFld», ‘Q’) || ‘-й квартал’ и т.д. Analysis Services просто вставляет это выражение в запрос к транзакционному источнику при обработке измерения. Результат, вообще говоря, зависит от настроек соединения, которое Analysis Services устанавливают с транзакционным источником (рис. 3).

Например, если имена членов уровня «Месяц» рассчитываются по формуле Сonvert(CHAR, DateName(month,«dbo».«vwTime».«Time»)), то полученное название месяца (скажем, May или «Май») определяется параметром Current Language в свойствах OLE DB-соединения.

Свойства объекта

аряду с именем другими стандартными свойствами члена измерения являются Key, ID, Caption, UniqueName, LevelName, LevelDepth и т.д. В частности, ключ (Key) используется для целей идентификации. Формула расчета ключа указывается в редакторе измерений в свойстве Member Key Column. Она не обязана совпадать с именем члена. В нашем примере для месяцев ключ равен DatePart(month,«dbo». «vwTime».«Time»). К члену измерения можно обращаться не только по имени, но и по ключу, предварив его знаком &:

SELECT {[Время].[2000].[2-й квартал].&[5]} ON COLUMNS FROM [Учет рейсов],
что эквивалентно [Время].[2000].[2-й квартал].[Май]. 

Набор свойств объекта зависит от его типа. Для ячеек куба доступны такие свойства, как FORMAT_STRING, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR и др. Они обеспечивают централизованное задание на аналитическом сервере правил форматирования и цветового выделения, которые могут затем использоваться всеми клиентскими приложениями независимо от способа реализации последних. Допускается устанавливать эти свойства динамически на основе значения ячейки. Вычисляемый член [Процент перевып] в виртуальном кубе «План_факт» оценивает расхождение между фактическим числом перевезенных с начала месяца пассажиров и нормой на данный день месяца. В зависимости от величины невыполнения ячейке придается тот или иной оттенок красного цвета. Это достигается следующим MDX-выражением в свойстве FORE_COLOR:

iif ([Measures].[Процент перевып] < -0.4, rgb(255, 20, 20), iif ([Measures].[Процент перевып] < -0.2, rgb(215, 0, 0), iif ([Measures].[Процент перевып] < 0, rgb(127, 0, 0), rgb(0,0,0))))

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

WITH MEMBER [Measures].[Разница] AS ‘[Measures].[Пасс нараст ит] - [Measures].[Норма]’,  
FORE_COLOR = ‘Iif (Count(Filter([Время].
CurrentMember.Children, [Measures].[Разница] < 0)) > 0, 255, 0)’, FORMAT_STRING    = ‘#,#.00’
SELECT {[Measures].[Разница]} ON COLUMNS, Descendants([Время].[2001], 
[Месяц]) ON ROWS FROM [План_факт] 
CELL PROPERTIES FORMATTED_VALUE, FORE_COLOR

Для обращения к свойствам объекта используется функция Properties, которой в качестве параметра передается название свойства. Чтобы поупражняться в этом, вновь обратимся к вычисляемому члену [Норма]. Для дневного уровня его значение должно рассчитываться как месячный план, умноженный на коэффициент, равный номеру текущего дня, деленному на количество дней в месяце. Учитывая, что день, месяц и год служат ключами членов соответствующих уровней, имеем для коэффициента:

val([Время].CurrentMember.Properties(«Key»))
 / Day(DateAdd(«m», 1, DateSerial(Ancestor([Время].CurrentMember, 
[Время].[Год]).Properties(«Key»), Ancestor([Время].CurrentMember, [Время].[Месяц]).Properties(«Key»),    1)) - 1)

Операции над множествами

езультаты объединения, пересечения и разности двух множеств получаются при помощи функций Union, Intersect и Except соответственно. Например:

Union({[Авиакомпания].[Восток-Запад], [Авиакомпания]. [Комета]}, 
{[Авиакомпания].[Комета], [Авиакомпания]. [Ласточка]} =
 {[Авиакомпания].[Восток-Запад], [Авиакомпания].[Комета], [Авиакомпания].[Ласточка]}
Union({[Авиакомпания].[Восток-Запад], [Авиакомпания]. [Комета]}, 
{[Авиакомпания].[Комета], [Авиакомпания].[Ласточка], ALL} =
 {[Авиакомпания].[Восток-Запад], [Авиакомпания].[Комета], [Авиакомпания].[Комета],
     [Авиакомпания].[Ласточка]}
Intersect({[Авиакомпания].[Восток-Запад], [Авиакомпания].[Комета]}, {[Авиакомпания].[Комета],
    [Авиакомпания].[Ласточка]} = {[Авиакомпания].[Комета]} 
Except({[Авиакомпания].[Восток-Запад], [Авиакомпания].[Комета]}, {[Авиакомпания].[Комета], 
   [Авиакомпания].[Ласточка]} = {[Авиакомпания].[Восток-Запад}

Слегка изменим задачу с раскраской ячеек. Если для данного члена измерения «Время» фактические перевозки меньше нормы, соответствующие ячейки должны быть окрашены в красный цвет. Если конкретно для этого члена все измерения в порядке, но один из его потомков (неважно, какой глубины) отстает от нормы — то желтым. Разобьем решение на этапы. Descendants([Время].CurrentMember, [День], SELF_AND_BEFORE), как нам уже известно, даст всех потомков текущего члена вплоть до уровня «День». При этом результат включает сам текущий член, который необходимо отнять: Except(…, {[Время].CurrentMember}). Из результата выберем только те члены, по которым существует невыполнение: Filter (…, [Measures].[Разница] < 0). И если такие члены есть: Сount(…) > 0, то ячейка должна быть желтого цвета. Таким образом, мы имеем:

iif(([Время].CurrentMember, [Measures].[Разница]) < 0, rgb(255, 0, 0), 
iif (Count(Filter(Except(Descendants([Время].CurrentMember, [День], SELF_AND_BEFORE),  
{[Время].CurrentMember}), [Measures]. [Разница] < 0)) > 0, rgb(230, 160,    30), 0)).

Именованные множества

менованное множество (Named Set) — это просто подмножество ячеек куба, выбранных по тому или иному критерию, которому присвоено имя просто для того, чтобы впоследствии к нему было удобно обращаться. Так же как и вычисляемые члены, именованные множества могут определяться в масштабах куба, сессии или запроса. В следующем примере создается именованное множество [Отстающие предприятия], содержащее авиакомпании, не выполняющие план пассажироперевозок по совокупным результатам 2001 года:

WITH MEMBER [Measures].[Разница] AS ‘[Measures].[Пасс нараст ит] - [Measures].[Норма]’
SET [Отстающие предприятия] AS ‘Filter([Авиакомпания].Members, (ClosingPeriod([Год],  
  [Время]. LastSibling), Measures.[Разница] ) < 0)’
SELECT
[Отстающие предприятия] ON ROWS,
{(ClosingPeriod([Год], [Время].LastSibling), [Measures].[Разница])} ON COLUMNS
FROM [План_факт]

Пользовательские свойства и упорядочивание членов уровня

ассмотрим регулярное сбалансированное измерение. Как правило, каждое поле реляционной таблицы или представления либо нескольких таблиц или представлений, на основе которых оно (измерение) построено, участвует в одном (например, City_Dim.RegionName, City_Dim.CountryName для измерения «Пункт назначения») или нескольких (например, DatePart(year,dbo.vwTime.Time), DatePart (quarter,dbo.vwTime.Time) для измерения «Время») его уровнях. Иногда, правда, из задачи не сразу понятно, пригодится нам то или иное поле или нет. Предположим, таблица Airline_Dim содержит поле NumOfEmployees, содержащее, в свою очередь, количество сотрудников, работающих в каждой авиакомпании. По логике вещей эта информация нам сейчас не нужна. Однако весьма возможно, что вскоре нам может понадобиться показатель анализ пассажиропотока в зависимости от объема компании. Поэтому несмотря на то, что количество сотрудников на данный момент «не тянет» на роль отдельного уровня иерархии, совсем отказываться от этих данных нецелесообразно. Сделаем из этого поля свойство члена [Авиакомпания].[Наименование] (member property — рис. 4).

Пример вывода пользовательского свойства:

WITH MEMBER Measures.[Колво сотрудников] AS ‘[Авиакомпания].CurrentMember.Properties(«Колво
    сотрудников»)’
SELECT {Measures.[Колво сотрудников]} ON COLUMNS,
[Авиакомпания].[Все авиакомпании].Children ON ROWS FROM [Учет рейсов]

В предыдущей версии упорядочивать члены уровня можно было только по их имени или ключу. В SQL Server 2000 Analysis Services к этому добавилась возможность сортировать по пользовательскому свойству, то есть фактически по любому полю таблицы измерения. Можно ввести специальное служебное поле, расставить в нем порядковые номера записей, сделать затем из него пользовательское свойство и выбрать в закладке Advanced редактора измерений Order By = это свойство (рис. 5).

Как известно, понятие множества означает неупорядоченный набор. Тем не менее мы уже неоднократно применяли к множествам и их членам функции, предполагающие отношение порядка (например, FirstSibling, LastSibling и т.д.). По умолчанию члены уровня внутри своего родителя считаются отсортированными в соответствии со свойством Order By этого уровня. Некоторые функции явно указывают условие сортировки, как, например, уже знакомые нам Order, TopSum, TopCount и т.д. Вернемся к задаче, с которой мы начали рассмотрение примеров в этой части статьи. При поиске максимально актуальной даты мы предполагали, что дни упорядочены в порядке возрастания номера дня. Это действительно так в нашем примере, но в зависимости от целей анализа дни в измерении «Время» могут быть отсортированы совершенно по иному критерию: скажем, по убыванию количества пассажиров, перевезенных за этот день. Тогда функция ClosingPeriod возвратит нам день с минимальным пассажиропотоком в этом месяце, который, как вы понимаете, не обязан быть последним днем месяца с точки зрения календаря. Как изменится решение с учетом данного фактора?

Очевидно, что независимо от определенного на уровне порядка мы должны отсортировать члены внутри месяца по возрастанию дат и взять от результата последний член:

Tail(Order(Descendants([Время], [День]), [Время].CurrentMember.Name, ASC))

Функция Tail возвращает подмножество из N последних элементов упорядоченного множества. Само множество передается в первом аргументе, N — во втором. Если он опущен, полагается N=1. Результатом работы функции Head служит, напротив, подмножество из N первых элементов. Здесь мы считали, что уровень All в измерении «Время» присутствует. В противном случае LastSibling требуется заменить на поиск года с наибольшим номером. Это делается аналогично, что мы и предлагаем сделать читателю самостоятельно.

КомпьютерПресс 2'2002