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 28 May 2014

Tip - Changing background image of Pentaho 5.0.1 CE Server (Pentaho 5.0.1 CE Home Page Customization)

Hi Guys,

This small post will talk about the background image change of Pentaho 5.0.1 CE ..
People are interested in showing their organization images per open source servers like pentaho , jasper.. when start the server or log out from the server.

You just need to replace the image :
Image Name : 
login-crystal-bg.jpeg
Image Location : 
G:\Pentaho\biserver-ce-5.0.1-stable\biserver-ce\pentaho-solutions\system\common-ui\resources\themes\crystal\images

Default Image size : 1255X633 px

For the new image that you are going to apply should be of same size..

User online converts to change the size ( One that I've followed is : http://www.resize2mail.com/server-host.php)

To change the brightness of the picture use Microsoft Office Picture Manager(Picture->Brightness and contrast)...


That's it, you have done.....

NOTE : 
1) You need not to restart the server(But an F5 click is preferable in images folder to check)
2) If you want to make the background complete white, just take a white image.....

Sample Image after applying a new background image to the server :


References :
http://www.nextcoder.com/?p=1172



Sadakar
BI developer
Jasper/Pentaho/Talend

Friday 23 May 2014

Mobile Dashboard Example in Pentaho : Highly Interactive Dashboard example in Pentaho CDE using Bootstrap Css - Sales Dashboard Example 3

Highly Interactive Dashboard example in Pentaho CDE using Bootstrap Css.

Give a gift to your client with open source technologies 

All the concepts are taken from pentaho forum threads and from my R&.


Special thanks to Pentaho & to the team for this awesome tool. Thank you to Pedro, duarte.leao and  lukolap for actively giving hits and for your encouragement to learn the tips and tricks.


This is an example covered with essential features on the dashboard. As it is a demonstration example, there might be few known issues but I believe this will give at least some idea to the readers of this post. 


Example developed and published date :
May 23rd 2014
Updated on 26th May 2014

Software set up : 
CDE,CDA,CDF Trunk (after 14. 05.06) , Pentaho 5.0.1 Stable CE, foodmart database of Postgres. (database comes along with jasperserver example database for postgres)

This post will cover the below stuff .
1. Charts Communication (Inter frame/charts communication by passing category name).
2. Drill down from 1 dashboard to another dashboard.
3. Relatively adjusting the chart heights and widths using bootstrap css & java script.


4. Drill back to Parent dashboard by providing a link or button on 2nd dashboard.
5. Table component formatting – Removing row banding.
6. Horizontal scroll down for table component if there are more columns to show on the dashboard.
6. Majorly Bootstrap Penel: 
How to divide a single panel to place 3 charts using bootstrap HTML code. 
7.Changing the color of bars as the same color of slice(related to point -1)


The first 3 points are already covered in my previous example post.
You can refer  previous dashboard examples here with source code availability

End User Benefits :
1) Fast access of data on the dashboard -  using Pentaho cache mechanism.
2) Awesome look and feel of the charts – using protovis charts
3) Mobile/iPad/laptop/desktop comparability of the dashboard – Using bootstrap css.
4) High interactivity on the dashboard.
     a) tool tip & legends
     b) No drill down to the next level but get reflecting in the same dashboard page.
     c) 1 level of drill down to the child dashboard. 


1) CDE Dashboard Example using bootstrap css - Sales Dashboard 2
http://pentaho-bi-suite.blogspot.in/2014/04/bootstrap-example-in-pentaho-cde.html

2) CDE dashboard example - Sales Dashboard 1
http://pentaho-bi-suite.blogspot.in/2013/12/pentaho-cde-dashboard-complete-example.html

Source code of the present example : Sales Dashboard 3
Download the example by clicking below link 
https://drive.google.com/folderview?id=0BymV_QP4TGBEaVlYVjBSQk9abVk&usp=sharing


Images of the current Example:

Parent Dashboard :
Drill down dashboard :

NOTE :
For drill down dashboard, you need to change the port number in click action of 3rd chart (pie chart) [I have used 8085 port number]


What is updated on May 26th 2014 on the same example :
Point 7 is done .
In detail :

for example, Assume you have doughnut(formally pie chart) having 5 different colors and this chart is associated with a bar chart.

In simple terms as shown in below images on clicking on slice of pie chart the colors of the bars also should pick the same color(by default the bars do not pick the same color, either it pick a same default color (blue) or if you set color role=category  in the properties it will display all different colors).





