How to create reports using Pentaho Report Designer

How to create reports using Pentaho Report Designer

Pentaho Business Intelligence platform provides several tools to design and deploy reports, the easiest is the Pentaho BI web reporting wizard (see pentaho_reporting).

While this approach is useful for basic or self service reporting, for complex reports with charts, cross linking and scripting a tool like Pentaho Report Designer is more appropriate. Pentaho Report Designer is the reporting engine integrated in Pentaho Business Intelligence.

Other excellent Pentaho compliant reporting tools are Jasper Reports and Eclipse BIRT, however Pentaho Report Designer has the advantage of being more integrated and easily deploy-able.

Steps :

Connect a database in Pentaho Report Designer

Pentaho Report Designer is an open source reporting tool available at http://sourceforge.net/projects/pentaho/files/

Once unzipped the application can be run with report-designer.bat (report-designer.sh in unix). To create a new report press File->New and then Data->Add datasource->JDBC. Pentaho Report Designer connects with several data source providers, for example SQL databaseMondrian OLAP and Pentaho Data Integration (Kettle).

In this example we’ll use a common JDBC connection for Postgres databases. To add a new connection press the plus icon on the JDBC dialog.

JDBC Connection

On the new dialog set the data base connection credentials, in this example a pre-existent Postgres database named Foodmart, with the following parameters:

Connection name: SugarBI
Connection type: Postgres
Host name: <host>
Database name: <database name>
Port number: 5432
User name: root
Password: <your password>
Access: Native (JDBC)

img-2

Now press Test to check the connection and then press ok. In case of connection error check the connection parameters and whether the JDBC driver is present in /report-designer/lib/jdbc.

Now press the plus icon and fill the SQL query text and query name, like in the following picture.

img-3

To check if the query works press Preview button and then Ok to close the dialog.

In a real enterprise application a JNDI connection would be a better solution than a direct JDBC connection. A JNDI connection, which is explained at the end of this post, defines a JDBC connection alias and avoids the hassle to update all the reports in case of database parameters changes.

Build a report with Report Designer

After the Postgresql connection configuration the Data tab (on the right) should display the connection name with all its fields. Placing a field in the report is very straight forward and can be accomplished by dragging and dropping the field on the Details band.

img-4

 

To set a page title click on “Ab” button in the left toolbar and drop it on Page Header band and to set a column label simply drag the “Ab” button over the Report Header band.

A page number can be added by right clicking Functions (right Data tab) and selecting Add functions..->Common->Pages of Pages. The new Pages of Pages field can then be dragged on the Page Footer.

img-5

Pentaho Report Designer allows to test the final result at any time by pressing the play icon and choosing an output format, for example PDF or HTML.

img-6

How to publish a report in Pentaho

Once built the report can be run directly on Pentaho Business Intelligence suite. Pentaho Report Designer provides direct access to Pentaho‘s repository, however the access needs to be enabled first. To enable the repository access edit biserver\pentaho-solutions\system\publisher_config.xml and set the password in the publisher-password tag:

<publisher-config>
<publisher-password>your password here</publisher-password>
</publisher-config>

img-7

The default credentials are:

URL: http://localhost:8080/pentaho
User: joe
Password: password

Click Ok and if Pentaho server is running a form like this one should appear:

img-8

Press ok, the report should appear on the Pentaho repository browser of the Pentaho Business Intelligence suite:

img-6

 

 

 

 

 

Creating Cascading Parameters with Pentaho Report Designer 5.0.1 Stable

Here we will be talking about creating cascading parameters with Pentaho Report Designer 5.0.1 Stable.

Cascade parameters provide a way of managing large amounts of data in reports. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter.

PRD cascading parameter

Step-1. Build the Parameter Queries :

I will need to build two parameter queries, the first will need to display a distinct list of countries and the second will need to display a distinct list of cities which belong to the country.

 

To create a new query click on the Add a new query icon (add a new query) and enter a SQL statement which will retrieve a distinct list of countries – make sure you don’t forget to name your query (in this example my country parameter query is named countryList):

PRD cascading parameter 2

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`COUNTRY`
FROM
 `customers`

The next step is to preview the query – as you can see from the screen shot below the countryList parameter query is retrieving a distinct list of countries:

PRD cascading parameter 3

Now we will need to add the second parameter which will display a distinct list of cities based on the value of the country parameter. Close the preview window and click on the icon (add a new query) and enter a SQL statement which will retrieve a distinct list of cities – make sure you don’t forget to name your query (in this example my city parameter query is named cityList):

PRD cascading parameter 4

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`CITY`

FROM
 `customers`

