What JasperETL can do

Here's what I've been doing for the past two days. I was requested to learn an ETL (Extract-Transform-Load) tool. Luckily Jaspersoft's ETL is as easy as ABC. So far.. so good.

For my sample scenarios, I used SqlServer database, MySQL database, and excel file as my data sources. But note that JasperETL has an extensive list of supported data sources. On the database side alone there is AS400, Access, DB Generics, DB JDBC, FireBird, Informix, Ingres, Interbase LDAP, MaxDB, Oracle, PostgreSQL, SQLite,Sybase,Teradata to name a few.

Below is a detailed decription of some jobs I did:

1. Job 1
- This job transfers all employee data from the SqlServer database to the employee table on the MySQL database. If the transaction succeeds, trigger 1 will run and send email to some recipients with the message "All data successfully transferred". If the transaction fails, trigger 2 will run and send email to some recipients with the message "An error occurred on the job process".

Components Used :
- tSendMail
- tMSSqlInput
- tMysqlOutput
- tMap
- tLogRow (for testing purposes)

This is how the entire workspace looks like. The diagram at the center show you the job design of Job1

2. Job 2
- This job transfers all products of a specific brand name. The data will be captured from the product table on SQLServer and loaded on both the product table on MySQL and the excel file.

Components Used :
- tFilterRow
- tMSSqlInput
- tMysqlOutput
- tFileOutputExcel
- tMap
- tLogRow (for testing purposes)

This is the job design for Job 2

3. Job 3
- This job fetches data from an excel file and loads it on the store table from MySQL database. Note that a new field was added on the store table (store_address) to hold the concatenated value of store_street, store_city, and store_state from the excel file. There is also one field on the excel file that has a different field name on the store table of the MySQL database.

Components Used :
- ExcelMetadata
- tMysqlOutput
- tMap
- tLogRow (for testing purposes)

This is the job design for Job 3

4. Job 4
- This job fetches data from the store database and loads it on an excel file (excel file used for Job 3)

Components Used :
- tMSSqlInput
- tFileOutputExcel
- tMap
- tLogRow (for testing purposes)

This is the job design for Job 4

5. Job 5
- This job joins tables employee and salary from the SQLServer database, get's the joined data and loads it on another table employee_salary on the MySQL database. It also throws all rejected inner join data to an xml file which, when exists, will email a notification that there are rejected data on the join

Components Used :
- tMSSqlInput
- tMysqlOutput
- tFileOutputXML
- tFileExist
- tSendMail
- tMap
- tLogRow (for testing purposes)

This is the tmap design for Job 5

This is how the job design looks like while it's running and both Statistic and Traces are ticked on the Run tab.

How to show the Change Password link on the Login page

Since we've identified the Web-inf folder from the previous tutorial, this one should be fairly easy to do.

Step 1 :
Look for your Web-Inf folder from your Jaspersoft folder. I'm using Jaspersoft's later version (3.7) so mine is located at ..\jasperserver-pro-3.7\apache-tomcat\webapps\jasperserver-pro\WEB-INF .


Step 2:
Locate the file jasperserver-serverlet.xml and set the property allowUserPasswordChange to true. Also, set a value to the passwordExpirationInDays property .


Step 3:
Restart your Jasperserver. Your login page should show the Change Password link after the Show locale & timezone link. Once you click on the Change Password link, the login page will look like



Cheers! ;-)

How to Configure Report Scheduler to work on your Jasper Server

Another BI technology I'm exploring right now is Jaspersoft. It's a powerful Open Source BI tool which is a bit similar to Oracle Hyperion. And since it uses Open Source technology, this means it's way cheaper and easier to sell. lol

Going back to the topic, I'd like to share how easy it is to configure your Jasper Server to use it's report scheduler facility.

Step 1 :
Look for your Web-Inf folder from your Jaspersoft folder. I'm using Jaspersoft's later version (3.7) so mine is located at ..\jasperserver-pro-3.7\apache-tomcat\webapps\jasperserver-pro\WEB-INF .


Step 2:
At the js.quartz.properties file, add your smtp and email credentials. If you are using gmail, be sure to use the 587 port as 465 port does not work well with gmail.

For example :
report.scheduler.mail.sender.host= your mail server (smtp.gmail.com for gmail)
report.scheduler.mail.sender.username= your email's username
report.scheduler.mail.sender.password= your email's password
report.scheduler.mail.sender.from= your email address
report.scheduler.mail.sender.protocol=smtp
report.scheduler.mail.sender.port=587

report.scheduler.web.deployment.uri=http://localhost:8080/jasperserver-pro (This url should be the same url you see when you open your Jasperserver login page. You only need to remove login.php)


Step 3 (Optional) :
If you'de like to increase your thread pool, you may do so by modifying the file js.quartz.base.properties .

Locate the line org.quartz.threadPool.threadCount and increase the thread count.

For example:
org.quartz.threadPool.threadCount = 3


Step 4 :
At the applicationContext-report-scheduling.xml file, locate the bean id reportSchedulerMailSender . Modify and add the following prop key under the property javaMailProperties.

For Example :

prop key="mail.smtp.auth">true
prop key="mail.smtp.starttls.enable">true


Step 5:
Restart your Jasperserver and schedule your report =)



Cheers!