блог о bi, №1 в рунете

Работа с eMondrian

Введение

eMondrian – это бесплатный OLAP-сервер с открытым исходным кодом, основанный на проекте Mondrian. Сервер OLAP представляет базу данных как многомерное пространство с измерениями, что позволяет скрыть сложность базовых таблиц и их связей, а также позволяет интерактивно анализировать данные с разных точек зрения. Сервер eMondrian может работать в операционных системах Windows и Linux.

eMondrian поддерживает стандарт XML для анализа (XMLA) и OLE DB для OLAP, с помощью которых можно подключиться к клиентским инструментам: Microsoft Excel, Power BI, Tableau. Любая база данных, имеющая драйвер JDBC, может быть источником данных для eMondrian.

В данной работе будет рассказано об использовании eMondrian, который применялся как посредник для работы анализа данных в Excel из хранилища данных SQL Server.

Установка eMondrian

В данной работе eMondrian был установлен локально на Windows 10 с Java 1.8.0_311 и Apache Tomcat / 9.0.56.

Ход установки:

Установить Java.
Установить Tomcat.
Настроить/запустить Tomcat.
Установить eMondrian (скачать и поместить war файл в каталог webapps java-сервера).

В итоге строка подключения будет выглядит следующим образом: http://localhost:8080/emondrian/xmla. Более подробно об установке можно ознакомиться в документации eMondrian.

Чтобы настроить экземпляр, нужно изменить три файла в веб-приложении eMondrian:

/WEB-INF/datasources.xml (Содержит описание источника данных и путь к файлу схемы).
WEB-INF/schema/schema.xml (Файл схемы).
/WEB-INF/classes/mondrian.properties (Свойства конфигурации сервера).

Подключение к базе данных

Изначально настройки источника данных находятся в файле /WEB-INF/datasources.xml. Он содержит строку подключения к источнику данных и ссылку на файл, в котором находится схема eMondrian. По умолчанию папка для файлов схемы является /WEB-INF/schema/.

Для подключения к базе данных нужно:

Установить JDBC драйвер соответствующей версии Java, если его нет в eMondrian.
Указать в файле mondrian.properties необходимые значения: строка подключения, драйвер и путь к драйверу.
Изменить файл datasources.xml, основные XML-элементы для изменения <DataSourceInfo> и <Catalogs>.

В <DataSourceInfo> записывается строка подключения, а в <Catalogs> определяется путь к схеме.

Написание схемы

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

Порядок расположения XML-элементов важен, основные элементы схемы представлены ниже в таблице 1.

Таблица 1. – Основные элементы схемы.

Коллекция кубов, виртуальных кубов, общих измерений и ролей.

Основной элемент, все остальные расположены внутри него, идет сразу после декларации XML

<Cube>

Набор измерений, сосредоточенных в таблице фактов.

Располагается внутри <Schema>

Куб, определяемый путем объединения измерений одного или нескольких кубов.

Располагается внутри <Schema> после кубов

Измерение.

Локальное измерение располагается в <Cube> и будет доступно для данного куба,

Глобальное измерение располагается в <Schema> и его можно использовать в нескольких кубах

Иерархия.

Находится в <Dimension>, название первой иерархии должно совпадать с названием измерения

<Level>

Уровень иерархии.

Находится в <Hierarchy>; уровни иерархии нужно располагать последовательно друг за другом

Мера.

Располагается в <Cube>

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

Располагается в <Cube>

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

Располагается в <Cube>

<Table>

Таблица фактов или измерений.

Располагается внутри <Cube> или <Dimension> перед другими элементами

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

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

Работа/подключение в Excel

Чтобы подключить eMondrian нужно:

Выбрать в Excel вкладку Data, затем Get Data -> From Database -> From Analysis Services.
Вставить адрес службы Mondrian XMLA в качестве имени сервера и нажать «Далее».
Выбрать куб и нажать «Готово».

Ниже представлена данная последовательность на рисунке 1.

Рисунок 1 (а, б). – Подключение в Excel.

а) - выбор From Analysis Services.

б) -адрес службы Mondrian

Работа eMondrian

Сервер eMondrian – это реляционный сервер OLAP (ROLAP), что означает, что он всегда отображает данные в реальном времени из источника, работает как прокси, представляющий данные в виде кубов и выполняющий многомерные запросы. Этот сервер выполняет запросы, написанные на языке многомерных выражений, считывает данные из исходной базы данных и представляет результаты в многомерном формате (рисунок 2).

