Tip : Tree Map Example : Display colors of groups based on condition.

function f() {
    var colorScale;

    var cd = this.chartDefinition;
    cd.legend = false;

    cd.leaf_fillStyle = function(scene) {
        /*
        if(!colorScale) {
            colorScale = pv.ramp('red', 'blue');
           
            var extent = this.chart.data.dimensions('size').extent();
            if(extent)
                colorScale.domain(extent.min.value, extent.max.value);
        }
 */      
        // Is colorScale created ?
    if(!colorScale) {
        colorScale = pv.Scale.linear().range("red", "black", "blue","#708090");
   
        var extent = this.chart.data.dimensions('size').extent();
        if(extent) {
            var min  = extent.min.value;
            var b=500;
            var c=1000;
            var max = extent.max.value;

            //colorScale.domain(min, (min + max) / 2, max);
            colorScale.domain(min,b,c,max);
        }
        }
       
        var size = scene.getSize();
        return colorScale(size);
    };
}

Fill Colour of bar(s) based on a condition in Pentaho CDE

This post will talk about conditional colours of bars with below like scenarios.

Sample Scenario : 
Print bar color as red when bar value <=2000
Print bar color as green when bar value >2000 and <10000
Print bar color as black when bar value>=10000

Scenario 2 : 
When representing -Ve values on charts .. this kind of representation is preferable. 

Code is Taken from Reference links :


function changeBars(){
var cccOptions = this.chartDefinition;

// For changing extension points, a little more work is required:
var eps = Dashboards.propertiesArrayToObject(cccOptions.extensionPoints);

// add extension points:
eps.bar_fillStyle = function getColor(){
var val = this.scene.vars.value.value;

if(val > 0 && val <= 5000){
return 'red';
}
else if(val > 5000 && val <= 50000){
return 'green';
}
else{
return 'black';
}
};

// Serialize back eps into cccOptions
cccOptions.extensionPoints = Dashboards.objectToPropertiesArray(eps);
}


NOTE : I have tried directly in Extension points but it did not work. 

Sample output 1 : 

 

Sample output 2 : 

function changeBars(){
var cccOptions = this.chartDefinition;

// For changing extension points, a little more work is required:
var eps = Dashboards.propertiesArrayToObject(cccOptions.extensionPoints);

// add extension points:
eps.bar_fillStyle = function getColor(){
var val = this.scene.vars.value.value;

if(val == 26297.2900){
return 'black';
}
else{
return 'blue';
}
};

// Serialize back eps into cccOptions
cccOptions.extensionPoints = Dashboards.objectToPropertiesArray(eps);
}

References : 

http://translate.google.com/translate?&ie=UTF-8&sl=&tl=en&u=http://www.redopenbi.com/group/ctools/forum/topics/colores-en-bar-chart?commentId=2400100%3AComment%3A103280&xg_source=activity&groupId=2400100%3AGroup%3A73260

OR

http://www.redopenbi.com/group/ctools/forum/topics/colores-en-bar-chart?commentId=2400100%3AComment%3A103280&xg_source=activity&groupId=2400100%3AGroup%3A73260

OR

http://forums.pentaho.com/showthread.php?150582-Bar-chart-colors-dynamically-changed-based-on-data-value-Along-with-JavaScript-vars

Heat Grid chart basic example in Pentaho CDE - updatable post

Happy Diwali 2014 22nd Oct :-)

Here is a sample work out on Heat Grid Chart....

Version: 14.10.15 stable CDE,CDF,CDA,CGG
Query: A simple query which runs on postgreSQL

What is Heatgrid ? 
A heat-grid is a colored matrix that displays a two-dimensional data-set. The intensity or the color of each cell depends on the value that it represents.

Layout section :
Row->Column->Bootstrap Panel

Components section:
Charts ->CCC Heat Grid Chart

Data Source:
query :
select 'f1' as Category, CAST ('12110' AS INTEGER)  as Series1,CAST (0 AS INTEGER) as Series2,CAST ('6312' AS INTEGER) as Series3,CAST ('18' AS INTEGER) as Series4
UNION
select 'f2' as Category,CAST ('5430' AS INTEGER)  as Series1,CAST ('1019' AS INTEGER) as Series2,CAST ('9205' AS INTEGER) as Series3,CAST ('1512' AS INTEGER) as Series4
UNION
select 'f3' as Category,CAST ('312' AS INTEGER)  as Series1,CAST ('5000' AS INTEGER) as Series2,CAST ('15444' AS INTEGER) as Series3,CAST ('7215' AS INTEGER) as Series4
UNION
select 'f3' as Category,CAST ('1278' AS INTEGER)  as Series1,CAST ('2165' AS INTEGER) as Series2,CAST ('5264' AS INTEGER) as Series3,CAST ('1040' AS INTEGER) as Series4
UNION
select 'f3' as Category,CAST (0 AS INTEGER)  as Series1,CAST ('209' AS INTEGER) as Series2,CAST ('3694' AS INTEGER) as Series3,CAST ('1257' AS INTEGER) as Series4

