Multi-tenant support for prompts in Dashboard

Many times we require having multi tenant environment for Pentaho customers. There are commonly known methods those are already designed for this purpose like “IDBDataSource” customization can be used to return custom data source name based upon a value in session for currently logged in user. When it comes to achieve multi-tenancy for prompts in dashboard without maintaining different connections for different users; choices are few and not wildly known.

Pentaho v5 comes with a bunch of rest based web services; one of those is to list down and see details of a data source in repository those are created using Data Source Wizard.

http://localhost:8080/pentaho/plugin/data-access/api/datasource/dsw/domain

Looking at the results of this web service; we can derive the fact that Pentaho stores these data sources in XMI format. If we further look at the rest service that downloads a specific data source definition;

http://localhost:8080/pentaho/plugin/data-access/api/datasource/dsw/domain/{DSWID}

We can find out complete structure of how Pentaho stores a data source looking at the downloaded zip file via above rest service and we come to know that it stores the queries already compiled like prepared statements and are not parameterized.

Fact being that Pentaho stores data sources in XMI format; leads to a conclusion that we can use customization of SQLGenerator to achieve multi tenancy in dashboards directly. We actually tried this and it is working for multi tenant environment. SQL Generator is a special class that is called when Pentaho Metadata queries the database.  There are two methods that can be overwritten.  The first is preprocessQueryModel and is the recommended method to overwrite.  It is called before the SQL is generated.  The usual approach is to simply add an AND condition to the query to restrict data, such as by building.  The second method, processGeneratedSql, is called after the SQL is generated.  While it is possible to modify this query, it would involve parsing and modifying the string query.

Steps for customizing preprocessQueryModel  method are below at a glance.

  1. Create a java class that extends pentaho.metadata.query.impl.sql.SqlGenerator class and override preprocessQueryModel method. Example is following code snippet.tenated_db1
  2. Compile class file and export it in a jar file at tomcat/webapps/pentaho/WEB-INF/lib
  3. pentaho-solutions/system/pentahoObjects.spring.xml should be modified to change the class defined for the sqlGenerator The following shows an example of the bean mapping: <bean id=”sqlGenerator” class=”com.mycompany.CustomSqlGenerator” scope=”prototype” />
  4. webapps/pentaho/WEB-INF/classes/classic-engine.properties should be modified as well by defining a new parameter: pentaho.reporting.engine.classic.extensions.datasources.pmd.SqlGeneratorClass=com.mycompany.CustomSqlGenerator

Once done, restart Pentaho and load a XAction file to set SESSION_CUSTOMERNUMBER variable that we are trying to read in java code. Create a new connection using data source wizard that uses SampleData as underlying connection:

tenated_db2

Now if we use TenatedDataSource in our dashboard; our SQLGenerator class will automatically pick the session variable SESSION_CUSTOMERNUMBER from current user session and append a where clause in final query like this:tenated_db3

Note that while creating data source, we have not defined a where clause and it is being generated on the fly as per the current session value for SESSION_CUSTOMERNUMBER variable. That is what we wanted to achieve.

Ref : http://javadoc.pentaho.com/bi-platform530/webservice530/

-Ankit Srivastava & Harsh Kumar

Custom Label Background in Pie Chart in Pentaho Report Designer

Hello folks!! It’s been quite a while since I wrote my last blog, and it’s always enthralling to be back to writing. Pentaho Report Designer, a tool which I love to explore, more specifically, on the charting front, as it is the least documented and most useful part of any reporting requirement.

This blog is about doing an interesting task in the PRD with pie chart. Recently, after looking at my blogs, one of my friend asked me about the possibility of a requirement in PRD, which was changing the background of labels in Pie Chart in PRD. At once, I thought it should be a configurable part, but I was not amazed on finding it missing from the configurable options. But nonetheless, I thought of doing it with Beanshell scripting (which I love the most in PRD), and got it working in just a little time. That friend of mine after getting the solution, asked me to write a blog about it as he wanted to help people with this knowledge and I’m more than happy to do so.

So here’s what you have to do in order to get the custom background of Pie chart labels in PRD. In chart properties, go to the Post Processing script section and select Beanshell from the dropdown, and write the following code in the pop up to get it working :

import java.awt.Color;
import org.jfree.chart.plot.PiePlot3D;
import org.jfree.chart.plot.PiePlot;

PiePlot plot = (PiePlot) chart.getPlot();
plot.setLabelBackgroundPaint(Color.WHITE);
plot.setLabelOutlineStroke(null);
plot.setLabelShadowPaint(Color.WHITE);

One can easily customize this code to suit their need, as in changing the color to some other instead of white and much more. Below is the screenshot of what output will look like without this custom code.

