03

UNIVERSITAS BINA NUSANTARA Jurusan Teknik Informatika Skripsi Sarjana Komputer Semester Ganjil tahun 2006/2007

ANALISIS DAN PERANCANGAN DATA WAREHOUSE PENJUALAN DAN PERSEDIAAN PADA PT POSMI STEEL INDONESIA

Monika Mulyani Sugiarto (0700679013) Caroline Setiabudi (0700705232) Yoseph Nerius Canjaya (0700710365) Kelas / Kelompok : 07PBT / 03 Abstrak Tujuan dari pembuatan skripsi ini adalah menganalisis sistem penjualan dan persediaan divisi stamping pada PT Posmi Steel Indonesia untuk mengetahui kebutuhan informasi yang diperlukan dan merancang suatu data warehouse untuk mendukung pengambilan keputusan. Metode penelitian yang digunakan adalah metode analisis dan perancangan. Metode analisis dilakukan dengan teknik survei langsung terhadap sistem yang sedang berjalan pada perusahaan dan studi kepustakaan. Metode perancangan dilakukan dengan cara pembuatan metadata, skema bintang, melakukan transformasi data, dan rancangan aplikasi data warehouse. Dari penelitian yang penulis lakukan ditemukan banyak data yang masih terlalu rinci. Jadi, diperlukan data yang lebih terintegrasi untuk mendukung pengambilan keputusan strategis dari pihak manajerial PT Posmi Steel Indonesia. Simpulan yang dapat diambil adalah perancangan data warehouse menjadi fasilitas yang dapat digunakan untuk mendukung kegiatan analisis sebagai dasar pengambilan keputusan oleh pihak eksekutif pada PT Posmi Steel Indonesia. Kata kunci : analisis, perancangan, data warehouse, penjualan, persediaan

vii

PRAKATA

Puji syukur kepada Tuhan Yang Maha Esa atas berkat dan rahmat-Nya sehingga penulis

dapat

menyelesaikan

skripsi

yang

berjudul

“ANALISIS

DAN

PERANCANGAN DATA WAREHOUSE PENJUALAN DAN PERSEDIAAN PADA PT POSMI STEEL INDONESIA” tepat pada waktunya. Tujuan dari penyusunan skripsi ini adalah untuk memenuhi salah satu syarat untuk memperoleh gelar kesarjanaan pada jenjang pendidikan Strata-1 Jurusan Teknik Informatika di Universitas Bina Nusantara. Pada kesempatan ini penulis juga ingin menyampaikan ucapan terima kasih atas segala bantuan, doa, dukungan, petunjuk, bimbingan, dan saran yang telah diberikan kepada penulis selama penulisan skripsi ini. Terima kasih yang sebesar-besarnya penulis sampaikan kepada : •

Bapak Prof. Dr. Gerardus Polla, M.App.Sc., selaku Rektor Universitas Bina Nusantara.

•

Bapak Ir. Sablin Y., M.Sc., M.CompSc., selaku Dekan Fakultas Ilmu Komputer Universitas Bina Nusantara.

•

Bapak H. Mohammad Subekti, BE, M.Sc., selaku Ketua Jurusan Teknik Informatika Universitas Bina Nusantara

•

Bapak Freddy Purnomo, S. Kom., M.Com., selaku Sekretaris Jurusan Teknik informatika Universitas Bina Nusantara.

•

Bapak Choirul Huda, S.Kom., MM., selaku Dosen Pembimbing yang telah memberikan bimbingan kepada penulis selama proses penyusunan skripsi ini.

viii

•

Bapak Toshihiko Takahashi selaku President Director PT Posmi Steel Indonesia atas kesempatan yang diberikan kepada penulis untuk melakukan survey pada perusahaan yang dipimpinnya.

•

Orang tua tercinta dan saudara-saudara penulis yang telah memberikan dukungan doa, moril maupun materiil selama penyusunan skripsi ini.

•

