Pentaho 8.3 CE

The third release of Pentaho under Hitachi was made on July instead of the usual date that was October. Pedro Alves describes lots of improvements, specially to PDI. The community version can be downloaded from SourceForge.

The Client Tools folder contains the superb ETL (pdi-ce-8.3.0.0-371.zip). And the Report Editor (prd-ce-8.3.0.0-371.zip), also the metadata editor (pme-ce-8.3.0.0-371.zip) and tools for multidimentional database design such as the schema workbench editor (psw-ce-8.3.0.0-371.zip) or aggregation designer (pad-ce-8.3.0.0-371.zip).

The Web Server to assign access rights to users, schedule automatic runs from ETL or reports and design dashboards is directly under the server folder (pentaho-server-ce-8.3.0.0-371.zip).

Edited: Remember that you only need to unzip the files to run the tools. The only requirement is java. To install the OpenJDK 8 version it you can:

sudo apt-get install openjdk-8-jdk

or:

  1. Browse to https://adoptopenjdk.net/ or https://openjdk.java.net/install/ and download a version 8 tar.gz.
  2. Uncompressit into /opt:
    sudo tar -C /opt -zxf ~/Downloads/OpenJDK8U-jdk_x64_linux_hotspot_8u222b10.tar.gz
  3. Change its user:
    sudo chown -R root:root /opt/jdku8222-b10
  4. Add it to your path:
    sudo gedit /etc/profile
    adding at the end two lines:
    export JAVA_HOME=”/opt/jdku8222-b10
    export PATH=$JAVA_HOME/bin:$PATH
Advertisements

3 Real OSS Human Resources Management Systems

In a LinkedIn post someone asked for a recommendation for a Human Resource Management System, it will be used by 300 persons and needed support for life, career and succession plan, performance and work environment evaluations, administration and control in training and educational programs. For payroll they just need an interface, as Mexico’s parametrization is a nightmare and they are outsourcing it. They are consultants so they also know they need system generated data for management reports and indicators to implement successful processes.

But they mainly got meeting proposals from enterprise software and proprietary developed suites providers. So after reading several articles and comparisons from sites like predictiveanalyticstoday, softwareadvice, softwaresuggest, I decided to filter here three Open Source Software (OOS) solutions. They are:

1. Sentrifugo

Developed in California by Sapplica Inc. for human resources management. It offers a system with:

  • Recruitment processes. Background check, interview schedule, document area for certifications and uploading those files.
  • Self-service portal for employees to update personal data, experience, education, holiday calendar, assignment to projects and can be used for onboarding.
  • Employees can apply for vacations or sick leave and responses from managers and authorizer are logged.
  • Performance evaluation in different modalities. Including feedforward.
  • Board and birthday notifications with organization’s relevant documents.
  • Employee service request of software installation or assets (resources, such as computers, phones with invoice number, provider details, warranty status, images) and their assignment to employees.
  • Expenses per trip, receipts and deposits.
  • Attendance record. With a simple and effective way to enter time, track activities and break time.
  • Record of disciplinary actions.
  • Hierarchy display, Unit and Department only.
  • Exit and separation management. Interview, department manager, checklist.
  • Listings are used, not reports. User activity, attrition rates, employees by department, analytical dashboards. Employee request, vacancies and hiring.
  • The configuration of new simple workflows can be done considering executing and authorizing persons; Imports employee data with Excel. It generates important activity logs for auditing and productivity analysis.

Menus are: Dashboard. Self-Service (Personal Details, Leaves -vacations are read only for employees-). Service request (Service request for other departments), HR (Roles & Privileges, Leaves, Exit procedure, Contacts), Appraisals (Manager, Self-Assessment, Team, History, Feed Forward). Recruitment (Requisitions, Candidates, Interviews, Selected/rejected), Background check, Organization (Units, Departments, Structure, Hierarchy, Announcements, Policy documents), Expenses (Category, Receipts, Travel, Advances, My Expenses). Disciplinary (Incidents, Team Incidents), Time (by Project).

