Serving Opentaps Analytics with Pentaho

From Opentaps Wiki

This page describes how to serve Opentaps Analytics reports with Pentaho. It covers the various setup steps required to load a report using Pentaho solutions.

 Note:  This is preliminary and a work in progress.  It describes steps to 
 set up the current state of affairs, which is changing over time.  The
 final setup will be different.

Contents 1 Setup 1.1 Required Downloads 1.2 Configuring opentaps Analytics for Pentaho 1.3 Setting up Pentaho 1.4 Deploying Opentaps Analytics Package for Pentaho 1.5 Trial Run 2 Creating New Jasper Reports 2.1 Setting up Pentaho Design Studio 2.2 Creating an .xaction Definition 2.3 Set up Jasper Resource and Action 2.3.1 Resource Definition 2.3.2 Jasper Report Action 2.3.3 JNDI Database Connection 2.3.4 Report Definition 2.3.5 Select Desired Output Format 2.4 Compile and Test Solution

Setup

Required Downloads

1. Opentaps Analytics checkout containing a pentaho/ directory
2. Pentaho Design Studio - Get the 1.6.0 Open Source GA version. The file should be pentaho-design-stuido-*.zip. You could also try the 1.7.0 M1 release and let us know if it works.
3. Pentaho Business Intelligence Suite - Download the latest pentaho_demo_mysql5-1.7.0* (For opentaps analytics before revision 508, download pentaho_demo_mysql5-1.6.0*). This is preconfigured and precompiled for mysql 5.0
4. Mysql 5.0 or greater

Configuring opentaps Analytics for Pentaho

Edit the file install.properties in your analytics/ directory and specify the paths to Kettle, Pentaho's solutions directory, and JBoss:

# To install the data warehouse and run ETL jobs, Specify the path to Kettle
kettle.path=/Applications/Kettle.app/Contents/Resources/Java/

# To install reporting solutions, specify the path to pentaho-solutions
pentaho.path=/Users/sichen/Desktop/pentaho-demo/pentaho-solutions/

# To install reporting solutions for Pentaho deployed with JBoss, specify path to JBoss
# If you don't see anything being copied by the ant deploy-pentaho, then make sure your paths are correct
jboss.path=/Users/sichen/Desktop/pentaho-demo/jboss/

Setting up Pentaho

The precompiled and preconfigured pentaho-demo contains a Pentaho Solutions application deployed on JBoss. It is analogous to what we're doing with Pentaho running on Tomcat inside of opentaps. However, the intention is that someone can run Opentaps Analytics on any ERP system, hence the need to use this deployment as a standardized approach.

First thing you want to do is load the MySQL data model in analytics/scripts/mysql/pentahoDataDump_Mysql.sql:

 mysql -u root -h localhost < scripts/mysql/pentahoDataDump_Mysql.sql

Now you can start pentaho,

 sh start-pentaho.sh

You should be able to access it via http://localhost:8080. Pick a user, preferably Joe the Admin, and log in. It should show you a dashboard with sample data.

Deploying Opentaps Analytics Package for Pentaho

To deploy opentaps Analytics into your Pentaho server, run the command

 ant deploy-pentaho

after you have configured the location of the Pentaho and JBoss servers in build.properties. This will automatically copy it over.

Trial Run

In the upper right hand corner of the Pentaho dashboard is a menu. Click on GO -> Solutions. There should be a list of folders and Opentaps Analytics should be in it.

Click on Opentaps Analytics. Our reports are located in this folder. There should be at least one report for customers by country. You can click on it to generate it. This report is served by a JasperReports .jrxml file.

To see the Customer Location based on google maps, get to the url http://localhost:8080/pentaho/CustomerLocationMap.jsp

Creating New Jasper Reports

Suppose you create a new Jasper Report file in the .jrxml format using our handy tutorial. Place the .jrxml file in the opentaps solutions directory,

 pentaho-demo/pentaho-solutions/samples/opentaps/

Next we will create an .xaction file that instructs Pentaho to serve the report. We can use the standalone Pentaho Design Studio, which is based off the Eclipse project.

Setting up Pentaho Design Studio

First run the program by navigating to the Pentaho Design Studio folder and running the Eclipse application in there.

Then follow these steps to create the project,

1. Select New -> Project
2. Select General -> Project and press Next >
3. Enter Pentaho Solutions as the project name
4. Uncheck the Use default location box
5. Enter the path to your pentaho-demo/pentaho-solutions directory

If you open up the Pentaho Solutions folder in the navigation context, it should show you several folders, including our opentaps folder and your .jrxml file.

Creating an .xaction Definition

Pentaho defines its reports through .xaction XML files. The Pentaho Design Studio is a tool that helps create, test, and compile them into a final report solution. Generally we will be creating one .xaction definition per report and per .jrxml.

The easiest way to create an .xaction file is to use the wizard. There is a handy button in the toolbar:

Make sure that the container is /Pentaho Solutions/samples/opentaps and give it a file name related to your report. When you're done, the main Eclipse window should fill up with several sections,

• Process Inputs
• Process Actions
• Process Outputs
• Console area with Problems tab among others
• Content display window

TODO: describe each briefly and point out the tabs on the bottom.

Set up Jasper Resource and Action

The steps in general are:

1. Create a Resource Definition pointing to your .jrxml file.
2. Create a Jasper Report action
3. Specify the Database Connection with JNDI (analytics)
4. Connect the Jasper Report action to the resource (Report Definition field)
5. Select desired output format

Resource Definition

Right click on the Process Inputs box and add a new resource. Give it a name such as report-definition and enter the name of the .jrxml file without any path into the Location field. It will use the relative path, which should be the pentaho-demo/pentaho-solutions/samples/opentaps directory. Make sure that the Source Type is solution-file.

Jasper Report Action

Right click on the Process Actions box and select Add -> Report -> Other -> Jasper

JNDI Database Connection

Click on the Jasper Report action. The main window should fill in with options. Select JNDI as the Database Connection and select analytics from the dropdown.

Report Definition

Drag the report-definition from the Process Inputs box to the field named Report Definition (TODO image showing this).

Select Desired Output Format

The Report Format field allows you to change the output from HTML to other formats such as PDF. For now, pick either PDF or HTML.

Compile and Test Solution

Make sure that Pentaho is running. Click on the Test tab. It should ask you if you want to save your xaction. Select yes. This will compile the .xaction file into a .jasper file and allow you to run it.

1. In the Pentaho Server URL field, type in http://localhost:8080/pentaho
2. Click on the Test Server button and it should show you the login page.
   1. Select the Admin user and log in.
3. Click on the Generate URL button and it will print out the URL to access this report
4. Click on Run to access the report

At this point your report should be rendered in the main window. You can also try it via a web browser.