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

Saturday 5 November 2016

Oracle VirtualBox: access Windows-host shared folders from Ubuntu-guest

Its a re-blog from

http://www.giannistsakiris.com/2008/04/09/virtualbox-access-windows-host-shared-folders-from-ubuntu-guest/

This article explains how to share a folder from windows host to ubuntu guest. 

Installing oracle java 8 in ubuntu 16.04 ELT server

Hi,

Follow below article to install oracle java 8 in ubuntu server (desktop).

https://www.digitalocean.com/community/tutorials/how-to-install-java-with-apt-get-on-ubuntu-16-04


Installing Oracle JDK steps :

1) Install oracle PPA's and update package repository

$ sudo add-apt-repository ppa:webupd8team/java
$ sudo apt-get update

2) Install oracle 8 java

$ sudo apt-get install oracle-java8-installer

3) Manage java (if there are multiple java installations are available, chose a one by typing below command)

sudo update-alternatives --config java

4) Configuring JAVA_HOME environment variable

Type below command to open environment file with nano editor 
sudo nano /etc/environment

at the end of the file add below path 
JAVA_HOME="/usr/lib/jvm/java-8-oracle"

Save the file and come back to command prompt and then issue below command to reload the environment variable. 
source /etc/environment

check JAVA_HOME path at command by issuing below command
echo $JAVA_HOME 

after issuing above command, you should get below path usr/lib/jvm/java-8-oracle


I hope this help you and for open jdk or other versions of java installation refer to the link
provided at the beginning of the post.


- Sadakar Pochampalli


Wednesday 24 August 2016

Tip : Sparkline in Pentaho CDE


1) Data Format for Spark line (field can be a string but it should be comma separated)


2) Data source connection in CDE and query

3) Define the column type in table component

4) Sample output



Sunday 21 August 2016

Pentaho Ctools CCC(Community Chart Components) charts latest demos dashboard-1 , dashboard-2 & dashboard-3

Hi,

Downlad and explore my latest demo workouts on CCC charts. In Ctools(CDE) there are majorly 18+ visualizations are available out of the box.

The 1st demo has...
1) Area chart
2) Bar chart
3) Box-Plot
4) Bullet chart-1 and Bullet chart-2
5) Dot chart and
6) Heat grid chart

(click on image to get best view)
2nd demo has...
1) Line chart
2) Metric dot chart
3) Metic line chart
4) 100% stacked bar chart
5) Dual level pie chart and
6) Stacked area chart

(click on image to get best view) 

3rd demo has...
1)  Stacked dot chart
2) Stacked line chart
3) Sunbrust chart
4) Treemap chart
5) Waterfall chart
6) Dial chart

(click on image to get best view)


This demo also have basic bootstrap CSS to make the panels zero border and change the colors of the panels. (have a close look at panels - showding effects are applied which makes good look and feel).

Note that the CCC components are not adjusted/added code to fit screen for mobile/tablet devices. Currently these supports desktop view.


Source code download and deployment procedure
1) Make sure you have"foodmart"database and "pentaho_cde_demo" databases are running on postgresql server with postgres/postgres as credentials. ( these two dbs are avaialble in download section as .backup files, restore them in your postgresql).
2) Upload the Dashboards2.zip file from PUC and check for the outputs.

Download

drop a comment if you have any problem in downloading or deploying the package.



Tip : How to create postgresql JNDI connection in Pentaho Data Integration (PDI) or Kettle ETL

Hi,

In this post you will see how to connect to postgresql "foodmart"database in PDI using JNDI concepts.

Software used : PDI 6.1 CE, PostgreSQL foodmart db

Steps :
1) Install postgresql driver in "lib"folder of PDI installation  (download driver here)
  Example : D:\data-integration\lib

2) Navigate to "sample-jndi" folder at PDI instllation  and open jdbc.properties file using notepad
3) Add below set of properties with foodmart database information

