My old Pentaho CDE dashboard workout view in 3 devices

TEST View -1

TEST View -2 

Learn Pentaho BI suite(CE) - Pentaho Schema Workbench - Analysis - OLAP - Cubes - Multi Dimensional Databases course structure

Learn Multi Dimensional Database using Pentaho mondrian OLAP Cubes.

 

* Click Images and navigate to get better view of the content.

Sample Slides : 

NOTE : Adding course content in construction with more topics and advanced concepts.

Learn Pentaho BI suite - Pentaho Data Integration (a.k.a Kettle ETL) course structure

Click the images to get best view of the content.

Download Detailed Course content here

MySQL : field doesn't have a default value in MySql when inserting values into a table

On the command prompt issue below command then try inserting the values.

mysql> set @@sql_mode = '  ';
Query OK, 0 rows affected (0.00 sec)

Example :
mysql> INSERT INTO nb (id) VALUES(3);
Query OK, 1 row affected, 1 warning (0.00 sec)

References:
https://netbeans.org/bugzilla/show_bug.cgi?id=190731

Tip : Install mysql driver in Pentaho Data Integration (Kettle)

1) Download mysql-connector-java-gpl-5.1.35.msi from

http://dev.mysql.com/downloads/connector/j/

2) Execute the .msi file. (As soon as you click on run button it executes and disappears). 

3) It'll create MySQL folder inside "Program Files(x86)". (Not the "Program Files")

    Go to below location "C:\Program Files (x86)\MySQL\Connector.J 5.1" and find 

     the "mysql-connector-java-5.1.35-bin.jar" file.

4) Copy this driver to lib folder of kettle .

i.e.,. copy driver in : pdi-ce-5.4.0.0-128\data-integration\lib

5) Start PDI and try giving connection to a mysql database .

Example : 

Resources : 

1) http://stackoverflow.com/questions/18894416/install-mysql-jdbc-driver-msi

Self-Signed SSL/TLS Configuration HOW-TO with Pentaho CE 5.3 BA Server || change http://localhost:8080/pentaho TO https://localhost:8443/pentaho/Login

It's a weekend playing with Pentaho CE 5.3 Business Analytics  server. Yeah..!!! it is. I love playing with Pentaho  BI suite though it is a small outcome or a bigger. I can't classify this post is a bigger or smaller but I just want to share how I did this and hope this will helpful to someone, somewhere across the globe who is/are looking for the same.

Special thanks to Madhava & Piyush Bhatt for making me to explore this. 

Reference site to this article : https://tomcat.apache.org/tomcat-7.0-doc/ssl-howto.html

Technology : 

* Pentaho BA Server CE 5.3

* Tomcat-7

* Java 1.7 (Oracle Java)

* Operating System : Windows-7 Home Premium. 
* Web browsers used to test : Google Chrome & Internet Explorer 

Aim of the article : 

Install SSL certificate to Pentaho BA Server 

i.e., change http://localhost:8080/pentaho TO https://localhost:8443/pentaho

What is Secure Sockets Layer (SSL) ?

• It is  technology which allow web browsers and web servers to communicate over a secured connection. 

• This means that the data being sent is encrypted by one side, transmitted, then decrypted by the other side before processing. 

• This is a two-way process, meaning that both the server AND the browser encrypt all traffic before sending out data.

• Another important aspect of the SSL/TLS protocol is Authentication. 

• This means that during your initial attempt to communicate with a web server over a secure connection, that server will present your web browser with a set of credentials, in the form of a "Certificate", as proof the site is who and what it claims to be. In certain cases, the server may also request a Certificate from your web browser, asking for proof that you are who you claim to be. This is known as "Client Authentication,"

•  Although in practice this is used more for business-to-business (B2B) transactions than with individual users. Most SSL-enabled web servers do not request Client Authentication.

Step by Step procedure :

1) Make sure java 1.7 is installed in machine & set JAVA_HOME in environment variables. 

2) Also make sure Pentaho BA server is running as a test drive before installing SSL certificate. 

3) There are two types of certificates we can install. 

     a) Self- Signed certificate ( Not trusted)

     b) Certificate from a certificate authority. 

