У ‎каждого,‏ ‎кто ‎работает ‎с ‎многомерными ‎запросами,‏ ‎рано ‎или‏ ‎поздно‏ ‎обязательно ‎возникает ‎желание‏ ‎задействовать ‎переменные.‏ ‎Но, ‎вот ‎беда, ‎переменных-то‏ ‎в‏ ‎языке ‎MDX‏ ‎нет ‎(или‏ ‎есть, ‎но ‎менеджмент-студия ‎про ‎них‏ ‎не‏ ‎знает)!

Что ‎же‏ ‎делать, ‎когда‏ ‎очень ‎хочется ‎написать ‎вот ‎такую‏ ‎конструкцию:

Есть‏ ‎два‏ ‎универсальных ‎способа‏ ‎использования ‎переменных‏ ‎в ‎MDX-запросах.‏ ‎По‏ ‎крайней ‎мере‏ ‎один ‎из ‎них ‎вам ‎обязательно‏ ‎подойдёт.

Во-первых, ‎вы‏ ‎можете‏ ‎формировать ‎MDX-запрос ‎динамически,‏ ‎причём ‎средствами‏ ‎Transact-SQL. ‎А ‎для ‎выполнения‏ ‎передавать‏ ‎его ‎аналитическому‏ ‎серверу ‎через‏ ‎механизм ‎связанных ‎серверов ‎(Linked ‎Server). Вот‏ ‎так:

Здесь‏ ‎MDX_AW ‎—‏ ‎это ‎связанный‏ ‎SSAS-сервер, ‎ссылающийся ‎на ‎вашу ‎многомерную‏ ‎базу‏ ‎данных.‏ ‎Результат, ‎разумеется,‏ ‎вы ‎получите‏ ‎в ‎табличном‏ ‎виде,‏ ‎как ‎и‏ ‎положено ‎в ‎языке ‎SQL. ‎Кстати,‏ ‎обратите ‎внимание‏ ‎на‏ ‎названия ‎столбцов.

Такое ‎решение,‏ ‎конечно, ‎нельзя‏ ‎назвать ‎изящным ‎— ‎это‏ ‎классический‏ ‎костыль. ‎Зато,‏ ‎он ‎выручит‏ ‎вас ‎в ‎любой ‎ситуации, ‎это‏ ‎универсальный‏ ‎способ.

Во-вторых, ‎в‏ ‎некоторых ‎ситуациях‏ ‎вы ‎можете ‎попробовать ‎обойтись ‎родными‏ ‎средствами‏ ‎MDX.‏ ‎Зачем ‎вам‏ ‎понадобились ‎переменные‏ ‎в ‎MDX-сценарии?‏ ‎Скорее‏ ‎всего ‎для‏ ‎параметризации ‎запроса. ‎А ‎параметризовать ‎запрос‏ ‎можно ‎и‏ ‎штатными‏ ‎средствами, ‎если ‎переписать‏ ‎его ‎в‏ ‎формате ‎XMLA:

Разумеется, ‎для ‎этого‏ ‎ваша‏ ‎аналитическая ‎система‏ ‎должна ‎уметь‏ ‎принимать ‎XMLA-сценарии ‎из ‎приложения. ‎А‏ ‎также‏ ‎учтите, ‎что‏ ‎вам ‎скорее‏ ‎всего ‎придётся ‎работать ‎в ‎XML-формате‏ ‎не‏ ‎только‏ ‎с ‎самим‏ ‎запросом, ‎но‏ ‎и ‎с‏ ‎результатом:

Зато,‏ ‎в ‎отличие‏ ‎от ‎варианта ‎со ‎связанным ‎OLAP-сервером,‏ ‎вы ‎получите‏ ‎настоящий‏ ‎многомерный ‎результат, ‎а‏ ‎не ‎табличный.

Это ‎классическая‏ ‎задача ‎по ‎созданию ‎списка ‎с‏ ‎разделителями. ‎При‏ ‎работе‏ ‎с ‎многомерной ‎базой‏ ‎данных ‎такой‏ ‎список ‎вы ‎вероятнее ‎всего‏ ‎захотите‏ ‎сгенерировать ‎из‏ ‎элементов ‎множества.‏ ‎Собрать ‎такую ‎строку ‎в ‎MDX-запросе‏ ‎очень‏ ‎просто!

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

