Многомерная модель

SELECT {[Measures].[Колво пасс]} on columns, TopCount(ORDER (FILTER([Пункт назначения].Members, [Пункт назначения].CurrentMember.Level.Name =”Город”), [Пункт назначения].CurrentMember.Name, BASC), 3, ([Measures].[Колво пасс])) ON rowS FROM [Учет рейсов]

В результирующем наборе помимо перечисленных городов отображается и столбец мер. 

Описание осей результирующего набора

Оси, по которым мы располагаем результаты MDX-запроса, не обязательно должны совпадать с измерениями куба. Мы можем расположить несколько измерений по одной оси, получив тем самым их декартово произведение. Например, для каждой авиакомпании будут перечислены все типы самолетов, вошедших в результаты запроса. Следовательно, нам необходимо в тексте запроса осуществить привязку множеств к конкретным осям отчета. Несмотря на то что Analysis Services позволяет возвращать истинно многомерные наборы данных, для простоты визуализации требуются, как правило, лишь плоские представления. Обычно используются лишь оси столбцов и строк (рис. 7):

select … on columns,

… on rows

from cube

where …

 

В общем случае результирующий набор может быть многомерным (добавляются  оси страниц, разделов, секций).

 

Каждой оси присваивается  номер:

x-axis=0; y-axis=1; z-axis=2…

 

Для первых пяти осей используются именования и не допускается пропуск осей в запросе:

Columns 0

Rows 1

Pages 2

Sections 3

Chapters 4

 

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

select axis [,axis]

from cube

where slicer [,slicer]

 

Нельзя располагать одно и то же измерение по разным осям отчета, поскольку такая операция лишена смысла. 

 

Функции и выражения языка MDX

 

зык MDX включает в себя значительное количество функций и выражений, позволяющих обрабатывать многомерные  данные в соответствии с бизнес-логикой приложения, например для получения отчетов. Некоторые из них носят избыточный характер, то есть могут быть выражены через другие функции. Это сделано для упрощения конечных формул, поскольку облегчает составление запроса к кубу и делает текст запроса лучше читаемым. 

 

Навигация по дереву измерений

 

Набор функций, позволяющих  ссылаться на одни члены измерения  относительно других, называют функциями  навигации. В MDX навигация осуществляется по измерениям, а точкой отсчета  является член измерения, обрабатываемый в данный момент Analysis Services (выражение CurrentMember), либо указанный в выражении член. 

 

Выражения, не зависящие  от уровня

 

Существуют два выражения, не зависящие от уровня точки отсчета.

 

Если требуется запросить  все члены измерения независимо от уровня, на котором они расположены, используется функция .Members.

 

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

SELECT

{[Пункт назначения].Members} ON ROWS,

{[Авиакомпания].Members} ON COLUMNS

FROM [Учет рейсов]

WHERE ([Время].[Все Время].[2000],

Measures.[Колво пасс])

 

На рис. 9 приведен результат  выполнения запроса. Выражение .Members позволило получить все значения измерения [Пункт назначения].

 

DefaultMember — выражение, позволяющее работать со значением измерения по умолчанию. 

 

На том же уровне (CurrentMember; PrevMember)

 

Для того чтобы перемещаться в пределах уровня, на котором находится  точка отсчета, используются следующие  функции. Ссылку на соседние члены измерения  без указания имени члена обеспечивают выражения .PrevMember и .NextMember.

 

Допустим, требуется создать  отчет, отображающий сведения на интересующий пользователя месяц в сравнении  с предыдущим. На одну из осей отчета помещаем измерение «Время», месяц  «март» (рис. 10):

 

Чтобы сослаться в запросе  на апрель, применим выражение:

[Время].[2000].[Март].NextMember

 

Чтобы сослаться на февраль:

[Время].[2000].[Март].PrevMember

 

Обратиться к члену  январь можно, используя выражение:

[Время].[2000].[Март].PrevMember.PrevMember,

 

но удобнее применить  более общую функцию:

[Время].[2000].[Март].Lag(2)

 

или:

[Время].[2000].[Март].Lead(-2)

 

