Cascading multi select parameters example in pentaho CDE

Hello Guys

You will learn how to do cascading with multi select components in pentaho CDE . There is another post on the same topic in this site which was a basic one.

In this you will find the implementation of 

1) Custom parameters & default values

2) Multi select input controls cascading. 

3) Fixed height & back ground color for  input controls.

    

Final Look of the cascading parameter :

One of the Layouts design for selects (Country select place holder) [Not the Country label  place]

  
Custom Parameters :
param1 for country
Default value in Javascript code  ["US","Canada"]

param2 for state
Default value in Javascript code ["BC","WA"]

param3 for city

select1 for country

    select 1 parameter & listener is : param1


select2 for state

     select2 listeners

     

    select2 parameters (at only parameter place give param2)

select3 for city

    select3 listeners
    

    select3 parameters (at only parameter place give param3)

 Query 1 : country query (SELECT DISTINCT country FROM customer)

Query2 : state query
SELECT DISTINCT state_province FROM customer WHERE country IN (${param1})
NOTE : for multiple selection parameter name should be given with in bracket i.e., with in ()

Query 3 : city query
SELECT
    DISTINCT city FROM customer
WHERE
          (
               country IN (${param1}) 
             AND
             state_province IN(${param2})
          )

NOTE : for multiple selection parameter name should be given with in bracket i.e., with in ()

OUTPUT :  Image 1

Download the example : Click Me - Cascading multi select and HTML tabs examples


:-) Sadakar :-)

* Rubber band drill down * Remove X-axis * Remove Y-axis * Pre-selection of bars * Conditional coloring and etc. :-)

Hello Every one..!!! :-)

This article is a collection of various tips from the pentaho forum and presented here as a document..

This will be useful as a POC on below topics & the best and easiest way of exploring all these is to download the example from this site and open in Edit mode in you PUC and navigating through all the properties & functions.

This example is an outcome of my R&D.

Features of this work out :

1) Remove X-Axis
2) Remove Y-Axis
3) Pre Select 'N' number of Bars
4) Base Axis(or X-axis) label font color, font size
5) Base Axis label rotation
6) Data label color for Bars
7) $ signed value(lable style) on data bar with bold font.
8) Second Y Axis(plot2) with line chart
9) Line color and value on the line is same.

10) Pre selected related information is displayed
    in pie chart as intra chart commnication when the dashboard loads

11) Rubber band based selection of intra chart drill down
   (i.e., multiple bars selection drill down). 

12) Giving multiple default values for a parameter
   (Example of custom parameter).

Image 1: Initial Loading of Dashboard

Image 2:Applying Rubber Band drill down(i.e., Selection of multiple Bars for drill down)

Image 3: After releasing rubber band check how the drill down happened

Download the Example :
Click Me

NOTE :
Queries are taken to display some data for charts.. might not related to requirement. 

Thank you :-)

Source of R&D :
Pentaho forum & experiments.

Core part of this Example is explained here : 

Main Feature : Rubber band drill down on the same dashboard (Intra chart rubber band based drill down ) OR multiple bars selection drill down on the same dashboard. 

 Scenario:
Lets say you have a bar chart and pie chart on your dashboard. 
Bar Chart : 
X-axis : Cities ( you can call cities as categories here).
Pie Chart:
Cities are categories for pie chart

i.e., Pass multiple cities from Bar chart to Pie chart by selection (i.e., select multiple bars and display the related information on pie chart with the same categories).

Step 1 : Create a custom parameter (param_city) and give default values for it. 

The way of giving default values(multiple values are input to a parameter) using custom parameter is:
In JavaScriptCode window write below

["Lebanon","Burbank","Portland"] 

NOTE : the names inside double quotes are city names

Step 2 : Rubberband selection functionality happens in 2 places and pre selection of bars happens in 1 place as given below respectively

userSelectionAction : 

function f(selectingDatums){
    var selectedCatsSet = {};
    var selectedCats = [];
    selectingDatums.forEach(function(datum) {
        var cat = datum.atoms.category.value;
        if(selectedCatsSet[cat] !== true) {
             selectedCatsSet[cat] = true;
             selectedCats.push(cat);
        }
    });

    // Return a new array, containing all datums having the same categories
    return this.chart.data.datums([{category: selectedCats}]).array();
} 

selecitonChangedAction:

function f(selectedDatums) {
  // Extract the category values of selected datums, assuming that there is only one
  // category dimension and that the name of the dimension is "category" (the default)
  var categories = selectedDatums.map(function(datum) { return datum.atoms.category.value; })
 
  Dashboards.fireChange('param_city', categories);
   
}  

renderCallback:

function f(){
    //var datums = this.chart.data.datums([{series: 'sum'}]);
    var datums = this.chart.data.datums([{category: ["Lebanon","Burbank","Portland"] }]);
    pvc.data.Data.setSelected(datums);
}
 
 Step 3 : Pie Chart & It's Query
1) Add parameter(param_city) to chart component and also give it as listener.
2) For the query of pie chart add parameter(the type here is StringArry - Not just String) and pass the parameter in SQL.
3) Note that the parameter has to catch multiple values.. i.e., your WHERE clause should be as follows WHERE IN (${param_city}) . If you ignore () parenthesis you will not find the result properly. 
 
 That's it.. you have done with multiple bar selection drill down..

