ABSTRAK
Bisnis merupakan salah satu entitas yang menghasilkan big data saat ini. Suatu ruang lingkup bisnis contohnya perusahaan akan membagi bagian bisnisnya menjadi sebuah entitas baru yang akan bekerja lebih terfokus pada satu hal atau bidang tertentu. Implementasi data warehouse pada perusahaan dengan kriteria tersebut akan sangat menguntungkan pihak pengambil keputusan karena sudah mendapatkan data ringkas sehingga memudahkan pengambilan keputusan yang sifatnya strategis bagi perusahaan. Tuntutan akan informasi strategis memunculkan masalah ketika perusahaan belum mempunyai gambaran bagaimana kondisi perusahaan kedepannya khususnya dalam implementasi Sistem Informasi Manajemen yang akan dibangun. Solusi yang bisa diberikan adalah membangun sebuah Arsitektur Bus Data Warehouse Bottom-Up dengan menerapkan konsep sharing dimensi yang akan mengintegrasikan seluruh data mart. Untuk merealisasikan solusi tersebut diperlukan aplikasi yang dibangun yang memiliki fitur utama yaitu melakukan integrasi data mart dan melakukan (Online Analytical Processing) sebagai media presentasi informasi bagi pengguna akhir. Kata Kunci: Data Warehouse, Data Mart, Integrasi, Dimension Sharing.
vii
ABSTRACT
Business is an entity that produces big data nowadays. A business scope, for example, a company will divide its business part into a new entity which will be focused on other specific field. The implementation warehouse data in company with that criteria will be favorable for the party that make decision since they get brief data to ease decision making which is strategic for the company. Demand on the strategic information make problem when the company has no draft of how the company condition in the future specifically in implementation the Management of Information System that will be built. Solution given is by building an Bottom-Up Bus Data Warehouse Architecture by applying dimention sharing concept which will integrate all data mart. In realizing that solution needs application that has main feature that integrating data mart and do (Online Analytical Processing) as presentation information media for the end user. Keywords: Data Warehouse, Data Mart, Integration, Dimension Sharing.
viii
DAFTAR ISI
HALAMAN SAMPUL ........................................................................................ i HALAMAN JUDUL .......................................................................................... ii 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 ............................................................................................ xv DAFTAR KODE PROGRAM ........................................................................ xvi BAB I PENDAHULUAN ................................................................................... 1 1.1 Latar Belakang....................................................................................... 1 1.2 Rumusan Masalah.................................................................................. 4 1.3 Batasan Masalah .................................................................................... 4 1.4 Tujuan ................................................................................................... 4 1.5 Manfaat ................................................................................................. 5 1.6 Sistematika Penulisan ............................................................................ 5 BAB II TINJAUAN PUSTAKA ........................................................................ 7 2.1 State of the Art ....................................................................................... 7 2.2 Data Warehouse .................................................................................. 23 2.2.1 Pendekatan Top-Down................................................................ 24 2.2.2 Pendekatan Bottom-Up ............................................................... 25 2.2.3 On-Line Transaction Processing (OLTP).................................... 26 2.2.4 On-Line Analytical Processing (OLAP) ...................................... 28 2.2.5 Data Mart ................................................................................... 29 2.2.6 Integrasi Dimension Sharing ....................................................... 31 2.2.7 Integrasi Dimension Compatibility.............................................. 31 2.3 Master Data Management.................................................................... 33 2.4 Pemodelan Sistem................................................................................ 42 2.5 Multidimensional Model ...................................................................... 46 2.6 MySQL................................................................................................ 47 2.6.1 Fitur MySQL .............................................................................. 47 2.5.2 PL/SQL ...................................................................................... 48 2.5.2.1 Stored Procedure ................................................................... 49 2.5.2.2 Function ................................................................................ 50 2.5.2.3 Trigger .................................................................................. 50 BAB III METODOLOGI DAN PERANCANGAN PROTOTIPE ................ 53 3.1 Tempat & Waktu Penelitian ................................................................. 53 3.2 Alur Penelitian ..................................................................................... 53 3.2.1 Definisi Masalah & Pengumpulan Data ...................................... 54 ix
3.2.2 Pengumpulan dan Studi Literatur ................................................ 54 3.2.3 Mempelajari dan Memahami Proses dan Alur Kerja Sistem ........ 55 3.2.4 Perancangan Database ................................................................ 56 3.2.5 Pengembangan Aplikasi .............................................................. 57 3.2.6 Pengujian Sistem dan Analisis Hasil Pengujian Sistem ............... 57 3.2.7 Pengambilan Kesimpulan............................................................ 57 3.3 Metode dan Metodologi Penelitian ....................................................... 58 3.3.1 Metode Penelitian ....................................................................... 58 3.3.2 Metodologi Penelitian ................................................................. 58 3.3.3 Kebutuhan Hardware dan Software Perancangan Aplikasi .......... 59 3.4 Pemodelan Sistem................................................................................ 59 3.4.1 Statement of Purpose .................................................................. 59 3.4.2 Daftar Kejadian .......................................................................... 60 3.4.3 Gambaran Umum Sistem ............................................................ 61 3.4.4 Diagram Alir (Flowchart) Sistem................................................ 63 3.4.4.1 Diagram Alir Establish Connection ....................................... 64 3.4.4.2 Diagram Alir Add Party ......................................................... 66 3.4.4.3 Diagram Alir Integrate .......................................................... 69 3.5 Gambaran Proses Integrasi ................................................................... 74 3.6 Perancangan Database ......................................................................... 76 3.6.1 Relationship ................................................................................ 76 3.6.2 Entity Relationship Diagram (ERD) ............................................ 77 3.6.3 Struktur Tabel ............................................................................. 78 3.7 Pengembangan Aplikasi ....................................................................... 90 BAB IV PENGUJIAN DAN ANALISIS HASIL ............................................ 92 4.1 Rancangan Arsitektur........................................................................... 92 4.1.1 Rancangan Sistem....................................................................... 92 4.1.2 Standard Operational Procedure (SOP)...................................... 96 4.1.2.1 SOP Konfigurasi Server MDM .............................................. 96 4.1.2.2 SOP Konfigurasi Server Data Mart ..................................... 101 4.1.2.3 SOP Add Party .................................................................... 102 4.1.2.4 SOP Integrasi....................................................................... 105 4.1.3 Kebutuhan Hardware dan Software Pengujian Aplikasi ............ 106 4.2 Pengujian Sistem ............................................................................... 107 4.2.1 Pengujian Data Mart Mapping .................................................. 107 4.2.2 Pengujian Data Mapping ........................................................... 120 4.2.3 Pengujian Integrasi Data ........................................................... 121 4.2.4 Pengujian Sinkronisasi Data...................................................... 127 4.3 Analisis Hasil Pengujian Sistem ......................................................... 134 4.3.1 Analisis Keberhasilan Sistem .................................................... 134 4.3.2 Analisis Kegagalan Sistem ........................................................ 135 4.3.3 Perbandingan Dimension Sharing dengan Generalization.......... 135 4.3.4 Implementasi Sistem ................................................................. 137 4.3.5 Analisa Kelebihan dan Kekurangan Aplikasi ............................ 138 BAB V SIMPULAN DAN SARAN................................................................ 140 5.1 Simpulan ........................................................................................... 140 x
5.2 Saran ................................................................................................. 140 DAFTAR PUSTAKA ..................................................................................... 142
xi
DAFTAR GAMBAR
Gambar 2.1 Integrasi Dimension Sharing Conformed .......................................... 8 Gambar 2.2 Dimensi Shareable pada 2 Data Mart yang Berbeda.......................... 9 Gambar 2.3 Hasil Integrasi Normal Conformed yang Telah Dilakukan .............. 10 Gambar 2.4 Skema Integrasi dengan Memberikan Pemetaan .............................. 10 Gambar 2.5 Integrasi Master Conformed............................................................ 11 Gambar 2.6 Dimensi Shareable dengan Kriteria Master-Conformed................... 12 Gambar 2.7 Diagram Venn Keterkaitan Data Dosen dan Data Pegawai .............. 12 Gambar 2.8 Hasil Akhir Skema Integrasi Data Mart Master Conformed ............ 13 Gambar 2.9 Contoh Data Pemetaan dengan Data Kosong (Null) ........................ 14 Gambar 2.10 Integrasi Replication Conformed................................................... 14 Gambar 2.11 Skema Akhir Integrasi Replication Conformed ............................. 15 Gambar 2.12 Hasil Akhir Sinkronisasi Replikasi Master-to-Master .................... 15 Gambar 2.13 Hasil Sinkronisasi dengan Data Redundan .................................... 16 Gambar 2.14 Proses Pertukaran Data Menggunakan (API) ................................. 19 Gambar 2.15 Gambaran Umum dan Struktur Tabel Federated Engine ................ 20 Gambar 2.16 Arsitektur Federasi dan Kondisi Arsitektur Federasi ..................... 21 Gambar 2.17 Proses Pencocokan antara Dimensi yang Bersifat Kompatibel ....... 32 Gambar 2.18 Dimensi Algoritma Loosely Coupled Integration .......................... 33 Gambar 2.19 Contoh Arsitektur Master Data Management (MDM) ................... 34 Gambar 2.20 Central Master Data System.......................................................... 36 Gambar 2.21 Arsitektur Leading System ............................................................ 38 Gambar 2.22 Arsitektur Master Data Harmonization via Standards .................... 39 Gambar 2.23 Arsitektur Repositori..................................................................... 41 Gambar 2.24 Notasi Cadinality .......................................................................... 44 Gambar 2.25 Simbol Diagram Blok ................................................................... 45 Gambar 3.1 Bagan Alur Penelitian ..................................................................... 53 Gambar 3.2 Gambaran Sistem Integrasi Data Mart ............................................ 61 Gambar 3.3 Flowchart Umum Sistem ................................................................ 64 Gambar 3.4 Flowchart Establish Connection ..................................................... 65 Gambar 3.5 Wireframe Establish Connection dan Add Party.............................. 66 Gambar 3.6 Flowchart Add Party ...................................................................... 67 Gambar 3.7 Flowchart Generate Cloning Dimension ......................................... 68 Gambar 3.8 Implementasi “SHOW CREATE TABLE [nama_tabel]” .................... 69 Gambar 3.9 Flowchart Integrate ........................................................................ 70 Gambar 3.10 Flowchart Mapping ...................................................................... 72 Gambar 3.11 Wireframe (User Interface) Proses Pemetaan ................................ 72 Gambar 3.12 Gambaran Proses Integrasi ............................................................ 74 Gambar 3.13 Struktur Database Sistem Integrator ............................................. 76 Gambar 3.14 ERD Entitas dimensi dan Entitas master_data_manager ............ 77 Gambar 3.15 ERD antara Entitas dimensidan Entitas mapping .................... 78 Gambar 3.16 ERD antara Entitas users dan Entitas password_resets ............. 78 xii
Gambar 4.1 Rancangan Arsitektur Sistem .......................................................... 92 Gambar 4.2 Overview Integrasi Data .................................................................. 94 Gambar 4.3 Layanan Add Party ......................................................................... 94 Gambar 4.4 Antar Muka Add Party, Mark Dimension ........................................ 95 Gambar 4.5 SOP Konfigurasi Server MDM ....................................................... 97 Gambar 4.6 Hasil Eksekusi Query [SHOW ENGINES] ...................................... 98 Gambar 4.7 Kondisi Awal my.ini, Engine Federated belum Aktif ................... 98 Gambar 4.8 Kondisi Akhir my.ini, Engine Federated sudah Aktif .................... 99 Gambar 4.9 Hasil Eksekusi Query Engine Federated Aktif ................................ 99 Gambar 4.10 Listing Database pada Server Lokal............................................ 100 Gambar 4.11 Hasil Uji Coba Federasi Data pada Database Acak ..................... 100 Gambar 4.12 SOP Konfigurasi Server Data Mart............................................. 101 Gambar 4.13 SOP Add Party ........................................................................... 103 Gambar 4.14 Kondisi ketika Sistem Gagal Melakukan Koneksi ....................... 104 Gambar 4.15 Kondisi ketika Sistem Sukses Melakukan Koneksi...................... 104 Gambar 4.16 Hasil Input yang telah Disimpan pada Tabel connection ........... 105 Gambar 4.17 SOP Integrasi.............................................................................. 106 Gambar 4.18 Form Add Party .......................................................................... 108 Gambar 4.19 Komponen Deskripsi Form ......................................................... 108 Gambar 4.20 Komponen Breadcrumb dalam Sebuah Form .............................. 108 Gambar 4.21 Komponen Combobox dalam Sebuah Form ................................ 109 Gambar 4.22 Kondisi Tabel connections ....................................................... 109 Gambar 4.23 Kondisi Form Add Party, Section Create Connection .................. 109 Gambar 4.24 Kondisi Tabel connections setelah Penambahan Data ............... 110 Gambar 4.25 Komponen Combobox yang Telah Terisi Data Baru ................... 110 Gambar 4.26 Komponen Cek Koneksi dan Tambah Koneksi ........................... 110 Gambar 4.27 Komponen Add Party, Koneksi Sukses ....................................... 111 Gambar 4.28 Komponen Combobox Load Database ...................................... 111 Gambar 4.29 Komponen Combobox Load Database dan Tabel ....................... 112 Gambar 4.30 Komponen Sidebar ..................................................................... 112 Gambar 4.31 Struktur Umum Data Mart Uji Coba ........................................... 113 Gambar 4.32 Kondisi Awal Database Sistem................................................... 114 Gambar 4.33 Form Add Party dengan Input Data............................................. 114 Gambar 4.34 Kondisi Awal Tabel federated_tables .................................... 115 Gambar 4.35 Kondisi Akhir Tabel federated_tables.................................... 115 Gambar 4.36 Kondisi Database Sistem setelah Federasi Data .......................... 115 Gambar 4.37 Form Party Overview ................................................................. 116 Gambar 4.38 Form Add Integration dalam Kondisi Kosong ............................. 117 Gambar 4.39 Combobox Second Dimension .................................................... 117 Gambar 4.40 Combobox Second Identifier....................................................... 118 Gambar 4.41 Form Add Integration dengan Data Lengkap............................... 118 Gambar 4.42 Form Integration Overview ......................................................... 119 Gambar 4.43 Data Form Integration Overview ................................................ 119 Gambar 4.44 Form Integration Overview ......................................................... 120 Gambar 4.45 Kondisi Load Data Form Integration Overview .......................... 120 Gambar 4.46 Form Dashboard ......................................................................... 121 xiii
Gambar 4.47 Komponen Select Integration ...................................................... 121 Gambar 4.48 Komponen First Box dan Second Box......................................... 122 Gambar 4.49 Komponen Header Box ............................................................... 123 Gambar 4.50 Komponen Search Box dan Pager ............................................... 123 Gambar 4.51 Komponen Data Tables ............................................................... 123 Gambar 4.52 Komponen Footer Pagination ...................................................... 124 Gambar 4.53 Komponen View of Integrated Data ............................................ 124 Gambar 4.54 Identifikasi Data View of Integrated Data ................................... 125 Gambar 4.55 Hasil Pencarian Komponen First Box dan Second Box ............... 126 Gambar 4.56 Kondisi Awal Data Peminjam Buku ............................................ 128 Gambar 4.57 Hasil Pengubahan Data Integrasi ................................................. 128 Gambar 4.58 Kondisi Akhir Data Peminjam Buku ........................................... 129 Gambar 4.59 Data Baru yang Menjadi Obyek Uji ............................................ 129 Gambar 4.60 Kondisi Data Baru Di dalam Sistem ............................................ 130 Gambar 4.61 Data Baru Dimensi Peminjam Buku ............................................ 130 Gambar 4.62 Pengecekan pada Komponen Dashboard ..................................... 131 Gambar 4.63 Komponen Integration Overview ................................................ 132 Gambar 4.64 Hasil Sinkronisasi ....................................................................... 134
xiv
DAFTAR TABEL
Tabel 2.1 Karakteristik Central Master Data System .......................................... 37 Tabel 2.2 Karakteristik Leading System ............................................................. 38 Tabel 2.3 Karakteristik Master Data Harmonization via Standards ..................... 40 Tabel 2.4 Karakteristik MDM Repository .......................................................... 41 Tabel 3.1 Struktur Tabel connection ................................................................ 79 Tabel 3.2 Contoh Data Tabel connection ......................................................... 80 Tabel 3.3 Struktur Tabel mapping ...................................................................... 82 Tabel 3.4 Contoh Data Tabel mapping ............................................................... 83 Tabel 3.5 Struktur Tabel data_mapping ............................................................ 84 Tabel 3.6 Contoh Data Tabel data_mapping ..................................................... 85 Tabel 3.7 Contoh Tabel yang akan Diintegrasikan ............................................. 85 Tabel 3.8 Struktur Tabel migration .................................................................. 85 Tabel 3.9 Contoh Data Tabel migrations ......................................................... 86 Tabel 3.10 Struktur Tabel users ........................................................................ 87 Tabel 3.11 Contoh Data Tabel users ................................................................. 89 Tabel 3.12 Struktur Tabel password_resets ..................................................... 89 Tabel 3.13 Contoh Data Tabel password_resets .............................................. 90 Tabel 4.1 Perbandingan Metode Integrasi ........................................................ 137 Tabel 4.2 Tabel Contoh Organisasi yang Dapat Menggunakan Sistem ............. 137
xv
DAFTAR KODE PROGRAM
Kode Program 2.1 Sintaks Dasar Membuat Stored Procedure ............................ 50 Kode Program 2.2 Perintah Memanggil Stored Procedure ................................. 50 Kode Program 2.3 Sintaks Dasar Membuat Function ......................................... 50 Kode Program 2.4 Sintaks Dasar Trigger ........................................................... 52 Kode Program 3.1 Query Kloning Federated Engine .......................................... 69 Kode Program 3.2 Sintaks Umum Kloning Dimensi dengan Federated Engine .. 81 Kode Program 3.3 Generasi Tabel dengan Eloquent Laravel .............................. 87 Kode Program 4.1 Contoh Query Kustom .......................................................... 93 Kode Program 4.2 Sintaks Pemberian Akses Via MySQL .................................. 95 Kode Program 4.3 Query untuk Melakukan Cek Status Engine DB .................... 97 Kode Program 4.4 Query Uji Coba Federasi Data ............................................ 100 Kode Program 4.5 Query Granting Access untuk Memberikan Akses DB ........ 102 Kode Program 4.6 MySQL Event Scheduler .................................................... 132 Kode Program 4.7 Fungsi Eksekusi Seluruh Prosedur ...................................... 133
xvi
