Подводим итоги проекта

Вот настало время повести итог “Open source BI” альтернативе в лице “Pentaho СЕ”, “SQL Power Architect“, “PDI”, “Schema Workbench” и одной из СУБД Mysql и PostgreSQL

Предлагаю вам к просмотру видео. (Финальная версия материалов/The final version of the materials)

План видео (ссылка на youtub http://youtu.be/LQHtIuKrtp4)
01:38 Краткий разбор проекта.
11:39 Обзор используемого ПО.
21:15 Безопасность проекта.
23:05 Резюме.

Аперетив

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

В уроке используем связку Pentaho CE 4, PDI 4.8, SQL Power Architect, “Schema Workbench” и PostgreSQL.

В принципе можете увеличить саму таблицу фактов и проверит как кубики вертятся. Однако стоит обратить внимание на работу элемента Dimension lookup/update и на параметр “Commit size”. Может произойти ситуация, когда “Dimension lookup/update” еще не обновит измерение, а в таблицу фактов будет происходить вставка. При этом будет нарушены ограничения внешних ключей.

Главное блюдо

И так основное блюдо готовилось очень долго и представляет из себя серию материалов по использованию программ. В качестве БД использую Mysql.

Подготовка

Кратко разбираем установку и настройку БД Foodmart, которую в дальнейшем объявим OLTP системой. Ссылка тут.

Установка и настройка основных рабочих инструментов Pentaho CE 4, PDI 4.8. Ссылка тут.

Варка

Проектирование ER схемы

Начинаем с разработки ER схемы БД. Используем SQL Power Architect. Ссылка на материалы тут и обзор. Особенности:

1. В уроке дается построение ER схемы
2. Сразу на этапе проектирования проводим оптимизацию по ключам, так как “Pentaho СЕ” использует ROLAP (Mondrian)
3. Основной упор делаю на “контрольное измерение”. В литературе не встречал, обзову как мне удобно. Кратко перечислю преимущества: загрузка по перекрестным периодам времени, простой откат к любой временной точке.

При проектировании постарался уйти от схемы снежинка к схеме звезда, хотя Mondrian может работать и обеими схемами.
Схемы БД делает возможным (однако на этих данных применить алгоритмы не получится, так как OLTP СУБД не содержала ключ транзакции):

1. Применение средств Data Mining (Алгоритм “Априори”)
2. Анализ потребительской корзины.
3. Анализ чеков (длина, структура, средний чек).

Развертывание схемы средствами PDI.

Рекомендую так делать по следующим причинам:

1. Схема и запросы будут в одном файле.
2. Схему можете развернуть и на другой БД.
3. И самое главное это первичное заполнение. Главное не забыть.
4. Генерацию правильного (для меня как аналитика) измерения времени.

Основная идея первичного заполнения это уйти от объединения left и right. Это первый шаг. И вторая цель это обеспечить работу элемента “Dimension lookup/update” (который требует от СУБД наличие ключа со значением “0”).

Разработка ELT процесса.

Ну вот и настал звездный час PDI. (ссылка)

1. Создание подключений к БД. (список поддерживаемых БД просто впечатляет).
2. Перенос таблиц измерений.
3. Параметризация запросов через переменные.
4. Фильтрация таблицы фактов
5. Заполнение таблицы фактов и замещение NULL значений на значения по умолчанию (делаю через SQL запрос).

Замещение NULL значений на значения по умолчанию - второй и заключительный шаг от left и right.
Параметризация запросов и контрольное измерение позволяют автоматизировать загрузку данных без участия человека. В нашей компании такой процесс уже крутится с ноября 2013 года и пока все без ошибок.

Тестирование ETL процесса + оптимизация.

Тестирование никто не отменял. Скрипты для проверки и видео тут.
При проектировании БД забыл добавить индекс к контрольному измерению. Это приводило к плохим результатам быстродействия ETL процесса. Вот этим занялись ссылка тут.

Разработка куба в Schema Workbench и публикация куба.

Будем разрабатывать куб, который будем просматривать в “Saiku Analytics”. Особенности урока:

1. Полная разработка куба и публикация.
2. Особенности проектирования измерения времени для “Saiku Analytics”. Улучшает отзывчивость системы и делает возможным строить более интересные срезы.
3. Установка плагина “Saiku Analytics”.

Все (схема, видео) располагаются по следующей ссылке.

Тестирование аналитических возможностей “Saiku Analytics”

Как и изначально и ожидалось, что “Saiku Analytics” проиграет Excel по возможностям визуализации. Основные причины:

1. Нет возможность работать с двумя осями.
2. Нет произвольной группировки по мерам.

Самочувствие после еды

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

Теперь по продуктам

PDI

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

1. Поддержка большого количества СУБД
2. Удобное и быстрое проектирование ETL процессов. (за два часа в видео спроектировал и оттестировал ETL процесс)
3. Возможность запуска из командной строки.
4. Простота установки и использования.
5. Для меня высокая скорость работы (тянул данные в районе 2,5 М строк. Заняло не более 5 минут). Стандартное время работы ежедневных задачах 20 - 30 минут.
6. Помогает проводить оптимизацию схемы. Показывает какие ключи нужно добавить.
7. Параметризация запросов и еще много чего.
8. Всеядность.
9. Возможность организовать циклы в обработке данных.

Минусы:

1. Нет проектов которые можно взять за основу.
2. Практически нет информации на русском.

Pentaho BI (Mondrian)

Неплохой продукт, но хотелось бы большего.
Плюсы:

1. Бесплатность.
2. Неплохие инструменты для визуализации кубов.
3. Своя аутентификация (в руководстве нашел, что можно прикрутить аунтификацию по LDAP. Не тестил.)
4. Доступ по протоколу XML/A (Pentaho 5.0 из коробки)
5. Поддержка ролей (Pentaho 5.0 из коробки)
6. Поддержка большого количества БД.
7. Основная нагрузка переносится на СУБД. (Сервер развертывал на офисной машине. Все летало)
8. Поддержка виртуальных кубов.
9. Параметризация схемы для использования агрегатных таблиц.
10. Улучшение с документацией (Pentaho 5.0 из коробки)
11. Поддержка схемы снежинка.

Вроде бы все не плохо, но есть проблемы:

1. Не поддерживает идею полуаддитивных мер и агрегацию LastChild, а именно не возможно работать с запасами (пока не могу придумать как красиво работать с остатками) .
2. MDX работает на подмножестве языка SQL. Это Rolap. В частности нет возможности выполнить пункт 1.
3. Работать комфортно можно в районе 10 М (пока на большем количестве не тестил. Просто нет данных). Далее требуются агрегаты.
4. Интеграция с Excel платная.

Поддержку полуаддитивных мер обещают в Mondrian 4.0

Schema Workbench

Ну очень современный и дружелюбный интерфейс :).
Плюсы:

1. Отслеживает некоторые ошибки.
2. Возможность публикации схем на сервере.
3. Относительная простота.

Минусы:

1. интерфейс.
2. поддержка русского языка (как отображаются русские названия в схеме).
3. Отслеживание ошибок. Иногда можно допустить детские ошибки, а потом не по детски их исправлять. Решается только внимательностью и тестированием.

Замены этой утилиты не вижу. Пока нет аналога поддерживающих функционал Schema Workbench.

Плагин “Saiku Analytics”

Плюсы:

1. Визуализация кубов
2. Интерфейс
3. Простая установка
4. Есть интересные графики
5. Экспорт данных в Excel.

Минусы:

1. Иногда не обновляет свой кэш. (Думал, что ETL процесс не отработал нормально)
2. Нет поддержки визуализации по двум осям.
3. Работа с иерархией (работает через closure таблицы).

Итоги

В целом довольно интересная получилась для меня четверка (Pentaho CE 4 или 5, PDI 4.8, SQL Power Architect, “Schema Workbench” ) и один из вратарей (Mysql или PostgreSQL). Я больше концентрируюсь на решении аналитических задач и проведением исследований. Очень не хватает бесплатной интеграции с Excel.
В целом проект соответствует заголовку сообщению, бесплатная альтернатива. Вы платите только своим временем. Для средних компаний самое то, особенно когда стоимость бизнеса равна стоимости BI решения.

Безопасность проекта

Проблемами безопастности данных не планировал заниматься в этом проекте, так как мне более интересно именно работать с аналитикой.
Пароли, которые используются в системе лежат в скрипте biserver-ce\data\hsqldb\hibernate.script
Подключение к БД hsqldb происходит login “SA” pas ‘’ (пустой пароль).

Ссылки по теме:

1. Вопрос к проектировки куба http://www.sql.ru/forum/1090575/vopros-k-proektirovki-kuba?hl=%f7%e5%ea%e8 

2. Ключ для дате-димешена: интегер, смарт или дату? http://www.sql.ru/forum/1088555/kluch-dlya-date-dimeshena-integer-smart-ili-datu

3. Анализ: "Совместно продаваемые товары"  http://www.sql.ru/forum/1089948/analiz-sovmestno-prodavaemye-tovary

4. Настройка безопасного соединения с Mysql. https://anonymousbi.wordpress.com/2013/12/15/pentaho-bi-server-5-0-1ce-mysql-installation-guide/

5. Установка Mondrian 4 для Pentaho 5.1 CE  / Mondrian 4, OSGi in Pentaho 5.1 CE http://www.willgorman.com/?p=58

• Вконтакте ()
• Google+
• Facebook ( )