Aller directement à la fin des métadonnées
Aller au début des métadonnées

In this section we will explain what was done to install Pentaho 6.1 business analytics platform on an Ubuntu server. We will also explain what was done to install Pentaho Data Integration, Pentaho Schema Workbench and Pentaho Report Designer on an Ubuntu Desktop.

We wanted to use my CiviCRM database as the source for reporting and analytics and a seperate mysql database for the data warehouse. The CiviCRM database is called civicrm  and the data warehouse database is called datawarehouse.

We will use JNDI to connect to both databases as it is more secure than JDBC (but you can obviously also use JDBC if you want to).

 

Install Pentaho Business Analytics Server

Before we start make sure you have an Ubuntu server up and running.

Install MySQL, Java and Zip

We need to install MySQL, the right Java stuff and possibly the Zip utility. Obviously if one of them is already up and running you can ignore this, but make sure you have the correct Java version!

On the server enter the following command:

Install MySQL, Java and Zip

Now it is time to download the BI-Server. You can download it from http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/6.1/biserver-ce-6.1.0.1-196.zip/download. Once it is downloaded, unzip the file:

Unzip

Create the databases

After you have unzipped it it is time to setup a mysql database for de BI Server and to set the connection for the mysql server. We have followed the guideline from this blog: https://interestingittips.wordpress.com/2011/05/05/complete-pentaho-installation-on-ubuntu-part-2/.

Enter the following commands to install the mysql databases:

Configure the BI server

Now it is time to configure the BI-server to use those databases. Open /your/path/to/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml and change the lines to match your mysql user and password also change it to use the mysql JDBC driver:

Make sure the hibernate component will use mysql. Do this by editing the file /your/path/to/biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml and change it to:

Edit the file /your/path/to/biserver-ce/pentaho-solutions/system/hibernate/mysql5.hibernate.cfg.xml

 

Edit the file /your/path/to/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties

 

Edit the file /your/path/to/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml

 

Modify the JNDI file by editing /your/path/to/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties

 

Add the mysql connector to the lib folder by making a symlink:

Edit the file /your/path/to/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml to prevent HSQLDB to start. Remove or make a comment from the lines below.

That is it you are now ready to start pentaho:

You can now access pentaho in your browser at http://your.server.name:8080

Install Pentaho Data Integration (PDI) on Ubuntu Desktop

This section explains how to install PDI (Pentaho Data Integration aka Kettle) on an Ubuntu Desktop. We did this on Ubuntu 14.04 and 15.04.

Install the Java Development Kit

Make sure you have the Java Development Kit installed. You should have version 8 installed, you can check this with:

If the answers your machine gives you are not the ones you want, you can install version 8  with:

Obviously this could be replaced by newer default versions as time moves on (sourire)

Install PDI

Download PDI (Pentaho Data Integration) from http://sourceforge.net/projects/pentaho/files/Data%20Integration/6.1/pdi-ce-6.1.0.1-196.zip/download. Extract the downloaded zip file into the folder where you want it to be (I use /home/erik/pentaho/).

Install the JDBC driver

You might have to install the JDBC driver for the connection to the MySQL CiviCRM database. Check http://stackoverflow.com/questions/11634181/pentaho-data-integration-sql-connection/32873938#32873938 for more details. To install the JDBC driver on your Ubuntu desktop:

You now need to create a link so PDI knows where to find the JDBC driver. This link should be in your PDI folder:

We use JNDI to connect, read more about the advantages of JNDI here: http://stackoverflow.com/questions/7760712/why-use-jndi-for-data-sources

To be able to connect with JNDI in PDI you need to edit the file /your/path/to/data-integration/simple-jndi/jdbc.properties in an editor and add the following lines. We defined two connections:

  1. the civicrm database (which is the source database where the data is extracted from)
  2. to the datawarehouse (which is the target database where the transformed data will be loaded to)

You can now start the PDI user interface (aka Spoon) by executing spoon.sh from the folder:

Install Pentaho Schema Workbench (PSW) on Ubuntu Desktop

This section explains how to install the Pentaho Schema Workbench aka Mondrian on an Ubuntu Desktop. We did this on Ubuntu 14.04 and 15.04.

Install the Java Development Kit

Make sure you have the Java Development Kit installed (which you will have if you have followed all the steps from the PDI installation). You should have version 8 installed, you can check this with:

If the answers your machine gives you are not the ones you want, you can install version 8  with:

Obviously this could be replaced by newer default versions as time moves on (sourire)

Install Schema Workbench

Download the Schema Workbench from http://sourceforge.net/projects/mondrian/files/schema%20workbench/3.12.0/psw-ce-3.12.0.1-196.zip/download. Unpack the file in the folder you want (i use /home/erik/pentaho)

Install the JDBC driver

You might have to install the JDBC driver for the connection to the MySQL CiviCRM database. If you have already installed PDI you already have the JDBC Driver. Check http://stackoverflow.com/questions/11634181/pentaho-data-integration-sql-connection/32873938#32873938 for more details. To install the JDBC driver on your Ubuntu desktop:

You now need to create a link so Schema Workbench knows where to find the JDBC driver. This link should be in your Schema Workbench folder:

No JNDI

You do not need to set up JNDI for Schema Workbench

 

You should now be able to start the Schema Workbench from the folder:

Install Pentaho Report Designer (PRD) on Ubuntu Desktop

This section explains how to install the Pentaho Report Designer on an Ubuntu Desktop. We did this on Ubuntu 14.04 and 15.04.

Install the Java Development Kit

Make sure you have the Java Development Kit installed (which you will have if you have followed all the steps from the PDI or Schema Workbench installation). You should have version 8 installed, you can check this with:

If the answers your machine gives you are not the ones you want, you can install version 8  with:

Obviously this could be replaced by newer default versions as time moves on (sourire)

Install Report Designer

Get Pentaho Report Designer from http://sourceforge.net/projects/pentaho/files/Report%20Designer/6.1/prd-ce-6.1.0.1-196.zip/download. Unpack in the folder you want to use (I use /home/erik/pentaho).

Install the JDBC driver

You might have to install the JDBC driver for the connection to the MySQL CiviCRM database. If you have already installed PDI or Schema Workbench you already have the JDBC Driver. Check http://stackoverflow.com/questions/11634181/pentaho-data-integration-sql-connection/32873938#32873938 for more details. To install the JDBC driver on your Ubuntu desktop:

You now need to create a link so Report Designer knows where to find the JDBC driver. This link should be in your Report Designer folder:

We use JNDI to connect, read more about the advantages of JNDI here: http://stackoverflow.com/questions/7760712/why-use-jndi-for-data-sources

To be able to connect with JNDI in PRD you need to edit the file /your/home/.pentaho/simple-jndi/default.properties in an editor and add the following lines. We defined two connections:

  1. the civicrm database (which is the source database where the data is extracted from)
  2. to the datawarehouse (which is the target database where the transformed data will be loaded to)

You can now start the Report Designer by executing spoon.sh from the folder:

Étiquette
  • Aucun