Sample output of the query:

category    series1    series2    series3    series4
f2                 5430        1019         9205         1512
f3                 312          5000         15444       7215
f3                 1278        2165         5264         1040
f1                12110         0             6312         18
f3                 0               209          3694          1257


To make the chart click-able you need to write below code extracted from webdetails.pt site
i.e.,

 clickable:   true
 clickAction:

 function(scene){
        alert('series = "'   + scene.getSeries() +
            '", category = ' + scene.getCategory() +
            '", color = '    + scene.getColor());
    } 

colour will give you the value of cell in Heat grid matrix.  

Test with Max and Min colour OR give colour codes in colour properties :-)

Sample output

Example Download Link :  Click Me

Documentation of CCC Heatgrid
 
1) http://www.webdetails.pt/ctools/ccc/

2) http://www.webdetails.pt/ccc2/

3) http://redmine.webdetails.org/projects/5/wiki/FAQ_Main_Changes_New_Features_CCC_v2

4) http://infocenter.pentaho.com/help/index.jsp?topic=%2Fpuc_user_guide%2Fconcept_grids.html


What next ?
1) Drill down on Heatgrid chart ?(With in the same page)
2) Dynamic

Error while saving & running the transformations/jobs in Kettle : Unexpected problem reading shared objects from XML file : null

What are shared objects in Kettle ?

The below pentaho wiki & info center gives the brief of the Shared object in Kettle. 

http://infocenter.pentaho.com/help/index.jsp?topic=%2Fpdi_user_guide%2Fconcept_pdi_usr_changing_the_pdi_home_dir.html

http://wiki.pentaho.com/display/EAI/.17+Shared+Objects



From the Menu bar


1) Click on Edit
2) Click on "Edit the kettle.properties file" to open it's properties.
3) Give value=50 for KETTLE_SHARED_OBJECTS variable
   i.e., KETTLE_SHARED_OBJECTS=50
4) Go back to the console and try saving the job and run it, now you will not get the error. 

Problem Image :

Solution Image :

Reference : 

http://forums.pentaho.com/showthread.php?93473-Unexpected-problem-reading-shared-objects-from-XML-file-null

Kettle : Stream Lookup Step explained with a Sample Transformation in Pentaho Kettle - Pentaho Data Integration

Hi Guys,

This simple transformation explains the "Stream Look Up" step example in Pentaho Kettle as part of my learning & documenting here for community developers.

In next articles I'll be sharing more interesting topics with End to End examples.

Example developed on : 
5.1.0 Kettle
Reference :
 D:\pdi-ce-5.1.0.0-752\data-integration\samples\transformations\Stream lookup - basics.ktr

Scenario : 
There are 2 tables lets say Employee and Department and the data is as follows in both of the tables.
There is a common field lets say DeptID_Emp and DeptID_Dept in both the tables. 

Lookup the DeptID_Emp field with DeptID_Dept and get the records of all employees with department names.

Employee Table:
EmpID    Name    DeptID_Emp
110    Sadakar    10
111    Hasini    10
112    Dolly    20
113    Kutti    20
114    Jikky    30

Department Table:
DeptID_Dept    DeptName
 10    Mathematics
 20    Computers

1. Drag and drop Data Grid step (Input->Data Grid) and Meta(Column names) and Data(Insert the employee data shown above) name it as "Employee".

2. Drag and drop Data Grid step (Input->Data Grid) and double click the step to open it's properties.
    Give meta data (i.e., column names with length) Insert the data shown in Department table.

3. Drag and drop Stream Lookup step (Look Up -> Stream Lookup).
   Connect Employee -> Stream Lookup and Department -> Stream Lookup
and open the properties of Stream Value Lookup.
 As shown in below figure set the configuration.

Lookup Step : Department
Filed : DeptID_Emp ( Lookup field from the source stream).
LookupFiled : DeptID_Dept (lookup the DeptID_Emp field with DeptID_Dept).

i.e., matching the values of the fields ( Internally it'll compared with = operator).

Get Fields : If you click on it, it will fetch all the fields from source stream (here it is Employee table) on.
Get Lookup fields: It'll fetch all the rows from lookup file(here it is Departmet table).

We can also provide default values to the fields that are coming from lookup file/table. 

4. Connect "Stream lookup" to a dummy step (Flow->Dummy) and have a preview.(right click on the dummy step and see the preview).

What the Stream Lookup do here in the transformation is : It'll look for a match from the source stream. If the values matches from the lookup file then associated department name is added to the dummy step else it'll take NULL value.

If we add select values step we can get the desired fields with new names.

Output is shown below image.

The sample transformation shown in below image :

Download the .ktr file here : Click me

:-)

