Use of Lateral Subqueries in PostgreSQL

Use of Lateral Subqueries in PostgreSQL

Lateral subqueries are subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)

In simple words, Lateral keyword allows to pass columns values from the previous join to your subquery.

Note: This new feature available in Postgres version 9.3 and above.

For Example :

Lets take an example suppose you are joining two table (table1 and table2) which returns 10 rows after joining and you want to add 1 column which holds YTD value. for this new column you have written a subquery. lets say 100 rows returned by subquery for year 2015.

Table Name

employee = employee_id, company_id, emp_name
company = company_id, companyname
emp_sal = employee_id, month,year, salary

Subquery without Lateral Keyword.

select employee.emp_name, c.companyname ,subquery.ytdvalue, subquery.year
from employee e join company c on e.company_id = c.company_id
join
( select sum(salary) as ytdvalue ,year, e.employee_id
from employee e1 join emp_sal es on e1.employee_id = es.employee_id
group by e1.employee_id, year
) subquery on subquery.employee_id = e.employee_id
where
e.employee_id in (1,2,3,4,5,6,7,8,9,10)

Explaination: Above subquery runs 100 times for each individual id and then join with e.employee_id and returns 10 rows as final output.

Subquery with Lateral Keyword

select employee.emp_name, c.companyname ,lateralsubquery.ytdvalue, lateralsubquery.year
from employee e join company c on e.company_id = c.company_id
join lateral
( select sum(salary) as ytdvalue,year, e.employee_id
from employee e1 join emp_sal es on e1.employee_id = es.employee_id
where
e1.employee_id = e.employee_id

group by e1.employee_id, year
) lateralsubquery on lateralsubquery.employee_id = e.employee_id
where
e.employee_id in (1,2,3,4,5,6,7,8,9,10)
Explaination: Above lateral subquery runs 10 times for each individual id because we are passing e.employee_id into subquery.

For more information visit
https://www.postgresql.org/docs/9.3/static/queries-table-expressions.html

Thanks and Regards
Fahad Anjum

The Print When Expression

The Print When Expression

The print when expression in Jasper Studio or Jasper iReport is very useful in so many requirements and it is defined as the name itself implies
i.e Print /Show a String, Column and so on Based on a condition passed.
Now here are some requirements on how/when we can implement The Print when expression.

Requiremnt: Show a particular column when a parameter is selected
Solution:
Here we made a parameter Called ShowLocation with values either ‘Y’ or ‘N’/blank space
now when ‘Y’ is selected then a Location column should show and when ‘N/blank space then it doesn’t show.

2 ways i did approach this:

1. I made a 2 Table Components one with the Location Column added and the other without the Location Column added.
one on top the other.
so when my parameter calls a ‘Y’ value it picks the Table with the Location Column and vice versa.

This approach is good but can be time and performance consuming

the 2nd approach was:

2. Only one Table Component was used, Opened the Table Component and selected the Column i want to restrict/show based on the parameter passed, in my scenario or requirement it is the Location column as
shown in the image below:

TableLayout

now i would select the cell button

Cellin the properties Tab and then Select the
Print When Expression.

Now here in the print when expression box i would write :
PrintExperssion
$P{showlocation}.equals(“Y”) as shown above

 

and you are good to go for a preview.
Now Let’s have to put a N or blank space in the parameter showlocation we get the following output
before
and now let’s pass ‘Y’ in the showlocation parameter

before-Y
So Now with this condition it will show the column when the parameter is checked in with a ‘Y’ Value.
and voila the output:

after

Thanks
Sohail Izebhijie.

Beginners Guide to Understanding Cross Join

Beginners Guide to Understanding Cross Join

In my previous Beginners Guide we talked about Types of joins i.e.

Inner Join, Left and Right Join but today we will be talking about cross join which happens to be more interesting to me than any other joins and happens to be helpful in real time for me.

What is a CROSS JOIN?

A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables.

Why CROSS JOIN?

Let’s say we have two table employee table “emp1” and department table “dept” now lets see how many records do we have in the emp1 table:

emp1
and also how many records do we have in the dept table:

dept

and for the emp1 table you want to “associate” the location column from the
dept table to all the records in the emp1 table so we use CROSS JOIN

but really why ?
In realtime Sometimes you might want to associate dates with a particular table and its records this happens to be helpful

Now we can do that with the Keyword CROSS JOIN as shown below.
cross

And there you go hope you will have a better understanding now.

-Sohail I.E

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 community 4.8 Installation in existing tomcat with PostgreSQL in Linux/ubantu OS.

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 @ nikhilesh@heicaltech.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”
>
<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)

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

Sadakar(BI developer)