Teman-teman dan banyak pihak yang tidak dapat disebutkan satu persatu yang telah banyak membantu dan memberikan dukungan moral dan semangat sehingga skripsi ini bisa selesai tepat pada waktunya. Penulis berusaha menyelesaikan skripsi ini dengan sebaik-baiknya namun

penulis menyadari keterbatasan yang sehingga tidak luput dari kesalahan. Karena itu penulis mengharapkan adanya saran dan kritik yang berguna untuk menyempurnakan skripsi ini. Akhir kata penulis juga berharap agar skripsi ini dapat bermanfaat dan berguna bagi para pembaca.

Jakarta, 12 Januari 2007

Penulis

ix

DAFTAR ISI

Halaman Judul Luar ....................................................................................................... i Halaman Judul Dalam ................................................................................................... ii Halaman Persetujuan.................................................................................................... iii Halaman Pernyataan Dewan Penguji ........................................................................... iv Abstrak ........................................................................................................................ vii PRAKATA................................................................................................................. viii DAFTAR ISI................................................................................................................. x DAFTAR TABEL....................................................................................................... xv DAFTAR GAMBAR ................................................................................................ xvii DAFTAR LAMPIRAN.............................................................................................. xxi BAB 1 PENDAHULUAN ............................................................................................ 1 1.1

Latar Belakang ................................................................................................. 1

1.2

Ruang Lingkup................................................................................................. 2

1.3

Tujuan dan Manfaat ......................................................................................... 3

1.4

Metodologi ....................................................................................................... 3

1.5

Sistematika Penulisan ...................................................................................... 5

BAB 2 LANDASAN TEORI........................................................................................ 7 2.1

Konsep Dasar Sistem Informasi....................................................................... 7

2.1.1

Pengertian Data .........................................................................................7

2.1.2

Pengertian Informasi .................................................................................7

2.1.3

Pengertian Sistem Informasi .....................................................................7

x

2.2

Pengertian Teknologi Informasi....................................................................... 8

2.3

Pengertian Database ........................................................................................ 8

2.4

Konsep Dasar Data Warehouse ....................................................................... 8

2.4.1

Pengertian Data Warehouse......................................................................8

2.4.2

Perbandingan sistem OLTP dengan sistem Data Warehouse...................9

2.5

Karakteristik Data Warehouse....................................................................... 10

2.6

Struktur Data Warehouse............................................................................... 14

2.7

Anatomi Data Warehouse.............................................................................. 16

2.7.1

Data Warehouse Fungsional ...................................................................16

2.7.2

Data Warehouse Terpusat.......................................................................17

2.7.3

Data Warehouse Terdistribusi ................................................................17

2.8

Arsitektur Data Warehouse ........................................................................... 18

2.9

Kegunaan Data Warehouse ........................................................................... 19

2.10

Data Mart....................................................................................................... 20

2.11

Perancangan Data Warehouse dengan Skema Bintang ................................. 21

2.11.1

Keuntungan Menggunakan Skema Bintang............................................21

2.11.2

Perancangan Skema Bintang...................................................................23

2.11.3

Skema Bintang Sederhana ......................................................................24

2.11.4

Skema Bintang dengan Banyak Tabel Fakta ..........................................24

2.11.5

Skema Bintang Majemuk........................................................................25

2.11.6

Skema Snowflake ....................................................................................26

2.11.7

Agregasi ..................................................................................................27

2.11.8

Denormalisasi..........................................................................................28

2.12

Definisi Penjualan dan Persediaan ................................................................. 28 xi

2.12.1

Penjualan .................................................................................................28

2.12.2

Persediaan ...............................................................................................30

BAB 3 ANALISIS KEBUTUHAN DATA WAREHOUSE ...................................... 32 3.1

Metode Analisis dan Perancangan ................................................................. 32

3.2

Latar Belakang Perusahaan ............................................................................ 33

3.3

Struktur Organisasi ........................................................................................ 35

3.3.1

Struktur Organisasi .................................................................................35

3.3.2

Tugas dan Wewenang .............................................................................36

3.4

Visi dan Misi Perusahaan............................................................................... 41

3.4.1

Visi ..........................................................................................................41

