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

Tuesday, 21 July 2015

Bar chart drill down to another dashboard example in Pentaho CDE

In this post, you will find the quick steps and best practices to develop bar chart drill down to other dashboard.

Soft ware setup for this example
1) Pentaho CE 5.4 BA server
2) Pentaho C-Tools 15.06.30
3) Foodmart database running on PostgreSQL server.
4) IE or Mozilla firefox.

Problem Statement : Drill down bar chart
1) Create 2 dashboards
2) Dashboard 1 :
contains bar chart - In this example lets say quarters(Q1,Q2,Q3 and Q4) on X-axis and UnitSales and StoreSales on Y axis.
3) Dashboard 2 :
A line chart - Months of quarter on X axis and UnitSales and Store Sales on Y axis.
4) Functionality :
When you click on a bar from dashboard 1 the quarter has to be passed as parameter value to the 2nd dashboard.

Steps :
1) Dashboard -1 
a) Go to bar chart click action and write below code. (Don't forget to make clickable = true). :-)

    function f(scene) {
    var vars = scene.vars;
    var s = vars.series.value;
    var c = vars.category.value;
    var v = vars.value.value;
    alert("series: " + s + "\ncategory: " + c + "\nvalue: " + v);  

    window.location =url+'?param_Quarter='+c;

b) In above code, c - represents category, the X-axis value, in this example the quarters Q1, Q2, Q3 and Q4.

c) param_Quarter is a string written in code, it would be the parameter(or custom parameter) in 2nd dashboard.

d) Combine URL and 1st parameter with ? (question mark). If you use & you will get error message like "Sorry, we really did it" .

e) When you pass multiple parameter separate parameters using &  but note that URL and 1st parameter should be separated by ?

f) Use alert functions to test the categories,values, series and etc.

Dashboard 2 :

1. Develop dashboard with custom parameters.(In this example , I have created  a custom parameter  that is :
param_Quarter  and the java code (you can say default value(s) expression editor) )

2. Don't directly test the drill down from 1st dashboard by clicking bar chart.(keep it on hold for some time).

3. View the 2nd dashboard output in browser though you will not get output.  (This is to take the URL)

4. Now, add the custom parameter created to the URL and test dashboard 2 individually, if you get the result then your 2nd dashboard is dynamic with parameters. Importantly, provide listeners and parameters to the components where ever required in your 2nd dashboard.  

Example : 

That's all, you are done.!!! 

Download Example :  Bar chart drill down example 
and have at the sample dashboards.

Note that to run this example you need to have foodmart database running on PostgreSQL database .
(I used jasperserver example foodmart database).

Here are quick snapshots of the output.

Dashboard 1 : 

Dashboard 2 : 

Thanks for reading this small article. Hope it helped you a bit : -)



Sunday, 19 July 2015

Tip : Pentaho CDA : Error Listing Queries : Observation in CDE & CDA

This post is a test outcome on how queries and its connections works in pentaho CDE.

Problem found : Error Listing Queries
I have created 3 new queries of connection types JDBC(sql over sqljdbc). I have not provided any connection details for 1st and 3rd queries but provided all the details username, password and URL for 2nd query.

When I previewed the queries keeping above as thy are, using CDA editor, it has not listed out the queries and shown me "Error Listing Queries". 

 Solution : 
I had tested the username, password and URL in some other database client tools. Every thing working fine but it is not listing the queries in CDA. Just removed the 1st and last query and tested again. I didn't find the magic behind it but worked fine. Now, it listed the query and able to see the output.

So, I think, the order of queries with connections providing plays important role while working with CDE dashboards.  

- Sadakar Pochampalli

Thursday, 16 July 2015

Pentaho 5.4 CE Login Page or Pentaho User Console(PUC) - 5.4 CE Customization Tips

In this post we will see how to customize pentaho 5.4 CE BA server login only page.

I thank you to the Java+Web developer for helping me in how to play around styles of Login Page using firebug and then in PUCLogin.jsp page

I had not involved in full time web development therefore you will find the tips not so awkward but hoping that this small write up would help someone somewhere who want to learn and apply  for your quick business needs.

