INTELIGENSI BISNIS SQL SERVER 2014

Sanksi Pelanggaran Pasal 113 Undang-Undang Nomor 28 Tahun 2014 tentang Hak Cipta

1. Setiap Orang yang dengan tanpa hak melakukan pelanggaran hak ekonomi sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf i untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 1 (satu) tahun dan/atau pidana denda paling banyak Rp100.000.000 (seratus juta rupiah).

2. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf c, huruf d, huruf f, dan/atau huruf h untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 3 (tiga) tahun dan/atau pidana denda paling banyak Rp500.000.000,00 (lima ratus juta rupiah).

3. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf a, huruf b, huruf e, dan/atau huruf g untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 4 (empat) tahun dan/atau pidana denda paling banyak Rp1.000.000.000,00 (satu miliar rupiah).

4. Setiap Orang yang memenuhi unsur sebagaimana dimaksud pada ayat (3) yang dilakukan dalam bentuk pembajakan, dipidana dengan pidana penjara paling lama 10 (sepuluh) tahun dan/atau pidana denda paling banyak Rp4.000.000.000,00 (empat miliar rupiah).

 

Inteligensi Bisnis SQL Server 2014

Ario Suryo Kusumo

PENERBIT PT ELEX MEDIA KOMPUTINDO

Inteligensi Bisnis SQL Server 2014

Ario Suryo Kusumo

©2017, PT. Elex Media Komputindo, Jakarta Hak cipta dilindungi undang-undang Diterbitkan pertama kali oleh Penerbit PT Elex Media Komputindo Kelompok Gramedia, Anggota IKAPI, Jakarta 2017

okti@elexmedia.id

717051053

ISBN: 978-602-04-3144-4

Dilarang keras menerjemahkan, memfotokopi, atau memperbanyak sebagian atau seluruh isi buku tanpa izin tertulis dari penerbit.

Dicetak oleh Percetakan PT. Gramedia, Jakarta Isi di luar tanggung jawab percetakan

v

KATA PENGANTAR

Banyak perusahaan yang sudah menerapkan Inteligensi Bisnis, yaitu

menggunakan informasi yang telah tersedia di perusahaannya untuk

membantu pengambil keputusan membuat keputusan yang lebih baik,

tepat, dan cepat. Salah satu penyedia teknologi Inteligensi Bisnis adalah

Microsoft SQL Server 2014.

Buku ini akan membahas Inteligensi Bisnis SQL Server 2014 dari

Integration Services, Reporting Services, Analysis Services Model

Multidimensional dan Tabular, sampai Data Mining.

Terima kasih untuk ibu Oktivia Handayani dan bapak V. Sugeng Hardojo

dari PT. Elex Media Komputindo.

Semoga buku ini dapat memberi pencerahan.

Jakarta, Maret 2017

Penulis

vii

PENDAHULUAN

Buku Inteligensi Bisnis SQL Server 2014 merupakan buku kedua dari tiga

buku yang penulis buat. Buku itu:

1. Administrasi SQL Server 2014.

2. Inteligensi Bisnis SQL Server 2014.

3. Pemrograman SQL Server 2014.

Persyaratan Software

Untuk menggunakan buku ini Anda harus sudah menginstal software

berikut:

• Sistem operasi Microsoft Windows Server 2008 R2 atau Windows

Server 2012 R2 atau Windows 7 atau Windows 8 atau Windows

10.

• Salah satu Aplikasi Microsoft Office 2007 atau Office 2010 atau

Office 2013 atau Office 2016.

• Program Microsoft SQL Server 2014 edisi Enterprise atau

Developer.

Catatan

Walaupun bisa dijalan di Windows 7, 8, atau 10, tapi idealnya SQL Server

2014 berjalan di Windows server karena di mesin production berjalan di

atas Windows Server. Penulis sendiri menggunakan Windows Server

2008r2.

viii

Konvensi yang Dipakai dalam Buku Ini

• Penulisan pemakaian menu digunakan tanda > sebagai pemisah.

Misalnya untuk klik menu File kemudian klik item menu Save,

ditulis sebagai klik menu File > Save.