3.4.2

Misi .........................................................................................................41

3.5

Tujuan Perusahaan ......................................................................................... 41

3.6

Subyek Data dan Fungsi Bisnis ..................................................................... 42

3.6.1

Subyek Data ............................................................................................42

3.6.2

Fungsi Bisnis...........................................................................................43

3.7

Analisa Matriks .............................................................................................. 43

3.8

Analisis Critical Success Factor.................................................................... 47

3.9

Prosedur yang Sedang Berjalan ..................................................................... 47

3.10

Teknologi Informasi PT Posmi Steel Indonesia ............................................ 48

3.10.1

Perangkat Keras ......................................................................................48

3.10.2

Perangkat Lunak .....................................................................................49

3.10.3

Matriks Fungsi Bisnis vs Aplikasi ..........................................................49

3.10.4

Matriks Subyek Data vs Aplikasi............................................................50

3.11

Analisis Kebutuhan Data dan Informasi ........................................................ 50 xii

3.12

Masalah yang Dihadapi.................................................................................. 51

3.13

Pemecahan Masalah....................................................................................... 52

BAB 4 DATA WAREHOUSE YANG DIUSULKAN ............................................. 53 4.1

Arsitektur Data Warehouse ........................................................................... 53

4.2

Rancangan Data Warehouse .......................................................................... 54

4.2.1

Analisis Basis Data .................................................................................56

4.2.2

Metadata .................................................................................................62

4.2.3

Rancangan Skema Bintang .....................................................................71

4.2.4

Transformasi Data Warehouse ...............................................................73

4.2.5

Hirarki Menu...........................................................................................76

4.2.6

Rancangan State Transition Diagram.....................................................77

4.2.7

Prototipe Aplikasi Data Warehouse .......................................................83

4.2.8

Spesifikasi Proses..................................................................................103

4.3

Rancangan Implementasi ............................................................................. 107

4.3.1

Dukungan Perangkat Keras...................................................................107

4.3.2

Dukungan Piranti Lunak .......................................................................108

4.4

Analisis Kapasitas Media Penyimpanan ...................................................... 109

4.4.1

Analisis Tabel Fakta..............................................................................111

4.4.2

Analisis Tabel Dimensi .........................................................................116

4.5

Analisis Pertumbuhan Data.......................................................................... 116

4.6

Analisis Manfaat .......................................................................................... 117

4.7

Rencana Implementasi ................................................................................. 118

4.8

Evaluasi ........................................................................................................ 118

BAB 5 SIMPULAN DAN SARAN.......................................................................... 119 xiii

5.1

Simpulan ...................................................................................................... 119

5.2

Saran............................................................................................................. 119

DAFTAR PUSTAKA ............................................................................................... 121 RIWAYAT HIDUP.................................................................................................. 123 LAMPIRAN – LAMPIRAN ......................................................................................L-1 FOTO COPY SURAT SURVEY

xiv

DAFTAR TABEL

Tabel 2.1 Perbedaan sistem OLTP dengan sistem data warehouse (Connolly dan Begg, 2005,1153) ................................................................ 10 Tabel 3.1 Tabel Subyek data....................................................................................... 42 Tabel 3.2 Tabel Fungsi Bisnis..................................................................................... 43 Tabel 3.3 Matriks Unit Organisasi vs Lokasi.............................................................. 44 Tabel 3.4 Matriks Unit Organisasi vs Subyek Data.................................................... 44 Tabel 3.5 Matriks Unit Organisasi vs Fungsi Bisnis................................................... 45 Tabel 3.6 Matriks Fungsi Bisnis vs Subyek Data ....................................................... 46 Tabel 3.7 Matriks Fungsi Bisnis vs Aplikasi .............................................................. 49 Tabel 3.8 Matriks Subyek Data vs Aplikasi................................................................ 50 Tabel 3.9 Analisis Kebutuhan Data dan Informasi ..................................................... 51 Tabel 4.1 Tabel Master Pelanggan.............................................................................. 56 Tabel 4.2 Tabel Master Produk................................................................................... 57 Tabel 4.3 Tabel Master Jenis Produk.......................................................................... 57 Tabel 4.4 Tabel Master Area....................................................................................... 57 Tabel 4.5 Tabel Master Keterangan Retur .................................................................. 58 Tabel 4.6 Tabel Master Karyawan .............................................................................. 58 Tabel 4.7 Tabel Master Jabatan .................................................................................. 58 Tabel 4.8 Tabel Master Status Kirim .......................................................................... 59 Tabel 4.9 Tabel Transaksi Persediaan......................................................................... 59 Tabel 4.10 Tabel Transaksi Header Penjualan ........................................................... 60

