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  

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