4) This procedure is with Self-Signed based on self-signed certificate. 

5) Navigate to the bin folder of JAVA installed from command prompt in Admin mode. 

   Example : 

  C:\Program Files\Java\jdk1.7.0_79\bin

6) To create a new JKS keystore from scratch, containing a single self-signed Certificate, execute the following from a terminal command line:

"%JAVA_HOME%\bin\keytool" -genkey -alias tomcat -keyalg RSA

Example : 

  C:\Program Files\Java\jdk1.7.0_79\bin>ketytool.exe -genkey -alias tomcat -keyalg RSA

7) The command will ask us enter password  and few other details. The default password we can use is "changeit" or developer is flexible to give any name. 

Example : changeit (Enter the password twice as it asks). 

8) As shown in above command line utility finish giving all the other details & the last password could be same as first password or it could differ. 

9) As soon as it re-directs to the path back, it will generate a certificate named as ".keystore" in system(computer) user location. 

Example : 

Location of User : C:\Users\sadakar\ (file directory)

File Name to find : .keystore

Note that .keystore is the self-signed certificate generated. 

10) Now, install the above certificate to pentaho tomcat server. i.e., configure the certificate to the tomcat server.xml file.

Location of server.xml file : 

E:\2_Archive_Installed\pentaho\biserver-ce-5.3.0.0-213\biserver-ce\tomcat\conf

Add below code : ( There is already a piece of code available in the file , un-comment the code and add new attributes to the tag). 

The final code should look like as below. 

<!-- Define a SSL HTTP/1.1 Connector on port 8443

         This connector uses the JSSE configuration, when using APR, the 

         connector should be using the OpenSSL style configuration

         described in the APR documentation ${user.home}/.keystore-->

    

    <Connector 

                       URIEncoding="UTF-8" 

                       port="8443" 

                       protocol="HTTP/1.1" 

                      SSLEnabled="true"

                      maxThreads="200" 

                      scheme="https" 

                      secure="true"

             keystoreFile="C:\Users\sadakar.p\.keystore" 

                     keystorePass="changeit"

                     clientAuth="false" 

                     sslProtocol="TLS" />

NOTE : 

The .keystore file is hard coded - we can replace it by  ${user.home}/.keystore

11) Start the pentaho BA server 

(E:\2_Archive_Installed\pentaho\biserver-ce-5.3.0.0-213\biserver-ce\start-pentaho.bat)

12) Go to any web browser and type this URL : https://localhost:8433

Sample pentaho login screen with self-signed certificate installed 

(Google Chrome)

14) Internet Explorer output

a) 

b) 

15) Sample pentaho login screen with out certificate installed

(Google Chrome)

IMP NOTE :

This is not a production related document instead a development experiment. 

* JKS -  Java Key Storke - A repository of security certificates. 

- Sadakar Pochampalli

Integration work out : USE CASE solved with Switch/Case, Merge Join , If field value is Null , Calculator Steps in Pentaho Data Integration

Hi ,

This post will explain the usage of various components with a USE CASE from community threads.

USE CASE thread : 
URL : 
http://forums.pentaho.com/showthread.php?187380-Data-need-to-be-filtered-and-sum-market-value-and-shares-based-on-Transaction-value&p=408478&posted=1#post408478

USE CASE : 
(A CSV file tab separated input).
Entity_Id Account_Name Trans_Code Total_Share Market_Value
10001 Growth Fund Buy 100 1000
10001 Growth Fund Sell 100 1000
10002 Aggressive Fund Buy 1300 13000
10002 Aggressive Fund Sell 450 4500
10003 Income Fund Buy 250 2500
10004 Brokerage Fund Buy 200 2000
10004 Brokerage Fund Sell 100 1000