For user permissions in the system, there are roles that control access to each option of the menus: view, add, edit, delete.

It is a web application developed with jQuery, PHP Zend with MySQL as a database. Its repository is in Github and Sourceforge. There is a Demo site, blog and a website entry that summarizes its functionality.

It’s the most complete application of the three reviewed here but still, it doesn’t support life, career or educational needs as originally requested. Remember that it has no payroll and the user interface is in only in English. It was last updated in April 2017.

Overview video: http://www.youtube.com/watch?v=GoLp3BZiu2c [1:10 min].

Screens (click for larger view) or access the Demo site.

2. Odoo

The Odoo community is the most active of the three presented here, they operate forums, tutorials on YouTube, chats, documentation on how to build modules (extensions). The community version is controlled by a Belgian association that guarantees its free operation. The first source code publication was made in 2005. Current version of this ERP, branch 12 offers in human resource management:

  • Recruitment.
  • Self-service portal for employees with contact details, contracts, information for onboarding.
  • Vacation and absence management. Assist Control.
  • Expense management. And its report, approval or rejection.
  • Documents by folders, categories and labels.
  • For hierarchy, it shows in profile of the official, his manager and subordinates.
  • Surveys. Email integration. Information board. Listings (no reports).

Companies and programmers had provided modules free of cost but not all of them work with the latest version. As an example personnel performance evaluation procedures can be added for formal interview, skills qualification, self-evaluation, exit interview, scheduling of evaluations, formats with questions for appraisals by position type. Employee insurance tracking. Educational or eLearning modules.

There are also modules with advanced functionality that requires monthly payment like Departmental HR Boards. Studio that allows to modify screen design and reports by the user. Reports eg. vacancies per business unit. Dental health monitoring. Help Desk for technical support. Review of KPI’s and KRA’s; Workflow management. Activities can be designed to perform automatic actions. There is also a module for Training Management. And there are companies that offer support contracts in lots of countries.

As an ERP (Enterprise Resource Planning) it can extend with modules for Email (mass mail for marketing), Website, Online sales (eCommerce), point of sale, customer service (CRM), Warehouse Management, Purchasing, Logistics (Delivery), Accounting and Billing, Manufacturing and Project Management.

Video: https://www.youtube.com/watch?v=aUv9kYhtIY4 [3:21 min].

Some screens are shown (click on them to see detail) or visit the Demo site:

Standard menus are: Employee (Details, Contracts, Challenges, Department. Config). Recruitment (Job Positions, Applications, Resumes and Letters, Reports, Config). Leaves (View by week, month, year; My Leaves -requests-, Managers, Reporting). Surveys. Expenses Help Desk, Fleet management. Documents.

It’s a Web application with JavaScript in the client and Python in the server, with PostgreSQL as database. The project started as TinyERP in 2005, on 2010 it changed to OpenERP, and it’s last update was in October 2018. The repository is at Github, has Documentation, Modules Catalog, Guides 1 2 3 and Demo site. Odoo received BOSSIE awards in 2014, 2015 and 2016.

3. OrangeHRM

The Secaucus company in New Jersey published the first OrangeHRM version in 2006. And it currently represents one of the main personnel management platforms, serving more than 3 million users in the cloud. It offers:

  • Recruitment processes.
  • Self-service portal for employees (onboarding).
  • Training.
  • Performance. Evaluation by KPI and monitoring.
  • Holidays and permits; Time and attendance.
  • Information and salary history.
  • Reimbursement-Expenses Report.
  • Email Integration, Mobile Payroll Application. Information board and customizable reports (MIS).

The advanced options in the paid version are: Reports. Benefits (payroll and insurance plan). Training. Travel expenses tracking. Projects. Document and notification manager. Disciplinary follow-up. Import data. Configurable fields. Logs. Failure reports and advanced workflow (advanced business with a rule-based engine) but only on a Cloud version at http://orangehrm.orangehrmlive.com.