Отрицательное число в  скобках меняет направление отсчета  членов измерения.

 

А теперь вернемся к нашему примеру. Представим, что главному экономисту аэропорта требуется отчет об объеме грузоперевозок за текущий год  по сравнению с предыдущим (рис. 11):

WITH MEMBER [Measures].[Вес груза за пред период]

AS ‘([Measures].[Вес груза],[Время].PREVMEMBER)’

SELECT CROSSJOIN({[Время].[2000], [Время]. [2001]},

{[Measures].[Вес груза], [Measures].[Вес груза за пред период]}) ON ROWS, {[Авиакомпания].[Комета],

    [Авиакомпания].[Ласточка]} ON COLUMNS

FROM [Учет рейсов]

 

Существует еще несколько  функций для обращения к членам измерения на том же уровне:

 определение первого  и последнего члена в ряду  FirstSibling и LastSibling;

 определение «кузенов»,  принадлежащих другому родителю  — Cousin(). 

 

Уровень ниже, уровень выше

 

Предполагается, что в  момент выполнения запроса известны имена членов родителей или потомков интересующего члена. В примере  отображены 2-го уровня измерения [Пункт  назначения] — Страна и Город. Функциональность выражений, используемых в этой группе, очевидна: .Children, .FirstChild, .LastChild — для известного родителя и .Parent — для известного потомка.

 

Допустим, требуется регулярно  предоставлять отчет о перевозках пассажиров и груза в пределах Российской Федерации (рис. 12).

 

Для этого на ось строк  поместим измерение [Пункт назначения]. Перечисление одного за другим всех городов России, имеющихся в измерении, могло бы решить поставленную задачу, но это не только приведет к удлинению запроса, но и заложит мину замедленного действия: спустя некоторое время может открыться рейс в город, в который ранее полеты не совершались, а потому его название не было включено в измерение. В результате подготовленный отчет станет неточен. Чтобы гарантировать пользователю актуальность созданных запросов, следует на оси строк расположить выражение (рис. 13):

[Пункт назначения].[РФ].Children

 

Выражения .FirstChild и .LastChild определяют первого и последнего потомка родителя (рис. 14).

 

Чтобы определить родителя известного члена измерения, используется функция .Parent (рис. 15).

 

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

[Measures].[Колвопасс].[Пункт назначения]. CurrentMember/

[Measures].[Колво пасс].[Пункт назначения]. CurrentMember.Parent*100

 

Для того чтобы определить «дедушку» (родителя родителя), например регион для города Алма-Ата, используем выражение:

[Алма-Ата].Parent.Parent – «СНГ».

 

В общем случае используется функция Descendants(), позволяющая ссылаться на члены измерений различных уровней. 

 

Другие функции и выражения 

 

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

 

Для получения декартова произведения членов измерений существует функция CrossJoin.

 

Допустим, экономической  службе авиапредприятия требуется  провести анализ перевозок авиакомпаний в разрезе регионов полетов за текущий и предыдущий годы. Чтобы построить отчет, разместим на оси строк [Пункт назначения].[Все пункты].Children и [Авиакомпания].[Все авиакомпании].Children, а на оси столбцов разместим измерение [Время] (рис. 16):

SELECT CROSSJOIN({[Пункт назначения].[Все пункты].Children},

{[Авиакомпания].[Все авиакомпании].Children})

ON ROWS,

{[Время].[Все время].[2000], [Время].[Все время].[2001]} ON COLUMNS

FROM [Учет рейсов]

Filter, TopCount(), TopSum()

 

Функция CrossJoin() позволяет увеличить результирующий набор строк за счет пересечения измерений, но нередко возникает необходимость уменьшить поток выходных данных, предъявляя определенные условия к мерам или измерениям. Задачи фильтрации данных занимают в многомерном анализе одно из первых мест по количеству запросов, которые используют функцию Filter и некоторые другие функции, служащие для отсечения ненужной информации.

 