To achieve the above you need to do the below

Parameter :
Create a parameter called "param3_color"
On pie chart :
set click-able as True
In the click action catch the color of slice in the created parameter.
the bold part of the code will do this job, the rest of the code for some other purpose in the dashboard.

function f(wedge) {
   
    Dashboards.fireChange('param1_ProductType', wedge.vars.category.value);
    //alert("you have clicked on"+" "+param1_ProductType);
   
    var color = this.pvMark.fillStyle();
    Dashboards.fireChange('param3_color',color.color);

   // alert(color.color);
  //  alert("Slice color code picked in parameter : "+param3_color);

}

On bar chart :
In the postFectch function write below code.
That means after fetching all color code you are applying that color code to the bars.

 function fun(){
 var selectedColor = Dashboards.getParameterValue("param3_color");
    if(selectedColor) {
        this.chartDefinition.colors = [selectedColor];
    }
 }




References :
1)http://forums.pentaho.com/showthread.php163345-Pass-pie-chart-slice-color-to-other-chart
2) https://interestingittips.wordpress.com/category/pentaho/page/2/
3) http://forums.pentaho.com/showthread.php?163032-Table-Component

4) http://forums.pentaho.com/showthread.php?163345-Pass-pie-chart-slice-color-to-other-chart&p=375976#post375976


Improve this dashboard by commenting - Suggestions & features add-ins are welcome to this example.

Sadakar
BI developer








Thursday 22 May 2014

Pentaho BA Enterprise Server 5.0.6 installation in linux

This post a small walk through the installation of Pentaho 5.0.6 EE in linux.

Download : Click here

Once you done with download you will get this file :(32 bi linux OS)
 pentaho-business-analytics-5.0.6-i386.bin

Run the file : Command is 

Downloads]$./pentaho-business-analytics-5.0.6-i386.bin

Sample Image

Click on Next -> and wait for few min .. it'll get installed.
Once the installation completes, go the browser and type http://localhost:8080/pentaho

That's it.

:)

Monday 12 May 2014

Creating Cubes with Oracle OLTP databases - A test scenario with synonims

This is article is written based on below queries before I explore it.

1) Is it mandatory to have Star Schema based warehouse to create Cubes ?
2) In oracle if you have multiple users(let's say multiple scheme's).. Is it possible t design a simple CUBE dimension from schema A of a table and measure from schema B of another table ?

The answer is , NO & Yes.

All the organizations may not maintain complete data ware housing solutions and run their transactional database using normalized OLTP models.

It is not mandatory to have a star schema based ware house to create OLAP cube to analyze your data but you can also use OLTP databases.


The new things I found while demonstrating a simple cube are..

1) Connecting to oracle database.
2) Fetching all the tables of all schemas.
3) Synonyms of a particular schema

 You will find a lot of time taking to load the database into PSW tool once you connected to a schema.. Using FILTER_SCHEMA_LIST at Options section of the database dialogue will take the control to load only specified schema tables.

By default the PSW engine assumes to load all the tables in all the scheme's.


A scenario of Dimensions and measures.

Suppose in a case, if you have a measure coming from table defined in SchemaA, measure coming from a table defined in SchemaB... in that case while giving tables for fact and dimension tables, the synonyms will not appear in drop down boxes... 

You need to hard code the tables names ... Of course your dimensions & measures will be in X with red color, it will not affect your definition of hard coded table names.

Save the schema and publish it to the server... have tested it with Saiku plug-in ad-hoc editor and worked nicely.


Thank you.

References :
1) http://jira.pentaho.com/browse/PSW-135

2) http://forums.pentaho.com/showthread.php?162540-OLTP-tables-VS-RDBMS-tables-Designing-OLAP-cubes-Peformance-factors&p=374714#post374714

3) http://forums.pentaho.com/showthread.php?157997-Schema-Workbench-freeze-after-establishing-a-connection-in-db








 

Tuesday 6 May 2014

Do you work with multiple databases in BI - Quick info of drivers details of leading databases & client tools info

Hi guys,

Not a big effort but keeping all info together for quick check of driver details in blog site...


Oracle 10g
Driver class
oracle.jdbc.driver.OracleDriver
URL
jdbc:oracle:thin:@localhost:1521:orcl where orcl is SID
User Name/password
system/manager
Download driver at