Video: https://www.youtube.com/watch?v=1oPoVLcnO8g [1:46 min]

These are screenshots of the application:

Menus are: Admin (Users; Job Titles, Pay Grades, Employment status, Shifts; Organization structure, Qualifications, email). Employee info. Leaves (Entitlements, Reports, Config, Assignment). Time sheets, Recruitment (Vacancies, candidates). Performance (Config, KPI, Trackers, Reviews). Dashboard, Directory.

OrangeHRM won the BOSSIE award in 2008, 2012, & 2013. There is Demo site, Blog. The programming is in PHP 7.3 and uses MariaDB 10 or MySQL 5 as a database. The code in SourceForce corresponds to version 4.3.2 (July 2019), the commercial version is 6.0.

Conclusion

Sentrifugo its the web application with most functionality available out of the box, its functionality can be increased by modules created by the community or your own developers. It almost fulfill the original requirements of the LinkedIn post but those ones are very advanced and require the coordination of several modules and that has not been coded yet. It main disadvantage is that it has not been updated on several years and for that its just my second recommendation.

OrangeHRM has a simple and elegant design, it’s open source version lacks benefit (insurance), training and expenses control that do exist in its cloud version. It lacks a visible community but that can be said for most OSS companies. Those are considerable warnings. Maybe the proprietary/cloud version its worth a pilot project but it would be my third recommendation.

That makes Odoo my first option. You only need a careful selection of modules needed to complete your process and requirements. Even with the investment to acquire non free modules its an excellent option to host on site, in a private cloud, or use their inexpensive cloud services.

What I see in Open Source Software in 2019

This could be a short rant in a Twitter post. But this will let me start writing here again.

It seems that OSS developers took a decision about two years ago and the industry too. Applications stopped being cool, communities languished, forums reduced its activity, in part for the consolidation of solutions, the decision of promoting the enterprise versions instead of the community ones and also in part of the allure of cloud, AI and cybersecurity technology market. It seems that OSS developers took a decision about two years ago and the industry too.

For instance PDI/Kettle leader and founder moved to other company and the desktop application has components (email or excel output) that had not been updated for newer versions of the software they are interfacing with. The other one is Sentrifugo a Human resource Web App, where there are no updates or activity from it’s founder in years. And lastly the BOSSIE’s awards has dropped the App category since 2017.

So, consider maintainers of those apps heroes, contribute as you can. Don’t let those products become those abandoned buildings in Instagram, they don’t deserve that.

Pentaho 8.2 by Hitachi Vantara is up in sourceforge.net


The second release of Pentaho -I’m still not at ease calling it Vantara- that is made every year, has finally came on December 2nd 2018. It almost seems like a Christmas present to BI enthusiast.
Pedro Alves published (link) a summary that list interesting improvements to Big Data functionality, PDI has a Python Executor and a Jupyter (feed) Integration. CDE can also create push-based streaming dashboards. There are lots of improvements to steps, even BA user capabilities and support of OpenJDK 8 JRE in server in these release.
This are the download links. Installation is done by downloading and unzipping.

Pentaho 8.2 
@ SourceForge
https://sourceforge.net/projects/pentaho/files/Pentaho%208.2/
BA (Web) Server 1.4GBhttps://sourceforge.net/projects/pentaho/files/Pentaho%208.2/server/pentaho-server-ce-8.2.0.0-342.zip/download
Destop tools:PDI (ETL) 1.2GBhttps://sourceforge.net/projects/pentaho/files/Pentaho%208.2/client-tools/pdi-ce-8.2.0.0-342.zip/download
PRD (Reports) 1GBhttps://sourceforge.net/projects/pentaho/files/Pentaho%208.2/client-tools/prd-ce-8.2.0.0-342.zip/download
(optional)PAD (Cube Aggregation Editor)https://sourceforge.net/projects/pentaho/files/Pentaho%208.2/client-tools/pad-ce-8.2.0.0-342.zip/download
PSW (Cube Schema Editor)https://sourceforge.net/projects/pentaho/files/Pentaho%208.2/client-tools/psw-ce-8.2.0.0-342.zip/download
PME (Business Model Editor)https://sourceforge.net/projects/pentaho/files/Pentaho%208.2/client-tools/pme-ce-8.2.0.0-342.zip/download