Внутри себя eMondrian использует кэш, состоящий из карт ключей и значений. В кэше очищаются значения, не имеющие связей, сборщиком мусора, а полностью кэш очищается в течении часа, так как час является максимальным временем ожидания сеанса по умолчанию. Кеш-пул состоит из единственного экземпляра, написанного при использовании паттерна одиночка (Singleton).

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

Рисунок 2. – Работа eMondrian.

Последовательность действий при работе в Excel после подключения к eMondrian:

Пользователь работает с панелью PivotTable, на каждое действие пользователя, Excel посылает MDX-запросы, используя протокол XMLA;
eMondrian парсит принятый запрос, части которого записывает/сравнивает с кэшом;
Опираясь на схему и кэш, eMondrian генерирует необходимые SQL-запросы к базе;
По JDBC eMondrian отправляет запросы в хранилище, после чего принимает результаты запросов и записывает результаты в кэш;
Рассчитывает необходимые значения, формирует результат для MDX-запроса и по протоколу XMLA отправляет его в Excel;
Принятый результат Excel отображает пользователю.

При первых запросах Excel, eMondrian посылает приблизительное такую последовательность запросов в хранилище данных:

1) Для каждого используемого измерения:

количество строк в справочнике.
значения справочника для всех используемых уровней иерархии.

2) Для каждой меры:

расчет всех тоталов для всех используемых уровней иерархий измерений.
расчет всех значений.

В случае фильтров, запросы к справочникам дублируются дважды (как предположение возможно один запрос для сравнения в кэше, другой для вычислений). Для формирования фильтра eMondrian посылает два типа запросов:

значения справочника.
запросы для выбранных значений из справочника в качестве фильтров.

Иногда подобное дублирование запросов происходит и для справочников, которые используются в запросе как строки или колонки сводной таблицы.

Запрос на количество строк в справочнике посылается на сервер редко: либо когда долго не использовали измерение, либо когда по другим каким-то причинам он был удален из кэша.

eMondrian предназначен для использования разных баз данных и генерируемые им запросы к базе придерживаются одной парадигмы:


select <список уровней нужных измерений>, <список агрегатов> 
from <список через запятую таблиц фактов и справочников>
where <условия объединения > and <условия для фильтрации> 
group by <по всем нужным уровням измерений>

Чтобы лишний раз не группировать по уровню измерения, можно использовать атрибут uniqueMembers="true" для данного уровня измерения, если заранее известно, что в данном измерение/уровне находятся уникальные значения, но как показала практика, eMondrian все равно измерения будет группировать и для фильтров рассчитываться уникальное количество строк.

Условия для фильтрации eMondrian формирует в запросе условия к колонкам таблиц справочников, в зависимости от данного измерения, по которому происходит фильтрация; условия формируются по-разному, на это влияет имеет или не имеет иерархию измерение для фильтра. Если значение выбрано в иерархии, то в запросе будут фигурировать колонки справочника, которые олицетворяют уровни выше по иерархии.

Таблица 2. – Формирование условия фильтрации

Когда одно значение в фильтре

колонка = значению

Когда несколько значений в фильтре

колонка in (значение1, значение2)

Когда одно значение выбрано в иерархии

колонка выше по уровню = значению для уровня выше and колонка = значению

Когда несколько значений выбраны в иерархии

((колонка выше по уровню = значению для уровня выше and колонка = значение1) or (колонка выше по уровню = значению для уровня выше and колонка = значение2))

Логирование в eMondrian

eMondrian ведет три вида логирования:

Основной журнал, логирует все что происходит в eMondrian (в том числе и все запросы).
Отдельный журнал приходящих MDX-запросов (чтобы не искать в основном журнале).
Отдельный журнал посылаемых SQL-запросов в базу (чтобы не искать в основном журнале).