Как ‎из ‎элементов ‎списка‏ ‎(столбца,‏ ‎набора) ‎собрать‏ ‎длинную ‎строку‏ ‎с ‎разделителями?

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

Однако, ‎не‏ ‎торопитесь ‎задействовать‏ ‎StrToSet! Работая ‎с ‎этой ‎функцией,‏ ‎вам‏ ‎каждый ‎раз‏ ‎придётся ‎решать‏ ‎четыре ‎сопутствующих ‎проблемы:

1. StrToSet заключает ‎результирующую ‎строку‏ ‎в‏ ‎фигурные ‎скобки,‏ ‎которые ‎нам‏ ‎скорее ‎всего ‎не ‎нужны. ‎Надо‏ ‎не‏ ‎забыть‏ ‎от ‎них‏ ‎избавиться.
2. Вы ‎скорее‏ ‎всего ‎хотели‏ ‎получить‏ ‎список ‎из‏ ‎названий ‎или ‎значений ‎элементов ‎множества.‏ ‎А ‎StrToSet сконвертирует‏ ‎в‏ ‎строку ‎не ‎названия,‏ ‎а ‎ключи.‏ ‎Придётся ‎подумать, ‎как ‎заменить‏ ‎их‏ ‎на ‎требуемые‏ ‎имена.
3. Разделитель ‎строки,‏ ‎возвращаемой ‎StrToSet — всегда ‎запятая. ‎Если ‎нужно‏ ‎что-то‏ ‎другое, ‎потребуется‏ ‎дополнительная ‎обработка.
4. И,‏ ‎конечно, ‎надо ‎будет ‎избавиться ‎от‏ ‎обрамления‏ ‎в‏ ‎виде ‎префиксов‏ ‎и ‎квадратных‏ ‎скобок.

Есть ‎другой‏ ‎способ‏ ‎решения ‎этой‏ ‎задачи. ‎Гораздо ‎более ‎удобный ‎и‏ ‎управляемый. ‎Один‏ ‎из‏ ‎режимов ‎работы ‎функции‏ ‎Generate как ‎раз‏ ‎предназначен ‎для ‎формирования ‎длинной‏ ‎строки‏ ‎с ‎разделителями.‏ ‎При ‎этом‏ ‎вы ‎сами ‎выбираете ‎и ‎значения‏ ‎и‏ ‎разделители.

Вот ‎так:

Функция‏ ‎CurrentMember в ‎этом‏ ‎контексте ‎работает ‎так ‎же, ‎как‏ ‎и‏ ‎всегда.‏ ‎Только ‎она‏ ‎отслеживает ‎корреляцию‏ ‎с ‎перебираемыми‏ ‎элементами‏ ‎не ‎по‏ ‎оси, ‎а ‎внутри ‎множества. ‎И,‏ ‎кстати, ‎синтаксически‏ ‎здесь‏ ‎разрешено ‎вызывать ‎CurrentMember не‏ ‎для ‎иерархии,‏ ‎а ‎сразу ‎для ‎перебираемого‏ ‎набора.

Вот‏ ‎ещё ‎пара‏ ‎примеров.

Давайте ‎составим‏ ‎список ‎не ‎из ‎названий, ‎а‏ ‎из‏ ‎произвольных ‎свойств:

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

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

А ‎вы ‎знаете,‏ ‎что‏ ‎из ‎MDX-запроса‏ ‎можно ‎вызывать‏ ‎экселевские ‎(и ‎ВБА) ‎функции? ‎Да,‏ ‎это‏ ‎то‏ ‎самое ‎чувство,‏ ‎когда ‎вы‏ ‎легко ‎можете‏ ‎добиться‏ ‎желаемого ‎результата‏ ‎в ‎Экселе, ‎а ‎вот ‎в‏ ‎MDX…

Попробуйте ‎обратиться‏ ‎к‏ ‎внешним ‎функциям ‎через‏ ‎префикс ‎Excel! или‏ ‎VBA!. Вот ‎так:

Студия ‎подчёркивает ‎вызов‏ ‎функции‏ ‎Format красным ‎—‏ ‎это ‎ошибка?‏ ‎Ведь ‎такой ‎функции ‎нет ‎в‏ ‎языке‏ ‎MDX! ‎Верно,‏ ‎нет. ‎Но‏ ‎зато ‎она ‎есть ‎в ‎VBA.