:-)

Sadakar
BI developer(Jasper/Pentaho/Talend ETL)

Access Control Concept in Pentaho Schema Workbench & Saiku Analytics tool: Display/Restrict Role based Cubes, Dimensions, Hirachies & levels

It's been a long time, I have explored some concepts in Pentaho Analysis using PSW(Pentaho Schema Workbench).

Here is a small workout on giving access of CUBES, Hierarchies,members to different users based on roles assigned to them.

You may not find what you need exactly in this article but I can say  surely that,  you will get some idea on below topics


1) Creating Users & Roles in the BA Server.
2) Assigning Roles to Users.
3) How BA server will identify the roles created in PSW with the roles in BA Server.
4) Developing a Schema of 2 sample CUBES
5) Scenario on the schema


Software  Setup:
Pentaho BA Server 5.x
PostgreSQL Server 9.x [Food Mart Database]
Pentaho Schema Workbench :3.8.0.0-209
Saiku Analytics : 2.6-PENTAHO5-SNAPSHOT (TRUNK)


Assuming you have initial idea on how to design a sample CUBE..
(For creating sample cubes you can follow this tutorial :
http://pentaho-bi-suite.blogspot.in/2013/12/pentaho-schema-workbench-simple-example.html
)

 Concept : Access Control
Problem Statement : 
1) Give Access of CUBE 1 to User 1 who is assigned with Role 1
       Schema grant, Cube grant, Hierarchy grant, member grant .
2) Give Access of CUBE 2 to User 2 who is assigned with Role 2


Step 1 :  How to built role mapper communication in BA Server for created roles in mondrian ?

• Enable mondrian user/role mapper in pentahoObjects.spring.xml 
• File location : /Pentaho/biserver-ce/pentaho-solutions/system

i.e., Uncomment   below code :

  <!--  This mondrian user/role mapper assumes that roles from the platform also exist in mondrian -->

  <!--
  Disabled by default in 3.5.2. In trunk, this should be enabled.
    -->
   
  <bean id="Mondrian-UserRoleMapper"
        name="Mondrian-One-To-One-UserRoleMapper"
        class="org.pentaho.platform.plugin.action.mondrian.mapper.MondrianOneToOneUserRoleListMapper"
        scope="singleton" />
 
NOTE : If your BA server is already running , just stop and restart it.

That's it.. Now the Roles assigned in schema will mapped to the roles assigned in BA server.(for users).



Step 2 :  Creating Schema with 2 CUBES
Let us take food-mart database(modelled for multidimensional database) and design below CUBES.

CUBE 1 ---> Sales
CUBE 2----> HR

Cube Design Image is shown below :

 

Download the schema file here :  Click Me
Open it with your PSW...

The design has the following concepts :
 i) Dimension Usage  : 
Create a dimension outside the CUBES( i.e., Right Click Schema -> Create Dimension) and make use of it in CUBES as Dimension Usage.

(i.e, If 2 CUBES use the same dimension, we need not to create it 2 times. We can create one time and use that dimension which ever the CUBE needs for it's specific purpose).

In this example  schema :  I made use of two shared dimensions as Dimension Usage. Those are  Store and Time

ii) Calculated Member(CM).
To calculate the measures.. for example : To find profit in this example.
It will results as a new measure in the output.


Step 3 : How to create Users  & Roles?  
Users :  

• Log-in into your server with Admin/password  first(login as Administrator).
• In BA Server Click on "Administration" from the dropdown Home menu. 
• Create 2 Users as shown in below image.

  

Roles:  
Also create 2 Roles in Manage Roles Tab
Sales User  and
HR User   as shown in below image.

Step 4 : Assign roles to Users
As shown in below image
i) HR user is assigned with "HR user" role.
ii) Sales user is assigned with "Sales user" role.

Scenario : 
CASE1) Give Access to CUBE 1 to User 1 who is assigned with Role1
    i) Schema grant
    ii) CUBE grant
    iii) Hierarchy grant  

Notes : 

• Role Name in PSW should match with the role name in BA Server. 
• Role Creation: In PSW "Sales User" role is created for Sales CUBE.
• Cube Grant: Right click on " Sales User" role and select "Add Cube Grant", give attributes : access =all , Cube=Sales
• Hierarchy Grant: Find below image.

•  Member Grant: access=all , member=[Store].[USA].[CA]
• Another   Member Grant: access=none , member=[Store].[USA].[CA].[Los Angeles]

• USA country in current example is having 3 states, CA,WA,OR.
• Out of these 3 states the access is given to only CA state.
• The cities other othn "Los Angeles" will be displayed with the other member grant. 

Output:
1) login to the BA Server as "Sales" user.
2) New -> Saiku Analytics

CASE2) Give Access of CUBE 2 to User 2 who is assigned with Role2
   
As a demonstration just given CUBE Grant to "HR User".

"HR User" is the role name given in PSW as well role created in BA server.
For this role the user is "HR" in this example. 

References :
1) http://mondrian.pentaho.com/documentation/schema.php#Defining_roles
 
2) http://infocenter.pentaho.com/help/index.jsp?topic=%2Fanalysis_guide%2Ftopic_mondrian_security.html


:-)

Sadakar
BI developer

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