FoodMart/type=javax.sql.DataSource
FoodMart/driver=org.postgresql.Driver
FoodMart/url=jdbc:postgresql://localhost:5432/foodmart
FoodMart/user=postgres
FoodMart/password=postgres

NOTE : FoodMart is the JNDI name we use in PDI tool to connect foodmart database

4) Launch the PDI tool and create a  new transformation
Click on View tab -> then under the transformation name right click on "Database connectioin"node to create a new connection
Give a name for the connection and in the setting write "FoodMart" as the JNDI name
make sure that you are accessing "JNDI" from General selection.


 5) Click on "Test" button to see the successfull connection of JNDI. note that you need not to relaunch the PDI tool if you are modifying only properties file with foodmart information. Incase of installating driver if your PDI is running, a restart/relaunch of PDI tool is required.


Cheers.!
Sadakar Pochampalli 










Wednesday 10 August 2016

Example : Customer Vehicle Summary sample dashboard in Pentaho CDE

Hi,

This example has below features

1) Dynamic features with parameters
2) Drill down functionality on first dashboard
3) Extension points
4) Query component example
5) Rectangular Boot strap panels
6) Online image URL
7) Interactivity features of pie charts
8) Table component in drill down dashboard
9) Drill back to Home dashboard from detailed dashboard
10) Basic CSS, CDF based java script functions.

(Click on image to get best view of the dashboard)

Summary Dashboard : 
Drill down dashboard:

NOTE : note that this dashboard is not made for mobile devices though uses bootstrap panels.


Download Example Here: Click me

Deployment process is : 
1) Upload the zip file to your pentaho 6.1 CE/EE
2) Make sure you run postgresql server having foodmart database of Jasper Server

Thank you
Cheers.!
Sadakar Pochampalli


Saturday 30 July 2016

Tip : Pie Chart Different Look and feel - Slice detachment property in Pentaho CDE

Hi,

To get slices detached from the centre, use below property from Advanced Properties of pie chart.
explodedSliceRadius=20%


Sample output : 


Thanks
Sadakar Pochampalli 

Monday 4 July 2016

Tip : Install Virtual Machine Disk file (Or .vmdk file ) in Oracle Virtual Box

Hi,

This post will talk about the installation of .vmdk file in Oracle Virtual Box.

Environment tested :
1) Guest : Windows 10 64 Bit
2) Host : Cent OS 6.5 Final (.vmdk file)

Steps :
1) Download and install Oracle Virtual Box in windows from below link
  https://www.virtualbox.org/wiki/Downloads

2) After the installation, you will get a short cut key for the installed Virtual box on desktop.

3) Double click on it to open the V.B and then click on "New" rounded button.

4) It will open a small pop-up window, fill the options as shown below and click on "Next" button.
   name = sadakar
   Type = Linux
   Version = Debian(64 bit)


5) Give memory size = 2GB and click on Next button.
6) In next window ( Hard disk) , out of 3 radio buttons, select "Use an existing virtual hard disk file" and browse for the .vmdk file in the directory system then click on Create.

7) This will create the Cent OS operating system which already in the form OS into Virtual Box.

I hope it helps some one.


References :
http://techathlon.com/how-to-run-a-vmdk-file-in-oracle-virtualbox/



Friday 17 June 2016

Pentaho Ctools book from Miguel Gaspar & Packet/Safari Publishers

Hello Readers,

Ctools first ever book is released..!

Thank you for the author and publisher for making me part of this project on review side. :-) :-)



Here is the snapshot : (Available as e-book and print version)

OR




URL :
https://www.packtpub.com/big-data-and-business-intelligence/learning-pentaho-ctools

Read Pedro's writing on the book here : 
http://pedroalves-bi.blogspot.in/2016/06/new-book-available-learning-pentaho.html


Regards,
Sadakar Pochampalli

Tuesday 10 May 2016

D3 Chord diagram Visualization example in Pentaho CDE

Hi,
In this post you will learn how to develop D3 Chord diagram.