xv

Tabel 4.11 Tabel Transaksi Detil Penjualan ............................................................... 60 Tabel 4.12 Tabel Transaksi Header Retur Penjualan ................................................. 60 Tabel 4.13 Tabel Transaksi Detil Retur Penjualan ..................................................... 61 Tabel 4.14 Tabel Transaksi Header Pengiriman......................................................... 61 Tabel 4.15 Tabel Transaksi Detil Pengiriman............................................................. 62 Tabel 4.16 Tabel FactPenjualan.................................................................................. 63 Tabel 4.17 Tabel FactPersediaan ................................................................................ 64 Tabel 4.18 Tabel FactReturPenjualan ......................................................................... 65 Tabel 4.19 Tabel FactPengiriman ............................................................................... 66 Tabel 4.20 Tabel DimProduk...................................................................................... 67 Tabel 4.21 Tabel DimPelanggan................................................................................. 67 Tabel 4.22 Tabel DimJenisProduk.............................................................................. 68 Tabel 4.23 Tabel DimStatusKirim .............................................................................. 68 Tabel 4.24 Tabel DimArea.......................................................................................... 69 Tabel 4.25 Tabel DimKetRetur................................................................................... 69 Tabel 4.26 Tabel DimWaktu....................................................................................... 70 Tabel 4.27 Tabel Transformasi Data........................................................................... 75 Tabel 4.28 Tabel Analisis Kapasitas Media Penyimpanan untuk Tabel Fakta......... 116 Tabel 4.29 Tabel Analisis Kapasitas Media Penyimpanan untuk Tabel Dimensi .... 116 Tabel 4.30 Tabel Analisis Pertumbuhan Data........................................................... 117 Tabel 4.31 Tabel Jadwal Rencana Implementasi...................................................... 118

xvi

DAFTAR GAMBAR

Gambar 2.1 Data yang berorientasi subyek (Inmon, 2002, p32) ................................ 11 Gambar 2.2 Masalah mengenai integrasi (Inmon, 2002, p33).................................... 12 Gambar 2.3 Masalah mengenai nonvolatility (Inmon, 2002, p34) ............................. 13 Gambar 2.4 Masalah mengenai time variancy (Inmon, 2002, p35)............................ 13 Gambar 2.5 Struktur dari data warehouse (Inmon, 2002, p36) .................................. 14 Gambar 2.6 Arsitektur Dasar Data Warehouse (Poe, 1996, p41)............................... 18 Gambar 2.7 Skema Bintang Sederhana (Poe, 1996, p124) ......................................... 24 Gambar 2.8 Skema Bintang dengan Banyak Tabel Fakta (Poe, 1996, p126)............. 25 Gambar 2.9 Skema Bintang Majemuk (Poe, 1996, p128) .......................................... 26 Gambar 2.10 Skema Snowflake (Poe, 1996, p127)..................................................... 27 Gambar 3.1 Struktur Organisasi PT Posmi Steel Indonesia ....................................... 35 Gambar 4.1 Arsitektur data warehouse terpusat ........................................................ 54 Gambar 4.2 Skema Bintang FactPenjualan................................................................. 71 Gambar 4.3 Skema Bintang FactReturPenjualan........................................................ 71 Gambar 4.4 Skema Bintang FactPersediaan ............................................................... 72 Gambar 4.5 Skema Bintang FactPengiriman .............................................................. 72 Gambar 4.6 Hirarki Menu ........................................................................................... 76 Gambar 4.7 Rancangan STD layar Windows.............................................................. 77 Gambar 4.8 Rancangan STD layar awal aplikasi........................................................ 77 Gambar 4.9 Rancangan STD layar utama aplikasi ..................................................... 78 Gambar 4.10 Rancangan STD submenu File untuk pengguna ”Non-Admin” .......... 78