Without Custom Code

Without Custom Code

With this custom code

With Custom Code

With Custom Code

Hope this helps someone and saves somebody’s valuable time. Please feel free to leave comments and requests for more such functionalities with Beanshell scripting in PRD.

Here is the sample prpt with Pentaho’s sample data.

Pentaho BA CE Installation

Pentaho, a buzz in the world of Business Intelligence now-a-days, is quite a comprehensive tool for analytics. The prime reason of its popularity (in my perspective) is its availability in two editions i.e. Community (CE) and Enterprise (EE). Where in Enterprise, one can avail a thousand of documentation & support for any and all purposes, but when it comes to Community edition, documentations are just for namesake.

Recently, when I was working on Community edition of Pentaho, the main challenge was the installation of Pentaho CE, since there is no proper documentation, neither official nor unofficial. It seems an easy task when it comes to install any tool, but in case of Pentaho, there are some peculiar settings which are prerequisites for its installation.

I did a lot of R&D and struggled for it, finally I decided to document it properly and share it to the Community so that people can be benefited from my struggle. Below is the straightforward approach to install Pentaho CE on Windows or Linux machine.

Since Pentaho CE always comes with archival installation as there is no EXE based installation, so before proceeding with installation download the latest stable version of archival from www.community.pentaho.com website. All the Pentaho BA CE components are available on under Download section.

After Downloading RAR file from community website. Unzip this to desired location and provide the complete read, write and execute permission for this folder (I assume the user is having complete permission for this folder).

Folder security permission

Folder security permission

Also remove the read only access.

Folder read only permission

Folder read only permission

Install Java JRE or JDK

Make sure that the version of the Java Run-time Environment (JRE) or the Java Development Kit (JDK) that Pentaho needs to run is installed on your system. You do not need to uninstall other versions of Java if you already have them running on your system. These instructions explain how to check for your default version of Java that is running on your computer and where to get the required version if you need one. For this installation the least recommended version in Java 7.

  1. Open a Terminal or a Command Prompt Enter this command.

    Java –version

  2. If the version of Java does not match the version needed to run Pentaho software, download it from Oracle site and install it.

Set Environment Variables

Set the PENTAHO_JAVA_HOME variable to indicate the path to the Java JRE or JDK that Pentaho should use. If you do not set this variable, Pentaho will not start correctly. To set environment variables, you should be logged into an account that has administrator-level privileges.

For Windows
  1. Go to “Start Menu > Computer
  2. Right Click on Compute and Select Properties
  3. Under Properties you will find the below screen, and select “Advanced system settings”.
Windows Properties for Advanced system settings

Windows Properties for Advanced system settings

  1. After Selecting “Advanced system settings” Select the “Environment Variables

    Environment Variables

    Environment Variables

  2. Once you select “Environment Variables” you will get another screen where you can add “JAVA_HOME” & “PENTAHO_JAVA_HOME”
set create Environment Variables

set create Environment Variables

  1. Once you set the path for PENTAHO_JAVA_HOME Click on OK

For Linux

  1. Open a terminal window and log in as
  2. Open the /etc/environment file with a text editor.
  3. Indicate where you installed Java in your /etc/environment file by typing this

    export PENTAHO_JAVA_HOME=/usr/lib/jvm/java-7-sun

    Note: Substitute /usr/lib/jvm/java-7-sun with the location of the JRE or JDK you installed on your system.

  1. Save and close the file.
  2. Log out, and then log back in for the change to take effect.
  3. Verify that the “PENTAHO_JAVA_HOME” variable is properly set by opening a Terminal window and typing this

    env | grep PENTAHO_JAVA_HOME

  1. The path to the variable should appear. If it does not, try setting the environment variable again.

Start BA Server

 For windows
  1. Launch the “start-pentaho.bat” file under biserver-ce folder
  2. Open a web browser and enter this URL: http://localhost:8080/pentaho.
  3. The Pentaho User Console Log On window appears.
For Linux
  1. Launch the “start-pentaho.sh” file under biserver-ce folder
  2. Open a web browser and enter this URL: http://localhost:8080/pentaho.
  3. The Pentaho User Console Log On window appears.

 

Custom Value Axis Scale in Pentaho Report Designer

The Pentaho Report Designer (PRD) has evolved as a very feature-rich product over the last few years. But still there are some features, that are not documented anywhere. You come to know these features as you dig more and more into the tool.

In this blog, I want to share one of those many unsung possibilities in PRD. While working with charts in PRD, sometimes it is required to have a custom range on the value axis(Y Axis). This seems to be a very common need to customize the scale as required but, PRD does not have any default option for this functionality, but still there is a out of the box work around which can make this happen and i.e. Beanshell Script.