• Bagian Catatan berisi informasi tambahan, seperti penjelasan

suatu konsep atau tip tentang suatu topik.

File Distribusi dan Kontak

File distribusi berisi database sampel, script query, dan kode sumber. Juga

Daftar Isi dan sampel ekstrak 1 Bab dapat didownload di

http://arioeska.blogspot.co.id.

Pertanyaan, komentar, kritik, dan saran dapat Anda kirimkan ke e-mail:

ario_eska@yahoo.com.

ix

DafTAR ISI

Kata Pengantar...........................................................................v

Pendahuluan.............................................................................vii

Daftar Isi ................................................................................... ix

BAB 1 Pengantar Inteligensi Bisnis ............................................. 1 Mengenal Inteligensi Bisnis....................................................................1

Konsep Inteligensi Bisnis ...................................................................1 Proses Inteligensi Bisnis.....................................................................2 Microsoft Inteligensi Bisnis.................................................................3

Instalasi Inteligensi Bisnis.......................................................................5 Instal SQL Server 2014 ....................................................................5 Instal SQL Server Data Tools............................................................10 Instal Report Builder .......................................................................12 Instal Sampel Data .........................................................................13

Bab 2 Integration Services........................................................ 17 Arsitektur SSIS....................................................................................18

Packages/Paket.............................................................................19 Control Flow..................................................................................20 Data Flow .....................................................................................23 Variabel .......................................................................................27 Parameter .....................................................................................28 Event Handler................................................................................28

Implementasi Data Warehouse ............................................................28 Schema Star..................................................................................28 Schema Snowflake.........................................................................31

Import dan Export Wizard...................................................................33 Mengimpor File Teks ......................................................................33 Mengekspor Tabel ke File Excel.......................................................36 Membuat Diagram Internet Sales .....................................................38

x

Bab 3 ETL dengan Integration Services ..................................... 41 Ekstrak Data ......................................................................................41 Control Flow......................................................................................49 Sales Union All ..................................................................................54 Conditional Split ................................................................................57 Load Dimensi .....................................................................................61 Load Fakta ........................................................................................67

Bab 4 Reporting Services dengan Report Builder ...................... 75 Pengantar Reporting Services ..............................................................75

Komponen Reporting Services .........................................................76 Siklus Hidup Laporan .....................................................................77

Menggunakan Report Builder ..............................................................80 Membuat Shared Data Source.........................................................80 Table dan Matrix Wizard ...............................................................81 Mengubah Tampilan dan Menyimpan Report....................................87 Menampilkan Report di Server ........................................................89 Menambah Chart ..........................................................................89

Menggunakan Gauge.........................................................................93 Modifikasi Report untuk Membuat Tablix ..........................................99

Menggunakan Query, Parameter, dan Filter ke Data Tablix ................. 102

Bab 5 Reporting Services dengan Data Tools .......................... 107 Laporan AWC ................................................................................ 107

Membuat Projek dan Data Sumber................................................ 107 Report Wizard ........................................................................... 109 Menguji dan Mempercantik Laporan............................................. 113 Menjalankan Report Server, Deploy dan Delivery Laporan............... 114 Laporan Penjualan dengan Parameter ........................................... 116

Laporan Indikator ............................................................................ 120 Membuat Projek dan Data Sumber................................................ 120

Report Wizard................................................................................ 120 Laporan Peta .................................................................................. 124

Membuat Projek dan Data Sumber................................................ 124 Menggunakan Expression ................................................................ 129

Pengantar Expression .................................................................. 129 Pembuatan Laporan .................................................................... 130 Laporan Customers ..................................................................... 130

xi

Laporan EmployeeAge ................................................................ 132 Laporan EmployeeSales............................................................... 133 Laporan CekNull......................................................................... 134 Laporan TopCustomers1 .............................................................. 134 Laporan TopCustomers2 .............................................................. 135

Tugas Administratif .......................................................................... 136 Mengelola Report di Report Manager ........................................... 136 Konfigurasi Sekuriti ..................................................................... 138

Bab 6 Analysis Services Model Multidimensional.................... 143 Pengantar Analysis Services ............................................................. 143

