ProcessMaker - Pentaho

From ProcessMaker
Jump to: navigation, search

Contents

Pentaho Reporting is an open source business intelligence suite providing advanced reporting, analysis and data mining, which is very useful when analyzing the efficiency and productivity of your organization using ProcessMaker. ProcessMaker Enterprise Edition provides an integration with Pentaho Reporting, so that the power and flexibility of Pentaho Reporting can be accessed inside the ProcessMaker interface. Custom reports generated by Pentaho are available under the Enterprise Edition's DASHBOARD and REPORTS menus.


NOTE: The plugin for now is only compatible with Pentaho BI server 3.5.2 Community and Enterprise Editions.


How the Plugin Works

The ProcessMaker Enterprise Edition includes a plugin to integrate with Pentaho Reporting, which must be separately installed and configured. The plugin allows ProcessMaker to connect to either the Community Edition or the Enterprise Edition of Pentaho Reporting.

The Online Analytical Processing (OLAP) is a popular technology, that can improve significantly your business analysis. It was historically characterized as a tool hard to implement and inaccessible for its high cost. Processmaker has included OLAP in its enterprise edition, creating a solution that sets multi-dimentional analysis to everyone. The OLAP database is a data cube derived from a fact table created inside the relational database. OLAP's objective is to group data from all the cases inside a workspace in order to make them easier to analyze.

Cubo2PM.jpg

Requirements

Check the recommended system requirements (in section 4.3 Recommended Hardware) to run a Pentaho server. Due to the fact that Pentaho is a Java application which uses a great deal of system resources, it is recommended to install Pentaho on a separate server from ProcessMaker.

  1. Install Sun Java JDK version 6u12, which can be downloaded from http://java.sun.com
  2. Install the Pentaho BI Suite Community Edition or Enterprise Edition, which can be found at http://sourceforge.net/projects/pentaho. Download the file biserver-ce.
  3. Processmaker templates.

Setup

Installing Pentaho

Installing in a Linux Server.

Installing JDK

  • Download the jdk-6u12-linux-i586-rpm.bin from the link: JDK Installer
  • Change permissions to the file: jdk-6u12-linux-i586-rpm.bin 
 chmod 777  jdk-6u12-linux-i586-rpm.bin
  • Execute the bin file 
 ./ jdk-6u12-linux-i586-rpm.bin

Unpacking PENTAHO

  • Download the biserver-ce-3.5.2.stable.tar.gz file from this link: Pentaho BI Suite
  • Copy the biserver-ce file to the /opt directory
  • Unpack the biserver-ce file. 
 tar –xvzf  biserver-ce-3.5.2.stable.tar.gz
  • In the /opt directory we now have two files: administration-console and biserver-ce.

Configure start scripts

Export variables: PENTAHO_JAVA_HOME y JAVA_HOME 

 echo export PENTAHO_JAVA_HOME=/usr/java/latest >> /root/.bashrc
 echo export JAVA_HOME=/usr/java/latest >> /root/.bashrc

Nota: To configure the two services to execute every time the server boots go to [1]

Starting the BI Platform

Services for the BI Server. 

./start-pentaho.sh    To start the BI Server, run the start-pentaho script in the /biserver-ce/ directory.

./stop-pentaho.sh     To stop the BI Server, run the start-pentaho script in the /biserver-ce/ directory.

Services Pentaho Administration Console. 

./start-pac.sh     To start the Pentaho Administration Console, run the startup script (onLinux) 
                   in the /biserver-ce/administration-console/ directory.

./stop-pac.sh      To stop the Pentaho Administration Console, run the startup script (onLinux) 
                   in the /biserver-ce/administration-console/ directory.


Configuring the BI platform with the Administration Console

  1. Open a Web browser and type in the Web or IP address of the Pentaho Administration Console server, which is http://localhost:8099/admin by default.
  2. Type in your administrator credentials, then click Login. The default credentials are admin for the user, and password for the password.
  3. You are now logged into the Pentaho Administrator Console and ready to finish configuring the BI Platform.


Log into the Pentaho user console

  1. Open a Web browser and type in the Web or IP address of the Pentaho server, which is http://localhost:8080/pentaho/ by default.

