* set as it is in the image(User variables) do not concentrate on system variables
CLASSPATH,JAVA_HOME,PATH
System variables
JAVA_HOME, JRE_HOME for pentaho community installation.
Thanks,
Sadakar
For Developers: Pentaho BI Suite Tutorials - Place to find tips & tutorials
For Decision Makers: Single stop destination to find insights of your business with No cost
For Decision Makers: Single stop destination to find insights of your business with No cost
Friday 30 August 2013
path, classpath, java_home,jre_home settings for pentaho community edition in windows 64 bit OS
System variables
JAVA_HOME, JRE_HOME for pentaho community installation.
Thanks,
Sadakar
Monday 26 August 2013
Sorting & limit the bars on bar charts in Pentaho CDE using java script and without ORDER BY and limit functions in SQL Query ( Descending order)
Hi Guys...!!!
This post tech you how you can make your bar chart dynamic..
You can limit the number of bars on bar charts with out using the "limit" function in your query as well you can also sort the bars in ascending or descending order with out using ORDER by clause in SQL.
Some times you might have to deal with this type of functionality in your dashboard.
So here is a working example. Follow the steps...
Environment:
Pentaho BI server 4.8 stable with C-Tools(CDA,CDE,CDF 13.06) installed.
PostgreSQL
Aim :
1) Creating a dashboard with two horizontal bar charts.
2) How to use single query result set for two charts
3) Order by the bar on the dashboard with out writing ORDER BY clause in SQL.
4) limit the number of bars on charts with out writing "LIMIT" clause in SQL
Suppose your result set is like below
Query Eg :
SELECT ColumnA, columnB,ColumnC from table_Name GROUP BY ColumnA
Result set Assumption:
ColumnA ColumnB ColumnC
pentaho 45 75
jasper 23 34
pdi 90 22
and assume there are 30 rows in the result set ...
You are using single query to plot the two charts on dashboard using ColumnA,ColumnB & ColumnA,ColumnC.
When you use ORDER BY clause in the query, you can only either order by columnB or ColumnC but you can not order by with ColumnB and ColumnC.
* In CDE, there is a functionality called "Indexs". Indexs for columns in pentaho CDE starts from 0,1,2 and etc.
* So ColumnA index is 0, ColumnB index is 1 and vice versa.
* Prepare your environment for the dashboard and have a look at the "Data sources" now.
* Click on "Output Options" and give index values as 0,1,2 by clicking "add" button multiple times.
How to get ColumnA, ColumnB on first chart ? (B'z you are using single query result set)
* Now click on the first chart component
* In the Advanced properties click on "Pre Execution"
* Write the below code to fetch ColumnA, ColumnB on first bar chart.
function f() {
this.chartDefinition.readers = [
{names:'category', indexes: 0},
{names: 'value', indexes: 1},
{indexs: 2}
];
}
NOTE:
ColumnA=category and is accessed with Index 0
ColumnB=value and is accessed with Index 1
index2 is written in the code b'z to ignore the value appending to the category showed on bars.
How to sort and limit the bars on first chart ?(B'z you are using single query result set)
* Click on the "Post Fetch" option from the Advanced properties.
* Write this code, this code will limit the number of bars on chart and sort the bars in descending order.
* In the code below "param_no_of_end_points" is the parameter created in the "Generic" section.
* And for the parameter create a select in the "Selects" section with "Text input" component.
function f1(cdaData) {
var categIndex = 0;
var valueIndex = 1;
var param_no_of_end_points = +Dashboards.getParameterValue("param_no_of_end_points");
if(isNaN(param_no_of_end_points))
{
param_no_of_end_points = 0;
}
var resultset = cdaData.resultset.slice();
var compareDesc = function(a, b)
{
return a === b ? 0 : a > b ? 1 : -1;
};
resultset.sort(
function(rowa, rowb)
{
return compareDesc(+rowa[valueIndex],+rowb[valueIndex]);
}
);
if(param_no_of_end_points > 0)
{
resultset.splice(0,resultset.length-param_no_of_end_points,0);
cdaData.resultset = resultset;
}
cdaData = {
metadata: cdaData.metadata,
resultset: resultset
};
return cdaData;
}
Sources for the CODE:
1) http://forums.pentaho.com/showthread.php?148352-Order-by-on-bar-chart-with-out-touching-the-query-in-CDE
2)http://www.w3schools.com/jsref/jsref_splice.asp
and etc.
Write the same code for the second chart but make sure to use different function names and Index value. In an application we can not write two functions with same name.
Save the the dashboard and see the preview.
Sadakar
BI developer
This post tech you how you can make your bar chart dynamic..
You can limit the number of bars on bar charts with out using the "limit" function in your query as well you can also sort the bars in ascending or descending order with out using ORDER by clause in SQL.
Some times you might have to deal with this type of functionality in your dashboard.
So here is a working example. Follow the steps...
Environment:
Pentaho BI server 4.8 stable with C-Tools(CDA,CDE,CDF 13.06) installed.
PostgreSQL
Aim :
1) Creating a dashboard with two horizontal bar charts.
2) How to use single query result set for two charts
3) Order by the bar on the dashboard with out writing ORDER BY clause in SQL.
4) limit the number of bars on charts with out writing "LIMIT" clause in SQL
Suppose your result set is like below
Query Eg :
SELECT ColumnA, columnB,ColumnC from table_Name GROUP BY ColumnA
Result set Assumption:
ColumnA ColumnB ColumnC
pentaho 45 75
jasper 23 34
pdi 90 22
and assume there are 30 rows in the result set ...
You are using single query to plot the two charts on dashboard using ColumnA,ColumnB & ColumnA,ColumnC.
When you use ORDER BY clause in the query, you can only either order by columnB or ColumnC but you can not order by with ColumnB and ColumnC.
* In CDE, there is a functionality called "Indexs". Indexs for columns in pentaho CDE starts from 0,1,2 and etc.
* So ColumnA index is 0, ColumnB index is 1 and vice versa.
* Prepare your environment for the dashboard and have a look at the "Data sources" now.
* Click on "Output Options" and give index values as 0,1,2 by clicking "add" button multiple times.
How to get ColumnA, ColumnB on first chart ? (B'z you are using single query result set)
* Now click on the first chart component
* In the Advanced properties click on "Pre Execution"
* Write the below code to fetch ColumnA, ColumnB on first bar chart.
function f() {
this.chartDefinition.readers = [
{names:'category', indexes: 0},
{names: 'value', indexes: 1},
{indexs: 2}
];
}
NOTE:
ColumnA=category and is accessed with Index 0
ColumnB=value and is accessed with Index 1
index2 is written in the code b'z to ignore the value appending to the category showed on bars.
How to sort and limit the bars on first chart ?(B'z you are using single query result set)
* Click on the "Post Fetch" option from the Advanced properties.
* Write this code, this code will limit the number of bars on chart and sort the bars in descending order.
* In the code below "param_no_of_end_points" is the parameter created in the "Generic" section.
* And for the parameter create a select in the "Selects" section with "Text input" component.
function f1(cdaData) {
var categIndex = 0;
var valueIndex = 1;
var param_no_of_end_points = +Dashboards.getParameterValue("param_no_of_end_points");
if(isNaN(param_no_of_end_points))
{
param_no_of_end_points = 0;
}
var resultset = cdaData.resultset.slice();
var compareDesc = function(a, b)
{
return a === b ? 0 : a > b ? 1 : -1;
};
resultset.sort(
function(rowa, rowb)
{
return compareDesc(+rowa[valueIndex],+rowb[valueIndex]);
}
);
if(param_no_of_end_points > 0)
{
resultset.splice(0,resultset.length-param_no_of_end_points,0);
cdaData.resultset = resultset;
}
cdaData = {
metadata: cdaData.metadata,
resultset: resultset
};
return cdaData;
}
Sources for the CODE:
1) http://forums.pentaho.com/showthread.php?148352-Order-by-on-bar-chart-with-out-touching-the-query-in-CDE
2)http://www.w3schools.com/jsref/jsref_splice.asp
and etc.
Write the same code for the second chart but make sure to use different function names and Index value. In an application we can not write two functions with same name.
Save the the dashboard and see the preview.
Sadakar
BI developer
Saturday 24 August 2013
Inserting images for dashboards in pentaho CDE
Hi guys..
After a long waited time, have given a trail on uploading images to pentaho CDE dashboard.
There are two ways to upload an image
1) Using Image option for the columns.
2) Using HTML code.
This post teach you the second method where I need to work out how can we upload using Image option.
If you have already done with Image option leave the procedure here in a comment.
There could be another ways of doing the same but it helps some ones urgent need.
Tools version :
CDE 13.09, Pentaho 5.0.1 CE.
Steps:
1) Place your image in the below mentioned folder
your installation folder\biserver-ce 5.0\pentaho-solutions\system\pentaho-cdf-dd\images\logo.png
Or
under images create your own folder(lets say folder name as "ProjectImages"
2) Now, Add HTML tag to a column and in the HTML code give the following, code which refers the location of your image in ProjectImages folder.
CODE in HTML tag:
<a>
<img src="images/ProjectImages/helical logo.png"/>
</a>
See the preview of the dashboard... you can find the logo of your company.
Sample output:
Imp NOTE:
This post may not gives you the exact functionality of uploading image to dashboard..
Working on actual procedure i.e, Using Image option of column and how to give URL.
will update you once done with it.
After a long waited time, have given a trail on uploading images to pentaho CDE dashboard.
There are two ways to upload an image
1) Using Image option for the columns.
2) Using HTML code.
This post teach you the second method where I need to work out how can we upload using Image option.
If you have already done with Image option leave the procedure here in a comment.
There could be another ways of doing the same but it helps some ones urgent need.
Tools version :
CDE 13.09, Pentaho 5.0.1 CE.
Steps:
1) Place your image in the below mentioned folder
your installation folder\biserver-ce 5.0\pentaho-solutions\system\pentaho-cdf-dd\images\logo.png
Or
under images create your own folder(lets say folder name as "ProjectImages"
2) Now, Add HTML tag to a column and in the HTML code give the following, code which refers the location of your image in ProjectImages folder.
CODE in HTML tag:
<a>
<img src="images/ProjectImages/helical logo.png"/>
</a>
See the preview of the dashboard... you can find the logo of your company.
Sample output:
Imp NOTE:
This post may not gives you the exact functionality of uploading image to dashboard..
Working on actual procedure i.e, Using Image option of column and how to give URL.
will update you once done with it.
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
Sadakar
BI developer
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
Sadakar
BI developer
Cascading parameters example in Pentaho CDE
Hi Guys,
It is quite easy to learn about cascading parameters in pentaho CDE dash boarding.
Example below teach you how to give cascading parameters on CDE dashboard.
Environment :
C-Tools 13.09
Pentaho BA Server 4.8 stableb
DB : postgreSQL foodmart ( jasperservers database)
IDEA of cascading parameters:
:-)
It is quite easy to learn about cascading parameters in pentaho CDE dash boarding.
Example below teach you how to give cascading parameters on CDE dashboard.
Environment :
C-Tools 13.09
Pentaho BA Server 4.8 stableb
DB : postgreSQL foodmart ( jasperservers database)
IDEA of cascading parameters:
Example:
1) Lay out section
* Design your lay out for keeping the parameters on dasbharod.
* Sample reference design of layout.
2) Components section
* You need to define 3 selects for 3 parameters
* You need to define 3 parameters
* Observer the Sample worked out image below
*
* Define 2 parameters for country and state(paramCountry and paramState)
* Define 3 Selects for displaying the selection of parameters on dashboard.
( CountrySelectComponent, StateSelectComponent, CitySelectComponent)
Parameters
1. ParamCountry
i.e, Name= paramCountry and Propertyvalue=USA(default value)
2. paramState
Name=paramState and Propertyvalue=OR(default value)
NOTE : you need NOT to create parameter for city in this case.
Select Components
* Select components are used to display the data coming from sequel query on dashboard as drop down list at the respected place holders.
1) CountrySelectComponent
Name: CountrySelecte Component
Parameter: paramCouentry (you should give param name otherwise, it wont work)
Datasource: give the query name (will discussion in Datasource selection)
HtmlObject : countrySelect
2)StateSelectComponent
Name: StateSelectComponent
Parameter : paramState
Listners : paramCountry
Parameters : Arg(paramCountry) and val(paramCountryP)
Datasource : queryState
HtmlObject : stateSelect
3) CitySelectComponent
* You need add two listners and two parameters at Listerns and parameters properties
Name;CitySelectComponent
Parameter : No need to give any parameters name here as we are not using parameter for city sleelct
Listners : paramState,paramCountry
Parameters :
Arg(paramCountry) and val(paramCountry)
Arg(paramState) and val(paramState)
Datasource : queryCity
HtmlObject : citySelect
3) Data Sources section:
Write queries for 3 selects
1) queryCountry(above image)
Name: queryCountry
Driver: org.postgresql.Driver
User name : postgres
Password : postgres
URL : jdbc:postgresql://localhost:6062/foodmart (5432 is the default port number for postgreSQL)
Query: SELECT DISTINCT country from customer
Parameters : paramCountry(arg) and paramCountry(val)
2) queryState(image is not shown in post refere queryCountry image)
Name: queryState
Driver: org.postgresql.Driver
User name : postgres
Password : postgres
URL : jdbc:postgresql://localhost:6062/foodmart (5432 is the default port number for postgreSQL)
Query: SELECT DISTINCT state_province from customer where country=${paramCountry}
Parameters : paramCountry(arg) and paramCountry(val)
3) queryCity(image is not shown in post refere queryCountry image)
Name: queryCity
Driver: org.postgresql.Driver
User name : postgres
Password : postgres
URL : jdbc:postgresql://localhost:6062/foodmart (5432 is the default port number for postgreSQL)
Query: SELECT DISTINCT city from customer WHERE country=${paramCountry} AND state_province=${paramState}
Parameters : paramCountry(arg) and paramCountry(val)
paramState(arg) and paramState(val)
Save the dashboard and see the preview.
Final output:
1) Test 1
2) Test2
Thank you for reading this post :) :)
for queries and suggestions : sadakar.1988@gmail.com
SOURCE CODE OF THIS EXAMPLE:
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 @ sadakar.1988@gmail.com 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.
root@sadakar-server:/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 :
root@sadakar-server:/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 :
root@sadakar-server:/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
root@sadakar-server:/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 root@sadakar-server:/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"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<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
root@sadakar-server:/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 :
root@sadakar-server:/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)
* URL :
References
1. http://blog.endpoint.com/2013/11/install-pentaho-bi-server-48-community.html
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 @ sadakar.1988@gmail.com 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.
root@sadakar-server:/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 :
root@sadakar-server:/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 :
root@sadakar-server:/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
root@sadakar-server:/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 root@sadakar-server:/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"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<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
root@sadakar-server:/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 :
root@sadakar-server:/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)
* URL :
References
1. http://blog.endpoint.com/2013/11/install-pentaho-bi-server-48-community.html
Pentaho BI Server Installation in existing tomcat server with PostgreSQL in Windows7 64bit
Need to update with images and few more imp stuff... Need a review on this artical.
Pre-requisites:
·
Web App
Server : apache-tomcat-6.0.36
·
BI Server
Version : biserver-ce-4.8.0-stable
·
Database:
postgreSQL, the below version or later
Operating
System : Windows 7 -64 bi OS (OR) LINUX(Amazon cloud instance)
·
Other
Tools used : PuTTy, WinScp
Download locations:
Apache-tomcat
Pentaho BIserver-ce-4.8
PostgreSQL
PuTTY
WinScp
http://winscp.net/eng/download.php
Steps for BI server installation in existing tomcat
1.
Apache tomcat installation (zip
file installation)
a.
Place the apache-tomcat-6.0.36.tar.gz
in your favourite place and unzip it.
b.
For
example: C:\Apache2\apache-tomcat-6.0.36.tar.gz
c.
Run the server (i.e., Test whether the server is
running properly or not)
d.
Once you confirm by seeing log of server, you
need to work up on installation.
2.
Copy folders from
biserver-ce-4.8.0-stable to the tomcat web apps
a.
After downloading
biserver-ce-4.8.0-stable from source forge site unzip in your favourite
location.
b.
Copy “pentaho”and
“pentaho-styles” folders to webapps folder of tomcat server.
c.
See the below image
d.
Copy the “pentaho-solutions”
folder in Similar location of tomcat installed folder.
NOTE: you can place it anywhere (i.e., you can
place “pentaho-solutions” folder in any location of your machine).
e.
Find the image below for
point “d”.
3.
Executing Scripting files
to create hibernate and quartz databases
Go to this location and down load all the 5 scripting files
Scripting files are:
1_create_repository_postgresql.sql
2_create_quartz_postgresql.sql
3_create_sample_datasource_postgresql.sql
4_load_sample_users_postgresql.sql
5_sample_data_postgresql.sql
Run all the files in postgreSQL which will create hibernate and quartz
databases in postgreSQL.
NOTE:
·
Execute
the scripts from command prompt.(Graphical execution may not work).
·
You
may need to give the permissions to execute these scripting files.
·
You
can also find all the five scripting files in the following location of pentaho
unzipped.
·
D:\Installation
Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\data
·
But
these files may not have all the data to be used by the pentaho server.
·
So
make sure that you have all the sample data for postgreSQL which will be used
by Pentaho Server(for example: user name & password stores in user table
& this we have to get from the database – this columns(data) may not
present in the downloaded unzip)—so it’s better you go the link specified above
and run the scripting files.
4.
Configuring the context.xml
file in tomcat server
a.
Open the context.xml
from “Config” folder
C:\Apache2\apache-tomcat-6.0.36\conf)
b.
Code before modified
<Context>
<!-- Default set of monitored
resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!-- Uncomment this to disable session
persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<!-- Uncomment this to enable Comet
connection tacking (provides events
on session expiration as well as
webapp lifecycle) -->
<!--
<Valve
className="org.apache.catalina.valves.CometConnectionManagerValve"
/>
-->
</Context>
|
c.
Code after modified( Copy and paste this code in
context.xml file)
<Context>
<!-- Default set of monitored resources -->
<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:6062/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:6062/quartz"
validationQuery="select
1"/>
</Context>
|
NOTE: do not
touch any other configuration files
5.
Changes in META-INF &
WEB-INF folders which resides in Pentaho folder of web-apps of tomcat
installation folder
Find the image below shown
I)
Changes in META-INF folder
a.
Open
the META-INF folder, you can find only one file. i.e., MANIFEST.MF
b.
You
need not to work with this file.
c.
Now,
create a new file.
i.
File
should be : context.xml
ii.
Copy
the following content in the file and save it.
<?xml version="1.0"
encoding="UTF-8"?>
<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:6062/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:6062/quartz"
validationQuery="select
1"/>
</Context>
|
NOTE: You can copy paste the “context.xml”
which we modified in Step 3.
The same thing you need to do
here.
II)
Changes in WEB-INF folder
a.
Open
web.xml file from this folder(C:\Apache2\apache-tomcat-6.0.36\webapps\pentaho\WEB-INF)
b.
Change
the following code( lines from 8 to 11)
Code after modified
<context-param>
<param-name>solution-path</param-name>
<param-value>locaton/path of pentaho-solutions</param-value>
</context-param>
NOTE:
*
You
need not to give path for pentaho-solutions if you copy the pentaho-solutions
folder similar to tomcat installation
*
Find
image-3
·
In
case if you copy-the pentaho-solutions folder somewhere else you must have to
give the path between <param_value> and </param-value> tags.
*
For
example :
<context-param>
<param-name>solution-path</param-name>
<param-value>D:\Installation
Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions</param-value>
</context-param>
·
If
the server doesn’t find path change the \ to / or give \\ or //
|
6.
Changes in
pentaho-solutions folder
Go to the location D:\Installation
Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system
and find the following files and configure settings .
1.
applicationContext-spring-security-jdbc.xml
2.
applicationContext-spring-security-hibernate.properties
1.
applicationContext-spring-security-jdbc.xml
<bean
id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property
name="driverClassName" value="org.postgresql.Driver"
/>
<property
name="url"
value="jdbc:postgresql://localhost:6062/hibernate"
/>
<property
name="username" value="hibuser" />
<property
name="password" value="password" />
</bean>
|
2.
applicationContext-spring-security-hibernate.properties
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:6062/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
|
Now, go to
this location C:\Apache2\pentaho-solutions\system\hibernate
and find the files and configure the
settings
1)
hibernate-settings.xml
2)
postgresql.hibernate.cfg.xml
1.
hibernate-settings.xml
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
|
2.
postgresql.hibernate.cfg.xml
<hibernate-configuration>
<session-factory>
<property
name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
<property
name="hibernate.generate_statistics">true</property>
<property
name="hibernate.cache.use_query_cache">true</property>
<!-- Postgres 8
Configuration -->
<property
name="connection.driver_class">org.postgresql.Driver</property>
<property
name="connection.url">jdbc:postgresql://localhost:6062/hibernate</property>
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property
name="connection.username">hibuser</property>
<property
name="connection.password">password</property>
<property
name="connection.pool_size">10</property>
<property name="show_sql">false</property>
<property
name="hibernate.jdbc.use_streams_for_binary">true</property>
<!-- replaces DefinitionVersionManager -->
<property
name="hibernate.hbm2ddl.auto">update</property>
<!-- load resource from classpath -->
<mapping resource="hibernate/postgresql.hbm.xml" />
<!-- This is only used by
Pentaho Administration Console. Spring Security will not use these mapping
files -->
<mapping resource="PentahoUser.hbm.xml" />
<mapping resource="PentahoRole.hbm.xml" />
<mapping resource="PentahoUserRoleMapping.hbm.xml" />
</session-factory>
</hibernate-configuration>
|
7.
Refrences
Subscribe to:
Posts (Atom)