Definisi Analysis Services ............................................................. 143 Arsitektur Analysis Services .......................................................... 144 Dasar-Dasar Analysis Services...................................................... 145

Model Multidimensional Internet Sales ............................................... 145 Membuat Projek Business Intelligence ............................................ 145 Menggunakan Data Sources ........................................................ 146 Menggunakan Data Source View ................................................. 147 Desain Cube .............................................................................. 149 Konfigurasi Dimensi .................................................................... 151 Membuat Hierarki....................................................................... 153 Browsing Cubes.......................................................................... 155 Ekspor ke Pivot Table Excel .......................................................... 158

MDX.............................................................................................. 161 Pengantar MDX Dan GPM ........................................................... 161 Membuat Projek Business Intelligence ............................................ 161 Menggunakan Data Sources ........................................................ 162 Menggunakan Data Source View ................................................. 162 Desain Cube .............................................................................. 162 Konfigurasi Dimensi .................................................................... 163 Browsing Cubes.......................................................................... 163 Calculations ............................................................................... 164 Menghitung Gross Profit Margin ................................................... 167 Ekspor ke Pivot Table Excel .......................................................... 167

KPI Internet Revenue ........................................................................ 169 Pengantar KPI............................................................................. 169 Membuka Solution BI................................................................... 170 Mendefinisikan Internet Revenue ................................................... 171

xii

Browsing Cube Menggunakan KPI Internet Revenue ........................ 174

Bab 7 Analysis Services Model Tabular .................................. 175 Pengantar Model Tabular................................................................. 175

Mengenal Model Tabular ............................................................ 175 Instalasi Model Tabular................................................................ 176

Model Tabular Internet Sales ............................................................ 179 Membuat Projek dan Menambah Data .......................................... 179 Mengubah Nama Kolom dan Mark As Data Table ......................... 183 Membuat Relationships................................................................ 186 Membuat Kolom Dihitung............................................................. 189 Membuat Measures..................................................................... 190 Membuat Perspectives ................................................................. 192 Membuat Hierarki....................................................................... 194 Membuat Partisi.......................................................................... 196 Analisa di Excel.......................................................................... 199

Bab 8 Data Mining ................................................................. 201 Pengantar Data Mining.................................................................... 201

Masalah Bisnis untuk Data Mining ................................................ 202 Tugas Data Mining ..................................................................... 202 Siklus Hidup Data Mining ............................................................ 206 Algoritma Data Mining ................................................................ 209

Data Mining Adventure Works ......................................................... 213 Membuat Projek Analysis Services ................................................ 214 Membuat Data Source................................................................. 214 Membuat Data Source View......................................................... 215 Membuat Mining Structure ........................................................... 216 Menentukan Tipe Data dan Tipe Isi ............................................... 218 Menentukan Struktur Set Pengujian Data........................................ 219 Menambah dan Memproses Model .............................................. 220

Eksplorasi Model Target Mailing....................................................... 223 Explorasi Model Decision Tree ..................................................... 223 Explorasi Model Clustering .......................................................... 225 Explorasi Model Naive Bayes ...................................................... 226

Bab 9 Data Mining Lanjutan ................................................... 229 Forecasting..................................................................................... 230

Membuat Projek Analysis Services dan Data Source ....................... 230

xiii

Membuat Data Source View......................................................... 230 Membuat Struktur dan Model Forecasting ...................................... 231 Mengubah Parameter Algoritma dan Memproses Model Forecasting 233 Eksplorasi Model Forecasting ....................................................... 234

Market Basket Analysis .................................................................... 235 Menambahkan Data Souce View dan Membuat Relasi Antar Tabel .. 236 Membuat Struktur dan Model Market Basket .................................. 237 Memodifikasi dan Memproses Model Market Basket....................... 239 Eksplorasi Model Market Basket ................................................... 240

Sequence Clustering ........................................................................ 241 Membuat Struktur dan Model Sequence Clustering ......................... 241 Memproses Model Sequence Clustering ........................................ 243

Analisis Faktor ................................................................................ 245 Membuat Data Source View......................................................... 245