You'll see an introductory screen with some Pentaho-related information and a Login button in the center of the screen.

  1. Click Login. The login dialog will appear.
  2. Select Joe from the user drop-down box, and type in password into the password field, then click Login.
  3. You are now logged into the Pentaho User Console and ready to start creating and running reports.

Copy the ProcessMaker templates

  • Download these two files: 
 # processmaker-dashboard.html
 # template-dashboard-processmaker.html
  • Copy the two files in the directory: \biserver-ce\pentaho-solutions\system\pentaho-cdf

Setup the publisher password

  • Edit in the file: biserver-ce/pentaho-solutions/system/publisher_config.xml the following: 
  <publisher-config>
    <publisher-password>password</publisher-password>
  </publisher-config>

Domain Name in the base url

  • Edit in the file: /biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml 
 <context-param>
   <param-name>base-url</param-name>
   <param-value>http://localhost:8080/pentaho/</param-value>
 </context-param>

Installing Pentaho in a Windows Server

Installing JDK

  • Download the JDK version jdk-6u21-windows-i586 from this link: JDK
  • Install the jdk-6u21-windows-i586.exe file in the Windows server.

Unpacking PENTAHO

  • Download the Pentaho Bi Suite. The version that is compatible with ProcessMaker is biserver-ce-3.5.2.stable and can be downloaded from this link: Pentaho BI Suite.

Look for the file in the directory Business Intelligence Server/3.5.2-stable

  • Unzip the pentaho file under a /opt directory. Two directories will be created:
    • administration-console
    • biserver-ce

Copying the templates

  • Download these two files: 
 # processmaker-dashboard.html
 # template-dashboard-processmaker.html
  • Copy the two files in the directory: /biserver-ce/pentaho-solutions/system/pentaho-cdf

Setup the publisher password

  • Open the file publisher_config.xml that is in the directory: biserver-ce/pentaho-solutions/system/
  • Change the publisher-password <Password_name> as password 
    <publisher-config>
    <publisher-password>password</publisher-password>
    </publisher-config>

Domain name in the base url

  • Open the web.xml file that is in the directory: /biserver-ce/tomcat/webapps/pentaho/WEB-INF/
  • Change the url in the param-value <Server_name> 
    <context-param>
    <param-name>base-url</param-name>
    <param-value>http://<Server_Name>:8080/pentaho/</param-value>
    </context-param>

Running the services in bisever-ce

  • Go to the directory /biserver-ce, to get the services running
  • Double click on the file set-pentaho-java
  • Double click on the file start-pentaho
  • In case you need to stop the services you should double click on the stop-pentaho

Running the services in administration-console

  • Go to the directory /administration-console, to get the services running
  • Double click on the file set-pentaho-java
  • Double click on the file start-pac
  • In case you need to stop the services you should double click on the stop-pac

Servers up

  • Go to the http://<Server_name>:8080 Pentaho User Console
  • Go to the http://<Server_name>:8099 Administration Console

Configure the Pentaho plugin in ProcessMaker

  • To configure the Pentaho plugin the enterprise plugin has to be installed, see the instructions.
  • Click on the Admin Tab.
  • Click on the Pentaho Reports Admin. Pentahoinst.png
  • In the screen a list will be shown like the one in the figure bellow.

Pentaho.png

  • This plugin has four options:
  1. Configuration
  2. Roles Manager
  3. Show JNDI Info
  4. Rebuild

Configuration

  • Click on the Configuration Button.

Pentahoconfig.png

Now you can see a panel like the image above with configuration for Pentaho, that is divided en two parts:

Pentaho User Control. This part has three fields:

  1. Pentaho Server: in this field type the same url that is in the web.xml file, http://<Server_name>:8080/
  2. Pentaho Admin User Name: in this field type the pentaho user,
  3. Password: in this field type the pentaho user's password

Pentaho Administration Console. This part has five fields:

  1. Pentaho Administration Console: in this field type the same url from the Pentaho Administration Console, http://<Server_name>:8099/
  2. Pentaho Administration User Name: in this field type the pentaho administration username.
  3. Password: in this field type the pentaho user's password
  4. Pentaho Publisher Password: in this field type the same password that is in the publisher_config.xml file.