Preview this query. At the moment the above query is only retrieving a distinct list of every city. I need to make sure that this query retrieves a list of cities based on the country parameter

PRD cascading parameter 5

For this query to only show a list of cities based on the value of the country parameter I will need to add the country parameter name to the WHERE clause of the cityList query. As I have not yet created the country or city parameters I will need to make note of the country parameter name I will be specifying in the cityList query – I have chosen to call the country parameter sCountryName. The new cityList query now looks like this:

PRD cascading parameter 6

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`CITY`
FROM
 `customers`
WHERE
 `customers`.`COUNTRY` = ${sCountryName}

I have now created both parameter queries – I will need to revisit this area later on to modify the report query customerList but for now click on the OK button to close the JDBC Data Source window.

 

Step -2. Create the Parameters

I will need to create two parameters:

  1. A country drop down parameter which is named sCountryName (step 1)
  2. A city drop down parameter which will be named sCityName

Creating the sCountryName Parameter

To create a new parameter make sure you have the Data tab active, right click on the Parameters menu item and select the Add Parameter… option:

PRD cascading parameter 7

An Add Parameter… window will pop up. Before you make any changes to the options expand the connection folder on the left and then select the query which will populate the parameter, in this example it is the countryList query. Below is a screen shot of all the sCountryName parameter options completed:

PRD cascading parameter 8

 

The options above are fairly self explanatory however here are descriptions of the most important options:

  • Name: This is the name we specified in step 1, this must match the value of this parameter we put in the cityList WHERE clause
  • Type: The type of this parameter is a drop down
  • Query: The query that will populate this parameter is the countryList query which was created in step 1
  • Value and Display Name: I have set these both to the COUNTRY field as the value is the same as the output I would like displayed in the drop down parameter
  • Value Type: The COUNTRY field is a string
  • Mandatory: I have checked this option as a user must select a country before running the report – this also ensures that the city parameter will be populated

Click the OK button to save the sCountryName parameter.

Creating the sCityName Parameter

The second parameter I will need to create is for the city drop down. There isn’t much different from this parameter and the sCountryName parameter so I can copy and paste this parameter and then change some options.

To copy the parameter right click on the sCountryName parameter under the Data tab and select the Copy option (alternatively you can use the CTRL+C shortcut):

PRD cascading parameter 9

To paste the parameter right click on the Parameters… item and select the Paste option (alternatively you can use the CTRL+V shortcut):

PRD cascading parameter 10

This will create an identical copy of the sCountryName parameter.

PRD cascading parameter 11

 

Double click on the sCountryName parameter which is located at the bottom of the Parameters list, this will open the Edit Parameter… window. Before you make any changes to the options expand the connection folder on the left and then select the query which will populate this parameter, in this example it is the cityList query. Below is a screen shot of all the sCityName parameter options completed:

PRD cascading parameter 12

The options above are fairly self explanatory however here are descriptions of the most important options:

  • Name: I have decided to name this parameter sCityName
  • Type: The type of this parameter is a drop down
  • Query: The query that will populate this parameter is the cityList query which was created in step 1
  • Value and Display Name: I have set these both to the CITY field as the value is the same as the output I would like displayed in the drop down parameter
  • Value Type: The CITY field is a string
  • Mandatory: I have checked this option as a user must select a city before running the report

Step-3. Modify the Report Query

Now that the parameters and parameter queries have been created we are now ready to modify the report query (customerList) to use the new parameters. To modify the report query expand the data source connections under the Data tab and double click on the report query, customerList:

PRD cascading parameter 13

This will open the JDBC Data Source window and automatically highlight the customerList query:

PRD cascading parameter 14

To make the customerList query use the two new parameters created in step 2 you will need to add in two new conditions to the WHERE clause like:

PRD cascading parameter 15

 

Here is the above query in a format which you can copy and paste from below:

SELECT
 `customers`.`CUSTOMERNUMBER`,
 `customers`.`CUSTOMERNAME`,
 `customers`.`CITY`,
 `customers`.`COUNTRY`

FROM
 `customers`
WHERE
 `customers`.`COUNTRY` = ${sCountryName}
AND
 `customers`.`CITY`    = ${sCityName}

Now the report query will use the values of the sCountryName and sCityName parameters in its WHERE clause. Click on the OK button to close the JDBC Data Source window.

Step -4. Preview the Report

The last step is to preview the report, to do this you can click on the (preview icon) found in the top left hand corner or alternatively click on View > Preview menu item.

PRD cascading parameter 16

By default nothing will be displayed in the report and none of the drop down parameters will be populated (if you would like a country to be set when you first run your report set a value for the Default Value option in step 2 for the sCountryName parameter).

To check if the cascading parameters are working pick a country from the first drop down for example Australia:

PRD cascading parameter 17

 

The city drop down parameter should automatically populate with the cities which belong to Australia (even though I’m not sure if you would classify Glen Waverly as a city):

PRD cascading parameter 18

After selecting a city i.e. Melbourne click on the UPDATE button and now the report is filtered to customers who are located in the country Australia and the city Melbourne:

PRD cascading parameter 19

 

Thanks

Rohit Verma

Helical IT Solutions

Pentaho BI and Ctools – Introduction

Pentaho Overview:
It offer a suite of open source Business Intelligence (BI) product called Pentaho. Business Analytics provide, Data integration, OLAP Service, Reporting, Dash boarding, Data Mining, ETL Capabilities and BI platform. It is web application like Jaspersoft. It uses Tomcat server.

Pentaho BI Platform:
Pentaho BI Platform supports Pentaho end to end business intelligence capabilities and provide central access to your business information, with back end security, integration, scheduling, auditing and more. Pentaho BI Platform has been designed in such a way that it can be scaled to meet the needs of any size organization.

OLAP:-
OLAP Stands For Online Analytical Processing , OLAP is an Approach to answering a multi-dimensional analytical query swiftly.

Dash Boarding:-
Pentaho Dashboards give business users the critical information they need to understand and improve organizational performance. Provides immediate insight into individual, departmental, or enterprise performance by delivering key metrics in an attractive and intuitive visual interface

ETL (Extract Transform Load):-
ETL System are Commonly used to integrate data from multiple application, typically developed and supported by different vendors or hosted on separate computer Hardware.

Data integration:
Data integration involves combining data residing in different sources and providing users with a unified view of these data.

There are many tools present inside Pentaho, the same have been elaborated below,
Pentaho Tools -> Pentaho is having many tools like.

1) PRD – Pentaho Report Designer (Reporting)
Transform all your data into meaningful information tailored to your audience with Reporting, a suite of Open Source tools that allows you to create pixel-perfect reports of your data in PDF, Excel, HTML, Text, Rich-Text-File, XML and CSV. These computer generated reports easily refine data from various sources into a human readable form.

2) PSW – Pentaho Schema Workbench or Mondrian Schema Workbench
The Mondrian Schema Workbench is a designer interface that allows you to create and test Mondrian OLAP cube schemas visually. The Mondrian engine processes MDX requests with the ROLAP (Relational OLAP) schemas. These schema files are XML metadata models that are created in a specific structure used by the Mondrian engine. These XML models can be considered cube-like structures which utilize existing FACT and DIMENSION tables found in your RDBMS. It does not require that an actual physical cube is built or maintained; only that the metadata model is created.

3) PME – Pentaho Meta Editor
Pentaho Metadata Editor (PME) is a tool that allows you to build Pentaho metadata domains and relational data models. A Pentaho Metadata Model maps the physical structure of your database into a logical business model.

4) PDI – Pentaho Data Integration
Pentaho data integration prepares and blends data to create a complete picture of your business that drives actionable insights.

In Server -> There are many tools inside server like.
1) C-Tools ->CDA,CDF,CDE
C-Tools-> Used to create Dashboard.
CDA : Community Dashboard Data Access
CDA – Community Data Access is a community project that allows to fetch data in various formats from Pentaho BI platform. It will be accessed by simple url calls and supports the following datasources:
1)      SQL
2)      MDX
3)      Metadata
4)      Kettle

CDF : Community Dashboard Framework
Community Dashboard Framework (CDF) is a project that allows you to create friendly, powerful and fully featured Dashboards on top of the Pentaho Business Intelligence server.
CDE :Community Dashboard Editor
CDE and the technology underneath it (CDF, CDA and CCC) allows the development and deployment of Pentaho Dashboards in a fast and effective way.

2) Saiku Analytics:-  Explore Data,Visualize  etc

 

Please get in touch with us for any Pentaho related queries, Helical IT Solutions

Creating Dashboard using Pentaho CDE

This blog will talk about how to create a dashboard using Pentaho CDE (Community Dashboard Editor) framework:-

1. Open Browser and paste this url in below
localhost:7080/pentaho
login inside pentaho according to username and password which one provided in your browser.

2. Go to File->New->CDE Dashboard and save your in favourite location.

3. CDE Dashboard are divide in Four major panel like Layout Panel, Component Panel,  Datasource  Panel and Preview your Dashboard.
Layout Panel- where i can design the resources, rows > column > and html tag.
component Panel-in query component i can access data source where i can provide a
datasource     name.
Datasourced Panel-where i can create datasource.