Daftar Pustaka....................................................................... 253

Tentang Penulis ...................................................................... 255

1

1 PENGANTAR INTELIGENSI BISNIS

Mengenal Inteligensi Bisnis

Konsep Inteligensi Bisnis

Business Intelligence/Inteligensi Bisnis merupakan istilah baru, tetapi

sebenarnya bukan konsep baru. Konsepnya adalah menggunakan

informasi yang telah tersedia di perusahaan Anda untuk membantu

pembuat keputusan membuat keputusan yang lebih baik, tepat, dan

cepat. Di masa lalu, sekitar tahun 1980-an namanya adalah sistem

informasi eksekutif (EIS/Executive Information System) kemudian

berganti nama menjadi sistem pendukung keputusan (DCS/Decision

Support System).

Inteligensi bisnis adalah kemampuan untuk mengumpulkan data dari

berbagai sumber, memproses, dan menampilkannya dalam bentuk yang

dapat dengan mudah dianalisis oleh manajer pada suatu organisasi

sehingga dari hasil analisis tadi dapat dihasilkan keputusan yang lebih

baik untuk organisasi tersebut. Tentunya dengan pengambilan keputusan

yang baik akan membantu bisnis berkembang dengan pesat dan menjaga

bisnis tetap berada pada performa terbaik.

2

Proses Inteligensi Bisnis

Gambar 1.1 Proses Inteligensi Bisnis

Sistem bisnis menangkap data dari operasi yang berkelanjutan, baik data

yang dimasukkan oleh manusia atau dihasilkan oleh proses otomatis.

Record yang disimpan dari transaksi bisnis sehari-hari ini disebut sebagai

OLTP/Online Transaction Processing.

Kadang-kadang orang membutuhkan database sebagai sumber untuk

analisa data. Data OLTP memiliki keterbatasan jika digunakan sebagai

data analisa karena data untuk analisa memerlukan summary/rekapitulasi

data transaksi yang terperinci dan waktu untuk menampilkan hasil query

secara cepat. Juga mempersiapan data menjadi lebih sulit ketika Anda

harus membuat analisa data dari waktu ke waktu, seperti pertumbuhan

tahun ke tahun, misalnya selama 10 tahun. Atau untuk menemukan pola,

seperti karakteristik dari orang yang membeli produk tertentu.

Kendala lainnya, biasanya sebuah organisasi menyimpan datanya dalam

berbagai bentuk. Sebagian besar data disimpan di database, tapi ada juga

yang disimpan dalam file lain, seperti excel atau teks karena struktur

database tidak dapat mengakomodasi data-data dalam file tadi. Ditambah

lagi, database yang digunakan bermacam-macam. Bisa jadi dalam format

database lain seperti DB2, SAP, SQL Server, dan Oracle. Tentunya

keberagaman sumber data seperti ini akan menyulitkan orang-orang

bisnis untuk melakukan analisis data secara cepat dan akurat.

Pada pembuatan solusi Inteligensi Bisnis, data-data tadi akan diambil,

dibersihkan, dan distandardisasi, lalu disimpan ke dalam sebuah database

3

yang disebut data warehouse. Proses pengambilan, pembersihan, dan

penyimpanan tadi dikenal dengan proses ETL/Extract, Transform, dan

Load.

Setelah datanya disimpan di data warehouse, kemudian data tadi akan

diproses ke dalam database multi dimensional dan tabular yang disebut

database OLAP/Online Analytical Processing. Di dalam OLAP terdapat

cube yang menyimpan summary fact/rekapitulasi fakta dan

dimension/dimensi yang dapat di-slice and dice (iris dan sajikan) untuk

keperluan analisis. OLAP inilah yang nantinya dapat diakses oleh orang-

orang bisnis menggunakan aplikasi Inteligensi Bisnis seperti Excel,

Reporting Services, atau Performance Point.

Keseluruhan proses mulai dari ETL hingga ke OLAP adalah proses yang

biasanya dilakukan setiap hari sekali secara otomatis, sering dikerjakan

pada tengah malam dan selesai sebelum jam masuk kerja sehingga pada

