Update Pentaho BI Server CE from 5.2 to 5.3, and 5.4.

Pentaho BI Server CE 5.2 migration to 5.3 and then 5.4 has been a difficult process. Soo many changes, undocumented, some without obvious reasons. I hope it’s just because I don’t get the new vision and that it will get easier over time.

This notes are from my system in which I use MySql for hibernate, quartz and business databases. The Jackrabbit repository is kept on the filesystem. The steps are the same on Linux and Windows, just change the suffix from .sh to .bat. By the way if you’re on windows 7 32 bits, stick to jdk 1.7, Pentaho is not compatible with the current 1.8 version.

Note: They fixed in 5.4, for people using i18n localization in PRD parameters, so numbers in text fileds doesn’t get reformated (i.e. years got visualy modified to 2,015).

Part I: Backup

1. Take an inventory:
   Log into your system, browse your ‘solutions’ and make an inventory, maybe take some snapshots so you can check that every option did make it through the export-import step.
   Browse your files (enable ‘show hidden files’) for resources, and files like .xmi, .sailu, .cda, etc.
   List the Marketplace plugins you use.
   In Datasources and administration make note of your configuration an users you have set up.
2. Stop your server:
   ./stop-pentaho.sh
3. Modify the exporter utility to backup your Saiku files (optional):
   Open the file: biserver-ce/pentaho-solutions/system/importExport.xml
   Add in convertersMap:

   <entry key="saiku" value-ref="streamConverter"/>
   

   Add to the map of NameBaseMimeResolver bean:

   <entry key="saiku" value="text/xml"/>
   

   Add to the list approvedExtensionList:

   <value>.saiku</value>
   

   Credit: Joel Blog.

4. Start Your Server
   ./start-pentaho.sh
5. Start a Terminal window and run the command in the \biserver-ce folder:

   ./import-export.sh --export --username=YOUR-USERNAME --password=YOUR-PASSWORD --url=http://localhost:8080/pentaho --charset=UTF-8 --path=/public --file-path=$HOME/Pentaho5/exported-file.zip

   You can read the infocenter documentation here and here.

6. Copy the exported-file.zip file to upload.zip (keep the original as backup). Open it and delete folders that are from Pentaho Demo (Steel Wheels, cde, plugin-samples, bi-developers), those ‘solutions’ already exist in Demo. Just keep your files.
7. Edit the exportManifest.xml in the same zip file to remove all items (xml opening and closing lines) that are not from files, your solution folders, take for example one at the bottom that correspond to a graphics file:

   〈ExportManifestEntity path="public/plugin-samples/samples.png"〉
   〈ExportManifestProperty〉
    〈EntityMetaData name="samples.png" createdDate="2015-02-21T00:34:32.695-06:00" isFolder="false" path="public/plugin-samples/samples.png" isHidden="true" locale="es_ES" title="samples"/〉
   〈/ExportManifestProperty〉
   〈ExportManifestProperty〉
    〈EntityAcl〉
     〈entriesInheriting〉true〈/entriesInheriting〉
     〈owner〉admin〈/owner〉
     〈ownerType〉USER〈/ownerType〉
    〈/EntityAcl〉
   〈/ExportManifestProperty〉
   〈/ExportManifestEntity〉
   

8. Shutdown your Server.

Part II: Install CE Demo

1. Download from:
   – BI Server 5.3 Sourceforge Folder. With a 673MB 5.3.0.0-213 zip file
   – For 5.4 we have the Sourceforge Folder or the 814MB biserver-ce-5.4.0.1-130.zip file.
2. Rename your current folder to something like ‘Pentaho5-old’.
3. Create a new Pentaho5 directory an unzip the biserver-ce into that folder
4. Start the Server (Demo).
5. Log in to http://localhost:8080/pentaho
   This will deploy web server files and upload initial content (biserver-ce/pentaho-solutions/system/default-content) for the web application.
6. Stop the server.

Part III: Replace your MySql Database instead of demo’s in-memory HSQLDB

You will be updating config files, so double check their content (you can use Meld in Linux or Win Diff in windows) before copying them. Also verify they are in utf-8 format.

1. Delete file
   biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
   It will be created automatically as a copy from the one edited in point 3.
2. Copy
   biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
   mine has two almost identical MySql connection strings, the lines changes to:

   driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
   validationQuery="SELECT 1" />

