Business Intelligence "murah" makae VB6+Excel Posted on : 2/01/2008 12:06:00 AM

Pernah liat aplikasi Business Intelligence? Ngerti apa itu Business Intelligence? Artikel terdahulu di blog ini sempat menjelaskan definisi "business intelligence", silakan baca klo masih blom mudeng apa itu BI (singkatan umum utk Business Intelligence). Nah, dalam artikel ku kali ini, aku akan bicara secara teknis (yippie... coding-coding...) tentang gimana mbikin aplikasi BI sederhana dan murah makae Visual Basic n Microsoft Excel. Murah? Yupz! Nyaris semua aplikasi BI di pasaran harganya mahal-mahal loo... Karena informasi yang disediakan dari aplikasi ini emang muahall bangedd. Berharga. Lha terus kenapa yg mo ta' tulis ini kulabelin "murah"? Hehehe, biar terkesan kontroversial ajah... - alasan utamanya sih, bcoz mbikin aplikasi BI pake VB6 + Excel ini GUAMPANGG bangedd n harga produksi nya ngga sampe ngabisin 2 batang rokok seharga @ Rp 600,00 - harga aplikasi development nya jg *murah* koq. xixixi (u know what I meant laah..). Berapa sih harga VB6 dan Microsoft Office 2003 ?!?!

Jadi begini, sebelum kulanjutin - aku mau cerita dikit dulu tentang apa sih komponen2 yang akan kita dalam aplikasi BI murah kita... Yaa, nggak semua komponen BI seperti dalam artikel yang ku post sebelumnya bakal kita coba implementasikan di sini sih. Namanya jg BI "murah" -- wajar donk? xixixi. Yang jelas, murah disini bukan berarti murahan! Ntar liat aja hasilnya, loe pasti terpana bolak-balik - "begini simpel seperti ini kah tuk bikin BI itu?"

Oke, cukup nge-gombalnya. Jadi begini, bahan2 yang kita butuhkan utk bikin nie aplikasi :

1. Data Source
Aplikasi business inteligence itu termasuk kategori OLAP (Online Analytical Processing). Proses ini merupakan kelanjutan dari proses data-mining (penambangan data). Nah, supaya aplikasi BI bisa jalan, tentunya sebelumnya musti ada donk data yg mo di-proses utk di-analisis...
Sebagai contoh dalam artikel-ku ini aku bikinin deh 1 tabel dalam 1 schema utk simulasi penambangan data.

DDL nya sebagai berikut (owya, aku makae MySQL. Lagi males makae access, ngga praktis utk coding pake script):


CREATE DATABASE db_contohdatamining;
USE db_contohdatamining;
CREATE TABLE tbldatamining (
kode int(10) NOT NULL auto_increment,
NamaSiswa varchar(50) NOT NULL,
Semester tinyint NOT NULL,
MataPelajaran varchar(50) NOT NULL,
Nilai tinyint NOT NULL,
PRIMARY KEY(kode)
)ENGINE=MyISAM;


tu tabel adalah contoh doank. Ntar sumber data mu ya ngambil dari tabel itu. Bisa aja sih u me-mining data dari query select yang multi tabel, atau kmu pre-definisi-kan pake VIEW. Sama aja itu.. Di sini aku asumsikan, ya itu lah kira2 struktur sumber data nya. Dalam struktur relasional database, lebih sehat sih klo ada TblMataPelajaran sendiri, n TblSiswa sendiri. Yah, ini kan cuman contoh. Suka-suka gue donk... Gue kan udah cukup baek (cakep lagi) utk ngasih contoh ini ke loe-loe pade... (cape loo blogging hal2 yg berbau tutorial itu). hihihi...

Oke, trus ini jg kusertakan beberapa contoh value data. Eksekusi aja :