Migrating data from Oracle database to Postgresql database using Pentaho Kettle

This post will talk about migrating data from Oracle database to Postgresql database.

The same can be doable from one database to another database.(i.e., for example MS-SQL server to postgreSQL).

Pentaho has it's in built wizard tools to Migrate data from one database to another another database.

Find the below steps how we can use this in-buit tool in migrating.

1) Create source database connection and target database connection.

i.e., for example oralce connection as source and postgresql connection as target.

Go to Tools -> Wizard -> Create database connection.

Repeat the same for postgresql connection.

2) Go to Tools -> Wizard ->Copy Tables . Find the below images.

3) It'll create a job and “N” number of transformations based upon the number of tables in oracle database.

4) Run your job.. That's all we have done.

Check the description & content of the tables in postgres.

References : 

https://wiki.postgresql.org/wiki/Migrating_from_one_database_to_another_with_Pentaho_ETL


http://wiki.pentaho.com/display/COM/Using+the+Copy+Table+Wizard

Toggle between two Divs for Pentaho CDE Charts

This post will cover the toggling among the charts.

i.e., Share one place holder for multiple charts by providing a link or a button.

Example developed on :

C-Tools of 14.07.29, foodmart of postgresql, pentaho 5.0.1 CE stable.

Step 1 :  Layout section.
1) Save the dashboard in bootstrap mode.
2) Row ->Column->Html

In Html write below code :

<p><a href="#" id="link">Show B</a></p>
<div id="a"></div>
<div id="b"></div>

<script type="text/javascript">
var $divA = $('#a'),
    $divB = $('#b'),
    $link = $('#link');

// Initialize everything
$link.text( 'Pie' );
$divA.hide();

$link.click(function(){

  // If A is visible when the link is clicked
  // you need to hide A and show B
  if( $divA.is( ':visible' ) ){
    $link.text( 'Pie' );
    $divA.hide();
    $divB.show();
  } else {
    $link.text( 'Bar' );
    $divA.show();
    $divB.hide();
  }

  return false;
});
</script>
 
Step 2: Data Sources section

1) Give all the connection details.

Name : query1 
 URL : jdbc:postgresql://localhost:5432/foodmart
 Driver : org.postgresql.Driver
Username/Password : postgres/postgres
Query :

SELECT * FROM
(
SELECT
        DISTINCT brand_name AS "Brand Name",
       SUM(unit_sales) AS "Sales"
FROM product p
INNER JOIN sales_fact_1997 sf7
ON p.product_id=sf7.product_id
INNER JOIN time_by_day t
ON sf7.time_id=t.time_id
WHERE
(
to_char(t.the_date,'YYYY-MM-DD')>='2012-01-01'
AND
to_char(t.the_date,'YYYY-MM-DD')<='2012-01-07'
)
GROUP BY "Brand Name"
ORDER BY SUM(unit_sales) DESC
limit 5
)table1

Step 3 : Components section
1) Take  pie  chart & bar chart.
2) Set all the properties like name, htmlObject (for pie take "a" as htmlObject and for bar chart "b" as htmlObject).
3) Save your dashboard and see the preview.


Sample output:

Image 1 : Pie Chart.  

 Image 2 : Perform click action on Pie you will get bar chart in place of pie chart that means you are toggling b/w two chart (i.e., Single place holder is sharing by two charts and the logic of two divs applied for CDE charts).

Download example here : Click Me

References :
1) http://jsfiddle.net/QAxgD/
2) http://stackoverflow.com/questions/18110320/toggle-between-two-divs
3) http://forums.pentaho.com/showthread.php?170449-How-To-Toaggle-Between-Chart-amp-Grid

Show pecentage of stacks along with Value of a Stacked Bar Chart in Pentaho CDE

This is a useful tip from Leo on Pentaho Forum,

For your Bar Chart set below properties.

Stacked =True
valuesVisible= True
valueMask = {value}{(value.percent)}

To popup the value of a stack or percentage of a stack you need to write below code in clickAction and make clickable is true.

function(scene) {
    var pctVar = scene.vars.value.percent;
    
    alert(pctVar.label);
}

 

Sample output Tested :

Query output should be like this for a stacked bar chart : 

Refer below link for more information : 

http://forums.pentaho.com/showthread.php?170389-Show-values-as-percentage-on-stacked-bar-chart

http://jsfiddle.net/duarteleao/e2Qfd/

Any chart responsive code in PreExecution in Pentaho CDE

