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.

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)

 

 

 

Tech Tips in Penaho Server – publishing password setting for server – Disable the folder names in Browser panel in the Server

Hi guys..

The below tips are very useful in Pentaho Server for BI developers.
1) publishing password setting for server
You need to publish the reports or schema(s)[developed in PSW] once you done with developing. For this you need set the publishing password for Pentaho server.
Go to this location 
<location of Pentaho>biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system\publisher-config.xml and edit the “publisher-config.xml”  file.

Give your publish password b/w  these tags  <publisher-password> </publisher-password>

Example:
<publisher-password>password</publisher-password>    ( password can be any name that you wish to give)

Snapshot is a worth more than text. find the image below.

Save the file. stop the server and start the server.

2) Disable the folder names in Browser panel of Pentaho Server
* What ever the folder you create in Browser panel of pentaho server, it directly stores   under pentaho-soulutions
i.,e in this location
<Pentaho location>Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions
* You can disable all of your folders that comes with installation & the later created ones  which you don’t want to appear in the browser panel.
*   Inside every folder(pentaho default or the folders created by you) you can find “index.xml” file. You need to edit this file. You need to change visable tag as “false”.
Find the sanpshot.

You need not restart the server. Just you need to refresh the browser panel in the server.
You can observer the invisibility of the folder.

Sadakar Pochampalli.

 

 

Date Input Controls/ Components in Pentaho CDE with a working example

Hello guys…!!!

In this post you will learn about how to create date input controls in CDE.
The dashboard images which I am going to show below are having lots of .css code. I’m not covering all the things but concentrating only on Date input controls in CDE.

My Environment :
BI Server: Pentaho 4.5
CTools: Not found the version numbers of  C-Tools(Unable to check them)– perhaps..! lesser than 13
Database : hsqldb (Comes along with Pentaho installation — A java based small db for servers like pentaho)
NOTE: Images are taken in this post after creating the dashboard, please find the yellow rectangular boxes to understand.

You need to work out @ 3 places in “Components” section.
1. Generic
2. Selects
3. Charts
Explained from step 1 to step 3
You need to work out  @ 1 places in “Data Sources” section. i.e, all in “Properties”
I’m not concentrating on Layout part for this example. So wherever the HtmlObject you find in this example replace them with your working HtmlObjects.

Step 1: Creating parameters
* Design your dashboard as per your requirement ( In the images you can find the sample designs).
 In the “Components” section:
1. Click on components
2. Click on Generic
3. Click on Date parameter
4. Go to properties give the name
eg: param_start_date
5. Click + symbol  per another date parameter
6. Go to properties give the name
eg: param_end_date

Find the  image

Step 2: Creating Date input Components
In the “Components” section
1. Click on Components
2. Click on Selects
3. Click on “Date input Component”
4. Go to Properties give name. eg: start_date in  my example
5. In the properties give HtmlObject. eg: start_date_select
* HtmlObject is the name of the column where you are putting
your dateinput control.
6. Give  Parameter.[It will appear once you try to type]
eg: In step 1, created parameters. So here give “param_start_date”
7. Give Listeners[click on it, you will find the list of Listeners. Click OK]
8. Repeat 3-7 for “end_date” Date input
component.

Step 3:
Find the steps in image.

 
HtmlObject is the place where you are putting your chart.

Step 4: Applying parameters for Chart & Query
            In the “DataSources”
Find the steps in the image
Find the sub sequent image for  Parameters applying for chart & Query.

do same as shown in images.

Step 5:
Save the dashboard and then Click on “Preview” button.
See the images below for sample out put with date input controls.
Preview of the dashboard with out any input controls selection

Preview of the dashboard after selecting date input controls.. find the images below one by one.

Find the image for observing whether the chart becomes dynamic or not with different date input controls.

That’s it.. You are done with date input controls on dashboard.
Thank you for reading this small post.

Sadakar
[email protected]
Helical IT Solutions Pvt. Ltd,
Hyderabad.