Good visualizations and good ETL transaformations

Pentaho CDE Samples

This is a collection of Dashboard examples already shared by bloggers that are recreated and tested on Pentaho Server CE 8.1 community edition.</p>

Most of these tutorials, presented in alphabetical order, are still online but some required minor changes to work in the current suite.

You can upload the code to see it in action or use it as reference. At the bottom you’ll find the link and instructions to upload them to your system.

Annual Sales

An interesting Dashboard is “Annual Sales” from Tercer Planeta (post) that shows the table component with columns like the databar or sparkline. It’s also interesting that it uses functions to change extension points, protovis colors or CSS in CCC charts.

The database is AdventureWorks (see previous post to upload it in SQL statements for MySql).

recreación Sales 2003-2005

Bullet Graph Dashboard

The original post of 2012 provided a step by step guide to build this Dashboard that uses a MDX query (Mondrian model from a star schema) and a tabular component to show the monthly data, it also gets that information in Kettle/PDI (ETL) and adds metrics data (like a scorecard/KPI) and shows them on a Bullet Chart. Its a simplified example (processes, bullet graph metrics and aggregations on mondrian) of a real Dashboard that I did for a client. There are also samples in pre-excecution and format functions to modify values in parameters or CSS to change the Chart style.

The chart data is refreshed by updating the queries when the year or month selectors change.

recreación Bullet Graph

For this to work on your system check last section.

Data Dashboard Steels-Wheels

This is a Dashboard that ATOL Conseils & Développements shared a while back. I couldn’t find the tutorial link but they have more complex and beautifull Dashboards in their site or articles like this one that talks about data access in a mondrian cube.

In this example you can see the use of pie and line charts and also the table component. At that time the arrows had to be drawn in javascript (use an if to show a gif file), now its a type of column. There is also code for the tooltip data.

recreación Atol

Sales Overview YTD Dashboard

This one was one of the first Dashboards that I saw. In 2011 Slawomir Chodnicki showed in his blog the complete procedure to build a Dashboard from a draft up to detail javascript that modified a graph (at that time no bullet graph component existed) to show what he wanted. I updated the components but the post is a jewel.

recreación YTD

Steel Wheels Demo Dashboard

This was a Dashboard included in CE. It shows bar graphs updated by a selector.

Demo Steel-Wheels.jpg

Welcome Dashboard in CTools

I guess this is a previus version of the current Dashboard example. The current one even lets you select views so this one could be used as a startup point to lear this tools.

Demo Welcome Dashboard

New Dashboard (RequireJS)

CDE used by default the 24 column blueprint design framework, now its the 12 column, but it also supports Bootstrap, it even has a button to generate a skeleton layout in the Layout view. When you craete a new CDE Dashboard it enables the RequireJS javascript library, you can change to node.js in the settings toolbar. That is important as some events are referended diferently in the former is this.dashboards and in the later is Dashboards with some events and methods available in each one.

This sample is documented at theat Hitachi-Vantara site and is built using the bootstrap framework in layout and RequireJS code. It is built differently that the old ones and further in the tutorial it says it can be embeded in pages outside the server.

Demo Sample with RequireJS

More samples

There are three more examples, two are components to use in more complex Dashboards, one has bars and the other dots.

And the thirth is a report from Will Gorman. This one needs tables from google analytics to work, but its a beautifull sample built with Pentaho Report Designer (PRD).

