Pentaho adalah software Business Intelligence (BI) yang menyediakan data integrasi, pelayanan OLAP, reporting, dashboarding, data mining dan kemampuan ETL. Pentaho Data Integration atau Kettle adalah software dari Pentaho yang digunakan untuk proses ETL (Extraction, Transformation dan Load). PDI dapat digunakan untuk migrasi data, membersihkan data, loading dari file ke database atau sebaliknya dalam volume besar. Keunggulan PDI adalah tersedia graphical user interface dan drag-drop komponen yang dapat memudahkan user. Pentaho Data Integration memiliki tiga komponen utama, yakni spoon, pan dan kitchen. Spoon adalah user interface untuk membuat job dan transformation, kemudian pan itu program yang mengeksekusi transform dan kitchen merupakan program yang mengeksekusi job.
Transformation adalah komponen dari PDI berupa rancangan transformasi yang disusun untuk melakukan proses ETL. Ciri-cirinya, terdiri dari step (berisikan tugas-tugas), dieksekusi oleh pan (pan.bat atau pan.sh) dan format filenya (.ktr). Selanjutnya Job adalah komponen dari PDI yang menangani kontrol atas aliran tugas atau transformasi. Job memiliki ciri-ciri, bisa terdiri dari satu atau beberapa transformation dan job lain, dieksekkusi oleh kitchen (kitchen.bat atau kitchen.sh) dan format filenya (.kjb).
Menginstall database portgresql
untuk informasi cara menginstall database postgresql pada ubuntu, bisa kunjungi tautan berikut :
Cara menginstall postgresql di ubuntu 22.04
Install java jdk 8
root@uworkshop-VirtualBox:~# sudo apt install openjdk-8-jdk
Membuat user pentaho
useradd pentaho passwd pentaho usermod -aG sudo pentaho
Membuat directory yg akan digunakan untuk lokasi aplikasi pentaho
root@server:/app# sudo mkdir pentaho root@server:/app# chown pentaho: pentaho
Extract Installer
unzip pentaho-server-ee-9.3.0.0-428-dist.zip
pentaho@server:/tmp$ ls -l total 1530700 drwxr-xr-x 2 pentaho pentaho 4096 Feb 1 10:16 hsperfdata_pentaho drwxr-x--- 2 root root 4096 Feb 1 10:17 hsperfdata_root -rw-rw-r-- 1 root root 0 Dec 17 10:05 ICRC_ROLL.lck -rw-rw-r-- 1 temporary temporary 1567400311 Feb 2 08:53 pentaho-server-ee-9.3.0.0-428-dist.zip drwx------ 3 root root 4096 Dec 17 10:04 snap-private-tmp drwx------ 3 root root 4096 Dec 17 10:04 systemd-private-18a294f95f8f40cd94062a21e592974f-ModemManager.service-u3YF1r drwx------ 3 root root 4096 Dec 17 10:04 systemd-private-18a294f95f8f40cd94062a21e592974f-systemd-logind.service-hGkllk drwx------ 3 root root 4096 Dec 17 10:04 systemd-private-18a294f95f8f40cd94062a21e592974f-systemd-resolved.service-DNA9FC -rw-rw-r-- 1 root root 0 Dec 17 10:05 TRXHANDLER_ROLL.lck drwx------ 2 root root 4096 Dec 17 10:04 vmware-root_984-2999526209 pentaho@server:/tmp$ pentaho@server:/tmp$ unzip pentaho-server-ee-9.3.0.0-428-dist.zip Archive: pentaho-server-ee-9.3.0.0-428-dist.zip creating: pentaho-server-ee-9.3.0.0-428/ inflating: pentaho-server-ee-9.3.0.0-428/README.txt inflating: pentaho-server-ee-9.3.0.0-428/install.bat inflating: pentaho-server-ee-9.3.0.0-428/install.sh inflating: pentaho-server-ee-9.3.0.0-428/license.txt inflating: pentaho-server-ee-9.3.0.0-428/installer.jar pentaho@server:/tmp$ pentaho@server:/tmp$ ls -l total 1530704 drwxr-xr-x 2 pentaho pentaho 4096 Feb 1 10:16 hsperfdata_pentaho drwxr-x--- 2 root root 4096 Feb 1 10:17 hsperfdata_root -rw-rw-r-- 1 root root 0 Dec 17 10:05 ICRC_ROLL.lck drwxr-xr-x 2 pentaho pentaho 4096 Apr 12 2022 pentaho-server-ee-9.3.0.0-428 -rw-rw-r-- 1 temporary temporary 1567400311 Feb 2 08:53 pentaho-server-ee-9.3.0.0-428-dist.zip drwx------ 3 root root 4096 Dec 17 10:04 snap-private-tmp drwx------ 3 root root 4096 Dec 17 10:04 systemd-private-18a294f95f8f40cd94062a21e592974f-ModemManager.service-u3YF1r drwx------ 3 root root 4096 Dec 17 10:04 systemd-private-18a294f95f8f40cd94062a21e592974f-systemd-logind.service-hGkllk drwx------ 3 root root 4096 Dec 17 10:04 systemd-private-18a294f95f8f40cd94062a21e592974f-systemd-resolved.service-DNA9FC -rw-rw-r-- 1 root root 0 Dec 17 10:05 TRXHANDLER_ROLL.lck drwx------ 2 root root 4096 Dec 17 10:04 vmware-root_984-2999526209 pentaho@server:/tmp$
Masuk ke directory installer pentaho
pentaho@server:/tmp$ cd pentaho-server-ee-9.3.0.0-428/ pentaho@server:/tmp/pentaho-server-ee-9.3.0.0-428$ ls -l total 1531300 -rw-r--r-- 1 pentaho pentaho 305 Apr 12 2022 install.bat -rw-r--r-- 1 pentaho pentaho 1567975366 Apr 12 2022 installer.jar -rwxr-xr-x 1 pentaho pentaho 26 Apr 12 2022 install.sh -rw-r--r-- 1 pentaho pentaho 56543 Apr 12 2022 license.txt -rw-r--r-- 1 pentaho pentaho 946 Apr 12 2022 README.txt pentaho@server:/tmp/pentaho-server-ee-9.3.0.0-428$
Memulai proses instalasi dengan menjalankan perintah
pentaho@server:/tmp/pentaho-server-ee-9.3.0.0-428$ ./install.sh
Akan muncul jendela instalasi dalam bentuk GUI, diperlukan ssh client yg support x-server atau desktop untuk menampilakn GUI installasi
pentaho@server:/tmp/pentaho-server-ee-9.3.0.0-428$ ./install.sh Command line arguments: ==================== Installation started Framework: 5.1.3-84aaf (IzPack) Platform: ubuntu_linux,version=5.15.0-75-generic,arch=x64,symbolicName=null,javaVersion=11.0.21 executable perms set on file set-pentaho-env.sh executable perms set on file distribute-files.sh executable perms set on file set-pentaho-env.sh executable perms set on file set-pentaho-env.sh executable perms set on file start_hypersonic.sh executable perms set on file stop_hypersonic.sh executable perms set on file import-export.sh executable perms set on file encr.sh executable perms set on file stop-pentaho.sh executable perms set on file promptuser.sh executable perms set on file start-pentaho.sh executable perms set on file buildFlashStorage.sh executable perms set on file build.sh executable perms set on file cleanup.sh executable perms set on file compile.sh executable perms set on file buildFlashStorage.sh executable perms set on file build.sh executable perms set on file cleanup.sh executable perms set on file compile.sh executable perms set on file install-snowflake-vfs.sh executable perms set on file shutdown.sh executable perms set on file version.sh executable perms set on file catalina.sh executable perms set on file makebase.sh executable perms set on file configtest.sh executable perms set on file setclasspath.sh executable perms set on file ciphers.sh executable perms set on file tool-wrapper.sh executable perms set on file digest.sh executable perms set on file startup.sh executable perms set on file daemon.sh executable perms set on file start-pentaho-debug.sh executable perms set on file set-pentaho-env.sh executable perms set on file install_license.sh Installation finished pentaho@server:/tmp/pentaho-server-ee-9.3.0.0-428$
Edit koneksi database
pentaho@server:~$ nano /opt/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml
pentaho@server:~$ cat /app/pentaho/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--===========================================================================
HITACHI VANTARA PROPRIETARY AND CONFIDENTIAL
Copyright 2002 - 2019 Hitachi Vantara. All rights reserved.
NOTICE: All information including source code contained herein is, and
remains the sole property of Hitachi Vantara and its licensors. The intellectual
and technical concepts contained herein are proprietary and confidential
to, and are trade secrets of Hitachi Vantara and may be covered by U.S. and foreign
patents, or patents in process, and are protected by trade secret and
copyright laws. The receipt or possession of this source code and/or related
information does not convey or imply any rights to reproduce, disclose or
distribute its contents, or to manufacture, use, or sell anything that it
may describe, in whole or in part. Any reproduction, modification, distribution,
or public display of this information without the express written authorization
from Hitachi Vantara is strictly prohibited and in violation of applicable laws and
international treaties. Access to the source code contained herein is strictly
prohibited to anyone except those individuals and entities who have executed
confidentiality and non-disclosure agreements or other agreements with Hitachi Vantara,
explicitly covering such access.
============================================================================-->
<Context path="/pentaho" docbase="webapps/pentaho/">
<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="hibuser" password="p455w0rdM45ukGas"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="hibuser" password="p455w0rdM45ukGas"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
validationQuery="select 1" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="pentaho_user" password="p455w0rdM45ukGas" testOnBorrow="true"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
validationQuery="select 1"/>
<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="hibuser" password="p455w0rdM45ukGas"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
validationQuery="select 1"/>
<Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="hibuser" password="p455w0rdM45ukGas"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
validationQuery="select 1"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="hibuser" password="p455w0rdM45ukGas"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate?searchpath=pentaho_dilogs"
validationQuery="select 1"/>
<Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
validationQuery="select 1"/>
<Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="pentaho_admin" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0"
maxIdle="5" initialSize="0"
maxWait="10000" username="jcr_user" password="p455w0rdM45ukGas"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/jackrabbit"
validationQuery="select 1"/>
<!--
url="jdbc:mysql://localhost:3306/jackrabbit"/>
url="jdbc:oracle:thin:@localhost:1521/XE"/>
url="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
-->
</Context>
Edit password yang ada di dalam sql agar lebih kuat
root@server:~# nano /opt/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/data/postgresql/create_quartz_postgresql.sql root@server:~# nano /opt/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/data/postgresql/create_jcr_postgresql.sql root@server:~# nano /opt/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/data/postgresql/create_repository_postgresql.sql
Edit pg_hba tambahkan user pentaho_user sementara sebagai trust agar bisa mengexsekusi script
pentaho@server:/home/pentaho$ sudo nano /etc/postgresql/14/main/pg_hba.conf
# DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the database using some other method. # Noninteractive access to all databases is required during automatic # maintenance (custom daily cronjobs, replication, and similar tasks). # # Database administrative login by Unix domain socket local all postgres peer local all pentaho_user trust # TYPE DATABASE USER ADDRESS METHOD
Lalu restart postgresql server
pentaho@server:/home/pentaho$ sudo systemctl restart postgresql
Import database
root@server:~# sudo -u postgres psql -a -f /opt/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/data/postgresql/create_quartz_postgresql.sql root@server:~# sudo -u postgres psql -a -f /opt/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/data/postgresql/create_jcr_postgresql.sql root@server:~# sudo -u postgres psql -a -f /opt/pentaho/pentaho-server-ee-9.3.0.0-428/pentaho-server/data/postgresql/create_repository_postgresql.sql
Membuat direktory (Jika ada error tidak bisa membuat directory)
pentaho@uworkshop-VirtualBox:~$ mkdir -p /home/pentaho/.pentaho/caches pentaho@uworkshop-VirtualBox:~$ mkdir -p /home/pentaho/.pentaho/metastore