pentaho: analysis with mondrian
TRANSCRIPT
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 1 / 50
DAFTAR ISI
1. Multidimensional Modelling dan OLAP .............................................................................. 4
1.1. Pendahuluan .................................................................................................. 4
1.2. Komponen-komponen Multidimensional Modelling ..................................................... 4
1.2.1. Cube .................................................................................................... 4
1.2.2. Dimension ............................................................................................. 5
1.2.3. Dimension Levels ..................................................................................... 5
1.2.4. Member ................................................................................................ 6
1.2.5. Measures ............................................................................................... 7
1.2.6. Cell ..................................................................................................... 7
2. Mondrian / Pentaho Analysis Server ................................................................................. 8
2.1. Perkenalan .................................................................................................... 8
2.2. Arsitektur Mondrian ......................................................................................... 8
2.3. Tahapan Instalasi Mondrian ................................................................................ 9
2.4. Instalasi Apache Tomcat ................................................................................. 10
2.5. Setting Environment Variable untuk JAVA_HOME dan JRE_HOME ................................. 10
2.6. Menjalankan Tomcat ...................................................................................... 12
2.7. Instalasi Paket Web Mondrian ........................................................................... 13
3. Schema Mondrian ........................................................................................................... 14
3.1. Pendahuluan ................................................................................................ 14
3.2. Definisi dimension, measure, hierarchy, member dan cell ......................................... 14
3.2.1. Cube .................................................................................................. 14
3.2.2. Measure .............................................................................................. 14
3.2.3. Calculated Member ................................................................................ 15
3.2.4. Dimension & Hierarchies (Shared) .............................................................. 15
3.2.5. Dimension & Hierarchies (Specific) ............................................................. 15
3.2.6. Virtual Cube......................................................................................... 16
3.2.7. Role .................................................................................................. 16
3.2.8. Properties ........................................................................................... 16
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 2 / 50
4. Pentaho Schema Workbench ......................................................................................... 17
4.1. Instalasi ..................................................................................................... 18
4.2. Melengkapi Driver Database ............................................................................. 18
4.3. Menjalankan Schema Workbench dan Membuat Koneksi Database ............................... 19
4.4. Contoh : Mapping Shared Dimension & Hierarchy’s Levels ......................................... 19
4.5. Contoh : Mapping Cube ................................................................................... 23
4.6. Mencoba Schema dengan mengeksekusi MDX ......................................................... 26
5. JPivot .............................................................................................................................. 27
5.1. JPivot ........................................................................................................ 27
5.2. JPivot Toolbar .............................................................................................. 27
5.3. Tag JSP ...................................................................................................... 27
5.4. Pembuatan dan Peletakan File JSP JPivot ............................................................ 28
5.5. Latihan : OLAP Navigator................................................................................. 30
6. MDX (Multi Dimensional Expression) .............................................................................. 32
6.1. MDX (Multi Dimensional Expression) .................................................................... 32
6.2. MDX Query & Syntax ...................................................................................... 32
6.3. Tuple dan Set .............................................................................................. 32
6.4. MDX Expression ............................................................................................ 33
6.5. MDX Functions .............................................................................................. 33
6.6. Navigasi Member / Node ................................................................................. 34
6.7. Filter dan Slicer ............................................................................................ 35
6.8. Sorting ....................................................................................................... 35
6.9. Functions Yang Terkait dengan Dimensi Waktu ...................................................... 36
6.10. Latihan : Contoh Penggunaan Umum ................................................................... 36
6.11. Ekspresi “With Member” ................................................................................. 36
6.12. Ekspresi “With Set” ....................................................................................... 37
6.13. Calculated Member dan Named Set .................................................................... 37
6.14. Ekspresi Conditional....................................................................................... 38
7. Pentaho BI Server .......................................................................................................... 39
7.1. Pentaho BI Server.......................................................................................... 39
7.2. Instalasi BI Server 3.6 Community Edition ............................................................. 39
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 3 / 50
7.3. Administration Console ................................................................................... 39
7.4. Susunan Folder BI Server ................................................................................. 41
8. Pentaho Design Studio & Xaction ................................................................................... 42
8.1. Pentaho Solution dan file .XACTION .................................................................... 42
8.2. Instalasi ..................................................................................................... 42
8.3. Project sebagai Placeholder ............................................................................. 43
8.4. Membuat File XAction Pertama dari Wizard .......................................................... 43
8.5. Define Process ............................................................................................. 45
8.6. Memasukkan Komponen dan Mengatur Flow .......................................................... 46
8.7. Latihan : Integrasi Mondrian Schema ke dalam BI Server ........................................... 47
9. Pentaho Aggregation Designer ....................................................................................... 49
9.1. Membuat Koneksi Skema ke Database ................................................................. 49
9.2. Menguji coba berbagai skenario aggregate dan optimasi waktu query ........................... 50
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 4 / 50
1. Multidimensional Modelling dan OLAP
1.1. Pendahuluan
OLAP (Online Analytical Processing) adalah sistem pelaporan analisis yang dirancang khusus
untuk melihat data analisis dari berbagai sisi secara cepat, menyeluruh dengan menggunakan
query khusus.
Kerangka / model implementasi OLAP yang paling banyak diadopsi adalah model multi
dimensi dengan perspektif Cube, Dimension, dan Measures.
1.2. Komponen-komponen Multidimensional Modelling
1.2.1. Cube
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 5 / 50
1.2.2. Dimension
1.2.3. Dimension Levels
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 6 / 50
1.2.4. Member
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 7 / 50
1.2.5. Measures
1.2.6. Cell
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 8 / 50
2. Mondrian / Pentaho Analysis Server
2.1. Perkenalan
Mondrian merupakan OLAP server yang berjalan di atas platform Java dan bersifat free open
source dan telah digunakan di berbagai framework BI seperti Pentaho dan Jasper.
Inisiator Mondrian adalah Julian Hyde (http://julianhyde.blogspot.com/) dan sejak diakuisisi
oleh Pentaho tetap merupakan project leader dari Mondrian. Mondrian juga dikenal dengan
nama Pentaho Analysis.
2.2. Arsitektur Mondrian
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 9 / 50
Mondrian
Mondrian OLAP Engine
Relational
Database
(RDBMS)
JDBC Driver .jar FilesMondrian Schema File
MDX Query
OLAP
Resultset
2.3. Tahapan Instalasi Mondrian
Instalasi Java Runtime Environment (JRE) atau Sun Java Development Kit (JDK)
Instalasi aplikasi web container Java, Apache Tomcat
Intalasi paket web Mondrian : mondrian.war
Database contoh untuk PHI-Minimart (versi OLTP dan OLAP)
Copy contoh file Skema dan JSP dari DVD
Jalankan Mondrian via browser di URL http://localhost:8080/mondrian
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 10 / 50
2.4. Instalasi Apache Tomcat
Download paket Apache Tomcat terbaru dari situs http://tomcat.apache.org, misalnya nama
paket yang didownload adalah “apache-tomcat-6.0.18.zip”.
Ekstrak ke folder yang diinginkan misalnya di c:\ apache-tomcat-6.0.18. Folder ini untuk selanjutnya dirujuk dengan nama [Apache Tomcat].
Seperti pada workbench, kita bisa meng-copy driver JDBC dari database yang dibutuhkan ke folder “lib” dari Apache Tomcat.
Selesai
2.5. Setting Environment Variable untuk JAVA_HOME dan JRE_HOME
Tomcat membutuhkan variable JAVA_HOME atau JRE_HOME sebagai acuan lingkungan Java. Berikut adalah langkah setup variable tersebut di Windows XP Professional
Dari Explorer, klik kanan icon My Computer dan pilih properties
Klik tab “Advanced” dari dialog “System Properties” yang muncul. Klik “Environment
Variables”
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 11 / 50
Akan muncul dialog “Environment Variables”. Klik “New” pada System / User variables.
Masukkan variable JAVA_HOME (JDK) atau JRE_HOME yang merujuk ke folder instalasi
Java Anda.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 12 / 50
Klik “OK”
Selesai
2.6. Menjalankan Tomcat
Jalankan command prompt
Masuk ke folder “bin” dari instalasi Tomcat Anda (c:\ apache-tomcat-6.0.18\bin)
Jalankan perintah “catalina.bat run”.
Tunggu beberapa saat sampai muncul informasi “Server Startup in … ms “
Jalankan browser favorit Anda dan buka halaman http://localhost:8080. Apabila muncul layar seperti pada gambar berikut maka instalasi sudah berhasil dilakukan.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 13 / 50
2.7. Instalasi Paket Web Mondrian
Jalankan Apache Tomcat
Ekstrak paket kompresi Mondrian (mondrian-3.1.1.12687.zip) dari DVD atau download dari http://sourceforge.net/projects/mondrian/
Copy file “mondrian.war” yang terdapat di folder “lib” hasil ekstraksi ke folder [Apache Tomcat]/webapps
Tunggu beberapa saat. Akan muncul folder “mondrian” dan pada console akan muncul info
“Deploying web application archive mondrian.war”
Jalankan browser Anda dan buka URL http://localhost:8080/mondrian. Jika instalasi berhasil akan muncul halaman berikut ini.
Selesai
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 14 / 50
3. Schema Mondrian
3.1. Pendahuluan
Schema di Mondrian merupakan suatu file teks XML yang mendefinisikan model logis dari
cube , hirarchies, levels, members dan pemetaan dari model ini ke tabel fisik database
relasional.
Tabel-tabel fisik yang merupakan sumber data ini biasanya menggunakan pemodelan star
schema dimana suatu tabel fisik dikelilingi oleh beberapa tabel dimensi tanpa percabangan lebih
jauh.
3.2. Definisi dimension, measure, hierarchy, member dan cell
Pada konstruksi bahasa pengolahan data relasional seperti SQL umumnya dirancang untuk
pengambilan data dalam 2 dimensi : kolom (column) dan baris (row).
Sedangkan di dalam data multidimensi (multidimensional data) struktur direpresentasikan lebih
dari 2 dimensi yang disebut cube. Semua terminologi dasar yang terkait dapat digunakan di
dalam MDX.
Skema dari Mondrian mendefinisikan pemetaan dari model logika dari cube, hirarchy, dan
member ke tabel dan kolom dari database. Skema ini berbentuk file XML.
Berikut adalah contoh cara mendefinisikan elemen-elemen dari model multidimensional di
dalam skema:
3.2.1. Cube
<Cube name="Penjualan" cache="true" enabled="true"> <Table name="fact_penjualan"> </Table> … </Cube>
3.2.2. Measure
<Measure name="Total Unit" column="jumlah_pembelian" formatString="#,##0" aggregator="sum"> </Measure>
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 15 / 50
3.2.3. Calculated Member
<CalculatedMember name="YTD" formula="Sum(Ytd([Waktu].CurrentMember), [Measures].[Total Unit])" dimension="Measures"> <CalculatedMemberProperty name="FORMAT_STRING" value="#,##0"> </CalculatedMemberProperty> </CalculatedMember>
3.2.4. Dimension & Hierarchies (Shared)
<Dimension type="TimeDimension" name="Waktu"> <Hierarchy hasAll="true" allMemberName="Semua Waktu" primaryKey="sk_waktu"> <Table name="dim_waktu"> </Table> <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"> </Level> <Level name="Quarter" column="quarter" type="Numeric" uniqueMembers="true" levelType="TimeQuarters" hideMemberIf="Never"> </Level> <Level name="Month" column="month" type="Numeric" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"> </Level> <Level name="Hari" column="day_of_month" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"> </Level> </Hierarchy> </Dimension>
3.2.5. Dimension & Hierarchies (Specific)
<Cube name="Karyawan" defaultMeasure="Total"> <Table name="hrd_fact_karyawan" /> <Dimension name="Dim_Karyawan" foreignKey="sk_karyawan"> <Hierarchy hasAll="true" allMemberName="Semua Karyawan" primaryKey="sk_karyawan" defaultMember="Semua Karyawan"> <Table name="hrd_dim_karyawan"/> <Level name="Karyawan" column="sk_karyawan" nameColumn="nm_pgw" uniqueMembers="false"/> </Hierarchy> </Dimension> …. </Cube>
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 16 / 50
3.2.6. Virtual Cube
<Cube name="Sales" defaultMeasure="Unit Sales"> ... <CalculatedMember name="Profit" dimension="Measures"> <Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula> ... </CalculatedMember> </Cube> <VirtualCube name="Warehouse and Sales" defaultMeasure="Profit" > ... <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Profit]"/> </VirtualCube>
3.2.7. Role
<Role name="California manager"> <SchemaGrant access="none"> <CubeGrant cube="Sales" access="all"> <HierarchyGrant hierarchy="[Store]" access="custom" topLevel="[Store].[Store Country]"> <MemberGrant member="[Store].[USA].[CA]" access="all" /> <MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="none" /> </HierarchyGrant> <HierarchyGrant hierarchy="[Customers]" access="custom" topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]"> <MemberGrant member="[Customers].[USA].[CA]" access="all" /> <MemberGrant member="[Customers].[USA].[CA].[Los Angeles]" access="none" /> </HierarchyGrant> <HierarchyGrant hierarchy="[Gender]" access="none" /> </CubeGrant> </SchemaGrant> </Role>
3.2.8. Properties
<Level name="MyLevel" column="LevelColumn" uniqueMembers="true"> <Property name="MyProp" column="PropColumn"/> <Level/>
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 17 / 50
4. Pentaho Schema Workbench
Pentaho Schema Workbench (PSW) adalah aplikasi GUI (Graphical User Interface) yang digunakan
untuk merancang skema XML Mondrian.
Schema Workbench
Mondrian OLAP Engine
Schema Workbench
Relational
Database
(RDBMS)
JDBC Driver .jar FilesMondrian Schema File
MDX Query
OLAP
Resultset
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 18 / 50
4.1. Instalasi
Copy file PSW yaitu “psw-ce-3.0.4.stable.zip” dari DVD atau download paket Schema
Workbench terbaru dari project Mondrian di http://sourceforge.net/projects/mondrian.
Ekstrak ke folder yang diinginkan misalnya di c:\schema-workbench.
Selesai
4.2. Melengkapi Driver Database
Schema Workbench tidak datang dengan driver database apapun di dalam paketnya sehingga kita
perlu melengkapinya sendiri. Berikut adalah caranya :
Download driver yang dibutuhkan dari Internet untuk database yang Anda gunakan. Misalnya mysql-connector-xxx.jar untuk database MySQL (http://www.mysql.com). Sedangkan untuk
SQL Server bisa download driver open source yaitu jtds-xxx.jar dari
http://jtds.sourceforge.net.
File-file tersebut juga bisa diambil dari folder “libext/JDBC” dari distribusi Kettle / Pentaho Data Integration apabila Anda sudah melakukan instalasi Kettle sebelumnya.
Letakkan file-file driver tersebut di folder [SCHEMA-WORKBENCH]/drivers
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 19 / 50
Selesai
4.3. Menjalankan Schema Workbench dan Membuat Koneksi Database
Jalankan workbench.bat sampai muncul aplikasi GUI dari Schema Workbench.
Pilih menu “Tools” > “Preferences”
Masukkan spesifikasi koneksi (Driver Class Name, Connection URL, User dan Password)
sesuai dengan spesifikasi database OLAP Anda. Klik tombol “Accept”.
Spesifikasi untuk database MySQL o Driver Class Name : com.mysql.jdbc.Driver
o Connection URL : jdbc:mysql://[HOSTNAME]/[DATABASE]
o User name : username dari database MySQL Anda
o Password : password dari database MySQL Anda
Spesifikasi untuk database SQL Server
o Driver Class Name : net.sourceforge.jtds.jdbc.Driver
o Connection URL : jdbc:jtds:sqlserver://[HOSTNAME]/[DATABASE]
o User name : username dari database SQL Server Anda
o Password : password dari database SQL Server Anda
o Selesai
4.4. Contoh : Mapping Shared Dimension & Hierarchy’s Levels
Pilih menu File | New | Schema. Akan muncul ruang kerja untuk perancangan. Pada contoh kali ini data OLAP adalah hasil data warehouse dari contoh database OLTP PHI-Minimart.
Pada panel kiri, klik kanan pada node “Schema” dan pilih menu “Add Dimension”
Mapping Tabel : akan muncul node “New Dimension 0”. Klik simpul untuk membuka node
tersebut sampai muncul node “Table”. Pilih tabel dimensi yang Anda inginkan, misalkan
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 20 / 50
“dim_cabang”. Perhatikan tanda x akan hilang setelah tabel dipilih.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 21 / 50
Klik node “Hierarchy”. Masukkan input seperti pada gambar berikut . Perhatikan bahwa primaryKey mutlak dimasukkan.
Penamaan Dimensi : klik node “New Dimension 0” dan namakan node tersebut “Cabang”
dengan type “Standard Dimension” (untuk dimensi waktu pilih “Time Dimension”).
Membuat Level : klik kanan pada node “Hierarchy” dan pilih menu “Add level”.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 22 / 50
Kita putuskan propinsi akan menjadi data untuk level pertama. Masukkan konfigurasi seperti pada gambar berikut.
Tambahkan level berikutnya dengan cara yang sama seperti di atas. Kali ini nama cabang
yang akan menjadi level selanjutnya dari hirarki ini.
Sampai titik ini coba simpan dulu skema yang telah kita buat. Misalkan dengan nama
“Training.xml”.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 23 / 50
Untuk dimensi lainnya lakukan dengan cara yang sama. Penyesuaian dilakukan untuk dimensi waktu.
Selesai
4.5. Contoh : Mapping Cube
Klik kanan kembali pada node Schema dan pilih menu “Add cube”
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 24 / 50
Klik node “New cube 0” dan namakan cube tersebut “Penjualan”
Klik simpul pada cube sampai muncul node “Table” dan masukkan tabel fact yang kita
inginkan. Conoh disini menggunakan tabel “fact_penjualan”.
Tambahkan measure : klik kanan pada node “Penjualan” dan pilih menu “Add measure”.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 25 / 50
Masukkan nama, field nilai pengukuran, dan juga tipe data dan agregasinya (count, sum, average,dll) pada spesifikasi measure yang kita buat.
Jalankan langkah yang sama untuk measure lainnya.
Menambahkan shared dimension ke dalam cube : klik kanan pada node “Penjualan” dan pilih menu “Add Dimension Usage”.
Masukkan spesifikasi penggunaan shared dimension. Contoh adalah pada gambar berikut. Pada contoh ini, name dan source namanya sama. Ini tidak mutlak karena name
menunjukkan nama dimensi yang berlaku pada cube ini, sedangkan source adalah sumber
dari shared dimension.
Simpan kembali skema tersebut.
Selesai
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 26 / 50
4.6. Mencoba Schema dengan mengeksekusi MDX
Pilih menu File | New | MDX Query
Akan terlihat ruang kerja “MDX Query Editor” dengan nama file schema kita di bagian atas
panel. Klik tombol “Connect”, apabila berhasil akan muncul dialog “Mondrian connection
successful”
Masukkan berbagai MDX yang terkait dengan cube yang sudah kita buat dan klik tombol “Execute”. Apabila berhasil maka schema tersebut sudah siap digunakan.
Selesai
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 27 / 50
5. JPivot
5.1. JPivot
JPivot merupakan client OLAP dengan antar muka web dan dikembangkan di atas teknologi
JSP (Java Server Pages). JPivot memiliki kemampuan berinteraksi dengan Mondrian secara
native dan OLAP engine lain melalui mekanisme layanan XMLA (XML for Analysis).
Pada paket instalasi web Mondrian, JPivot sudah disertakan secara default sebagai interfacenya
sehingga tidak diperlukan instalasi tambahan.
5.2. JPivot Toolbar
Di dalam halaman JPivot kita bisa memasukan berbagai tombol yang dapat digunakan untuk
melakukan navigasi dan pengolahan tampilan data (tabular maupun chart) serta ekspor dokuen
ke Excel maupun PDF.
Latihlah diri Anda dengan menggunakan berbagai tombol tersebut dan lihat perbedaannya.
Apabila ada yang kurang mengerti bisa langsung ditanyakan kepada instruktur Anda.
5.3. Tag JSP
Halaman JPivot menggunakan tag-tag JSP untuk mengolah berbagai action terhadap Mondrian.
Salah satu tag paling penting adalah mondrianQuery yaitu tag dimana kita menspesifikasikan
katalog / schema, koneksi database, serta query MDX kita.
Contoh:
<jp:mondrianQuery
id="query01"
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/phi_olap?user=root&password="
catalogUri="/WEB-INF/queries/PHI.xml">
select
{[Measures].[Total Unit]} on columns,
{([Produk].[Semua Produk])} ON rows
from Penjualan
</jp:mondrianQuery>
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 28 / 50
5.4. Pembuatan dan Peletakan File JSP JPivot
Jika kita perhatikan 6 link pertama dari halaman web Mondrian, maka semua mengacu ke
halaman testpage.jsp dengan parameter berikut :
o testpage.jsp?query=mondrian (JPivot Pivot table)
o testpage.jsp?query=xmla (JPivot pivot table by XMLA)
o testpage.jsp?query=fourhier (JPivot with 4 hierarchies)
o testpage.jsp?query=testrole (JPivot with role 'California Manager' set)
o testpage.jsp?query=arrows (JPivot with arrows)
o testpage.jsp?query=colors (JPivot with colors)
Kesemua parameter query tersebut sebenarnya mewakili sebuah file jsp dengan nama terkait (parameter mondrian untuk file mondrian.jsp, xmla untuk xmla.jsp, dstnya). File tersebut
terletak di [APACHE TOMCAT]/webapps/mondrian/WEB-INF/queries.
Isi dari keenam file jsp tersebut adalah untuk mengeksekusi perintah MDX dengan rincian pengguanan katalog skema yang kita inginkan dan database OLAP. Misalkan berikut adalah
rincian isi dari file fourhier.jsp.
Dengan melihat contoh di atas kita bisa memasukkan MDX dan rincian skema/database
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<jp:mondrianQuery id="query01"
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/foodmart?user=root&password="
catalogUri="/WEB-INF/queries/FoodMart.xml"
jdbcUser="root" jdbcPassword="" connectionPooling="false">
select {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
{([Gender].[All Gender], [Marital Status].[All Marital Status],
[Customers].[All Customers],
[Product].[All Products] ) } on rows
from Sales where ([Time].[1997])
</jp:mondrianQuery>
<c:set var="title01" scope="session">4 hierarchies on one axis</c:set>
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 29 / 50
yang telah kita buat ke dalam salah satu file-file tersebut, misalkan mondrian.jsp (dengan
tujuan kita tidak usah membuat menu baru).
Dan mendapatkan hasil JPivot seperti pada gambar berikut ketika kita mengklik link “JPivot
Pivot Table”
Selesai
<jp:mondrianQuery id="query01"
jdbcDriver="net.sourceforge.jtds.jdbc.Driver"
jdbcUrl="jdbc:jtds:sqlserver://localhost/OLAP"
catalogUri="/Training.xml"
jdbcUser="" jdbcPassword="" connectionPooling="false">
select {[Measures].[Unit Terjual]} on columns,
{[Produk].[Semua Produk].Children} on rows
from Penjualan
</jp:mondrianQuery>
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 30 / 50
5.5. Latihan : OLAP Navigator
Gunakan OLAP Navigator untuk melakukan navigasi data dan melihat data cube Penjualan dari contoh database PHI-Minimart.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 31 / 50
JPivot JSP
Mondrian OLAP Engine
JPivot JSP
Relational
Database
(RDBMS)
JDBC Driver .jar FilesMondrian Schema File
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<jp:mondrianQuery id="query01"
jdbcDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbcUrl="jdbc:sqlserver://192.168.1.6;databaseName=OLAP_PENTAHO"
catalogUri="/Training.xml"
jdbcUser="sa" jdbcPassword=""
connectionPooling="false">
with member [Measures].[PPN] as '0.1 * [Measures].[Nilai Penjualan]'
member [Measures].[Total Penjualan] as '([Waktu].[Semua Waktu], [Measures].[Nilai
Penjualan])'
member [Measures].[Rasio] as '([Measures].[Nilai Penjualan] / [Measures].[Total Penjualan])'
select {([Measures].[Nilai Penjualan]), ([Measures].[PPN]), [Measures].[Total Penjualan],
[Measures].[Rasio]} ON COLUMNS,
{[Waktu].[Semua Waktu]} ON ROWS
from [Penjualan]
</jp:mondrianQuery>
<c:set var="title01" scope="session">Test Query uses Mondrian OLAP</c:set>
Call by Testpage.jsp
MDX Query
Pivot
View
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 32 / 50
6. MDX (Multi Dimensional Expression)
6.1. MDX (Multi Dimensional Expression)
MDX adalah suatu bahasa (language) yang mengekspresikan seleksi, kalkulasi dan definisi
metadata terhadap database OLAP, dan memiliki kapabilitas untuk menentukan bagaimana hasil
query ditampilkan.
6.2. MDX Query & Syntax
Query adalah konstruksi bahasa yang digunakan untuk mengakses dan mengambil data oleh
client MDX.
Syntax dasar Query :
Select
{ Column Headers Set } on Columns,
{ Row Headers Set } on Rows
from [Cube]
Where [Slicer Dimension / Measure]
Contoh :
select {[Measures].[Nilai Penjualan]} ON COLUMNS,
{[Produk.Default].[Semua Produk]} ON ROWS
from [Penjualan]
6.3. Tuple dan Set
Tuple dan Set adalah konsep yang sangat fundamental dan penting di MDX.
Tuple adalah interseksi dari satu (dan hanya satu) member yang diambil dari satu atau beberapa
dimensi di dalam cube.
Set adalah kumpulan dari nol atau lebih tuple yang memiliki karakteristik dimension yang sama
(same dimensionality). Jika tidak memiliki tuple maka set disebut kosong (empty set).
Di dalam ekspresi MDX, Tuple biasanya ditutupi oleh pasangan kurung biasa - ( ) - sedangkan
set oleh kurung kurawal - { } -.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 33 / 50
Contoh Tuple :
()
[Measures].[Unit Penjualan]
([Measures].[Unit Penjualan])
([Produk].[Buah], [Measures].[Unit Penjualan])
([Produk].[Buah], [Karyawan].[Semua Karyawan])
Contoh Set :
{}
{([Measures].[Unit Penjualan], [Measures].[Total Penjualan])
{
([Produk].[Buah], [Measures].[Unit Penjualan]),
([Produk].[Sayur-sayuran], [Measures].[Unit Penjualan])
}
6.4. MDX Expression
MDX Expression atau adalah statement parsial atau potongan code kecil yang dapat digunakan
untuk mendefinisikan calculated member, set, atau member property. Ekspression / Ekspresi
dari MDX ini bisa mengambil suatu tuple atau set sebagai parameternya dan selalu
mengembalikan suatu nilai (termasuk nilai null).
6.5. MDX Functions
Function tentunya merupakan bagian ekspression dari MDX yang sangat penting dan digunakan
untuk berbagai tujuan dan keperluan. Function dapat mengembalikan nilai numeric, teks, set,
dimension, dan sebagainya.
Beberapa contoh function MDX adalah Count, Parent, Descendants, AVG, YTD, dan
sebagainya. Function di Mondrian sebagian besar kompatibel dengan function di Microsoft
Analysis Service sehingga untuk referensi fungsi selengkapnya, penulis menyarankan untuk
melihat dari Microsoft Developer Network (MSDN) Library dan juga dokumentasi dari paket
Mondrian.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 34 / 50
6.6. Navigasi Member / Node
Untuk melakukan navigasi terhadap member dari level dimension maka kita menggunakan
function berikut :
CurrentMember, referensi ke node member saat ini
Contoh : SELECT {[Measures].[Total Unit] } ON COLUMNS, {([Produk].CurrentMember)} ON ROWS FROM [Penjualan]
PrevMember, referensi ke node member sebelumnnya dari member yang diacu saat ini
Contoh : SELECT {[Measures].[Total Unit] } ON COLUMNS, {([Produk].CurrentMember.PrevMember)} ON ROWS FROM [Penjualan]
Children, mengembalikan set semua node level di bawah dari member yang diacu
Contoh : SELECT {[Measures].[Total Unit], [Measures].[Jumlah Penjualan]} ON COLUMNS, {[Produk].CurrentMember.Children} ON ROWS FROM [Penjualan]
Parent, mengembalikan node member satu tingkat di atas level dari member yang diacu
Contoh : SELECT {[Measures].[Total Unit] } ON COLUMNS, {[Produk].[Sayur-sayuran].Parent} ON ROWS FROM [Penjualan]
Descendants, mengembalikan set semua node turunan dari level tertentu dari level yang diacu
Contoh: SELECT {[Measures].[Total Unit] } ON COLUMNS, {Descendants([Produk],[Kategori])} ON ROWS FROM [Penjualan]
Ancestor, mengembalikan set semua node dari level teratas sampai dengan level sebelum node member yang diacu tersebut.
Contoh: SELECT {[Measures].[Total Unit] } ON COLUMNS, {Ancestor([Produk].[Semua Produk].[Sayur-sayuran])} ON ROWS FROM [Penjualan]
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 35 / 50
6.7. Filter dan Slicer
Jika kita familiar dengan SQL maka tentunya kita mengenal klausa “where” yang digunakan
untuk mengambil subset dari sebuah tabel, view atau join dari table/view.
MDX juga memiliki klausa “where” tapi bukan merupakan satu-satunya filter yang digunakan,
namun lebih cenderung dan intensif menggunakan fungsi FILTER. Where pada MDX lebih
sering disebut slicer karena cube “dipotong” menjadi subset berdasarkan existing member,
bukan dengan ekspresi tertentu misalkan perbandingan numerik.
Syntax dari fungsi Filter ini :
Filter(Set, Logical Expression)
Contoh Penggunaan:
SELECT [Measures].[Unit Terjual] ON COLUMNS,
FILTER(
[Produk].[Semua Produk].Children , [Measures].[Unit Terjual] < 100) ON
ROWS
FROM [Penjualan]
6.8. Sorting
Untuk melakukan pengurutan atau sorting di MDX, sekali lagi kita menggunakan function.
Function dasar untuk melakukan sorting adalah function ORDER.
Syntax dari fungsi Filter ini :
Order(Set, Numeric Expression, [ASC | DESC | BASC | BDESC] )
Keterangan :
BASC dan BDESC digunakan jika kita ingin sorting efektif terhadap semua member dipantau
tanpa menjaga struktur level / grouping pada hierarki.
Contoh Penggunaan:
SELECT [Measures].[Unit Terjual] ON COLUMNS,
ORDER(
[Produk].[Semua Produk].Children , [Measures].[Unit Terjual], ASC) ON
ROWS
FROM [Penjualan]
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 36 / 50
6.9. Functions Yang Terkait dengan Dimensi Waktu
YTD, mengembalikan semua set measures dari titik periode awal tahun sampai titik beriode
berjalan
Contoh: Sum(YTD([Time].CurrentMember), [Measures].[Target Value])
ParallelPeriod, mengembalikan member untuk titik member yang sama pada periode yang
lalu atau ke depan
Contoh: (ParallelPeriod([Year],1,[Time].CurrentMember), [Measures].[Cur Qty])
6.10. Latihan : Contoh Penggunaan Umum
Buatlah tampilan cube seperti pada halaman berikut di bawah ini dengan menggunakan
berbagai ekspresi dan fungsi MDX dari cube Penjualan kita.
Tips : Gunakan ekspresi PrevMember, ParallelPeriod, YTD, AVG.
Waktu Target Realisasi Rasio Rata-rata
Periode
Sebelumnya
Year To
Date
2008 5,400,000 5,800,000 107% 483,333 0 5,800,000
1 240,000 280,000 117% 280,000 0 280,000
2 340,000 380,000 112% 380,000 240,000 660,000
… … … … … .. …
6.11. Ekspresi “With Member”
Ekspresi With Member digunakan untuk mendefinisikan suatu ekspresi yang menghasilkan satu
member dalam suatu nama.
Contoh Penggunaan:
WITH MEMBER [Measures].[Profit] AS '[Measures].[Store Sales]-
[Measures].[Store Cost]',
FORMAT_STRING = '$#,###'
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
{[Product].Children} ON ROWS
FROM [Sales]
WHERE [Time].[1997]
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 37 / 50
6.12. Ekspresi “With Set”
Ekspresi With Member digunakan untuk mendefinisikan suatu ekspresi yang menghasilkan satu
member dalam suatu nama.
Contoh Penggunaan:
WITH SET [Top Sellers] AS
'TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse
Sales])'
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{[Top Sellers]} ON ROWS
FROM [Warehouse]
WHERE [Time].[Year].[1997]
6.13. Calculated Member dan Named Set
Ekspresi With Member dan Set dapat didefinisikan secara permanen di dalam Schema dengan
menggunakan tag untuk Calculated Member (tag CalculatedMember) dan Named Set (tag
NamedSet).
Contoh Penggunaan CalculatedMember: <CalculatedMember name="Profit" dimension="Measures">
<Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula>
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>
Contoh Penggunaan NamedSet: <Cube name="Warehouse">
...
<NamedSet name="Top Sellers">
<Formula>TopCount([Warehouse].[Warehouse Name].MEMBERS, 5,
[Measures].[Warehouse Sales])</Formula>
</NamedSet>
</Cube>
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 38 / 50
6.14. Ekspresi Conditional
Conditional pada MDX menggunakan function IIF (If and only IF) dengan syntax sebagai
berikut :
IIF(<search_condition>, <true_part>, <false_part>)
Contoh Penggunaan IIF :
WITH MEMBER [Measures].[Varian] AS IIF (([Measures].[Penjualan] >
[Measures].[Budget]),
([Measures].[Penjualan] - [Measures].[Budget]), ([Measures].[Budget] -
[Measures].[Penjualan]))
SELECT
{[Measures].[Penjualan], [Measures].[Budget], [Measures].[Varian]} ON
COLUMNS,
{[Waktu].Children} ON ROWS
FROM Penjualan
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 39 / 50
7. Pentaho BI Server
7.1. Pentaho BI Server
Pentaho BI Server adalah aplikasi server Java berbasis web yang di dalamnya terdapat layanan
solusi dan integrasi dari komponen produk Pentaho seperti Pentaho Data Integration / Kettle,
Pentaho Data Analysis / Mondrian, Pentaho Reporting / JfreeReport, charts, dan lain-lain.
7.2. Instalasi BI Server 3.6 Community Edition
Dari DVD buka folder BI-Server
Ekstrak biserver-ce-3.6.0.stable.tar.gz atau biserver-ce-3.6.0.stable.zip ke folder c:\biserver-ce
Masuk ke folder c:\biserver-ce
Jalankan file start-pentaho.bat
Buka URL http://localhost:8080
Masukkan user/password : joe/password
7.3. Administration Console
Administration Console adalah aplikasi berbasis web untuk mengelola role dan user kita serta
hak akes yang bisa didapatkan. Di samping itu juga melakukan berbagai tugas administratif
seperti membersihkan cache, dan sebagainya.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 40 / 50
Langkah-langkah menjalankan Administration Console
Buka folder C:\biserver-ce\administration-console
Jalankan startup.bat
Buka URL http://localhost:8099
Masukkan user/password : admin/password
Buat beberapa role dan user
Dari Pentaho user console batasin hak akses halaman analysis yang telah kita buat.
Selesai
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 41 / 50
7.4. Susunan Folder BI Server
webapps
Preconfigured Pentaho BI Server ‘s
Folder Structure
tomcat
bin
META-INF WEB-INF
Common/lib
pentaho
lib
JDBC (Java
Database
Connectivity) Driver
.jar files
JDBC (Java Database
Connectivity) Driver
.jar files
BI SERVER
Installation FolderAdministration Console
Startup.bat Stop.bat
Start-
pentaho.batStop-
pentaho.bat
Context.xml
pentaho-style
pentaho-solutions
system Solution folders
Xaction FilesPentaho BI
Files
JNDI (Java Naming
Directory Interface)
Database Connection
Schema
Prepared By :
PHI-Integration.com
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 42 / 50
8. Pentaho Design Studio & Xaction
8.1. Pentaho Solution dan file .XACTION
Pada BI Server, semua integrasi produk Pentaho dipaketkan dalam apa yang dinamakan
Pentaho Solution. Paket ini terdiri satu file workflow berekstensi .XACTION yang kemudian
mengintegrasikan komponen BI Pentaho seperti analysis pivot view, ETL, mail, SQL data
source, dan lain sebagainya.
Untuk merancang XACTION Pentaho telah menyediakan satu aplikasi editor yaitu Pentaho
Design Studio (PDS). Aplikasi ini dibuat di atas editor Java, Eclipse. PDS dapat di-download
di website sourceforge http://sourceforge.net/projects/pentaho/files/Design%20Studio/.
8.2. Instalasi
Ekstrak file pds-ce-win-3.5.0.stable.zip dari DVD ke folder yang Anda inginkan
Jalankan file PentahoDesignStudio.exe
Jika muncul welcome screen pada editor tersebut tutup saja untuk melanjutkan
perancangan workflow
Selesai
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 43 / 50
8.3. Project sebagai Placeholder
Project pada Pentaho Design Studio merupakan placeholder / container untuk file-file
XACTION.
Berikut adalah langkah-langkah pembuatan project :
Pilih menu File | New | Project
Pada saat muncul dialog awal New Project, pilih General | Project
Namakan project sesuai yang Anda inginkan pada dialog berikutnya, misalkan “Pentaho”
Klik tombol Finish
Project kita sudah dibuat dan terdaftar di panel Package Explorer
Selesai
8.4. Membuat File XAction Pertama dari Wizard
Klik tombol New Action Sequence Wizard pada bagian menu toolbar
Pada saat muncul dialog Action Sequence Wizard, masukkan isi entri sesuai gambar
berikut :
o Container : Pentaho
o Filename : HelloWorld.xaction
o Template : Hello World
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 44 / 50
Klik tombol Finish
Akan muncul file HelloWorld.xaction pada panel PackageExplorer dan editor spesifikasi file tersebut di bagian workspace
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 45 / 50
Pada bagian bawah editor terdapat empat tab yaitu : o General : Untuk memasukkan nama file dan deskripsi yang akan dikenali oleh
Pentaho BI Platform (BI Server)
o Define Process : Rancangan spesifikasi workflow
o XML Source : Melihat secara keseluruhan konstruksi XML yang dihasilkan
o Test : Untuk melihat hasilnya di BI Server
8.5. Define Process
Pada bagian tab Define Process inilah kita memasukkan komponen-komponen dari workflow
Xaction. Pada bagian ini terdapat tiga flow panel utama yaitu :
Process Inputs : Inisialisasi input, bisa berasal dari session, request, global environment
variable, dll.
Process Actions : Definisi step-step untuk memproses input, menghasilkan output baik dalam bentuk resultset ataupun render tampilan / view
Process Outputs : Definisi output
Pada contoh HelloWorld.xaction kita, maka belum ada input maupun output. Tapi di bagian
actions ada satu komponen yaitu HelloWorldComponent, klik dua kali pada komponen
tersebut. Perhatikan di workspace akan muncul dialog konfigurasi.
Masukkan “Hello World” pada bagian Name dan “Testing” pada bagian Message. Perhatikan
nama komponen sekarang berubah menjadi “Hello World.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 46 / 50
8.6. Memasukkan Komponen dan Mengatur Flow
Kita bisa memasukkan komponen lainnya dengan mengklik icon “+” pada panel actions dan
mengatur flownya dengan tanda panah atas dan bawah.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 47 / 50
8.7. Latihan : Integrasi Mondrian Schema ke dalam BI Server
Dengan petunjuk instruktur lakukan integrasi skema Mondrian dan query MDX untuk cube
tertentu ke BI Server.
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 48 / 50
Pivot .Xaction View
Mondrian OLAP Engine
.XAction
Relational
Database
(RDBMS)
JDBC Driver .jar Files
Mondrian Schema File
Repository Folder
JNDI Call
MDX Query
Pivot
View
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 49 / 50
9. Pentaho Aggregation Designer
Pentaho Aggregation Designer adalah utilitas editor untuk schema Mondrian dengan tujuan
mengoptimalkan penggunaan aggregate dengan berbagai kombinasi dimensi. Kombinasi baru ini
akan diimplementasikan secara fisik dengan membuat tabel-tabel aggregate baru.
9.1. Membuat Koneksi Skema ke Database
Data Analysis Solution with Mondrian (Pentaho Analysis)
PHI-Integration.com Halaman : 50 / 50
9.2. Menguji coba berbagai skenario aggregate dan optimasi waktu query