For those who are new to Beanshell Script, Beanshell Script is like a blessing in PRD, especially when you are working with charts. It is one of the Chart-Post Processing Script that you can write in PRD. To write a Beanshell Script in PRD just select the type of Chart-Post Processing script as “beanshell” from the available values and write the script in the next field. Refer to the image for more clarity on where to write the beanshell script.

Pentaho Report Designer

Pentaho Report Designer

You can write the script in the new window which pops up. There are two ways you can customize the value axis range using beanshell script:

1)      Setting the fixed upper and lower bound for the range

2)      Taking the upper bound and lower bound from the minimum and maximum values of the chart

Fixed Upper and Lower Bound:
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.ValueAxis;

CategoryPlot chartPlot = chart.getCategoryPlot();
ValueAxis yAxis= chartPlot.getRangeAxis();
yAxis.setUpperBound(maxValue);
yAxis.setLowerBound(minValue);

Where “maxValue” and “minValue” can be any values that you want to set as upper and lower bound respectively.

Setting Bounds Based on the Values of Chart:
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.axis.ValueAxis;
import java.util.*;
import java.math.*;
import java.util.List;
import java.util.ArrayList;

CategoryPlot chartPlot = chart.getCategoryPlot();
ValueAxis yAxis= chartPlot.getRangeAxis();
data = chartPlot.getDataset();
keys = data.getRowKeys();
ArrayList dataVals = new ArrayList();
for (int i=0;i<keys.size();i++)
{
ab=data.getValue(i,i);
dataVals.add(ab);
}
Double maxVale=(Collections.max(dataVals)).doubleValue();
Double minVale=(Collections.min(dataVals)).doubleValue();
yAxis.setUpperBound(maxVale+5);
yAxis.setLowerBound(minVale-5);

And here you go. This is how you can achieve this.Here is the sample output.

Custom Value Axis Scale

Custom Value Axis Scale

And here is the link to download the sample prpt for this.

Trust me,  it is one of the many possibilities using Beanshell Script.Hope this post helps people and soon will be coming with many more possibilities in PRD.

Horizontal Gradient Bar chart in Pentaho Reporting Designer

As a hard core UI / UX designer, I always feel color plays a very important role in our life. Without colors anything will be colorless and same I feel when I see any BI dashboards and reports without any color.

I never understand why people want to create a report in Gray scale or in single color only. Reports should have some colors in that, where people can see the details and highlight those points where they need to focus more.

On one of my recent project, I have seen a very beautiful report which was created in PowerPoint and same I need to replicate in Pentaho Report Designer. The report was pretty cool and was having so many details and challenges.

Apart from other challenges like Multiple Language, Dynamic Formatting and complex formulas, I was having big time challenge with Gradient Horizontal Bar Charts. I have never seen gradient color in horizontal bar charts under Pentaho Report Designer, so was little surprised.

But finally understood the way to handle this challenge and now showcasing the same.

For creating a gradient horizontal bar chart in Pentaho report designer I used a beanshell code which works with JFree Charting library.

There is couple of other Post-Processing script also available in Pentaho Report Designer which processes after the chart renders. I created this with beanshell because JFree has good support with beanshell rather than other Post-Processing script.

You can add the beanshell script on your chart in report designer, by double clicking on your chart and selecting beanshell under Scripting > Chart Post-Processing Script in left panel.

Pentaho Report Designer

Pentaho Report Designer

Then write the following beanshell script for gradient horizontal bar chart

import java.util.*;
import java.awt.Paint;
import java.awt.Color;
import java.awt.Stroke;
import java.awt.BasicStroke;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.ValueAxis;
import org.jfree.chart.renderer.category.CategoryItemRenderer;
import org.jfree.chart.renderer.category.BarRenderer;
CategoryPlot myPlot = chart.getCategoryPlot();
BarRenderer categoryItemRenderer = myPlot.getRenderer();
categoryItemRenderer.setShadowVisible(false);
plot = chart.getPlot();
renderer = plot.getRenderer();
data = chart.getPlot().getDataset();
keys = data.getRowKeys();
Color[] c={
new Color(0x4784D7),
new Color(0x1F4F8F),
new Color(0xDBE7A5),
new Color(0xC8DB78),
new Color(0x7D838F),
new Color(0x272B39)
};
for (int i=0;i<keys.size();i++)
{
int j=(i*2);
g = new GradientPaint(0, 0,c[j],10, 0, c[j+1],true);
renderer.setSeriesPaint(i,g);
}

Here is the final result

Horizontal Bar Chart

Horizontal Bar Chart

Download the .PRPT file from here: http://tinyurl.com/gradientbar