saat orang-orang bisnis tiba di kantor, mereka sudah disuguhi laporan

analisis dari data pada hari sebelumnya. Sebagai catatan, biasanya data

pada database OLAP tidak bersifat realtime, tapi h-1. Hal ini

dimaksudkan agar prosesnya tidak menggangu kinerja database OLTP

yang berjalan. Selain itu juga karena untuk keperluan analisis, data yang

dianalisis adalah data yang telah selesai ditransaksikan dan bukan data

yang sedang ditransaksikan.

Microsoft Inteligensi Bisnis

Gambar 1.2 Microsoft Inteligensi Bisnis

4

Pada subsubbab sebelumnya sudah dibahas konsep dan proses Inteligensi

Bisnis secara umum, pada subsubbab ini dibahas Inteligensi Bisnis dari

sisi teknologi Microsoft.

SQL Server Database Engine adalah layanan inti untuk menyimpan,

mengolah, dan mengamankan data. Menggunakan Database Engine,

Anda dapat membuat database relasional untuk proses transaksi online,

yaitu OLTP atau data pengolahan analisis, yaitu OLAP. Termasuk

membuat tabel untuk menyimpan data dan objek database lainnya,

seperti index, view, dan stored procedure.

Untuk mengubah database OLTP menjadi database OLAP digunakan

SSIS/SQL Server Integration Services. SSIS adalah platform untuk

integrasi data dan aplikasi workflow. SSIS merupakan kakas data

warehouse yang cepat dan fleksibel untuk ETL data.

ETL adalah Extract, yaitu proses untuk mengumpulkan data dari berbagai

sumber, Transform, memindahkan dan membersihkannya, kemudian

Load untuk menyimpannya ke dalam sistem yang lain.

Kakas ini juga dapat digunakan untuk mengotomatisasi pemeliharaan

database SQL Server dan update data cube multidimensi.

Kita menggunakan SSRS/SQL Server Reporting Services untuk

mendesain, mengembangkan, tes, dan deploy/meyebarkan laporan.

Menggunakan Data Tools dapat dibuat komponen RDL/Report Definition

Language dengan icon grafis. Dengan cara ini daripada menulis kode,

pengguna dapat melakukan drag dan drop icon grafis ke dalam format

laporan SSRS.

Pengguna dapat berinteraksi secara langsung dengan web services report

server atau menggunakan Report Manager, aplikasi berbasis web dengan

interface web services report Server. Dengan Report Manager, pengguna

dapat melihat, subscribe/berlangganan, dan mengelola laporan, serta

mengelola dan memelihara sumber data dan pengaturan keamanan.

Report Manager juga dapat memberikan laporan SQL melalui e-mail atau

berbagi file.

SSRS dapat menerima data dari OLTP, OLAP, dan cube SQL Server

Analysis Services.

5

SSAS/SQL Server Analysis Services adalah kakas untuk melakukan OLAP

dan data mining. OLAP melalui data Multidimensional dan tabular.

OLAP adalah kemampuan untuk menangani query yang kompleks

melalui akses data secara multidimensi.

Data mining adalah menganalisa data untuk menemukan pola-pola yang

tersembunyi.

Instalasi Inteligensi Bisnis

Kabar gembira untuk Anda yang sedang belajar SQL Server 2014 karena

edisi Developer sekarang dapat diperoleh secara gratis. Lihat artikel

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/micr

osoft-sql-server-developer-edition-is-now-free/.

Untuk latihan, diperlukan SQL Server 2014 edisi Developer atau

Enterprise karena kalau edisi lain fiturnya ada yang tidak lengkap.

Instal SQL Server 2014

1. Jalankan SQL Server 2014 setup.exe dari media instalasi, akan

dipicu Autostart yang menampilkan layar SQL Server Installation

Center.

2. Di layar akan ditampilkan semua opsi untuk menginstal dan

mengelola SQL Server 2014. Karena kita akan melakukan instalasi

standar, di menu sebelah kiri layar klik Installation. Layar terlihat

seperti Gambar 1.3.

 Gambar 1.3 SQL Server Installation Center Layar Installation