Embedding BI Reports (Jasper Reports/ Jasper Server/ Pentaho Reports/ Pentaho Server)

There are many methods of embedding BI reports, this blog tries to explain the meaning of Embedded BI, different approaches which can be used, their respective advantages and disadvantages

 

A)     What is embedded BI?

Lets say you have developed your entire BI solution which includes reports, dashboards, mashups, analysis etc, the platform which is used can be anything like Jaspersoft or Pentaho. The process of integration of this BI solution with your existing application/software/portal is called Embedded BI.

Embedded BI provides a lot of advantages like ability to add more reports over and above the existing canned reports which your application might provide, the look and feel and customization of all the reports can be changed as per the software/user preference, better visualization, unified application for everything. Biggest advantage can be your product value will increase much more, hence resulting in much more sales and revenue.

a) Embedding using iFrame Method :-

iFrame method of embedding BI reports can be used for embedding Jasper server, Jasper reports and Pentaho server, Pentaho reports. The report pulled in this case is using iframe as a tag. Even though the report is external, but to an end customer he will not be able to recognize the same.

The advantages of using iFrame method is this method is fast and quick to implement. The disadvantages includes it will be difficult to manage user, also in this case there can be cross browser compatibility issues, security is a major issue here.

 

b) Embedding Using webservice:- Webservices can also be used to integrate reports, dashboard, interactive report or/and ad-hoc reports. We can use webservices to accept the information from the Jasper/Pentaho server, this Jasper server can be anywhere ( on Tomcat or any other server). Using webservices we will invoke the response from Jasper Server/Pentaho server. This response can be can be then embedded inside application/software. Jasper server/Pentaho server also supports RESTFUL service (JSON format responses). In this approach there are no or less security related issues.

The disadvantage of this approach is there are no interactive features available of the report which thus results in poor user experience. Also in order to handle and understand the responses from jasper server/Pentaho server, custom coding is required in the application. In case if we had put input controls in the report, the same functionality then needs to be implemented in the application via custom coding. Aside, having many webcalls at times can be resource intensive as well.

The advantage of this approach is webservices approach gives more seamless integration since there is no security issue and no thirdparty calls (like in the case of iframe). Also in this case there are no interbrowser compatibility issues (assuming it is handled by your developer team).

c) Embedding using Jasper/Pentaho Libraries:- In this method we create JRXML/Pentaho report (prpt) files using ireport/Pentaho report designer. Compile these reporting using Java API, and then use this compiled code to generate views using Jasper Server API calls

The advantage of this method is this gives the most seamless integration. Also since all the information is present as a part of the application/server, no third party calls are required.

Disadvantage of this approach is some interactivity is lost in this method. Also the input parameters are also required to be created in the own application via custom coding.

 

Please get in touch at [email protected] for much more details about the respective approach, how to embed the same using these approaches, which was is the best suited to your needs and much more.

Reading data from single data source(Single SQL Query) for two charts in pentaho CDE

Hi guys…!!!

Community Dashboard Editor is the best reporting/dash boarding  tool that I have worked. It’s smart functionality made me love to work with it and exploring the things time by time.

In this post you’ll learn how to fetch different columns from result set of a single query and use them in different analysis purpose in dash boarding.

Recently I needed to work with a single query data source(SQL) of having 3 columns result set …
Lets say there are 3 columns A,B and C where as A column is having some category names and B and C are having some values

A  B   C
————–
abc 2   4
pqr  6   8
xyz 10 5
and etc.

From the result set A&B are on first chart and A&C are on other chart..
Now how ?????? This question leads me to check the “Data sources” section of CDE.

Follow the steps below.

1) Click on the “sqloverjndi” which  you created for your SQL query.
2)  In the properties section you can find an option called “Output options”. Just click on  it.
3) Let’s say you have 3 columns in your result set and these 3 columns takes index values starting from 0 to n.. i.e., A column index is 0 , B column index is 1 and for the C  value 2 is the index.

  NOTE: if you have more numbers of columns you can give as many indexes by clicking “Add” button as many times.