4. inside layout panel.
create Resource  Name-style, inside resource create row and column and again create row    and inside
row create html tag.
Row-
column
Row
Html    Name-title, HTML-<H1>Customer Report</H1>
Html    Name-Query, HTML-<H1>Write any thing</H1>

5. Go to Datasource Panel-
create  Datasource-go to SQL Query ->select any
a)sql over sqljdbc,   b)sql over sqljndi
I have selected sql over sqljdbc->
Name-    DataConnection
Driver-   org.postgresql.Driver
user name- postgres
password-  postgres
url- jdbc:postgresql://localhost:5432/foodmart
Access Lavel- public
Query-  select count(customet_id) from customer;

6. Go to Component Panel-
go to Others-> Query Component.
Name- customer_component
Result Var-  myresult
Datasource-  DataConnection
HtmlObject-  cust_query
PostExecution- function fun()
{
document.getElementById(‘cust_query’).innerHTML = myresult;
}
7. Save the component and click on Preview Your Dashboard. And See the output.

 

For any questions regarding CDE, please get in touch with us Helical IT Solutions

 

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

MicrosoftInternetExplorer4

1. Open Browser and paste this url in below

                 localhost:7080/pentaho

 login in pentaho according to username and password which one provided in your browser.

 

2. Go to File->New->CDE Dashboard and save your in favourite location.

 

3. CDE Dashboard are divide in Four major panel like Layout Panel, Component Panel,  Datasource  Panel and Preview your Dashboard.

                Layout Panel- where i can design the resources, rows > column > and html tag.

                component Panel-in query component i can access data source where i can provide a 

                datasource      name.

                Datasourced Panel-where i can create datasource.

 

4. inside layout panel.

 create Resource  Name-style, inside resource create row and column and again create row    and inside 

   row create html tag.

                     Row-

                                column

                                    Row

                                                Html    Name-title, HTML-<H1>Customer Report</H1>

                                                Html    Name-Query, HTML-<H1>Write any thing</H1>

 

5. Go to Datasource Panel-

                create  Datasource-go to SQL Query ->select any

1)sql over sqljdbc,   2)sql over sqljndi

                I was selecting  sql over sqljdbc->

                Name-DataConnection

                Driver-   org.postgresql.Driver

                user name- postgres

                password-  postgres

                url- jdbc:postgresql://localhost:5432/foodmart

                Access Lavel- public

                Query-  select count(customet_id) from customer;

 

6. Go to Component Panel-

                go to Others-> Query Component.

                Name- customer_component

                Result Var-  myresult

                Datasource-  DataConnection

                HtmlObject-  cust_query

                PostExecution- function fun()

{

                                                document.getElementById(‘cust_query’).innerHTML = myresult;

                                                }

7. Save the component and click on Preview Your Dashboard. And See the output.

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;}

Pentaho 5.0.1 CE integration with MySQL 5.0.1 CE (Windows or Linux family)

Pentaho 5.0.1 CE integration with MySQL 5.0.1 CE (Windows or Linux )

Parts

  1. Creating databases
  2. Modifying configuration files
  3. Stopping HSQL db start up

Creating databases

Command to execute the scripting files

mysql>source  D:\ biserver-ce\data\mysql5\create_jcr_mysql.sql

Similarly execute the remaining .sql files(i.e, execute create_quartz_mysql.sql and create_repository_mysql.sql)

  1. Check the databases created using show databases command on MySQL command prompt.

 

Modifying configuration files

1. applicationContext-spring-security-hibernate.properties.

Edit the file pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

Original code

jdbc.driver=org.hsqldb.jdbcDriver

jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.HSQLDialect

Modified code

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.MySQLDialect

  2. hibernate-settings.xml

Edit the file pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

Modified code

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

 

3. mysql5.hibernate.cfg.xml

Location of the file: pentaho-solutions\system\hibernate\mysql5.hibernate.cfg.xml

Do need to change any code in this file.. Just need to check everything is perfect or not

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>

<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>

<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

<property name="connection.username">hibuser</property>

<property name="connection.password">password</property>

4. quartz.properties

Location of the file: pentaho-solutions\system\quartz\quartz.properties

Original Code

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Modified Code

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

5. context.xml

Location of the file: tomcat\webapps\pentaho\META-INF\context.xml

Original Code

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="hibuser" password="password"

driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"

validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />

 

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="pentaho_user" password="password"

driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"

validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>

Modified Code

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="hibuser" password="password"

driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"

validationQuery="select 1" />

 

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="pentaho_user" password="password"

driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"

validationQuery="select 1"/>

 Imp Note:

Delete pentaho.xml file in below location

tomcat\conf\Catalina\localhost\pentaho.xml

Reason:

Pentaho creates  on startup pentaho.xml as a copy of context.xml

6. repository.xml

Location of the file: pentaho-solutions\system\jackrabbit\repository.xml.

Comment this code means (<! – – every thing here – -> )

Active means: Remove comment

i)                    FileSystem part

Comment this code

<FileSystem>

<param name=”path” value=”${rep.home}/repository”/>

</FileSystem>

Active this code

<FileSystem>

<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_repos_"/>

</FileSystem>

ii)                  DataStore part

Comment this code

<DataStore/>

Active this code

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">

   <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

   <param name="user" value="jcr_user"/>

   <param name="password" value="password"/>

   <param name="databaseType" value="mysql"/>

   <param name="driver" value="com.mysql.jdbc.Driver"/>

   <param name="minRecordLength" value="1024"/>

   <param name="maxConnections" value="3"/>

   <param name="copyWhenReading" value="true"/>

   <param name="tablePrefix" value=""/>

   <param name="schemaObjectPrefix" value="ds_repos_"/>

 </DataStore>

iii)                Security part in the FileSystem Workspace part

Comment this code

<FileSystem>

<param name=”path” value=”${wsp.home}”/>

</FileSystem>

   Active this code

<FileSystem>

<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_ws_"/>

</FileSystem>

iv)                PersistenceManager part

Comment this code

<PersistenceManager>

<param name=”url” value=”jdbc:h2:${wsp.home}/db”/>

<param name=”schemaObjectPrefix” value=”${wsp.name}_”/>

</PersistenceManager>

Active this code

<PersistenceManager>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user" />

<param name="password" value="password" />

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>

</PersistenceManager>

v)                  FileSystem Versioning part

Comment this code

<FileSystem>

<param name=”path” value=”${rep.home}/version” />

</FileSystem>

 

Active this code

<FileSystem>

<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_ver_"/>

</FileSystem>

vi)                PersistenceManager Versioning part

 

Comment this code:

 

<PersistenceManager>

<param name=”url” value=”jdbc:h2:${rep.home}/version/db”/>

<param name=”schemaObjectPrefix” value=”version_”/>

</PersistenceManager>

Active this code:

<PersistenceManager>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user" />

<param name="password" value="password" />

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="pm_ver_"/>

</PersistenceManager>

Stopping HSQL db start up

In web.xml file

Comment or delete this code (Commenting is preferable)

<!– [BEGIN HSQLDB DATABASES] –>

<context-param>

<param-name>hsqldb-databases</param-name>

<param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>

</context-param>

<!– [END HSQLDB DATABASES] –>

 

Also comment this code

<!– [BEGIN HSQLDB STARTER] –>

<listener>

<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>

</listener>

<!– [END HSQLDB STARTER] –>

 

 

You have done with integrating pentaho 5.0.1 CE with Mysql 5.5

Now login to the Pentaho server .

URL:  http://localhost:8080/pentaho

Username/Password : Admin/password

NOTE:

  • You will not find any sample working. B’z you have not installed sample data.
  • Example available in pentaho are developed on sample data so you need to execute and give the new sample data base connections(for this you need to execute .sql file of sample data).

Helical IT Solutions

Inter panel communication in pentaho CDE

Inter panel communication in pentaho CDE

In this post you will learn how to communicate with the other panels in the same dashboard.

Example Scenario :
Panel 1 : BarChart is placed in panel-1
Panel 2: Table data displayed in panel-2

Functionality : The ideal functionality expected is when we click on any of the bars (panel 1), then the panel 2 should get updated and corresponding data should be displayed in 2nd panel. i.e., Make use of parameters, clickAction property, Listeners.

Example Developed on :
Pentaho 5.0 CE server
Pentaho C-Tools version : 13.09
Database : postgresql-foodmart( jasper food mart database)

Steps:
1) Create parameter (Lets say : param1_position_title)
2) Add it to listeners & parameters in your table component.
3) Add it to parameters in your data source for table query
Example:
SELECT
employee_id,
full_name,
position_id,
department_id
FROM
employee
WHERE
position_title=${param1_position_title}

4) Add to parameters in your chart(bar chart).

5)  BarChart query example:

SELECT DISTINCT
position_title AS position,
sum(salary) AS salary
FROM employee
GROUP BY position_title

6) In the chart properties
clickable = True
clickAction
function fun()
{
Dashboards.fireChange(‘param1_position_title’, this.scene.atoms.category.value);
}

Get in touch with us for any Pentaho related consultation, query

Pentaho Schema Workbench and Saiku Analytics – Quick Introduction

Web-based Ad Hoc Query and Reporting

Pentaho web-based ad hoc query and reporting is a capability that extends Pentaho Reporting to provide end-user self-service for report creation.

