Teachers

Being crazy of Pentaho and its associated tools Id like to share some thing about Pentaho CE 5.0.1 BA latest release..
First of all, Id like to thank you to the web details team for the efforts they kept in bringing up the powerful analytical engine(BA server).

The home page itself make you to stick with BA server where you can enter into the server with "admin" and "password" as the default credentials.. Yes, "joe" user is no more but he left with the same password to log into BA server.

There will be NO more admin console ... Now it is very easy and developer friendly to navigate in one console(User Console) to give data base connections and new roles for new users..

A lot of stuff included in the home page itself, giving the blogs links, books on different tools of pentaho family. In a single word I can say , All the stuff related to Pentaho at a single place. A BI developer like me can reduce the time for developing reports, dashboards , jobs.

Market place for installing C-Tools and the installed plug-ins list drop down is one of the good ideas of the team.

As quick as Pentaho released its CE 5.0.1 , I just downloaded and trying to explore the new look and navigating from all the menus, reports, connection places and etc.

Its very easy to install the pentaho CE BA now in windows...
Installation procedure :
1) Down load the software as a .zip file using this link .
2) Unzip it in your fav folder
3) Click on start-pantaho.bat file

Home page :


NOTE: path and class path for java must be set before the installation.

Hello Guys,

Here is a small document prepared on differentiating Pentaho Open source Vs Jasper Professional.

Based on the below information one can take decision on tool selection for BI needs.

I would like to present a clear picture with insight & readers of this document are encouraged to add your points in the comment box..

IMP NOTE:  Remember , I am comparing the Pentaho Open source(Community) 5.0.1 stable  with Jasper Professional(licensed) 5.5 

Dashboards :

Key feaute	Pentaho Open source Dashboard	Jasper Professional dashboard
Tool	Community Tools(C-Tools) – Server plug ins CDE(Community Dashboard Editor) CDF(Community Dashboard Framework) CDA(Community Data Access)	iReport/Jasper Studio Desktop stand alone tools
Development of Reports done at	Fly on the server itself	Depends on stand alone designing tools & need to publish to the server and again need to design the dashboard
Responsiveness /Mobile support	Responsiveness is achievable using bootstrap css framework	Lack of responsiveness even though there is Proprtional property of the dashboard designer
Dashboards output end view-able devices	Compatible with Mobile/iPad/laptop/Desktop/TV like big screens	Compatible with laptop/desktop
Charts	Protovis charts implemented, extend able to D3 charts.. can integrate fusion and HTML charts also.	Embedded charts engines – Jfree, Fusion & HTML5 charts. Can integrate protovis/D3 charts using html component of iReport/Jasper Studio
Loading of charts on the dashboard	Will take lesser time using Pentahos powerful caching mechanism	Will take longer time to load the charts on the dashboard
Embedding BI	Achievable using iframe tags	Achievable using iframe tags
Community support	Active	Active as it is a professional .. general community is not so active
Interactivity/Drill down/ Inter charts(frames) communication	Achievable	Achievable
Multi-tenancy	Need to check (will update on this)	Achievable
Customization	Developer friendly	Not developer friendly
Download here	Pentaho Maket Place or install using ctools-installer.sh http://community.pentaho.com/	Purchase license https://www.jaspersoft.com/jaspersoft-business-intelligence-software-trial

Key feature	Pentaho Open source Analysis	Jasper Opensource/Pro Analysis
Desktop Stand alone Development Tool	Pentaho Schema workbench	Schema Workbench
Front End tool to view the output	Pviot4J Analytics (or) Saiku Analytics (or) JPivot View (or) BTable (or) Ivy Schema Editor	JPivot View
Ad-hoc Reporting on Cube ouptut	Yes, using Saiku Analytics	Ad-hoc Editor (For jasper pro & enterprise it is an add-on feature. http://www.jaspersoft.com/editions
Query Language	MDX ( Multi Dimensional Expression)	MDX (Mullti Dimensional Expression)
Caching	Enabled – Very fast access of data	Enabled – some what less speed when compared with pentaho server
Community Support	Very Active – Quick Responses	Not so active as it is not a owned product of jasper/tibco
Documentation	Online documentation available with examples	Online pdf documentation is available with Examples

Key feature	Pentaho Open source Ad-hoc	Pentaho Enterprise Ad-hoc	Jasper Pro Ad-hoc
Source for ad-hoc reporting	Meta data layer creation	Meta data layer creation	Create topics OR Create Domains
Tool	Pentaho Meta Data Editor(PME) (Stand alone desktop tool) (OR ) Create JOINS on the fly on the server and use it for ad-hoc reporting	PME	No desktop tool.. directly in the server create Domains OR topics
Output Server tools	WAQR – Web Ad-hoc Query and Reporting (OR) Saiku Reporting compatible with 4.8 server as of now	Interactive Reporting - Advanced of WAQR But WAQR is not supported by any more Interactive reporting is an extension of WAQR in Enterprise Version of pentaho	Jasper Ad-hoc Editor
Support	Deprecated – No longer supported by penatho	On demand support	On demand – Need to be jasper customer
Visualization of Data in the form of charts	No	No	Yes .. using Ad-hoc report output visualization of data in the form of selected charts .

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.

Thats 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 its 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

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 : 

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

Hi Guys,

It is quite easy to learn about cascading parameters in pentaho CDE dash boarding.

Example below teach you how to give cascading parameters on CDE dashboard.

Environment :
C-Tools 13.09
Pentaho BA Server 4.8 stableb
DB : postgreSQL foodmart ( jasperservers database)

IDEA of cascading parameters:

:-)

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

Hi guys,

This post teach you how to make bars as lines in pentaho CDE.

Source for this post is : http://jsfiddle.net/duarteleao/7maGD/
and http://type-exit.org/adventures-with-open-source-bi/2011/06/creating-dashboards-with-cde/

Scenario : Some end users wants visualization of grouped bars as lines where one of the bar is as bar and remaining bars as targeted lines on the same bar.


Properties have to give:
Plot2 : True
Find the remaining properties in below image: version of CDE is : 13.06 but will work in higher versions also.

NOTE: Find the image for how to give the above code as properties in Extension points of chart  component.

 Sample output of the chart on dashboard after giving the above properties.

 

Sadakar
BI developer
("Learning never exhausts the mind")

Hi Guys,

Below tip is used for disable Edit Mode of CDE dashboards ..

When this is useful ?
CASE 1 : 
If you are not going for Embedding BI to integrate the CDE dashboards.

CASE 2: 
If you dont want your end user to open the CDE file to view the CODE.. If you are using the Server itself in your end client location he/she also may not need all these except logging into the server and see the dashboards...

For doing this.. i.e., to disable the Edit mode of CDE dashboards, you just need to comment below code in plugin.xml file of pentaho-cdf-dd folder.

File location : /home/sadakar/softwares installed/Pentaho/biserver-ce/pentaho-solutions/system/pentaho-cdf-dd

<!--
                <operation>
                    <id>EDIT</id>
                    <perspective>wcdf.edit</perspective>
                </operation>
-->

Sample Image:


NOTE: As it is a configuration file, you need to restart the server once the changes done to it.

Another Imp NOTE: 
As C-Tools(CDE,CDF,CDA) are uneatable from Marketplace, and if you go for any upgrade of them then you might lost this configuration.. what itll do internally when you upgrade to new versions of C-Tools, internally itll uninstall the plug-ins and then download the new.. hence as you will get a fresh copy you there might be a chance of losing your settings.. This is not tested..

Thank you :-)

Sadakar
BI developer
Pentaho/Jasper/Talend ETL