function f(){
var myself = this;
  // Set initial width to match the placeholder
  myself.chartDefinition.width = myself.placeholder().width();

  // Attach the resize handler only on the first execution of the chart component
  if (!this.resizeHandlerAttached){

    // Ensure render is only triggered after resize events have stopped
    var debouncedResize = _.debounce(function(){

      // Show chart again.
      myself.placeholder().children().css('visibility','visible');

      // Change chart width
      myself.chartDefinition.width = myself.placeholder().width();
      myself.render( myself.query.lastResults() );
    }, 200);

    // Attach resize handler
    $(window).resize(function(){

      // Only trigger resize if the container has changed width
      if ( myself.chartDefinition.width != myself.placeholder().width()){

        // Temporarily hide chart so that overflow does not happen
        myself.placeholder().children().css('visibility','hidden');

        // Trigger the resize with debounce
        debouncedResize();
      }    
    });

    this.resizeHandlerAttached = true;
  }
  
}

Dynamic Dashboard Example using MongoDB - Parameterised Dashboard Example in pentaho CDE with mongoDB

This post will cover below topics

MongoDB:

1. Creating mongoDB collection from flat file data.
2. Test the Collection Content

PDI(Kettle)

3. Querying the mongoDB collection in Kettle to fetch the data.
   ( Designing Transformation to get the required result set).

Pentaho CDE:

4. Creating a dynamic Pie Chart using in CDE with Kettle transformations as data sources.

Software Ready :

1) MongoDB 2.6 CE
2) PDI (Kettle) - 5.0.1- stable CE
3) Pentaho BA Server - 5.0.01- stable  CE
4) C-Tools -14.07.29 - Bootstrap supported .
5) Browser - Google Chrome / Mozilla Firefox

Example : 

Show the top counts of NextURL field for the given URL's.
count, NextURL and URL are fields in the collection.

Categories : NextURL, Measure: count and parameter is : URL.


I've written basic of this article in my previous post which is a static report. Click here for the similar article without parameters.

MongoDB:

1) Download and install mongo DB : Click here

2) Create a Collection called "PageSuccession"  in Demo Database : Click here for collection creation with mongoDB
3) Test the collection whether the content exists or not. 
  >db.PageSuccession.find().pretty() 

PDI(Kettle)

Write the transformation in Kettle as shown in below figure. 

 Let's say the transformation name is : DashboardWithMongoDBParameters.ktr

This transformation will give you the below result set as per the example requirement.

(Top 10 Next URL's with count and url as parameter in transformation).

NextURL    Count
/demo    26205
/feeds/press    19601
/home    7369
/download    6419
/products    5164
/product/product2    4098
/product/product3    4047
/product/product4    3607
/product/product6    2759
/product/product1    2270

NOTE : default parameter is given to get the above result set. (Default parameter value is : --firstpage--)

At the time of job/step execution, if we give different value for parameter we will get data related to that parameter..

Let us say our parameter(Described in next steps how to create) is "param_url" and now the value given is : /about and the output will be differ.

NextURL    Count
/about    593
/team    111
/contact    108
/about/customers    55
/product/product2    47
/product/product12    35
/product/product3    33
/news    32
/products    26
/download    24

Quick Image for better understanding:

 MongoDB Input:

Configure Connection :  Host : localhost or give the ip address of mongoDB installed  

                                            machine. 27017 is the port number for mongoDB.

 Query                            :  { url : "${param_url}" } where param_url is the parameter defined.

Fields                              :  check the single output jSON field. 

JSON input:

Always use Json input with mongoDB input. B'z we write json expressions on mongoDB documents. Direct way of mongoDB querying is not allowed.

Sample Rows: This will limit the number of rows from the previous step. It'll take range values. For example : 1..10 or 1..20

Click the get fields

Creating parameter for transformation

1) Double click on the canvas to get the transformation properties. 

2) Click on Parameters Tab and define a parameter called "param_url" with --firstpage-- as it's default value. 

3) On run time you can give different value for param_url parameter.

4) Value of the parameter will be replaced in the Query written in MongoDB input and gives you the required result set. 

Pentaho CDE

 Now let's jump into Dashboard creation  with Pentaho CDE.

1) Prepare layout to keep parameters and pie chart. 

2) Data sources section.

Parameter is url field values from collection. So to feed the selection we again need to write a simple transformation which fetch the field values of url.

Sample transformation can be designed as shown below figure to get only url field.

Let's say the transformation name is : DashboardWithMongoDBParameters2.ktr

The above transformation will give the below output( part of the output is shown in next lines).

URL
--firstpage--
/about
/about/awards
/about/customers
/ad/easy
/ad/lead
/ad/save
/ad/survey
/ad/training
/analyst_perspective
/buy
/careers
/careers/account
/careers/capetown
/careers/engineer
/careers/frankfurt
/careers/london
/careers/manager
/careers/munich
/careers/paris
/careers/sales
/careers/syndey
/confirmed/consulting
/confirmed/contact
/confirmed/demo
/confirmed/sales
/confirmed/thankyou
/contact
/customer
/demo
/docs
/docs/doc1.pdf
/docs/doc10.pdf
/docs/doc11.pdf
/docs/doc12.pdf