Software Requirement : 
1) Pentaho BA 5.4 CE Server
2) Images (Your company logo, Your company favicon logo).
3) Any web-browser (I preferred Mozilla firefox as Inspcet / Inspect with firebug options are handy to play around).


I have tested all the changes explained below on laptop and desktop web browsers where I didn't find greater difference in UI.

I have also tested the changes made in iPhone and found there are some known alignment issues hence this post will work only for desktop/laptop browsers.

(Yes, I have a one by this time - thanks to my small customers/consultants.  In few of my earlier posts I used to test the mobile comparability of dashboards by minimizing the web browsers - Now, no more that method I will use :-)

Let us start with ,

1) Remove background Image

Problem : 

Solution : Remove "login-crystal-bg.jpeg" image

E:\1_Softwares\Business Intelligence\Pentaho BI Tools\BI Servers\biserver-ce-\biserver-ce\pentaho-solutions\system\common-ui\resources\themes\crystal\images

from above location remove "login-crystal-bg.jpeg" image. 

Login Page view after removing image

Points to note down :
1) Restart of server is required.
  (Why ? Web applications usually doesn't pick up updated or removed images ).
2) Delete the cache in browser (clean the browser).

2) Remove default login elevators

 Solution : 
 1) Disable/Comment few lines of code in PUCLogin.jsp file (This login page code is written in this file).
2) How to find ? Using webpage "Inspect Element"
    Right click on Login as an Elavator and click "Inspect  Element" as shown in below image

  • Note down the ID of the DIV element and search it in "PUCLogin.jsp" ( I used notepad++ to view the code and search) 
  • ID = eval-users-toggle-container (near by 186 line in the .jsp file)
  • Comment the code start with   <div id="eval-users-toggle-container"> and end with it's </div>
  • Server restarting is not required b'z we are not editing css or image but html

Sample view of BA server after this change : 

 3)  Change Credential Look and feel

Stage 1 :  Change <label> tags to <span> and add <br> tags as shown below

Stage 2 :  Adjust login form  - Horizontal center and vertically middel

a) Inspect the id="login-form-container" and adjust left and top pixels i.e., as shown in above image add them to the element class. As a result style tag will be added to the container.

b) The above is on fly experiment, do the same in PUCLogin.jsp file
 add style="top: 150px; left: 440px; border-radius: 25px; border: 2px solid rgb(178, 196, 212); width: 380px; height: 220px;"

c) After adding the code in PUCLogin.jsp should look like below (Line number near 167)

<div id="login-form-container" class="lang_<%=cleanedLang%>" style="top: 150px; left: 440px; border-radius: 25px; border: 2px solid rgb(178, 196, 212); width: 380px; height: 220px;">

d) Server restart is not required -> To see the affect reload the server by clicking F5 or refresh.

e) Change the left and top pixel(s) till you get it middle of Login Page. 

Stage 3 : Inner elements adjustments inside form
1) Add styles to the <span> and input elements explained in Stage 1 

<span style="margin-left: 35px;"><%=Messages.getInstance().getString("UI.PUC.LOGIN.USERNAME")%></span>
              <input id="j_username" name="j_username" type="text" placeholder="" autocomplete="off" style="margin-left: 20px; margin-top: 10px;">


 <span style="margin-left: 35px;"><%=Messages.getInstance().getString("UI.PUC.LOGIN.PASSWORD")%></span>
              <input id="j_password" name="j_password" type="password" placeholder="" autocomplete="off" style="margin-left: 30px; margin-top: 5px;">

2) Add styles to "Login" button 
<button type="submit" id="loginbtn" class="btn" style="margin-left: 199px; margin-top: -18px;">

3) Login form header (User Console) styling 
<h1 style="margin-left: 110px; margin-top: 20px;font-size: 25px;margin-bottom: 5px;">

4) Remove vertical scroll - add height style to below

<div id="login-wrapper" style="height:200px;">
  <div id="login-background" style="top:-82px;height:600px">

and comment below code

  <div id="login-footer-wrapper">
    <div id="login-footer" class="beforeSlide"><%=Messages.getInstance().getString("UI.PUC.LOGIN.COPYRIGHT", String.valueOf(year))%></div>

After changing all the pentaho login page look as below

 5) Change favicon image .
