Pentaho BI

For a long time, we are collecting data to manage processes. Of course, we require reports to follow the process. We can do this, but now our needs change very quickly. It is not sufficient to take the raw data from the systems or the reports prepared for the previous needs and convert them with the excel application into the format we want. The data changes in the time period we spend applying this process and the results we wanted to produce have already changed. Reporting should be able to keep up with these conditions. It is almost impossible to make quick changes with monthly reports. This process needs to be automated. Instant conditions must come to light.

Retrieving the data in the format we want has become the basic requirement of our day in order to make accurate and timely decisions. In order to compete in today’s world, our aim must be to interpret the data not to collect the data. We are in a period where competition is very tough and the world is now global village.

We are aware that there is more than using data.

There are concepts and tools developed to find solutions to these problems. One of these is the BI (business intelligence) concept. Our company has preferred the Pentaho BI tool as the main element of the solution for this concept. In this article, we aim to explain the basic features of Pentaho BI tool.

Topics

  • Plug-In / Marketplace
  • Report
  • OLAP Cube
  • Dashboard

Pentaho BI
Pentaho BI is an application group that includes multiple tools. These are;

  • Business Analytics Platform: BI platform and web interface
  • Data Integration: ETL tool
  • Report Designer: Allows designing reports that will be used in the BI platform
  • Schema Workbench: Designs BI cubes
  • Aggregation Designer: Enables optimizing defined cubes

Here are some information about installation and usage.

Download the BI Server tool from the “Business Analytics Platform” tab at http://community.pentaho.com/. To run the application on a Turkish platform, you can add the following line to the “catalina.bat” in the directory “biserver-ce\tomcat\bin”.

set “JAVA_OPTS=%JAVA_OPTS% -Duser.language=en -Duser.region=US”

To start the application, run the script “biserver-ce\start-pentaho.bat”. After the application is opened, go to the address “http://localhost:8080/” in the browser.  If the application is opened properly, you will see the following screen. The version of the design tools must be compatible with the biserver version. Otherwise, you may not be able to upload a report that you designed to the server.

Then, log in as admin.

Datasource
You need a datasource to access the data through the platform, so define a datasource by following the steps “Create New> Datasource Platform”. The name of the datasource is important because you’ll be using the name when loading reports and the OLAB cube.

If you do not have the driver for the database that you will connect to, you must put the jar file of jdbc driver in the directory “biserver-ce\tomcat\lib” of the server.

Plug-In / Marketplace
One of the most beautiful features of Pentaho bi server is that it can be extended with the plug-ins. When you first install the application, you need to install this plug-in manually to access the Marketplace. To do so, download “Pentaho Marketplace” plug-in from http://www.pentaho.com/marketplace/ and then put it under the directory “\biserver-ce\pentaho-solutions\system” on your server. Then restart the application. You will now see “Marketplace” link in the main menu.

You can enrich the platform by adding different visualization tools to the application through the Marketplace. Or you can add a feature you want on the Pentaho BI platform by developing a custom plug-in.

Report
To design a report, you need the “Report Designer” tool. You can design your reports on your own computer with this tool. When your design is complete, you can upload the report to Pentaho Server.

To run the application on a Turkish operating system, download the appropriate version of the application from the Pentaho website and add the memory parameters “-Duser.language=en -Xms1024m -Xmx2048m -XX:MaxPermSize=256m”  to report-designer.bat file.

start “Pentaho Report Designer” “%_PENTAHO_JAVA%” -Duser.language=en -Xms1024m -Xmx2048m -XX:MaxPermSize=256m -jar “%~dp0launcher.jar” %*

To connect to the Oracle DB, you need to add the Oracle jdbc driver to the “lib\jdbc” directory.

To connect to the DB, follow the steps “Data> Add Data Source> JDBC” in the Report Designer interface.

After defining Datasource, you need to define the queries that you will use when designing the report. You can make these queries parametric. You can retrieve the data of the parameters you have created through a different query.

 

OLAP Cube
To design your OLAP Cube, download the appropriate package from the “Schema Workbench” tab.

As you have done when setting up the Report tool, you need to set the language and driver settings in this tool. You need to make a connection definition before you start the design. Define and save your db connection parameters in the “Options > Connection” menu.

After completing the design, upload it to the server with “File> Publish” option.

The result:

You can install the “Pivot4J”plugin, which offers better pivot tables, from the marketplace.

Dashboard
Pentaho can define advanced dashboards on Pentaho biserver. You can create a new dashboard with the “Create New> CDE Dashboard” option on the homepage. The structure of dashboards created with CDE (community dashboard editor) dashboard on Pentaho is as follows:

It consists of 3 parts:

  1. Datasource section: You need to create different types of queries here to use it in charts and other components.
  2. Components section: Here you need to define the components, such as charts, maps, etc., that you want to add to the dashboard. When you make definitions here, you are using the queries that you defined in the datasource section.
  3. Layout section: Here you determine how your dashboard should look. There are enough layout options to meet your needs for positioning the component. When you write the names you gave to the fields you created here to the “HtmlObject” part of the components you defined in the components section, you determine where the component will be displayed in the layout.