crate a simple parameter in CDE and name it as param_url and also create a selection and give parameter & listener as param_url for it and give place holder for it (i.e., html Object).

Go to Data sources  in CDE and create two Kettel Queries 

one for getting result to plot the data on pie chart 

another to feed the input selection. 

For the pie chart query give 

locate the transformation uploaded to the folder in the server and then 

Variables : param_url , param_url as arg and value.

Parameters : param_url & param_url as arg and value. 

Kettle Step Name : Sort rows 2 (the step which gives the result set for chart). 

For input query give : locate the transformation file uploaded in folder and give the step name ( for this : Select values)

Check the output of queries using CDA editor.. 

Save the dashboard and preview it..

Preview 1 : With --firstpage-- input value for URL

Preview 2 : With /carres/paris input value for URL

Download the example : Click Me..!!!

Readers of this post encouraged to add your suggestions , feed back & additions to this post. drop your comments 

Sadakar

BI developer 

 

 

Kettle : Basics : Parameters in Pentaho Kettle Transformation - Simple Example

This is a list of References on Parameters usage in Pentaho Data Integration(PDI).

Reference:

http://wiki.pentaho.com/display/EAI/Named+Parameters

http://pentaho4u.wordpress.com/2012/08/20/pentaho-data-integration-using-parameters-in-transformations/


http://type-exit.org/adventures-with-open-source-bi/2010/07/using-named-parameters-in-kettle/


http://www.prashantraju.com/2010/06/using-variables-with-table-input-steps/


Will update with an example asap.


:-)

BarChart Example in Pentaho CDE using mongoDB(NoSQL database)

This articles is for beginners in MongoDB+Pentaho Kettle+Pentaho CDE.

Aim of this post is : 
Creating a Bar Chart using MongoDB in Pentaho CDE (A static bar chart, not parametrized) 

• There is no direct way to connect to mongoDB in pentaho CDE.

• Using “Kettle Queries” option in pentaho CDE we can get required result sets for visualization.

Let's assume you have a collection called “PageSuccessions” in “Demo” database in mongoDB server.

You can refer below articles from pentaho WIKI to Write, Read, creating Reports in PRD using mongoDB.

http://wiki.pentaho.com/display/BAD/MongoDB

Let's assume you have below documents in “PageSuccessions” collection.

MongoDB Preparation for this example is from :
http://wiki.pentaho.com/display/BAD/Write+Data+To+MongoDB

In mongo shell let's have a look at what is there in “PageSuccessions” by giving below command.

> db.PageSuccessions.find().pretty();
{
    "_id" : ObjectId("5404609c5ae882923576f854"),
    "key" : "--firstpage--~^~/about",
    "url" : "--firstpage--",
    "nextUrl" : "/about",
    "Count" : NumberLong(504)
}
{
    "_id" : ObjectId("5404609c5ae882923576f855"),
    "key" : "--firstpage--~^~/about/awards",
    "url" : "--firstpage--",
    "nextUrl" : "/about/awards",
    "Count" : NumberLong(80)
}
{
    "_id" : ObjectId("5404609c5ae882923576f856"),
    "key" : "--firstpage--~^~/about/customers",
    "url" : "--firstpage--",
    "nextUrl" : "/about/customers",
    "Count" : NumberLong(667)
}


Creating Bar Chart – Top 10 url pages

Getting top 10 documents from Kettle.

As shown in figure-1 in you need to write kettle transformation to fetch the top 10 urls.

• As of 5.0.1 Pentaho kettle release, we can not directly query on mongoDB.
• We'll take JSON step to get the fields. 
• MongoDB input component query area supports only JSON query expressions which you can find at http://wiki.pentaho.com/display/EAI/MongoDB+Input
• From the ETL design at final step you should get the result set as shown below.

Download the ktr file and try to open each step.

URL            Count  
/demo            114747  
--firstpage--            108143  
/download            21583  
/feeds/press            15378  
/products            14686  
/product/product3            14380  
/partners/resell            14316  
/download/download3.zip    13939  
/product/product4            13334  
/buy            10293

Creating Dashboard in Pentaho CDE( A simple bar chart using above query result set)

1) Prepare your layout in bootstrap mode of Dashboard. (Find it in settings).
2) Take a BarChaart component from the Components section.
3) In Data sources section take “KETTLE Queries”.(Upload the ktr file to your dashboard folder using Pentaho User Console).
4) In “Kettle Tran formation file” property locate the uploated ktr file.(for eg: DashboardWithMongoDB.ktr).
5) Give the step name which is giving the top 10 rows(for example : Select values).
6) Check the output of this query usign CDA editor.
7) Go back to the Bar chart and set height, width, data source (the name of data source created in above steps.. for ex: query1 is the name of the kettle data source).

8) Save the dashboard & Preview it. Sample output is shown in figure-2.

