Showing posts with label pentaho. Show all posts
Showing posts with label pentaho. Show all posts
Monday, February 16, 2015
Drill down from one dashboard to another dashboard custom parameter example in pentaho CDE
This post teach you how to drill down from one dashboard to another dashboard by passing parameters.
Example developed on :
1) BA server - 4.8 version
2) C-Tools 13.09 version
3) Web browser : Mozilla firefox(Always preferable for pentaho C-Tools)
4) Database : foodmart (postgreSQL - jasper server default db).
Before learning this post I suggest you to have a glance at the previous post on the same drill down with out any parameters concept.
http://pentaho-bi-suite.blogspot.in/2013/11/drill-down-from-one-dashboard-to.html
Aim of the concept :
1) Click on state slice in dashboard 1
2) As soon as you click on particular state slice, you will be navigating to another dashboard displaying all the details of that state in another dashboard in the form a table.
i.e, Drill down from Pie Chart to Tabular display of data by passing parameter.
Dashboard 1 :
Create your chart(s) as shown in below figure you
1) Create a Simple parameter lets say param_custom_state ( No need of giving any default value )
2) Go to chart component properties
-> Give the data source(query created in data source section) that will fit for the above chart .
-> Give the above created parameter for "Listeners" as well for "Parameters".
3) In the clickAction write below code
function sendParameter(scene){
var url=http://localhost:8085/pentaho/content/pentaho-cdf-dd/Render?solution=CDEExploring&path=%2FTest&file=test_dev_drill.wcdf;
var vars = scene.vars;
var c = vars.category.value;
var v = vars.value.value;
alert("category: " + c + "
value: " + v);
value: " + v);
window.location=url+¶m_custom_state=+c;
}
NOTE : also look at this thread for URL passing.
http://forums.pentaho.com/showthread.php?165490-Need-help-with-dashboard-drill-down-url
NOTE :
1) URL = 2nd dashboard URL
2) url+parameter(which you are passing)
3) On pie chart, slice will take the category value( here the category value is "state" and represented by c)
4) This "state" will be the custom parameter for the 2nd dashboard.
5) Give alert function whether the code is working fine or not (This is a testing methodology).
6) Note that drill down code slightly vary for other charts in pentaho CDE bz of protovis scripting changes as well as per new things in CCC2.(i.e., For bar chart drill down the given code may be differ from the code written here).
6) Note that drill down code slightly vary for other charts in pentaho CDE bz of protovis scripting changes as well as per new things in CCC2.(i.e., For bar chart drill down the given code may be differ from the code written here).
5) Save the dashboard and see the preview.. Once you click on slice itll alert saying that the selected state along with the value and then you can see the drill down to another dashboard.
Dashboard 2:
NOTE that you have to create 2nd dashboard separately and have to know how to get the URL of it.(Open In New Window)
Create a table component and suitable query for it where your query for the tabular display should take the input parameter "state" which is your custom parameter and on the table component give it as parameter also.
For instance : in where clause of your query .
where
s.store_state like ${param_custom_state}
1) Go to "Generic" section and click on custom parameter
2) Give the name for it (it is not mandatory to give the same name but preferable to give the same parameter name that you are receiving from 1st dashboard).
3) In the Java script code write below code
param_custom_state = Dashboards.getQueryParameter("param_custom_state");
Once you click on WA state in the above image , you will navigate to another dashboard which displays that particular state details. Sample image is shown below.
This is the way how you can work custom parameter for drill down functionality.
For tricks and tips navigate through my blog post and discover some thing interest in pentaho..
Add your inputs and improvements in comments :)
Same topic discussed in below posts :
1) http://pentaho-bi-suite.blogspot.in/2013/11/drill-down-from-one-dashboard-to.html
2) http://pentaho-bi-suite.blogspot.in/2013/12/pentaho-cde-dashboard-complete-example.html
Same topic discussed in below posts :
1) http://pentaho-bi-suite.blogspot.in/2013/11/drill-down-from-one-dashboard-to.html
2) http://pentaho-bi-suite.blogspot.in/2013/12/pentaho-cde-dashboard-complete-example.html
Sunday, February 15, 2015
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.
Lets look at simple basics and then well 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)Whats 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, ftping files
4) Extensions for transformations & jobs in Kettle ?
Transformation : .ktr
Jobs : .kjb
5) What are steps & hops ?
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.
#: Ive taken postgresql as output table.
Thank you.
Sadakar
BI developer.
(Pentaho/Jasper/Talend/Kettle).
:-)
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.
Lets look at simple basics and then well 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)Whats 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, ftping 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.
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.
#: Ive taken postgresql as output table.
Thank you.
Sadakar
BI developer.
(Pentaho/Jasper/Talend/Kettle).
:-)
Friday, February 13, 2015
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 Ill 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-752data-integrationsamples ransformationsStream 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 its 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 itll 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: Itll 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 : Itll 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 itll 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
:-)
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 Ill 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-752data-integrationsamples ransformationsStream 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 its 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 itll 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: Itll 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 : Itll 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 itll 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
:-)
Subscribe to:
Posts (Atom)