USE CASE OUTPUT ( Subtraction for Total_Share(Buy-Sell) & Subtraction for Market_Value(Buy-Sell). 

Entity_Id Account_Name Total_Share Market_Value
10001 Growth Fund
10002 Aggressive Fund   850          8500
10003 Income Fund           250          2500

10004 Brokerage Fund  100                 1000

Design flow of ETL 

Quick understanding of steps used in above USE CASE 

1) Switch/Case
Detailed Information on Switch/Case :
( http://wiki.pentaho.com/display/EAI/Switch-Case)

2) Multi way Merge Join
Detailed Information at :
( http://wiki.pentaho.com/display/EAI/Multiway+Merge+Join )

3) If field value is Null
Detailed Information at :
( http://wiki.pentaho.com/display/EAI/If+field+value+is+null )

4) Calculator
Detailed Information at :
(http://wiki.pentaho.com/display/EAI/Calculator)

Download files : 
( Input TEXT FILE + .ktr file) - Click Me

NOTE : 
Edit the input file path in .ktr file as per your system environment and explore it by running the file.

I hope this post has given some idea on steps subjected by seeing at the images provided.

What if there are more than 1 row with the same Id for sells or buys ? 
Will this procedure works ? ? NO

Write your comments for any queries related..

- Sadakar Pochampalli

Tip : Single Row normalize example in PDI

This post is written based on a thread from community forum .

(http://forums.pentaho.com/showthread.php?187160-Identify-duplicate-column-value-from-single-row)

USE CASE : 

EXPECTED OUTPUT 

Transformation Design :

Sample KTR file download :  Click HERE

References :
1)  http://wiki.pentaho.com/display/EAI/Row+Normaliser
2) http://wiki.pentaho.com/display/EAI/Row+denormaliser
3) http://rpbouman.blogspot.in/2012/03/generic-normalizer-step-for-kettle.html
4) http://stackoverflow.com/questions/15232294/converting-row-columns-to-rows-with-pentaho-kettle-pdi

Tip : File path as variable in kettle.properties file to read excel files in Pentaho Kettle

In this post we will see how we can make file path as variable in kettle.properties file. 

Environment : 

OS Windows 7, Kettle ETL(PDI) CE 5.3, Excel files. 

Problem Statement : 

How to give file directory name as variable (PATH for files)

Solution  1 : By selecting the predefined variables from the drop down. 

${Internal.Transformation.Filename.Directory} 

Solution 2 : 

Another way with variable defined in kettle.properties file

1. Create a variable in kettle.properties file. 

    Location of the file :  C:\Users\sadakar.p\.kettle

2. Variable Name to give in kettle.properties file is (for eg) : PATH_FOR_DATA

    Value of the variable would be the path of the directory where files stores. 

  i.e., 

PATH_FOR_DATA=C:/Users/sadakar.p/sadakar drive/BI/Pentaho/1_Kettle/DWH_INHOUSE/Data/Education/

3. Now design transformation with Microsoft Excel input and table(or dummy step) step as shown in below image and press Control+ Space to get the available variables list in selected files tab(File/Directory). 

 

In this example we have to chose the PATH_FOR_DATA variable created in kettle.properties file. 

4. Test whether the data from all excel sheets is populating or not by clicking on preview button(one should get the field names for the first time with hard located path). 

NOTE : 

1) A restart of Kettle(spoon) is required once the variable is defined in kettle.properties file other wise the in the list of variables it will not populate. 

:-) 

Pentaho CTools Introduction for Business Analysts (Quick selected points from webdetails.pt) - Download PPT

I have a prepared a very small PPT for Business Analysts & for BI consultants to introduce the features of Open source Dashboard tool. (C-Tools).

Resource for the PPT is:  Pentaho Webdetails site.

I hope the selected points from http://webdetails.pt/ site will give you the quick information to demonstrate or to prepare a like one.

Quick Snapshot :

Download PPT: Click Here


- Sadakar Pochampalli

Pentaho Data Integration Basics - 6 - (DWH) : A simple time dimension table creation using Kettle tool for Data warehouse

This post will talk about developing a basic time dimension table in a warehouse.

For detailed, time_dimension tables creation find reference links listed at the end of this post.


Software setup :  PDI 5.3, PostgreSQL

Schema diagram for time dimension table : (part of DataMart Design for time dimension table)

ETL design flow in PDI(a.k.a Kettle)

