JNDI Connection - Pentaho Report Designer

As a major design principle, we have always encouraged developers to move away from using hardcoded hostnames and use JNDI instead. Major reason for that being that the code remains loosely coupled with the configuration files. You would not need to rebuild your code if there are changes and it acts like a macro which can be used anywhere throughout the code driven by configuration.

Follow the below steps for configuring Pentaho Report Designer to use the JNDI connection. For this example, I will take the MySQL Connection as an example.

Step - 1: JDBC Driver

The JDBC Driver should reside in the location - lib/jdbc at the path where your report designer is installed or extracted. Thus, to provide a mysql connectivity to your report designer, download the mysql-connector and place it in that path.

Step - 2: Configuring JDBC Connection and Setting JNDI Properties

When installing Pentaho, it always creates a hidden directory inside the User Home where it keeps it's configuration files.

Windows - c:/Users//.pentaho
Linux/Mac - $HOME/.pentaho

Inside this directory, you should be able to find the file ".pentaho/simple-jndi/default.properties". Open this file and edit it as follows. Your JNDI is not SampleData

Step - 3: Report Designer Configuration

Watch the below video for Report Designed Configuration

Pentaho BI 101 - Pentaho CTools Installation in 3 different ways

One of the most important advantages of Open Source BI tools is that you can have plenty of people working and contributing different kinds of plugins to enable unimaginable scenarios. CTools was started by a company called WebDetails with one such idea to make a host of small and useful plugins and make pentaho more acceptable to developers. WebDetails was recently bought by Pentaho and in versions to come, we might see the entire toolset as a part of distribution itself.

As part of this blog, we would see 3 different ways to install CTools plugins. You may ask why 3 different ways? Well, the simple answer to that is its open source and it follows TIMTOWDI(There is more than one way to do it). You would also understand it why as you go through this post and video tutorial on YouTube.

1. ctools-installer.sh - This is a very strong choice for *nix developers. However, for Windows and Mac, you might need to install necessary tools to be able to run this shell script. Windows might need CygWin to run and Mac users need "wget" utility and can refer here to install it. On Mac however, the pre-requisite is to download and install XCode library from AppStore which is a 2GB install in itself. 
2. Individual plugins using JAR files - This is a very simple approach and there are no pre-reqs required except JAVA which you would already have since you have installed Pentaho. However, you might need to install each plugin manually and thus its feasible if you need only one or two.
3. Pentaho MarketPlace - MarketPlace is an always welcomed approach in any toolset. This also is by far the simplest approach to installing/maintaining and administering the plugin versions across your installations. PS:- This is not working at the moment(without proxy as well). This used to work 2 months back nicely. Refer Bug Report

Thus based on your choice of installation, please go ahead and choose one of the methods above.

CTools-installer Link - https://github.com/pmalves/ctools-installer

CTools Individual Installers - http://www.webdetails.pt/ctools.html

Pentaho MarketPlace - http://ci.pentaho.com/ and choose marketplace 4.8 or 5.0 depending on your version.

Pentaho BI 101 - Getting Started Series

As part of the first series on the DWBI Pub channel on YouTube, I have started to cover the Pentaho BI getting started series. The motive is to help people scale up to Open Source technologies, learn and contribute to real world projects.

As part of the getting started series, I would cover all aspects of Pentaho Business Intelligence and then take each component at a time and help people learn and deliver on real world projects. I hope to share as well as increase my knowledge while working on this series.

You can subscribe to my channel as well as add the playlist to give you most updated information as I upload more videos.

Topics in the Series (Update: Jan 27, 2014)

1. Introduction Part 1
2. Introduction Part 2
3. CTools Installation

Need for Learn and Share in DWBI world

As open source technologies are maturing, I always feel that the resources available to learn are very dispersed which is why beginners who are not able to find the right mentors often find it hard to figure out where to start. The reason for this is simple. Most of the open source punters are often too occupied to learn and are not able to find time to share their learnings with the world.

While working as a Practice Lead and Architect at YoungSoft Inc., I always feel that there is interest in people to learn new technologies but often people like us are not free to train them or help them. Thus, I have created a channel on YouTube as DWBI Pub and have started sharing my knowledge with the world.

As part of my attempt to get more people aware about BI and related technologies, the first part of the video series involve getting started with Pentaho. Later I also plan to cover my encounters with Informatica, Big Data technologies like Hadoop, Hive etc., Visualization tools like Yellowfin and many others.

Another purpose of starting this channel is also to exchange knowledge with the best in the industry and have their valuable feedback and comments.

Please feel free to subscribe to my channel and leave your feedback.

DWBI Pub on YouTube - Beer Conversations about Data

Install Oracle Sun Java JDK on Linux

In this post, I would try and collate information about installing Sun Java JDK on various Linux platforms. Since the time licensing issues have come up, the distros have stopped shipping sun-java and thus we need to add additional repos to achieve the same. I have found a very diverse information with various methodologies how people have installed but none of them give a very holistic picture. Hope this post can become a one stop information for many people struggling out there to get the right information all at one place and thus would keep updating this post to add new and relevant information.

