Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Wednesday 20 November 2013

Installation of Pentaho 5.0.1 CE in windows 7 & changes made from previous version.

Being crazy of Pentaho and it's associated tools I'd like to share some thing about Pentaho CE 5.0.1 BA latest release..
First of all, I'd like to thank you to the web details team for the efforts they kept in bringing up the powerful analytical engine(BA server).

The home page itself make you to stick with BA server where you can enter into the server with "admin" and "password" as the default credentials.. Yes, "joe" user is no more but he left with the same password to log into BA server.

There will be NO more admin console ... Now it is very easy and developer friendly to navigate in one console(User Console) to give data base connections and new roles for new users..

A lot of stuff included in the home page itself, giving the blogs links, books on different tools of pentaho family. In a single word I can say , All the stuff related to Pentaho at a single place. A BI developer like me can reduce the time for developing reports, dashboards , jobs.

Market place for installing C-Tools and the installed plug-in's list drop down is one of the good ideas of the team.

As quick as Pentaho released it's CE 5.0.1 , I just downloaded and trying to explore the new look and navigating from all the menus, reports, connection places and etc.

It's very easy to install the pentaho CE BA now in windows...
Installation procedure :
1) Down load the software as a .zip file using this link .
2) Unzip it in your fav folder
3) Click on start-pantaho.bat file

Home page :


NOTE: path and class path for java must be set before the installation.

Tuesday 12 November 2013

Drill down from one dashboard to another dashboard in Pentaho CDE - Simple Example (Not included any parameters)

Hi Guys,

This post will teach you how you can drill down from one dashboard to another dashboard in pentaho CDE. This workout has done by one of my colleagues who is a fast learner.

You need to focus on 2 things in this post:
1) Connecting to JDBC datasource
2) Drill down from one dashboard to another dashboard.

NOTE :
I'll update you in next post about drill down from one dashboard to another dashboard using custom parameters concept.(Next work out).

Versions & Tools used for this post:
Pentaho C-Tools(CDE,CDA,CDF) 13.09 stable.
PostgreSQL DB - (foodmart database - which is one of  jasperserver default databases)
Pentaho BA server 4.8 stable.

SOURCE CODE OF THE EXAMPLE
Download the example using below link
https://drive.google.com/file/d/0BymV_QP4TGBEZ0p1cnpydDJGeDQ/edit?usp=sharing

Deployment procedure:
1) Down load the .rar file from the link(File-Download in the google drive)
2) Unzip it and place it in "pentaho-solutions" folder.
3) Refresh or clear the cache of the pentaho browser(find upper left - Browser)
4) If you are unable to find the folder name , you need to create an index.xml file (Eg: you can find it inside any folder. copy and paste it inside your working folder and change the name of it).
5) No need to restart the pentaho server.
NOTE: Edit data base connections as per your requirement also you may need to change the query if you use any other database than foodmart

Dashboard 1:
1) Layout section
* Design your lay out for dashboard1
2) Components section
* You will be placing 1 bar chart where you will click on bars for drilling down to another dashboard.
We will come back to this section again after designing Dashboard 1 and Dashboard 2
3) Data Sources section
* Click on Data source Icon on right top conrner.
* From the left .. click on SQL queries -> Click on sql over sqljdbc
* Give the name and all the properties as shown in below image.

NOTE:
PostgreSQL server default details :
Driver: org.postgresql.Driver
UserName : postgres
Password : postgres
URL:  jdbc:postgresql://localhost:6062/foodmart where 6062 is the port number which I used for postgres and foodmart is the database.
Note that default port number for postgresSQL is 5432 .

* Write a query which will suits for bars in the chart
* For eg:
SELECT
    c.country,
    c.state_province ,
    c.city,
    SUM(sf.store_sales)

FROM
    customer c,
    sales_fact_1997 sf
WHERE  c.customer_id=sf.customer_id
GROUP BY
    c.country,
    c.state_province,
    c.city