IMP NOTE : As of 5.0.1 pentaho release JSON query expressions will give you the result sets  from monogoDB. (Which you can find at mongoDB Input step from BigData Node).



Transformation Sample Design Image:

Bar Chart Sample output Image :

Download Transformation : Click-1
Download Sample Bar Char : Click-2

References : 

1) http://wiki.pentaho.com/display/BAD/MongoDB
2)  http://wiki.pentaho.com/display/EAI/MongoDB+Input#MongoDBInput-queryexamples

Next post : Parametrized dashboard using MongoDB.


Sadakar Pochampalli
:-) 

My first learning example in Kettle - An example is reproduced from Pentaho Tutorials Topic : Filtering Rows

Hi Guys,

Here is my first learning experience with Kettle Community ETL.

Download Kettle from :  Click Me

Sources to get start with Kettle :

1) http://wiki.pentaho.com/display/EAI/Getting+Started
2) http://localhost:8080/pentaho/docs/InformationMap.html
3) http://wiki.pentaho.com/display/EAI/Pentaho+Data+Integration+%28Kettle%29+Tutorial

You should have installed java in your local machine and path, java_home, jre_home set for it.

Below topic is a re-production of Kettle Transformation & Job example from Pentaho Tutorials and the description is slightly differ from the actual description.

Let's look at simple basics and then we'll jump into 1st example.


#  What is Transformation in Kettle ?
# What are jobs in Kettle ?
# Core difference b/w Transformation & Jobs in Kettle ? 
# Extensions for transformations & jobs in Kettle ?
# What are steps & hops ? 

1) What is the usage of Transformation ?

    Transformations are used to describe the data flows for ETL such as reading from a source, transforming data and
    loading it into a target location.

2) Jobs

Jobs are used to coordinate ETL activities such as

Defining the flow and dependencies for what order transformations should be run
Preparing for execution by checking conditions such as, "Is my source file available?," or "Does a table exist?"
Performing bulk load database operations
File Management such as posting or retrieving files using FTP, copying files and deleting files
Sending success or failure notifications through email


3)What's the difference between transformations and jobs?

Transformations are about moving and transforming rows from source to target. Jobs are more about high level flow control: executing transformations, sending mails on failure, ftp'ing files

4) Extensions for transformations & jobs in Kettle ?
Transformation : .ktr
Jobs : .kjb

5) What are steps & hops ?

• A transformation is a network of logical tasks called steps.
• Transformations are essentially data flows.
• The transformation is, in essence, a directed graph of a logical set of data transformation configurations.

• Steps are the building blocks of a transformation, 
• for example a text file input or a table output. 
• There are over 140 steps available in Pentaho Data Integration and they are grouped according to function; for example, input, output, scripting, and so on. 
• Each step in a transformation is designed to perform a specific task, such as reading data from a flat file, filtering rows, and logging to a database 
•  Steps can be configured to perform the tasks you require. 
•  
• Hops are data pathways that connect steps together and allow schema metadata to pass from one step to another. 
•  Hops determine the flow of data through the steps not necessarily the sequence in which they run.
•  When you run a transformation, each step starts up in its own thread and pushes and passes data. 

More info  at :  Click Me

TOPIC :
Load sales data into a database. Several of the customer records are missing postal codes(zip codes) that must be resolved before loading into the database.

You will be given two .csv files to load the data into database. 
The first file is : sales_data.csv and later one is Zipssortedbycitystate.csv

( Location of the files : /home/sada/softwares installed/Pentaho/data-integration/samples/transformations/files
)


In short , there is a column called "postalcode"  in sales_data.csv with missing postal codes(i.e., the values are null) and need to fill the missed the missed postal codes using "postalcode"  column of Zipsortedbycitystate.csv file. 


Explanation : 
The Final Transformation looks as shown in below :

The final job looks as shown in below(doing job is optional for this topic)

Transformation is divided into below sub tasks.
1) Retrieve Data From Flat File ( i.e., Retrieve Data From "sales_data.csv")
2) Filtering the records ( from the source file get only not null valued rows for "postal code").
3) Load to into Relational database ( Total Number of rows in this 2747)
4) Retrieve Data From your look-up file.
5) Resolve Missing zip information.


Download the example : Click Me

NOTE : While running the .ktr file (or .kjb file) in your environment you should specify the flat files(.csv files) location as per your folder structure.

#:  I've taken postgresql as output table.

Thank you.

Sadakar
BI developer.
(Pentaho/Jasper/Talend/Kettle).

:-)

OLTP Vs OLAP , differences

Best source I found for quicker understanding..

http://datawarehouse4u.info/OLTP-vs-OLAP.html

:-)

RICH Bar Chart ( Stacked Bar + Moving Average Trend Lines + Line Chart) Example in Pentaho CDE - Submitt Button Component Explained on CDE dashboard

Hi Guys,