Roles Manager

  • Inside the Pentaho Connector Plugin there is a built-in roles management feature.
  • To see the roles, click on the Roles Manager. By default there are two main roles PH_ADMIN and PH_USER. Whenever a new report in the solution repository is synchronized with the Pentaho plugin it is assigned to the role PH_ADMIN. Manual assignment is required for other roles.

Pentaho users2.png

  • To assign new reports or dashboards click in the Permissions section. A dialog will show up a screen with a lists of reports, where any report can be added if it has been already synchronized.

Pentaho users3.png

Show JNDI Info

Rebuild

Build a cube in PM to be consumed by Pentaho

The OLAP cube works as puzzle cubes in games do. In the puzzle game you have to order by the colors and in data warehouse you are supposed to organize the data in tables or relationships. The puzzle cube is 3-dimensional and the OLAP cubes are n-dimensional, that is why they are named hypercubes. An OLAP cube will have data from a certain variable(the one that is going to be analyzed), providing a logical data view given by the information system towards the data warehouse. The data analysis is based on the hypercube dimension and it is named multidimensional analysis. Multidimensional analysis is a process that groups data in two basic categories which are data dimension category and measurement category.

The ProcessMaker Cube

The analytical online process (OLAP) is a technology that is becoming more popular and it can improve significantly business analysis. OLAP has been known as a tool hard to implement and not accessible due to its high cost. ProcessMaker Enterprise Edition has used the OLAP coming up with a solution that makes the multidimensional analysis accessible to all.

The main objective for OLAP to be used in ProcessMaker to get information about its performance. To give information about the performance, case's data from a workspace will be grouped to be analyzed. The OLAP database is a data cube derived from a fact fable created inside the relational database.

Dimensions

A dimension is a primary organizer of measure and attribute information in a cube. The cube dimensions are attributes related to the variables. They are complementary information, that is needed for the data presentation to the users, such as descriptions, names, time ranges, etc.

The dimension are attributes related to the variables, used to index, order, group or resume their values. The dimension has minor granularity, taking the values a set of elements with less detail than the variables.

Variables

This are also called 'management indicators', because they are the data that is being analyzed. The variables represent a measurable part of the objects or event that are going to be analyzed. Often, the variables are represented by detailed numeric values for each instance of the object or measured event.

Reports

To see the Reports, click on REPORTS in the ProcessMaker top menu. With the plugin there are built in custom reports to analyze the ProcessMaker's performance.

Pentaho menu.png

Pentaho reports for ProcessMaker

Cases by process

This Report shows all the information about the cases in a grid and bar graph. The bar graph shows the number of cases that have been created in every process in the workspace, comparing the closed and opened cases.

PR casesbyprocess.png

Compare Cases per task per Process

This report has three graphs: a pie chart that indicates the task advance percentage within the process, a bar graph that shows how the users are handling the cases (if they have overdue cases) and a line graph that shows the relationship between time and cases.

PR casestaskprocess.png

Creating Pentaho reports for ProcessMaker

After installing Pentaho's plugin in ProcessMaker, the plugin creates:

  • A Data Base connection by default named wf_workflow

DBConnection.png


  • While creating a database user for accessing ProcessMaker´s WorkFlow database (ph_workflow), you can also create new Database Connections.

NewDBConnection.png

Find the details for this Data Base user in ProcessMaker→ ADMIN→Pentaho Reports Admin→ Show JNDI Info:


JNDIInfo.png


It can be reused for creating new data sources.

  • Finally creates a Pentaho user named “wf_workflow”, used to share Pentaho Reports on ProcessMaker “Reports” tab.

CreatePenathoUser.png


Rebuild this information by clicking on IconRebuild.png directly from ProcessMaker:

RebuildInformation.png

Creating a DataBase Connection

A Database connection is used to create a data source and it can have one or more data sources. There are two ways to create a new Database Connection in Pentaho:

  • Using Pentaho´s Administration Console.
  • Using Pentaho´s User Console

Using Pentaho´s Administration Console

Step 1: Login to the Administration Console on your server´s port 8099.


Step 2: Go to Administration→Database Connections.


AdministrationDataBaseConnection.png


Step 3: Create a new Database Connection using the plus symbol