Software used for this example : 
1) Pentaho BA Server 6.1 CE
2) Postgre SQL foodmart database
3) Pentaho Ctools 6.1.0.1-196 (master)
4) D3 Component Library 14.06.18

NOTE: Original Code is taken from this site with slight java script modifications 
https://bl.ocks.org/mbostock/4062006

(Click on image to get best view)

 Steps : 
1) Install D3 Components Library from Market Place
2) After successful installation, restart the server if it is already running. 
3) From the home menu click on "Create New" -> "CDE Dashboard"

4) Layout section :
#) Design the layout as shown in above image
2 rows, first row is for dashboard title, 2nd row is for dashboard content(table & chord diagram). 
Adjust the layout if you have any parameters to display 
(This dashboard is not associated with any parameters). 

5) Data sources section :
Connect to postgre SQL database as shown in below image and test the connection using CDA editor and preview the query written. 

( Click on image to get best view)
6) Components section :
#) From D3 Components section select "D3 Component" as shown in below image
#) Fill the Component properties as shown in below image

(Click on image to get best view)

#) Core part : Convert SQL result set to matrix format and write D3 script for chord diagram
Copy paste below code in "Custom Chart Script" code section (later modify it as per your requirement)


function f(dataset){

var matrix = [];

for(var i=0; i < dataset.resultset.length; i++){
    var dataObject=[];
    /*
    dataObject.a = dataset.resultset[i][0];
    dataObject.b = dataset.resultset[i][1];
   alert(dataObject.a);
   matrix.push(dataObject);
   */
   matrix.push([dataset.resultset[i][0],dataset.resultset[i][1],dataset.resultset[i][2],dataset.resultset[i][3]]);

    alert([dataset.resultset[i][0]]);
   alert([dataset.resultset[i][1]]);
   alert([dataset.resultset[i][2]]);
   alert([dataset.resultset[i][3]]);
    }
  

/*   
var matrix = [
  [11975,  5871, 8916, 2868],
  [ 1951, 10048, 2060, 6171],
  [ 8010, 16145, 8090, 8045],
  [ 1013,   990,  940, 6907]
];
*/

var chord = d3.layout.chord()
    .padding(.05)
    .sortSubgroups(d3.descending)
    .matrix(matrix);

var width = 860,
    height = 400,
    innerRadius = Math.min(width, height) * .41,
    outerRadius = innerRadius * 1.1;

var fill = d3.scale.ordinal()
    .domain(d3.range(4))
    .range(["#000000", "#FFDD89", "#957244", "#F26223"]);

var svg = d3.select("#"+this.htmlObject).append("svg")
    .attr("width", width)
    .attr("height", height)
  .append("g")
    .attr("transform", "translate(" + width / 2 + "," + height / 2 + ")");

svg.append("g").selectAll("path")
    .data(chord.groups)
  .enter().append("path")
    .style("fill", function(d) { return fill(d.index); })
    .style("stroke", function(d) { return fill(d.index); })
    .attr("d", d3.svg.arc().innerRadius(innerRadius).outerRadius(outerRadius))
    .on("mouseover", fade(.1))
    .on("mouseout", fade(1));

var ticks = svg.append("g").selectAll("g")
    .data(chord.groups)
  .enter().append("g").selectAll("g")
    .data(groupTicks)
  .enter().append("g")
    .attr("transform", function(d) {
      return "rotate(" + (d.angle * 180 / Math.PI - 90) + ")"
          + "translate(" + outerRadius + ",0)";
    });

ticks.append("line")
    .attr("x1", 1)
    .attr("y1", 0)
    .attr("x2", 5)
    .attr("y2", 0)
    .style("stroke", "#000");

ticks.append("text")
    .attr("x", 8)
    .attr("dy", ".35em")
    .attr("transform", function(d) { return d.angle > Math.PI ? "rotate(180)translate(-16)" : null; })
    .style("text-anchor", function(d) { return d.angle > Math.PI ? "end" : null; })
    .text(function(d) { return d.label; });

svg.append("g")
    .attr("class", "chord")
  .selectAll("path")
    .data(chord.chords)
  .enter().append("path")
    .attr("d", d3.svg.chord().radius(innerRadius))
    .style("fill", function(d) { return fill(d.target.index); })
    .style("opacity", 1);

// Returns an array of tick angles and labels, given a group.
function groupTicks(d) {
  var k = (d.endAngle - d.startAngle) / d.value;
  return d3.range(0, d.value, 1000).map(function(v, i) {
    return {
      angle: v * k + d.startAngle,
      label: i % 5 ? null : v / 1000 + "k"
    };
  });
}

// Returns an event handler for fading a given chord group.
function fade(opacity) {
  return function(g, i) {
    svg.selectAll(".chord path")
        .filter(function(d) { return d.source.index != i && d.target.index != i; })
      .transition()
        .style("opacity", opacity);
  };
}


}