ORDER BY
c.country,c.state_province,c.city



* See the preview of the dashboard
* Out put will looks like below.

Dashboard 2 :
* Repeat the same steps as followed in Dashboard 1
* Take any component to display on the second dashboard.
* I have taken table component in this example and  the output will looks like below mentioned image.
*


We have done with 2 dashboards individually.
Now, It's the time for us to some trick on Dashboard 1 so that when we click on Dashboard 1 , it will have to take you to Dashboard2

Steps:
1) Lets go back to Dashboard 1
2) Go to the Chart component and in the properties give clickable as True
3) In the clickAction you need to give the URL of second dashboard in java script.
Eg:
function q(s,c,v)
{

window.location = 'http://localhost:8085/pentaho/content/pentaho-cdf-dd/Render?solution=CDE-+Exploring&path=%2FDevelopement%2FDashboard+Drill+down&file=drill_down_to_table.wcdf';

}


That's it you have done with drill down. Save the dashboard 1 and see the preview and then click on any bar appearing , you will navigate to the 2nd dashboard.

NOTE :
This post is only giving the idea of how to drill down from one dash board to another dashboard.
This post doesn't work with any parameters. The use custom parameter with drill down from 1 dashboard to another dashboard will come in next post from my end.


URL generating problem / Path problem for Second Dashboard

How to generate the URL for 2nd dashboard ( not only for this but also works for every dashboard).?

* Right click on 2nd Dashboard and click on "Open In New Tab" the with the URL the 2nd dashboard will open in fresh tab.
* Copy that URL and paste in drill down function.
* For eg : Just reference : Find the images below





References :
1) http://forums.pentaho.com/showthread.php?152634-Drill-down-from-bar-chart-to-another-dashboard

2) http://forums.pentaho.com/showthread.php?82999-Drill-Down-in-DashBoards

Sadakar
("Learning never exhausts the mind")




Wednesday 6 November 2013

Bar chart with Target lines in pentaho CDE - making bars as lines using java script in pentaho CDE

Hi guys,

This post teach you how to make bars as lines in pentaho CDE.

Source for this post is : http://jsfiddle.net/duarteleao/7maGD/
and http://type-exit.org/adventures-with-open-source-bi/2011/06/creating-dashboards-with-cde/

Scenario : Some end users wants visualization of grouped bars as lines where one of the bar is as bar and remaining bars as targeted lines on the same bar.


Properties have to give:
Plot2 : True
Find the remaining properties in below image: version of CDE is : 13.06 but will work in higher versions also.
In "Extension points" for the chart component you need to give 3 properties from the first link.
They are 

 extensionPoints: {
        plot2Dot_shape: 'bar',
        plot2Dot_shapeSize: function() {
                       var diam = this.chart.plotPanels.bar.barWidth ||
                       this.chart.options.barSizeMax;
           
                     return this.finished(diam);
        },
        plot2Dot_shapeAngle: function() {
            return this.chart.isOrientationHorizontal() ? 0 : -Math.PI/2;
        }

NOTE: Find the image for how to give the above code as properties in Extension points of chart  component.

 Sample output of the chart on dashboard after giving the above properties.

 

Sadakar
BI developer
("Learning never exhausts the mind")


Tuesday 5 November 2013

Adding dollar($) sign to X-axis lables(values) for bar charts in pentaho CDE

Hi Guys,
Using below java script one can easily add '$' sign to the values of X-axis for any charts in pentaho CDE.
Example:
function f(v) { return "$" + sprintf('%d', v/1000) + 'k'; }


Write the java code in "orthoAxisTickFormatter" java script wizard.
Make sure to give orthoAxisTicks as "True"

Sample output:


NOTE:
This code works with 13.06 as well with 13.09 version of pentaho CDE.
Also note that in java script if one line is not executing then the remaining lines will not execute. i.e., for instance you have 10 lines of code and 4th line is not executed then the remaining lines will not execute. Check with an alert function.


Sadakar Pochampalli
BI developer