где запросы могут быстро становиться сложными и громоздкими, крайне важно иметь инструменты, которые помогают поддерживать порядок и эффективность. MDX, язык многомерных выражений, предоставляет такой мощный инструмент — именованные наборы (Named Sets). Это не просто синтаксический сахар; это фундаментальная концепция, которая значительно повышает читаемость ваших запросов, упрощает их отладку и, что не менее важно, способствует повторному использованию кода.

Представьте, что вы аналитик в AdventureWorks, и вам регулярно требуется анализировать продажи для определенной группы высокодоходных клиентов, или для конкретного набора продуктов, которые являются вашими бестселлерами. Каждый раз вручную перечислять всех этих клиентов или продукты в каждом запросе не только утомительно, но и чревато ошибками. Более того, если состав этой группы изменится, вам придется обновлять каждый запрос, что является неэффективным и рискованным. Именованные наборы решают эту проблему, позволяя вам дать имя сложной коллекции элементов и использовать это имя в любом месте запроса.

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

Именованный набор — это коллекция кортежей или членов, которой присваивается уникальное имя. Это имя затем можно использовать в MDX-запросе так же, как вы использовали бы любое другое измерение, иерархию или меру. По сути, вы создаете "переменную" для набора данных.

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

1.     В скрипте MDX-куба (MDX Script): Именованные наборы, определенные в скрипте куба (обычно в SQL Server Management Studio или Visual Studio для Analysis Services), становятся частью метаданных куба. Они доступны для всех пользователей, подключающихся к этому кубу, и могут быть использованы в любом MDX-запросе или клиентском приложении (например, Excel PivotTable). Это идеальный вариант для часто используемых, стандартных бизнес-групп.
2.     Внутри MDX-запроса (WITH SET Clause): Именованные наборы, определенные в секции WITH запроса, являются временными. Они существуют только на время выполнения этого конкретного запроса и не сохраняются в кубе. Этот подход идеально подходит для ad-hoc анализа, для наборов, которые нужны только в одном отчете, или для прототипирования.

Мы сосредоточимся на втором варианте (WITH SET), так как он наиболее гибок для аналитиков, пишущих запросы.
Базовый синтаксис WITH SET:

WITH SET Set_Name AS
 Set_Expression
SELECT ...


●       Set_Name: Имя, которое вы присваиваете набору. Это имя должно быть уникальным в пределах запроса и не должно конфликтовать с существующими именами измерений, иерархий или мер.
●       Set_Expression: Выражение MDX, которое определяет набор. Это может быть любая комбинация членов, функций (таких как Filter(), CrossJoin(), TopCount(), Order()) и других наборов.

Одна из самых очевидных выгод именованных наборов — это значительное улучшение читаемости сложных запросов. Вместо того чтобы встраивать длинные и запутанные определения наборов непосредственно в оси SELECT, вы можете вынести их в секцию WITH, присвоить им осмысленные имена и затем использовать эти имена.

Пример 1: Продажи AdventureWorks для "Топ-10 Продуктов по Доходу"
Представьте, что вам нужно получить продажи для 10 самых продаваемых продуктов AdventureWorks за 2007 год. Без именованного набора запрос выглядел бы так:

SELECT
 [Measures].[Sales Amount] ON COLUMNS,
 TopCount(
   [Product].[Product].[Product Name].Members,
   10,
   ([Measures].[Sales Amount], [Date].[Calendar Year].&[2007])
 ) ON ROWS
FROM
 [Adventure Works]
WHERE
 [Date].[Calendar Year].&[2007]


Этот запрос относительно прост. Но что, если "Топ-10 Продуктов по Доходу" используется в нескольких местах запроса или в других запросах? Или если критерий "топ-10" станет "топ-15"?

Теперь давайте используем именованный набор:

WITH SET [Top 10 Products 2007] AS
 TopCount(
   [Product].[Product].[Product Name].Members,
   10,
   ([Measures].[Sales Amount], [Date].[Calendar Year].&[2007])
 )
SELECT
 [Measures].[Sales Amount] ON COLUMNS,
 [Top 10 Products 2007] ON ROWS
FROM
 [Adventure Works]
WHERE
 [Date].[Calendar Year].&[2007]


Разбор:
●       Мы определили именованный набор [Top 10 Products 2007] в секции WITH SET.
●       Выражение для этого набора (TopCount(...)) стало намного более читаемым, так как оно вынесено и названо.
●       На оси ROWS мы просто ссылаемся на [Top 10 Products 2007], что делает основную часть запроса очень ясной.

Это значительно упрощает понимание того, что отображается на оси ROWS, даже если определение самого набора достаточно сложное.

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