#) Now, save your dashboard and preview it.

#) CSS used for this dashboard in "Resources" section is

body{
    margin-top:30px;
    font: 10px sans-serif;
}

#row2Col1Table_wrapper{
          padding-top: 50px;
}
.chord path {
    fill-opacity: .67;
    stroke: #000;
    stroke-width: .5px;
}

.label{
    font : 10px sans-serif;
}  


I hope this post helps some one in the community to get start with Custom D3 charts. 

In future posts you may find interesting visualizations in this site. Stay tune for updates. 


Download this example :
Click Me

Deployment Procedure:
Upload this example using PUC and in the data sources change the postgresql url and passwords as per your requirement.


 References for this post : 

1) http://diethardsteiner.github.io/dashboards/2014/10/15/CCC-Add-Event-Info-to-your-Charts.html 
2) https://bl.ocks.org/mbostock/4062006 
3) http://bl.ocks.org/mbostock/1046712 
4) http://www.delimited.io/blog/2013/12/8/chord-diagrams-in-d3

- Sadakar Pochampalli  

Sunday 8 May 2016

Oacle 11g express edition database connection and sample query execution example in Pentaho CDE

Hi,
In this post you will see how to connect to Oracle 11g Express edition and how fire a query against the connected data-source in Pentaho CDE

Software used to test this example : 
1) Pentaho BA Server 6.1
2) Pentaho Ctools (6.1- CDE,CDF,CDA,CGG)


Steps : 
1) Download and install "ojdbc6" in "lib" folder of pentaho installatio.
 http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html
 Driver installation location : 
(E:\2_Archive_Installed\Pentaho\6.1\biserver-ce-6.1.0.1-196\biserver-ce\tomcat\lib)

2) Start the pentaho BA server using "start-pentaho.bat" . (Double click on it)

3) Create a new dashboard (Home -> Create New -> Dashboard) and save the dashboard with the name of your choice ( In my case it is "Oracle 11g Connection")

4) Design the layout for a table component ( row -> Column).

5) Navigate to Data source section and click on " SQL Queries" from the bottom and then click on "sql over sqljdbc"
6) In its properties, fill the query name, username, password, URL as shown in below image.
Name= Oracle11g_Query
Driver = oracle.jdbc.OracleDriver
User Name = sadakar
Password = sadakar
URL =  jdbc:oracle:thin:@//localhost:1521/XE ( I used default SID as it is my local instance) 
Query = SELECT * FROM employee

 
7) Test whether the connection is successful or not and preview the query output.

#) Save the dashboard, unless you save it, the CDF will not generate .cda file to test the connection
    (Of course, you can write your own CDA file, but in this test I am not going to talk about it).
#) Go the location where the .cda generated (its the location where you saved your dashboard).
#) Open the CDA file in Edit mode and click on "Preview" button.

#) It will open a new tab in the browser