How does Ad-Hoc Reporting work?

Web-based ad hoc query and reporting provides a browser-based wizard that allows end-users to create their own reports.

The wizard uses web services and components in the Pentaho platform to utilize metadata models that have been published from the Pentaho metadata editor.

Pentaho metadata allows the content of the reporting database to be presented to business users in familiar business terms.

Reports created by users use metadata queries to define the data that user wants to see.

These metadata queries are translated into SQL every time a report is run so any changes to the database are hidden from the users.

 What are the tools to be used for Ad-hoc reporting?

1)      Mondrian – PSW –Pentaho Schema workbench – OLAP cube creation

2)      Saiku Analytics

  Mondrian – PSW –Pentaho Schema workbench – OLAP cube creation

  • It is a designer interface that allows you to create and test Mondrian OLAP cube schemas visually.
  • The Mondrian engine processes MDX requests with the ROLAP (Relational OLAP) schemas.
  • These schema files are XML metadata models that are created in a specific structure used by the Mondrian engine.
  • These XML models can be considered cube-like structures which utilize existing FACT and DIMENSION tables found in your RDBMS.
  • It does not require that an actual physical cube is built or maintained; only that the metadata model is created.

Features of PSW:

  • High performance, interactive analysis of large or small volumes of information
  • Dimensional exploration of data, for example analyzing sales by product line, by region, by time period
  • Parsing the MDX language into Structured Query Language (SQL) to retrieve answers to dimensional queries
  • High-speed queries through the use of aggregate tables in the RDBMS
  • Advanced calculations using the calculation expressions of the MDX language

Saiku Analytics

  • On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.
  • OLAP functionality is characterized by dynamic multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities (The OLAP Council)
  • By harnessing the power of OLAP, Saiku allows users to choose the measures and dimensions they need to analyse and “slice and dice” the data and drill into the detail to uncover relationships, opportunities and issues.
  • The intuitive user interface lets users drill down and up, filter, pivot, sort, and chart against OLAP and In-Memory engines.
  • Utilizing the Olap4J library, Saiku is the first application on the market to offer support for Mondrian’s Scenario feature allowing non destructive editing of query results, giving users the ability to adjust the figures and perform “what-if” analysis over their data.
  •  By harnessing the power of Mondrian, Saiku offers scalable in-memory analysis.
  • Large amounts of data can be stored in memory in a distributed manner across the local network, offering greatly improved performance over large data warehouses as the aggregated data is retrieved from the network instead of reading from disk.

NOTE:

  • Saiku Analytics for pentaho is a web-based Ad-hoc reporting tools.
  • It’s a plug-in for pentaho. You can download and install from Penthao Market Place in Penthao Server.

Features of Saiku Analytics

  • Drag & Drop Report-Design
  • Export to: PDF,CSV,XLS,CDA,PRPT
  • Uses Pentaho Report Designer PRPT-Templates
  • Grouping
  • Aggregation
  • Totals
  • OpenFormula Support

Working with PSW

Example:
Schema Workbench – Creating an OLAP Schema
Database Connection
Connecting to postgres database.
·         Download the type4 driver from the following location http://jdbc.postgresql.org/download.html
·         Add this driver in the “ drivers” folder which is located at C:\Program Files (x86)\psw-ce-3.2.1.13885\schema-workbench\drivers
·         Restart the schema work bench.
·         Now do the following steps.
·         Go to “Options” then Click on “Connection”. i.e, OptionsàConnection
·         Give the database details. The connection details are depicted in the following figure.

Schema

 Step 1:-  File->New->Schema
And give the Schema Name(our schema name is foodmart)  as follows
CUBE
Right click on Foodmart schema and do Add cube. Click on New Cube 1 and write Products for name attribute.
 

Dimension

Right click on Products cube and do Add Dimension. Click on New Dimension 1 and write Product for name attribute. Choose product_idcolumn for foreignKey attribute.
Right click on default hierarchy and do Add table. Click on Table and write product for name attribute.

Level

Right click on default hierarchy and do Add Level. Click on New Level 1 and fill out the following fields:
Brand Level Attributes
Name:Brand
Table: product
Column:brand_name
 
 
Repeat the previous step  to create two levels as below:
Name and SKU Levels Attributes
name
Name
SKU
table
product
product
column
product_name
SKU

Measure

Right click on Products cube and do Add Measure twice. Click on New Measure 0 and New Measure 1 then fill out the following fields:
Product and Class Numbers Attributes
name
product number
class number
aggregator
distinct-count
distinct-count
column
product_id
product_class_id

Save your Schema

Click on File > Save As.  Choose a path and a name for your schema.

 

