Когда ‎Илон‏ ‎Маск ‎заходит ‎в ‎бар, ‎все‏ ‎его ‎посетители‏ ‎становятся‏ ‎в ‎среднем ‎миллиардерами!‏ ‎:)

Эта ‎старая‏ ‎шутка ‎иллюстрирует ‎проблему ‎вычисления‏ ‎среднего‏ ‎значения. ‎Когда‏ ‎нам ‎требуется‏ ‎среднее ‎значение ‎по ‎столбцу, ‎обычно‏ ‎мы‏ ‎рефлекторно ‎используем‏ ‎агрегатную ‎функцию‏ ‎AVG. Но, ‎как ‎ни ‎странно, ‎в‏ ‎большинстве‏ ‎случаев‏ ‎такое ‎решение‏ ‎будет ‎неправильным.

Вот‏ ‎классический ‎пример‏ ‎—‏ ‎посмотрите ‎на‏ ‎список ‎сотрудников:

Какова ‎средняя ‎зарплата ‎в‏ ‎компании? ‎Если‏ ‎использовать‏ ‎популярную ‎функцию ‎AVG, то‏ ‎получим ‎среднее‏ ‎арифметическое ‎— ‎чуть ‎больше‏ ‎миллиона‏ ‎рублей.

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

А ‎вот ‎профсоюз ‎работников‏ ‎скорее‏ ‎всего‏ ‎с ‎такой‏ ‎статистикой ‎не‏ ‎согласится. ‎Ведь‏ ‎если‏ ‎оценивать ‎«по‏ ‎честному», ‎то ‎зарплата ‎в ‎среднем‏ ‎чуть ‎больше‏ ‎ста‏ ‎рублей!

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

А‏ ‎теперь‏ ‎заново ‎просмотрите‏ ‎все ‎ваши‏ ‎аналитические ‎SELECT’ы‏ ‎—‏ ‎наверняка ‎во‏ ‎многих ‎местах ‎вы ‎почувствуете ‎необходимость‏ ‎заменить ‎среднее‏ ‎арифметическое‏ ‎на ‎медиану.

В ‎SQL‏ ‎вычисление ‎медианы‏ ‎можно ‎выполнить ‎при ‎помощи‏ ‎функции‏ ‎PERCENTILE_CONT. Эта ‎функция‏ ‎рассчитывает ‎процентили,‏ ‎так ‎что ‎для ‎получения ‎медианы‏ ‎нужно‏ ‎указать ‎параметр‏ ‎0,5.

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

1. Медиана‏ ‎вычисляется‏ ‎гораздо ‎медленнее,‏ ‎чем ‎среднее ‎арифметическое. ‎Если ‎посмотреть‏ ‎на ‎план‏ ‎исполнения‏ ‎этого ‎запроса, ‎сразу‏ ‎станет ‎понятно‏ ‎насколько ‎тяжело ‎серверу ‎даётся‏ ‎вычисление‏ ‎процентилей.
2. Функция ‎PERCENTILE_CONT‏ ‎весьма ‎неудобно‏ ‎оформлена ‎синтаксически. ‎Не ‎как ‎обычная‏ ‎агрегатная‏ ‎функция ‎и‏ ‎даже ‎не‏ ‎как ‎функция ‎от ‎набора ‎строк.‏ ‎Это‏ ‎функция‏ ‎над ‎упорядоченным‏ ‎набором, ‎поэтому‏ ‎использовать ‎её‏ ‎в‏ ‎запросах ‎бывает‏ ‎не ‎очень ‎удобно.

Сразу ‎хочется ‎спросить:

• Что‏ ‎за ‎странный‏ ‎синтаксис:‏ ‎"WITHIN ‎GROUP"?
• Зачем ‎здесь‏ ‎"TOP ‎(1)"?
• Почему‏ ‎в ‎секции ‎"OVER" пустые ‎скобки‏ ‎-‏ ‎разве ‎так‏ ‎можно?
• Как ‎посчитать‏ ‎медиану ‎в ‎старых ‎версиях ‎СУБД,‏ ‎где‏ ‎нет ‎оконных‏ ‎функций?

Да, ‎соглашусь,‏ ‎это ‎неудобно. ‎Хотелось ‎бы ‎просто‏ ‎иметь‏ ‎простую‏ ‎функцию, ‎типа‏ ‎median(), как ‎во‏ ‎многих ‎других‏ ‎языках.‏ ‎Но ‎в‏ ‎SQL ‎пока ‎так.

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

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

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

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

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

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

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

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

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

Опытные ‎разработчики‏ ‎и ‎администраторы ‎прекрасно ‎знают, ‎что‏ ‎никогда ‎и‏ ‎ни‏ ‎при ‎каких ‎обстоятельствах‏ ‎нельзя ‎назначать‏ ‎разрешения ‎отдельным ‎пользователям. ‎Только‏ ‎группам‏ ‎или ‎ролям!

Это‏ ‎действительно ‎правильный‏ ‎подход, ‎но ‎если ‎вы ‎его‏ ‎исповедуете,‏ ‎время ‎от‏ ‎времени ‎у‏ ‎вас ‎будет ‎возникать ‎необходимость ‎проверять‏ ‎членство‏ ‎конкретного‏ ‎пользователя ‎(как‏ ‎правило, ‎текущего)‏ ‎к ‎той‏ ‎или‏ ‎иной ‎группе‏ ‎или ‎его ‎принадлежность ‎к ‎конкретной‏ ‎роли.

В ‎SQL-сервере‏ ‎есть‏ ‎простые ‎функции ‎и‏ ‎представления ‎для‏ ‎таких ‎проверок.

Для ‎демонстрации ‎этих‏ ‎механизмов‏ ‎я ‎создам‏ ‎несколько ‎ролей‏ ‎и ‎групп ‎разных ‎типов. ‎Разумеется,‏ ‎проверим,‏ ‎учитывает ‎ли‏ ‎проверка ‎вложенность.‏ ‎В ‎моих ‎примерах ‎проверяемый ‎пользователь‏ ‎входит‏ ‎только‏ ‎в ‎самую‏ ‎глубоко ‎вложенную‏ ‎группу/роль ‎—‏ ‎№‏ ‎3 ‎(помимо‏ ‎членства ‎в ‎одной-двух ‎стандартных).

Роли ‎базы‏ ‎данных

Анализируя ‎системные‏ ‎представления‏ ‎Sys.Database_Principals и ‎Sys.Database_Role_Members, вы ‎получите‏ ‎полную ‎картину‏ ‎о ‎членстве ‎в ‎группах.

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

