Date Input Controls/ Components in Pentaho CDE with a working example

Hello guys…!!!

In this post you will learn about how to create date input controls in CDE.
The dashboard images which I am going to show below are having lots of .css code. I’m not covering all the things but concentrating only on Date input controls in CDE.

My Environment :
BI Server: Pentaho 4.5
CTools: Not found the version numbers of  C-Tools(Unable to check them)– perhaps..! lesser than 13
Database : hsqldb (Comes along with Pentaho installation — A java based small db for servers like pentaho)
NOTE: Images are taken in this post after creating the dashboard, please find the yellow rectangular boxes to understand.

You need to work out @ 3 places in “Components” section.
1. Generic
2. Selects
3. Charts
Explained from step 1 to step 3
You need to work out  @ 1 places in “Data Sources” section. i.e, all in “Properties”
I’m not concentrating on Layout part for this example. So wherever the HtmlObject you find in this example replace them with your working HtmlObjects.

Step 1: Creating parameters
* Design your dashboard as per your requirement ( In the images you can find the sample designs).
 In the “Components” section:
1. Click on components
2. Click on Generic
3. Click on Date parameter
4. Go to properties give the name
eg: param_start_date
5. Click + symbol  per another date parameter
6. Go to properties give the name
eg: param_end_date

Find the  image

Step 2: Creating Date input Components
In the “Components” section
1. Click on Components
2. Click on Selects
3. Click on “Date input Component”
4. Go to Properties give name. eg: start_date in  my example
5. In the properties give HtmlObject. eg: start_date_select
* HtmlObject is the name of the column where you are putting
your dateinput control.
6. Give  Parameter.[It will appear once you try to type]
eg: In step 1, created parameters. So here give “param_start_date”
7. Give Listeners[click on it, you will find the list of Listeners. Click OK]
8. Repeat 3-7 for “end_date” Date input
component.

Step 3:
Find the steps in image.

 
HtmlObject is the place where you are putting your chart.

Step 4: Applying parameters for Chart & Query
            In the “DataSources”
Find the steps in the image
Find the sub sequent image for  Parameters applying for chart & Query.

do same as shown in images.

Step 5:
Save the dashboard and then Click on “Preview” button.
See the images below for sample out put with date input controls.
Preview of the dashboard with out any input controls selection

Preview of the dashboard after selecting date input controls.. find the images below one by one.

Find the image for observing whether the chart becomes dynamic or not with different date input controls.

That’s it.. You are done with date input controls on dashboard.
Thank you for reading this small post.

Sadakar
[email protected]
Helical IT Solutions Pvt. Ltd,
Hyderabad.

Pentaho Community Dashboard (CDE) Basics – Creating a Simple Dashboard

Pentaho Community Dashboard Editor (CDE)
CTools Website – Community Tools

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

What is CDE ?
* CDE is one of the plugins for Pentaho BI Server, contributed and maintained by     Pentaho Partner webdetails.
* We create dashboards using this tool.
* Community Dashboard Editor (CDE) was born to simplify the creation, edition and      rendering processes of the CTools Dashboards.
* CDE is a very powerful and complete tool, combining front end with data sources and custom components in a seamless way.

Where you can get the plug-in in Pentaho Community Server ?
How to download the plug-in & what are the dependencies?

You can find the symbol “Pentaho Market place” on the menu bar..
Click on it you can see the Pentaho Marketplace editor as shown in below figure.
Find Community Dashboard Editor and Click on install.
As I have already installed it is showing as Up to Date.
Its generally a good practice to keep on updating the plugins with the latest version available in the market.
NOTE:
You must install the dependent plug-ins before you start working with CDE.

The dependencies for CDE are:
CDF(Community Dashboard Framework)
CDA(Community Data Access)
You must restart your server to take the effect of installation.

Where you can find the sample examples after installing?
* In the left side panel you can find plugin-samples folder.
* Expand it and then click on CDE
* In the Files you can find sample example CDE Sample Dashboard.
* Right Click on CDE_Sample Dashboard àThen click on Edit.
CDE has 3 major components
They are.
* Layout
* Componets
* Data Sources.CDE has developed based on MVC-2 architecture of Advanced Java Technologies.
* Layout – View
* Components – Controller
* Data sources – Model.
Find the images below to understanding the basic of CDE.

* Scroll down the dashboard. Find “About” on the left side and find the version number of CDE installed.

* Close the version window and Just click on Preview. I’ll let you know how to work out with all the components in CDE with a good working example.
* Now, just click on Preview. You can find the output of the dashboard as shown in below figures.

* Scroll down.

* Read the points that were in boxes. You will get the basic idea of CDE dash boarding.
Example:
Aim : To design a simple chart(on dashboard)
Environment:
* Pentaho BI Server community 4.8 stable version.
* CDA,CDF & CDE installed in the server from Market place.
Database: SampleData (It comes along with pentaho installation.. for this artical I’m not connecting to any external database.)
* This example was based upon CCC-Version1. The current Example which I’m going to explain is based upon CCC2-Version2.
* There are a lot of changes made from CCC-V1 to CCC-V2.
* Make sure your environment of CDE supports CCC-V2 for the example.

Step 1: Creating New CDE Dashboard
There are two ways to create a new CDE dashboard.
i) From the menu bar
ii) By clicking CDE icon
 Save your dashboard
 Click on Save -> Save your dashboard in your fav folder with your fav name.
 

After saving your dashboard  “Refresh your Repository” and then you can find the file as shown below figure.

Step 2:  Working with Layout, Componets and Data sources.
CDE is mainly works on scripting such as css, javascript.
Click on + sign.. (We are going to add Cascading Style Sheet code(css) code to our dashboard).
Resource type : css
External file
Resource type : css

