Pentaho Migration 5.1 to 5.2 [Almost Working]

This are not the final instructions, but they will allow you to test the new server with your content and data as you have them now.

—————————————————————————————————————

This are the steps I took for my Pentaho 5.0 to 5.1 and 5.1 to 5.2 upgrades. My installation uses MySql for hibernate, quartz and business databases and I keep the Jackrabbit repository on filesystem. The steps are the same on Linux and Windows.

First Part: Install New Software

  1. Make an inventory: Marketplace, Config (datasources), Reports [That’s optional].
  2. Stop the Pentaho service.
  3. Rename your folder with the current version to something like ‘Pentaho5-old’.
  4. Create a new Pentaho5 directory an unzip your BI Apps into that folder, then:
    • For Pentaho Report Designer:
      1. Rename the folder to report-designer as it used to be named.
      2. Copy your development folder from the ‘backup’.
      3. Also copy your DB drivers: mysql-connector-java-5.1.17.jar, ojdbc14.jar, orai18n.jar, -IT is using Oracle 9, and still works- from your backup to report-designer/lib/jdbc
    • For PDI/Kettle
      1. Copy your development folder from the backup.
      2. Copy your DB drivers (mysql-connector-java-5.1.17.jar, ojdbc14.jar, orai18n.jar) from your backup to data-integration/bin.
    • For the BI Server itself:
      1. Copy your DB drivers -other than MySql- (ojdbc14.jar, orai18n.jar) from your backup folder to biserver-ce/tomcat\lib
  5. Start your demo:
    ./start-pentaho.sh
    Check out what’s new at http://localhost:8080/pentaho
    The Marketplace throwed several connection errors, until it was succesfull.
  6. Stop the server:
    ./stop-pentaho.sh

Second Part: Modify Configuration Files

Compare your config files before copying them, and verify they are in utf-8 format. Remember that you have the backup folder and you only need to rename it to recover to a working system.

  1. Delete the file
    biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
    It will be created as a copy from the one you edit in point 2
  2. Copy
    biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
    mine has MySql connection strings already
  3. Copy
    biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
    Only needed changes on line 73 and 205, to comment HSQLDB from starting
    and:
    At line 539 change your session-timeout. Its’ in seconds, I use: 180
  4. Copy
    biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml
    The only change is to replace mysql: mysql5.hibernate.cfg.xml
  5. Copy jindi datasources:
    biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
  6. Copy MySql config for Hibernate:
    biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
  7. Copy
    biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.properties
    It contains:

    datasource.driver.classname=com.mysql.jdbc.Driver
    datasource.url=jdbc:mysql://localhost:3306/hibernate
    datasource.username=hibuser
    datasource.password=password
    datasource.validation.query=SELECT 1
  8. Copy
    biserver-ce/pentaho-solutions/system/pentaho.xml
    I changed:

    SampleData
    localhost
    MySql
    3306
    pentaho_user
    password
    select 1

    And turn off the default users select combo at login page (that should be disabled in your administration console) in line 20:

    <login-show-sample-users-hint>false</login-show-sample-users-hint>
  9. This is where I had trouble:
    This didn’t work: I first ran the update on
    biserver-ce/data/mysql5/create_quartz_mysql.sql
    to make shure I had de correct quartz5 prefixed tables, then created upper/lower cased tables, tables, deleted conten on different folders, and so on. But ran on errors at startup about listener problems:

    1. Cannot create JDBC driver of class
    2. SystemListener.xml
    3. EmbeddedQuartzSystemListener.ERROR_0007_SQLERROR
    4. PentahoSystem.ERROR_0014 org.pentaho.platform.scheduler2.quartz.EmbeddedQuartzSystemListener
      with no luck on making it work.

    This did work

    1. Dropping the quartz database and ran the SQL script at:
      biserver-ce/data/create_quartz_mysql.sql
      that creates qrtz5_ prefixed tables and also updates user permissions.
    2. Modified as suggested in the script:
      biserver-ce/pentaho-solutions/system/quartz.properties
      that’s the quartz config, to:

      org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
    3. [skip this one, Marcelo suggested a better option]
      Edit biserver-ce/pentaho-solutions/system/systemListeners.xml and stopped Quartz Listener from runnig. That is the problem with this instructions: your system won’t be able to schedule jobs as it is:

      <!--bean id="quartzSystemListener" class="org.pentaho.platform.scheduler2.quartz.EmbeddedQuartzSystemListener" /-->
    4. Move to a backup folder -away from Pentaho- the following file:
       biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib/pentaho-hadoop-hive-jdbc-shim-5.2.0.0-209.jar

