Многомерные вычисления с использованием MDX

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

MDX и SQL

Язык MDX (Multidimensional Expressions) был создан в конце 90-х годов компанией Microsoft в качестве стандартного языка для работы с многомерной моделью данных. Поддержка MDX реализована в OLAP-серверах, и для его использования необходим OLAP-сервер. MDX позволяет сравнительно просто выполнять весьма сложные вычисления, реализовать которые в SQL было бы трудоемко, и в этом его главное преимущество. Например, если нас интересует объем продаж в текущем году по десятку наиболее продаваемых продуктов прошлого года, в SQL нам придется написать соединения, подзапросы и группировки. В MDX этого не потребуется. При этом реализация большинства операций в OLAP-серверах хорошо оптимизирована для работы с большими объемами данных. MDX предназначен и для написания запросов к многомерной модели, и для выполнения пользовательских вычислений. Мы кратко рассмотрим оба варианта использования. SQL работает с плоскими реляционными таблицами. Чтобы выбрать данные, мы перечисляем в списке набор атрибутов и выражений – это будут столбцы результата. Чтобы выбрать только отдельные строки, мы используем логическое выражение в WHERE. MDX работает с многомерной базой данных (кубом), где все данные хранятся в разрезе измерений, и чтобы выбрать какие-то значения, в MDX необходимо указать координаты для каждого измерения в кубе (перечислить элементы), на пересечении которых нужно извлечь значения. И именно гибкие возможности определения координат делают MDX мощным инструментом. Например, выберем объем продаж в разрезе категорий продукции за 2004 год: SQL: MDX: Обратите внимание на особенности MDX-запроса. В запросе указано, что нам нужно выбрать данные по двум осям – строк и столбцов, т.е. результатом будет кросс-таблица. В столбцах нас интересует один элемент Order Quantity измерения Measures. В строках нам интересны все элементы иерархии Category измерения Product. И в WHERE мы также ограничили набор данных, указав координату по измерению Date с одним элементом 2004 из иерархии Calendar Year. В MDX в выражении WHERE указывается не логическое выражение, а координаты, по которым нужно извлекать данные, и WHERE никогда напрямую не фильтрует множества, определенные на осях (для этого на оси следует использовать функцию Filter). Наши координаты не полные, так как мы не указали элементы для других измерений куба. В этом случае OLAP-сервер использует элементы по умолчанию, настроенные при создании измерений. Как правило, это элементы All, соответствующие агрегированным значениям по измерениям. Функция агрегации тоже настраивается при создании куба. Если наш источник рассматривать как куб с тремя измерениями (продукты, показатели, время), то визуально запрос можно представить так, как показано на рисунке. В качестве результата будет вырезан отмеченный желтым цветом «подкуб», состоящий из ячеек-значений, которые будут представлены в разрезе двух измерений-осей. Как правило, вам не придется писать MDX-запрос целиком самостоятельно, так как визуальные инструменты генерируют его автоматически. Давайте посмотрим, как это сделать в «Форсайт. Аналитической платформе».

Подключение к OLAP-серверу

Для подключения к OLAP-серверу нужно создать в репозитории объект «Каталог ADOMD», в параметрах указать реквизиты подключения. В поле «Имя» указывается имя многомерной базы данных. После этого через контекстное меню созданного объекта каталога следует вызвать команду «Обновить», чтобы платформа создала необходимые объекты метаданных в репозитории на основе метаданных OLAP-сервера. В моем случае в репозитории появился куб Sales. Открыв его, я могу визуально настроить аналогичный срез данных.

Создание MDX-вычислений

Помимо готовых показателей, созданных в кубе, пользователи могут выполнять собственные вычисления с использованием MDX на уровне отчета. Для этого необходимо создать новый вычисляемый элемент в каком-либо измерении (все измерения в этом плане равноправны, выбирайте любое по смыслу), так как все значения извлекаются в привязке к координатам, и для нового значения нужна новая координата. В качестве выражения следует определить корректное MDX-выражение, возвращающее число. Выражение может содержать следующие элементы: - операторы (арифметические, логические, сравнение, операции над множествами); - MDX-функции; - базовые элементы – константы, элементы измерений, координаты, множества, которые комбинируются при помощи операторов и функций. Когда вы разместите новый элемент на одной из осей (строки и столбцы), OLAP-сервер начнет вычислять выражение для каждой ячейки этого элемента с учетом всех прочих координат, определенных в отчете. В MDX есть два основных объекта, которые практически всегда приходится использовать в вычислениях: - Координаты. Координата (tuple) – это комбинация элементов из иерархий одного или более измерений. Например, ([Measures].[Order Quantity], [Date].[Calendar Year].&[2004]). Используются круглые скобки. Координата определяет либо ячейки куба на пересечении элементов измерений, либо используется как набор элементов измерений – это зависит от контекста. Ограничение: из каждого измерения может использоваться только один элемент. - Множества. Множество – это упорядоченный набор из одной или более координат. Например, {[Product].[Category].[Bikes], [Product].[Category].[Accessories]}. Используются фигурные скобки. Опять же, в зависимости от контекста множество описывает либо ячейки, на которые ссылаются координаты, либо просто элементы. Ограничение: каждая координата во множестве должна содержать одинаковый перечень и порядок измерений. В MDX есть много функций для работы с этими объектами. Давайте рассмотрим несколько примеров. Если вы будете конструировать выражения самостоятельно, всегда обращайте внимание, объект какого типа нужен для требуемой функции, и какой тип объекта она возвращает.

Вычисления с координатами

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

Вычисления с множествами

Многие функции в MDX работают с множествами, например, Sum, Avg. Давайте рассчитаем средний объем поквартальных продаж для каждого года: Функция Avg усредняет числовое выражение, заданное вторым аргументом, по всем элементам множества, заданного первым аргументом. В строки отчета мы разместим года и будем считать среднее по кварталам каждого года. Наш вычисляемый элемент рассчитывается для каждой строки таблицы, и множество для усреднения мы можем конструировать динамически, для каждого текущего года беря его кварталы. Именно эту операцию выполняет выражение:

Заключение

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