Пример 2: Продажи AdventureWorks для "Высокодоходных Клиентов" по регионам и годам
Предположим, "Высокодоходные Клиенты" — это те, кто сделал заказов на сумму более $10,000 в 2007 году. Мы хотим проанализировать их продажи по регионам и годам.
Без именованного набора:

SELECT
 [Measures].[Sales Amount] ON COLUMNS,
 CrossJoin(
   [Geography].[Sales Territory].[Sales Territory Group].Members,
   [Date].[Calendar Year].Members
 ) ON ROWS
FROM
 [Adventure Works]
WHERE
 Filter(
[Customer].[Customer].[Customer].Members,
   ([Measures].[Sales Amount], [Date].[Calendar Year].&[2007]) > 10000
 )


С использованием именованного набора:

WITH SET [High-Value Customers 2007] AS
 Filter(
   [Customer].[Customer].[Customer].Members,
   ([Measures].[Sales Amount], [Date].[Calendar Year].&[2007]) > 10000
 )
SELECT
 [Measures].[Sales Amount] ON COLUMNS,
 CrossJoin(
   [Geography].[Sales Territory].[Sales Territory Group].Members,
   [Date].[Calendar Year].Members
 ) ON ROWS
FROM
 [Adventure Works]
WHERE
 [High-Value Customers 2007]


Разбор:
●       Мы определили сложный набор [High-Value Customers 2007] один раз.
●       Затем мы использовали его в WHERE Clause. Если бы нам понадобилось использовать этот же набор клиентов для других целей (например, на оси или в другом фильтре), нам не пришлось бы повторять все определение Filter(...).

Преимущества переиспользования:
●       Меньше дублирования кода: Сокращает объем кода, который нужно писать и поддерживать.
●       Упрощенная поддержка: Если определение набора меняется (например, порог для "высокодоходных клиентов" становится $15,000), вам нужно внести изменение только в одном месте — в определении именованного набора. Все запросы, ссылающиеся на этот набор, автоматически будут использовать новое определение.
●       Снижение ошибок: Меньше дублирования означает меньше шансов на опечатки или несоответствия в логике.

Область видимости именованного набора определяет, где этот набор может быть использован.

1.     Запрос-уровень (Query-scoped):
○       Определяются с помощью WITH SET в начале MDX-запроса.
○       Доступны только в рамках того запроса, в котором они определены.
○       Идеально подходят для ad-hoc анализа, отчетов, которые не требуют сохранения логики на уровне куба, или для тестирования новых определений наборов.
○       Пример: Все наши предыдущие примеры.

2.     Сессия-уровень (Session-scoped):
○       Определяются с помощью команды CREATE SESSION SET.
○       Доступны в течение всей текущей пользовательской сессии.
○       Полезны, когда один и тот же набор должен использоваться в нескольких последовательных запросах в рамках одной сессии, но не должен быть частью постоянных метаданных куба.
○       Пример: Аналитик может определить набор [My Custom Product Group] в начале сессии и затем использовать его в различных запросах, не переопределяя его каждый раз.

3.     Куб-уровень (Cube-scoped):
○       Определяются в MDX-скрипте куба (например, в SQL Server Analysis Services).
○       Становятся постоянной частью куба и доступны всем, кто имеет к нему доступ.
○       Идеальны для стандартных бизнес-групп, которые должны быть доступны всем пользователям и во всех отчетах (например, [Top Selling Products], [Key Regions]).
○       Пример: Если в AdventureWorks есть постоянно отслеживаемый набор 'Strategic Accounts', его можно определить на уровне куба.

Важно: В контексте плагина для Excel, скорее всего, вы будете работать с запросами уровня сессии или запроса, так как они дают наибольшую гибкость без необходимости изменять структуру самого куба.

Давайте рассмотрим несколько более сложных сценариев с использованием именованных наборов и данных AdventureWorks.

Пример 3: Анализ продаж для 'Премиум-клиентов' и 'Основных продуктов' по кварталам
Предположим:
●       Премиум-клиенты: Клиенты, чьи интернет-продажи за 2007 год превысили $5,000.
●       Основные продукты: Продукты, относящиеся к категориям 'Bikes' и 'Components'.
Мы хотим увидеть продажи этих продуктов для этих клиентов по кварталам 2007 года.

WITH SET [Premium Customers 2007] AS
 Filter(
[Customer].[Customer].[Customer].Members,
   ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2007]) > 5000
 )
SET [Core Products] AS
 {[Product].[Category].&[Bikes], [Product].[Category].&[Components]}
SELECT
 [Measures].[Sales Amount] ON COLUMNS,
 NON EMPTY [Date].[Calendar].[Calendar Quarter].Members ON ROWS
FROM
 [Adventure Works]
