UNIVERSITAS BINA NUSANTARA Jurusan Teknik Informatika Program Studi Strata-1 Skripsi Sarjana Komputer Semester Ganjil 2006/2007
PERANCANGAN DATA WAREHOUSE PENJUALAN PADA PT ISTANA KEMAKMURAN MOTOR (HONDA DAAN MOGOT) Gunawan Bangsa Wijaya / 0700678704 Jemmy / 0700681762 Felicia Cahyadi / 0700716116 Kelas / Kelompok : 07 PGT / 011 Abstrak Tujuan penelitian ini adalah menganalisis, merancang, dan mengembangkan data warehouse penjualan. Data warehouse yang dirancang ini dapat membantu menjelaskan kondisi gambaran dan perkembangan perusahaan dengan cepat, jelas, akurat, dan praktis. Informasi yang dihasilkan dapat digunakan untuk membantu mengambil keputusan dalam menentukan langkah bagi perkembangan perusahaan. Metode penelitian yang digunakan menggunakan ‘Fact Finding Technique’ dan ‘Nine Steps Methodology’ yang dikembangkan oleh Ralph Kimball. Teknik fact finding yang digunakan meliputi examining documentation, interview, observation, dan research. Dari hasil penelitian ditemukan permasalahan pada prosedur analisis informasi penjualan membutuhkan waktu cukup lama dan kurang praktis karena hanya menggunakan pencetakan laporan bulanan dari data detail operasional sebagai landasan analisis dan pengambilan keputusan. Hal ini menyebabkan analisis menjadi kurang luas. Maka diperlukan aplikasi berbasis data warehouse yang mampu mengolah dan menampilkan data menjadi informasi yang rinci, jelas, terintegrasi, historis, dan dalam cara yang fleksibel yang disesuaikan dengan keinginan dan kebutuhan user, sehingga mampu memberikan gambaran yang tepat atas kondisi perusahaan. Penulis menyimpulkan bahwa kebutuhan akan data dan aplikasi pendukung pengambilan keputusan adalah penting bagi perusahaan, agar setiap keputusan yang diambil memiliki landasan yang kuat. Kata Kunci : Analisis, Perancangan, Data Warehouse, Penjualan, Pengambilan Keputusan.
iv
PRAKATA
Puji syukur ke hadirat Tuhan atas karunia dan berkatnya sehingga penulisan skripsi ini dapat diselesaikan dengan baik. Skripsi ini disusun sebagai penulisan hasil Perancangan Data Warehouse untuk kegiatan Penjualan pada PT Istana Kemakmuran Motor. Ucapan terima kasih dan penghargaan penulis sampaikan kepada berbagai pihak yaitu : 1. Bapak Ir. Sablin Yusuf, M.Sc., M.Comp.Sc. selaku Dekan Fakultas Ilmu Komputer Universitas Bina Nusantara. 2. Bapak H. Mohammad Subekti, BE ,M.Sc. selaku Ketua Jurusan Teknik Informatika. 3. Bapak Ashari, S.Kom., M.Kom. selaku dosen pembimbing yang telah banyak membantu dan mengarahkan kami dalam penyusunan hingga selesainya skripsi ini. 4. Pimpinan dan segenap karyawan PT Istana Kemakmuran Motor dan Four Best Synergy (Information Technology Consultant) yang telah memberikan bantuan dan dukungan selama kami melakukan penelitian untuk menyusun skripsi ini. 5. Orangtua dan keluarga tercinta yang telah memberikan dukungan moral maupun material. 6. Seluruh Dosen dan Staff pengajar Universitas Bina Nusantara Jakarta yang telah memberikan bekal ilmu dan pengetahuan kepada penulis. 7. Rekan-rekan yang telah memberikan dukungan serta doa restu kepada penulis sejak awal kuliah hingga selesainya skripsi ini.
iv
8. Semua pihak dan teman-teman yang tidak dapat penulis sebutkan satu-persatu, yang telah membantu penulis dalam menyelesaikan skripsi ini, baik secara langsung maupun tidak langsung.
Penulis berharap skripsi ini dapat dijadikan awal bagi pengembangan selanjutnya, sehingga hasil yang telah diperoleh dapat disempurnakan dan lebih berdaya guna di masa mendatang.
Jakarta, Januari 2007
Penulis
iv
DAFTAR ISI Halaman Judul Luar.......................................................................................................... i Halaman Judul Dalam...................................................................................................... ii Halaman Persetujuan Softcover ..................................................................................... iii Abstrak ........................................................................................................................... iv Prakata ..............................................................................................................................v Daftar Isi ........................................................................................................................ vii Daftar Tabel .................................................................................................................... xi Daftar Gambar ................................................................................................................xv Daftar Lampiran........................................................................................................... xvii
BAB 1 PENDAHULUAN 1.1
Latar Belakang ..........................................................................................1
1.2
Ruang Lingkup..........................................................................................2
1.3 Tujuan dan Manfaat ..................................................................................3 1.4
Metodologi................................................................................................4
1.5
Sistematika Penulisan ..............................................................................5
BAB 2 LANDASAN TEORI 2.1
Teori Database ..........................................................................................8 2.1.1
Definisi Database .........................................................................8
2.1.2
Relational Model...........................................................................8
2.1.3
Relational Database......................................................................9
iv
2.2
2.3
2.1.4
Relational Database Management System....................................9
2.1.5
Pengertian Online Transaction Processing ...............................10
Teori Data warehouse.............................................................................11 2.2.1
Definisi Data Warehouse............................................................11
2.2.2
Goal dari Data Warehouse .........................................................12
2.2.3
Karakteristik Data Warehouse....................................................13
2.2.4
Struktur Data Warehouse ...........................................................20
2.2.5
Komponen DataWarehouse........................................................23
2.2.6
Arsitektur Data Warehouse ........................................................23
2.2.7
Anatomi Data Warehouse...........................................................30
2.2.8
Aliran Data pada Data Warehouse .............................................33
Teori Perancangan Data Warehouse ......................................................38 2.3.1 Perancangan Database dari Data Warehouse.............................38 2.3.2
2.4
Metodologi Perancangan Data Warehouse ................................44
Teori Penjualan .......................................................................................48
BAB 3 ANALISIS SISTEM YANG BERJALAN 3.1
Riwayat Perusahaan ................................................................................50
3.2
Struktur Organisasi .................................................................................51
3.3
Tugas dan Wewenang .............................................................................51
3.4
Prosedur yang Sedang Berjalan ..............................................................60
3.5
Analisis Hardware dan Software ............................................................64
3.6
Analisis Aplikasi dan User .....................................................................65
iv
3.7
Analisis Matrix Kebutuhan Informasi ....................................................67
3.8
Permasalahan yang Dihadapi..................................................................69
3.9
Usulan Pemecahan Masalah ...................................................................70
3.10 Metodologi Perancangan ........................................................................70 3.11 Skema Bintang ........................................................................................75
BAB 4 IMPLEMENTASI DAN EVALUASI 4.1
Arsitektur Data Warehouse ....................................................................79
4.2
Kebutuhan Hardware dan Software ........................................................84
4.3
4.2.1
Kebutuhan Hardware..................................................................84
4.2.2
Kebutuhan Software....................................................................85
Rancangan Implementasi ........................................................................85 4.3.1
Sumber Data................................................................................85
4.3.2
Database Warehouse ..................................................................96
4.3.3
Tabel Dimensi dan Fakta ............................................................96
4.3.4
Tabel Staging ............................................................................100
4.3.5
Tabel Filter Time Stamp ...........................................................106
4.3.6
Stored Procedure UpdateFilterTimeStamp ...............................106
4.3.7
Transformasi Data (Staging dan Warehousing)........................106
4.3.8
Penjelasan Data Transformation Services ................................122
4.3.9
Front End Tool..........................................................................127
4.3.10 Jadwal Implementasi.................................................................127 4.3.11 Rencana Backup dan Recovery .................................................129
iv
4.3.12 Security .....................................................................................131 4.4
Evaluasi.................................................................................................132
BAB 5 SIMPULAN DAN SARAN 5.1
Simpulan ...............................................................................................134
5.2 Saran .....................................................................................................135
DAFTAR PUSTAKA RIWAYAT HIDUP LAMPIRAN SURAT SURVEI SURAT WAWANCARA ANALISIS KEBUTUHAN SURAT WAWANCARA EVALUASI
iv
DAFTAR TABEL
Tabel 2.1
Perbandingan data operasional dan data warehouse .................................19
Tabel 3.1
Tabel Komputer Server PT Istana Kemakmuran Motor............................64
Tabel 3.2
Tabel Komputer Client PT Istana Kemakmuran Motor ............................65
Tabel 3.3
User vs Aplikasi.........................................................................................66
Tabel 3.4
User vs Informasi.......................................................................................67
Tabel 3.5
Tabel Fakta dan Measure...........................................................................71
Tabel 3.6
Tabel Dimensi dan Atribut.........................................................................72
Tabel 3.7
Tabel Durasi Database ..............................................................................73
Tabel 3.8
Tabel Dimensi, Atribut dan Tipe SCD ......................................................73
Tabel 4.1
Tabel Master Aksesoris..............................................................................86
Tabel 4.2
Tabel Master Customer..............................................................................86
Tabel 4.3
Tabel Master Kendaraan ............................................................................87
Tabel 4.4
Tabel Master Kelas Kendaraan..................................................................88
Tabel 4.5
Tabel Master Jenis Kendaraan ...................................................................88
Tabel 4.6
Tabel Master Warna Kendaraan ................................................................89
Tabel 4.7
Tabel Master Model Kendaraan.................................................................89
Tabel 4.8
Tabel Master Tipe Kendaraan....................................................................89
Tabel 4.9
Tabel Master Salesman ..............................................................................90
Tabel 4.10 Tabel Master Supervisor ............................................................................90 Tabel 4.11 Tabel Transaksi Penjualan Kendaraan dan Aksesoris ...............................91 Tabel 4.12 Tabel Transaksi Matching..........................................................................92
iv
Tabel 4.13 Tabel Transaksi Pesanan Kendaraan .........................................................93 Tabel 4.14 Tabel Transaksi Matching Accessories .....................................................94 Tabel 4.15 Tabel Transaksi Penjualan Aksesoris secara Purna Jual............................94 Tabel 4.16 Tabel Master BBN .....................................................................................95 Tabel 4.17 Tabel Master Karoseri................................................................................95 Tabel 4.18 Tabel Transaksi Reserved ..........................................................................96 Tabel 4.19 Tabel Dimensi Aksesoris ...........................................................................97 Tabel 4.20 Tabel Dimensi Customer ...........................................................................97 Tabel 4.21 Tabel Dimensi Kendaraan..........................................................................97 Tabel 4.22 Tabel Dimensi Salesman............................................................................98 Tabel 4.23 Tabel Dimensi Waktu ................................................................................98 Tabel 4.24 Tabel Fakta Penjualan Kendaraan .............................................................98 Tabel 4.25 Tabel Fakta Penjualan KendaraanAcc .......................................................99 Tabel 4.26 Tabel Fakta Aksesoris Purna Jual ..............................................................99 Tabel 4.27 Tabel Staging Aksesoris ..........................................................................100 Tabel 4.28 Tabel Staging Customer...........................................................................100 Tabel 4.29 Tabel Staging Kendaraan.........................................................................101 Tabel 4.30 Tabel Staging Kelas Kendaraan...............................................................101 Tabel 4.31 Tabel Staging Jenis Kendaraan................................................................101 Tabel 4.32 Tabel Staging Warna Kendaraan .............................................................102 Tabel 4.33 Tabel Staging Model Kendaraan .............................................................102 Tabel 4.34 Tabel Staging Tipe Kendaraan.................................................................102 Tabel 4.35 Tabel Staging Salesman...........................................................................103
iv
Tabel 4.36 Tabel Staging Supervisor.........................................................................103 Tabel 4.37 Tabel Staging Penjualan ..........................................................................103 Tabel 4.38 Tabel Staging Matching...........................................................................104 Tabel 4.39 Tabel Staging Pesanan Kendaraan...........................................................104 Tabel 4.40 Tabel Staging Matching Aksesoris ..........................................................105 Tabel 4.41 Tabel Staging Aksesoris Purna Jual.........................................................105 Tabel 4.42 Tabel Filter Time Stamp ..........................................................................106 Tabel 4.43 Staging Aksesoris.....................................................................................107 Tabel 4.44 Warehousing Aksesoris ...........................................................................108 Tabel 4.45 Staging Customer.....................................................................................109 Tabel 4.46 Warehousing Customer............................................................................109 Tabel 4.47 Staging Kelas Kendaraan.........................................................................110 Tabel 4.48 Staging Jenis Kendaraan ..........................................................................111 Tabel 4.49 Staging Warna Kendaraan .......................................................................111 Tabel 4.50 Staging Model Kendaraan........................................................................111 Tabel 4.51 Staging Tipe Kendaraan...........................................................................112 Tabel 4.52 Staging Kendaraan ...................................................................................112 Tabel 4.53 Warehousing Kendaraan..........................................................................113 Tabel 4.54 Staging Supervisor ...................................................................................114 Tabel 4.55 Staging Salesman .....................................................................................115 Tabel 4.56 Warehousing Salesman............................................................................115 Tabel 4.57 Warehousing Waktu.................................................................................116 Tabel 4.58 Warehousing Penjualan Kendaraan .........................................................120
iv
Tabel 4.59 Warehousing Penjualan Aksesoris Kendaraan ........................................120 Tabel 4.60 Warehousing Penjualan Aksesoris Kendaraan Purna Jual.......................122 Tabel 4.61 Jadwal Implementasi................................................................................127 Tabel 4.62 Proses Backup ..........................................................................................129
iv
DAFTAR GAMBAR
Gambar 2.1
Data warehouse berbasis customer ........................................................14
Gambar 2.2
Koleksi data yang memiliki area subjek yang sama diikat dengan common key.............................................................................................15
Gambar 2.3
Area subjek dapat berisi data yang berasal dari media yang berbeda dalam data warehouse ............................................................................15
Gambar 2.4
Pokok persoalan dari integrasi ................................................................16
Gambar 2.5
Pokok persoalan dari nonvolatile............................................................18
Gambar 2.6
Struktur data warehouse .........................................................................22
Gambar 2.7
Arsitektur data warehouse......................................................................24
Gambar 2.8
Skema Bintang Sederhana ......................................................................40
Gambar 2.9
Skema Bintang dengan beberapa Tabel Fakta ........................................41
Gambar 2.10
Skema Bintang dengan Tabel Dimensi Tambahan.................................42
Gambar 2.11
Skema Snowflake ....................................................................................43
Gambar 3.1
Struktur Organisasi PT Istana Kemakmuran Motor ...............................51
Gambar 3.2
Skema Bintang Penjualan Kendaraan.....................................................76
Gambar 3.3
Skema Bintang Penjualan Accessories Kendaraan.................................77
Gambar 3.4
Skema Bintang Penjualan Accessories secara Purna Jual ......................78
Gambar 4.1
Arsitektur Data Warehouse ........................................................................
Gambar 4.2
Jumlah Penjualan Unit Kendaraan Berdasarkan Jenis Kendaraan tertentu pada Bulan Tertentu...................................................................82
iv
Gambar 4.3
Jumlah Penjualan Unit Kendaraan Berdasarkan Salesman Tertentu dengan pada Bulan 3,4,5,6 Tahun 2005..................................................83
Gambar 4.4
Tabel Fakta Penjualan Kendaraan ..........................................................99
Gambar 4.5
DTS Dimensi Aksesoris........................................................................107
Gambar 4.6
DTS Dimensi Customer........................................................................108
Gambar 4.7
DTS Dimensi Kendaraan ......................................................................110
Gambar 4.8
DTS Dimensi Salesman ........................................................................114
Gambar 4.9
DTS Dimensi Waktu.............................................................................116
Gambar 4.10
DTS Fakta Penjualan Kendaraan dan Fakta Aksesoris Kendaraan ......119
Gambar 4.11
DTS Fakta Aksesoris Purna Jual...........................................................121
iv
DAFTAR LAMPIRAN
Gambar L.1
Langkah 1 dan 2................................................................................... L30
Gambar L.2
Langkah 3 dan 4................................................................................... L31
Gambar L.3
Langkah 5............................................................................................. L31
Gambar L.4
Langkah 6, 7, dan 8.............................................................................. L32
Gambar L.5
Langkah 9............................................................................................. L32
Gambar L.6
Langkah 10........................................................................................... L33
Gambar L.7
Langkah 11........................................................................................... L33
Gambar L.8
Langkah 14........................................................................................... L34
Gambar L.9
Langkah 16 (Tampilan Berbentuk Chart) ............................................ L34
Gambar L.10 Langkah 16 (Tampilan Berbentuk Tabel)............................................ L35 Gambar L.11 Unit Kendaraan Terjual Berdasarkan Supervisor (Diagram Batang) .. L35 Gambar L.12 Unit Kendaraan Terjual Berdasarkan Supervisor (Tabel) ................... L35 Gambar L.13 Unit Kendaraan Terjual Berdasarkan Customer (Grafik Lingkaran) .. L35 Gambar L.14 Unit Kendaraan Terjual Berdasarkan Customer (Tabel) ..................... L35 Gambar L.15 Unit Aksesoris Terjual Berdasarkan Tahun (Diagram Garis).............. L35 Gambar L.16 Unit Aksesoris Terjual Berdasarkan Tahun (Tabel) ............................ L35 Gambar L.17 Unit Aksesoris Terjual vs Supervisor, Tahun (Diagram Batang) ........ L35 Gambar L.18 Unit Aksesoris Terjual vs Supervisor, Tahun (Tabel) ......................... L35
iv