Let’s create a simple dashboard and add a chart.

First, define a query in the datasource section. Create a query by clicking on “sql over sqljndi” link in the “SQL Queries” part from the tabs on the left. Then give the name part on the right a meaningful name. Type the name of the datasource that we first defined in the JNDI property. When you type the first letter or press the down arrow key, the datasources defined in the system are listed. Create a query similar to the following one in the Query section:

select service_type_name service_type,region_name region,count from BI_BOLGE_VW where year = ${year} [This query gives the number of services by region based on service type.]

This query works according to the year parameter. In Dashboard, we will add a year parameter and filter the chart according to the value of that parameter.

In Parameters, define the parameter “Name: year, value: year, type: string”. In this way, we have defined our query.

Next, we move to the components section.

Here we create a parameter by selecting the “Simple Parameter” option from the “Generic” tab on the right. [Name: year, property value: 2012]

Then, we need to create a combo box containing years. To do this, select “Select Component” option from the “Select” tab in the right section. [Name: SelectYear, parameter: year, Listeners: years, values: 2011,2012…2016]

On the right side, we select “CCC Bar Chart” from “Charts” tab to create a bar chart. [name: areaBasedService, Listeners: year, parameters: (year, year), datasource: (query name we defined in datasource section, crosstabMode: False)]

Then we go to the layout section, where we’ll create the dashboard layout. First, we continue by choosing one of the available templates. Select a suitable template with the “Apply Template” option, which is the 2nd button on the top. I chose the one with two columns.

Then, I added a new line after the “spacer” line and named it “Criteria”. I added a column inside it and named it “YearCriterion”. We now have to go to the “HtmlObject” section of the “YearSelect” component that we define in the components section and add this information. So, the year combo box will appear in this section of the layout.

Then, we write the name information of the first column (Panel_1) in the “Body” section after the criterion to the “HtmlObject” part of the bar chart that we defined in the components section.

So we have completed our dashboard.

Note :
To show the dashboard that you have defined in a different web application in iframe, you need to make the following change:

You need to set the following parameter to true in the “biserver-ce\pentaho-solutions\system\security.properties” file.

requestParameterAuthenticationEnabled=true
In the homepage, find your dashboard in the directory where you saved it using “Browse Files” option and select it from the right side and open it with the “Open in a new window” option. Copy the address from the pop-up window. At the end of the address, add the user and password information with the following parameters.

http://:8080/pentaho/api/repos/%3Apublic%3ASteel%20Wheels%3ADashboards%3ADilaver.wcdf/generatedContent?userid=admin&password=password

In this way, you can view your dashboard in a different web application.

Harita
There are several map visualization components in Pentaho. There is also a map component inside the IvyDashboard components. Here, I’ll explain Custom> NewMapComponent and IvyVectorMap components.

IvyDC – Go to the marketplace to install Ivy Dashboard Components and find and install the plug-in. Then restart the application. You will now see it in the components section on the dashboard editor screen.

IvyVectorMap Turkey Map;
Download the jquery-jvectormap-data-turkey-en-en.js file at https://github.com/cbilgili/jVectorMap-Turkey. On your server, go to the directory “biserver-ce\pentaho-solutions\system\IvyDC\resources\components\jQVectorMapsComponent”. Move the downloaded file to this directory. Open the file “component.xml” and add the file you downloaded to “<Dependencies>” part. Add “<Value display=’Turkey’>turkey_1_mill_en</Value>” to “<CustomProperties>” under “<Values>” part. In this way, Turkey map will be displayed under maps. The maps in this component are the maps at http://jvectormap.com/maps/world/world/.

You need to add a datasource before adding IvyVectorMap to the dashboard. The data structure must be the data set like the result of the query “select city,Measure from temp_tr_map_ivy_view”, which is shown below.

In this data set, the cities are represented like this; 53: Konya, 7: Ankara, 63: Ordu, 40: İstanbul. Unfortunately, the map is not arranged according to the plate code.

Then, go to the component section and click “SVG Vector Map” link from the “Ivy IS” section in the left-side tabs to add the component. In the settings, enter the datasource name that we defined in the datasource part.

Then place the map here after creating a suitable space in the layout section. Write the name of field that you created in the layout section to HtmlObjet of JQVectorMapComponent. Then, preview.

 

Usage of NewMapComponent?
You need to define a datasource first to use this component. Datasource should contain the data that is the result of the query “select Latitude,Longitude,city name ,MEASURE total from temp_map_view”.

Then go to the components section and click “NewMapComponent” in “Custom” tab on the left side. Set the parameters as follows. Important part: Do not skip writing datasource.

Then place the component in a suitable place on the layout and preview it.

As a result, your map will look like this.