Trend Lines: WIKI
# A trend line is formed when a diagonal line can be drawn between two or more price pivot points.

# They are commonly used to judge entry and exit investment timing when trading securities.It can also be referred to as  a dutch line as it was first used in Holland.

# More info at : http://en.wikipedia.org/wiki/Trend_line_%28technical_analysis%29

I have tested 3 kinds of trend lines support with Community Dashboard Editor.They are
i) linear :
ii) moving-average and
iii) weighted-moving-average.

For more info about types refere this : http://www.ehow.com/list_7255661_types-trend-lines.html

Example Developed on :
C-Tools of 14.06.18 version, foodmart database of postgresql(A jasper server sample database which follows star schema model).

Focused on Core part of the Example in this post.


Sample Query:

SELECT
    to_char(t.the_date,'mm-DD-YYYY') Date,
    SUM(sf7.unit_sales) UnitSales,
    SUM(sf7.store_sales) StoreSales,
    SUM(sf7.store_cost) StoreCost
FROM time_by_day t INNER JOIN sales_fact_1997 sf7 ON t.time_id=sf7.time_id
WHERE
    to_char(t.the_date,'YYYY-MM-DD')>='2012-01-01'
    AND
    to_char(t.the_date,'YYYY-MM-DD')<='2012-01-07'
GROUP BY t.the_date
ORDER BY t.the_date


Sample output:

date           unitsales    storesales    storecost
01-01-2012    348.0000    706.3400    280.4990
01-02-2012    635.0000    1304.5300    525.8396
01-03-2012    589.0000    1294.1200    515.2609
01-04-2012    20.0000            42.8700            17.6873
01-05-2012    966.0000    1987.1900    809.6743
01-06-2012    993.0000    2162.3400    864.9502
01-07-2012    1265.0000    2696.6100    1078.2984

Note that column indexes start from 0 and ends with n-1 i.e., here it takes 0,1,2,3
Converting above resultsetinto below points on the chart(RICH BAR CHART)
X-axis : data values
Left Side Y-axis : Stacked bars with UnitSales & StoreCost
Right Side Y-axis : StoreSales Line Chart.
Moving average Trend lines for : Left Side Y-axis measures(i.e., to UnitSales & StoreCost).

On the chart component you need to give below properties.

For Line Chart :
Plot2 : True
Plot2ColorAxis:2
Plot2Series:storesales  (making 3rd column as line chart - i.e, 2nd indexed column)
PlotFrameVisible: false

For Stacked :
stacked :true

For TrendLines of unitsales & storecost:
trendType: moving-average
NOTE : other trendTypes are linear  and weighted-moving-average


(NOTE :
1) I've focused on major properties in this post.. download the example and debugg for other properties set. for instance, extension points and colors.
2) Also, I have not discussed about parameterisation of this example in this post.. you can find how from_date & to_date implemented on example by downloading.
)


How submit button works ?

NOTES:
1. For the 1st time when dashboard loads, all the components on the dashboard should take default values.

2. Next time, when you select other inputs on the dashobard, the components have to stop it's loading for each of the input selection.(the blinking).

3. After selecting new inputs & after clicking "Submit" button only you the components on the dashboard should load.

How one can accomplish this on CDE Dashboards....
1) Let's assume you have two date parameters (of Generic -> Date Parameters).
    param1_FromDate & param2_ToDate

2) Let's say the corresponding Date Picker Selects (of selects -> Date input components).
    select1_FromDate & select2_ToDate

3) Usually we set listeners for these Date selects.. Do not do this (i.e., don't make your Date selects to listen the param1_FromDate & param2_ToDate parameters). But give the Parameters to the Date selects.

4) Let us take two more simple parameters .. Let's say param1 & param2 ( Generic -> Simple parameter).
  
5) Now on your chart component
   Give parameters as : param1_FromDate & param2_ToDate
   Give Listenrs as : param1 and param2

6) Take a button component from Others of Components section and give place holder for it(i.e., htmlObject to keep this button).
   and give Label name as : Submit

7) Write below code in the Expression area of button component.  
   
  Code:
  function f(){
   
    Dashboards.fireChange('param1',param1_FromDate);
    Dashboards.fireChange('param2',param2_ToDate);
   
    alert("param1_FromDate="+param1_FromDate);
    alert("param1_ToDate="+param2_ToDate);
    alert("param1="+param1)
    alert("param2="+param2)
   
 
  }

8) Alerts for testing purpose. In the above code capturing the acual date parameters values into general parameters.

9) 3 & 5 points after this code will work (i.e., when previewing the dashboard)..

Download a sample Example here :

Click Me

Sample output

Sadakar Pochampalli
BI developer.

Navigation Menu Component Explained in Pentaho CDE

Hi Guys,


The below points explains you how Navigation Menu Component is useful for Dashboards.

NOTES from Plugins Documentation :