Installing Sun Java JDK on Ubuntu 10 and 11

## Install the package required for adding repositories 
deploy$ sudo apt-get install python-software-properties
deploy$ sudo add-apt-repository ppa:ferramroberto/java
deploy$ sudo apt-get update
deploy$ sudo apt-get install sun-java6-jdk
deploy$ sudo update-java-alternatives -s java-6-sun

## Adding to environment Variable
deploy$ sudo vi /etc/environment
## Append the line - JAVA_HOME="/usr/lib/jvm/java-6-sun"

Remote Desktop using VNC and FVWM Crystal

This is one of my favorite things. Most of the times, I do not like having a development environment on my own machine because of various constraints like RAM, OS etc. It also helps in environments when DB connections cannot be made over VPN and only internal servers can access them. Thus, for working remotely, this is the best practice. 

I have always followed a policy of creating an environment for myself on the development server and working on that. For this, sometimes I require to have a windows manager too. FVWM crystal serves as one of the best windows manager I have ever worked with. It is light weight, superfast over VPN and renders pretty nicely. 

Official documentation page from Ubuntu

Quick steps to get you setup for Ubuntu

## Installing VNC Server on Ubuntu and FVWM-Crystal

deploy$ sudo apt-get install vnc4server fvwm-crystal

## Starting VNC Server - You can give the geometry settings you like.
## Note that this is a normal alphabetical 'x' and not a multiply sign.
deploy$ vncserver -geometry 1200x800 

## For the first time, it would ask you for a VNC Password
## Please enter your desired password for logging into your remote machine.

## The above command would start the VNC server at 
## terminal :1 or :2 depending upon what is available. 
## To kill a particular port run
deploy$ vncserver -kill :1

Accessing the VNC Remote desktop

There are a variety of clients available but mostly on Mac and Windows I have used RealVNC and on Linux I have used Remmina. There are varied functionalities with these clients and you can choose according to what functionalities you would require.

To open the remote desktop, open the client and enter the URL in the format :. Here port is the terminal port on which the windows manager has got started when the above command of vncserver was given. For example 10.0.0.23:1. One you do this, you would be able to login to remote terminal using fvwm-crystal or any other default windows manager. If you have multiple windows manager, the suggestion is to edit the ".vnc/xstartup" which is there in your Home directory to configure it.

Important FAQ - If you are not able to access the remote machine, then your server might be blocking the VNC port. In such case, you would require to tunnel it to do the same. Follow the steps of Port forwarding in Ubuntu Documentation to achieve the same.

## Find out the remote port where VNC is running - Mostly 5900 or 5901
deploy$ netstat -tpln | grep -iR xvnc
## On your local system, create a tunnel
deploy$ ssh -L 5900:localhost:5900 joe@remotehost

## Connect to remote machine from VNC client giving IP 
## of localhost as "localhost:5900"

Mondrian Logging and Tracing

I have been trying to write up this post for quite sometime now. But lately things have been so busy that I could not find time. So here it is for a lot of folks who have faced trouble setting up Logging and Tracing with Mondrian. Below are the steps to configure Logging and Tracing in Mondrian.

Prerequisite
The below are not the bare minimum but is a tested configuration on my server.

1. Apache Tomcat >= 6
2. Mondiran >= 3.2

Configuring Environmental Variables
While setting up Mondrian we understand that we have to setup CATALINA_OPTS as an environmental variable. For successfully setting up Logging and Tracing, we need to have 2 more declaratives added to the end as below:-



Adding the above in .bashrc in CATALINA_OPTS env variable allows you to do the following:-

• Adding log4j.xml file allows tomcat to pick up the logging configuration from your webapps/mondrian/WEB-INF/classes/log4j.xml. (Sample file is present in the section below)
• Adding log.dir allows you to specify where you want your log files to be generated.

Mondrian, Apache Tomcat and MySQL – Installation and Configuration on Linux

In this post, I am going to walk you through a manual installation and configuration of Mondrian with Apache Tomcat 6. I have always believed to have a clean install from source and thus I am going to follow the same here as well. The following procedure is what I have used in our production and is thoroughly tested in Ubuntu, CentOS and RHEL. Please post your comments/suggestions if you find any flaws in this.

Step – 1: Installing Sun-JDK
There have been many a times that I have faced unknown and hard to debug problems with the default installations of Sun-JDK which comes with Ubuntu/RHEL and other Operating Systems. Specially in production systems, I feel much more comfortable compiling things myself and thus if you are planning to make deployments in production system, it is better that you know what you are installing and where.

PasswordLess Login with Open-SSH Linux

OpenSSH allows you to login to remote systems and run commands. This is not a new concept for most of linux developers but for who require to perform remote commands without being prompted for a password, this quick tutorial gives just a way. Password less login to remote systems is useful in a lot of ways like copying backups across systems, monitoring multiple systems, trigger various commands, sync repositories etc.

So following is what you would need to do to achieve this. There are plenty of tutorials already for this over the internet since it is not a new concept. I am just documenting it for my personal use and for those who stumble upon my blog.

