Pentaho BI Server CE 5.2 migration to 5.3 has been the most dificult process that I remember. Many changes, undocumented, without obvious reason. I hope it’s only 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 data. 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.
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 Saiku files:
Open the file: biserver-ce/pentaho-solutions/system/importExport.xml
Search for: <util:map id=”convertersMap”>
add <entry key=”saiku” value-ref=”streamConverter”/>
Search for: <bean id=”IPlatformImportMimeResolver” class=”org.pentaho.platform.plugin.services.importer.NameBaseMimeResolver” />
Add <entry key=”saiku” value=”text/xml”/>
Search for: <property name=”localeExportList”>
Search for almost the last : <constructor-arg>
Credit: The idea was read from Joel Blog.
- Start Your Server
- Start a Terminal window and run in the biserver-ce folder:
./import-export.sh --export --username=<your-user> --password=<your-password> --url=http://localhost:8080/pentaho --charset=UTF-8 --path=/public --file-path=/home/<your-os-user>/Pentaho5/exported-file.zip
Check the infocenter documentation.
- Copy that exported-file.zip file to upload.zip and open it. Delete tfrom it he folders that are from Pentaho Demo, like Steel Wheels. Just keep your files.
- Also edit the exportManifest.xml to remove the lines that are not for your solution folders.
- Shutdown your server.
Part II: Install CE Demo
- Download 5.3 (released february 15th of 2015) from:
– BI Server 5.3 Sourceforge Folder
– 673MB 188.8.131.52-213 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 de Memory HSQLDB to your MySql Database
You will be updating your config files, so double check them (use Meld in Linux o Win Diff) before copying them, and verify they are in utf-8 format.
- Delete the file
It will be created as a copy from the one you’ll edit in point 3
mine has two MySql connection strings, the first one changes to:
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate" validationQuery="SELECT 1" />
At line 73 and 205: <!– [BEGIN HSQLDB DATABASES]
At line 78 and 209: [END HSQLDB DATABASES] –>
At line 539 you can change your session-timeout. Its’ in seconds:
The only change is to replace mysql: mysql5.hibernate.cfg.xml
- Copy jindi datasources:
The strings change to MySql like this:
Then copy and paste the apropiate lines so you can configure your databases.
- Copy MySql config for Hibernate:
Change MySql strings and the dialect to:
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
They changed from hibernate to userdb but Im not using a database for users like they do in the demo so I changed it back.
to stop the login message that show users and:
<type>MySql</type> <port>3306</port> <query>select 1</query>
- Check your quartz5 prefixed database against the script on, including case sensitive if your SO or DB are sensitive.
If there are diferences is better to recreate the daabase
the quartz config, to:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
- Copy your DB drivers -other than MySql- (ojdbc14.jar, orai18n.jar) from your backup folder to biserver-ce/tomcat\lib
- Start your demo:
- You should be able to login as Admin.
- If you use a different language than English the before all in Home->Marketplace install your language package from the Marketplace, restart the server, then from the Tools Toolbar choose install and restart again.
In Home->Marketplace install plugins like Saiku, Saiku Chart Plus, WAQR, restart.
In Home->Administration create your users.
In Manage DataSources modify the SampleData jdbc to access MySql and create your other Mysql Database Connections.
- 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:
./import-export.sh --import --overwrite=true --username=<your-user> --overwrite=true --permission=true --retainOwnership=true --password=<your-password> --url=http://localhost:8080/pentaho --type=files --charset=UTF-8 --path=/ --file-path=/home/<your-os-user>/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.
- Create your users, Database connections and upload files that were not included in the export-import process.
- Start your server. Clear your browser’s cache, reset your pentaho solutions to reload.
Part V: Apply your Look and Feel
As of this writing (02/21/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.
- 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 .properties files as the localization are good enough.
Thats it. You have now a Pentaho 5.3 BI 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 5.3:
- Dowload the file.
- Copy your development folder from the backup.
- 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 PDI/Kettle 5.3:
- Download the file.
- Copy your development folder from the backup.
- Copy your DB drivers from your backup to data-integration/bin.
- For Metadata 5.3:
- Download the file.
- Copy your model files from the backup.
- Copy your DB drivers from your backup to metadata/bin.