perpustakaan.uns.ac.id
digilib.uns.ac.id
PENGEMBANGAN SISTEM ANALISIS AKADEMIS MENGGUNAKAN OLAP DAN DATA CLUSTERING STUDI KASUS : AKADEMIK UNIVERSITAS SEBELAS MARET SURAKARTA SKRIPSI Diajukan untuk Memenuhi Salah Satu Syarat Mencapai Gelar Strata Satu Jurusan Informatika
Disusun Oleh :
BAKHARUDIN YUSUF BAKHTIAR NIM. M0510013 HALAMAN JUDUL
JURUSAN INFORMATIKA FAKULTAS MATEMATIKA DAN ILMU PENGETAHUAN ALAM UNIVERSITAS SEBELAS MARET SURAKARTA 2015
commit to user i
perpustakaan.uns.ac.id
digilib.uns.ac.id
H ALA M AN P ERSETUJ UAN
commit to user ii
perpustakaan.uns.ac.id
digilib.uns.ac.id
H ALA M ANPEN G ESA H AN
commit to user iii
perpustakaan.uns.ac.id
digilib.uns.ac.id
MOTTO HALAMAN MOTTO
(Penulis)
-
(QS: Al-Baqarah ayat 282)
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
PERSEMBAHAN HALAMAN PERSEMBAHAN Karya ini Penulis persembahkan kepada:
mua
-teman Informatika UNS angkatan
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
KATA PENGANTAR
Segala puji dan syukur penulis ucapkan kepada Allah SWT, yang hanya karena rahmat dan karunia-Nya, penulis dapat menyelesaikan skripsi ini dengan judul Pengembangan Sistem Analisis Akademis Menggunakan OLAP dan Data Clustering Studi Kasus : Akademik Universitas Sebelas Maret Surakarta Penulis menyadari akan hambatan yang ditemui dalam penyusunan skripsi ini. Begitu banyak bimbingan, bantuan, saran, doa dan dukungan yang diberikan dalam penyusunan skripsi ini. Oleh karena itu, penulis mengucapkan terima kasih kepada : 1. Bapak, ibu serta kakak yang senantiasa memberikan dukungan, motivasi dan doa kepada penulis, 2. Bapak Antonius Bima Murti Wijaya S.T.,M.T Selaku Dosen Pembimbing I yang telah memberikan pengarahan selama proses penyusunan Tugas Akhir ini, 3. Bapak Hasan Dwi Cahyono, S.Kom, M.Kom. selaku Dosen Pembimbing II yang telah memberikan masukan, kritik dan saran yang membangun, 4. Team Generate Jadwal UNS yang telah memberi masukkan, dukungan dan motivasi, 5. Teman-teman yang senantiasa selalu berbagi pengetahuan, pengalaman, dan memberikan dukungan dan motivasi. Semoga skripsi ini bermanfaat bagi semua pihak yang berkepentingan.
Surakarta, Februari 2015
Penulis
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
PENGEMBANGAN SISTEM ANALISIS AKADEMIS MENGGUNAKAN OLAP DAN DATA CLUSTERING STUDI KASUS : AKADEMIK UNIVERSITAS SEBELAS MARET SURAKARTA
Bakharudin Yusuf Bakhtiar Jurusan Informatika, Fakultas Matematika dan Ilmu Pengetahuan Alam, Universitas Sebelas Maret
ABSTRAK Kualitas lulusan sangat penting untuk meningkatkan keberhasilan universitas, salah satu cara meningkatkan keberhasilan tersebut adalah dengan melengkapi kemanfaatan sistem informasi. Universitas Sebelas Maret merupakan universitas yang memanfaatkan sistem informasi secara departemental karena beberapa dibangun dengan menggunakan framework dan database yang berbeda. Hal ini akan menyulitkan untuk mengolah dan menganalisis data-data tersebut. Aplikasi pengembangan sistem analisis akademis menggunakan Online analytical processing (OLAP) dan data clustering dapat menjadi solusi untuk mengatasi masalah tersebut sekaligus membantu menganalisis data-data. Proses pembuatan aplikasi ini meliputi perancangan data warehouse, pengambilan data akademik dari sistem penjadwalan, sistem akademik dan sistem penerimaan mahasiswa baru, proses extraction, transformation, loading (ETL), pembangunan cube, pembuatan report, juga pengelompokkan IPK dan lama studi lulusan menggunakan K-means clustering. OLAP dapat mengurangi aktifitas query dan mempercepat respon terhadap permintaan user. Penelitian ini mengembangkan sistem OLAP dan clustering untuk memenuhi kebutuhan user. Kebutuhan user tersebut adalah sistem dapat melakukan drill up dan drill down untuk membantu merekap data akademik dari berbagai dimensi dan mengelompokkan data lulusan. Hasil dari pengelompokkan ini, setiap data lulusan mahasiswa dikelompokan berdasarkan kualitas lulusan mahasiswa yang dinilai berdasarkan IPK dan lama studi. Kata Kunci : ETL, OLAP, K-means clustering.
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
DEVELOPMENT SYSTEM OF ACADEMIC ANALYSIS USING DATA OLAP AND CLUSTERING CASE STUDY : ACADEMIC SEBELAS MARET UNIVERSITY SURAKARTA
Bakharudin Yusuf Bakhtiar Department of Informatics, Mathematic and Natural Science Faculty, Sebelas Maret University
ABSTRACT The graduation quality is very important to improve the success of the university, one of the way is completing the utility of their information system. Sebelas Maret University is one of the university which utilizes information system departementally because it is developed by using different framework and database. This will give a difficulty to manage and analyze the data. The development of academic analysis system using online analytical processing (OLAP) and data clustering can be a solution to solve these problems and help to analyze the data. The development process of this application included data warehouse planning, gathering academic data from scheduling system, academic system, and new admissions system, extraction process, tranformation process, loading process (ETL), cube development, report making, also clustering the GPA and study term of graduation using K-means clustering method. OLAP can decrease the query activity and accelerate the response to the user request. This research develops the OLAP system and clustering to fulfill the user requirement. The user requirement is a system which can do a drill up and drill down to help recapitulating the academic data from various dimension and grouping the graduation data. This grouping result, each graduated student data grouped by graduate quality assessed by GPA and the term of study. Keywords: ETL, OLAP, K-means clustering
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
DAFTAR ISI HALAMAN JUDUL...................................................................................................... i HALAMAN PERSETUJUAN ...................................................................................... ii HALAMAN PENGESAHAN ...................................................................................... iii HALAMAN MOTTO .................................................................................................. iv HALAMAN PERSEMBAHAN .................................................................................... v KATA PENGANTAR.................................................................................................. vi ABSTRAK .................................................................................................................. vii ABSTRACT ............................................................................................................... viii DAFTAR ISI ................................................................................................................ ix DAFTAR TABEL ........................................................................................................ xi DAFTAR GAMBAR .................................................................................................. xii BAB I PENDAHULUAN ............................................................................................. 1 1.1. Latar Belakang................................................................................................... 1 1.2. Rumusan Masalah ............................................................................................. 2 1.3. Batasan Masalah ................................................................................................ 2 1.4. Tujuan Penelitian ............................................................................................... 3 1.5. Manfaat Penelitian ............................................................................................. 3 1.6. Sistematika Penulisan ........................................................................................ 3 BAB II TINJAUAN PUSTAKA................................................................................... 4 2.1. Dasar Teori ........................................................................................................ 4 2.1.1. Data warehouse ................................................................................................. 4 2.1.2. Online Transactional Processing (OLTP) dan Online Analytical Processing . 13 2.1.3. K-Means Clustering ........................................................................................ 14 2.1.4. Sum Square Error (SSE).................................................................................. 18 2.2. Penelitian Terkait............................................................................................. 18 BAB III METODOLOGI PENELITIAN.................................................................... 21 3.1. Tahap Pengumpulan Data................................................................................ 21 3.2. Tahap Analisis ................................................................................................. 21 3.3. Tahap Desain ................................................................................................... 21 3.4. Tahap Implementasi ........................................................................................ 22 3.5. Pengujian Clustering ....................................................................................... 22 BAB IV HASIL DAN PEMBAHASAN .................................................................... 23 4.1. Deskripsi Data ................................................................................................. 23 4.2. Analisis Sistem ................................................................................................ 23 4.3. Desain Sistem .................................................................................................. 27
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
4.4. Implementasi Sistem ....................................................................................... 28 4.4.1. Implementasi Pembersihan Data (ETL) .......................................................... 28 4.4.2. Implementasi Pembuatan Online Analytical Processing (OLAP)................... 45 4.4.3. Implementasi Pembuatan Report OLAP ......................................................... 60 4.4.4. Implementasi K-means Clustering .................................................................. 68 4.5. Pengujian Hasil Clustering .............................................................................. 74 4.6. Pembahasan ..................................................................................................... 75 BAB V PENUTUP...................................................................................................... 76 5.1. Kesimpulan ...................................................................................................... 76 5.2. Saran ................................................................................................................ 76 DAFTAR PUSTAKA ................................................................................................. 77 LAMPIRAN ................................................................................................................ 79
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
DAFTAR TABEL
Tabel 4.1 Sumber Data Jumlah Camaba, Mahasiswa dan Lulusan ............................ 25 Tabel 4.2 Sumber Data IPK dan Lama Studi Lulusan ................................................ 25 Tabel 4.3 Sumber Data Beban SKS Dosen ................................................................. 25 Tabel 4.4 Sumber Data Nilai Mahasiswa.................................................................... 25 Tabel 4.5 Sumber Data Pemakaian Ruang .................................................................. 26 Tabel 4.6 Sumber Data Pengelompokkan Data .......................................................... 26 Tabel 4.7 Tahapan pembangunan data warehouse ..................................................... 28 Tabel 4.8 Daftar Tabel Sumber Data dan Staging Area.............................................. 29 Tabel 4.9 Daftar Tabel DB_Staging2 dan Tabel data_warehouse2 ............................ 36 Tabel 4.10 Sample Data K-means ............................................................................... 69 Tabel 4.11 Hasil perhitungan jarak obyek ke centroid ............................................... 69 Tabel 4. 12 Hasil Perhitungan Pusat Cluster Baru...................................................... 70
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
DAFTAR GAMBAR
Gambar 2.1 Komponen Data warehouse ...................................................................... 6 Gambar 2.2 Data Multidimensional .............................................................................. 9 Gambar 2.3 Measure dari tabel fakta .......................................................................... 10 Gambar 2.4 Hirarki Dimensi Mahasiswa .................................................................... 11 Gambar 2.5 Star Schema ............................................................................................. 12 Gambar 2.6 Snowflake Schema ................................................................................... 12 Gambar 2.7 Flowchart Algoritma K-Means ............................................................... 17 Gambar 4.1 Sumber Data SPMB ................................................................................ 23 Gambar 4.2 Sumber Data SIGEJE .............................................................................. 24 Gambar 4.3 Sumber Data SIAKAD ............................................................................ 24 Gambar 4.4 Rancangan Snowflake Schema ................................................................ 26 Gambar 4.5 Arsitektur Perangkat Lunak ..................................................................... 27 Gambar 4.6 Arsitektur Perangkat Keras ..................................................................... 27 Gambar 4.7 Desain Control Flow pada Container ............................ 30 Gambar 4.8 Desain Data Flow Task Fakultas............................................................. 30 Gambar 4.9 Desain Data Flow Task Prodi ................................................................. 31 Gambar 4.10 Desain Data Flow Task Lokasi ............................................................. 31 Gambar 4.11 Desain Data Flow Task Matakuliah ...................................................... 31 Gambar 4.12 Desain Data Flow Task Plot Dosen ...................................................... 31 Gambar 4.13 Desain Data Flow Task TAS................................................................. 32 Gambar 4.14 Desain Data Flow Task Ruang.............................................................. 32 Gambar 4.15 Desain Data Flow Task Waktu ............................................................. 32 Gambar 4.16 Desain Data Flow Task Kelas ............................................................... 33 Gambar 4.17 Desain Data Flow Task Ruang Waktu .................................................. 33 Gambar 4.18 Desain Data Flow Task Dosen .............................................................. 33 Gambar 4.19 Desain Data Flow Task Gedung ........................................................... 33 Gambar 4.20 Desain Data Flow Task Mapen ............................................................. 34 Gambar 4.21 Desain Data Flow Task r_camaba........................................................ 34 Gambar 4.22 Desain Data Flow Task r_alumni.......................................................... 34 Gambar 4.23 Desain Data Flow Task r_khs ............................................................... 35 Gambar 4.24 Desain Data Flow Task r_mahasiswa ................................................... 35 Gambar 4.25 Desain Control Flow pada Container .................... 37 Gambar 4.26 Desain Data Flow Task DimFakultas ................................................... 38 Gambar 4.27 Desain Data Flow Task DimProdi ........................................................ 38
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
Gambar 4.28 Desain Data Flow Task DimDosen....................................................... 38 Gambar 4.29 Desain Data Flow Task DimSMTA ...................................................... 38 Gambar 4.30 Desain Data Flow Task DimProvinsi.................................................... 39 Gambar 4.31 Desain Data Flow Task DimKabupaten................................................ 39 Gambar 4.32 Desain Data Flow Task DimMahasiswa ............................................... 40 Gambar 4.33 Desain Data Flow Task DimMakul....................................................... 40 Gambar 4.34 Desain Data Flow Task DimTahunAjar................................................ 40 Gambar 4.35 Desain Data Flow Task DimCamaba .................................................... 41 Gambar 4.36 Desain Data Flow Task DimLulusan .................................................... 41 Gambar 4.37 Desain Data Flow Task DimKelas ........................................................ 42 Gambar 4.38 Desain Data Flow Task DimLokasi ...................................................... 42 Gambar 4.39 Desain Data Flow Task DimGedung .................................................... 42 Gambar 4.40 Desain Data Flow Task DimRuang....................................................... 42 Gambar 4.41 Desain Data Flow Task FactCamaba .................................................... 43 Gambar 4.42 Desain Data Flow Task FactBebanDosen............................................. 43 Gambar 4.43 Desain Data Flow Task FactNilai ......................................................... 44 Gambar 4.44 Desain Data Flow Task FactRuang....................................................... 44 Gambar 4.45 Data Source View .................................................................................. 46 Gambar 4.46 Hasil Pembuatan Cube .......................................................................... 47 Gambar 4.47 Hirarki Angkatan, Provinsi, Kabupaten ................................................ 48 Gambar 4.48 Hirarki Angkatan, SMTA...................................................................... 48 Gambar 4.49 Hirarki Angkatan Gender ...................................................................... 48 Gambar 4.50 Hirarki Fakultas, Prodi, Angkatan......................................................... 48 Gambar 4.51 Hirarki Fakultas, Prodi .......................................................................... 49 Gambar 4.52 Hirarki Tahun Wisuda, Provinsi, Kabupaten ........................................ 49 Gambar 4.53 Hirarki tahun Wisuda, SMTA ............................................................... 49 Gambar 4.54 Hirarki Fakultas, Prodi, Angkatan......................................................... 50 Gambar 4.55 Hirarki Tahun Wisuda, Gender ............................................................. 50 Gambar 4.56 Hirarki Tahun Wisuda, Status .............................................................. 50 Gambar 4.57 Hirarki Tahun Akademik, Semester ...................................................... 50 Gambar 4.58 Hirarki Fakultas, Prodi, Dosen .............................................................. 51 Gambar 4.59 Hirarki Angkatan, SMTA...................................................................... 51 Gambar 4.60 Hirarki Angkatan, Agama ..................................................................... 52 Gambar 4.61 Hirarki Angkatan, Gender ..................................................................... 52 Gambar 4.62 Dimension Usage Cube ......................................................................... 56 Gambar 4.63 Penggunaan OLAP Dalam Sharepoint .................................................. 61 Gambar 4.64 Dashboard Laporan Alamat Mahasiswa ............................................... 62
commit to user
perpustakaan.uns.ac.id
digilib.uns.ac.id
Gambar 4.65 Dashboard Laporan Asal SMTA Mahasiswa ....................................... 62 Gambar 4.66 Dashboard Laporan Gender Mahasiswa ............................................... 63 Gambar 4.67 Dashboard Laporan Alamat Lulusan .................................................... 63 Gambar 4.68 Dashboard Laporan asal SMTA Lulusan ............................................. 63 Gambar 4.69 Dashboard Laporan Gender Lulusan .................................................... 64 Gambar 4.70 Dashboard Laporan Status Lulusan ...................................................... 64 Gambar 4.71 Dashboard Laporan Profil IPK ............................................................. 65 Gambar 4.72 Dashboard Laporan Profil Lama Studi ................................................. 65 Gambar 4.73 Dashboard Laporan asal SMTA Camaba ............................................. 66 Gambar 4.74 Dashboard Laporan Gender Camaba.................................................... 66 Gambar 4.75 Dashboard Laporan Agama Camaba .................................................... 66 Gambar 4.76 Dashboard Laporan Pilihan Camaba .................................................... 67 Gambar 4.77 Dashboard Laporan Beban SKS Dosen ................................................ 67 Gambar 4.78 Dashboard Laporan Pemakaian Ruang................................................. 68 Gambar 4.79 Dashboard Laporan Nilai IP Mahasiswa .............................................. 68 Gambar 4.80 Chart hasil K- means Clustering ............................................................ 70 Gambar 4.81 Hasil Pusat Cluster ................................................................................ 71 Gambar 4.82 Dimensi IPK Hasil Clustering............................................................... 71 Gambar 4.83 Dimensi Lama Studi Hasil Clustering................................................... 72 Gambar 4.84 Dimensi Gender Hasil Clustering ......................................................... 72 Gambar 4.85 Dimensi SMTA Hasil Clustering .......................................................... 73 Gambar 4.86 Dimensi Provinsi Hasil Clustering........................................................ 73 Gambar 4.87 Dimensi Kabupaten Hasil Clustering.................................................... 74 Gambar 4.88 Hasil Pengujian SSE.............................................................................. 75
commit to user