Will Gorman report.jpg

The Dashboard’s Code

To get this files into your system you have to:

  1. Get this zip file.
    You can confirm that it contains a manifest in xml and a folder with the different CTools files.
  2. Then login into yor Pentaho-server , select the home button and select Browse Files → click on Public folder.
  3. In the right menu the upload option will appear. Click on it. Click on the Browse button and navigate to the downloaded file and click Open and Ok.

After a few momments you can navigate in the Additional Examples folder. To see all the files you have to click on the top menu view and click on Show Hidden Files.

menu

Most of them work with the SampleData Datasource so they should work on your system as it is.

[Edit]

If you want to see Geo Localization examples then check Kleyson de Sousa Rios Github page and download and upload the zip file with his examples for the NewMapComponent (NMC-samples).


* Mondrian schema location (Bullet Graph Dashboard)

I could’t find a way to reference the mondrian schemas on the system JCR repository taht is needed in the Mondrian Input Step, so I edited the Datasource Kettle Component → dsBulletGraph by clicking in (…)

EditInputMondrianCatalog

And used ${Internal.Transformation.Filename.Directory} and move two levels up “/../..” to refer to your tomcat/pentaho solutions.

EditInputMondrianCatalog1

If you develop your PDI transformation, the Catalog location field must contain the complete path-filename to your mondrian XML specification, then afer you upload it in your Pentaho Server, make the edit to your schema path.

So, for this example to work, you have to download a copy of steelwheels.mondrian.xml that’s in your Pentaho Demo system repository to the /tomcat/pentaho-solutions directory, or download this copy in a Zip file.

Pentaho Server 8.1 with MariaDB (MySql)

[Edited on:
– December 2018 to correct config drivers for MariaDB.
– June 13th 2018, to add the solution note to Sql parameters.]

Migrate your Database

The official way to change the database is at pentaho-wiki and pentaho-doc. These are notes to my 2011 post that modifies the DEMO to use MariaDB instead of the HSQLDB that comes with it.

Important: I verified this on a Ubuntu 18.04 and MariaDB 10.1, keeping the  jcr (Jackrabbit repository) in thefilesystem and have a problem with JINDI SQL Queries that I have to substitute with JDBC SQL Queries (see the final note).

I. DATABASE DRIVER

Download the driver [Connector/J mariadb-java-client-2.3.0.jar] to ~/Pentaho8/pentaho-server/tomcat/lib.

If you have the desktop tools, copy the driver also to ~/Pentaho8/report-designer/lib, ~/Pentaho8/data-integration/lib, ~/Pentaho8/shema-workbench/lib.

II. DATABASE CONTENT

Create content in Database

Start the MariaDB engine and run the suplied scripts in ~/Pentaho8/pentaho-server/data/mysql5, to create required databases:

cd ~/Pentaho8/pentaho-server/data/mysql5
 mysql -u root-user -p root-password < create_quartz_mysql.sql
 mysql -u root-user -p root-password < create_repository_mysql.sql

Download the Sample Database (here), unzip and upload it:

mysql -u root-user -p root-password < SampleData_2018_mysql.sql

III. TOMCAT CONFIG

Hibernte Settings

  1. Goto to ~/Pentaho8/server/pentaho-server/pentaho-solutions/system/hibernate
  2. Open hibernate-settings.xml.
  3. Find the  tag config-file and change it to
    system/hibernate/mysql5.hibernate.cfg.xml
  4. Open mysql5.hibernate.cfg.xml change the class drivers
    <property name="connection.driver_class">org.mariadb.jdbc.Driver</property>
    <property name="connection.url">jdbc:mariadb://localhost:3306/hibernate</property>