xvii

Gambar 4.11 Rancangan STD submenu File untuk pengguna ”Admin” ................... 79 Gambar 4.12 Rancangan STD submenu Add User..................................................... 79 Gambar 4.13 Rancangan STD submenu Change Password....................................... 80 Gambar 4.14 Rancangan STD submenu Delete User................................................. 80 Gambar 4.15 Rancangan STD submenu View ............................................................ 81 Gambar 4.16 Rancangan STD submenu Chart........................................................... 81 Gambar 4.17 Rancangan STD submenu Pivot Table.................................................. 81 Gambar 4.18 Rancangan STD submenu Star Schema ................................................ 82 Gambar 4.19 Rancangan STD submenu Tools ........................................................... 82 Gambar 4.20 Rancangan STD submenu About........................................................... 82 Gambar 4.21 Rancangan STD submenu Logout......................................................... 83 Gambar 4.22 Form Awal Aplikasi.............................................................................. 83 Gambar 4.23 Form Login............................................................................................ 84 Gambar 4.24 Pesan kesalahan Form Login jika ada yang tidak diisi ......................... 84 Gambar 4.25 Pesan kesalahan Form Login jika salah mengisi................................... 84 Gambar 4.26 Pesan saat Login berhasil ...................................................................... 85 Gambar 4.27 Menu Utama Aplikasi ........................................................................... 85 Gambar 4.28 Submenu dari menu File bila posisi adalah “Non-Admin”................... 86 Gambar 4.29 Submenu dari menu File bila posisi adalah “Admin”........................... 87 Gambar 4.30 Form Add User...................................................................................... 87 Gambar 4.31 Pesan kesalahan Form Add User bila ada yang kosong........................ 88 Gambar 4.32 Pesan kesalahan jika Password dan Re-type Password tidak sama ...... 88 Gambar 4.33 Pesan kesalahan jika Password tidak 6 karakter................................... 88 Gambar 4.34 Konfirmasi Form Add User................................................................... 89 xviii

Gambar 4.35 Pesan penambahan pengguna telah berhasil ......................................... 89 Gambar 4.36 Pesan kesalahan Form Add User bila Username sudah digunakan ...... 89 Gambar 4.37 Form Change Password........................................................................ 90 Gambar 4.38 Pesan kesalahan jika Password dan Re-type Password tidak diisi ....... 91 Gambar 4.39 Konfirmasi penggantian password........................................................ 91 Gambar 4.40 Pesan penggantian password telah berhasil .......................................... 91 Gambar 4.41 Form Delete User.................................................................................. 92 Gambar 4.42 Pesan kesalahan Form Delete User jika Username tidak dipilih.......... 92 Gambar 4.43 Pesan kesalahan Form Delete User jika Username sedang dipakai ..... 93 Gambar 4.44 Konfirmasi Form Delete User............................................................... 93 Gambar 4.45 Pesan penghapusan pengguna telah berhasil......................................... 93 Gambar 4.46 Pesan jendela Log Out........................................................................... 94 Gambar 4.47 Menu View............................................................................................. 94 Gambar 4.48 Menu View Chart .................................................................................. 95 Gambar 4.49 Pesan kesalahan bila field tidak dipilih ................................................. 96 Gambar 4.50 Menu View Pivot Table ......................................................................... 97 Gambar 4.51 Pesan kesalahan bila field tidak dipilih ................................................. 98 Gambar 4.52 Menu View Star Schema ....................................................................... 99 Gambar 4.53 Menu DTS ............................................................................................. 99 Gambar 4.54 Konfirmasi jika tombol Execute All Packages dipilih ........................ 100 Gambar 4.55 Menu DTS jika tombol Execute All Packages dipilih......................... 100 Gambar 4.56 Pesan jika proses DTS sudah selesai................................................... 100 Gambar 4.57 Menu About ......................................................................................... 101 Gambar 4.58 Form About Application...................................................................... 102 xix