WHERE
 ([Premium Customers 2007], [Core Products], [Date].[Calendar Year].&[2007])


Разбор:
●       Мы определили два именованных набора: [Premium Customers 2007] и [Core Products].
●       Затем мы использовали эти наборы в WHERE Clause, создавая кортеж, который фильтрует весь куб по этим конкретным группам клиентов и продуктов, а также по 2007 году.
●       На оси ROWS мы видим продажи по кварталам, но только для пересечения этих двух групп.
Этот запрос демонстрирует, как именованные наборы позволяют создавать очень специфические срезы данных, не загромождая основной запрос сложными выражениями.

Пример 4: Процентный вклад каждой подкатегории в 'Топ-5 Категорий по Продажам' за 2008 год
Здесь мы комбинируем TopCount для категорий и расчет процента вклада для подкатегорий.

WITH SET [Top 5 Categories 2008] AS
 TopCount(
   [Product].[Category].Members,
   5,
   ([Measures].[Sales Amount], [Date].[Calendar Year].&[2008])
 )
MEMBER [Measures].[Sales % of Parent Category] AS
 IIF(
   IsEmpty(([Measures].[Sales Amount], [Product].[Category].CurrentMember.Parent)),
   NULL,
   [Measures].[Sales Amount] / ([Measures].[Sales Amount], [Product].[Category].CurrentMember.Parent)
 )
 , FORMAT_STRING = "Percent"
SELECT
 {[Measures].[Sales Amount], [Measures].[Sales % of Parent Category]} ON COLUMNS,
 NON EMPTY
 Generate(
   [Top 5 Categories 2008],
[Product].[Category].CurrentMember.Children
 ) ON ROWS
FROM
 [Adventure Works]
WHERE
 [Date].[Calendar Year].&[2008]


Разбор:
●       Мы определили именованный набор [Top 5 Categories 2008], который содержит 5 самых продаваемых категорий продуктов в 2008 году.
●       Мы также определили расчетную меру [Sales % of Parent Category], которая вычисляет процент продаж подкатегории от ее родительской категории.
●       На оси ROWS мы используем функцию Generate(). Она итерирует по каждому члену в [Top 5 Categories 2008] и для каждого из них возвращает набор его дочерних элементов (подкатегорий). Таким образом, мы получаем список всех подкатегорий, принадлежащих к этим топ-5 категориям.
●       Результат показывает продажи и процентный вклад для каждой подкатегории внутри ее родительской категории, но только для тех категорий, которые вошли в наш "Топ-5".
Этот пример наглядно демонстрирует, как именованные наборы могут быть использованы в сочетании с другими мощными функциями MDX (Generate, TopCount, CurrentMember.Parent) для создания очень точных и сложных аналитических отчетов.

Подводя итог, использование именованных наборов (или, как их иногда называют, алиасов для наборов) дает множество преимуществ:

1.     Улучшенная читаемость запросов: Сложные выражения наборов выносятся в отдельную секцию и получают осмысленные имена, делая основной запрос более понятным. Это особенно ценно, когда запросы становятся очень длинными. "Чистый код — это поддерживаемый код, и именованные наборы играют здесь ключевую роль", — это принцип, который должен быть в основе любой практики написания MDX.
2.     Повторное использование кода: Один и тот же набор может быть определен один раз и использован многократно в различных частях запроса или в разных запросах (если это набор уровня сессии или куба). Это сокращает объем написанного кода.
3.     Упрощенная поддержка и модификация: Изменение определения сложного набора требует редактирования только в одном месте, а не во всех местах, где он используется. Это значительно снижает риск ошибок и ускоряет процесс обновления аналитической логики.
4.     Снижение вероятности ошибок: Меньше дублирования кода означает меньше возможностей для опечаток или несоответствий в логике, что приводит к более надежным отчетам.
5.     Повышение производительности (в некоторых случаях): Хотя сам по себе именованный набор не обязательно ускоряет запрос, он может помочь в оптимизации, делая запрос более структурированным. Кроме того, если именованный набор уровня куба используется часто, Analysis Services может оптимизировать его вычисление.
6.     Модульность: Именованные наборы способствуют модульному подходу к разработке MDX-запросов, позволяя разбивать сложные задачи на более мелкие, управляемые компоненты.

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

Освоив концепцию именованных наборов и научившись эффективно применять их в своих запросах
(особенно с использованием данных AdventureWorks), вы сделаете огромный шаг вперед в своем мастерстве MDX.
Это позволит вам создавать более сложные и точные отчеты, которые будут служить надежной основой для принятия стратегических решений в вашей компании, а также заложит прочную основу для будущего курса по MDX/DAX на основе плагина для Excel.