1) Navigation Menu component generate a menu.
2) It allows the user to navigate through the solution folders.
3) When a template is not present in the selected folder, the default-dashboard-template is presented. (see template documentation)
4) The menu items are build dynamically using the index.properties present in each solution folders.
5) To hide undesired folders edit index.properties and set the property "visible" to false.
Options


All the time client may not wish to go back to the Repository for dashboard solutions & check out.. instead this component makes him/her job easy. 

How to implement ?

1) Layout section : Row ->Column(Col1[htmlObject]).
2) Components sections : Others-> Navigation Menu Component.
3) Give the basic properties to it.

Name : NavigationMenuComponent
htmlObject : Col1


Sample output:

Observations :
1) This output is with blueprint mode of dashobard.
2) Observed a small issue with bluepint mode. i.e., there is slight less width each folder name.


:-)

Dual Level Pie Chart in Pentaho CDE - Example Explained

Hi Guys,

This post will tech you how to create Dual level pie chart.

Example developed Environment :

1) C-Tools of 14.07.29 version on 5.1.0 Server.
2) Foodmart database of Postgresql(A jasper server example database).

Step 1 : LayOut section

1) Row ->Column("Col1" is the htmlObject-> Bootstrap Panel
2)
Bootstrap Panel:
Name : Panel2, Corners: Simple & Panel Style : Primary
Panel Header :
Name: Panel2_Header, Corners: Simple & Text Align: Center
Add html : HTML: <b>Dual Level Pie Chart Example in Pentaho CDE</b> , Font size: 18
Panel Footer : Remove it.

Step 2 : Data sources section
1) Give all the properties
Name : query2
Driver : org.postgresql.Driver
UserName/Password: postgres/postgres
URL : jdbc:postgresql://localhost:5432/foodmart
Query :
SELECT
        distinct
                gender,
                member_card AS card,
                sum(total_children) AS TotalChildern,
                sum(num_children_at_home) AS childernathome
FROm customer
group by gender,member_card
order by gender,member_card

Query Sample Output:
gender    card    totalchildern    childernathome
F    Bronze    6626        1278
F    Golden    2012        1529
F    Normal    3025        1048
F    Silver    1196        377
M    Bronze    6570        1288
M    Golden    2029        1511
M    Normal    3062        945
M    Silver    1210        399

3) Components Section
1) From Charts ->Select Pie Chart
2) Give all the required properties
Few of the major properties are:
Name : DualPieChart2
Title: Total Children Vs Children At Home
Datasource : query2
Colors : #5F9EA0,#6495ED,#006400,#483D8B
Html Object : Panel2_Body
PreExecution:

function f() {
    $.extend(this.chartDefinition, {
       
     // Data source
    crosstabMode: false,
    readers: ['gender, card, childernathome, TotalChildern'],

    // Data
    dimensions: {
        // Dimension bound to "dataPart" is hidden by default
        gender: {isHidden: false},
        // Sort brands
        card:  {comparer: def.ascending},
        // Notice the currency sign and the /1000 scale factor (the comma beside the dot).
        //sales:  {valueType: Number, format: "¤#,0,.0K"}
    },

    // Visual Roles
    visualRoles: {
        // Chart
        dataPart: 'gender',

        // Main pLot
        value:    'TotalChildern',
        category: 'card'
    },

    // Plots
    plots: [
        {
            // Main plot (outer)
            name: 'main',
            dataPart: 'F',
            valuesLabelStyle: 'inside',
            valuesOptimizeLegibility: true,
            slice_innerRadiusEx: '60%',
            slice_strokeStyle:   'white'
        },
        {
            // Second plot (inner)
            name: 'inner',
            type: 'pie',
            dataPart: 'M',
            valuesLabelStyle: 'inside',
            valuesOptimizeLegibility: true,
            slice_strokeStyle: 'white',
            slice_outerRadius: function() {
                return 0.5 * this.delegate(); // 50%
            }
        }    
         
         
        ]
    });
}
Save your dashobard and preview it in New Window.

Core Part :
Explained at this thread in forum :
http://forums.pentaho.com/showthread.php?167447-CCC-Charts-Outer-Ring-to-donut-pie-%28Inner-ring-to-donut-pie%29

NOTES :
1) For Dual level pie chart your query result set should be categorised as shown in sample result set.
2) In the PreExecution code, in plots sections you are dividing it to two plots.
3) In the same way, we can also implement tri level pie charts, double dual level pie charts by dividing the plots(Better option
is going with Sunbrust chart in this case).


Reference :
http://www.webdetails.pt/ctools/ccc.html


Why to wait ??? Download Example here : Click Me (Note that to run all my blog examples you should have foodmart database running on postgresql server).




Sample output 1:

Sample output 2:  

Mobile Output Sample :

:-) For demo's on free open source CDE dashboards contact me at my mail ID:-)