System connections

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/META-INF/
  2. Open context.xml
  3. Change classname, url and SQL query to:
    <?xml version="1.0" encoding="UTF-8"?>
    <Context path="/pentaho" docbase="webapps/pentaho/">
    	<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
    		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		maxWait="10000" username="hibuser" password="password"
    		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/hibernate"
    		validationQuery="select 1" />
    
    	<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
    		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		maxWait="10000" username="pentaho_user" password="password"
    		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/quartz"
    		validationQuery="select 1"/>
    
    </Context>

Spring config

  1. Go to ~/Pentaho8/pentaho-server/Pentaho-solutions/system/
  2. Open applicationContext-spring-security-hibernate.properties
  3. Change driver, url and dialect to
    jdbc.driver=org.mariadb.jdbc.Driver
    jdbc.url=jdbc:mariadb://localhost:3306/hibernate
    jdbc.username=hibuser
    jdbc.password=password
    hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Add datasources to jndi list

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
  2. Open jdbc.properties
    1. Change the driver definitions to mysql
      SampleData/type=javax.sql.DataSource
      SampleData/driver=org.mariadb.jdbc.Driver
      SampleData/url=jdbc:mariadb://localhost:3306/sampledata
      SampleData/user=pentaho_user
      SampleData/password=password
      Hibernate/type=javax.sql.DataSource
      Hibernate/driver=org.mariadb.jdbc.Driver
      Hibernate/url=jdbc:mariadb://localhost:3306/hibernate
      Hibernate/user=hibuser
      Hibernate/password=password
      Quartz/type=javax.sql.DataSource
      Quartz/driver=org.mariadb.jdbc.Driver
      Quartz/url=jdbc:mariadb://localhost:3306/quartz
      Quartz/user=pentaho_user
      Quartz/password=password
      SampleDataAdmin/type=javax.sql.DataSource
      SampleDataAdmin/driver=org.mariadb.jdbc.Driver
      SampleDataAdmin/url=jdbc:mariadb://localhost:3306/sampledata
      SampleDataAdmin/user=pentaho_admin
      SampleDataAdmin/password=password

Stop HSQLDB from starting

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/WEB-INF/
  2. Open web.xml
  3. Find the word HSQLDB. There are to points to edit. You will delete “–>” on the first line and “<–” on the last, to make it into a comment.In version 8.1 go to lines 25 and 167. They should look like this:
    <!-- [BEGIN HSQLDB DATABASES]
     <context-param>
     <param-name>hsqldb-databases</param-name>
     <param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
     </context-param>
     [END HSQLDB DATABASES] -->

    and

    <!-- [BEGIN HSQLDB STARTER]
     	<listener>
     	<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
     </listener>
     [END HSQLDB STARTER] -->

IV. Change Web Server Datasources

  1. Start your BI server and log-in  (localhost:8080/pentaho) as Admin.
  2. Click at the left top menu: Manage Data Sources
  3. You need to select SampleDataJDBC data-source and click on the gear icon to open the config window.
  4. Change the database type to MariaDb. Then change the Database Name to sampledata (the database name is in lowercase), port to 3306, type again the password (password). Click on TEST. Click OK if its succesfull.

Here, you can define a connection to your databases or you can return later.

That’s it, you should have a working Server with MariaDB. To test it by using the samples: Browse: Samples → Public → Steel Wheels.

V. Modify your SQL Queries that uses Parameters

But there is a problem if you want to use a query wit SQL with parameters. Maybe there is a missconfiguration in CDA or the repository, or a problem with the MySql driver for a MariaDB engine, as the parameters in the JNDI configuration report an error and do not work.

To get a result I use only SQL JDBC, by supplying these config parameters on each query

driver:    org.mariadb.jdbc.Driver
 url=      jdbc:mariadb://localhost:3306/sampledata?generateSimpleParameterMetadata=true
 user:     pentaho_admin
 password: password

Troubleshoot Log

If you have problems check the log file is ~/Pentaho8/pentaho-server/tomcat/logs/catalina.out its very verbose so look for ERROR lines