4)For Chart 1:
i) Click on the chart component where you want show A& B columns ( A is category B is value)
ii) In the properties(Click on Advanced properties) click on “Pre Execution”
iii) Write this below code
function f() {
this.chartDefinition.readers = [
{names:’category’, indexes: 0},
{names: ‘value’, indexes: 1},
{indexs: 2}
];
}
Why this {indexs:2} ? if you omit this the values of index2 append to the category names… to eliminate that problem you need to write it.

 5) For Chart2 :
Repeat the steps in in point 4)
Slight changes in code …

function f() {
this.chartDefinition.readers = [
{names:’category’, indexes: 0},
{names: ‘value’, indexes: 2},
{indexs: 1}
];
}

You are done with reading data from single query of 3 columns where 1&2 for one chart and 1&3 for another chart…

Save your dashboard and see the preview.

Sadakar
BI developer
( “Learning never exhausts the mind” )

Extract year,quarter,month & day from date input control in pentaho CDE using java script – MDX Query Scenario

Hello guys…!!

Some times you need to extract the parts(year,month,day) of Date for some specific use..
For example:
Assume you are creating a report with MDX query which has dimension called “Date” having levels “Year”, “Quarter”,”Month” & “Day”.

(Note : Assume your schema is having
Year: yyyy Quarter: 1 or 2 or 3 or 4 Month : 1,2,3,4…….12  Day : 1,2,3…. 31)

Also assume you do not have direct date dimension in your schema (i.e, you do not have a dimension which takes ‘yyyy-MMM-dd’ column.

But, you need to display date(yyyy-QQ-MMM-dd) or (yyyy-MMM-dd) on X-axis of chart.. Remember you are not having any direct date in your schema but have “Date” with year,quarter,month & day as levels.

From your start_date(or end_date) input control you can extract the individuals using the following java script for CDE and use them properly in your MDX date range place.

This should be done in “Pre Execution” section of Chart component

function extract_function(){

tmp_date = new Date(param_start_date);
var quarter = [‘Q1′,’Q2′,’Q3′,’Q4’];
var month = [‘JAN’,’FEB’,’MAR’,’APR’,’MAY’,’JUN’,’JUL’,’AUG’,’SEP’,’OCT’,’NOV’,’DEC’];

param_start_year = tmp_date.getFullYear();
param_start_quarter = q[Math.floor((tmp_date.getMonth()+ 3) / 3)-1];
param_start_month = m[tmp_date.getMonth()];
param_start_day = tmp_date.getDate();

tmp_date = new Date(param_end_date);
param_end_year = tmp_date.getFullYear();
param_end_quarter = q[Math.floor((tmp_date.getMonth()+ 3) / 3)-1];
param_end_month = m[tmp_date.getMonth()];
param_end_day = tmp_date.getDate();

}

NOTE: 
* quarter and month variables are taken as arrays with default values.
* You need to calculate the month and send it to array ..
When you calculate months
1 becomes JAN, 2 becomes FEB and etc as well
When you calculate quarters
1 becomes Q1, 2 becomes Q2, 3 becomes Q3 & 4 becomes Q4

Forget about your problems …!!!! and Meet us @   http://www.helicaltech.com/contact.php

Sadakar

BI developer

 

Pentaho BI Server community 4.8 Installation in existing tomcat with PostgreSQL in Linux/ubantu OS

This post teach you how to install the pentaho-bi-server.4.8.0 stable version in tomcat server with postgreSQL database.

I’ve gone through many posts but could not find all the stuff in a single place. I just worked out and sharing the experience with it.
If you find any difficulty in the below steps feel free to drop a mail @ [email protected] for help.

Prerequisites :
1. Pentaho BI server CE 4.8.0 stable
2. tomat 6 server
3. PostgreSQL
4.PuTTY/WinScp

1)  Download the biserver-stable-4.8.0 using the following command in some folder.
Syntax :
wget URLOfTheDownloadLocation
Example:
wget http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable  /biserver-ce-4.8.0-stable.zip

2) After downloading completed unzip it using uznip command.
Syntaz :
unzip .zipfileName
Example:
unzip  biserver-ce-4.8.0-stable.zip
After unzipping you can find two folders .. They are i)administration-console & ii) biserver-ce