Working with PSW
Example:
Schema Workbench – Creating an OLAP Schema
Database Connection
Connecting to postgres database.
·         Download the type4 driver from the following location http://jdbc.postgresql.org/download.html
·         Add this driver in the “ drivers” folder which is located at C:\Program Files (x86)\psw-ce-3.2.1.13885\schema-workbench\drivers
·         Restart the schema work bench.
·         Now do the following steps.
·         Go to “Options” then Click on “Connection”. i.e, OptionsàConnection
·         Give the database details. The connection details are depicted in the following figure.

Schema

 Step 1:-  File->New->Schema
And give the Schema Name(our schema name is foodmart)  as follows
CUBE
Right click on Foodmart schema and do Add cube. Click on New Cube 1 and write Products for name attribute.
 

 

Dimension

Right click on Products cube and do Add Dimension. Click on New Dimension 1 and write Product for name attribute. Choose product_idcolumn for foreignKey attribute.
Right click on default hierarchy and do Add table. Click on Table and write product for name attribute.

Level

Right click on default hierarchy and do Add Level. Click on New Level 1 and fill out the following fields:
Brand Level Attributes
Name:Brand
Table: product
Column:brand_name
 
 
Repeat the previous step  to create two levels as below:
Name and SKU Levels Attributes
name
Name
SKU
table
product
product
column
product_name
SKU

Measure

Right click on Products cube and do Add Measure twice. Click on New Measure 0 and New Measure 1 then fill out the following fields:
Product and Class Numbers Attributes
name
product number
class number
aggregator
distinct-count
distinct-count
column
product_id
product_class_id

 

Save your Schema

Click on File > Save As.  Choose a path and a name for your schema.

Working with Saiku Analytics
Example:
Steel Wheels CUBE demonstration in Saiku Analytics
·         CUBE is designed using PSW and published to the BA server.
·         Images below describe how to create Ad-hoc reporting using Saiku Analytics and the features of Saiku Analitics.http://helicaltech.com/wp-admin/post.php?post=1077&action=edit&message=1
1) Selecting CUBE  to be used for Ad-hoc reporting after publishing to the BA Server.(Example of Steel Wheels CUBE)
·         We can select the cube on which we want to perform ad-hoc reporting.
2. Drag and drop Dimension and measures to the Columns and Rows

·We can drag drop the fields(Dimensions) to the rows and values to the columns(Measures).
3. Swapping Axis Feature –
·         Swapping the same output of above image
4. Exporting to Spread Sheet
·         We can export the data/charts displayed on the console to Excel format or csv format.
5. Filter data (Year wise – 2004 data only displayed)
·         Filtering the data – Click on particular row or column by which we want to filter.
·         We can place the dimension in “ Filter” section to filter data.
·         Image below gives the idea on how to do this.
6. Data Visualization – Interactivity
·         Visualizing data on the charts.
·         It is interactive.. On hovering to the slices it’ll give the details of that slice as shown in below image.
·         Supports stacked bar, bar, line and heat grid types.

7. MDX Query Analysis

  • Saiku engine internally generates MDX queries.
  • Also pentaho mondrain and sql log files we can find in the installation folder.
  • For doing this we need to enable the log files.
  • Mondrian engine internally converts the MDX queries to SQL queries.

 

 

Pentaho C-Tools Manul installation in CE 4.8 and 5.0 – jar files installation.

Hi guys..
Some times we can NOT access Pentaho Market place due to the proxy settings enabled…  I was trying it and unable to fix it .. will have a deep look into it… but alternatively installed Pentaho-C-Tools manually.

Find the bug link below.

http://forums.pentaho.com/showthread.php?153500-Issue-Pentaho-Market-Place-in-4-8-server-Not-connecting-to-server-amp-5-0-install

There was some problem with proxy internet access . I could not figure it out exactly … so thought of installing C-Tools manually…
hence have given a trail on installing C-Tools in Pentaho CE 4.8 and installed successfully.

Manual installation of C-Tools in 4.8 CE
Steps :
1) Down load C-Tools from web-details site

CDA link : http://www.webdetails.pt/ctools/cda.html
CDE link : http://www.webdetails.pt/ctools/cde.html
CDF link : http://www.webdetails.pt/ctools/cdf.html

2) These are executable jar file, you need to click on them as generally we install windows based software.

3) importantly you need to locate the installation path..
Generally give your pentaho-solutions path

4) Now,start the server and find the CDE icon on Tools bars of pentaho.

Note that , you may not get all the CDE example in this way of installation …. I need to figure out by which way we need to install samples..

Manual installation of C-Tools in 5.0 CE
I have not tried yet this installation.. but you can refere Pedro’s blog to install the C-Tools manually in BA server.