У ‎этой ‎функции ‎есть‏ ‎расширенная ‎версия‏ ‎—‏ ‎Is_RoleMember, у ‎которой ‎есть‏ ‎второй ‎параметр,‏ ‎через ‎который ‎можно ‎указать‏ ‎идентификатор‏ ‎пользователя ‎(database_principal),‏ ‎членство ‎которого‏ ‎хотим ‎проверить.

Группы ‎Active ‎Directory

Функция ‎Is_Member умеет‏ ‎также‏ ‎работать ‎с‏ ‎группами ‎Active‏ ‎Directory. ‎И ‎тоже ‎с ‎учётом‏ ‎вложенности.

Учтите,‏ ‎что‏ ‎эта ‎функция‏ ‎ориентируется ‎на‏ ‎SID’ы ‎групп,‏ ‎рассчитанные‏ ‎при ‎входе‏ ‎пользователя. ‎Поэтому, ‎если ‎пользователь ‎был‏ ‎включён ‎в‏ ‎группу‏ ‎после ‎соединения ‎с‏ ‎сервером, ‎придётся‏ ‎переподключиться.

Серверные ‎роли

Вряд ‎ли ‎вам‏ ‎когда-нибудь‏ ‎на ‎практике‏ ‎придётся ‎программно‏ ‎проверять ‎членство ‎в ‎серверной ‎роли,‏ ‎однако‏ ‎такая ‎возможность‏ ‎имеется ‎—‏ ‎представление ‎Sys.Server_Role_Members.

А ‎также ‎функция ‎Is_SrvRoleMember, которая‏ ‎работает‏ ‎аналогично‏ ‎Is_RoleMember, но ‎на‏ ‎уровне ‎контекста‏ ‎сервера ‎(login),‏ ‎а‏ ‎не ‎базы‏ ‎данных.

Не ‎всякую‏ ‎базу ‎данных ‎можно ‎перенести ‎с‏ ‎Enterprise-редакции ‎на‏ ‎SQL‏ ‎Server ‎Standard ‎Edition.‏ ‎Имеется ‎специальный‏ ‎механизм, ‎показывающий ‎список ‎задействованных‏ ‎enterprise-механизмов,‏ ‎мешающих ‎миграции‏ ‎на ‎стандартную‏ ‎редакцию.

Приходилось ‎ли ‎вам ‎при ‎восстановлении‏ ‎базы‏ ‎данных ‎из‏ ‎резервной ‎копии‏ ‎или ‎при ‎присоединении ‎файлов ‎базы‏ ‎к‏ ‎серверу‏ ‎наблюдать ‎сообщения‏ ‎об ‎ошибке,‏ ‎при ‎том,‏ ‎что‏ ‎на ‎исходном‏ ‎сервере ‎база ‎восстанавливается/присоединяется ‎нормально?

Часто ‎это‏ ‎происходит ‎при‏ ‎попытке‏ ‎переноса ‎базы ‎с‏ ‎корпоративной ‎(Enterprise‏ ‎Edition) ‎на ‎стандартную ‎(Standatd‏ ‎Edition)‏ ‎редакцию ‎SQL-сервера.

Прежде‏ ‎чем ‎осуществлять‏ ‎такой ‎перенос, ‎полезно ‎проверить, ‎а‏ ‎не‏ ‎используются ‎ли‏ ‎в ‎вашей‏ ‎базе ‎механизмы, ‎работающие ‎только ‎в‏ ‎корпоративной‏ ‎редакции‏ ‎SQL-сервера. ‎Если‏ ‎задействована ‎хоть‏ ‎одна ‎enterprise-функция,‏ ‎то‏ ‎перенести ‎базу‏ ‎на ‎standard-редакцию ‎не ‎получится.

А ‎в‏ ‎случае, ‎если‏ ‎вы‏ ‎твёрдо ‎вознамерились ‎перенести‏ ‎базу ‎на‏ ‎сервер ‎стандартной ‎редакции, ‎было‏ ‎бы‏ ‎здорово ‎сразу‏ ‎увидеть ‎список‏ ‎всех ‎механизмов, ‎которые ‎придётся ‎отключить.

Проверить‏ ‎использование‏ ‎корпоративных ‎механизмов,‏ ‎которые ‎помешают‏ ‎такой ‎миграции ‎можно ‎при ‎помощи‏ ‎специального‏ ‎системного‏ ‎представления ‎http://Sys.DM_DB_Persisted_SKU_Features.

Вот‏ ‎так:

В ‎редакторе‏ ‎запросов ‎SQL ‎Server ‎Management ‎Studio‏ ‎можно ‎исполнять‏ ‎не‏ ‎только ‎SQL-код!

Обратите ‎внимание‏ ‎на ‎пункт‏ ‎меню ‎студии, ‎который ‎выделен‏ ‎на‏ ‎картинке:

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

Во-первых, ‎ничего‏ ‎не ‎запуская ‎и ‎никуда ‎не‏ ‎выходя, ‎вы‏ ‎можете‏ ‎исполнять ‎команды ‎операционной‏ ‎системы ‎(на‏ ‎клиентском ‎компьютере). ‎Для ‎этого‏ ‎начните‏ ‎строку ‎с‏ ‎двух ‎восклицательных‏ ‎знаков:

Только ‎не ‎выполняйте ‎таким ‎образом‏ ‎команды,‏ ‎требующие ‎ввода‏ ‎или ‎реакции‏ ‎пользователя! ‎Например, ‎тот ‎же ‎NetSh,‏ ‎запущенный‏ ‎без‏ ‎параметров, ‎будет‏ ‎ожидать ‎дальнейших‏ ‎указаний.

Во-вторых, ‎в‏ ‎одном‏ ‎сценарии ‎теперь‏ ‎вы ‎можете ‎переключаться ‎между ‎разными‏ ‎серверами:

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

И ‎в‏ ‎четвёртых, ‎так‏ ‎же, ‎как ‎и ‎в‏ ‎самой‏ ‎утилите ‎SQLCmd,‏ ‎можно ‎работать‏ ‎с ‎входными ‎(сценарии) ‎и ‎выходными‏ ‎(результат)‏ ‎файлами:

Как ‎видите,‏ ‎в ‎именах‏ ‎файлов ‎тоже ‎можно ‎использовать ‎макропеременные.

Если‏ ‎режим‏ ‎SQLCmd‏ ‎вам ‎понравился,‏ ‎вы ‎можете‏ ‎его ‎настроить‏ ‎один‏ ‎раз ‎для‏ ‎всех ‎новых ‎окон ‎студии. ‎Зайдите‏ ‎в ‎меню‏ ‎«Сервис»‏ ‎— ‎«Параметры» ‎и‏ ‎поставьте ‎галочку:

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

