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

Advertisements

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

Pentaho 8.x

[Edited on may 15th 2018 for 8.1]

In sourceforge, Pentaho has been renamed “Hitachi Vantara | Pentaho”, the binaries for all projects in this version has been uploaded for the community. Pedro Alves has summarized the main features for 8.0 and 8.1.

The links to the Web server and the Desktop tools are:

Pentaho 8.1: https://sourceforge.net/projects/pentaho/files/Pentaho%208.1/
Pentaho 8.0: https://sourceforge.net/projects/pentaho/files/Pentaho%208.0/

  1. Server
    • Tomcat server to squedule jobs, grant access to run reports and jobs to users and design dashboards.
      BI Server 8.1
      BI Server 8.0 [1.2GB]
  2. Client Tools
    • Pentaho Data Integration – Best ETL you’ll find.
      PDI 8.1 [1 GB]
      PDI 8.0 [979.8 MB]
    • Pentaho Report Designer. Reporter for different databases.
      PRD 8.1 [868.7 MB]
      PRD 8.0 [666.0 MB]
    • Pentaho Metadata Editor – Grant access and model (physical and business) on how to query your data.
      PME 8.1 [861 MB]
      PME 8.0 [836.5 MB]
    • Pentaho Aggregation Designer – Specify mondrian cube aggregations.
      PAD 8.1 [26.3 MB]
      PAD 8.0 [25.6 MB]
    • Pentaho Schema Workbench – Edit your mondrian cube.
      PSW 8.1 [31.3 MB]
      PSW 8.0
      [84.4 MB]
    • Big Data Plugin
      BDP 8.1 [629.5 MB]
      BDP 8.0 [619.8 MB]

Sound like its time for discovery.

Pentaho 7.1 – Demo Install

The newest release Pentaho 7.1.0.0.12 in its community edition is available for download at sourceforge (link) since the 22th of may of 2017.

Pedro Alvez announced its availability the same day with an excellent post in his blog (link). He mentions new visualizations, scalability with big data engines and repository improvements -someday they’ll deliver something you can work confortable with, maybe-, and a new web theme. We’ll see how they are supported in the CE edition. A new mobile access for the EE. The PDI team is exited about its ‘metadata injection’ improvements.

You’ll find it at Sourceforge.net:

  • Web application: Business Intelligence Server [1.1 GB] with 58 closed reports.
  • The best ETL aplication: PDI [904 MB] with 142 closed reports.
  • Report Designer [606 MB] with 19 closed reports in their JIRA system.
  • Pentaho Metadata [787 MB] with 5 closed reports.
  • And Big Data Shims folder.

Steps for backup, install, upgrade to a MySQL local DB are the same that you are already familiar with and has been previously posted in this blog (link) and well documented at the Pentaho Wiki.

Visiting Pentaho. Waiting for 7.0

[Update november 14th]

Ok, It’s here. It’s up since november 9th.

Go and get it at the wonderfull site: Sourceforge.net files.

[Update october 18th]

Pentaho Business Analytics 7.0 will be available for download in mid november. These are links to the announcement and overview posts.

[Original Post]

In october 2016 we should be getting next version of Pentaho. According to its Jira records it will be a stability oriented release, I count 58 fixes for BI Server, 139 in PDI, 10 in PRD, 6 in CDE/CTOOLS (you can check it for each project in 7.0.0 version at http://jira.pentaho.com/) and few improvements.

Having a reliable suite is a benefit for all specially in this market where de community editions are being abandoned in FOSS and their enterprise editions are being developed attending corporate needs rather than the medium or small companys.

On that topic, I browsed the forums (http://forums.pentaho.com/) to see what  people are asking. This is what I found from the 1st to the 22th of september:

Project Questions Unanswered Notes
BI Platform 22 8 Installation and simple questions. 2 people even asked if anyone has any clues, they were lost.
Pentaho Reporting [PRD] 30 15 Advanced questions. 2 moved as they placed it in wrong forum.
Pentaho Data Integration [PDI] 127 23 Advanced questions. Some can be solved with the modified java step or mail step examples.
CTools 32 11 They ask how to modify graphs in specific ways, some of them answered in CTools web site, but need clarification on frameworks used.

Of these projects the BI Server is the one having most novices asking already answered questions. A FAQ should help these people. For the PDI and PRD there are some senior members giving excelent advice I felt good about humanity with the existence of these people.

I guess there will be no modifications in installing or upgrading instructions, maybe I should clean up the old posts in this site.

Well that’s my 2 cents on the CE application and support for the upcoming 7.0.0 version.