File Name : PUCLogin.jsp

Find below code and change logo name to your logo name. 
<link rel="shortcut icon" href="/pentaho-style/favicon.png"/>

Location to find favicon image or place a new logo of your choice is 

E:\1_Softwares\Business Intelligence\Pentaho BI Tools\BI Servers\biserver-ce-\biserver-ce\tomcat\webapps\pentaho-style

Keep a new logo of your company with favicon.png and save the file. 

Refresh the PUC login page and find the change.

6) Change button colors 

Login Button color change : 
<button type="submit" id="loginbtn" class="btn-warning " style="margin-left: 199px; margin-top: -18px;"><%=Messages.getInstance().getString("UI.PUC.LOGIN.LOGIN")%></button>

Error button "OK" color change
   <div class="button-panel">
      <button class="btn-warning pull-right" onclick="document.getElementById('loginError').style.display='none'"><%=Messages.getInstance().getString("UI.PUC.LOGIN.OK")%></button>

7) Change puc-login-logo.png 
Image name : puc-login-logo.png
Location : <directory>\biserver-ce-\biserver-ce\pentaho-solutions\system\common-ui\resources\themes\images
How to : Take your company or client logo , rename it with same name (take a backup copy of original).

Adjustments : 
File to edit : PUCLogin.jsp
Find below code : 
<div id="login-logo"></div>
Change code to add styles :
<div id="login-logo" Style="left:440px;margin-top:80px;"></div

Download this PUCLogin_ModifiedCopy.jsp and check how the styles are added.

A sample final output of the pentaho login page

Thursday, 9 July 2015

Pentaho Data Integration Basics - 7 - (DWH) : Implementing SCD Type-II dimension table in data warehouse

In this post we will learn how to implement Slowly Changing Dimension (SCD) Type-II

What is SCD Type 2 ?

Software Requirements : 
1) Pentaho Data Integration(a.k.a Kettle ETL) - 5.4.0
2) PostgreSQL database
3) Sample data

Problem Statement :  (All the three points comes under)

1) Source data (Staging data environment)  - Raw Data for employee staging table

Let's say table name is: emp_staging

2) Problem scenario :

 Track the location of employee when he moved to other place.(with the same emp_no )
 i.e., keep the old location value along with new location value (i.e., keep track of history - how ? )

Example :
If 103 numbered employee moves to a new location let's say "new delhi" keep both the records(old location and new location records) in the dimension table

CASE 1 : emp_staging (raw data) table after 103 employee moved to a new location "new delhi"


CASE 2 : emp_staging (raw data) table after 103 employee moved again to a new location "USA"
NOTE : employee can be shifted to "N" number of times to "N" number of locations.
Whenever employee moves to a new location keep all the locations in the dimension table along with new location.

3) Expected final output for dimension table

 Let's say, the dimension table name is "dim_employee"

ETL  1st RUN : (On raw data)
table name : dim_employee

ETL 2nd RUN (Change-1): Employee 103 moved to "new delhi" from "chenni"
table name : dim_employee

3rd RUN(Change-2) : Assume the same 103 employee moved from "chenni" to "USA"
 table name : dim_employee

Theory about SCD Types: 
1) Type 1 :
2) Type 2 :

All 3 types.

Technical Implementation of above example in Pentaho Data Integration (using Kettle ETL)

1)  ETL :  Prepare staging area (Sample data) in staging database.  (table name : emp_staging)
2)  Design : Dimension table design in data model (dim_employee)
3)  ETL : load the dimension table (Run 1 , Run 2 and Run 3 , for this example). 

1)  ETL : Prepare staging area(Sample data) in staging database. (table name : emp_staging)

2)  Design : Dimension table design in data model (dim_employee)

3)  ETL : Design transformation and load the dimension table 

Dimension Lookup/Update

1) Provide all the db connections
2) Write the dimension table name (eg : dim_employee)
3) a) Surrogate key = primary key = emp_id_sk
    b) version field holds the revision numbers for dimensions to keep track of history.
4) Keys
     Stream field = emp_code and dimension field=emp_code
5) Fields

Download this example :  Click here

Hope this helps some one :-)

Thanks for your time on this page :-)