ANALISIS DAN PERANCANGAN BUSINESS INTELLIGENCE PADA PT. XYZ
S KRIPS I
Oleh Billy King
1100030282
Wilson
1100031120
Christianto
1100032092
Universitas Bina Nusantara Jakarta 2011
ANALISIS DAN PERANCANGAN BUSINESS INTELLIGENCE PADA PT. XYZ
S KRIPS I
diajukan sebagai salah satu syarat untuk gelar kesarjanaan pada Jurusan Sistem Informasi Jenjang Pendidikan S trata-1
Oleh
Billy King
1100030282
Wilson
1100031120
Christianto
1100032092
Universitas Bina Nusantara Jakarta 2011
ii
ANALISIS DAN PERANCANGAN BUSINESS INTELLIGENCE PADA PT. XYZ
S KRIPS I
Disusun oleh:
Billy King 1100030282
Wilson 1100031120
Disetujui oleh: Pembimbing
Indrajani, S.Kom., MM Kddsn: D1521
Universitas Bina Nusantara Jakarta 2011
iii
Christianto 1100032092
PERNYATAAN Dengan ini kami, Nama : Billy King NIM : 1100030282 Nama : Wilson NIM : 1100031120 Nama : Christianto NIM : 1100032092 Judul skripsi : Analisis dan Perancangan Business Intelligence Pada PT. XYZ
M emberikan kepada Universitas Bina Nusantara hak non-eksklusif untuk menyimpan, memperbanyak, dan menyebarluaskan skripsi karya <<saya/kami>>, secara keseluruhan atau hanya sebagian atau hanya ringkasannya saja, dalam bentuk format tercetak dan atau elektronik.
M enyatakan bahwa kami, akan mempertahankan hak exclusive kami, untuk menggunakan seluruh atau sebagian isi skripsi kami, guna pengembangan karya di masa depan, misalnya bentuk artikel, buku, perangkat lunak, ataupun sistem informasi.
Jakarta, 9 M aret 2011
Billy King
Wilson
v
Christianto
Prakata Puji syukur penulis panjatkan kepada Tuhan Yang M aha Esa atas rahmat dan berkatnya yang membimbing dan menyertai penulis sehingga penulis dapat menyelesaikan skripsi ini tepat pada waktunya. Skripsi ini dibuat untuk memenuhi syarat kelengkapan studi untuk jenjang pendidikan Sarjana Strata 1 pata jurusan Sistem Informasi di Universitas Bina Nusantara. Atas bantuan dan bimbingan serta kesempatan yang telah diberikan sehingga penulisan laporan ini dapat selesai, maka penulis mengucapkan terima kasih yang sebesar – besarnya kepada : 1. Bapak Prof. Dr. Ir. Harjanto Prabowo, MM , selaku Rektor Universitas Bina Nusantara, 2. Bapak Ir. Sablin Yusuf M .Sc, M .Comp. Sc selaku Dekan Fakultas Ilmu Komputer Universitas Bina Nusantara, 3. Bapak Johan, S.Kom., MM selaku Kepala Jurusan Sistem Informasi Universitas Bina Nusantara, 4. Ibu Indrajani, S.Kom., MM , selaku dosen pembimbing kami yang senantiasa selalu memberi masukan-masukan untuk kami, 5. Bapak Wim Raharja selaku IT Support dari PT. XYZ juga para karyawan yang bekerja di dalamnya yang telah membantu memberikan informasi yang dibutuhkan, 6. Teman-teman yang selalu mendoakan dan juga mendukung kami, juga kerjasamanya yang sangat membantu dalam penyelesaian penulisan laporan ini,
vii
7. Segenap keluarga khususnya kedua orang tua kami masing – masing yang telah mendukung kami baik secara moril maupun secara materiil dalam penyelesaian penulisan laporan ini, 8. Dan semua pihak yang tidak dapat disebutkan satu per satu oleh penulis baik secara langsung maupun tidak langsung telah membantu penulis dalam penulisan skripsi ini. Tak ada gading yang tak retak, penulis menyadari penulisan skripsi ini masih jauh dari kesempurnaan. M aka dari itu, penulis menerima segala saran dan kritik dari para pembaca yang nantinya akan menjadi masukkan bagi penulis untuk menyempurnakan skripsi ini. Akhir kata, penulis mengharapkan semoga skripsi ini bermanfaat bagi pembaca sekalian.
Jakarta, 9 M aret 2011
Penulis
viii
DAFTAR ISI
Halaman Judul ..................................................................................................................... i Halaman Judul Dalam ........................................................................................................ii Halaman Persetujuan Hard Cover.....................................................................................iii Halaman Pernyataan Dewan Penguji ................................................................................ iv Halaman Pemberian Hak Cipta Non Eksklusif dari M ahasiswa ke Universitas Bina Nusantara............................................................................................................................ v Abstrak .............................................................................................................................. vi Prakata ..............................................................................................................................vii Daftar Isi............................................................................................................................ ix Daftar Tabel..................................................................................................................... xiv Daftar Gambar ................................................................................................................xvii Daftar Lampiran ............................................................................................................... xx BAB 1 PENDAHULUAN.................................................................................................. 1 1.1 Latar Belakang................................................................................................ 1 1.2 Ruang Lingkup ............................................................................................... 3 1.3 Tujuan dan M anfaat Penelitian ....................................................................... 4 1.4 M etodologi...................................................................................................... 5 1.4.1 Studi Kepustakaan ................................................................................. 5
ix
1.4.2 M etode Analisis ..................................................................................... 5 1.4.3 M etode Perancangan.............................................................................. 6 1.5 Sistematika Penulisan ..................................................................................... 7 BAB 2 LANDASAN TEORI............................................................................................. 9 2.1 Teori-Teori Dasar/Umum ............................................................................... 9 2.1.1 Pengertian Sistem .................................................................................. 9 2.1.2 Pengertian Data.................................................................................... 10 2.1.3 Pengertian Informasi............................................................................ 10 2.1.4 Pengertian Knowledge ......................................................................... 11 2.1.5 Pengertian Sistem Informasi................................................................ 11 2.1.6 Pengertian Database ............................................................................ 12 2.1.7 Pengertian Metadata ............................................................................ 12 2.1.8 Pengertian Sistem M anajemen Basis Data .......................................... 13 2.1.9 Pengertian Bisnis ................................................................................. 14 2.1.10 Pengertian Business Intelligence ....................................................... 14 2.1.11 Siklus Hidup BI ................................................................................. 16 2.1.12 Komponen BI..................................................................................... 17 2.1.13 Keuntungan dalam M enggunakan BI ................................................ 20 2.1.14 Teknologi BI ...................................................................................... 22 2.1.15 Desain dan Implementasi BI.............................................................. 24 x
2.1.16 Pengertian Data Mining..................................................................... 26 2.1.17 Pengertian OLAP ............................................................................... 27 2.1.18 Perbedaan OLAP, Statistik, dan Data Mining................................... 28 2.1.19 Pengertian ETL (Extract, Transform, Load) ..................................... 29 2.1.20 Pengertian Data Mart ........................................................................ 29 2.1.21 Pengertian Skema Bintang/Star Schema ........................................... 30 2.1.22 Pengertian Interface........................................................................... 31 2.1.23 Microsoft Time Series ........................................................................ 32 2.1.23 Tingkat M anajemen ........................................................................... 33 2.2 Teori-Teori Khusus....................................................................................... 34 2.2.1 Pengertian Penjualan dan Sistem Penjualan ........................................ 34 2.2.2 Pengertian Pembelian .......................................................................... 34 2.2.3 Pengertian Account Receivable............................................................ 35 2.2.4 Pengertian Invoice ............................................................................... 35 BAB 3 ANALISIS SISTEM INFORMASI BERJALAN................................................ 37 3.1 Riwayat Perusahaan ....................................................................................... 37 3.2 Struktur Organisasi......................................................................................... 38 3.2.1 Tugas ..................................................................................................... 39 3.3 Teknologi Informasi ....................................................................................... 61 3.3.1 Perangkat Keras..................................................................................... 61 xi
3.3.2 Piranti Lunak ......................................................................................... 63 3.4 Tata Laksana Sistem Berjalan ........................................................................ 63 3.4.1 Sales Order............................................................................................ 63 3.4.2 Purchase Order ..................................................................................... 65 3.4.3 Picking and Sales Delivery - Fertilizer ................................................. 67 3.4.4 Picking and Sales Delivery - Pesticide & Chemical ............................. 69 3.4.5 A/R Invoice ............................................................................................ 71 3.4.6 Sistem yang Sedang Berjalan ................................................................ 73 3.5 Diagram Aliran Data ...................................................................................... 75 3.5.1 Entity Relationship Diagram ................................................................. 75 3.5.2 Konfigurasi Basis Data.......................................................................... 76 3.6 Permasalahan yang Dihadapi ......................................................................... 86 3.7 Analisa Kebutuhan Sistem Terhadap Sistem Berjalan................................... 87 3.8 Usulan Pemecahan M asalah ........................................................................... 88 BAB 4 RANCANGAN SISTEM YANG DIUSULKAN ................................................ 89 4.1 Perancangan Aplikasi ..................................................................................... 89 4.1.1 Perancangan M etadata........................................................................... 89 4.1.2 Perancangan Star Schema ..................................................................... 99 4.1.3 Extract Transform Load ...................................................................... 102 4.1.3.1 Tabel Deskripsi Transformasi Fakta dan Dimensi .................. 102 xii
4.1.3.2 Proses ETL .............................................................................. 128 4.1.3.3 Proses Data Mining ................................................................. 138 4.1.4 Navigation Diagram ............................................................................ 146 4.2 Rancangan Layar Aplikasi ........................................................................... 148 4.3 Implementasi Sistem .................................................................................... 158 4.3.1 Sistem yang Diusulkan ........................................................................ 159 4.3.2 Spesifikasi Perangkat Keras ................................................................ 161 4.3.3 Spesifikasi Piranti Lunak..................................................................... 162 4.3.3.1 Spesifikasi Piranti Lunak Server ............................................. 162 4.3.2.2 Spesifikasi Piranti Lunak Client.............................................. 162 BAB 5 SIM PULAN DAN SARAN............................................................................... 164 5.1 Simpulan....................................................................................................... 164 5.2 Saran ............................................................................................................. 165 DAFTAR PUSTAKA..................................................................................................... 166 RIWAYAT HIDUP........................................................................................................ 168 LAMPIRAN-LAMPIRAN...............................................................................................L1 SURAT SURVEI .................................................................................................................
xiii
DAFTAR TABEL
Tabel 2.1 Perbedaan OLTP dengan OLAP ...................................................................... 27 Tabel 2.2 Perbedaan OLAP, Statistik, dan Data Mining ................................................. 28 Tabel 3.1 Basis Data Barang ............................................................................................ 77 Tabel 3.2 Basis Data Harga Barang ................................................................................. 78 Tabel 3.3 Basis Data Grup Barang................................................................................... 78 Tabel 3.4 Basis Data M itra Bisnis.................................................................................... 78 Tabel 3.5 Basis Data Grup M itra Bisnis........................................................................... 79 Tabel 3.6 Basis Data Header PO ...................................................................................... 80 Tabel 3.7 Basis Data Detil PO.......................................................................................... 80 Tabel 3.8 Basis Data Header DO ..................................................................................... 81 Tabel 3.9 Basis Data Detil DO......................................................................................... 81 Tabel 3.10 Basis Data Header SO .................................................................................... 82 Tabel 3.11 Basis Data Detil SO........................................................................................ 83 Tabel 3.12 Basis Data Header Retur ................................................................................ 83 Tabel 3.13 Basis Data Detil Retur.................................................................................... 84 Tabel 3.14 Basis Data Header Invoice ............................................................................. 85 Tabel 3.15 Basis Data Detil Invoice ................................................................................. 85 Tabel 3.16 Basis Data Jurnal Barang ............................................................................... 86 xiv
Tabel 4.1 Perancangan M etadata...................................................................................... 89 Tabel 4.2 Basis Data Dimensi Barang.............................................................................. 92 Tabel 4.3 Basis Data Dimensi Grup Barang .................................................................... 92 Tabel 4.4 Basis Data Dimensi M itra Bisnis ..................................................................... 93 Tabel 4.5 Basis Data Dimensi Waktu .............................................................................. 93 Tabel 4.6 Basis Data Dimensi Grup M itra Bisnis............................................................ 94 Tabel 4.7 Basis Data Dimensi Prediksi Barang ............................................................... 95 Tabel 4.8 Basis Data Dimensi Fakta Penjualan Barang ................................................... 96 Tabel 4.9 Basis Data Dimensi Fakta Pembelian Barang.................................................. 96 Tabel 4.10 Basis Data Dimensi Fakta Invoice yang Belum Dibuat ................................. 97 Tabel 4.11 Basis Data Dimensi Fakta Total Piutang........................................................ 98 Tabel 4.12 Basis Data FilterTimeStamp .......................................................................... 98 Tabel 4.13 Tabel Deskripsi Stored Procedure Proc_Update_FilterTimeStamp ............ 102 Tabel 4.14 Tabel Deskripsi Transformasi DimTime...................................................... 103 Tabel 4.15 Tabel Deskripsi Transformasi DimGroupBP............................................... 107 Tabel 4.16 Tabel Deskripsi Transformasi DimItem....................................................... 108 Tabel 4.17 Tabel Deskripsi Transformasi DimGroupItem ............................................ 109 Tabel 4.18 Tabel Deskripsi Transformasi DimBP ......................................................... 110 Tabel 4.19 Tabel Deskripsi Transformasi DimItemForecast ......................................... 111 Tabel 4.20 Tabel Deskripsi Transformasi FactSalesAmount......................................... 112 xv
Tabel 4.21 Tabel Deskripsi Transformasi FactPurchaseAmount................................... 116 Tabel 4.22 Tabel Deskripsi Transformasi FactInvoice .................................................. 120 Tabel 4.23 Tabel Deskripsi Transformasi FactARAmount............................................ 123 Tabel 4.24 Spesifikasi Perangkat Keras ......................................................................... 161 Tabel 4.25 Spesifikasi Piranti Lunak Server .................................................................. 162 Tabel 4.26 Spesifikasi Piranti Lunak Client................................................................... 162
xvi
DAFTAR GAMBAR
Gambar 2.1 A General System Architecture ...................................................................... 9 Gambar 2.2 Keuntungan dari BI ...................................................................................... 15 Gambar 2.3 Siklus Hidup BI ............................................................................................ 16 Gambar 2.4 Pengertian M endasar BI ............................................................................... 20 Gambar 2.5 Contoh Star Schema ..................................................................................... 31 Gambar 2.6 Penggunaan M icrosoft Time Series.............................................................. 32 Gambar 2.7 Tingkat M anajemen...................................................................................... 33 Gambar 3.1 Struktur Organisasi PT. XYZ ....................................................................... 38 Gambar 3.2 Jaringan Server PT. XYZ ............................................................................. 62 Gambar 3.3 Flowchart Sales Order ................................................................................. 64 Gambar 3.4 Flowchart Purchase Order........................................................................... 66 Gambar 3.5 Flowchart Picking and Sales Delivery - Fertilizer....................................... 68 Gambar 3.6 Flowchart Picking and Sales Delivery - Pesticide & Chemical................... 70 Gambar 3.7 Flowchart A/R Invoice.................................................................................. 72 Gambar 3.8 Flowchart Sistem yang Sedang Berjalan ..................................................... 74 Gambar 3.9 Entity Relationship Diagram (ERD) PT. XYZ............................................. 76 Gambar 4.1 Star Schema FactSalesAmount..................................................................... 99 Gambar 4.2 Star Schema FactPurchaseAmount............................................................. 100 xvii
Gambar 4.3 Star Schema FactInvoice ............................................................................ 101 Gambar 4.4 Star Schema FactARAmount ..................................................................... 102 Gambar 4.5 M enyimpan file Intergration Service Project ............................................. 128 Gambar 4.6 Halaman Utama Intergration Service Project ............................................. 129 Gambar 4.7 M engonfigurasikan Connection M anagers untuk Database OLAP........... 130 Gambar 4.8 M engonfigurasikan Connection M anagers untuk Database OLTP........... 130 Gambar 4.9 Setting Control Flow................................................................................... 131 Gambar 4.10 Setting Execute SQL Task Editor............................................................. 132 Gambar 4.11 Setting Data Flow ..................................................................................... 133 Gambar 4.12 M enyetel OLE DB Source Editor............................................................. 134 Gambar 4.13 M enyetel OLE DB Destination Editor ..................................................... 135 Gambar 4.14 M apping OLE DB Destination................................................................. 136 Gambar 4.15 M emulai Proses Debugging ..................................................................... 136 Gambar 4.16 Hasil Proses Debugging ........................................................................... 137 Gambar 4.17 Hasil Akhir Proses ETL............................................................................ 138 Gambar 4.18 M elakukan Penambahan M ining Structure .............................................. 139 Gambar 4.19 M emilih M etode untuk M endefinisikan M ining Structure ...................... 140 Gambar 4.20 M embuat Struktur Data M ining ............................................................... 140 Gambar 4.21 M emilih Data Source View ...................................................................... 141 Gambar 4.22 M emilih Dimensi yang Akan di-M ining .................................................. 142 xviii
Gambar 4.23 M emilih Key, Input, dan Prediction ......................................................... 142 Gambar 4.24 Dimensi yang Akan Diprediksi ................................................................ 143 Gambar 4.25 Tab M ining M odels .................................................................................. 144 Gambar 4.26 Langkah Awal Sebelum Penyetelan Algorithm Parameters .................... 144 Gambar 4.27 Penyetelan Algorithm Parameters............................................................ 145 Gambar 4.28 Tampilan Forecasting Harga Barang........................................................ 146 Gambar 4.29 Navigation Diagram Aplikasi BI PT. XYZ ............................................. 147 Gambar 4.30 Rancangan Layar Halaman Utama ........................................................... 148 Gambar 4.31 Rancangan Layar Halaman Lupa Password............................................. 149 Gambar 4.32 Rancangan Layar Halaman Home ............................................................ 150 Gambar 4.33 Rancangan Layar Halaman Sales Performance ....................................... 151 Gambar 4.34 Rancangan Layar Halaman Sales Detail .................................................. 152 Gambar 4.35 Rancangan Layar Halaman Forecasting Harga ....................................... 153 Gambar 4.36 Rancangan Layar Halaman Trend ............................................................ 154 Gambar 4.37 Rancangan Layar Halaman Alert.............................................................. 155 Gambar 4.38 Rancangan Layar Halaman Ubah Password ............................................ 156 Gambar 4.39 Rancangan Layar Halaman Contact Us ................................................... 157 Gambar 4.40 Rancangan Layar Halaman Help.............................................................. 158 Gambar 4.41 Flowchart Sistem yang Diusulkan ........................................................... 160
xix
DAFTAR LAMPIRAN
Lampiran-1: Wawancara Dengan Bapak Joko, Business Head – Pesticide & Fertilizer PT. XYZ (29 Oktober 2010) ............................................................................................L1 Lampiran-2: Wawancara Dengan Bapak Arif, Operational Director PT. XYZ (5 November 2010)...............................................................................................................L3 Lampiran-3: Halaman Aplikasi BI PT. XYZ ..................................................................L4 Gambar 1 Halaman Utama ......................................................................................L4 Gambar 2 Halaman Lupa Password ........................................................................L4 Gambar 3 Halaman Home bagian Gauge ................................................................L5 Gambar 4 Halaman Home bagian Graph ................................................................L5 Gambar 5 Halaman Sales Performance ...................................................................L6 Gambar 6 Halaman Sales Detail ..............................................................................L6 Gambar 7 Halaman Forecasting Harga ...................................................................L7 Gambar 8 Halaman Trend .......................................................................................L7 Gambar 9 Halaman Alert .........................................................................................L8 Gambar 10 Halaman Ubah Password ......................................................................L8 Gambar 11 Halaman Contact Us .............................................................................L9 Gambar 12 Halaman Help .......................................................................................L9
xx