Обращаться‏ ‎к‏ ‎функциям‏ ‎VBA ‎можно‏ ‎либо ‎через‏ ‎приставку ‎«VBA!», либо‏ ‎через‏ ‎«VBAMDX.», а ‎можно‏ ‎и ‎вообще ‎без ‎префикса:

А ‎ещё‏ ‎вы ‎можете‏ ‎аналогичным‏ ‎образом ‎задействовать ‎функции‏ ‎Excel. ‎Вот‏ ‎так:

Однако, ‎прежде ‎чем ‎использовать‏ ‎такой‏ ‎«ход ‎конём»,‏ ‎примите ‎во‏ ‎внимание ‎три ‎вещи.

Во-первых, ‎вам ‎доступны‏ ‎не‏ ‎все ‎функции,‏ ‎а ‎только‏ ‎те, ‎использование ‎которых ‎в ‎контексте‏ ‎MDX-запроса‏ ‎не‏ ‎противоречит ‎здравому‏ ‎смыслу. ‎Из‏ ‎всего ‎богатства‏ ‎ВБА‏ ‎можете ‎использовать‏ ‎вот ‎эти ‎девяносто ‎функций:

Во-вторых, ‎учтите,‏ ‎что ‎если‏ ‎вам‏ ‎понадобятся ‎функции ‎из‏ ‎Экселя, ‎то‏ ‎вам ‎придётся ‎установить ‎сам‏ ‎эксель‏ ‎на ‎машину‏ ‎с ‎аналитическим‏ ‎сервером ‎(следите ‎за ‎совпадением ‎битности).‏ ‎И,‏ ‎кстати, ‎экселевские‏ ‎функции ‎также‏ ‎доступны ‎далеко ‎не ‎все.

И ‎в-третьих,‏ ‎не‏ ‎ждите‏ ‎от ‎этого‏ ‎механизма ‎высокой‏ ‎производительности. ‎Если‏ ‎требуется‏ ‎высокомасштабируемое ‎решение,‏ ‎то ‎применять ‎функции ‎VBA ‎и‏ ‎Excel ‎следует‏ ‎с‏ ‎осторожностью.

В ‎MDX-запросе‏ ‎мы ‎можем ‎сами ‎выбирать ‎что‏ ‎и ‎вдоль‏ ‎какой‏ ‎оси ‎выводить. ‎Можно,‏ ‎к ‎примеру,‏ ‎выбросить ‎календарь ‎на ‎горизонтальную‏ ‎ось,‏ ‎а ‎товары‏ ‎— ‎на‏ ‎вертикальную. ‎А ‎можно ‎наоборот. ‎Но‏ ‎это‏ ‎только ‎в‏ ‎том ‎случае,‏ ‎если ‎запрос ‎использует ‎несколько ‎измерений.

А‏ ‎если‏ ‎нам‏ ‎нужна ‎только‏ ‎одна ‎размерность?

Посмотрите‏ ‎на ‎размерность‏ ‎на‏ ‎картинке. ‎Это‏ ‎довольно ‎длинные ‎строки, ‎и ‎их‏ ‎список, ‎конечно,‏ ‎удобнее‏ ‎листать ‎по ‎вертикали.

Но‏ ‎в ‎MDX-запросе‏ ‎первая ‎ось ‎всегда ‎горизонтальная‏ ‎(ON‏ ‎COLUMNS).

И, ‎если‏ ‎мы ‎просто‏ ‎выведем ‎список ‎на ‎эту ‎ось,‏ ‎получится‏ ‎не ‎очень‏ ‎удобный ‎горизонтальный‏ ‎список.

Посмотрите:

Листать ‎такой ‎список ‎не ‎очень‏ ‎удобно.‏ ‎Обычно‏ ‎хочется, ‎чтобы‏ ‎горизонтальная ‎ось‏ ‎была ‎покороче‏ ‎и‏ ‎вела ‎себя‏ ‎более ‎предсказуемо. ‎Однако, ‎пропустить ‎горизонтальную‏ ‎ось ‎нельзя‏ ‎—‏ ‎синтаксически ‎в ‎MDX-запросе‏ ‎сначала ‎обязательно‏ ‎идут ‎столбцы ‎(ON ‎COLUMNS),‏ ‎а‏ ‎потом ‎строки‏ ‎(ON ‎ROWS).