3) Install tomcat server externally(archive based installation) in your favorite location.
Example:
I’m taking jasperserver tomcat to install the pentaho server.
[email protected]:/opt/jasperreports-server-cp-5.0.0/apache-tomcat#

4) Executing .sql files in postgresSQL
* You need to build the two databases they are i) hibernate & quartz
*  Reason: You are going to install the pentaho bi server with postgreSQL(not with the hsql which directly comes with the download to interact with the server),hence you need to build the two databases for pentaho server to work properly.
* Where you can find the .sql scripting files ?
Check in the location :

/biserver-ce/data/postgresql
( biserver-ce is the folder where you unzipped in step-2)

Scripting file names:

create_quartz_postgresql.sql
create_repository_postgresql.sql
create_sample_datasource_postgresql.sql
migrate_quartz_postgresql.sql
migration.sql
Commands to run the .sql files from putty :

[email protected]:/opt/jasperreports-server-cp-5.0.0/postgresql/bin# ./psql -U postgres -p 5432 -a -f /home/sadakar/softwares/pentaho/biserver-ce/data/postgresql/
create_quartz_postgresql.sql

In the similar way execute the remaining scripting files.. you just need to change the file name in the above command.

Imp points to NOTE when you run the script files * You need to go to the “bin” folder of postgres installed and run the above command.
* In my case I’m using the the postgresql that installed with jasper server.
* In the above command -U user name -p Port number of the postgreSQL
* Must specify   -a -f  in the command otherwise the script will not run.
* When you run the script it’ll ask you for postgreSQL password : give password as “password”.
If you use any other password for postgres give that password
* When you run the script it’ll ask for database user names :
Open the script files in your fav editor and find this line

CREATE USER pentaho_user PASSWORD ‘password’;
This means for the quartz database password is “password” and for the same follows to other scripting files while executing.

 NOTE:
* Once you execute all the scripting files check the postgreSQL databases whether the “hibernate” and “quartz” databases created or not.
* If you do not find the databases you might done wrong some where , cross check again the steps.
* And find 12 tables in “quartz” database and 1 table in “hiberante”database.

Hmmm… You are not done with the databases actually… b’z you do not have all the tables in “hibernate” database. B’z the scripting files do not have all the data & tables.

I’ll give you the links here to run the scripting files to get the tables.
At present do not think of it. Find this in following steps…!!

5. Changes in config.xml file of tomcat server
* You need to add the following code to the config.xml file
* location of the file : tomcat/confg/context.xml
* In my case the location is :
[email protected]:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/conf#
<contex>
<WatchedResource>WEB-INF/web.xml</WatchedResource>

<Resource name=”jdbc/Hibernate” auth=”Container” type=”javax.sql.DataSource”
factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″
maxWait=”10000″ username=”hibuser” password=”password”
driverClassName=”org.postgresql.Driver” url=”jdbc:postgresql://localhost:5432/hibernate”
validationQuery=”select 1″ />

<Resource name=”jdbc/Quartz” auth=”Container” type=”javax.sql.DataSource”
factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″
maxWait=”10000″ username=”pentaho_user” password=”password”
driverClassName=”org.postgresql.Driver” url=”jdbc:postgresql://localhost:5432/quartz”
validationQuery=”select 1″/></Context> 
6. Adding postgresql-driver in the lib folder of tomcat
* You need to copy the postgresql-driver in the lib folder of tomcat
* location of the lib folder for tomcat is :  tomcat/lib
* In my example it is there at
[email protected]:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/lib# 
* You can directly download the postgresql driver using the following command or copy and paste it in lib folder if you are already using in some other place in your machine.
* Command is :
wget jdbc.postgresql.org/download/postgresql-9.2-1003.jdbc4.jar
 
7. Changes need to do inside pentaho-solutions folder