3. To disable HSQLDB startup, edit
   /biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
   Comment out block at line 73:

     〈!-- [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 line 205:

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

   At line 539 you can change your session-timeout. Its’ in seconds:

     〈session-config〉
       〈session-timeout〉180〈/session-timeout〉
     〈/session-config〉
   

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
   The strings change to MySql like this:

   SampleData/driver=com.mysql.jdbc.Driver
   SampleData/url=jdbc:mysql://localhost:3306/sampledata
   

   Copy and paste the appropiate lines to configure your databasources.

6. Copy MySql config for Hibernate:
   biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
   Change MySql strings and the 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

7. Copy
   biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.properties
   It contains in different lines:

   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
   

   Note: They used the string userdb instead of hibernate but Im not using such database for users as they do in the demo so I changed it back.

8. Copy
   biserver-ce/pentaho-solutions/system/pentaho.xml
   I changed:

   〈login-show-sample-users-hint〉false〈/login-show-sample-users-hint〉
   

   to stop the login message that show users and:

     〈sampledata-datasource〉
   	  〈name>SampleData〈/name〉
   	  〈host>localhost〈/host〉
   	  〈type>MySql〈/type〉
   	  〈port>3306〈/port〉
   	  ...
   	  〈query>select 1〈/query〉
   

9. Check your quartz5 prefixed database against the script on, including case sensitive if your OS or DB are sensitive.
   biserver-ce/data/mysql5/create_quartz_mysql.sql
   If there are diferences it is better to recreate the database.
10. Modify
    biserver-ce/pentaho-solutions/system/quartz/quartz.properties
    the quartz config, to:

    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

11. Copy special DB drivers like (ojdbc14.jar, orai18n.jar) if you have them. MySql 5.17 is already there from your backup folder to biserver-ce/tomcat/lib.
12. If you’re updating 5.4 a problem that we though was solved is present again (you do not need to do this for 5.3). Move to a backup folder the following file:

     biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib/pentaho-hadoop-hive-jdbc*.jar

13. Start your demo:
    ./start-pentaho.sh
14. If you get an error, clear your browser cache, for Quartz errors check your database prefix.
    Login as ‘Admin’ again.
15. Install Plugins:
    If you use a different language than English, in Home->Marketplace, install your language package, restart the server. Then from the Tools -> Language Packs choose install and restart again. These are two related steps.
    In Home->Marketplace install plugins like Saiku, Saiku Chart Plus, WAQR, restart.
    In Home->Administration, create your users. Asign the admin role to at least one of them.
    In Manage DataSources, modify the SampleData jdbc to access MySql. You can create your SQL Mysql Database Connections or wait until you move in your files in next section.
16. You can check the new Dashboard in Browse Files->Public->Steel Wheels->Dashboards->CTools Dashboards.

Part IV: Move in Your Content

1. Wiith your server running:
   In a terminal, load your backup file. Use your user and password that you created in las section:

   ./import-export.sh --import --overwrite=true --username=YOUR-USERNAME --password=YOUR-PASSWORD --overwrite=true --permission=true --retainOwnership=true --url=http://localhost:8080/pentaho --charset=UTF-8 --path=/ --file-path=$HOME/Pentaho5-old/upload.zip

2. Copy your content from your backup folder in
   biserver-ce/pentaho-solutions
   This files (solution folders, metadata files, mondrian models) are not used as they are outside of the repository but I use them for development and then upload them.
3. In ‘Manage DataSources’, create your JDBC, Analisys, Metadata and OLAP connections, as you upload files that were not included in the export-import process like mondrian.xml, metadata.xmi. This is where your inventory from your old system is most valuable (you can start it as long as both are not running at the same time).
4. Restart your server. Clear your browser’s cache, reset your pentaho solutions.

Part V: Apply your Look and Feel

As of this writing (5.2->5.3: 02/21/2015, 5.3->5.4: 06/28/2015):

• The changes on login screen images, login page html and index main content works as I wrote in here.
• There is no Emerald Theme yet from the marketplace.
  But I got most of it when I copied it from the backup 5.2 directory:

   pentaho-solutions/system/pentaho-emerald-theme/

  Haven’t checked those .css. They need minor tune up.

• I didn’t use my edited localization messages.properties created in previous posts as the translation in the marketplace are good enough.

That’s it. You have now a Pentaho BI CE 5.3/5.4 system.

Part VI: Install your Desktop Development Tools

To update your Desktop Tools rename their current folders to somethining-old, download the new files, that are really slow form the legendary sourceforge.net and unzip them on your Pentaho5 folder so you’ll have them all in one place:

• For Pentaho Report Designer:
  1. Dowload the 5.3 file. or 5.4 file.
  2. Copy your ‘development folder’ from the backup.
  3. Copy your DB drivers, in my case: mysql-connector-java-5.1.17.jar, ojdbc14.jar, orai18n.jar, that works with Oracle 8 & 9, from your backup to report-designer/lib/jdbc
• For the new look in PDI/Kettle:
  1. Download the 5.3 file or 5.4 file.
  2. Copy your ‘development folder’ from the backup.
  3. Copy your DB drivers from your backup to data-integration/bin.
• For Metadata 5.3:
  1. Download the 5.3 file or 5.4 file.
  2. Copy your model files (mdr.btd and mdr.btx) in your old metedata-editor forder to your new one.
  3. Copy your DB drivers from your backup to metadata/bin.
  4. Start your editor and Define your connection.
• There are two other projects that you would like to install if you work with OLAP models (check some older documentation here):
  1. Mondrian schema workbench here.
  2. Mondrian agregated designer here.