Hello
Guys..
Scenario
:
If you
receive excel sheet or csv file as data source then how to create dashboard
charts or tables in pentaho CDE ?
Like
jasper here we do not have any direct way to connect excel sheet as a data
source. Here in Pentaho suite we need to make use of Kettle(PDI) to work out
with Excel or CSV data sources.
Current
post is an example of using Excel data source for Pentaho CDE dashboard.
This post
is not using any parameters(Need to work out with parameters and update this
blog post).
Aim of
the Example:
Creating
dashboard charts using Excel Datasheet
Environment
:
1)
PDI(Kettlle)- 4.4 stable
2)
Pentaho BA server - 4.8 stable
3) Excel
data source(with .xls extension)
4)
Pentaho C-Tools(13.09 version)
5) Web
browser(Mozilla is preferable)
Example
:
Display names on X-axis and summation of ranks on Y-axis on Pentaho CDE chart...
Data display should be grouped by name .
Data display should be grouped by name .
NOTE:
Save your kettle file in the folder where you also saving the CDE files.
i.e., Generally in 4.8 BA server
under pentaho-solutions/your project(dashboard) folder. B'z you need to pick the developed .ktr file from data sources section.
Steps:1 Working with PDI for Excel Data
1) Drag an drop Excel Input step.
Design-> Input -> Microsoft Excel Input ->
2) Double click on Microsoft Excel Input step and Browse for your excel file and then Add.
3) Drag and drop "Group By" step from Design->Statistics
Double click on it and select Group field as name
In the aggregates select as below
Name Subject Type Value
name rank Sum
4) Connect the two Steps( i.e., connect "Microsoft Excel Input" and "Group By" with an Hop.
5) See the preview (Use Debug this transformation).
6) Sort the output that you got from step 5.
Drag and drop "Sort rows" step from Design ->Transform.
connect "Group By" step to "Sort rows" step using a hop.
7) After sorting add another step say "Group By 2" to your transformation and now see the preview of the transformation.
Kettle Design Image:
Down load source code of this example here
References :
1) Drag an drop Excel Input step.
Design-> Input -> Microsoft Excel Input ->
2) Double click on Microsoft Excel Input step and Browse for your excel file and then Add.
3) Drag and drop "Group By" step from Design->Statistics
Double click on it and select Group field as name
In the aggregates select as below
Name Subject Type Value
name rank Sum
4) Connect the two Steps( i.e., connect "Microsoft Excel Input" and "Group By" with an Hop.
5) See the preview (Use Debug this transformation).
6) Sort the output that you got from step 5.
Drag and drop "Sort rows" step from Design ->Transform.
connect "Group By" step to "Sort rows" step using a hop.
7) After sorting add another step say "Group By 2" to your transformation and now see the preview of the transformation.
Kettle Design Image:
Step 2: Kettle Transformation as Data source in pentaho CDE
1) Layout section :
Design and develop your dashboard as per your requirement.
2) Components section:
Take a bar chart and give place holder for that using html object
3) Data sources section:
i) Select "KETTLE Queries" and then click on "kettle over kettle TransFromFile"
ii) Give name and locate your .ktr file and then write the step name from where you are getting output in your transformation.. In this example step name is : Group by 2 (note that white spaces are there in name)
iii) Give the name of this data source in component section for your chart data source.
That's it. you have done with working with Kettle as data source for pentaho CDE dashboarding.
Sample output of this example
Down load source code of this example here
References :
1) http://codeissue.com/articles/a04f273a99579d2/
Pedro's blog post:
2) http://pedroalves-bi.blogspot.in/2011/07/elasticsearch-kettle-and-ctools.html
Pedro's blog post:
2) http://pedroalves-bi.blogspot.in/2011/07/elasticsearch-kettle-and-ctools.html
Buenas, podrias decirme cuales serian los datos que van en los dos group by?
ReplyDeleteComo faço para exportar o csv gerado usando o CDE?
ReplyDelete