Pentaho + PostgreSQL
Платформа Pentaho представляет собой свободное (бесплатное) комплексное BI решение, состоящее из набора аналитических продуктов, которые успешно развиваются с 2005 года.
Благодаря способности сочетать интеграцию данных с аналитической обработкой и качественной визуализацией данных, система позволяет аналитикам принимать верные решения, опираясь на собранную информацию о деятельности компании.
Pentaho является самым известным BI-решением с открытым исходным кодом и служит идеальным приложением для предприятий, которые хотят получить больше пользы от своих данных при минимальных вложениях.
Платформа Pentaho предлагается как в платной версии Enterprise Edition, так и в бесплатной свободно распространяемой версии Community Edition, позволяющей создавать базовые аналитические приложения с использованием дополнений-надстроек из магазина Pentaho Marketplace.
Основные задачи, решаемые линейкой продуктов Pentaho
Pentaho также совместим с мобильными клиентами, что позволяет пользователям работать на своих планшетах и смартфонах.
Pentaho Data Integration (DI)
Интеграция данных из разрозненных источников
Pentaho DI обеспечивает мощные возможности извлечения, преобразования/изменения и загрузки (ETL), используя инновационный подход, основанный на использовании метаданных.
Pentaho DI включает в себя визуальные инструменты для загрузки данных из произвольных источников – файлов CSV и Excel, любых СУБД, учетных систем (1C:Бухгалтерия и др.).
Основная задача Pentaho DI- свести все имеющиеся данные в единое хранилище (DWH) и предоставить бизнес-пользователям собранную и пригодную для анализа информацию.
Создание печатных отчетов
Конструктор отчетности Pentaho Reporting представляет собой набор средств с открытым исходным кодом, позволяющий создавать печатные (регламентные) отчеты на основе широкого спектра источников данных.
PostgreSQL. Хранилище данных на СПО
Зачастую данные для анализа необходимо заранее подготовить специальным образом (объединить данные из разнородных источников, структурировать, очистить от «грязи», сопоставить справочники) прежде, чем начинать их анализ. Инструмент для такой обработки данных был описан выше (Pentaho DI).
Получаемые в результате данные для анализа обычно сохраняются в специализированной базе данных, называемой Корпоративным Хранилищем Данным (КХД).
Одна из таких баз данных – PostgreSQL.
Учебник Pentaho
Что такое Pentaho BI?
Pentaho — это инструмент бизнес-аналитики, который предоставляет клиентам широкий спектр решений для бизнес-аналитики. Он способен создавать отчеты, анализировать данные, интегрировать данные, извлекать данные и т. Д. Pentaho также предлагает полный набор функций BI, которые позволяют повысить производительность и эффективность бизнеса.
Из этого урока в Пентахо вы узнаете:
Особенности Пентахо
Ниже приведены важные особенности Pentaho:
Pentaho BI suite
Pentaho BI Suite включает в себя следующие компоненты:
Составление отчетов
Отчетность Pentaho зависит от проекта JFreeReport. Это поможет вам удовлетворить ваши потребности в деловой отчетности. Этот компонент также предлагает публикацию отчетов по расписанию и по запросу в популярных форматах, таких как XLS, PDF, TXT и HTML.
Анализ
Он предлагает широкий спектр анализа, широкий спектр функций, включая представление сводной таблицы. Инструмент предоставляет расширенные возможности графического интерфейса пользователя (с использованием Flash или SVG), интегрированные виджеты панели мониторинга, портал и интеграцию с рабочим процессом.
Кроме того, Pentaho Spreadsheet Services позволяет пользователю просматривать, поворачивать и использовать диаграммы из MS Excel.
Сводки
Панель инструментов предлагает отчеты и анализ, которые предоставляют контент для панелей Pentaho. Дизайнер панели самообслуживания включает в себя обширные встроенные шаблоны и макет панели мониторинга. Это позволяет бизнес-пользователям создавать персонализированные информационные панели с минимальным обучением.
Сбор данных
Инструмент интеллектуального анализа данных обнаруживает скрытые закономерности и показатели будущей производительности. Он предлагает наиболее полный набор алгоритмов машинного обучения из проекта Weka, который включает кластеризацию, деревья решений, случайные леса, анализ главных компонентов, нейронные сети.
Он позволяет просматривать данные графически, программно взаимодействовать с ними или использовать несколько источников данных для отчетов, дальнейшего анализа и других процессов.
Pentaho Data Integration
Этот компонент используется для интеграции данных везде, где они существуют.
Богатая библиотека преобразований с более чем 150 готовыми объектами отображения.
Он поддерживает широкий спектр источников данных, который включает в себя более 30 платформ с открытым исходным кодом и проприетарных баз данных, плоских файлов. Это также помогает аналитике больших данных в интеграции и управлении данными Hadoop.
Кто использует Pentaho BI?
Pentaho BI — это широко используемый инструмент, который могут использовать такие профессионалы в области программного обеспечения, как:
Установите Pentaho в AWS
Шаг 1) Перейдите по ссылке и нажмите «Продолжить, чтобы подписаться».
Запуск OLAP-сервера на базе Pentaho по шагам
Настройка Pentaho BI Server
Выставляем переменные окружения:
Скачиваем и распаковываем свежую версию Pentaho Business Intelligence (biserver-ce-4.8.0-stable.zip). Я залил содержимое архива (папки administration-console и biserver-ce) в папку c:\Pentaho. Итак, распаковать — распаковали, но сервер пока еще не сконфигурирован. Этим мы сейчас и займемся…
Скачиваем MySQL-коннектор для Java (mysql-connector-java-5.1.23-bin.jar). Закидываем его в папку c:\Pentaho\biserver-ce\tomcat\lib.
По умолчанию Pentaho использует движок HSQLDB, т.е. создает и хранит все базы данных в памяти, в том числе тестовую базу sampledata. Это еще нормально для небольших таблиц (таких, как демо), но для боевых данных обычно движок меняют на MySQL или Oracle, например. Мы будем использовать MySQL.
Заливаем в MySQL базы hibernate и quartz. Обе они используется под системные нужды Pentaho. Качаем отсюда файлы 1_create_repository_mysql.sql и 2_create_quartz_mysql.sql. Импортим их в MySQL.
Теперь наш MySQL-сервер настроен как репозиторий Pentaho. Подконфигурируем Pentaho-сервер для использования этого репозитория по умолчанию. Для этого будем править следующие xml-ки:
Меняем driver, url и dialect на com.mysql.jdbc.Driver, jdbc:mysql://localhost:3306/hibernate и org.hibernate.dialect.MySQL5Dialect соответственно.
Меняем параметры driverClassName на com.mysql.jdbc.Driver, параметры url на jdbc:mysql://localhost:3306/hibernate и jdbc:mysql://localhost:3306/quartz соответственно в 2-х секциях, параметры validationQuery меняем на select 1.
В параметре меняем hsql.hibernate.cfg.xml на mysql5.hibernate.cfg.xml.
Удаляем весь ненужный хлам кроме Hibernate и Quartz.
5. Сносим папки \pentaho-solutions\bi-developers, \pentaho-solutions\plugin-samples и \pentaho-solutions\steel-wheels. Это тестовые данные, которые нам нужны в принципе не будет.
Удаляем или комментим все сервлеты секций [BEGIN SAMPLE SERVLETS] и [BEGIN SAMPLE SERVLET MAPPINGS], кроме ThemeServlet.
Удаляем секции [BEGIN HSQLDB STARTER] и [BEGIN HSQLDB DATABASES].
7. Удаляем каталог \data. Этот каталог содержит тестовую БД, скрипты для запуска этой БД и инициализации репозитория Pentaho.
Удаляем каталоги с именами SteelWheels и SampleData.
Удаляем или комментим строку:
Указываем наш solution-path: c:\Pentaho\biserver-ce\pentaho-solutions.
Настраиваем web-морду Pentaho
После всех манипуляций с конфигами, можно уже и подзапустить чего-нибудь. Идем в папку с нашим сервером и запускаем start-pentaho.bat или sh-шник, кому что нужно в его операционной системе. По идее, никаких ERROR’ов в консоли или логах томката быть уже не должно.
Итак, если все прошло гладко, то по адресу localhost:8080 отобразится форма входа:
Вводим стандартный логин/пароль (joe/password) и попадаем внутрь. Теперь нужно установить olap-клиент, который и будет, собственно, отображать наши к нему запросы. У платной версии Pentaho есть свой клиент, для CE мы использовали плагин Saiku.
Заходим в пункт Pentaho Marketplace верхнего меню, устанавливаем Saiku Analytics.
Тут пока всё, пришло время подготовки данных для аналитики.
Подготовка таблиц фактов и измерений
Pentaho — это ROLAP-реализация технологии OLAP, т.е. все данные, которые мы будем анализировать, хранятся в обычных реляционных таблицах, разве что, возможно, некоторым образом заранее подготовленных. Поэтому все, что нам нужно, — это создать нужные таблицы.
Скажу немного о предметной области, для которой нам нужна была статистика. Есть сайт, есть клиенты и есть тикеты, которые эти клиенты могут писать. Еще и с комментариями, да. И все эти тикеты наш саппорт разбивает по разным тематикам, проектам, странам. И вот нам было нужно, например, узнать, сколько тикетов по тематике «Доставка» пришло с каждого проекта из Германии за прошлый месяц. И все это разбить по админам, т.е. посмотреть кто из саппорта и сколько таких тикетов обработал и т.д. и т.п.
Все подобные срезы технология OLAP и позволяет проводить. Про сам OLAP подробно рассказывать не стану. Будем считать, что с понятиями OLAP-куба, измерений и мер читатель знаком и в общих чертах представляет себе, что это такое и с чем это едят.
Конечно, реальные данные реальных клиентов я в качестве примера разбирать не буду, а для этой цели воспользуюсь своим небольшим сайтом с футбольной статистикой. Практической пользы от этого немного, но в качестве образца — самое оно.
Итак, есть таблица players. Попробуем найти всякую-разную полезную и не очень статистику: количество игроков каждой страны, количество игроков по амплуа, количество действующих игроков, количество российских полузащитников в возрасте от 30 до 40 лет. Ну что-то вроде такого…
Итак, на чем я остановился? А, точно, подготовка таблиц. Тут есть несколько способов: воссоздать все таблицы руками и голыми SQL-никами или воспользоватся утилитой Pentaho Data Integration (PDI, также известная как Kettle) — компонент комплекса Pentaho, отвечающий за процесс извлечения, преобразования и выгрузки данных (ETL). Она позволяет установить соединение с определенной БД и с помощью уймы различных инструментов подготовить нужные нам таблицы. Скачиваем её. Закидываем mysql-коннектор в папку lib и запускаем PDI через Spoon.bat.
Сначала соберем сердце нашей статистики — таблицу игроков. Изначально ее структура выглядит как-то так:
Часть полей (name, surname, patronymic, full_name или birthplace, например) для статистики не нужна. Поля типа Enum (status, has_career) нужно вынести в отдельные таблицы измерений, а в основной таблице просто проставить айдишники внешних ключей.
Итак, приступим: File > New > Job (Ctrl+Alt+N). Откроется рабочая область задания. Переходим во вкладку View, создаем новое соединение с БД (Database connections > New): вбиваем сервер, БД, пользователя и пароль, даем соединению какое-нибудь имя (у меня fbplayers) и сохраняем (c:\Pentaho\biserver-ce\pentaho-solutions\jobs\fbplayers.kjb).
Создаем трансформацию (File > New > Transformation, Ctrl+N). Сохраним ее под именем prepare_tables.ktr. Точно так же как и с заданием (job), добавляем коннект к БД для трансформации. Готово.
Переходим во вкладку View и раскрываем раздел Input. Выбираем инструмент Data Grid. Он хорошо подходит, если нужно вынести какие-то поля с небольшим количеством возможных вариантов в отдельные связанные таблицы. Итак, вытягиваем Data Grid в рабочую область и открываем ее для редактирования двойным кликом. Вбиваем название данного шага трансформации (Player Status), начинаем задавать структуру данной таблицы (вкладка Meta) и сами данные (вкладка Data). В структуре имеем 2 поля:
1. Name — id, Type — Integer, Decimal — 11
2. Name — status, Type — String, Length — 10.
Во вкладке Data вбиваем 2 строки: 1 — active, 2 — inactive.
Переходим в раздел Output и вытягиваем оттуда элемент Table Output. Двойной щелчок, задаем имя элемента как Player Status Dim. Коннект должен отобразиться в следующей строчке. В поле Target Table пишем название таблицы, которая будет создана в БД для хранения статуса игроков: player_status_dim. Ставим чекбокс Truncate Table. Связываем входной и выходной элементы: щелкаем по Player Status и с зажатой кнопкой Shift тянем мышь на Player Status Dim. Связь должна появиться в виде стрелки, соединяющей эти элементы.
Такую же штуку нужно провернуть с флажком для карьеры (Player Career):
1. Name — id, Type — Integer, Decimal — 11
2. Name — has_career, Type — String, Length — 3.
Во вкладке Data вбиваем 2 строки: 1 — no, 2 — yes.
Точно также собираем выходную таблицу Player Career Dim.
Теперь вынесем дату рождения игрока в отдельную таблицу измерений. По большому счету, Pentaho позволяет использовать дату прямо в таблице фактов, изначально мы так и делали с данными нашей предметной области. Но возникло несколько проблем:
Итак, по этим причинам мы переделали нашу изначальную структуру и создали таблицу со всеми уникальными значениями времени, оставив минимальным уровнем часы. Для нашего тестового примера такой детализации не будет, будут только года, месяцы и дни.
Создадим новую трансформацию (initial_sql). Не забываем про коннект. Из коллекции элементов выбираем Scripting > Execute SQL Script. В него пишем сборщик дат:
Тут же, в этой трансформации, создаем еще 2 SQL-скрипта — для создания таблицы Player Career Dim и Player Status Dim:
Приступаем к основной части нашей миссии — сборке таблицы фактов. Создаем трансформацию (player_fact.ktr). Про коннект не забыли, правда? Из вкладки Input кидаем Table Input, из Output — Table Output соответственно. В Table Input пишем клёвый SQL-ник:
В Table Output указываем имя таблицы — player_fact. Связываем исходную и результирующую таблицы стрелкой.
Опять идем в наш job. Из вкладки General добавляем новую трансформацию. Открываем ее, даем имя Prepare Tables и указываем путь до нашей сохраненной трансформации prepare_tables.ktr.
То же самое делаем с трансформациями Initial SQL и Player Fact.
Закидываем на форму кнопку Start и соединяем элементы в следующей последовательсти: Start > Initial SQL > Prepare Tables > Load Player Fact.
Теперь можно попробовать запустить задание. В панели инструментов жмакаем зеленый треугольник. Если наши руки были достаточно прямы, то около каждого из наших элементов мы увидим зеленую галочку. Можно зайти на свой сервер и проверить, что таблички действительно созданы. Если что-то пошло не так, то лог покажет все наши грешки.
Создание куба и публикация его на сервере
Теперь, когда у нас есть подготовленные данные, займемся, наконец, и OLAP-ом. Для создания olap-кубов у Pentaho есть утилита Schema Workbench. Скачиваем, распаковываем, закидываем mysql-коннектор в папку drivers, запускаем workbench.bat.
Сразу же заходим в меню Options > Connection. Вводим наши параметры подключения к БД.
Приступаем: File > New > Schema. Сразу сохраним схему (у меня fbplayers.xml). Зададим имя схеме.
Через контекстное меню схемы создаем куб. Назовем его именем сущности, статистику по которой будем считать, т.е. Player.
Внутри куба указываем таблицу, которая будет у нас таблицей фактов: player_fact.
Если выбрать куб Player, то красная строчка внизу правой области подскажет нам, что в кубе должны быть заданы измерения (Dimensions), т.е. те параметры, по которым будут производиться срезы данных.
Есть 2 способа задать измерение кубу: непосредственно внутри него (через Add Dimension) и внутри схемы (Add Dimension у схемы плюс Add Dimension Usage у самого куба). Мы в своей статистике использовали второй вариант, т.к. он позволяет одно измерение применить к нескольким таблицам фактов сразу (к нескольким кубам). Эти кубы мы потом объединили в виртуальный куб, что позволило нам выводить статистику по нескольким кубам одновременно.
В нашем тестовом проекте мы также воспользуемся вторым способом, разве что не будем создавать виртуальные кубы.
Итак, добавим первое измерение (по стране). Создаем измерение схемы, даем ему имя Country. Внутри него уже есть 1 иерархия, ей зададим имя Country_Hierarchy. В эту иерархию добавляем таблицу, которая хранит значения измерения Country, т.е. country.
Это моя обычная mysql-таблица со списком стран следующей структуры:
После этого добавляем в иерархию 1 уровень (Add Level). Назовем его Country_Level и свяжем таблицу фактов с этой таблицей измерения: поле table выставляем в country, column — в id, nameColumn — в name. Т.е. это значит, что при сопоставлении ID страны из таблицы фактов ID страны из таблицы country в качестве результата вернется название страны (для читабельности). Остальные поля в принципе можно и не заполнять.
Теперь мы можем вернуться к кубу Player и задать ему только что созданное измерение (через Add Dimension Usage). Задаем имя (Country), source — это наше созданное измерение Country (в выпадающем списке оно и будет пока единственным), а поле foreignKey — main_country_id, т.е. это говорит Pentaho, что когда он видит какой-то main_country_id в таблице фактов, он обращается к таблице измерения (Country) по указанному столбцу (id) и подставляет на место main_country_id значение name. Как-то так…
Осталось только указать кубику, что мы собственно хотим агрегировать-то )) Добавляем в куб меру (Add Measure). Зададим ей имя PlayerCount, агрегатор — distinct-count и поле, по которому будем агрегировать — player_id. Готово!
Давайте остановимся на этом ненадолго и проверим, что мы тут наколдовали. Запускаем веб-морду Pentaho: localhost:8080/pentaho (не забываем про start-pentaho.bat). Заходим в File > Manage > Data Sources. Жмем кнопку добавления нового источника. Выбираем тип — Database Table(s). Самое важное, что нам тут нужно — это создать новое соединение (Connection). Задаем имя (Fbplayers) и вбиваем наши данные для доступа к БД. После сохранения Connection’а, жмем везде Cancel, больше нам тут ничего не нужно.
Далее нам нужно опубликовать созданную схему на сервере Pentaho: File > Publish. Задаем урл: localhost:8080/pentaho и вводим пароль на публикацию. Этот пароль задается в файлике c:\Pentaho\biserver-ce\pentaho-solutions\system\publisher_config.xml. Установим этот пароль в 123, например, юзер и пароль стандартные — joe/password. Если все нормально, то после должно отобразиться окно выбора папки, куда сохранять нашу схему. Вводим имя соединения, которое мы создали на прошлом шаге (Fbplayers) в поле «Pentaho or JNDI Source». Создадим папку schema и сохраним файл в нее. Если все прошло нормально, мы должны увидеть радостное окошко:
Пойдемте глядеть! Заходим на веб-морду, открываем Saiku, выбираем наш куб из выпадающего списка. Видим появившиеся измерение Country и меру PlayerCount. Перетягиваем Country_Level в поле Rows, PlayerCount — в Columns. По умолчанию, на панели Saiku вжата кнопка автоматического выполнения запроса. Обычно стоит ее отжать перед натаскиванием измерений и мер, но это не принципиально. Если автоматическое выполнение отключено, жмем кнопку Run. Радуемся!
Но если вдруг вместо красивых данных вы увидели сообщение вроде “EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost”, не волнуйтесь — это бывает, просто нажмите Run еще раз-другой.
Щелчком по стрелочкам на кнопке меры, мы можем отсортировать результирующую выборку по убыванию или возрастанию.
Пока у нас немного данных, давайте посмотрим, что у нас еще доступно. Можно ограничить выводимые данные, скажем, только по странам на букву А:
Можно посмотреть графики. Обычно это красиво, если в выборке немного данных.
Можно посмотреть статистику по выборке: минимальный, максимальный показатель, среднее значение и т.д. Можно выгрузить все это достояние в xls или csv. Также накиданный нами с помощью конструктора запрос можно сохранить на сервере, чтобы потом вернуться к нему.
Итак, суть понятна. Давайте создадим еще пару измерений. В принципе, измерения по статусу игрока и наличию карьеры ничем не отличаются от измерения по стране. Да и результатом и в том, и в другом случае будут всего 2 строки (active/inactive и has/no).
Гораздо интереснее обстоит дело с иерархией типа Дата. Ее мы сейчас и создадим. Возвращаемся в Workbench, добавляем новое измерение (BirthDate). Ему вместо StandardDimension выставляем параметр TimeDimension. Иерархия здесь уже есть. Добавляем таблицу измерения — player_birth_date_dim.
Добавляем первый уровень — Year. Задаем значения table = player_birth_date_dim, column = id, levelType = TimeYears. Для данного уровня добавляем свойство Key Expression со значением `year`.
Добавляем второй уровень — Month. Задаем значения table = player_birth_date_dim, column = id, levelType = TimeMonths. Для данного уровня добавляем свойство Key Expression со значением `month`, Caption Expression со значением “CONCAT(`year`, ‘, ‘, MONTHNAME(STR_TO_DATE(`month`, ‘%m’)))”.
Добавляем третий уровень — Day. Задаем значения table = player_birth_date_dim, column = id, levelType = TimeDays. Для данного уровня добавляем свойство Caption Expression со значением “CONCAT(LPAD(`day`, 2, 0), ‘.’, LPAD(`month`, 2, 0), ‘.’, `year`)”.
Добавляем созданное измерение в куб, в качестве foreignKey указав bith_date_id.
Публикуем. Попробуем разбить всех игроков по году рождения.
А теперь добавим к параметру «Год» еще и параметр «Месяц». Pentaho разобьет каждый из годов на месяцы и посчитает количество игроков, рожденных в определенный месяц каждого года. По умолчанию, отображаются только данные по месяцам, но если отжать в тулбаре кнопку «Hide Parents», то можно увидеть и суммарное количество игроков за данный год.
Но основная сила Pentaho, да и всего OLAP, собственно, не в таких простых выборках, а в срезах по нескольким измерениям одновременно. Т.е. например, найдем количество игроков каждой страны, рожденных после 1990 года.
С увеличением количества метрик запросы могут становиться все более сложными и точечными, отражающими конкретную статическую потребность.
На этом наша длинная-длинная статья завершается. Надеюсь, данный туториал поможет кому-то по-новому взглянуть на решения OLAP или, быть даже может, ввести эти решения в своих организациях.
