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.

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.

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.