INSERT INTO tblDataMining
(NamaSiswa,Semester,MataPelajaran,Nilai)
VALUES
('eRQee',1,'PPKn',7), ('eRQee',1,'Agama',8),
('eRQee',1,'B.Ind',8),('eRQee',1,'B.Ing',8),
('eRQee',2,'PPKn',6), ('eRQee',2,'Agama',6),
('eRQee',2,'B.Ind',7),('eRQee',2,'B.Ing',9),
('Neeah',1,'PPKn',9), ('Neeah',1,'Agama',8),
('Neeah',1,'B.Ind',6),('Neeah',1,'B.Ing',6),
('Neeah',2,'PPKn',8), ('Neeah',2,'Agama',7),
('Neeah',2,'B.Ind',7),('Neeah',2,'B.Ing',7),
('GNuKa',1,'PPKn',7), ('GNuKa',1,'Agama',9),
('GNuKa',1,'B.Ind',8),('GNuKa',1,'B.Ing',7),
('GNuKa',2,'PPKn',7), ('GNuKa',2,'Agama',9),
('GNuKa',2,'B.Ind',7),('GNuKa',2,'B.Ing',7),
('Tomb',1,'PPKn',6), ('Tomb',1,'Agama',7),
('Tomb',1,'B.Ind',6),('Tomb',1,'B.Ing',9),
('Tomb',2,'PPKn',6), ('Tomb',2,'Agama',6),
('Tomb',2,'B.Ind',7),('Tomb',2,'B.Ing',8);


2. cExcelReport.bas
Ini class-module bikinanku. Jujur, ini class module pertama yg kubikin beberapa tahun yg lalu. Udah beberapa kali sih mengalami penyempurnaan, tp aku cukup puas dengan nie class. Mengenkapsulasi beberapa kebutuhan utk reporting ato manipulating Excel Spreadsheet secara remote dari VB. :cool: Ntar kubedah deh arsitektur class-ku ini di postingan-ku yg laen...

Untuk mbikin PivotTable secara ostosmastis dari VB, pakae method ini :

Public Sub CreatePivotTable(sSourceRange As String, _
sDestinationRange As String, _
sRowFields As String, _
sColumnFields As String, _
sDataFields As String, _
sTitleDataFields As String)
Dim pt As Object

On Error GoTo Hell
Set pt = oSheet.PivotTableWizard _
( _
SourceType:=1, _
SourceData:=oSheet.Range(sSourceRange), _
TableDestination:=oSheet.Range(sDestinationRange) _
)

pt.AddFields RowFields:=sRowFields, _
ColumnFields:=sColumnFields
pt.AddDataField pt.PivotFields(sDataFields), sTitleDataFields
Exit Sub

Hell:
debug.print "[" & err.number & "] " & err.description
End Sub


Owya, cExcelReport.bas ini bisa you donlot di sini. Monggo dipake, gpp... aku ikhlas koq. But keep my credits on the top of ur code, please... Utk menghormati Creative Common Licenses... hehehe. Banyak programmer yg ngga gila harta, tp nyaris semua programmer gila hormat loo... (tonton/baca aja Takedown, bintangnya Kevin Mitnick ama Tsutsomu Shimomura...).

3. Sedikit code di salah satu form VB.
Kmu codingin ini di salah satu form mu. Jangan lupa juga, include-kan reference ke Microsoft ActiveX Data Objects Library 2.8 (versi sebelumnya nggak apa2 sieh, asal jangan 2.5 ke bawah aja)... Juga masukin component Microsoft Windows Common Controls 6.0 (soalnya di cExcelReport-ku ada kebutuhan utk export dari Listview). Reference utk Microsoft Excel xx Object Library *amat sangat* tidak dibutuhkan. Dengan cExcelReport.bas ku -- versi Excel yg dibutuhkan nggak ditentukan. BEBAS! Cuman harus ada aja... Klo u jalanin nie aplikasi di kompie yg ngga ada Excel-nya, InsyaAllah nggak jalan... hehehe, suer disambar geledek..

'--declaration
Private xls as cExcelReport
Private db as ADODB.Connection

Private Const CONST_HOST = "localhost"
Private Const CONST_PORT = "3306"
Private Const CONST_SCHEMA = "db_contohdatamining"
Private Const CONST_USERNAME = "root"
Private Const CONST_PASSWORD = "123456"

'--routine to open database connection
Private Sub OpenDB(ByRef db as ADODB.Connection)
db.CursorLocation = adUseClient
db.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & CONST_HOST & ";" _
& "PORT=" & CONST_PORT & ";" _
& "DATABASE=" & CONST_SCHEMA & ";" _
& "UID=" & CONST_USERNAME & ";" _
& "PWD=" & CONST_PASSWORD & ";" _
& "OPTION=" & 1 + 2 + 8 + 16 + 2048
db.open
DoEvents
If Err.Number <> 0 And db.state <> adStateOpen Then
GoTo Hell
End If
Exit Sub
Hell:
msgbox "[" & err.number & "] " & err.description
End Sub

