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!

Preparing your Middle and Database Tier for Hyperion EPM System Fusion Edition Installation

If you're planning to install and create a testing development for Planning, consider using a virtual machine for your Middle and Database Tier. It maybe easy for some to install the Hyperion Enterprise Performance Management Fusion Edition, but for other, installation is taking forever. So, to help lessen your burden and to help you prevent from reformatting your personal computers over and over again, i suggest you use either VMWare or Microsoft's Virtual PC to host your Middle and Database Tier.

Below is a list of what I did to successfully install Hyperion on my Web Application and Database Server:
1. I created my virtual machine to host my Middle and Database Tier.
2. I then installed it with Windows 2003 with SP2 and IIS.
3. And then I installed it with MS SQL 2005. This will serve as my RDBMS.
4. I made sure that cookies and javascripting is enable on my IIS.
5. I made sure that .Net Framework 2.0 is installed on my PC.
6. I checked and made sure that .Net Framework 2.0 is an allowed web service extension on IIS. To do/check this option, go to your IIS Manager, click on the Web Service Extensions folder, select .Net Framework 2.0 Web Service Extension on the right side, and click on the Allow button if it's not yet clicked.
7. Login to MS SQL 2005 and create a new database and a new user with the system admin role.
8. Install the latest version of Hyperion Enterprise Performance Management Fusion Edition. The version I installed was version 11.1.1.0.307
9. And configure each of the components by going to Foundation Services and clicking the EPM System Configuration icon. The wizard will easily help you configure all the Hyperion Fusion Edition tools and applications.

I will discuss about setting up the Hyperion Planning Client Tier on my next post

My first Oracle Hyperion Training

I just attended my first Oracle Hyperion Sales and Pre-Sales Training at the Oracle Philippines office here in Makati City last Aug 11 and 12. Thanks to Sean, Julia, Chris, and Anne for sharing their thoughts and selling skills!

Just to share, here's a list of what i saw or have found out about Hyperion:
1. I've learned that the BI Foundation is composed of the EssBase and the OBIEE tools
2. I've learned that thru the EssBase, Hyperion is able to produce information that answer's the How? and the What if's?.
3. I've learned that the BI Applications sits on top of the BI Foundation.
4. I've learned that all the BI Applications are pre-built and are ready to install hot pluggins. This means that these are not templates that need to be configured. But if you have an existing datastorage built from oracle, sap, peoplesoft, or siebel, then it can easily use those datastorage.
5. I've learned that the EPM applications such as the Planning, Financial Manager, Performance Scorecard, and Data Quality Management all sits on top of the BI Foundation.
6. I've learned that the Workforce Planning and CapEx Planning have a different license.
7. I've learned that there are still a lot more that i want and need to learn about datawarehousing.
8. And best of all, with these kinds of new BI applications, I've learned that anyone withought IT education can easily create their own applications!

What's the difference between OLTP and OLAP?

This two words which both starts with the letters OL, both ends with the letter P, and both consists of four letters are very much different IT/BI(Business Intelligence) jargons.

How are they different?
By Definition :
OLTP which stands for Online Transaction Processing or simply transaction processing is a class of systems that facilitates and manages transaction-related applications and provide real time accesses to its data which is being updated by other transactions. It is also used to refer to a processing wherein the system can quickly respond to user requests/transactions.

OLAP which stands for Online Analytical Processing refers to an approach to quickly provide answers to analytical queries that are multi-dimensional in nature. Through the use of what they call Aggregations, some have claimed that for complex queries, OLAP cubes can produce an answer in around 0.1% of the time for the same query on OLTP relational data.

An Inside look on some of the New Features of Hyperion Planning Fusion Version 11.1.1.0.0 : The Enterprise Performance Management Architect

The EPMA(Enterprise Performace Management Architect):
- You can now organize your applications into folders
- With this, you can now organize your application library the way you organize your shell library. Also, it helps you easily find the application you're looking for.
- There have been improvements in the simplification of import process. Now you can directly import in the application. In the past, this has to be done on the Shell library first.
- Grid Editor. You can now make bulk updates to multiple members at the same time. This now also allows you to see, create, and delete multiple members of the same dimensions without having to go back in the dimension library.
- There is now an Application Creation Wizard which is a tool that walks you thru the creation of an application or planning application in the EPMA.
- New EPMA Batch Clients which allows running EPMA tasks in the batch log thru scripting or using a cover line interface.
- Planning Classic Parity Items now supports DTS(Dynamic Time Series)

Terminologies in EPMA
- Master View = Libraries of shared dimensions
- Application View = Application (Now made up of shared and local dimensions and not considered as a view on the main dimension catalog.)
- Shared Dimensions = Dimensions that can be used by multiple applications and that are stored in the Shared Library.
- Local Dimension = Detached, independent dimensions that exists only in one application.

An Inside look on some of the New Features of Hyperion Planning Fusion Version 11.1.1.0.0 : Calculation Manager

3.Calculation Manager
- This will be used for Hyperion Planning and Hyperion Financial Manager.
-Will ease the maintenance of business rules since it allows you to create and share complete rules which you can also organize using folders and rule sets. This has predesigned templates which you can use to build your own rules. This is categorized as one of the next generation of Hyperion Business Rule.

An Inside look on some of the New Features of Hyperion Planning Fussion Version 11.1.1.0.0: Smart View

2.Smart View for Planning
- On Adhoc Queries and Analysis
-You can now write data back to the database
-Provides a smooth integration between the Planning web and the Smart View - no need to relogin to excel.
-You can view attached docs on the cells

- Connection Manager is improved
- You get a tree view of each product and from each of these products you can browse to different servers and different applications
- You can also drill from the web form level

- Will allow you to perform modelization of cubed slices thru the use of Smart Slice.
- Report is now created using one or more Smart Slice/s that can come from different sources.
- Your Power Point presentations can now be dynamically linked to the data sources.
- There is a Function Grid that links a cell in Excel to a data from a data source that now makes it a more flexible formatting report.

Benifits of Smart View on Planning 11.1.1:
a. There is a tight integration in Planning web since you can now simply go back and forth from the Excel file to your web UI.
b. You can now dynamically link your office presentation such as Power Point or Word to your EPM sources withought doing other manual steps such as copying and pasting tables or graphs.
c. You can now create interactive reports from all your EPM sources

An Inside look on some of the New Features of Hyperion Planning: Core Planning

1. Core Planning
- With the enhancement, you will now be able to work with data forms and perform the following actions:
- Attachment of excel documents on data forms
- Select dates automatically using the calendar
- Show member formulas from data form
- Hide and show rows with no data on data form
- Launch SmartView from data form through a simple click w/o the need to reconnect.
- Drill through the Financial Data Quality Management (FDM)

On the user interface:
- You may now choose to display the aliases when using copy versions
- You will now be able to cleanup annotations using the clear data option

Others
- You can create folder securities. will this feature, all the forms under this folder will inherit the assigned security on the folder. And if a form under the folder still has a unique security other that the security created on the folder, you may still opt to override the folder security for this particular form.
- Job Status