Step-1 : If you dont have a keypair already, then generate one.

## Leave the options blank and press Enter. 
## It automatically selects the defaults which are good enough

deploy$ ssh-keygen -t rsa

## Output
Generating public/private rsa key pair.
Enter file in which to save the key (/home/deploy/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/deploy/.ssh/id_rsa.
Your public key has been saved in /home/deploy/.ssh/id_rsa.pub.

Step-2 : If you wish to login to a machine called remotehost from localhost then we need to copy this key file to the remotehost.

## The following command will prompt you for the password once
## and copy the key file for you
ssh-copy-id -i ~/.ssh/id_rsa.pub user@remotehost

Great!! You are ready to go and login to the remote host from now onwards without entering your password again. You would need to repeat this password if you are logging from any other machine to remote host. This method is only local to login in from your system. Try doing the following now:-

ssh user@remotehost

Advance users can follow the Debian Guide for further information.

Findings on using FlexMonster with Mondrian

Occasionally, you tend to stumble upon a tool which impresses you in the first glance itself. But mostly, these first impressions do not go that far. Recently, for a reporting module, I was looking at a lot of RIA libraries and came across FlexMonster.

FlexMonster is essentially a data visualization solution for any reporting and BI. It's main advantage is having out of the box support for Mondrian(ROLAP) and a Pivot component. Though Mondrian is essentially open source and comes without any cost, FlexMonster solutions costs you about $999 licence. This according to me is a very high price one would pay as compared to the open source alternatives available but some more work into this tool can justify the cost.

One can easily get jazzed by the online demo for FlexMonster but I seriously feel that they have couple of design flaws they need to think through. No doubt, it has a lot of potential to become one of the best OLAP viewers till date, but I am unsure about their development cycle and planned releases if any.

Pros:-

Have tried to lay down some of the things that were strikingly amazing in FlexMonster and made me to like it a lot.

1. Out of the box support for XMLA - This is a serious advantage which many of the other OLAP viewers do not have like JPivot.
2. Exposed as JS library - This makes it really really simple to be embedded into any web application (Python, PhP etc.).
3. UI design is very intuitive - Keeping into mind that most of the OLAP users love simplicity of the UI, it does a fairly good job.
4. Since based on flex, there are no cross browser issues.
5. General Pivot components like sorting/filtering etc. are all out of box.

Cons:-

Here comes the ugly picture that makes FlexMonster unusable for a lot of potential customers.

1. Flexmonster is essentially a Flex version of JPivot but does not do a complete job at it. It wins the race in Rich UI, but lacks a lot of features that JPivot has to offer out of the box for "Free".

2. Lacks MDX support - A lot of business users would want many features that can be taken out only via MDX. Though they claim on the main page of their website - "XMLA and MDX support for multidimensional data visualization".

3. Customization using parameters looks incomplete. For example:- setting the parameter to expand-all as on, it would expand by default on both rows and columns. I would certainly want either my rows or columns to be fully expanded and not the other one.

4. Drill down in charts is only available for CSV and not for OLAP.

5. Stores reports configurations in XML files. This makes embedding the flexmonster as a solution highly unusable in Large Scale data intensive business environments because clients might log in and try to save out their reports. For many businesses, the clients can vary from 100 to millions of customers. This essentially means million or more config files in worst case. Sounds like a bad design to me.

6. Since FlexMonster is reading the config from XML and creating MDX on the fly, their constructs of Rows and Columns should be much more extensive for Developers to user MDX constructs like Hierarchize and Lag etc.

Summing up, FlexMonster gave me a good feel at the first instance, but going ahead its limitations started overgrowing its benefits specially for a production data intensive system. I would love to use this tool again when it matures a bit.

Changing Columns to Proper Case in MySQL - Using InitCap() function

MySQL String functions have lots of capabilities to manipulate the columns the way you wish to display. But apparently MySQL does not have any inbuilt function to convert the string to Proper Case. Some people understand this term as Sentence Case or Capitalized. However, on MySQL Dev site I found this amazing function which is worth quoting since it does exactly that.

DELIMITER $$
DROP FUNCTION IF EXISTS `initcap` $$
CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
    SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
    SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
    SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END $$
DELIMITER ;

Usage of this function would be as follows:-

- SELECT initcap('new delhi') returns "New Delhi"

Saving the article for my reference and for a lot of other people who might be looking for something similar.

What this Blog is About!!!

Friends, welcome to my blog!! It has been really long time since I did any blogging. My last blog was way back in 2007 on one of my websites which I had shut down due to lack of time. But since very long I have felt a need to document a lot of things that interest me and put forward to the world my everyday learnings. As I keep on venturing more and more into various technologies, I keep stumbling across lots of challenges which are worth documenting for like minded people to come across and get the benefit.

People coming across this blog would mostly find the articles about Operating Systems (mostly Linux), HowTo's on various topics, Databases in general and Data Warehouse, Performance and Scalability of various Web Applications etc and lot of Design patterns revolving around various technologies. Hope you find the blog extremely useful!!!