RANCANG BANGUN SKEMA INTEGRASI DATA MART MENGGUNAKAN GENERALIZATION
TUGAS AKHIR
Diajukan guna memenuhi sebagian persyaratan dalam rangka menyelesaikan Pendidikan Sarjana Strata Satu (S1) Program Studi Teknologi Informasi
I Gede Sugita Aryandana NIM: 1204505091
JURUSAN TEKNOLOGI INFORMASI FAKULTAS TEKNIK UNIVERSITAS UDAYANA 2016
RANCANG BANGUN SKEMA INTEGRASI DATA MART MENGGUNAKAN GENERALIZATION
TUGAS AKHIR
Diajukan guna memenuhi sebagian persyaratan dalam rangka menyelesaikan Pendidikan Sarjana Strata Satu (S1) Program Studi Teknologi Informasi
I Gede Sugita Aryandana NIM: 1204505091
JURUSAN TEKNOLOGI INFORMASI FAKULTAS TEKNIK UNIVERSITAS UDAYANA 2016
LEMBAR PERNYATAAN
Dengan ini saya menyatakan bahwa Tugas Akhir ini tidak terdapat karya yang pernah diajukan untuk memperoleh gelar kesarjanaan diperguruan tinggi lain, dan sepanjang pengetahuan saya tidak terdapat karya atau pendapat yang pernah ditulis atau diterbitkan oleh orang lain, kecuali yang secara tertulis diacu dalam naskah ini dan disebutkan pada daftar pustaka.
Badung, September 2016
I Gede Sugita Aryandana
KATA PENGANTAR
Puji dan syukur penulis panjatkan kehadapan Ida Sang Hyang Widhi Wasa/Tuhan Yang Maha Esa, karena atas Asung Kerta Wara Nugraha-Nya, penulit dapat menyelesaikan tugas akhir dengan judul “Rancang Bangun Skema Integrasi Data mart Menggunakan Generalization”. Selama pelaksanaan tugas akhir penulis mendapat masukan dan bimbingan dari berbagai pihak, untuk itu penulis ingin mengucapkan rasa terima kasih kepada: 1.
Bapak Prof. Ir. Ngakan Putu Gede Suardana, MT.,Ph.D, selaku Dekan Fakultas Teknik universitas Udayana.
2.
Bapak Dr. Eng. I Putu Agung Bayupati, ST.,MT, selaku Ketua Jurusan Teknologi Informasi Universitas Udayana.
3.
Bapak I Made Sukarsa, S.T.,M.T., selaku dosen pembimbing I, yang telah memberikan bimbingan selama menempuh pendidikan di Jurusan Teknologi Informasi Fakultas Teknik Universitas Udayana.
4.
Bapak Putu Wira Buana, S.Kom., MT., selaku dosen pembimbing II, yang telah memberikan bimbingan selama menempuh pendidikan di Jurusan Teknologi Informasi Fakultas Teknik Universitas Udayana.
5.
Kedua orang tua dan keluarga yang telah memberikan dukungan dan motivasi dalam pembuatan tugas akhir ini.
6.
Teman-teman seperjuangan dan segenap civitas di Jurusan Teknologi Informasi Universitas Udayana yang telah memberikan sumbangan ide, pemikiran dan dukungan dalam penyusunan tugas akhir ini. Penulis menyadari bahwa tugas akhir ini masih jauh dari sempurna. Akhir
kata penulis memohon maaf jika ada kesalahan dalam penulisan tugas akhir ini.
Denpasar, 24 September 2016
I Gede Sugita Aryandana
ABSTRAK
Integrasi adalah proses penggabungan data yang dilakukan terhadap sistem berbeda untuk menjadi satu (1) atau mempunyai makna yang sama atau proses untuk membuat sesuatu menjadi utuh kembali dengan sumber yang berbeda-beda. Manfaat integrasi yaitu untuk memudahkan administrator dalam mengelola sistem dengan mudah dalam 1 wadah yang mempunyai fungsi berbeda-beda tetapi mempunyai makna yang sama, kesulitan dari proses integrasi ini adalah mencari kesamaan informasi atau data agar bisa dilakukan proses integrasi untuk setiap sistem yang berbeda. Data warehouse merupakan gudang data yang berguna untuk menyimpan semua history data yang dimiliki oleh instansi atau perusahaan disetiap periodenya. Penelitian ini menggunakan metode Generalization untuk melakukan proses integrasi. Generalization merupakan sebuah metode integrasi yang berguna untuk meminimalisir atau memperkecil perbedaan data yang ada berdasarkan Subclass yang kemudian Subclass tersebut digabungkan menjadi sebuah Superclass yang berguna untuk menampung beberapa data dari Subclass yang ada. Software yang digunakan untuk membuat penelitian ini adalah MySQL Framework Laravel, PHP dan Javascript. Hasil uji coba yang didapatkan adalah kumpulan dari beberapa Subclass yang telah ditentukan atau dipilih kemudian membentuk sebuah Superclass yang berguna untuk menampung sumber informasi dari Subclass. Kata Kunci: Integrasi, Data Warehouse, Generalization.
ABSTRACT
Integration is a process of merging data which is performed on a different system to be one (1) or have the same significance or the process for conducting things in complete form from different sources. The advantage of integration is to allow users in order to manage the system easily in one container that has different functions but has the same meaning. The difficulty of integration process is to find the similarity of the information or data in order to do integration process in each different system. Warehouse data is a data that is used to in order to store all data history owned by institutions or companies in each period. This study applied a generalization method in order to conduct the integration process. Generalization is an integration method that aims to minimize or reduce the differences of existing data in accordance with the Subclass. Then, subclass is combined into Superclass which is useful to collect some data from the existing Subclass. Software applied to create this research is MySQL and Laravel framework. The result is a collection of some Subclasses predetermined or selected. Afterwards, it forms a Superclass that is useful to accommodate the information sources from Subclass. Keywords: Integration, Data Warehouse, Generalization
DAFTAR ISI
HALAMAN SAMPUL ........................................................................................... i HALAMAN JUDUL ............................................................................................. ii LEMBAR PERNYATAAN ................................................................................. iii LEMBAR PENGESAHAN TUGAS AKHIR .................................................... iv BERITA ACARA TUGAS AKHIR ..................................................................... v KATA PENGANTAR .......................................................................................... vi ABSTRAK ........................................................................................................... vii ABSTRACT ......................................................................................................... viii DAFTAR ISI ......................................................................................................... ix DAFTAR GAMBAR ........................................................................................... xii DAFTAR TABEL .............................................................................................. xiv DAFTAR KODE PROGRAM ........................................................................... xv BAB I PENDAHULUAN ...................................................................................... 1 1.1 Latar Belakang ................................................................................................. 1 1.2 Rumusan Masalah ............................................................................................ 3 1.3 Tujuan .............................................................................................................. 4 1.4 Manfaat ............................................................................................................ 4 1.5 Batasan Masalah .............................................................................................. 4 1.6 Sistematika Penulisan ...................................................................................... 5 BAB II TINJAUAN PUSTAKA........................................................................... 6 2.1 State of the Art ................................................................................................. 6 2.2 Definisi Data Warehouse ................................................................................. 9 2.2.1 Karakteristik Data Warehouse .............................................................. 10 2.2.2 Tabel Fakta ............................................................................................ 11 2.2.3 Tabel Dimensi ....................................................................................... 11 2.2.4 Pendekatan Data Warehouse ................................................................ 12 2.3 Definisi Extraction Transformation Loading (ETL)...................................... 14 2.3.1 Proses Extraction Transformation Loading (ETL) ............................... 14 2.4 Definisi Online Transaction Processing (OLTP) .......................................... 14 2.5 Definisi Online Analytical Processing (OLAP) ............................................ 16 2.5.1 Arsitektur Three Tier Data Warehouse ................................................ 16 2.5.2 Karakteristik Online Analytical Processing (OLAP)............................ 17 2.5.3 Teknik Analisa Online Analytical Processing (OLAP) ........................ 18 2.5.4 Keuntungan Online Analytical Processing (OLAP) ............................. 18 2.6 Pemodelan Sistem .......................................................................................... 19 2.7 Multidimensional Model ................................................................................ 21 2.8 Definisi Data mart ......................................................................................... 22 2.8.1 Karakteristik Data mart ........................................................................ 22 2.8.2 Cara Implementasi Data mart ............................................................... 23 2.9 Definisi Generalization .................................................................................. 25 2.9.1 Konsep Generalization ......................................................................... 29
2.9.2 Pendekatan Generalization Dengan Data Warehouse .......................... 30 2.9.3 Mapping Generalization ....................................................................... 31 2.9.4 Langkah-langkah Mapping Generalization .......................................... 33 2.9.5 Struktur Data Generalization di dalam Data Warehouse...................... 37 2.9.6 Fungsi Generalization di dalam Data Warehouse ................................ 38 2.10 Federated Storage Engine............................................................................. 39 2.11 MySQL.......................................................................................................... 41 2.11.1 Tipe Data MySQL............................................................................... 42 2.11.2 Kelebihan MySQL .............................................................................. 44 2.12 PHP ............................................................................................................... 45 2.12.1 Keunggulan PHP ................................................................................. 46 2.12.2 Kekurangan PHP ................................................................................. 46 2.13 Pengertian JavaScript .................................................................................... 46 2.13.1 Fungsi JavaScript ................................................................................ 47 2.13.2 Kelebihan JavaScript .......................................................................... 47 2.13.3 Kekurangan JavaScript ....................................................................... 48 BAB III METODE DAN PERANCANGAN SISTEM .................................... 49 3.1 Tempat dan Waktu Penelitian ........................................................................ 49 3.2 Alur Penelitian ............................................................................................... 49 3.2.1 Analisa dan Pengumpulan Data ............................................................ 51 3.2.2 Pengumpulan Data ................................................................................ 51 3.2.3 Proses Perancangan Sistem ................................................................... 51 3.2.4 Perancangan Data Mart ........................................................................ 52 3.2.5 Perancangan User Interface System ...................................................... 52 3.2.6 Uji Coba Sistem .................................................................................... 52 3.2.7 Analisis Sistem dan Kesimpulan ........................................................... 52 3.2.8 Flowchart Generaization ...................................................................... 53 3.3 Literatur .......................................................................................................... 54 3.3.1 Sumber Literatur ................................................................................... 54 3.3.2 Jenis Literatur ........................................................................................ 54 3.3.3 Metode Pengumpulan Literatur............................................................. 54 3.4 Metode dan Metodologi Penelitian ................................................................ 54 3.4.1 Metode Penelitian ................................................................................. 55 3.4.2 Metodologi Penelitian ........................................................................... 55 3.5 Sumber Data ................................................................................................... 55 3.5.1 Data Primer ........................................................................................... 55 3.6 Bahasa Pemograman ...................................................................................... 56 3.7 Perangkat Pemodelan Sistem ......................................................................... 56 3.7.1 Tujuan Pembuatan Sistem ..................................................................... 56 3.7.2 Gambaran Umum .................................................................................. 56 3.8 Karakteristik Pengguna .................................................................................. 59 3.9 Flowchart Check Connection ........................................................................ 59 3.10 Flowchart Pembuatan Superclass ................................................................. 60 3.11 Gambaran Proses Integrasi ............................................................................ 61 3.12 Struktur Database Sistem ............................................................................. 62 3.13 Struktur Data mart Library ........................................................................... 72
3.14 Struktur Data Mart SIMAK .......................................................................... 80 3.15 Entity Relationship Diagram (ERD) ............................................................. 87 BAB IV HASIL DAN PEMBAHASAN ............................................................ 89 4.1 Rancangan Arsitektur Sistem ......................................................................... 89 4.2 Standard Operational Procedure (SOP) ....................................................... 90 4.2.1 SOP Konfigurasi Server ....................................................................... 90 4.3 Kode Program ................................................................................................ 92 4.3.1 Kode Program Create Master Connection ............................................ 92 4.3.2 Kode Program Create Superclass......................................................... 92 4.3.3 Kode Program View Data ..................................................................... 93 4.3.4 Kode Program View Data Detail .......................................................... 94 4.3.5 Kode Program Federated Table ........................................................... 95 4.4 SOP Create Superclass ................................................................................... 95 4.5 Uji coba .......................................................................................................... 97 4.5.1 Panel Login ........................................................................................... 97 4.5.2 Panel Sistem ......................................................................................... 98 4.6 SOP Integrasi ............................................................................................... 110 4.7 Pengujian Integrasi Data .............................................................................. 112 4.8 Pengujian Sinkronisasi data ......................................................................... 114 4.9 Analisa Sistem ............................................................................................. 118 4.9.1 SIMAK (Sistem Informasi Akademik) ............................................... 118 4.9.2 SIMPEG (Sistem Informasi Kepegawaian) ........................................ 119 4.9.3 Hasil Analisa SIMAK dan SIMPEG dengan Federated Table ........... 121 4.9.4 Analisa Perubahan Data Tanpa Federated Table ............................... 123 4.10 Kelebihan sistem ......................................................................................... 125 4.11 Kekurangan Sistem ..................................................................................... 126 BAB V PENUTUP ............................................................................................. 127 5.1 Kesimpulan .................................................................................................. 127 5.2 Saran ............................................................................................................ 127 DAFTAR PUSTAKA ........................................................................................ 128
DAFTAR GAMBAR
Gambar 2.1 Arsitektur Three Tier (Warehousing and Online Analytical Processing, 2015) .................................................................................................. 17 Gambar 2.2 Proses Generalization ....................................................................... 28 Gambar 2.3 Struktur Database Generalization .................................................... 28 Gambar 2.4 Disjoint Constraint ............................................................................ 30 Gambar 2.5 Pendekatan Generalization ............................................................... 31 Gambar 2.6 Maping Generalization ..................................................................... 32 Gambar 2.7 Relasi Genralization.......................................................................... 35 Gambar 2.8 Hasil Mapping Generalization .......................................................... 36 Gambar 2.9 struktur Data mart Library................................................................ 37 Gambar 2.10 Struktur Data mart SIMAK ............................................................ 38 Gambar 2.11 Struktur gabungan dari Data mart Library dan Data mart SIMAK 39 Gambar 2.12 Federated Table .............................................................................. 41 Gambar 3.1 Alur Penelitian................................................................................... 50 Gambar 3.2 Flowchart Generalization ................................................................. 53 Gambar 3.3 Gambaran Umum Sistem .................................................................. 57 Gambar 3.4 Flowchart Check Connection ............................................................ 59 Gambar 3.5 Flowchart Pembuatan Superclass ..................................................... 60 Gambar 3.6 Gambaran Proses Integrasi ................................................................ 61 Gambar 3.7 Struktur Database Sistem ................................................................. 62 Gambar 3.8 Struktur Data mart Library ............................................................... 72 Gambar 3.9 Struktur Data mart SIMAK .............................................................. 80 Gambar 3.10 ERD Entitas dimensi dengan Entitas Management Data................ 87 Gambar 3.11 Entitas dimensi dengan Entitas mapping ........................................ 88 Gambar 3.12 Entitas user dengan Entitas akun .................................................... 88 Gambar 4.1 Rancangan Arsitektur Sistem ............................................................ 89 Gambar 4.2 SOP Konfigurasi Server .................................................................... 91 Gambar 4.3 SOP Create Superclass ..................................................................... 96 Gambar 4.4 Tampilan Login Sistem ..................................................................... 97 Gambar 4.5 Form Registrasi ................................................................................. 97 Gambar 4.6 Kondisi Tabel Users .......................................................................... 98 Gambar 4.7 Tampilan Menu Create Connection .................................................. 98 Gambar 4.8 Kondisi Tabel Connections ............................................................... 99 Gambar 4.9 Menu Create Superclass ................................................................... 99 Gambar 4.10 Kondisi Tabel Superclass .............................................................. 100 Gambar 4.11 Tampilan Menu Memilih Subclass ............................................... 100 Gambar 4.12 Kondisi Tabel Datamarts ............................................................... 101 Gambar 4.13 Menu View Data ............................................................................ 101 Gambar 4.14 Kondisi Tabel Datamars ................................................................ 101 Gambar 4.15 Tampilan Menu View Data Detail ................................................. 102 Gambar 4.16 Menu Integrasi View Data Superclass .......................................... 102 Gambar 4.17 Kondisi Tabel Fields ..................................................................... 103
Gambar 4.18 Menu Integrasi View Detail Superclass ........................................ 103 Gambar 4.19 Menu Integrasi Tabel Fakta........................................................... 104 Gambar 4.20 Menu Dashboard .......................................................................... 104 Gambar 4.21 Menu Sidebar ................................................................................ 105 Gambar 4.22 Struktur Data Mart Localhost Uji Coba ........................................ 106 Gambar 4.23 Struktur Data Mart Virtualbox Uji Coba....................................... 106 Gambar 4.24 Struktur Database Sistem .............................................................. 107 Gambar 4.25 Form Create Superlcass dengan Input Data .................................. 108 Gambar 4.26 Kondisi Awal Tabel Datamarts ..................................................... 108 Gambar 4.27 Kondisi Akhir Tabel Datamarts .................................................... 108 Gambar 4.28 Form Integration View .................................................................. 109 Gambar 4.29 Hasil Integrasi ............................................................................... 109 Gambar 4.30 SOP Integrasi................................................................................. 110 Gambar 4.31 layanan Create Superclass ............................................................ 111 Gambar 4.32 Form Integration ........................................................................... 112 Gambar 4.33 Menu Combobox ........................................................................... 112 Gambar 4.34 Select Subclass .............................................................................. 113 Gambar 4.35 Read Data Superclass Integration ................................................ 113 Gambar 4.36 Hasil Integrasi ............................................................................... 114 Gambar 4.37 Kondisi Awal Superclass_peminjaman buku ............................... 115 Gambar 4.38 Kondisi perubahan data mart library ............................................ 115 Gambar 4.39 Kondisi Akhir Perubahan Data Mart Library ............................... 116 Gambar 4.40 Kondisi Awal Data Mart Library ................................................. 116 Gambar 4.41 Kondisi Akhir Data Mart Library................................................. 117 Gambar 4.42 Hasil penambahan data pada data mart library ............................ 117 Gambar 4.43 Contoh Data Tabel Simak_dim_students ...................................... 119 Gambar 4.44 Contoh Data Tabel lib_dim_borrowers ......................................... 121 Gambar 4.45 Perubahan Data Tabel Lib_dim_borrowers .................................. 121 Gambar 4.46 Perubahan Data Tabel Simak_dim_students ................................. 122 Gambar 4.47 Perubahan Data Tabel Lib_dim_borrowers pada system .............. 122 Gambar 4.48 Perubahan Data Tabel Simak_dim_students pada system ............ 123 Gambar 4.49 Contoh Data Tabel lib_dim_borrowers Tanpa Federated Table .. 123 Gambar 4.50 Contoh Data Tabel Simak_dim_students Tanpa Federated Table 124 Gambar 4.51 Data Tabel Lib_dim_borrowers Pada Sistem................................ 124 Gambar 4.52 Data Tabel Simak_dim_students Pada Sistem .............................. 125
DAFTAR TABEL
Tabel 2.1 Contoh Tabel Fakta ............................................................................. 11 Tabel 2.2 Contoh Tabel Dimensi .......................................................................... 12 Tabel 2.3 Lib_dim_borrowers............................................................................. 26 Tabel 2.4 Simak_dim_students........................................................................... 27 Tabel 2.5 Superclass Peminjaman Buku........................................................... 27 Tabel 2.6 Lib_dim_borrowers............................................................................. 33 Tabel 2.7 Simak_dim_students........................................................................... 34 Tabel 2.8 Superclass Peminjaman Buku........................................................... 34 Tabel 2.9 Hasil Superclass peminjaman buku ................................................ 37 Table 3.1 Tabel Data Marts...................................................................................... 63 Table 3.2 Contoh Data Tabel Datamarts ........................................................... 64 Table 3.3 Tabel Users........................................................................................... 64 Table 3.4 Contoh Data Tabel Users ..................................................................... 65 Table 3.5 Tabel Fields......................................................................................... 66 Table 3.6 Contoh Data Tabel Fields ................................................................... 67 Table 3.7 Tabel Connections.............................................................................. 67 Table 3.8 Contoh Data Tabel Connections ......................................................... 68 Table 3.9 Tabel Faktas......................................................................................... 69 Table 3.10 Contoh Data Tabel Faktas ................................................................ 70 Table 3.11 Tabel Superclass............................................................................... 71 Table 3.12 Contoh Data Tabel Superclass ......................................................... 71 Table 3.13 Tabel Lib_dim_books......................................................................... 72 Table 3.14 Contoh Data Tabel Lib_dim_books ................................................... 73 Table 3.15 Tabel Lib_dim_borrowers................................................................. 74 Table 3.16 Contoh Data Tabel Lib_dim_borrowers ........................................... 75 Table 3.17 Tabel Lib_dim_times......................................................................... 75 Table 3.18 Contoh Data Tabel Lib_dim_times ................................................... 76 Table 3.19 Tabel Lib_Fact_Borrowed_books..................................................... 77 Table 3.20 Contoh Data Tabel Lib_Fact_Borrowed_books ............................... 78 Table 3.21 Tabel Lib_fact_borrowers............................................................... 78 Table 3.22 Contoh Data Tabel Lib_fact_borrowers ......................................... 79 Table 3.23 Tabel Simak_dim_departments......................................................... 81 Table 3.24 Contoh Data Tabel Simak_dim_departements ................................. 81 Table 3.25 Tabel Simak_dim_students............................................................... 82 Table 3.26 Contoh Data Tabel Simak_dim_students ......................................... 83 Table 3.27 Tabel Simak_dim_times..................................................................... 83 Table 3.28 Contoh Data Tabel Simak_dim_times ............................................... 84 Table 3.29 Tabel Simak_fact_uc_units............................................................. 84 Table 3.30 Contoh Data Tabel Simak_fact_uc_units ....................................... 86
DAFTAR KODE PROGRAM
Kode Program 4.1 Create Connection .................................................................. 92 Kode Program 4.2 Create Superclass ................................................................... 93 Kode Program 4.3 View Data ............................................................................... 93 Kode Program 4.4 View data detail ...................................................................... 94 Kode Program 4.5 Federated Table ...................................................................... 95