http://pedroalves-bi.blogspot.pt/2013/11/ctools-for-pentaho-50-is-available-cdf.html

Pentaho CDE Dashboard Example – Adding few more functionality to it

I have been exploring the pentaho CDE… A small work around on it which has given me a learning experience as well understanding the power of the tool…

Here is a example dashboard for you… and adding more functionality to it…. If you have data ready or want to perform BI solutions on your business let us know .. find us at http://helicaltech.com/ located in hyderabd, AP, INDIA.

SalesDasbboard

Features:
1. Superb Layout (Back ground color, rows, columns structured based one)
2.Charts
a. Pie Chart ( 1st row 1st 2 charts -SQL based charts & 3rd one is MDX based)

  • Pie charts are converted to circulared
  • Percentage values, actual values(featured any one of them we can remove)
  • Bar charts(4 and 5) – SQL based charts
      b. Bar Charts
  • Dynamic with number of bars
  • X-axis labels are rotated.
  • Dynamic with date input controls.
  • Currency symbol is added.
      c. Line Chart
  • Two lines are plotted
  • Dynamic with date input controls
SQL queries :
Complex queries are written to get the exact result sets … for instance, JOINS, aggregate functions
CSS and JavaScript
To design the lay out written CSS code and to get the Advanced functionalities written java script..

Featured functionalists on the same example
Working on the below:
1. Drill down functionality on 2nd Chart – will display table component information in another dashboard and providing some button or link to come back to first dashboard.
2. Exporting parts of the dashboard or entire dashboard to pdf.
3. And many more at a single dashboard…

Will update once done with the functionality….

For source code or any assistance on C-Tools(CDA,CDF,CDE) meet me at [email protected]

I believe in “Learning Never Exhausts The Mind”

Embedding BI Reports (Jasper Reports/ Jasper Server/ Pentaho Reports/ Pentaho Server)

There are many methods of embedding BI reports, this blog tries to explain the meaning of Embedded BI, different approaches which can be used, their respective advantages and disadvantages

 

A)     What is embedded BI?

Lets say you have developed your entire BI solution which includes reports, dashboards, mashups, analysis etc, the platform which is used can be anything like Jaspersoft or Pentaho. The process of integration of this BI solution with your existing application/software/portal is called Embedded BI.

Embedded BI provides a lot of advantages like ability to add more reports over and above the existing canned reports which your application might provide, the look and feel and customization of all the reports can be changed as per the software/user preference, better visualization, unified application for everything. Biggest advantage can be your product value will increase much more, hence resulting in much more sales and revenue.

a) Embedding using iFrame Method :-

iFrame method of embedding BI reports can be used for embedding Jasper server, Jasper reports and Pentaho server, Pentaho reports. The report pulled in this case is using iframe as a tag. Even though the report is external, but to an end customer he will not be able to recognize the same.

The advantages of using iFrame method is this method is fast and quick to implement. The disadvantages includes it will be difficult to manage user, also in this case there can be cross browser compatibility issues, security is a major issue here.

 

b) Embedding Using webservice:- Webservices can also be used to integrate reports, dashboard, interactive report or/and ad-hoc reports. We can use webservices to accept the information from the Jasper/Pentaho server, this Jasper server can be anywhere ( on Tomcat or any other server). Using webservices we will invoke the response from Jasper Server/Pentaho server. This response can be can be then embedded inside application/software. Jasper server/Pentaho server also supports RESTFUL service (JSON format responses). In this approach there are no or less security related issues.

The disadvantage of this approach is there are no interactive features available of the report which thus results in poor user experience. Also in order to handle and understand the responses from jasper server/Pentaho server, custom coding is required in the application. In case if we had put input controls in the report, the same functionality then needs to be implemented in the application via custom coding. Aside, having many webcalls at times can be resource intensive as well.

The advantage of this approach is webservices approach gives more seamless integration since there is no security issue and no thirdparty calls (like in the case of iframe). Also in this case there are no interbrowser compatibility issues (assuming it is handled by your developer team).

c) Embedding using Jasper/Pentaho Libraries:- In this method we create JRXML/Pentaho report (prpt) files using ireport/Pentaho report designer. Compile these reporting using Java API, and then use this compiled code to generate views using Jasper Server API calls

The advantage of this method is this gives the most seamless integration. Also since all the information is present as a part of the application/server, no third party calls are required.

Disadvantage of this approach is some interactivity is lost in this method. Also the input parameters are also required to be created in the own application via custom coding.

 

Please get in touch at [email protected] for much more details about the respective approach, how to embed the same using these approaches, which was is the best suited to your needs and much more.