Part Three: Move in Your Content

  1. Copy your content from your backup folder in
    biserver-ce/pentaho-solutions
    This is where I keep my .xmi, .mondrian.xml files and solution folders
  2. Delete and then replace your repository folder:
    biserver-ce/pentaho-solutions/system/jackrabbit
  3. Start your server. Clear your browser’s cache, reset your pentaho solutions and reload.

Part Four: Apply your Look and Feel

To finish

  1. Copy your images, .html , messages files with your modifications from your backup folder to the new one. There is a previous checklist.
    Note: Had only to modify directly the .properties files (insted of the _es.properties) on biserver-ce/tomcat/webapps/pentaho/mantle/ (browser & home/properties) for my localized text as the i18n calls didn’t work for the left panels.
  2. Update your plugins on the marketplace

Now you can try your new and beautifull NEW 5.2 BI system.

———————————

Notes on the 5.2 – MySql – Quartz Problem

There are some Quartz listener/socket problems reported since v3.6, but every recomendation on forums or blogs hasn’t worked so far. The error is present on windows/ubuntu, java 32/64 bits, mysql/mariadb.

This is what I found so far:

  1. The problem is not IN the database as no query appears on the MySql general log, until the Pentaho shows the problem.
  2. The 1st error in the log makes me think of a Tomcat config problem :
    eduler2.quartz.EmbeddedQuartzSystemListener
    EmbeddedQuartzSystemListener.ERROR_0007_SQLERROR
    Cannot create JDBC driver of class ‘com.mysql.jdbc.Driver’ for connect URL ‘jdbc:mysql://localhost:3306/quartz’
  3. The diff in config files shows more classes on the new version, but the driver configurations remais the same
Advertisements

Customize The Pentaho 5.x Look

To present a Pentaho BI demo to your company or client, you should upload relevant business data, develop reports, maybe OLAP models and MDX queries, even Dashboards. To take it to the next step, you can modify files to show their logo and text above Pentaho’s marketing messages.

What follows is a list of ‘quick and dirty’ tips to make that customization. It’s not the preferred way a programmer o designer go about it, but it works, so:

Login Screen Images

You will need two images:

  • Logo, 360×93 pixels in size. Transparency recommended. Replace at
    pentaho-solutions\system\common-ui\resources\themes\images\puc-login-logo.png
  • Background, 1255×663 pixels in size. Edit it and add brightness to emulate original effect. Replace at
    pentaho-solutions\system\common-ui\resources\themes\crystal\images\login-crystal-bg.jpeg

I have tested it with the Crystal (default) and Emerald (available on 5.2) theme.

Login Screen Text

To modify the text in the screen you can edit a message.properties file. I chose to replace the variables by fixed words. The file is:

\biserver-ce\tomcat\webapps\pentaho\jsp\PUCLogin.jsp

To change user, password, login button text, copyright, first copy and paste each one of those lines, comment the original and type your text on the new ones, like this:

<!-- ...getString("UI.PUC.LOGIN.COPYRIGHT", ... -->

 Main Page

What I changed in the main page is the center panel text, leaving intact the navigation bar on the left and upper toolbars and menues. So I edited:

biserver-ce/tomcat/webapps/pentaho/mantle/home/content/welcome/index.html

Its a regular html file, just check the divs as they are rendered as tabs or categories in case you want to keep them.

For CSS modifications, add rules to one of these:

biserver-ce/tomcat/webapps/pentaho/mantle/home/content/welcome/css/main.css
biserver-ce/pentaho-solutions/system/common-ui/resources/themes/crystal/globalCrystal.css
biserver-ce/pentaho-solutions/system/common-ui/resources/themes/onyx/globalOnyx.css
biserver-ce/pentaho-solutions/system/pentaho-emerald-theme/resources/themes/emerald/bootstrap/css/bootstrap-namespaced.css

I installed the new (in 5.2) Emerald Theme and added at the bottom of main.css:

/* Header fonts */
h1.large  { font-size: 2.5em; color: #3BA749; }
.header-navigation-item { font-size: 1.2em; color: #3BA749; }
.content h2 { color: #3BA749; }
/* To avoid Multilist height bug, and jpivot message, I edited  globalEmerald.css */
.parameter-container SELECT { height:auto; }
#deprecatedWarning { display:none; }

For other pages, themes have preference so in bootstrap-namespaced.css I added also at the bottom:

 /* Content width */
.bootstrap .row-fluid .span9  { width:71%; }
.bootstrap .container-fluid { padding-left: 10px; padding-right: 10px; }
 /* Main left sidebar headers */
.bootstrap .well h3 { color: #3BA749; }
/* Browse Files column width */
#fileBrowserFolders { width:28%;  }
#fileBrowserFiles { width:45%; margin-left:0px; }
#fileBrowserButtons { width:22%; margin-left:0px; }
.fileBrowserColumn .header { color: #3BA749; }

To make Tomcat to reload the .css, I had to delete the content of

biserver-ce/pentaho-solutions/system/tmp
biserver-ce/tomcat/temp
biserver-ce/tomcat/work/Catalina/localhost

Then restart the server and clean the browser cache at login screen.

Translation (i18n)

If you like to change words, you need to modify some text files, modify the text to the right of the equal sign and save them with UTF-8 encoding:

  • In 5.0 and 5.1:
    Main app mesages are located at:
    \biserver-ce\tomcat\webapps\pentaho\mantle\home\properties\messages.properties
  • In 5.0
    To edit the navigation text on the upper left selector, edit:
    \biserver-ce\pentaho-solutions\system\default-plugin\resources\messages\messages.properties
  • In 5.1:
    – One file contains login, navigation content and tools header text. It’s located at
    \biserver-ce\tomcat\webapps\pentaho\mantle\messages\mantleMessages.properties
    – Navigation text and right content of options like browse files are in:
    \biserver-ce\tomcat\webapps\pentaho\mantle\browser\messages_es.properties
  • Notes:
    – Use unicode, ie, \u00E1 instead of á, or otherwise your system may fail after login.
    – Copy each message file to a new one and add your locale, i.e, for spanish copy to messages_es.properties and then edit you translation. You also need to add the line es=Español to the file messages_supported_languages.properties on the same folder.

Restart your server, clear your browser cache and test it.

Notes

Other images were modified in older versions and havent verified if they are used today but they are listed if needed:

  • Logo. 185x50px. pentaho-solutions\system\common-ui\resources\themes\images\logo.png
  • Logo. 52x60px. tomcat\webapps\pentaho\mantle\themes\onyx\images\logo.png
  • Login. 224x94px. tomcat\webapps\pentaho-style\images\login\logo.png

About other text messages files:

  • There is a similar copy of the first file mentioned above but has no effect:
    \biserver-ce\tomcat\webapps\pentaho\mantle\browser\messages.properties
  • There are older translation files that are no longer needed. But they are listed:
    \biserver-ce\tomcat\webapps\pentaho\mantle\messages\MantleLoginMessages.properties
    \biserver-ce\tomcat\webapps\pentaho\mantle\messages\filechooser_messages.properties

Thanks to:

An evaluation post of 5.0 by nextCoder that suggested the login page straight modification in http://www.nextcoder.com/?p=1172

There is a guide in your Pentaho installation, look into \biserver-ce\tomcat\webapps\pentaho\docs\customizing_pentaho_guide.pdf. I found it updated after making this notes, is consise and complete.