Step by Step procedure
Download the ktr file and have a look into each of the step.

will updating the procedure steps 

Download Files : 
Transformation + PostgreSQL .backup file of dim_time table


References :
1) http://type-exit.org/adventures-with-open-source-bi/2010/09/building-a-detailed-date-dimension-with-pentaho-kettle/
2) http://www.fabianschladitz.de/25-kommentare-zur-welt/software/375-howto-create-a-date-dimension-with-pdi-kettle.html
3) http://forums.pentaho.com/showthread.php?90452-Auto-generated-date-dimension-tables
4) http://forums.pentaho.com/showthread.php?74831-How-to-Date-Dimension
5) http://stackoverflow.com/questions/18841625/pentaho-kettle-conveting-month-numeric-value-to-month-name

Tip : Kettle ETL : Get only first string in a comma separted text - Modified Java Script Step Example

Problem Statement : 
Get first sting in below text value in a field
"Children out of school, primary, female"
Solution : 
Children out of school

Tool set : PDI 5.3 CE, Excel Data File(or Table input)


1) Get data from input file or input table ( Input-> Table input step).
   Note that the filed name is : indicator_name from which we want to get first string from a comma separated text.   

2) Take Modified Java Script Value step (Scripting -> MJSV) and write below expression
var indicator_sub_name = indicator_name.split(",")[0]

* Hop Table input to MJSV
* After writing expression click on "Get fields" button in MJSV
* The variable will be a new stream filed to the next step

3) Add select values step and preview the transformation.( ignore the unwanted fields in ignore tab).

Quick Image:

Hope this tip helps some one ...!!!
:-)

Pentaho Data Integration(CE): Basics-4 : Variables in Pentaho Data Integration (a.k.a Kettle ETL)

In this post you will find the basic understanding of variables in pentaho kettle ETL.

Ways to define variables in Kettle

• Variables can be used throughout Pentaho Data Integration, including in transformation steps and job entries.

1) Variable can be defined as parameters(Named parameters)

2) Variables can be defined in Kettle properties file with key value paired syntax

3) Also in ,Previous job or transformation (if  transformation is going to be called from a job)

4) Variables section of the "Execute transformation window" or "Execute job" window when click on Run button.

How to use variables ?

• Grabbing variables using the Get Variable step  OR
• By specifying meta-data strings like 
• ${VARIABLE} - Windows Style (OR)
•  %%VARIABLE%% - Linux Style

 Refer below links for more information on variables 
1) http://wiki.pentaho.com/display/COM/Using+Variables+in+Kettle
2) http://wiki.pentaho.com/display/EAI/.07+Variables

In addition to above links, below information will be helpful to understand.

Variables in kettle.properties file with an example:

1) Define variables as key value pairs in kettle.properties file.
2) Location of the file in windows is : C:\Users\sadakar.p.SADAKAR_HYD\.kettle & the file name is kettle.properties
 Example : 

3) For example, You want to provide database connection details through variables. Define the configuration details as shown in above image in kettle.properties file.
Connecting to foodmart database of postgresql server
i.e., HOST=localhost
       DATABASE=foodmart
     USER_NAME=postgres

4) Now take a new transformation & get "Table Input" step from Input section category to the canvas
5) Double click to get it's properties and provide the above created variables in the respective placeholders.
6) Use Ctrl+Space button to get the list of all variables. Find the variables coming from kettle.properties and provide them in the text boxes.

6) Test the connection & see the magic..

Variables as parameters :

1) You can also use variables as parameters.
2) Example : select all the employees belonging to a particular country.. i.e., every time you pass a country dynamically you have to the results from employee table.
3) Take a new transformation and save it.. Press control+T to get the properties of transformation and define a parameter. For example : Define "VAR_COUNTRY" as a parameter and provide default value as "Mexico"
4) Now take a "Table input component" and give all the connections of postgres foodmart database and test it.
5) In query area write this select statement :
SELECT * FROMcustomer WHERE country like '${VAR_COUNTRY}'

NOTE : You should check "Replace Variables in Script"

