Membangun OLAP Cube dengan SQL Server Data Tools

 

OLAP Cube adalah sebuan inti penting dalam OLAP dan Datawarehousing, karena setelah kita mengumpulkan data dan mengolahnya maka kita perlu sebuah cara untuk melihat hasil dari pengolahan data tersebut untuk pengambilan keputusan dengan cepat. Termasuk bila kita ingin merubah variabel dari data tersebut seperti kasus berikut.

1. User ingin melihat penjualan barang per tahun
2. Setelah melihat penjualan per tahun user ingin lebih detil dengan melihat data per bulan
3. Setelah melihat penjualan per bulan, user ingin melihat lebih detil total produksi dan jumlah order bedasarkan tahun dan negara tempat pejualan terjadi

Tentu saja kalau kita membuat laporan ini  satu per satu ke dalam excel akan menjadi sangat lama. Sehingga kita membuat sebuah OLAP Cube di mana setiap variabel bisa diganti dengan segera. Kemudian OLAP Cube ini bisa dilempar menjadi sebuah Excel yang mana bisa kita buat agar laporan tersebut disimpan dan dibagikan ke orang lain.

Pertama kita akan melakukan pengambilan data menggunakan SQL Server Data Tools.

Buat New Project dan kemudian pilih Analysis Services Multidimensional and Data Mining.

 Saat anda sudah membuat sebuah project baru, langsung arahkan perhatian pada bagian kanan atau solution explorer

 

Lakukan klik kanan pada Data Sources, maka anda akan dimasukan pada Data Source Wizard. Silahkan buat koneksi baru di sana

Bila anda memiliki koneksi ke datawarehouse silahkan gunakan. Tetapi bila belum atau mau menghindari error yang bisa terjadi silahkan buat koneksi baru dengan menekan tombol new.

Koneksi yang dibangun akan menggunakan default provider atai SQL Server Native Client 11.0

Bila anda memiliki yang lebih baru silahkan gunakan, namun pastikan services nya jalan terlebih dahulu karena defaultnya bukan automatic namun manual dan bisa tidak jalan.

Kemudian silahkan cari nama server tempat anda menaruh data. Keudian pada log on to the server gunakan SQL Server Authentication. Bila anda lupa, silahkan membuat user atau melakukan reset password melalui SQL Server Management Studio. Karena bila menggunakan Windows Authentication akan sering menemui kegagalan dalam masaah login(pengalaman pribadi). Setelah memasukan username dan password silahkan memilih datawarehouse yang ingin anda gunakan. Dalam contoh ini saya akan menggunakan Adventure Works Datawarehouse  2012. 

 

Lakukan Test Connection dan bila berhasil tekan Ok, kemudian anda akan dikembalikan ke halaman data source wizard, tekan finish. Kemudian, perhatikan pada solution explorer maka data source anda akan muncul. Klik kanan di sana lalu pilih open, maka anda akan masuk dalam Data Source Designer, pilih Impersonation Information kemudian masukan username dan password anda kemudian tekan ok

 Anda bisa memilih inherit tapi entah kenapa di saya sering terjadi error. Menurut pendapat pribadi, semua tergantung instalasi awal SQL Server Data Tools yang anda miliki.

 Langkah berikutnya adalah klik kanan pada Data Source Views dan ikuti Data Source View Wizard yang muncul. Pilih Data Source yang telah dibuat, klik next. Halaman berikutnya akan mengarahkan anda pada pemilihan table dan view yang ada dalam datawarehouse. Untuk Contoh ini saya akan menggunakan FactInternetSales sebagai objek yang akan saya buat OLAP Cube nya. Kemudian anda bisa memilih "Add Related Tables" untuk memasukan semua table yang memiliki relasi dengan FactInternetSales. Dalam kasus ini karena saya tidak mengerti isi data dari Adventure Works maka kita akan menggunakan DimProduct, DimCurrency dan DimDate. Karena mereka berelasi dan tujuan saya adalah ingin melihat penjualan dari internet bedasarkan dimensi produk, waktu dan mata uang.

 Setelah selesai silahkan pilih next dan kemudian tekan Finish. Maka layar anda harusnya sudah ada diagram dari tabel-tabel yang telah kita pilih.

Setalh tampil seperti gambar di atas, Silahkan perhatikan kembali di sebelah kanan pada bagian cubes. Silahkan klik kanan dan buat new cube. Pada creation method gunakan pilihan "Use Existing tables"

Kemudian pilih next dan pilih semua table yang telah kita masukan tadi, kemudian pilih next, maka akan masuk ke dalam pilihan measures atau nilai yang ingin kita ukur. Bila anda memiliki target yang ingin anda ukur dari datawarehouse anda silahkan pilih sesuai kebutuhan tetapi bila tidak silahkan pilih semua kemudian tekan next dan cube wizard akan membuatkan dimensi otomatis untuk anda, tekan next dan kemudian tekan finish. Meski begitu Dimensi yang dibangun ini tidak sempurna. Karena hanya key saja yang dimasukan. Karena kita tidak bisa membaca data hanya bedasarkan key nya saja maka ada baiknya kita berikan dimensi yang detil. Silahkan klik pada bagian kanan di dimensi.

Kita mulai dari Dim Date terlebih dahulu, lakukan double klik dan anda akan dipindahkan pada halaman Dim Date. Maka akan kita lihat bahwa pada bagian attributes hanya ada date key. Cara menambakan attributes cukup mudah kita tinggal klik drag kolom dari data source view menuju attirbutes.

Yang saya masukan dari DimDate data source view pada attributes adalah 

• CalendarYear
• English Day Name
• English Month Name
• Calendar Quater
  
• Calender Semester
  

Kalau sudah tekan Ctrl+S untuk menyimpan Dimensi Date. Kemudian lakukan hal yang sama pada Dimensi product dan Currency. 