Sample link for CDA 
(http://localhost:9090/pentaho/plugin/cda/api/previewQuery?path=/public/D3%20Calendar%20View%20Example/Oracle%2011g%20Connection.cda)

#) Select the "Data Access ID" and see the result set. If you wont see the preview you must check the  database connection properties. (You can use any other client tool that takes SQL url, username, password and driver class name).


8) Now, take a component and populate this result set ( In my case, I took "Table" component from Components section tested it).
Sample output of table component on dashboard: 

This way one can connect to "Oracle 11g Express edition". I hope it helps someone in the community.

References : 
URL I have taken from : https://razorsql.com/docs/help_oracle.html
Driver class & Other formats of URL : https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html

- Sadakar Pochampalli  
 

Stream lookup Step sample example using oracle tables in PDI

Hi,

This is re-blogging from below my previous post :
http://pentaho-bi-suite.blogspot.in/2014/09/stream-lookup-step-explained-with.html

Just thought of doing the same example using Oracle 11g Express database and here are the steps how I did.
You will learn , how to connect to Oracle 11g database and Stream lookup step capability with tables. 

Software Used for this example :  
1) Oracle 11g Express Edition
2) Pentaho Data Integration 6.1 CE

First, we will see how to connect to Oracle 11g Express edition in Pentaho data integration. 

NOTE : 
Before connecting oracle xe database(or database of your choice) download ojdbc6 driver from http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html site and paste in "lib" folder that can found at kettle installation folder at E:\2_Archive_Installed\Pentaho\6.1\pdi-ce-6.1.0.1-196\data-integration\lib
 
Now,  restart the kettle if it is already running. 
 
1) File -> New Transformation
2) Right click on "Database Connections" node and then click on "New"
3) As shown in below image give all the properties
Host Name : localhost, database name : xe, username and password : sadakar/sadakar
Click on "Test" button to confirm the connection :
Click on "Share Connection" for future usage.

Aim of the post : 
 lookup the data coming from employee(source) table with department (lookup) table  and insert the location field from department table in empolyee_with_locations (target) table.

Source table : employee
Target table : employee_with_locations
Lookup table : department

 DDL and sample data for employee and department table is as follows 
employee table
CREATE TABLE SADAKAR.employee
(
  EmpID INTEGER primary key
, Name VARCHAR2(30)
, DeptID_Emp INTEGER
)
;

SELECT * FROM employee
 
empid name    deptid_emp
110    Sadakar    10
111    Hasini    10
112    Dolly    20
113    Kutti    20
114    Jikky    30

department table
CREATE TABLE SADAKAR.department
(
  DeptID_Dept INTEGER primary key
, DeptName VARCHAR2(30)
, Location VARCHAR2(2000)
)
;
SELECT * FROM department
deptid_dept deptname location
10    Mathematics    Netherlands
20    Computers    USA

Also create DDL for target table 
employee_with_locations
CREATE TABLE SADAKAR.employee_with_locatioins
(
  empid INTEGER primary key
, name VARCHAR2(30)
, dept_id INTEGER
, location VARCHAR2(2000)
)
;

Now, design the transformation as shown in below image. 
Double click on Stream lookup step and specify the lookup ids and get the retrieve field as shown in below image

Now save the transformation and preview the output. 
Output should be :

Lastly, the difference between "Stream lookup" and "database lookup" is here. 

Stream lookup stores the data in memory and then it will start doing all of the lookups, data can come from anywhere (from file or table)
Database lookup looks up data from a database, and only touches the data you lookup.

References :
http://forums.pentaho.com/showthread.php?65356-Difference-between-stream-value-lookup-and-database-lookup-step

I hope it helps some one.! 

Sadakar Pochampalli

Friday 29 April 2016

Pentaho Ctools(CDE,CDF,CDA & CGG) technical interview questions

Hi,

I have written basic to intermediate level Ctools interview questions.  