* This is quite interesting thing to work here.
* Before you do modifications in pentaho-solutions folder, you need to copy this folder similar to tomcat installation location( You can keep this folder any where you want).
* For example : I have copied this folder from bi-server folder to similar location where the tomcat is installed .(from step 2 of this artical)
i.e.,  At [email protected]:/opt/jasperreports-server-cp-5.0.0# ls

apache-ant     common        installation.log  license.txt        properties.ini    scripts                  uninstall.dat
apache-tomcat  ctlscript.sh  java              pentaho-solutions  releaseNotes.txt  Third-Party-Notices.pdf
buildomatic    docs          licenses          postgresql         samples           uninstall

* You need to configure the the settings for postgresql in applicationContext-spring-security-jdbc.xml file
* location of this file is : pentaho-solutions/system/applicationContext-spring-security-jdbc.xml

<bean id=”dataSource”
>
<property name=”driverClassName” value=”org.postgresql.Driver” />
<property name=”url”
value=”jdbc:postgresql://localhost:5432/hibernate” />
<property name=”username” value=”hibuser” />
<property name=”password” value=”password” />
</bean>

* Next, you need to configure setting in : applicationContext-spring-security-hibernate.properties
* location of this file is : pentaho-solutions/system/applicationContext-spring-security-hibernate.properties

jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

8. Changes need to do in hibernate folder
Navigate to “hibernate” folder from “system” folder of same “pentaho-solutios” folder.
* You’ll find different .xml files for different databases.
* You need to touch
i) hibernate-settings.xml and
ii) postgresql.hibernate.cfg.xml          files.. i.e., you need to do some modifications in these two files.
Changes in :
i) hibernate-settings.xml file
Comment this line
<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

Enable this line
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

ii) postgresql.hibernate.cfg.xml

* You need not to do any modifications in this but you need to have an eye in this file.
if your postgresql port number is different than 5432 , just give your one and if you give the appropriate host if you use any host number .

9. Changes in context.xml file of META-INF folder of tomcat
* You need to modify the “context.xml” file located in the tomcat/webapps/pentaho/META-INF folder.
* In my example: It is located at

[email protected]:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/webapps/pentaho/META-INF#

<Context path=”/pentaho” docbase=”webapps/pentaho/”>
<Resource name=”jdbc/Hibernate” auth=”Container” type=”javax.sql.DataSource”
factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″
maxWait=”10000″ username=”hibuser” password=”password”
driverClassName=”org.postgresql.Driver” url=”jdbc:postgresql//localhost:5432/hibernate”
validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES” />

<Resource name=”jdbc/Quartz” auth=”Container” type=”javax.sql.DataSource”
factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″
maxWait=”10000″ username=”pentaho_user” password=”password”
driverClassName=”org.postgresql.Driver” url=”jdbc:postgresql://localhost:5432/quartz”
validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES”/>
</Context>

NOTE: We deployed “pentaho” and “pentaho-style” folders in weapps folder of tomcat server.

10. Changes in web.xml file of WEB-INF folder of tomcat
You need to modify web.xml of WEB-INF folder of tomcat server. i.e,. tomcat/webapps/pentaho/WEB-INF/web.xml
* In my example the location of the file is :
[email protected]:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/webapps/pentaho/WEB-INF#

<context-param>
<param-name>solution-path</param-name>
<param-value>/opt/jasperreports-server-cp-5.0.0/pentaho-solutions</param-value>
</context-param>

 NOTE: give the path for the “pentaho-solutios” b/w <param-value> and </param-value> tags

* You also need to check the port number & URL for the pentaho server in the same web.xml file.
<context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://localhost:9090/pentaho/</param-value>
</context-param>

NOTE: if you use some other port number for tomcat other than 8080 , you must specify the port number as shown above.

11.Tomcat server shutdown & startup
*  Go to the bin folder of tomcat server and shutdown the server if it already runs.
* Start the tomcat server.
* Commands :
Shutdown: ./shutdown.sh
Startup :   ./startup.sh

12. Type the pentaho server URL in any browser
* Go to the URL of any browser( Mozilla firefox is preferable as it is having firebug facility to track the errors if you get any)

Meet us if you have a business @ http://www.helicaltech.com/contact.php

Sadakar(BI developer)