Pentaho Server 8.1 with MariaDB (MySql)

[Edited on june 13th 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 based on my 2011 post that modifies the DEMO to use MariaDB (or MySql) 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 CONTENT

Driver

[Optional] Update the driver to Connector/J (mysql-connector-java-5.1.46.zip), choose older version and platform independent. Then unzip its content and move mysql-connector-java-5.1.46.jar to ~/Pentaho8/pentaho-server/tomcat/lib. The OPTION error on logs will go away. You should move the older driver to another location.

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

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

II. TOMCAT CONFIG

Quartz Settings

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/quartz/
  2. Open quartz.properties.
  3. Find #_replace_jobstore_properties and change it to
    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

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. Check settings to MySQL Configuration according to your system (If you use defaults there is nothing to change) in mysql5.hibernate.cfg.xml.

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="com.mysql.jdbc.Driver" url="jdbc:mysql://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="com.mysql.jdbc.Driver" url="jdbc:mysql://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=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://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
  3. Change the driver definitions to mysql
    SampleData/type=javax.sql.DataSource
    SampleData/driver=com.mysql.jdbc.Driver
    SampleData/url=jdbc:mysql://localhost:3306/sampledata
    SampleData/user=pentaho_user
    SampleData/password=password
    Hibernate/type=javax.sql.DataSource
    Hibernate/driver=com.mysql.jdbc.Driver
    Hibernate/url=jdbc:mysql://localhost:3306/hibernate
    Hibernate/user=hibuser
    Hibernate/password=password
    Quartz/type=javax.sql.DataSource
    Quartz/driver=com.mysql.jdbc.Driver
    Quartz/url=jdbc:mysql://localhost:3306/quartz
    Quartz/user=pentaho_user
    Quartz/password=password
    SampleDataAdmin/type=javax.sql.DataSource
    SampleDataAdmin/driver=com.mysql.jdbc.Driver
    SampleDataAdmin/url=jdbc:mysql://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] -->

III. Change Web App Datasources

  1. Start your server and log in your browser (localhost:8080/pentaho) as Admin
  2. In the PUC (Pentaho User Console) page click at the left top menu: FileManage 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 MySql. Then change the Database Name to sampledata (the database name is in lowercase), port to 3306, type again the password (password) and test the connection. Click OK if its succesfull.
    ManageDatasource_v8.1

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.

 

IV. 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:   com.mysql.jdbc.Driver
 url=      jdbc:mysql://localhost:3306/sampledata?generateSimpleParameterMetadata=true
 user:     pentaho_admin
 password: password

 

Troubleshoot

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

Advertisements

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.

Pentaho 6.0.x Install

Introduction

After reading the changelog, I thought that the numbering from 5.4 to 6.0 was not justified. But then I realized that this is the first version of Pentaho under the Hitachi brand, and, seting a round number setted a milestone as completed. A good sign is that they kept the release day of the community version (CE), the same as the commercial one.

Recomendation: Check the ETL, it is a wonderfull tool. Spend time with it, it will let you clean and process data from several sources (Text, NoSql, DB, Excel, SAP ERP) and send it on its way [to services or users] in different formats. Of course it can be used with other suites. Then proceed with the Web server that is primary a client side tool that show processes according to user privileges and calendarize processes. To build dashboards you can choose between two sets of bulding blocks: CTools or Ivy. They are rudimentary but let you set parameters and recover data from the ETL or Report Designer and let user click on graphs for you to run queries. The Report Designer that has its own [complex] way of doing things but will let you create fixed-format reports mainly in pdf or html. The Metadata and Schema tools will help you make business/olap models and data governance.

Demo Install

First Download each file and install it as stated in the guide, play with it and then proceed with another.

Download links

Pentaho files: sourceforge zip files 

  1. ETL Tool [>810MB]: v6.0v6.0.1
  2. Tomcat Web Server [>900MB]: v6.0, v6.0.1
  3. Report Designer [>530MB]: v6.0, v6.0.1
  4. Metadata Editor [>500MB]: v6.0, v6.0.1
  5. OLAP Schema Workbench [~30MB]: v3.11
  6. OLAP Aggregation Designer [~30MB]: v6.0

Guide

Install steps for the pentaho demo applications have been using similar steps since 4.8. So the old 5.x guide works with the new files. Please follow this post using the new files.

You can skip the Java SDK installation if you already have it on your system. On a new box you’re better off with java SDK 1.8.0 as Pentaho 6.0 now works with it. Warning: If you’re on 5.x you’re probably on java SDK 1.7.0 and you can work with it, just rememeber to backup your development files before upgrading to java 1.8.0.

In that post you’ll also find instructions to install MySql that is an option in case you want to use your data. BTW I now use MariaDB and its working fine.

Memory adjustements:
I tested the apps on a windows 32 bits system and I had to edit the startup batch files to  lower the memory limits:
DATA INTEGRATION 32 bits: spoon.bat
FROM      -Xms1024m -Xmx2048m
TO            -Xms768m -Xmx1024m
REPORT-DESIGNER 32 bits: report-designer.bat
FROM      -Xms1024m -Xmx2048m
TO            -Xms512m -Xmx1024m
BI SERVER 32 bits: start-pentaho.bat
FROM     -Xms2048m -Xmx6144m
TO           -Xms768m -Xmx1024m