Sample Questions : 
1) How do you write MDX queries in CDE ? How do you feed a chart component with MDX ?
2) What are parameters and listeners ? What is the parameter syntax ? 
3) What are widgets ? give an example
4)  What is cross tab mode & Series in rows for a CCC chart component ?
5)  What are extension points ? Give at least 5 examples.


Download 40+ interview questions here: Click Me

 Cheers..!!!

Thursday 28 April 2016

Charts CGG Component example in Pentaho CDE

Hi,

This tutorial helps you in understanding Charts CGG Component.

More About CGG : 
http://www.webdetails.pt/ctools/cgg/
http://pedroalves-bi.blogspot.in/2012/09/cgg-putting-ccc-charts-in-pentaho.html
http://diethardsteiner.github.io/prd/2015/02/10/Pentaho-CCC.html

 Webdetails says about CGG
Open up an existing CDE Dashboard in Edit Mode and press the keyboard shortcut "Shift + G". This will prompt a popup, where you can choose which charts in the Dashboard you want to render as CGG charts.
When you save the Dashboard, CGG will generate a JavaScript file for each chart chosen in the popup, and will save it in your Pentaho Solution directory. Those JavaScript files are, basically, CCC chart definitions.

Example : Explanation  with  2 dashboards
1 is for regular dashboard and another is for CGG images dashboard.
Software Environment :
1) Pentaho BA 6.1 CE Server
2) Ctools

Dashboard-1 : 

Lets assume you have below shown dashboard
Open the dashboard in Edit mode and press Shift+G, it will open a pop window.

Observe the generated JS files in the location where the dashboard is saved, for example.

Dashboard-2 :

Design the layout and take two CGG components from CCC Charts as shown below  and give
Cgg path for the Js files generated in dashboard-1

Cgg path for Pie chart in Dashboard-1 : public/test/1_chartPie.js
Cgg path for Dot chart in Dashboard-2 : public/test/2_chartDot.js

NOTE : Cgg Path = Path of generated JS files 

(it can be done in single dashboard also)

(Click the image)

2nd Dashboard Preview : (Click on the image)

NOTE : 
#) In 2nd dash board it is not required define any query components.  
#) 2nd dash board will render the SVG images generated in 1st dashboard and get the image looks like dashboard view. 

Download :
Dashboard-1  & Dashboard-2 :
Click Me 
Deployment:  
Upload the two zip files to Public folder and run PostgreSQL foodmart database in your environment (or change the connections and queries as per your environment).

References : 
http://forums.pentaho.com/showthread.php?146887-Define-Size-of-dot

Learn how to create and use Templates in Pentaho CDE

Hi,
This tutorial teach you how to create & use templates in Pentaho CDE.

Why templates ? 
Templates are pre-defined structure of basic reports/a dashboard.
Whenever you have similar structure for some 4 or 5 dashboards in a project and if you use template you can faster the development time.

Pentaho CDE templates can include Layout, components and Data sources. In simple terms if you save a dashboard as template it will available in "My Templates" section.

Click on this image:


Lets see how to convert a dashboard as a template and use it in a new dashboard creation.

Steps to create a dashboard and convert it as Template
1) File -> New -> CDE dashboard
2) Now, lets layout the dashboard (that we will save as template) as shown in below image
3) Lets define data source connections in "Data source sections" as shown in below image
4) Save the dashboard in some folder in repository and see the preview of the dashboard and the sample is some thing similar to the one shown below.

(Please click on Image for best view)
5) Making dashboard as template 
a) Click on "Save as Template" as shown in below image
b) It will open a popup window. Fill the name, title and check the components and data sources.

c) Now, this saved template "Template_sadakar_sample_2" will be available in "My Templates" section.

6) Applying Template ( Checking whether the template is available or not )
#) click on "Apply" template button.
#) From the opened popup, click on "My Templates" button as shown below

#) Now chose the template created and click on "OK" button.
#) Click on "OK" button..

#) Save the dashboard that is being applied with above template.


This way one can work with templates in CDE editor. I hope it helps some one.!

Sadakar