Работая‏ ‎с ‎многомерными‏ ‎базами ‎данных,‏ ‎вы ‎наверняка‏ ‎наблюдали‏ ‎такой ‎эффект,‏ ‎когда ‎мера ‎не ‎делится ‎по‏ ‎некоторым ‎измерениям.

Вот‏ ‎я‏ ‎пытаюсь ‎разложить ‎показатели‏ ‎розничных ‎продаж‏ ‎по ‎реселлерам:

Но ‎ни ‎количество‏ ‎проданных‏ ‎единиц ‎товара,‏ ‎ни ‎выручка‏ ‎не ‎распадаются ‎на ‎слагаемые.

Такой ‎же‏ ‎результат‏ ‎я ‎наблюдаю,‏ ‎подключившись ‎к‏ ‎базе ‎из ‎экселя:

В ‎этом ‎нет‏ ‎ничего‏ ‎странного,‏ ‎потому ‎что‏ ‎я ‎пытаюсь‏ ‎проделать ‎некорректную‏ ‎операцию.‏ ‎Если ‎заглянуть‏ ‎в ‎структуру ‎куба, ‎то ‎мы‏ ‎увидим, ‎что‏ ‎размерность‏ ‎реселлеров ‎вообще ‎никак‏ ‎не ‎связана‏ ‎с ‎розничными ‎продажами. ‎Срезы‏ ‎по‏ ‎реселлерам ‎и‏ ‎их ‎атрибутам‏ ‎имеют ‎смысл ‎при ‎анализе ‎оптовых‏ ‎продаж.‏ ‎А ‎прикладывать‏ ‎их ‎к‏ ‎рознице ‎нет ‎никакого ‎смысла. ‎Поэтому‏ ‎сервер‏ ‎и‏ ‎не ‎может‏ ‎разложить ‎розничные‏ ‎меры ‎по‏ ‎значениям‏ ‎чисто ‎оптового‏ ‎атрибута ‎Reseller.[Business ‎Type].

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

Хорошо, ‎что‏ ‎мы‏ ‎помним, ‎как‏ ‎устроены ‎наши‏ ‎кубы ‎и ‎не ‎будем ‎пытаться‏ ‎в‏ ‎запросах‏ ‎резать ‎меры‏ ‎по ‎несуществующим‏ ‎связям. ‎Однако,‏ ‎отсутствие‏ ‎сообщения ‎об‏ ‎ошибке ‎или ‎какого-то ‎предупреждения ‎означает,‏ ‎что ‎пользователи‏ ‎время‏ ‎от ‎времени ‎будут‏ ‎пытаться ‎строить‏ ‎некорректные ‎отчёты ‎и ‎тратить‏ ‎время‏ ‎на ‎поиск‏ ‎ошибок.

В ‎конструкторе‏ ‎куба ‎есть ‎возможность ‎устранить ‎такую‏ ‎возможность.‏ ‎Это ‎флаг‏ ‎IgnoreUnrelatedDimensions в ‎настройках‏ ‎группы ‎мер:

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

Полезно‏ ‎сбросить ‎этот‏ ‎флаг ‎для ‎устранения ‎возможности ‎случайно‏ ‎или ‎в‏ ‎результате‏ ‎ошибки ‎получить ‎некорректные‏ ‎данные ‎в‏ ‎отчётах:

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

Ещё ‎удобнее‏ ‎стало ‎в‏ ‎Экселе. ‎Я ‎пытаюсь ‎некорректно‏ ‎разложить‏ ‎меры ‎по‏ ‎строкам, ‎но‏ ‎даже ‎сами ‎значения ‎атрибутов ‎в‏ ‎сводной‏ ‎таблице ‎не‏ ‎показываются:

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

Триггер ‎входа‏ ‎— ‎очень ‎полезный ‎механизм. Однако, ‎обращаться‏ ‎с ‎ним‏ ‎следует‏ ‎осторожно. ‎LOGON-триггеры ‎срабатывают‏ ‎ещё ‎до‏ ‎того, ‎как ‎пользователь ‎начнёт‏ ‎работу‏ ‎с ‎SQL-сервером,‏ ‎поэтому ‎если‏ ‎такой ‎триггер ‎написан ‎неаккуратно ‎или‏ ‎срабатывает‏ ‎с ‎ошибкой,‏ ‎пользователи, ‎включая‏ ‎администраторов, ‎просто ‎не ‎смогут ‎подключиться‏ ‎к‏ ‎серверу.

Вот‏ ‎пример ‎триггера‏ ‎входа, ‎который‏ ‎умышленно ‎написан‏ ‎так,‏ ‎чтобы ‎сбрасывать‏ ‎все ‎попытки ‎подключения ‎к ‎серверу:

Теперь‏ ‎при ‎попытке‏ ‎установить‏ ‎соединение ‎с ‎SQL-сервером,‏ ‎получаем ‎сообщение‏ ‎об ‎ошибке, ‎случившейся ‎во‏ ‎время‏ ‎выполнения ‎триггера‏ ‎входа:

Если ‎вы‏ ‎попали ‎в ‎такую ‎ситуацию, ‎придётся‏ ‎воспользоваться‏ ‎специальной ‎лазейкой,‏ ‎которая ‎позволяет‏ ‎администратору ‎подключиться ‎к ‎SQL-серверу ‎в‏ ‎обход‏ ‎LOGON-триггеров.‏ ‎Такое ‎«сервисное»‏ ‎подключение ‎доступно‏ ‎только ‎членам‏ ‎серверной‏ ‎роли ‎SysAdmin.‏ ‎Обратите ‎внимание, ‎что ‎возможность ‎удалённого‏ ‎сервисного ‎подключения‏ ‎может‏ ‎быть ‎отключена ‎в‏ ‎настройках, ‎тогда‏ ‎придётся ‎работать ‎прямо ‎на‏ ‎сервере.

В‏ ‎утилите ‎SQLCmd‏ ‎воспользоваться ‎сервисным‏ ‎подключением ‎можно, ‎указав ‎параметр ‎-A:

А‏ ‎при‏ ‎подключении ‎из‏ ‎студии ‎используйте‏ ‎префикс ‎ADMIN: перед ‎именем ‎сервера. ‎Вот‏ ‎так:

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

