Universitas Bina Nusantara
Jurusan Teknik Informatika Program Studi Ilmu Komputer Skripsi Sarjana Komputer Semester Ganjil tahun 2007/2008
ANALISIS DAN PERANCANGAN DATA WAREHOUSE MARKETING PADA BINUS UNIVERSITY Ferawati Meliawaty Johan Wijaya Kelas / Kelompok
0800768360 0800771052 0800773291 07 PAT / 02
Abstrak Marketing merupakan salah satu bagian yang penting dalam suatu perusahaan. Oleh karena itu, keputusan yang diambil harus berdasarkan data dan informasi yang akurat sehingga dapat dianalisis dengan baik. Tujuan penelitian ini adalah menganalisis sistem yang sedang berjalan untuk mendapatkan informasi yang diperlukan, mengidentifikasi masalah yang ada pada perusahaan, dan memberikan usulan pemecahan masalah berupa perancangan data warehouse marketing untuk perusahaan. Studi pustaka, survei langsung ke perusahaan, analisis terhadap database perusahaan, dan wawancara dengan pihak eksekutif dilakukan untuk menganalisis sistem dan memperoleh data yang dibutuhkan. Penelitian ini menggunakan metodologi nine step yang dikembangkan oleh Kimball untuk merancang sistem. Hasil yang diperoleh dari penelitian ini adalah sebuah rancangan data warehouse beserta aplikasi, yang diharapkan dapat membantu pihak eksekutif dalam melakukan analisis dan mengambil keputusan. Simpulan dalam penelitian ini adalah data warehouse marketing yang dirancang dapat mengatasi masalah dan memenuhi kebutuhan informasi para eksekutif perusahaan, serta membantu pihak eksekutif dalam melihat gambaran kondisi departemen marketing dalam kurun waktu tertentu. Untuk mengembangkan hasil penelitian ini, maka disarankan untuk memonitor dan memelihara data warehouse secara rutin agar senantiasa dapat berfungsi dengan baik.
Kata Kunci : analisis, perancangan, data warehouse, marketing
iv
PRAKATA
Puji syukur penulis ucapkan kepada Tuhan Yang Maha Esa karena atas berkat dan rahmat-Nya penulis dapat menyelesaikan skripsi dengan judul “Analisis dan Perancangan Data Warehouse Marketing pada BINUS UNIVERSITY” ini tepat pada waktunya. Penelitian ini merupakan salah satu syarat untuk menempuh sidang sarjana guna mendapat gelar sarjana komputer pada Fakultas Ilmu Komputer, Jurusan Teknik Informatika, Universitas Bina Nusantara. Atas bantuan dan dukungan secara langsung maupun tidak langsung yang telah diterima, penulis ingin mengucapkan terima kasih yang sebesar-besarnya kepada : 1. Bapak Prof. Dr. Gerardus Polla, M.App.Sc., selaku Rektor BINUS UNIVERSITY. 2. Bapak Ir. Sablin Yusuf, M.Sc., M.Comp.Sc., selaku Dekan BINUS UNIVERSITY. 3. Bapak Fredy Purnomo, S.Kom., M.Kom., selaku ketua Jurusan Teknik Informatika BINUS UNIVERSITY. 4. Bapak H. M. Subekti, BE., M.Sc., selaku dosen pembimbing yang banyak memberikan pengarahan dan bimbingan dalam penulisan penelitian ini. 5. Seluruh staff dosen dan karyawan akademis dan non-akademis BINUS UNIVERSITY yang secara langsung maupun tidak langsung telah menunjang dalam penyelesaian skripsi ini. 6. Bapak Ryan Alfons Kaloh, MM, selaku Chief Marketing Officer BINA NUSANTARA Group yang mengijinkan penulis untuk melakukan penelitian pada Marketing BINUS UNIVERSITY. 7. Seluruh staff Marketing BINUS UNIVERSITY yang telah banyak membantu dalam memberi informasi yang berguna untuk penelitian ini. v
8. Ibu Meyliana, S.Kom., MM, selaku Corporate Function Application Manager BINA NUSANTARA Group. 9. Ibu Lindawati, S.Kom., M.T.I., selaku IS Developer BINA NUSANTARA Group. 10. Orang tua dan saudara-saudara tercinta yang telah memberikan dorongan yang berharga baik secara moril maupun materiil. 11. Rekan-rekan penulis yang selalu membantu dan memberikan dorongan semangat bagi penulis. 12. Serta semua pihak yang namanya tidak dapat disebutkan satu-persatu. Akhir kata, penulis berharap agar penelitian ini dapat bermanfaat bagi pembaca serta dapat dijadikan awal bagi penelitian dan pengembangan selanjutnya, sehingga hasil yang telah diperoleh dapat disempurnakan lebih baik lagi di masa mendatang.
Jakarta, 17 Januari 2008
Penulis
vi
DAFTAR ISI
Halaman Judul Luar ................................................................................................................ i Halaman Judul Dalam .............................................................................................................ii Halaman Persetujuan Hardcover ..........................................................................................iii Abstrak ....................................................................................................................................iv Prakata ......................................................................................................................................v Daftar Isi ................................................................................................................................vii Daftar Tabel ...........................................................................................................................xii Daftar Gambar ......................................................................................................................xiv Daftar Lampiran ...................................................................................................................xvi
BAB 1 PENDAHULUAN 1.1
Latar Belakang............................................................................................................1
1.2
Ruang Lingkup.............................................................................................................2
1.3
Tujuan dan Manfaat.....................................................................................................2
1.4
Metodologi Penelitian .................................................................................................3
1.5
Sistematika Penulisan..................................................................................................4
BAB 2 LANDASAN TEORI 2.1 Teori-Teori Dasar .........................................................................................................6 2.1.1
Data dan Informasi..........................................................................................6
2.1.2
Database ..........................................................................................................7
2.1.3
OLTP (On-Line Transaction Processing) ....................................................7 vii
2.1.4
Data Warehouse..............................................................................................8 2.1.4.1 Pengertian Data Warehouse..............................................................8 2.1.4.2 Karakteristik Data Warehouse..........................................................9 2.1.4.3 Perbandingan Sistem OLTP dengan Data Warehouse..................10 2.1.4.4 Arsitektur Data Warehouse ............................................................11 2.1.4.5 Struktur Data Warehouse................................................................12 2.1.4.6 Aliran Data Data Warehouse..........................................................14 2.1.4.7 Anatomi Data Warehouse...............................................................16 2.1.4.8 Kegunaan Data Warehouse ............................................................18 2.1.4.9 Data Mart.........................................................................................19 2.1.4.10 Perancangan Data Warehouse......................................................20 2.1.4.11 Skema Bintang (Star Schema)......................................................24
2.2
Teori-Teori Khusus....................................................................................................26 2.2.1
Pengertian Pemasaran ...................................................................................26
2.2.2
Konsep Pemasaran ........................................................................................27
2.2.3 stilah-Istilah Marketing.................................................................................28 2.2.4
SWOT ............................................................................................................29
2.2.5
Analisis Lima Kekuatan................................................................................31
BAB 3 ANALISIS SISTEM YANG SEDANG BERJALAN 3.1
Sejarah Perusahaan ....................................................................................................34
3.2
Struktur Organisasi Perusahaan………….... ...........................................................36
3.3
Wewenang dan Tanggung Jawab………….............................................................37
3.4
Visi dan Misi Perusahaan………….... .....................................................................42 viii
3.5
Sistem yang Sedang Berjalan…………...................................................................43 3.5.1
Subjek Data....................................................................................................43
3.5.2
Fungsi Bisnis .................................................................................................44
3.5.3
Proses Bisnis..................................................................................................44
3.5.4
Analisis SWOT..............................................................................................49
3.5.5
Analisis Five Forces .....................................................................................52
3.5.6
Entity Relationship Diagram (ERD)............................................................56
3.5.7 Analisis Aplikasi dan User ...........................................................................57 3.6
Analisis Kebutuhan Data dan Informasi ..................................................................58
3.7
Permasalahan yang Dihadapi....................................................................................60
3.8
Alternatif Pemecahan Masalah .................................................................................60
BAB 4 PERANCANGAN DAN IMPLEMENTASI DATA WAREHOUSE 4.1 Arsitektur Data Warehouse ......................................................................................62 4.2 Rancangan Data Warehouse.....................................................................................64 4.2.1
Pemilihan Proses ...........................................................................................64
4.2.2
Pemilihan Grain ............................................................................................66
4.2.3
Identifikasi dan Penyesuaian Dimensi.........................................................68
4.2.4
Pemilihan Fakta.............................................................................................72
4.2.5
Penyimpanan Pre-calculation di Tabel Fakta .............................................72
4.2.6
Memastikan Table Dimensi..........................................................................74
4.2.7
Pemilihan Durasi Database ..........................................................................79
4.2.8
Melacak Perubahan dari Dimensi Secara Perlahan.....................................80
4.2.9 Penentuan Prioritas dan Model Query .........................................................81 ix
4.3
Rancangan Skema Bintang .....................................................................................111 4.3.1
Skema Bintang CostPerCustomer..............................................................111
4.3.2
Skema Bintang KeefektifanPromosi..........................................................112
4.3.3
Skema Bintang KeefektifanSumberInfo....................................................113
4.3.4 Skema Bintang Kompetitor ........................................................................114 4.3.5 Skema Bintang PenjualanFormulir ............................................................115 4.3.6 Skema Bintang PerbandinganMarket ........................................................116 4.3.7 Skema Bintang SegmentasiPendaftar ........................................................117 4.3.8
Skema Bintang Telemarketing ...................................................................118
4.4
Metadata...................................................................................................................119
4.5
Transformasi Data ...................................................................................................148
4.6
Rancangan Layar .....................................................................................................151
4.7
Kebutuhan Hardware dan Software .......................................................................162
4.8
Kebutuhan Personel.................................................................................................163
4.9
Rencana Implementasi ............................................................................................163 4.9.1
Instalasi ........................................................................................................163
4.9.2
Konversi Data..............................................................................................164
4.10 Rencana Backup Dan Restore.................................................................................164 4.10.1. Backup..........................................................................................................164 4.10.2. Restore .........................................................................................................165 4.11 Evaluasi ....................................................................................................................165 4.11.1. Evaluasi Perancangan .................................................................................165 4.11.2 Evaluasi Aplikasi.........................................................................................167
x
BAB 5 SIMPULAN DAN SARAN 5.1. Simpulan...................................................................................................................169 5.2. Saran .........................................................................................................................170
DAFTAR PUSTAKA.........................................................................................................171 RIWAYAT HIDUP............................................................................................................173 LAMPIRAN-LAMPIRAN.........................................................................................L1-L18 FOTOCOPY SURAT SURVEY Hasil Wawancara
xi
DAFTAR TABEL
Tabel 2.1
Tabel Perbandingan Sistem OLTP dengan Data Warehouse........................10
Tabel 3.1
Tabel Subjek Data pada Marketing BINUS UNIVERSITY .........................43
Tabel 3.2
Tabel Fungsi Bisnis pada Marketing BINUS UNIVERSITY.......................44
Tabel 3.3
Tabel Analisis Matriks SWOT pada Marketing BINUS UNIVERSITY .....51
Tabel 3.4
Tabel Analisis Aplikasi vs User......................................................................58
Tabel 4.1
Tabel Dimensi vs Grain pada Cost per Customer .........................................69
Tabel 4.2
Tabel Dimensi vs Grain pada Keefektifan Promosi ......................................69
Tabel 4.3
Tabel Dimensi vs Grain pada Keefektifan Sumber Info ...............................69
Tabel 4.4
Tabel Dimensi vs Grain pada Kompetitor......................................................70
Tabel 4.5
Tabel Dimensi vs Grain pada Penjualan Formulir.........................................70
Tabel 4.6
Tabel Dimensi vs Grain pada Perbandingan Market.....................................70
Tabel 4.7
Tabel Dimensi vs Grain pada Segmentasi Pendaftar.....................................71
Tabel 4.8
Tabel Dimensi vs Grain pada Telemarketing.................................................71
Tabel 4.9
Tabel Dimensi yang Terdapat Dalam Perancangan Data Warehouse..........76
Tabel 4.10 Tabel DimAgama .............................................................................................76 Tabel 4.11 Tabel DimGelombang......................................................................................76 Tabel 4.12 Tabel DimJenisKegiatan..................................................................................76 Tabel 4.13 Tabel DimJenisKelamin...................................................................................76 Tabel 4.14 Tabel DimJenisPembayaran ............................................................................77 Tabel 4.15 Tabel DimJenisSMU........................................................................................77 Tabel 4.16 Tabel DimJurusanKompetitor .........................................................................77 Tabel 4.17 Tabel DimJurusanS1 ........................................................................................77 xii
Tabel 4.18 Tabel DimJurusanSMU ...................................................................................77 Tabel 4.19 Tabel DimKotaPendaftar .................................................................................78 Tabel 4.20 Tabel DimKotaSMU........................................................................................78 Tabel 4.21 Tabel DimLokasi..............................................................................................78 Tabel 4.22 Tabel DimPekerjaan.........................................................................................78 Tabel 4.23 Tabel DimPeriode ............................................................................................78 Tabel 4.24 Tabel DimPropinsi ...........................................................................................78 Tabel 4.25 Tabel DimSMU ................................................................................................79 Tabel 4.26 Tabel DimSumberInfo .....................................................................................79 Tabel 4.27 Tabel DimUniversitas ......................................................................................79 Tabel 4.28 Tabel DimWaktu ..............................................................................................79 Tabel 4.29 Tabel Durasi Database ....................................................................................80 Tabel 4.30 Tabel Analisis Kapasitas Media Penyimpanan Tabel-Tabel Fakta untuk lima Tahun ........................................................................................................93 Tabel 4.31 Tabel Analisis Kapasitas Media Penyimpanan Tabel-Tabel Dimensi untuk lima Tahun ......................................................................................................110 Tabel 4.32 Tabel Dimensi Agama ...................................................................................120 Tabel 4.33 Tabel Dimensi Gelombang............................................................................120 Tabel 4.34 Tabel Dimensi JenisKegiatan ........................................................................121 Tabel 4.35 Tabel Dimensi JenisKelamin.........................................................................121 Tabel 4.36 Tabel Dimensi JenisPembayaran...................................................................122 Tabel 4.37 Tabel Dimensi JenisSMU..............................................................................122 Tabel 4.38 Tabel Dimensi JurusanKompetitor................................................................123 Tabel 4.39 Tabel Dimensi JurusanS1 ..............................................................................123 xiii
Tabel 4.40 Tabel Dimensi JurusanSMU..........................................................................124 Tabel 4.41 Tabel Dimensi KotaPendaftar .......................................................................124 Tabel 4.42 Tabel Dimensi KotaSMU ..............................................................................125 Tabel 4.43 Tabel Dimensi Lokasi ....................................................................................125 Tabel 4.44 Tabel Dimensi Pekerjaan ...............................................................................126 Tabel 4.45 Tabel Dimensi Periode...................................................................................126 Tabel 4.46 Tabel Dimensi Propinsi..................................................................................128 Tabel 4.47 Tabel Dimensi SMU ......................................................................................128 Tabel 4.48 Tabel Dimensi SumberInfo............................................................................129 Tabel 4.49 Tabel Dimensi Universitas ............................................................................129 Tabel 4.50 Tabel Dimensi Waktu ....................................................................................130 Tabel 4.51 Tabel Fakta CostPerCustomer ......................................................................131 Tabel 4.52 Tabel Fakta Keefektifan Promosi..................................................................133 Tabel 4.53 Tabel Fakta Keefektifan Sumber Info...........................................................135 Tabel 4.54 Tabel Fakta Kompetitor.................................................................................137 Tabel 4.55 Tabel Fakta Penjualan Formulir....................................................................139 Tabel 4.56 Tabel Fakta Perbandingan Market ................................................................141 Tabel 4.57 Tabel Fakta Segmentasi Pendaftar ................................................................143 Tabel 4.58 Tabel Fakta Telemarketing ............................................................................146 Tabel 4.59 Tabel spesifikasi hardware dan software komputer server.........................162 Tabel 4.60 Tabel spesifikasi hardware dan software workstation.................................162 Tabel 4.61 Tabel Jadwal Implementasi ...........................................................................163 Tabel 4.62 Tabel Primary Key .........................................................................................165 Tabel 4.63 Tabel Foreign Key..........................................................................................166 xiv
DAFTAR GAMBAR
Gambar 2.1
Arsitektur Data Warehouse.......................................................................11
Gambar 2.2
Struktur Data Warehouse ..........................................................................14
Gambar 2.3
Aliran Informasi dari Data Warehouse ....................................................16
Gambar 2.4
Contoh Skema Bintang .............................................................................26
Gambar 2.5
Lima Kekuatan yang Menentukan Daya Tarik Stuktural Segmen .........31
Gambar 3.1
Struktur Marketing BINUS UNIVERSITY .............................................36
Gambar 3.2
Diagram Alir Sistem yang Sedang Berjalan.............................................47
Gambar 3.3
Diagram Alir Sistem yang Sedang Berjalan (lanjutan) ...........................48
Gambar 3.4
Analisis Five Forces Marketing BINUS UNIVERSITY ........................54
Gambar 3.5
ERD Marketing BINUS UNIVERSITY...................................................56
Gambar 4.1
Arsitektur Data Warehouse Tepusat pada Marketing BINUS UNIVERSITY...........................................................................................63
Gambar 4.2
Skema Bintang CostPerCustomer ..........................................................111
Gambar 4.3
Skema Bintang KeefektifanPromosi.......................................................112
Gambar 4.4
Skema Bintang KeefektifanSumberInfo.................................................113
Gambar 4.5
Skema Bintang Kompetitor .....................................................................114
Gambar 4.6
Skema Bintang PenjualanFormulir.........................................................115
Gambar 4.7
Skema Bintang PerbandinganMarket .....................................................116
Gambar 4.8
Skema Bintang SegmentasiPendaftar .....................................................117
Gambar 4.9
Skema Bintang Telemarketing ................................................................118
Gambar 4.10
Transformasi Data pada DimAgama ......................................................149
Gambar 4.11
Transformasi Data pada FaktaKompetitor .............................................150 xiv
Gambar 4.12
Rancangan Layar Login...........................................................................151
Gambar 4.13
Rancangan Layar Loading.......................................................................152
Gambar 4.14
Rancangan Layar Utama .........................................................................152
Gambar 4.15
Rancangan Layar Menu System ..............................................................153
Gambar 4.16
Rancangan Layar Change Password ......................................................154
Gambar 4.17
Rancangan Layar Menu Preference........................................................155
Gambar 4.18
Rancangan Layar Menu Transformation................................................156
Gambar 4.19
Rancangan Layar Dimension Tables ......................................................157
Gambar 4.20
Rancangan Layar Fact Tables.................................................................158
Gambar 4.21
Rancangan Layar Menu Report...............................................................159
Gambar 4.22
Rancangan Layar Menu Graph ...............................................................160
Gambar 4.23
Rancangan Layar Menu Help ..................................................................161
xv
DAFTAR LAMPIRAN
Lampiran 1
Tampilan Layar Login............................................................................... L1
Lampiran 2
Tampilan Layar Loading .......................................................................... L1
Lampiran 3
Tampilan Layar Menu Utama .................................................................. L2
Lampiran 4
Tampilan Layar Change Password.......................................................... L2
Lampiran 5
Tampilan Layar DTS Dimensi ................................................................. L3
Lampiran 6
Tampilan Layar DTS Fakta ...................................................................... L4
Lampiran 7
Tampilan Layar Laporan Penjualan Formulir......................................... L5
Lampiran 8
Tampilan Layar Laporan Segmentasi Pendaftar ..................................... L5
Lampiran 9
Tampilan Layar Laporan Cost Per Customer ......................................... L6
Lampiran 10
Tampilan Layar Laporan Keefektifan Media Promosi ........................... L6
Lampiran 11
Tampilan Layar Laporan Kinerja Promosi.............................................. L7
Lampiran 12
Tampilan Layar Laporan Telemarketing ................................................. L7
Lampiran 13
Tampilan Layar Laporan Perbandingan Market ..................................... L8
Lampiran 14
Tampilan Layar Laporan Kompetitor ...................................................... L8
Lampiran 15
Tampilan Layar Grafik Penjualan Formulir............................................ L9
Lampiran 16
Tampilan Layar Grafik Segmentasi Pendaftar ...................................... L10
Lampiran 17
Tampilan Layar Grafik Cost Per Customer .......................................... L11
Lampiran 18
Tampilan Layar Grafik Keefektifan Media Promosi ............................ L12
Lampiran 19
Tampilan Layar Grafik Kinerja Promosi............................................... L13
Lampiran 20
Tampilan Layar Grafik Telemarketing .................................................. L14
Lampiran 21
Tampilan Layar Grafik Perbandingan Market ...................................... L15
Lampiran 22
Tampilan Layar Grafik Kompetitor ....................................................... L16 xvi
Lampiran 23
Tampilan Layar Menu Help ................................................................... L17
Lampiran 24
Tampilan Layar Menu About.................................................................. L18
xvii