В общем случае построения запроса недостаточно указать, какие  измерения будут находиться на осях, а по каким будет произведен срез. Аналитик нуждается в деталях, уточнениях, конкретных данных. Эти возможности  предоставляются функцией Filter. Ее синтаксис очень прост: необходимо указать набор данных и условное выражение, а возвращены будут данные исходного набора, для которых выполняется указанное в Filter() условие. Кроме основной функции фильтрации имеется набор вспомогательных функций: TopCount(),TopSum(), BottomCount(), BottomSum().

 

Сочетание этих функций дает аналитику возможность формировать  такие типовые отчеты, как:

 Фильтрация данных  по величине показателя 

 

(Авиакомпании-перевозчики  с объемом перевозок не менее…):

SELECT FILTER({[Авиакомпания].[Все авиакомпа-нии].Children}, ([Measures].[Колво

        пасс])>50) ON ROWS, {[Measures].[Колво пасс]} ON COLUMNS FROM [Учет рейсов]

 Фильтрация данных по условию на измерение

 

(Города, содержащие в  названиях подстроку):

SELECT FILTER({[Пункт назначения].members}, InStr([Пункт назначения].CurrentMember.Name,

        “ск”)<>0) ON ROWS, {[Measures].[Вес груза], [Measures].[Колво пасс]}

        ON COLUMNS FROM [Учет рейсов]

 Ведущие и последние в отсортированном списке

 

(Три авиакомпании-перевозчика  с наибольшим пассажирооборотом):

SELECT TopCount({[Авиакомпания].[Все авиакомпа-нии].Children}, 3, ([Measures].[Колво

        пасс])) ON ROWS, {[Measures].[Колво пасс]} ON COLUMNS FROM [Учет рейсов] 

 

ORDER

 

Итак, все необходимые  для анализа данные получены, осталось лишь представить информацию в удобном  для пользователя виде. Упорядочивание членов измерений на осях производится при помощи функции ORDER(). В обычной  ситуации, когда нужно отсортировать  члены одного уровня измерения на оси, ее действие аналогично предикату ORDER языка SQL. Если в запросе используются члены нескольких уровней измерения, упорядочивание членов можно производить  как с сохранением иерархии уровней, так без нее (так называемый Break Hierarchy):

ASC | DESC | BASC | BDESC,

BASC – B (Break Hierarchy) ASC

 

В ряде случаев необходимо анализировать данные без учета  иерархии. Рассмотрим это на примерах.

 

Задача № 1: отсортировать  данные о количестве отправленных пассажиров по направлениям полетов по убыванию с сохранением иерархии измерения  «Пункт назначения» (рис. 17):

 

SELECT {[Measures].[Колво пасс]} ON COLUMNS, ORDER({[Пункт назначения].members}, ([Measures]. [Колво пасс]), DESC) ON ROWS FROM [Учет рейсов]

 

Задача № 2: предоставить сведения о количестве пассажиров по направлениям полетов, упорядоченных  по убыванию, включая сведения по регионам, странам и городам, независимо от уровня — для выявления нерентабельных направлений (рис. 18):

 

Результаты анализа показывают, что полеты в Алма-Ату приносят большую прибыль, чем полеты во Владивосток, хотя полеты в регионе Россия значительно  выгоднее полетов в Казахстан. 

 

NonEmpty

 

Регулярные загрузки хранилища  данных не предполагают, что при  каждой загрузке будут предоставляться  данные по всем измерениям и показателям. Например, в бархатный сезон рейсы  на Адлер осуществляются всеми авиакомпаниями ежедневно, а в зимнее время —  лишь два раза в неделю, то есть для  измерения «Время» не каждый день января будет заполнен сведениями о  полетах на Адлер. Это значит, что  появятся «пустые» ячейки, которые  являются неинформативными и вряд ли будут интересовать аналитика при работе с отчетом. Необходимо учитывать, что хотя пустоты физически в кубе не хранятся, многомерная модель требует их отображения. Можно исключить пустые строки и столбцы из отчета, применив функцию NonEmpty. Допустим, вас интересует напряженность полетов в конкретные дни января по направлениям полетов. Задействовав функцию NonEmpty, вы исключаете направления полетов, по которым не осуществлялись рейсы за этот период. Отчет сокращается, а результаты не искажаются.