В ‎интеграционных‏ ‎службах ‎SQL-сервера ‎не ‎хватает ‎полезной‏ ‎трансформации ‎Row_Number.‏ ‎И‏ ‎встроенной ‎функции, ‎через‏ ‎которую ‎можно‏ ‎было ‎бы ‎реализовать ‎эту‏ ‎трансформацию,‏ ‎тоже ‎нет.‏ ‎Так ‎что,‏ ‎засучив ‎рукава, ‎придётся ‎взяться ‎за‏ ‎скрипты.

Решение‏ ‎очень ‎простое‏ ‎и ‎гибкое‏ ‎— ‎у ‎вас ‎не ‎будет‏ ‎с‏ ‎ним‏ ‎никаких ‎проблем.‏ ‎Готовый ‎пакет‏ ‎прикреплён ‎(см.‏ ‎ниже).

В‏ ‎пакете ‎демонстрируется‏ ‎два ‎роунамбера:

1. Простая ‎расстановка ‎номеров
2. Расстановка ‎номеров‏ ‎с ‎секционированием

Ниже‏ ‎пошаговое‏ ‎решение.

Сначала ‎сортируем ‎строки‏ ‎в ‎том‏ ‎порядке, ‎в ‎каком ‎надо‏ ‎генерировать‏ ‎номера:

Отсортированный ‎поток‏ ‎заводим ‎в‏ ‎Script ‎Transformation ‎и ‎создаём ‎в‏ ‎нём‏ ‎дополнительную ‎колонку‏ ‎для ‎роунамбера:

Далее‏ ‎открываем ‎редактор ‎кода:

Далее ‎добавляем ‎в‏ ‎сгенерированный‏ ‎шаблон‏ ‎кода ‎пару‏ ‎строк:

Всё.

Ранжирование ‎с‏ ‎секционированием ‎чуть‏ ‎посложнее.‏ ‎Надо ‎чуть‏ ‎больше ‎кода ‎написать ‎— ‎вот‏ ‎так:

И ‎поле‏ ‎с‏ ‎секцией ‎надо ‎подать‏ ‎на ‎вход‏ ‎скрипта. ‎Вот ‎так:

SQL ‎Server‏ ‎умеет ‎работать ‎c ‎временными ‎таблицами.‏ ‎Названия ‎у‏ ‎таких‏ ‎таблиц ‎начинаются ‎с‏ ‎одной ‎или‏ ‎с ‎двух ‎решёток ‎(#).‏ ‎Временные‏ ‎таблицы, ‎очевидно,‏ ‎придуманы ‎для‏ ‎того, ‎чтобы ‎хранить ‎в ‎них‏ ‎данные‏ ‎временно. ‎Например,‏ ‎для ‎сохранения‏ ‎промежуточных ‎результатов ‎каких-либо ‎вычислений. ‎Или‏ ‎для‏ ‎передачи‏ ‎каких-то ‎данных‏ ‎от ‎одного‏ ‎процесса ‎другому.

Иногда‏ ‎требуется‏ ‎проверить, ‎была‏ ‎ли ‎уже ‎создана ‎определённая ‎временная‏ ‎таблица ‎или‏ ‎нет.‏ ‎Как ‎это ‎правильно‏ ‎сделать?

Если ‎речь‏ ‎идёт ‎о ‎глобальной ‎временной‏ ‎таблице,‏ ‎то ‎проверить‏ ‎её ‎наличие‏ ‎можно ‎традиционным ‎способом ‎— ‎заглянув‏ ‎в‏ ‎список ‎таблиц.‏ ‎Только ‎не‏ ‎забудьте ‎сделать ‎поправку ‎на ‎то,‏ ‎что‏ ‎все‏ ‎временные ‎таблицы‏ ‎— ‎и‏ ‎глобальные ‎и‏ ‎локальные‏ ‎— ‎создаются‏ ‎не ‎в ‎текущей ‎пользовательской ‎базе‏ ‎данных, ‎а‏ ‎в‏ ‎системной ‎базе ‎TempDB.

Посмотрите‏ ‎на ‎пример.‏ ‎Один ‎процесс ‎создал ‎глобальную‏ ‎временную‏ ‎таблицу. ‎Теперь‏ ‎этот ‎и‏ ‎все ‎другие ‎процессы ‎могут ‎обращаться‏ ‎к‏ ‎ней, ‎как‏ ‎к ‎обыкновенной‏ ‎таблице. ‎В ‎том ‎числе, ‎они‏ ‎увидят‏ ‎её‏ ‎в ‎системных‏ ‎каталогах ‎sys.Tables и‏ ‎Information_Schema.Tables.

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

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

Если‏ ‎посмотреть ‎внимательнее,‏ ‎то ‎можно ‎заметить, ‎что ‎к‏ ‎названиям‏ ‎таблиц ‎сервер‏ ‎дописывает ‎уникальное‏ ‎окончание, ‎по ‎которому ‎он ‎и‏ ‎определяет‏ ‎чья‏ ‎это ‎таблица:

Возиться‏ ‎с ‎этими‏ ‎окончаниями ‎в‏ ‎SQL-коде‏ ‎не ‎хотелось‏ ‎бы. ‎И ‎не ‎нужно. ‎Потому‏ ‎что ‎у‏ ‎нас‏ ‎есть ‎функция ‎Object_ID, которая‏ ‎автоматически ‎выбирает‏ ‎нужную ‎таблицу:

Если ‎объект ‎с‏ ‎заданным‏ ‎названиям ‎существует,‏ ‎то ‎функция‏ ‎Object_ID вернёт ‎его ‎идентификатор, ‎а ‎если‏ ‎нет‏ ‎— ‎NULL.‏ ‎Таким ‎образом‏ ‎мы ‎сможем ‎удобно ‎и ‎надёжно‏ ‎проверить‏ ‎наличие‏ ‎даже ‎такого‏ ‎необычного ‎объекта,‏ ‎как ‎локальная‏ ‎временная‏ ‎таблица:

У ‎этой‏ ‎функции ‎есть ‎необязательный ‎второй ‎параметр,‏ ‎который ‎можно‏ ‎использовать‏ ‎для ‎указания ‎конкретного‏ ‎типа ‎объекта.‏ ‎«U» ‎означает, ‎что ‎мы‏ ‎ищем‏ ‎именно ‎таблицу.

Если ‎вы‏ ‎озабочены ‎защитой ‎данных, ‎то ‎в‏ ‎вашем ‎распоряжении‏ ‎большой‏ ‎набор ‎механизмов, ‎предоставляемых‏ ‎СУБД. ‎Однако,‏ ‎большинство ‎из ‎них ‎работают‏ ‎только‏ ‎тогда, ‎когда‏ ‎данные ‎находятся‏ ‎в ‎работающей ‎базе. ‎А ‎что‏ ‎происходит,‏ ‎когда ‎данные‏ ‎покидают ‎SQL-сервер,‏ ‎например, ‎после ‎резервного ‎копирования? ‎Если‏ ‎злоумышленник‏ ‎украл‏ ‎резервную ‎копию‏ ‎вашей ‎базы‏ ‎данных, ‎то,‏ ‎восстановив‏ ‎её ‎на‏ ‎своём ‎сервере, ‎он ‎увидит ‎все‏ ‎ваши ‎данные!

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

SQL-сервер‏ ‎шифрует‏ ‎резервную ‎копию‏ ‎ключом, ‎который ‎сохраняется ‎вместе ‎с‏ ‎самой ‎резервной‏ ‎копией.‏ ‎Поэтому ‎этот ‎ключ‏ ‎тоже ‎нужно‏ ‎зашифровать. ‎Это ‎делается ‎при‏ ‎помощи‏ ‎сертификата, ‎который‏ ‎хранится ‎отдельно‏ ‎от ‎базы ‎и ‎не ‎попадает‏ ‎в‏ ‎резервную ‎копию.

Сам‏ ‎этот ‎сертификат‏ ‎защищается ‎мастер-ключом. ‎Если ‎вы ‎раньше‏ ‎не‏ ‎использовали‏ ‎криптографию ‎в‏ ‎SQL-сервере, ‎то,‏ ‎возможно, ‎мастер-ключа‏ ‎у‏ ‎вас ‎тоже‏ ‎нет ‎и ‎его ‎придётся ‎создать.‏ ‎Вот ‎так:

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

Всё, ‎теперь‏ ‎можно‏ ‎создавать‏ ‎зашифрованные ‎резервные‏ ‎копии. ‎Если‏ ‎вы ‎делаете‏ ‎это‏ ‎командой ‎BACKUP‏ ‎DATABASE, ‎то ‎добавьте ‎указание ‎WITH‏ ‎ENCRYPTION:

А ‎если‏ ‎пользуетесь‏ ‎интерфейсом, ‎то ‎загляните‏ ‎на ‎последнюю‏ ‎закладку:

Посмотрим ‎на ‎результаты. ‎Вот‏ ‎что‏ ‎получит ‎злоумышленник,‏ ‎украв ‎не‏ ‎защищённую ‎резервную ‎копию:

Как ‎видите, ‎всё‏ ‎содержимое‏ ‎легко ‎читается.‏ ‎А ‎вот‏ ‎так ‎выглядит ‎зашифрованная ‎копия:

При ‎планировании‏ ‎защиты‏ ‎резервных‏ ‎копий ‎учтите,‏ ‎что ‎такое‏ ‎шифрование ‎имеется‏ ‎не‏ ‎во ‎всех‏ ‎версиях ‎SQL-сервера. ‎Если ‎вы ‎пользуетесь‏ ‎версией ‎младше‏ ‎2014-й,‏ ‎то ‎придётся ‎либо‏ ‎шифровать ‎саму‏ ‎базу, ‎либо ‎пользоваться ‎дополнительными‏ ‎инструментами.

Начиная ‎с‏ ‎2008-й ‎версии, ‎администраторы ‎Windows ‎уже‏ ‎не ‎являются‏ ‎по‏ ‎умолчанию ‎администраторами ‎SQL-сервера.‏ ‎Это ‎означает,‏ ‎что ‎может ‎возникнуть ‎ситуация,‏ ‎при‏ ‎которой ‎нет‏ ‎ни ‎одной‏ ‎учётной ‎записи, ‎способной ‎управлять ‎SQL-сервером.

Например,‏ ‎вы‏ ‎назначили ‎какую-то‏ ‎учётную ‎запись‏ ‎Windows ‎администратором ‎SQL-сервера, ‎а ‎потом‏ ‎эту‏ ‎учётную‏ ‎запись ‎удалили.‏ ‎Или ‎забыли‏ ‎пароли ‎ко‏ ‎всем‏ ‎учётным ‎записям,‏ ‎которые ‎исполняли ‎роль ‎sysadmin.

Это ‎не‏ ‎просто ‎теоретическая‏ ‎возможность!‏ ‎На ‎практике ‎я‏ ‎периодически ‎сталкиваюсь‏ ‎с ‎SQL-серверами, ‎над ‎которыми‏ ‎потерян‏ ‎контроль. ‎То‏ ‎есть ‎сам‏ ‎по ‎себе ‎SQL-сервер ‎работает ‎нормально,‏ ‎все‏ ‎базы ‎данных‏ ‎функционируют, ‎но‏ ‎никто ‎не ‎может ‎этим ‎сервером‏ ‎управлять.

Для‏ ‎выхода‏ ‎из ‎такой‏ ‎ситуации ‎Микрософт‏ ‎оставил ‎специальную‏ ‎лазейку.

Найдите‏ ‎в ‎списке‏ ‎windows-служб ‎SQL ‎Server ‎и ‎откройте‏ ‎окно ‎со‏ ‎свойствами:

Остановите‏ ‎службу:

Когда ‎служба ‎остановится,‏ ‎на ‎той‏ ‎же ‎закладке ‎окна ‎свойств‏ ‎службы‏ ‎откроется ‎возможность‏ ‎задать ‎дополнительные‏ ‎параметры ‎здя ‎запуска ‎службы. ‎Введите‏ ‎в‏ ‎это ‎поле‏ ‎параметр ‎«-m» и‏ ‎снова ‎запустите ‎службу.

Запущенный ‎таким ‎образом‏ ‎SQL-сервер‏ ‎позволит‏ ‎подключиться ‎локальному‏ ‎администратору ‎независимо‏ ‎от ‎настроек‏ ‎аутентификации.‏ ‎Вы ‎можете‏ ‎от ‎имени ‎локального ‎администратора ‎запустить‏ ‎Management ‎Studio‏ ‎или‏ ‎SQLCmd ‎и ‎восстановить‏ ‎административные ‎учётные‏ ‎записи. ‎После ‎этого ‎перезапустите‏ ‎службу‏ ‎в ‎обычном‏ ‎режиме ‎(без‏ ‎параметра ‎«-m»)

Администраторам ‎часто‏ ‎бывает ‎необходимо ‎перебрать ‎все ‎таблицы‏ ‎базы ‎данных,‏ ‎чтобы‏ ‎над ‎каждой ‎таблицей‏ ‎произвести ‎какое-то‏ ‎действие. ‎Например, ‎перестроить ‎индексы.‏ ‎

Традиционно‏ ‎для ‎такого‏ ‎перебора ‎можно‏ ‎использовать ‎курсор. ‎Но ‎есть ‎способ‏ ‎проще‏ ‎- ‎процедура‏ ‎sp_MSForEachTable. Ей ‎можно‏ ‎передать ‎текст ‎команды ‎или ‎запроса,‏ ‎который‏ ‎будет‏ ‎выполнен ‎для‏ ‎каждой ‎таблицы‏ ‎в ‎базе.‏ ‎Команда,‏ ‎разумеется, ‎параметризуется‏ ‎- ‎вместо ‎вопросительного ‎знака ‎будет‏ ‎подставлено ‎название‏ ‎таблицы.

Посмотрите‏ ‎на ‎сигнатуру ‎этой‏ ‎процедуры:

За ‎один‏ ‎вызов ‎вы ‎можете ‎передать‏ ‎ей‏ ‎три ‎команды,‏ ‎которые ‎будут‏ ‎выполнены ‎для ‎каждой ‎таблицы, ‎плюс‏ ‎начальное‏ ‎и ‎конечное‏ ‎действия ‎для‏ ‎всего ‎пакета ‎команд, ‎а ‎также‏ ‎указать‏ ‎условие‏ ‎включения ‎таблицы‏ ‎в ‎перебор.‏ ‎Параметр ‎@ReplaceChar предназначен‏ ‎для‏ ‎запросов, ‎в‏ ‎которых ‎не ‎получается ‎использовать ‎вопросительный‏ ‎знак ‎для‏ ‎параметризации.

Каждая‏ ‎из ‎трёх ‎команд‏ ‎может ‎содержать‏ ‎больше ‎одного ‎SQL-запроса. ‎При‏ ‎написании‏ ‎фильтра ‎@WhereAnd учтите,‏ ‎что ‎ваша‏ ‎строка ‎будет ‎встроена ‎внутри ‎процедуры‏ ‎в‏ ‎более ‎сложный‏ ‎запрос ‎к‏ ‎системным ‎таблицам, ‎поэтому ‎используйте ‎для‏ ‎фильтрации‏ ‎столбцы‏ ‎из ‎SysObjects. Например:

Имеется‏ ‎аналогичная ‎процедура‏ ‎для ‎перебора‏ ‎всех‏ ‎баз ‎данных‏ ‎на ‎сервере ‎- ‎sp_MSForEachDB:

С ‎её‏ ‎помощью ‎вы‏ ‎сможете‏ ‎выполнить ‎однотипный ‎набор‏ ‎действий ‎над‏ ‎каждой ‎базой ‎на ‎сервере:

При ‎работе‏ ‎с ‎данными ‎часто ‎приходится ‎использовать‏ ‎«строки ‎соединения‏ ‎с‏ ‎источником ‎данных» ‎(connection‏ ‎string). ‎Синтаксис‏ ‎этих ‎строк ‎и ‎набор‏ ‎параметров,‏ ‎которые ‎можно‏ ‎в ‎них‏ ‎использовать, ‎зависит ‎от ‎типа ‎хранилища‏ ‎и‏ ‎версии ‎драйвера.‏ ‎Держать ‎всё‏ ‎это ‎в ‎уме ‎проблематично.

К ‎счастью,‏ ‎есть‏ ‎способ‏ ‎быстро ‎и‏ ‎без ‎ошибок‏ ‎сконструировать ‎такую‏ ‎строку.‏ ‎Редактор ‎для‏ ‎работы ‎со ‎строками ‎соединения ‎уже‏ ‎имеется ‎в‏ ‎Windows.

Создайте‏ ‎пустой ‎файл ‎с‏ ‎расширением ‎«UDL»:

По‏ ‎двойному ‎щелчку ‎на ‎UDL-файле‏ ‎по‏ ‎умолчанию ‎открывается‏ ‎конструктор ‎соединений.‏ ‎Он-то ‎нам ‎и ‎нужен! ‎Здесь‏ ‎мы‏ ‎удобным ‎образом‏ ‎настраиваем ‎все‏ ‎параметры ‎подключения, ‎причём ‎этот ‎редактор‏ ‎использует‏ ‎свой‏ ‎вариант ‎интерфейса‏ ‎для ‎каждого‏ ‎драйвера.

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

UDL ‎—‏ ‎это‏ ‎обыкновенный ‎текстовый‏ ‎формат. ‎Откройте‏ ‎теперь ‎этот ‎файл ‎блокнотом…

…и ‎получите‏ ‎готовую‏ ‎строку ‎соединения:

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

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

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

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

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

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

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

Вот ‎так:

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

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

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

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

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

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

Назначение ‎таких‏ ‎«упрощённых» ‎компонентов ‎— ‎помочь ‎читателю‏ ‎быстрее‏ ‎сориентироваться ‎в‏ ‎отчёте ‎и‏ ‎сфокусироваться ‎на ‎самом ‎важном. ‎Однако,‏ ‎имеется‏ ‎один‏ ‎подводный ‎камень,‏ ‎который ‎запросто‏ ‎может ‎всё‏ ‎испортить.

Посмотрите‏ ‎как ‎в‏ ‎течение ‎года ‎изменялась ‎выручка ‎от‏ ‎продаж ‎в‏ ‎разных‏ ‎странах:

Благодаря ‎спарклайнам ‎у‏ ‎меня ‎получился‏ ‎очень ‎компактный ‎и ‎наглядный‏ ‎отчёт.

А‏ ‎как ‎вы‏ ‎думаете, ‎где‏ ‎выручка ‎была ‎больше: ‎в ‎Канаде‏ ‎или‏ ‎в ‎США?

Кажется,‏ ‎что ‎разница‏ ‎в ‎объёмах ‎продаж ‎по ‎этим‏ ‎странам‏ ‎различается‏ ‎не ‎существенно.‏ ‎Хотя, ‎конечно,‏ ‎надо ‎бы‏ ‎посмотреть‏ ‎на ‎числовые‏ ‎показатели, ‎чтобы ‎сказать ‎наверняка. ‎Стоп!‏ ‎Ведь ‎мы‏ ‎и‏ ‎заморочились ‎этими ‎спарклайнами‏ ‎как ‎раз‏ ‎ради ‎того, ‎чтобы ‎сделать‏ ‎отчёт‏ ‎более ‎компактным‏ ‎и ‎не‏ ‎перегружать ‎его ‎числами!

Давайте ‎посмотрим ‎на‏ ‎внутренности‏ ‎нашего ‎спарклайна:

Зайдя‏ ‎в ‎настройки‏ ‎вертикальной ‎оси, ‎мы ‎обнаружим, ‎что‏ ‎синхронизация‏ ‎шкалы‏ ‎между ‎спарклайнами‏ ‎по ‎умолчанию‏ ‎отключена. ‎А‏ ‎это‏ ‎означает, ‎что‏ ‎верхняя ‎точка ‎на ‎каждом ‎графике‏ ‎означает ‎совершенно‏ ‎разные‏ ‎значения! ‎Получается, ‎такой‏ ‎спарклайн ‎нельзя‏ ‎сравнивать ‎с ‎соседними ‎графиками,‏ ‎но‏ ‎это ‎странно‏ ‎— ‎ведь‏ ‎я ‎как ‎раз ‎хотел, ‎чтобы‏ ‎у‏ ‎читателя ‎с‏ ‎первого ‎взгляда‏ ‎сложилась ‎картина ‎соотношения ‎показателей ‎продаж‏ ‎по‏ ‎разным‏ ‎странам.

А ‎теперь‏ ‎давайте ‎синхронизируем‏ ‎шкалу ‎по‏ ‎вертикальной‏ ‎оси:

Совсем ‎другое‏ ‎дело! ‎Теперь ‎сразу ‎понятно ‎как‏ ‎идут ‎дела‏ ‎в‏ ‎США, ‎а ‎как‏ ‎в ‎Канаде:

Если‏ ‎используете ‎в ‎отчётах ‎гистограмму‏ ‎(databar),‏ ‎не ‎забудьте‏ ‎и ‎для‏ ‎неё ‎также ‎проверить ‎настройки ‎выравнивания‏ ‎шкалы.‏ ‎В ‎большинстве‏ ‎случаев ‎шкалу‏ ‎у ‎встраиваемых ‎графиков ‎лучше ‎синхронизировать.

В ‎реляционной‏ ‎базе ‎данных ‎нельзя ‎назначать ‎права‏ ‎на ‎строки.‏ ‎Поэтому,‏ ‎для ‎защиты ‎отдельных‏ ‎строк ‎часто‏ ‎используют ‎представление ‎(VIEW) ‎с‏ ‎привязкой‏ ‎к ‎идентификатору‏ ‎текущего ‎пользователя.‏ ‎Это, ‎пожалуй, ‎самый ‎распространённый, ‎но‏ ‎далеко‏ ‎не ‎единственный‏ ‎сценарий ‎использования‏ ‎в ‎коде ‎обращений ‎к ‎текущему‏ ‎контексту‏ ‎безопасности.

Как‏ ‎отлаживать ‎такой‏ ‎код? ‎Можно‏ ‎открыть ‎несколько‏ ‎соединений‏ ‎с ‎сервером‏ ‎от ‎имени ‎разных ‎пользователей ‎и‏ ‎смотреть ‎на‏ ‎результаты‏ ‎одного ‎и ‎того‏ ‎же ‎запроса‏ ‎в ‎разных ‎окнах. ‎Но‏ ‎если‏ ‎это ‎приходится‏ ‎делать ‎по‏ ‎многу ‎раз, ‎переключение ‎между ‎соединениями‏ ‎начинает‏ ‎утомлять ‎и‏ ‎отвлекать.

Альтернативный ‎вариант‏ ‎— ‎создайте ‎для ‎пользователя ‎свой‏ ‎собственный‏ ‎связанный‏ ‎сервер ‎(Linked‏ ‎Server), ‎который‏ ‎ссылается ‎сам‏ ‎на‏ ‎себя. ‎Вот‏ ‎так:

И ‎в ‎настройках ‎этого ‎связанного‏ ‎сервера ‎укажите‏ ‎контекст‏ ‎безопасности, ‎в ‎котором‏ ‎хотите ‎провести‏ ‎отладку:

Теперь ‎можно ‎смотреть ‎на‏ ‎данные‏ ‎глазами ‎разных‏ ‎пользователей ‎через‏ ‎одно ‎подключение ‎и ‎даже ‎из‏ ‎одного‏ ‎окна:

Описанный ‎приём‏ ‎— ‎не‏ ‎единственный. ‎Но ‎он ‎самый ‎простой‏ ‎и‏ ‎не‏ ‎требует ‎вмешательства‏ ‎в ‎саму‏ ‎отлаживаемую ‎базу‏ ‎данных.

Для ‎администратора‏ ‎баз ‎данных ‎резервное ‎копирование ‎—‏ ‎вещь ‎обязательная.‏ ‎Не‏ ‎важно ‎какими ‎базами‏ ‎вы ‎управляете,‏ ‎большими ‎или ‎маленькими, ‎делать‏ ‎бэкапы‏ ‎необходимо. ‎Разработчики‏ ‎СУБД ‎это‏ ‎понимают ‎и ‎стараются ‎сделать ‎процедуру‏ ‎резервного‏ ‎копирования ‎максимально‏ ‎простой ‎и‏ ‎понятной ‎для ‎администратора. ‎Но ‎есть‏ ‎в‏ ‎резервном‏ ‎копировании ‎баз‏ ‎данных ‎один‏ ‎момент, ‎в‏ ‎котором‏ ‎СУБД ‎вам‏ ‎помочь ‎не ‎сможет…

Современные ‎приложения ‎всё‏ ‎чаще ‎проектируются‏ ‎так,‏ ‎чтобы ‎работать ‎с‏ ‎данными ‎не‏ ‎в ‎одной ‎базе ‎данных,‏ ‎а‏ ‎в ‎нескольких.‏ ‎Это ‎действительно‏ ‎полезно ‎— ‎таким ‎образом ‎можно‏ ‎повысить‏ ‎масштабируемость, ‎производительность,‏ ‎надёжность ‎и‏ ‎управляемость ‎приложения. ‎Но, ‎как ‎всегда‏ ‎бывает‏ ‎в‏ ‎инженерном ‎деле,‏ ‎это ‎не‏ ‎обходится ‎без‏ ‎сопутствующих‏ ‎недостатков. ‎Главный‏ ‎из ‎которых ‎— ‎усложнение ‎системы.‏ ‎Возможности ‎системы‏ ‎растут‏ ‎вместе ‎с ‎её‏ ‎сложностью, ‎что‏ ‎повышает ‎требования ‎к ‎обслуживанию‏ ‎многобазовых‏ ‎приложений. ‎Одна‏ ‎из ‎проблем,‏ ‎которую ‎придётся ‎решать ‎при ‎эксплуатации‏ ‎таких‏ ‎приложений ‎—‏ ‎создание ‎(и‏ ‎последующее ‎восстановление) ‎согласованных ‎резервных ‎копий‏ ‎нескольких‏ ‎баз‏ ‎данных.

Перед ‎вами‏ ‎аналог ‎приложения,‏ ‎работающего ‎с‏ ‎несколькими‏ ‎БД. ‎Оно‏ ‎постоянно ‎обрабатывает ‎данные ‎в ‎базах,‏ ‎согласовывая ‎изменения‏ ‎при‏ ‎помощи ‎транзакций.

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

SQL ‎Server ‎позволяет ‎восстанавливать‏ ‎базу‏ ‎в ‎состоянии‏ ‎не ‎только‏ ‎на ‎момент ‎создания ‎резервной ‎копии,‏ ‎но‏ ‎и ‎на‏ ‎любой ‎произвольный‏ ‎момент ‎времени. ‎Чтобы ‎добиться ‎максимальной‏ ‎точности,‏ ‎следует‏ ‎восстанавливать ‎состояние‏ ‎базы ‎на‏ ‎момент ‎фиксации‏ ‎определённой‏ ‎транзакции, ‎а‏ ‎саму ‎транзакцию ‎провести ‎так, ‎чтобы‏ ‎она ‎внесла‏ ‎изменения‏ ‎сразу ‎во ‎все‏ ‎базы.

Можно, ‎к‏ ‎примеру, ‎создать ‎в ‎каждой‏ ‎базе‏ ‎табличку ‎и‏ ‎в ‎транзакции‏ ‎добавлять ‎в ‎неё ‎строку. ‎А‏ ‎чтобы‏ ‎эту ‎транзакцию‏ ‎при ‎восстановлении‏ ‎можно ‎было ‎отличить ‎от ‎остальных,‏ ‎снабдим‏ ‎её‏ ‎меткой.

Теперь, ‎когда‏ ‎эта ‎метка‏ ‎сохранена ‎в‏ ‎журнале‏ ‎транзакций, ‎можно‏ ‎создать ‎резервную ‎копию ‎журнала. ‎А‏ ‎при ‎восстановлении‏ ‎не‏ ‎забудьте ‎указать ‎параметр‏ ‎STOPATMARK ‎(или‏ ‎STOPBEFOREMARK), ‎чтобы ‎все ‎базы‏ ‎восстановились‏ ‎на ‎момент‏ ‎фиксации ‎нашей‏ ‎контрольной ‎транзакции.

Проверяем ‎базы ‎после ‎восстановления‏ ‎из‏ ‎резервной ‎копии:

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

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

Хорошо ‎быть‏ ‎администратором. ‎Подключаемся ‎к ‎серверу, ‎разворачиваем‏ ‎список‏ ‎баз‏ ‎данных ‎и‏ ‎до ‎каждой‏ ‎базы ‎нам‏ ‎есть‏ ‎дело!

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

Вот‏ ‎один‏ ‎из ‎наших‏ ‎учебных ‎серверов.‏ ‎У ‎каждого ‎пользователя ‎есть ‎своя‏ ‎база‏ ‎данных,‏ ‎в ‎которой‏ ‎он ‎—‏ ‎полноправный ‎хозяин.‏ ‎Есть‏ ‎также ‎несколько‏ ‎баз, ‎в ‎которые ‎пользователь ‎может‏ ‎заглянуть ‎с‏ ‎ограниченными‏ ‎правами. ‎А ‎к‏ ‎большинству ‎баз‏ ‎данных ‎у ‎пользователя ‎вообще‏ ‎нет‏ ‎никакого ‎доступа.‏ ‎Но ‎все‏ ‎эти ‎факторы ‎не ‎принимаются ‎во‏ ‎внимание‏ ‎SQL-сервером, ‎мы‏ ‎всё ‎равно‏ ‎видим ‎весь ‎список:

Давайте ‎посмотрим, ‎что‏ ‎тут‏ ‎можно‏ ‎придумать.

Пользователь ‎«Фёдор‏ ‎Самородов», ‎подключившись,‏ ‎видит ‎список‏ ‎баз‏ ‎данных ‎благодаря‏ ‎тому, ‎что ‎у ‎роли ‎public есть‏ ‎разрешение ‎VIEW‏ ‎ANY‏ ‎DATABASE.

Вы ‎можете ‎это‏ ‎разрешение ‎отозвать‏ ‎(тогда ‎это ‎изменит ‎картину‏ ‎для‏ ‎всех ‎пользователей),‏ ‎либо ‎запретить‏ ‎индивидуально ‎для ‎некоторых ‎пользователей. ‎Попробуем‏ ‎запретить‏ ‎просмотр ‎этих‏ ‎метаданных ‎для‏ ‎нашего ‎пользователя:

Это ‎сработало! ‎Правда, ‎стало‏ ‎ещё‏ ‎хуже‏ ‎— ‎теперь‏ ‎мы ‎не‏ ‎видим ‎вообще‏ ‎никаких‏ ‎баз, ‎кроме‏ ‎master и ‎tempdb.

Заметьте, ‎что ‎на ‎сам‏ ‎доступ ‎к‏ ‎базам‏ ‎это ‎не ‎повлияло.‏ ‎Если ‎мы‏ ‎знаем ‎название ‎нужной ‎базы‏ ‎данных,‏ ‎то ‎сможем‏ ‎переключиться ‎в‏ ‎её ‎контекст:

К ‎сожалению, ‎добиться ‎желаемой‏ ‎гибкости‏ ‎мы ‎не‏ ‎сможем. ‎В‏ ‎общем ‎случае ‎такая ‎задача ‎не‏ ‎решаема.‏ ‎SQL‏ ‎Server ‎покажет‏ ‎нам ‎только‏ ‎те ‎базы,‏ ‎владельцами‏ ‎которых ‎мы‏ ‎являемся. ‎Причём, ‎учитывается ‎именно ‎фактор‏ ‎владения, ‎членства‏ ‎в‏ ‎роли ‎db_owner недостаточно.

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

Сделаем ‎пользователя‏ ‎владельцем ‎одной ‎из ‎баз:

А ‎теперь‏ ‎обновим‏ ‎список:

Или‏ ‎выполним ‎аналогичный‏ ‎запрос ‎к‏ ‎sys.Databases:

Вот ‎это‏ ‎уже‏ ‎похоже ‎на‏ ‎то, ‎что ‎мы ‎задумали, ‎и‏ ‎это ‎—‏ ‎лучшее,‏ ‎чего ‎мы ‎можем‏ ‎добиться ‎в‏ ‎текущих ‎версиях ‎SQL-сервера.

Существует ‎механизм,‏ ‎позволяющий ‎из ‎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»‏ ‎создаёт‏ ‎для ‎разработчика‏ ‎дополнительную ‎степень‏ ‎свободы.