Решение‏ ‎— ‎использовать ‎пустое ‎множество ‎для‏ ‎скрытия‏ ‎ненужных ‎осей.‏ ‎Вот ‎так:

Теперь‏ ‎и ‎со ‎списком ‎работать ‎удобнее‏ ‎и‏ ‎сроцесс‏ ‎построения ‎структуры‏ ‎запроса ‎стал‏ ‎более ‎комфортным.

В‏ ‎итоговом‏ ‎запросе ‎место‏ ‎пустого ‎перечисления, ‎вероятно ‎займёт ‎какая-то‏ ‎другая ‎конструкция,‏ ‎но‏ ‎в ‎процессе ‎сочинения‏ ‎этого ‎запроса‏ ‎конструкция ‎«{} ‎ON ‎COLUMNS»‏ ‎создаёт‏ ‎для ‎разработчика‏ ‎дополнительную ‎степень‏ ‎свободы.

Есть ‎два‏ ‎популярных ‎сценария ‎использования ‎динамических ‎MDX-запросов:‏ ‎сложная ‎параметризация‏ ‎и‏ ‎контекстно-зависимые ‎запросы. ‎Да,‏ ‎бывает, ‎что‏ ‎без ‎динамических ‎MDX-запросов ‎не‏ ‎обойтись.‏ ‎На ‎этот‏ ‎случай ‎имеются‏ ‎четыре ‎инструмента ‎— ‎они ‎предназначены‏ ‎как‏ ‎раз ‎для‏ ‎конструирования ‎запросов‏ ‎«на ‎лету».

Все ‎умеют ‎писать ‎динамические‏ ‎SQL-запросы.‏ ‎Составляем‏ ‎строку ‎и‏ ‎передаём ‎её‏ ‎команде ‎EXECUTE или‏ ‎процедуре‏ ‎sp_ExecuteSQL:

Многие ‎также‏ ‎в ‎курсе, ‎чем ‎эти ‎два‏ ‎способа ‎различаются.‏ ‎А‏ ‎что ‎насчёт ‎динамических‏ ‎MDX-запросов?

Ведь ‎в‏ ‎языке ‎MDX ‎синтаксические ‎ограничения‏ ‎заметно‏ ‎более ‎суровы,‏ ‎чем ‎в‏ ‎SQL, ‎и ‎без ‎динамического ‎формирования‏ ‎MDX-выражений‏ ‎бывает ‎тяжело.

Для‏ ‎работы ‎с‏ ‎динамическими ‎запросами ‎в ‎MDX ‎имеется‏ ‎набор‏ ‎функций,‏ ‎названия ‎которых‏ ‎начинаются ‎с‏ ‎«StrTo»:

• StrToMember
• StrToSet
• StrToTuple
• StrToValue

В ‎отличие‏ ‎от‏ ‎языка ‎SQL,‏ ‎при ‎работе ‎с ‎многомерными ‎базами‏ ‎данных ‎мы‏ ‎строим‏ ‎динамически ‎не ‎весь‏ ‎запрос ‎целиком,‏ ‎а ‎отдельные ‎его ‎части:‏ ‎член‏ ‎размерности, ‎перечисление,‏ ‎кортеж ‎или‏ ‎скалярное ‎значение.

Для ‎использования ‎динамической ‎адресации‏ ‎члена‏ ‎размерности ‎используйте‏ ‎функцию ‎StrToMember:

Синтаксически‏ ‎корректное ‎перечисление ‎можно ‎сконструировать ‎функцией‏ ‎StrToSet:

StrToTuple поможет‏ ‎собрать‏ ‎кортеж ‎из‏ ‎координат ‎или‏ ‎из ‎их‏ ‎частей:

А‏ ‎StrToValue позволяет ‎интерпретировать‏ ‎выражение ‎как ‎ячейку:

Подобные ‎динамические ‎конструкции‏ ‎могут ‎пригодиться‏ ‎вам‏ ‎при ‎параметризации ‎MDX-запросов‏ ‎или ‎при‏ ‎индивидуальной ‎нарезке ‎куба ‎с‏ ‎привязкой‏ ‎к ‎текущему‏ ‎пользователю.