6) Complete the transformation by adding a dummy step to the input step and have a preview.
7) select the dummy step to view it's output in "Execution Results" step. You will find the only "Mexico results "

8) If you pass other parameter value like "USA" when you run the transformation, you will all the result set for "USA" country. 

Variables section of "Execute transformation window" or "Execute job window" when click on Run button. 

Take the same example of above scenario with a small change.. 

Remove parameter from transformation settings and save it. Execute the transformation by clicking "Run button". In "Execute a transformation" window you can find the parameter used in the query. 

Provide the value for that parameter and test the output. 

the scenario is shown in below image.

Pentaho Data Integration(CE): Basics-5 : Scheduling Community Kettle ETL Jobs in Winodws 7 or 8

In this post we will see the basics of how to schedule a Job in Windows environment using "Windows Task scheduler".

Prior to scheduling jobs or transformations either in linux or windows one should know how to execute jobs/transformations from command line by passing arguments or parameters or variables.

1) Design & develop transformation or job.
2) Make transformation or job dynamic using parameters or variables.
3) Execute the transformation or job from command line by passing parameter or variables.
4) Prepare a list of commands in a batch file.
5) Schedule the .bat file in windows using "Task Scheduler" Tool.


Transformations are used to process the rows of data flow whereas jobs are used for high level flow control hence we will be designing a transformation to process some data & then use this transformation in job.
Finally we will be seeing how to schedule job in Windows.
 
 Design & develop transformation or job

A Simple scenario : select table content from database and store in a file. Each run should fetch different table content by passing table name as a parameter.

Transformation Design :  Image-1

 
 Job Design : Image-2

Transformation :
1) Click Crtrl+N to create a new transformation and save it as "Parameters.ktr"  and then press "Ctrl+T" to get its settings.
2) Define a parameter "TABLE_NAME" under parameters section  and provide description & default value.
3) Take a "Table input" component and connect to foodmart database by providing all the details.
4) Write a simple query  : For example : SELECT * FROM ${TABLE_NAME}
5) Check "Replace Variables in Script"
6) Add dummy & Text files as shown in image-1
7) Execute it from GUI and command prompt by passing different values for table names.
For example on the command prompt: 

D:\Softwares Archive Installed\Pentaho\pdi-ce-5.2.0.0-209\data-integration>Pan.bat /file:C:\Users\sadakar.p\Desktop\Parameters.ktr "-param:TABLE_NAME=sales_fact
_1997" 

8)

9) Repeat step-8 & step-9 with different value (i.e., by passing different table name) on the command prompt.

Job
1) Ctrl+Alt+N to create a new job and save it as "Parameters_job.kjb" as shown in image 2.
2) Ctrl+J to get job settings and define parameter as "TABLE_NAME".
3) Connect START job entry -> Transformation  entry -> success entry as shown in image-2
4) Double click on "transformation" entry & provide the path of the transformation then go to parameters tab and click on "Get parameters" button.
5) Now, save the job & run from GUI and command prompt.
6) For example : 
D:\Softwares Archive Installed\Pentaho\pdi-ce-5.2.0.0-209\data-integration>Kitchen.bat /file:C:\Users\sadakar.p\Desktop\Parameters_job.kjb "-param:TABLE_NAME=sa
les_fact_1997"

7) Now you can conclude that the jobs and transformations are running fine.

Preparing a batch file :

d:
cd /d "D:\Softwares Archive Installed\Pentaho\pdi-ce-5.2.0.0-209\data-integration\"
call Kitchen.bat /file:E:\Explore\Kettle\2_WindowsScheduling\Parameters_job.kjb "-param:TABLE_NAME=region" -logfile=E:\Explore\Kettle\2_WindowsScheduling\log.txt
exit
 

Windows Scheduling :
1) Go to "Task Scheduler" in windows (simply type it in search box) and click on "Create Basic Task".

2)

3)

4)

5)

6)

7) Next click on finish

8)

At the time of execution it'll pop a window saying that the execution has started.

In this way we can schedule Kettle community jobs in windows 7 or 8 .

:-) comments & suggestions are most welcome to improve this article :-)

Thank you for spending your time on this page.