PostgreSQL
Driver class
org.postgresql.Driver
URL
jdbc:postgresql://localhost:5432/foodmart
User Name/password
postgres/postgres
Download driver at

MySQL
Driver class
com.mysql.jdbc.Driver
URL
jdbc:mysql://localhost:3306/foodmart 
User Name/password
root/password
Download driver at

Driver class
com.microsoft.sqlserver.jdbc.SQLServerDriver
or
MSSQLServer (com.merant.datadirect.jdbc.sqlserver.SQLServerDriver)
URL
jdbc:sqlserver://localhost:1433/DatabaseName
User Name/password
Username/password for ms-sql server

net.sourceforge.jtds.jdbc.Driver

Syntax:
jdbc:jtds:sqlserver://<host>:<port>/<database_name>
Eg:
jdbc:jtds:sqlserver://127.0.0.1:1433/test
Download driver at

Open source database client Tools
DB Visulizer
Squerriel


Thank you.
Sadakar
BI developer
 

Monday 5 May 2014

Embedding BI - Call username & password and dashboard parameters from CDE dashobard URL using iframe tags

A small workout on Embedding BI using Pentaho CDE dashboards..

Version Specific :
BA server : 5.1.0 stable CE
C-Tools : CDE,CDA,CDF of latest TRUNK version (after 14.x)

Steps :
1) Open your dashboard in new window to generate the URL
2) For instance the URL is some thing similar as shown below

URL when you open in new window
http://localhost:8085/pentaho/api/repos/:public:Explore:CDEExploring:Test:passParametersFromURL.wcdf/generatedContent

URL after adding BA Server Username and Password as parameters
http://localhost:8085/pentaho/api/repos/:public:Explore:CDEExploring:Test:passParametersFromURL.wcdf/generatedContent?userid=admin&password=password

URL after adding Dashboard parameters along with BA server credentials as parameters

http://localhost:8085/pentaho/api/repos/:public:Explore:CDEExploring:Test:passParametersFromURL.wcdf/generatedContent?userid=admin&password=password&param1_deptId=100&param2_Date=01/04/2014

Here you need to take custom parameters instead of Simple parameters.
i.e.,
For example :
param1_deptId is the custom parameter and in the java script area you need to write below code
function sendParam(){
   
       return  Dashboards.getQueryParameter("param1_deptId");
   
}


In another case of passing parameter like date type

param2_Date is the custom parameter and in the java script area you need to write below code
function sendParam(){
   
       return  Dashboards.getQueryParameter("param2_Date");
   
}


You need to add these parameters to your chart and query
For chart : 
Parameters : param1_deptId,param2_Date
Listners : param1_deptId,param2_Date
For query :
Parameters: param1_deptId (String Type) 
                     param2_Date(String Type)   

Sample Query in Oracle SQL where clause for the above scenario :
where 
to_char(column1_deptId)=${param1_deptId}
AND
to_char(column2_date(+),'mm/dd/yyyy') =${param2_Date}

note that in the above clause (+) indicates in right outer join Oracle 10g

NOTE: You can pass multiple general parameters by adding &paramName=value in the URL


3) Copy paste the URL in the web browser and test.. You will find the dashboard output directly.

Imp Note : 
URL passing mechanism in BA server 5 is slightly differ from previous versions.
As suggested in jira for pentaho 5 server, you need to modify applicationContext-spring-security.xml under pentaho-solutions/system

Change 1 : at line 22 


Original Code :
/api/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS
Replace the Original Code with :

/api/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,requestParameterProcessingFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS

Change 2 : at line 23  
Original Code :

/plugin/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS
Replace the Original Code with :

 plugin/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,requestParameterProcessingFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS

Once you done the changes , you need to restart the BA server.

Using iframetags :

<iframe src="DashboardURLwithParameters"></iframe>

References : 
1) http://pedroalves-bi.blogspot.in/2013/08/embedded-analytics-in-pentaho-with.html

2) http://forums.pentaho.com/showthread.php?157329-Authentication-for-CDE-Dashboard

3)http://jira.pentaho.com/browse/BISERVER-10708

4)http://forums.pentaho.com/showthread.php?146325-How-to-link-a-CDE-dashboard-to-another-CDE-dashboard-by-passing-parameters&p=358865#post358865


Thank you for reading this article and if you have any queries and/or suggestions drop your comments....