'--form load
Private Sub Form_Load()
Set db = New ADODB.Connection
db.mode = adModeReadWrite
db.CursorLocation = adUseClient
OpenDB
End Sub

'--tombol klik
Private Sub Command1_Click()
screen.mousepointer = vbHourglass
Dim sRangeSourceAwal As String, sRangeSourceAkhir As String
Dim sRangeDest As String
'tahap 1 : persiapkan object excel dan bikin dokumen baru
Set xls = New cExcelReport
xls.init

Dim iI As Integer, rs As ADODB.Recordset, sSQL As String
sSQL = "SELECT NamaSiswa,Semester,MataPelajaran,Nilai " & _
"FROM TblDataMining"
Set rs = db.execute(sSQL)
if rs.recordcount > 0 then
'tahap 2 : data-mining
xls.TulisCell 9, 1, "NamaSiswa", vbWhite
xls.TulisCell 9, 2, "Semester", vbWhite
xls.TulisCell 9, 3, "MataPelajaran", vbWhite
xls.TulisCell 9, 4, "Nilai", vbWhite
sRangeSourceAwal = "$A$9"
For iI = 1 To rs.RecordCount
xls.TulisCell 9 + iI, 1, rs(0).Value, vbWhite 'Nama Siswa
xls.TulisCell 9 + iI, 2, rs(1).Value, vbWhite 'Semester
xls.TulisCell 9 + iI, 3, rs(2).Value, vbWhite 'Mata Pelajaran
xls.TulisCell 9 + iI, 4, rs(3).Value, vbWhite, , , , , , , True 'nilai
rs.MoveNext
Next iI
sRangeSourceAkhir = "$D$" & CStr(9 + CLng(rs.RecordCount))
sRangeDest = "$F$10"
'tahap 3 : bikin pivot table
xls.CreatePivotTable sRangeSourceAwal & ":" & sRangeSourceAkhir, _
sRangeDest, _
"MataPelajaran", _
"NamaSiswa;Semester", _
"Nilai", _
"Sum of Nilai"
xls.ChangeWidth 0, 1, 4
'tahap 4 : tampilkan file excel
xls.tampil
else
Msgbox "Tidak ada data yang dapat di-analisis, bro...", _
vbInformation + vbOkOnly, "Maaf"
end if
rs.close
Set rs = nothing
Screen.mousepointer = vbDefault
End Sub

'--form unload
Private Sub Form_Unload()
on error resume next
set db = nothing
set xls = nothing
End Sub

n mau tau hasilnya kya apa? Lihatlah capturan berikut ini :

Lho? Kosong?

Hehehe, emang... tp coba aja klik tombolnya. Ntar bakal muncul dokumen Excel seperti ginian deh :

Yup, PivotTable Excel udah siap utk dipake untuk analisis. Coba drag Field-field di dock-window "Pivot Fields" ke dalam kotak biru dalam grid, Taroh field nilai di tengah, yg lainnya terserah u... ntar bakal menghasilkan tabel yang kira-kira seperti ini penampakannya :

Mau lebih representatif utk menganalisis? Klik kanan tu PivotTable , klik popup PivotChart... Otomatis, tanpa keluarin keringat apa2 (ato batang rokok tambahan utk disulut) -- udah jadi deh chart mu... Keren kan?

N mau di-kustomisasi ? monggo silahkan... uthex-uthex aja combo2 di field utk custom grouping or custom sorting tanpa pusing-pusing bikin/ngirim query lagi ke database... Mo ngganti "skin" chart utk analisis data multidimensional jg bisa... tinggal pilih field-nya, drag, pilih tipe chart (coba-coba aja ndiri), n bisa deh jadi kya seperti gambar berikut :

Pegimane? Mudah bukan? Keren bukan? Cakep bukan? Seperti inilah kira-kira aplikasi BI sederhana itu... seperti yg muncul dalam diskusi di milis, BI kurang lebih aja sama kya Pivot Excel...

Utk buat yg kesulitan nyoba, u bisa donlot source demo project nya di sini (tp u musti pake MySQL n eksekusi script SQL seperti yg udah kujelasin di atas).. mo sesuai-in dengan db favoritmu ndiri, silakan.. ganti ndiri yak? hehehe....