Gambar 4.59 Form About Us .................................................................................... 102

xx

DAFTAR LAMPIRAN

Gambar 1. Form Awal Aplikasi.................................................................................L-1 Gambar 2. Form Login...............................................................................................L-1 Gambar 3. Menu Utama Aplikasi ..............................................................................L-2 Gambar 4. Submenu dari menu File bila posisi adalah “Non-Admin”......................L-2 Gambar 5. Submenu dari menu File bila posisi adalah “Admin”..............................L-3 Gambar 6. Form Add User.........................................................................................L-3 Gambar 7. Form Change Password...........................................................................L-4 Gambar 8. Form Delete User.....................................................................................L-4 Gambar 9. Menu View................................................................................................L-5 Gambar 10. Menu View Chart ...................................................................................L-5 Gambar 11. Menu Awal View Chart Penjualan, Retur Penjualan, dan Pengiriman..L-6 Gambar 12. Menu Awal View Chart Persediaan .......................................................L-6 Gambar 13. Menu View Chart Penjualan 2D Bar......................................................L-7 Gambar 14. Menu View Chart Retur Penjualan 3D Bar............................................L-7 Gambar 15. Menu View Chart Pengiriman Pie .........................................................L-8 Gambar 16. Menu View Pivot Table ..........................................................................L-8 Gambar 17. Menu Awal View Pivot Table Penjualan................................................L-9 Gambar 18. Menu Awal View Pivot Table Retur Penjualan......................................L-9 Gambar 19. Menu Awal View Pivot Table Pengiriman...........................................L-10 Gambar 20. Menu Awal View Pivot Table Persediaan ............................................L-10 Gambar 21. Menu View Pivot Table Penjualan .......................................................L-11

xxi

Gambar 22. Menu Print Preview Pivot Table Penjualan.........................................L-11 Gambar 23. Menu View Chart 2D Bar Pivot Table Penjualan ................................L-12 Gambar 24. Menu View Detail Pivot Table Penjualan ............................................L-12 Gambar 25. Menu View Pivot Table Retur Penjualan .............................................L-13 Gambar 26. Menu Print Preview Pivot Table Retur Penjualan ...............................L-13 Gambar 27. Menu View Chart 3D Bar Pivot Table Retur Penjualan ......................L-14 Gambar 28. Menu View Detail Pivot Table Retur Penjualan ..................................L-14 Gambar 29. Menu View Pivot Table Pengiriman.....................................................L-15 Gambar 30. Menu Print Preview Pivot Table Pengiriman ......................................L-15 Gambar 31. Menu View Chart 3D Line Pivot Table Pengiriman ............................L-16 Gambar 32. Menu View Detail Pivot Table Pengiriman..........................................L-16 Gambar 33. Menu View Pivot Table Persediaan......................................................L-17 Gambar 34. Menu Print Preview Pivot Table Persediaan .......................................L-17 Gambar 35. Menu View Chart 2D Line Pivot Table Persediaan .............................L-18 Gambar 36. Menu View Detail Pivot Table Persediaan...........................................L-18 Gambar 37. Menu View Star Schema Penjualan......................................................L-19 Gambar 38. Menu View Star Schema Retur Penjualan............................................L-19 Gambar 39. Menu View Star Schema Persediaan ....................................................L-20 Gambar 40. Menu View Star Schema Pengiriman ...................................................L-20 Gambar 41. Menu Tools...........................................................................................L-21 Gambar 42. Form DTS ............................................................................................L-21 Gambar 43. Form DTS jika tombol Execute All Packages dipilih ..........................L-22 Gambar 44. Menu About ..........................................................................................L-22 Gambar 45. Form About Application.......................................................................L-23 xxii

Gambar 46. Form About Us .....................................................................................L-23

xxiii