CreateDBConnection.png


Step 4: Configure the New Database Connection


ConfigureNewDB.png

Fill in the information:

A. Database Connection Name, choose a name that suits you.

Driver Class: The database driver name it's selected depends on the type of database it's accessing. For example, org.hsqldb.jdbcDriver is a sample driver name for a HSQLDB database.

B. Data Source User Name - ask the administrator for permission to access this if it’s required to access to the database.

C. Data Source User Password - ask the administrator for permission to access this if it is required to access the database.

D. Type or select the URL from the list. This is the URL of the database; for example, jdbc:hsqldb:hsql://localhost/sampledata. JDBC establishes a connection to a SQL-based database and then sends and processes the SQL statements.


Step 5: Test Connection.

Click Test. A success message appears if the connection is established.


Step 6: Save your Entries

Click OK to save your entries.

Using Pentaho´s User Console

When the Report is viewed or created in Pentaho´s User Console and it has sufficient privileges, a database connection can be added or edited when trying to add a new data source:

Step 1: View an existing report

ExistingReport.png


Step 2: Add Add.png , edit Edit.png or delete Delete.png a database connection


AddEditDelete.png


Follow the steps Using Pentaho´s Administration Console.

Creating Simple Reports and publish them in Pentaho

Step 1:Create a New Report

Click on New Report after login into Pentaho’s Users Console

PentahologinNewReport.png


Or go to File → New → New Report:

PentahologinNewReport2.png


or Click on the IconNewReport.png icon on the Tool Bar.


Step 2: Select or create a new data source

DataSource.png


  • Select a data source: Includes creating or editing a query to have an acces to the database

NewDataSource.png


You can set the Types for each Field/Column and determine whether you need any aggregation. In addition, you can rename your Column name as well.

Query of this example:


<source lang="sql"> SELECT APP_TAS_TITLE, APP_STATUS , APP_CURRENT_USER, count(APP_UID) 

      FROM APP_CACHE_VIEW
  group by APP_TAS_TITLE,

APP_STATUS, APP_CURRENT_USER 07' </source>

  • Apply a template: Set a template for displaying the Report, press Next to go to the next Step:

TemplatePentaho.png

Pentaho Community version has 6 Templates to be used:

  • Summer Theme Template

SummerTemplate.png

  • A basic template

BasicTemplate.png

  • Winter Theme Template

WinterTemplate.png


  • Spring Theme Template

SpringTemplate.png

  • Autumn Theme Template

AutumnTemplate.png

  • Pentaho Theme Template

PentahoTemplate.png

Step 3: Making Selections

It's possible to set Groups, Details and Filters for the Report by dragging the items of the Query:


SettingGroups.png

Step 4: Customize Selections

It's possible to setup the Format, Alignments, Constraints and Sorting of the columns/fields of the query.


SCustomizeGroups.png

Step 5: Report Settings

Pentaho allows to setup page orientation and paper settings. ReportSettings.png


And add descriptions, headers, footers and page options:

PentahoOptions.png


Step 6: Preview the Report Click on OkButton.png button to preview the report


PreviewReport.png

Step 7: Save your report Save the report by clicking on the save button on the toolbar or by going to File→Save or Save as:


SaveReport.png

Note: Save into the folder /workflow. Pentaho plugin look for this folder to check which reports has been shared from Pentaho and to show them into ProcessMaker

Setting Shared Properties

To make Pentaho report published and available in ProcessMaker's Report Tab Folder follow the steps below:

Step 1: Go to Reports Options/Properties

Refresh the browser, go to workflow→Your Report (on files) →click on ButtonReport.png options Button→Share


ReportProperties.png

Step 2: Add ProcessMaker User, created by Pentaho´s ProcessMaker Plug-in:

AddProcessMakerUser.png


Step 3: Grant User Permissions over Pentaho Report

PermissionReport.png

Step 4: Go to ProcessMaker´s Reports Tab

The report will be available under ProcessMaker's Report Tab


ReportTabPM.png

Retrieved from "http://wiki.processmaker.com/index.php?title=ProcessMaker_-_Pentaho&oldid=23239"
Personal tools
Namespaces

Variants
Actions
Navigation
translations
Toolbox