Resource file : click on   ^  that is highlighted using arrow in the below image.

   * Give Styles2(do not give Styles2.css i.e, only give name but not extension).
   * It automatically cmes with .css extension and with $ and flower braces.
   * Save the css file externally to a folder as shown in below figure.
   * To see the Styles2.css appearing in the folder your file must contain some css code.
   * You must refresh your Repository to see the name of the file(Styles2.css) in the folder.
Giving Title to Dashboard:
*  CDE layout mainly works on Rows and Columns.
* Click on Add row symbol as shown in below figure.
 *  Give name of the Row and give back ground colour as shown in below figure.

* And give Corners: Round( it is below the back ground , not shown in the image-it is not visible here in the image)

 *  Now add Column(find ||) symbol on Layout Structure and click on it.

* Give all the necessary properties as shown in the below figure.
  Name: title
 HTML : <h1> Sales OverView YDT <h1> ( as show in figure at arrow symbol click on button.. You will be prompted to a new window there you have to write this HTML Code)
Font Size: 10
 See the preview:
The preview consists of only the title of the dashboard with its layout.
It is further going to be modified.

 Add your .css code here…

Css code
Body{
                background-color:#fffdf1;
}
h1{
                font-size:24px;
                color:#fff;
                margin-left:10px;
                margin-top:10px;
}
 Now see the preview.

By the time you get some idea on it.

Do as follows, I’m reducing the images now as you might get some idea..
Add another row -> Column->Html  as shown in figure.
For html give Name as title and write HTML code <h3>Sales Performance</h3>  in HTML editor
Add the following code to Styles.css to editor
h3{
    font-size:18px;
                font-weight:bold;
                color:#b68c58;
                margin-left:10px;
                margin-top:10px;
                margin-bottom:8px;
}
.salesTitle{
    color:#666;
                height:2px;
    margin-top:10px;
                margin-left:10px;
                margin-bottom:2px;
                width:939px;
}
Find the images for doing the above and see the preview again
 Add another column as shown in below figure

Give the following properties:

Name: sales_chart
Span size: 16
Now working with Data Sources and Query part:
  Click on Data Sources

·         Find SQL Queries in Left panel.
·         Click on sql over sqlJndi
·         In left panel give Properties
o   Name : get_sales_performance
o   Access Level : public (default)
o   Jndi: SampleData(We are working with pre defined database that comes along with pentaho software to develop this dashboard)
o   Query:
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2003
)
Working with Component
·         Click on Components
·         On the left panel click on Charts
·         In properties give the following
o   Name: sales_chart
o   Datasource: get_sales_performance
o   Width: 610
o   Height:140
o   HtmlObject : sales_chart
o   SeriesInRows : False
·         I will show you how to work with Advanced Properties in the upcoming posts on
CDE.
Now save your dashboard and Preview
The preview will look like as follows
That’s it.
This artical is for developing simple dashboard.
In my next article I’m going to make this dashboard dynamic with parameters and will make the dashboard better look and feel.
Thanks,

Pareto Chart – Data Visualization

PARETO CHART

What is Pareto Chart? Pareto chart is a data visualization tool which contains both bars & line graphs. In this, individual values are represented in decreasing order by bars & the cumulative total is represented by the line. Its named after Vilfred Pareto, an Italian economist and sociologist who conducted a study in Europe in the early 1900s on wealth and poverty. He found that wealth was concentrated in the hands of the few and poverty in the hands of the many. The principle is based on the unequal distribution of things in the universe.

Pareto Chart Example

When to use : Whenever we are having a number of factors, then pareto chart is used to highlight the relative importance (since the bar graphs are also arranged in decreasing order).

See the typical use cases highlighted below

– Used in customer care to show the most coomon customer dis-satisfaction factors

– Can be used in quality control to show common source of defects

The Pareto chart is generally used to you focus your improvement efforts on those issues that: 1.) Cost the most or 2.) Pose the highest risk / liability or 3.) those areas that occur the most often.

Dos & Donts :

If used properly, pareto chart can help a lot in understanding the key factors. The below mentioned factors are to be kept in mind & if used in conjuction, can provide a lot of actionable insight

a. Sub division :- It means, lets say at a customer care post using pareto they have found out that from a specific location maximum complaints are coming. Now, ideally they should further design a pareto for that specific location to get more insights (like in some cases there might be some complaints from a specific part of location because of some miscellaneous factor etc)

b. Multi-perspective analysis :- We should also do a multi-perspective analysis for ideal insights. For examples – if we take above case, not only they should do an analysis location wise but also reason wise & service wise etc. This might give them specific reasons for specific locations & hence counter measure can be taken to sort out the matter.

c. Repeast Analysis :- This depends on case to case or industry to industry basis, but based on their true knowledge & how they think the data dynamics are changing, the pareto charts should be updated & redesigned.

This is generally know as first level pareto analysis, second level pareto analysis (this is pareto analysis of the first bar of first pareto analysis) & third level pareto analysis (this is pareto analysis of the first bar of second pareto analysis)

Industry specific examples of Pareto Chart Usage :-

Below mentioned are some of the real world example usages of Pareto chart usage in industries & business organizations for data visualizations & analysis

  • Marketing – Where are the majority of my advertising dollars going? Which channels produce the most sales leads?
  • Healthcare – What types of infections are the most prevalent? What procedures are associated with the majority of return hospital visits?
  • Sales – Does a small percentage of customers account for a large percentage of revenue? If so, which ones?
  • Customer Service – How can I improve customer satisfaction? What do customers complain about the most?
  • Manufacturing – What defect types are most prevalent & key to improving an inspection process etc