Untuk Dimensi Date ini kita akan masukan hirarki, tujuan hiraki ini untuk mempermudah dalam melihat data nantinya. Kemudian memang secara fungsi data dalam dimDate ini ada hirarkinya.

untuk menambahkan hirarki maka klik drag dari kolom kiri ke tengah.

Kapan Hierarki dalam OLAP Cube bisa digunakan?

Hierarki digunakan apabila antar kolom dalam 1 tabel memiliki relasi 1-to-Many. Data ini biasanya mengindikasikan ada pola dalam data tersebut. Pola seperti apa? pola yang dimaksud adalah pola user dalam menelusuri data. Contoh, seorang user mungkin akan melihat data dari tahun terlebih dahulu, kemudian akan melihat data bedasarkan 1/4 tahun, kemudian baru dilihat data per bulan. Contoh lainnya mungkin dari negara-->propinsi-->kota atau produk kategoti-->sub kategori-->produk.

Hierarki akan sangat berguna apabila pola atau hierarki tersebut semakin banyak level dan akan sangat percuma membangun hierarki dalam 1 level saja.

Yang saya masukan dari DimProduct data source view pada attributes adalah

• EnglishProductName
  

Yang saya masukan dari DimCurrency data source view pada attributes adalah

• CurrencyName

Yang saya masukan dari FactInternetSales data source view pada attributes adalah

• OrderQuantity
• SalesAmount
  

Bila sudah selesai silahkan Klik kanan pada Cubes 

Pilih Process, pilih yes, kemudian pilih yes lagi, maka anda akan dimasukan dalam process cube, pastikan process option full dan kecilkan sedikit windows process cube ini agar tombol run anda kelihatan. Kemudian tekan run. Maka Data akan diambil dari datawarehouse dan dimasukan dalam project kita. Setelah selesai (processing cibe adventure works menjadi complete) tekan close lalu tekan close lagi pada process cube.

Pada bagian kanan di solution explorer Double klik pada Adventure Works DW2012 cube, lalu pilih browser. Pilihan ada pada bagian paling kanan.

 Bila halaman kosong akan ada perintah untuk deploy dan pilihan kedua untuk refresh(maaf tidak ada screenshotnya) karna kita sudah deploy maka klik pilihan kedua. Maka kita sudah dapat melakukan penentuan variabel dalam OLAP Cube. Silahkan klik drag dari measure group ke tengah. 

Untuk melihat data dari cube, yang harus dilakukan adalah melakukan klik drag dari measures dan klik drag dari Dimension, saya akan menggunakan DimProduct. Untuk Dim Product saya akan ambil English Product Name dan dari  measures saya akan menggunakan order quantity dan unit price dari fact internet sales

 

Silahkan mengatur sendiri hasil yang anda inginkan, sesuaikan dengan tujuan dari fact table dan dimensi yang anda miliki.  

Mengolah hasil OLAP Cube di SQL Server Data Tools di Microsoft Excel

Namun, tidak bisa dipungkiri bahwa kontrol OLAP Cube dalam SQL Server Data Tools sangat membingungkan. Saran saya dilempar ke excel dan diolah menggunaka Pivot table.

Setelah anda klik anda akan dilempar ke excel dan saat anda dihadapkan pada Microsoft Excel Security Notice Pilih enable.

Berikutnya anda akan ada di excel dengan pivot tables yang sudah terisi. Tugas anda adalah menentukan mana yang akan menjadi di kolom(Column) dan baris(Rows) serta menentukan nilai yang akan diamati(values).

Kemudian ada beberapa syarat khusus untuk memasukan pada column, rows dan value di pivot table fields

Simbol file hanya dapat masuk ke dalam column atau rows dan tidak masuk ke dalam value.

 

   sedangkan untuk value hanya bisa dimasukan dari simbol sigma seperti gambar ini.

Masukan pada columns

• Calendar Year
• English Month Name

Kemudian pada Rows Masukan

• English Product Name
  

Pada values masukan 

• Order Quantity
  

Maka Hasil akhir anda akan menjadi seperti berikut

Kemudian agar file ini bisa dikirimkan ke orang lain gunakan OLAP Tools dan pilih Convert to Formulas.

 

Maka file tersebut sudah tidak terhubung dengan SQL Server Data Tools dan hasil OLAP Cube tersebut dapat dikirimkan pada orang lain.

Tools Drill up dan Drill Down.

Drill up dan drill down digunakan untuk melihat data dalam lebih detil. Tetapi sebelum bisa melakukan drill up dan drill down. Maka kita perlu melakukan setting hierarchy dalam SQL Server data tools. Dalam blog ini saya sudah membuat hierarchy bedasarkan waktu. Dengan urutan Tahun, Quater(1 tahun dibagi menjadi 4), Month. Maka saat saya melihat data dengan menggunakan excel dan menaruh hierarchy date dalam rows maka fitur drill up dan drill down di excel untuk olap dalam pivot table akan menyala.

Ketika ditekan drill down, maka data hanya akan difokuskan pada hiearki tersebut. Hiarki dimulai dari tahun, quater dan bulan maka saat drill down kita akan difokuskan pada quater nya. Hasil akhr akan seperti gambar berikut. Pindah kursor dahulu ke tahun 2013, lalu tekan drill down. Maka akan muncul data yang hanya untuk tahun 2013 saja. Hal ini berguna agar kita bisa memfokuskan diri untuk menganalisa data dalam 1 tahun tersebut. Selain itu akan muncul angka 1 hingga 4. Ini terjadi karena dalam data quarter terdiri dari 1 hingga 4 dan mereka akan membagi bulan dalam 1 tahun menjadi 4. dalam 1 akan ada januari, febuari, maret