Pentaho BI Server CE 5.2 migration to 5.3 and 5.4 has been the most difficult process that I remember. 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: One thing they did fixed for us using i18n in other languages in 5.4 is that numbers in text fileds doesnt get reformated anymore (they got visualy modified. e.g. 2,015 ).
Part I: Backup
Log into your system, browse your solutions folders and make an inventory or take some snapshots so you can check that every file did make it through the export-import step.
So browse your files (showing hidden ones) for reports, resources, all files: .xmi, .sailu, .cda, etc.
Check also the Marketplace for plugins you use.
Config details: Users and Datasources.
- Stop your server:
- Modify the exporter if you like to backup your Saiku files:
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:
Credit: Joel Blog.
- Start Your Server
- 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
- Copy the exported-file.zip file to upload.zip (keep both in case you have to edit them). Open it. Delete from it he folders that are from Pentaho Demo (Steel Wheels. cde, plugin-samples, bi-developers), those ‘solutions’ already exist yin the demo. Just keep your files.
- 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〉
- You can shutdown your Server.
Part II: Install CE Demo
- Download from:
– BI Server 5.3 Sourceforge Folder. With a 673MB 22.214.171.124-213 zip file
– For 5.4 we have the SF Folder, and the 814MB biserver-ce-126.96.36.199-130.zip file.
- Rename your current folder to something like ‘Pentaho5-old’.
- Create a new Pentaho5 directory an unzip the biserver-ce into that folder
- Start the Server (Demo).
- 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.
- 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.
- Delete file
It will be created automatically as a copy from the one edited in point 3.
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" />
- To disable HSQLDB startup, edit
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〉
The only change is to replace mysql: mysql5.hibernate.cfg.xml
- Copy jindi datasources:
The strings change to MySql like this:
Copy and paste the appropiate lines to configure your databasources.
- Copy MySql config for Hibernate:
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
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.
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〉
- Check your quartz5 prefixed database against the script on, including case sensitive if your OS or DB are sensitive.
If there are diferences it is better to recreate the database.
the quartz config, to:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
- 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.
- If you’re updating 5.3 to 5.4 a problem that we though was solved is present again.
- Start your demo:
- You should be able to login as ‘admin’.
If you get an error, clear your browser cache and check your database :)
- Install Plugins:
If you use a different language than English, in Home->Marketplace, install your language package, and restart the server. Then from the Tools -> Language Packs choose install and restart again. These are two steps.
In Home->Marketplace install plugins like Saiku, Saiku Chart Plus, WAQR, restart.
In Home->Administration, create your users. Assing 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.
- You can check the new Dashboard in Browse Files->Public->Steel Wheels->Dashboards->CTools Dashboards.
Part IV: Move in Your Content
- Wiith your server running
In a terminal, load your backup file. Use youser and password that you created before:
./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
- Copy your content from your backup folder in
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.
- 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).
- 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:
- Haven’t checked the .css. They definetly need a tune up but probably will be a different story as they might be in the repository.
- I also didn’t use the localization messages in the .properties files as the localization are good enough.
That’s it. You have now a Pentaho BI CE 5.3/5.4 system.
Note: You’ll see that they are still modifying PRD parameters but they are not ready yet.
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:
- For the new look in PDI/Kettle:
- For Metadata 5.3:
- There are two other projects that you would like to install if you work with OLAP models (check some older documentation here):