Язык MDX. Базовые понятия и термины многомерных выражений

[s.sakharova (AlphaBI Community)]

Язык многомерных выражений (MDX) является отраслевым стандартом языка запросов и вычислений, используемым для эффективной работы с многомерными структурами данных.

• Многомерное выражение основано на спецификации XML для аналитики (XMLA), поддержка MDX реализована в OLAP-серверах.
• В AlphaBI используем его open-source реализацию в виде проекта Mondrian от Pentaho.

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

• SQL - работает с двумерными таблицами, которая может меняться только вертикально, т.е. набор колонок и его последовательность определена заранее, работает со скалярными типами (строка, целое число т.п.) и короткими транзакциями.
• MDX - работает с многомерной моделью. которая оперирует координатами и понятными пользователю предметными сущностями. Поэтому в MDX мы пишем, что именно мы хотим: какие именно данные и как именно их вывести в отчете. Каждый запрос отвечает на 3 вопроса:
  • Что агрегировать?
  • Что выводим в строки?
  • Что выводим в столбцы?

Рассмотрим пример хранилища.

В DWH есть витрина данных с демографическими показателями рождаемости и смертности в разрезе дат, регионов, пола и типа местности. На базе витрины построено многомерное хранилище данных. В многомерных базах данных OLAP в центре находится таблица фактов, в которой накапливаются показатели (сумма, кол-во) и ссылки на справочники. При работе: срезы, фильтры, грани куба строятся по справочникам, а агрегаты по таблице фактов.

На базе хранилища построен куб, в структуре данного куба:

Меры:

• Количество рожденных
• Количество умерших

Размерности с иерархиями:

1. Периоды
   1. год
   2. квартал
   3. месяц
2. Субъекты РФ
   1. федеральный округ
   2. регион
3. Пол
   1. пол
4. Тип местности
   1. тип местности

1. Базовые элементы MDX-выражений

• Иерархия - логическая структура размерности. Например, [dim].[hier]
• Уровень (Level) - используются в качестве средства организации и объединения данных иерархии, т.е. описывает иерархию построения данных. Например, [dim].[hier].[level]
• Элемент (Member) - единица описания на каждом уровне иерархии. Например, [dim].[hier].[member]

	Координаты/Кортеж (Tuple)		Множество/Набор (Set)
Комментарий	Комбинация элементов из иерархий одного измерения	Комбинация элементов из иерархий более 1 измерения	Множество/набор элементов (member set) - это комбинация элементов из иерархий одного измерения	Множество/набор кортежей (tuple set) - это комбинация элементов из иерархий одного или более измерений.
Пример формулы	([dim_period].[period_h].[2020])	( [dim_period].[period_h].[2020], [dim_reg].[reg_h].[Центральный федеральный округ] )	{ [dim_period].[period_h].[2020],[dim_period].[period_h].[2021] }	{ ([dim_period].[period_h].[2020], [dim_reg].[reg_h].[Центральный федеральный округ]),([dim_period].[period_h].[2021], [dim_reg].[reg_h].[Центральный федеральный округ]) }
Пример в OLAP
Пример в кубе

2. Операторы MDX-выражений

Язык многомерных выражений поддерживает арифметические и логические операторы, операторы сравнения, операторы над наборами, а также строковые и унарные операторы.
Справочник: Справочник по операторам многомерных выражений - SQL Server | Microsoft Learn

В таблице содержится список допустимых операторов.

Виды операторов	Пример
Комментарии
// здесь следует комментарий -- здесь следует комментарий /* здесь следует комментарий */	// Comment_Text /* Comment_Text */
Арифметические операторы и операции над множествами
+ – сложение/объединение/соединение - – вычитание/исключение * – умножение/перекрестное соединение / – деление ^ – возведение в степень : – задание интервалов	[Measures].[births_cnt]-[Measures].[deaths_cnt] [dim_period].[period_h].[2020] + [dim_period].[period_h].[2020] {[dim_period].[period_h].[2020] : [dim_period].[period_h].[2022]}
Операторы сравнения
>=, = , <=, >, <, <>	[Measures].[births_cnt]>10000
Логические операторы
AND (И) OR (ИЛИ) XOR (Исключение) NOT(Отрицание) IS (Эквивалентность)	[Measures].[births_cnt]>10000 AND [Measures].[births_cnt]<100000 [dim_reg].[reg_h].[fo].CurrentMember is [dim_reg].[reg_h].[Центральный федеральный округ]

3. MDX-функции по категориям значений

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

Виды:

• fn(a,b), например, Descendants([dim_reg].[reg_h].[Центральный федеральный округ])
• a.fn, например, [dim_reg].[reg_h].[Центральный федеральный округ].FirstSibling
• a.fn(a,b)? например, [dim_reg].[reg_h].Levels(1).Members

Справочник: https:// docs.microsoft.com/ru-ru/sql/mdx/mdx-function-reference-mdx?view=sql-server-ver15
MDX Specification: http :// mondrian.sourceforge.net/head/mdx.html

Примеры функций по категориям

Описание	Примеры функций
Функции элементов Функции для получения элементов из других многомерных сущностей, таких как измерения, уровни или кортежи. Например, функция FirstChild действует на элемент и возвращает элемент.	CurrentMember, DefaultMember, FirstChild, NextMember, OpeningPeriod, Parent, StrToMember, Lead
Функции кортежей Функции, возвращающие кортежи. Эти функции можно использовать в любом месте, где допускаются кортежи. Например, функция Item (кортеж) может использоваться для получения первого кортежа из набора.	Current, Item (кортеж), Root, StrToTuple
Функции массивов Функции массивов обрабатывают набор и возвращают массив.	SetToArray
Функции иерархий Функции иерархий возвращают иерархию, обрабатывая элемент, уровень, иерархию или строку.	Hierarchy, Dimension, Dimensions
Функции уровней Функции уровней возвращают уровень, обрабатывая элемент, уровень или строку.	Level, Levels
Функции наборов Функции, возвращающие наборы. Чтобы извлечь набор, не обязательно явно перечислять все кортежи в квадратных скобках. Например, оператор «двоеточие» (:) позволяет использовать естественный порядок элементов для создания набора.	AllMembers, BottomCount, Children, Distinct, DrilldownLevel, Filter, Members, PeriodsToDate, TopCount ExceptAncestors
Логические функции Логическая функция обрабатывает многомерное выражение, возвращая информацию о кортежах, элементах или наборах в выражении. Например, функция IsEmpty оценивает, возвращает ли выражение значение пустой ячейки.	IsAncestor, IsEmpty, IsGeneration, IsLeaf, IsSibling
Числовые функции Числовая функция обрабатывает многомерное выражение, возвращая скалярную величину. Например, функция Aggregate возвращает скалярную величину, вычисляемую с помощью статистической обработки мер по кортежам в заданном наборе.	Aggregate, Avg, Count, IIf, Max, Ordinal, Rank, StrToValue, Sum, Var, DistinctCount, Value, Min, Median
Строковые функции Строковая функция обрабатывает многомерное выражение, возвращая строку. Например, функция UniqueName возвращает строковое значение, включающее в себя уникальное имя измерения, иерархии, уровня или элемента.

4. Точки кастомизации MDX-выражений

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

Точка кастомизации		Результат выражения	Пример
Определение структуры куба в дизайнере:	формула расчетной меры	значение меры	Доля количества рожденных в ФО от общего числа рожденных в стране, при условии, что на ось выведена иерархия по регионам Divide([Measures].[births_cnt], sum([dim_reg].[reg_h].DefaultMember, [Measures].[births_cnt]))*100
	формула расчетного элемента иерархии	значение меры	Итог по всем округам за исключением одного sum(except([dim_reg].[reg_h].Levels(1).Members, [dim_reg].[reg_h].[Приволжский федеральный округ]))
	именованный набор - результат	tuple set	Количество рожденных мальчиков в сельской и городской местности местности {([gender_d].[gender_h].[Мужчины],[population_type_d].[population_type_h].[сельское население]), (([gender_d].[gender_h].[Мужчины],[population_type_d].[population_type_h].[городское население])}
	именованный набор - результат	member set	3 региона с самой высокой рождаемостью TopCount([dim_reg].[reg_h].Levels(2).Members, 3, [Measures].[births_cnt])
OLAP представление:	формула в настройках иерархии на оси	member set	Получить набор всех вышестоящих элементов (родителей) для Москвы Ascendants([dim_reg].[reg_h].[Центральный федеральный округ].[г. Москва])
	формула расчетной меры	значение меры	Прирост количества рожденных по сравнению с предыдущим периодом [Measures].[births_cnt]-sum([dim_period].[period_h].Prevmember, [Measures].[births_cnt])
	формула расчетного элемента иерархии	значение меры	Года по которым был отрицательный прирост населения sum(filter([dim_period].[period_h].Levels(1).Members,([Measures].[births_cnt]- [Measures].[deaths_cnt])<0))