Все журналы можно посмотреть через браузер, перейдя по ссылке на свой развернутый eMondrian (в данном случаем http://localhost:8080/emondrian/), и на основной странице внизу будет представлены ссылки на переход к страницам журнала. Все ссылки представлены ниже (слева вид на основной странице, справа – ссылки логов).

http://localhost:8080/emondrian/logs/FILE
http://localhost:8080/emondrian/logs/MDXLOG
http://localhost:8080/emondrian/logs/SQLLOG

Ход работы

В SQL Server было разработано хранилище:

Факты:

Продажи (10000000 шт).
Поставки (10926713 шт).

2. Справочники

Календарь (за 3 года).
Магазины (100 шт).
Производители (1000 шт).
Номенклатура (10000 шт).
Группы номенклатур (500 шт).

Для проверки работы eMondrian была создана схема:
1. Общие измерения:

Календарь (иерархия Г-К-М-Д).
Магазины.
Производители.
Номенклатура (иерархии Номенклатура и Группы номенклатур-Номенклатура).

2. Кубы:

Продажи.
Поставки.
Товарооборот (куб содержащий запрос представления объединения продаж и поставок).
Продажи и поставки (виртуальный куб, объединяющий кубы продаж и поставок).

SQL Server находится удаленно на сервере, eMondrian и Excel находятся непосредственно на одном ПК.

Пример работы

Подсчет количества значений в измерениях.

Выбрав в Excel куб, первым делом на базу посылаются запросы на количество значений в измерениях/уровнях, рисунок из Profiler с описанием показан ниже (слева пояснение, справа вид в Profiler)

В данном случае были посланы 9 запросов:
Количество магазинов
Количество производителей
Количество номенклатуры
Количество групп номенклатур
Количество пар номенклатура -группа номенклатур
Количество лет
Количество лет-кварталов
Количество лет-кварталов-месяцев
Количество лет-кварталов-месяцев-дней

Запрос на количество лет-кварталов-месяцев-дней

2. Выбор значения (меры) в сводной таблице.

Если выбрать в Excel значение товарооборота в шт., за которое отвечает вычислительный элемент Movement quantity, рассчитывающийся из разницы прихода и продаж, то на eMondrian придет MDX-запрос:


select from [Movement]
where ([Measures].[Movement quantity])

А eMondrian отправит SQL-запрос к базе для получения значений мер прихода и продаж в шт.


select
    sum("v_fct_movement_test_olap"."sale_quantity") as "m0",
    sum("v_fct_movement_test_olap"."cost_quantity") as "m1"
from
    (select sale as sale, quantity as sale_quantity, 0 as cost, 0 as cost_quantity, did, store_id, good_id, manufacturer_id from dbo.fct_sales_test_olap
union all
select 0 as sale, 0 as sale_quantity, cost as cost, quantity as cost_quantity, did, store_id, good_id, manufacturer_id from dbo.fct_income_test_olap) as "v_fct_movement_test_olap"

После получения значений из хранилища, eMondrian запишет их в кэш и рассчитает значения товарооборота, после чего отправит результат в Excel.

3. Добавление колонок и строк в сводной таблице
Далее для анализа значений товарооборота добавим в сводной таблице колонки и сроки (календарь и магазин) для получения значений товарооборота в шт. по каждому магазину и по каждому году с раскрытием 21 года по кварталам.
Excel отправит следующий запрос:


select NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Store].[All Stores]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS,
  NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownLevel({[Date].[All Date]})}}, {[Date].[2021]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
from [Movement]
where ([Measures].[Movement quantity])

После получения eMondrian сгенерирует запросы к базе для получения значений, которых ему не хватает для расчета результата, рисунок из Profiler с описанием показан ниже (справа пояснение, слева вид в Profiler)

В данном случае были посланы 16 запросов:

Получения значений лет из календаря

(дважды)

Количество уникальных лет
Продажи и приходы в шт по годам
Получение значений лет-квартал из календаря
Получение значений кварталов из календаря
Получение значений магазинов

(дважды)

Количество уникальных магазинов
Продажи и приходы в шт по магазинам
Продажи и приходы в шт по магазинам и по годам
Количество уникальных значений лег-картал-месяцы
Количество уникальных значений лег-картал-месяцы-дни
Количество уникальных кварталов
Продажи и приходы в шт за 21 год по кварталам
Продажи и приходы в шт за 21 год по кварталам и по магазинам

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

Результаты расчета eMondrian и отображения в Excel значений товарооборота в шт. по каждому магазину и по каждому году с раскрытием 21 года по кварталам представлены на рисунке 3.

Рисунок 3. – Результат расчета в Excel.

Тестирование

Измерение календарь имеет иерархию Год-Квартал-Месяц-День, изначально имела несколько иерархий, но при наличии нескольких иерархий, расчетный остаток вычислялся корректно только для первой иерархии, поэтому было принято решение оставить только одну.

Справочник «группы номенклатур» было принято реализовать как одну из частей иерархии «номенклатур». Так как группы номенклатур имеют связь с фактами только через номенклатуру. Изначально в схеме было предпринято объединение номенклатур с группами номенклатур, используя XML-элемент <Join>, но во время подключения к Excel начала выскакивать ошибка, что в измерении используется две таблицы, поэтому было принято решение создать самостоятельно запрос для объединения двух справочников, используя XML-элементы <View> и <SQL>.

Кубы продаж и поставок являются простыми кубами, связанные с соответствующими таблицами фактов, которые были предназначены для проверки агрегатов для подсчета мер. Куб товарооборот и виртуальный куб «продаж и поставок» имеют одно и тоже предназначение: расчет мер (продаж, поставок и товарооборота) и вычислительных элементов расчетного остатка и расчетного счета.

В ходе работы каждый куб в схеме был отдельно протестирован на:

Корректность получаемых значений;
Скорость расчета;
Нагружаемость сервера;
Нагружаемость компьютера;
Эффективность посылаемых запросов;

Результаты тестирования, по каждому критерию для всех вышеописанных кубов, приведены ниже в таблице 3.

Таблица 3. – Сравнение кубов по заданным критериям

Корректность получаемых значений

Соответствуют

Некорректно при множественной фильтрации

Время расчета

Приемлемое, но растет от сложности и количества посылаемых запросов к базе

Приемлемое, но координально растет по причине формирования некорректных запросов

Нагружаемость сервера

От 10% до 70%, зависит от количества и сложности вычислений запроса

От 10% до 80%, зависит от количества и сложности вычислений запроса

От 10% до 100%, если формирует некорректные запросы

Нагружаемость компьютера

Центральный процессор до 30% при сложных вычислениях

Эффективность посылаемых запросов

Составляет корректные запросы, но неоптимальные

Посылает некорректные запросы при множественной фильтрации

Нагружаемость компьютера в основном происходит при получении и при расчете значений, записи их в кэш, а также при формировании ответа Excel.

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

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

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

Куб товарооборот был реализован с использованием xml-элементов схемы <View> и <SQL> с помощью которых был создан запрос для объединения таблиц фактов продаж и поставок, а значения товарооборота являлись вычисляемыми элементами, также был создан его аналог, где было создано представление в хранилище, в котором вычислялись значения товарооборота и с которым велась работа как с таблицей фактов, но во время тестирования данный аналог давал такие же результаты, но время расчета было немного больше.

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

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

Таблица 4. – Альтернативы расчета диапазона дат для накопительного итога

NULL – как начало времен для диапазона дат

Ошибка, так как в данном диалекте MDX нет NULL

PeriodsToDate – рассчитывает диапазон дат, от начала до текущего значения

Корректно работал, но перестал на сложных запросах с множественной фильтрацией

OpeninigPeriod – как начало для диапазона дат

Корректно работает

По умолчанию мера имеет тип Numeric, но для каждой меры можно задать один из трех типов: String, Integer, Numeric; а также можно задать формат отображения. В качестве разделителя тысячных разрядов используется «,», примеры встроенных форматов, для значения 12342345.25:

formatString="#,###" (значения будет отображаться как 12,342,345)
formatString="#,###.00" (значения будет отображаться как 12,342,345.00)
formatString="#,###.##" (значения будет отображаться как 12,342,345.25)

Существует возможность использовать свои форматы, создаваемые через подключаемые модули, более подробно об этом можно ознакомиться в соответствующем разделе документации.

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

Заключение

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

Преимущества:

Позволяет обращаться к хранилищу как к кубу.
Не сложен в установке.
Простой функционал для понимания и использования.

Недостатки:

Не все драйвера есть изначально и нужно искать подходящий драйвер для соединения с базой.
Не весь функционал работает корректно, а некоторые элементы не работают.
Составляет неоптимизированные запросы к базе.
Имеется в наличии только старая документация Mondrian.

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

Мнение о продукте: интересный инструмент для обращения базы как к кубу, можно использовать, если нужно просматривать/анализировать данные для несложных структур моделей, а также, если для анализа необходимы несложные MDX-запросы.

Личные советы, проверенные при работе:

В схеме снежинка лучше заранее либо с помощью xml-элемента <SQL>, либо с помощью представлений в базе объединить справочники (так как xml-элемент <JOIN> не сработал), и преобразовать в схему звезда.
Виртуальный куб разочаровал, например для того же товарооборота, лучше создать таблицу, или представление, или самостоятельно написать запрос на объединение с помощью xml-элемента <SQL>, и использовать один из этих вариантов в обычном кубе на схеме.
Не использовать при выборе значений фильтра на подобии «все, кроме нескольких», так как он сформирует гигантский запрос с условием, где пройдется по каждому элементу фильтра и по всем иерархиям, кроме невыбранных.
Использовать uniqueMembers="true" для уровней измерений для тех справочником, где заведомо знаем, что значения в нем уникальные, чтобы лишний раз не группировать по уникальным элементам.
Не использовать сложные запросы с множественными фильтрами при расчете вычислительных элементов, так как в таких случаях будут долгие вычисления: либо на сервере с базой, либо в самом eMondrian.

Вернуться к блогу