88
DAFTAR PUSTAKA
Dennis, Alan, Barbara Haley Wixom, and Roberta M. Roth, System Analysis and Design 5th Edition, John Wiley & Son, River Street, Hoboken, NJ, 2012. Turban, Efraim, Ramesh Sharda, and Dursun Delen, Decision Support and Business Intelligence Systems 9th Edition, Pearson Education, Inc, Prentice Hall, New Jersey, 2011. Pusadan, Muhammad Yazdi, Rancang Bangun Data Warehouse, Graha Ilmu, Yogyakarta, 2013. Rasmussen, Nils, Claire Y. Chen, Manish Bansal, Business Dashboard, John Wiley & Sons, Inc, 2009. Anhar, Panduan Menguasai PHP & MySQL Secara Otodidak, Jakarta : Mediakita, 2010. Baboo, S. Santhosh, August 2013, “Designing Real Time Business Intelligence Reporting for Better Business Insights”. International Journal of Computer Applications, Volume 76 – No 14, www.ijcaonline.org/archives/volume76/number14/13314-0762 Debbarma, Nikhil, Gautam Nath, Hillol Das, October 2013, “Analysis of Data Quality and Performance Issues in Data Warehousing and Business Intelligence”. International Journal of Computer Applications, Volume 79 – No 15, www.ijcaonline.org/archives/volume79/number15/13818-1862
Lampiran 1
Filter dan SQL Command Proses ETL
Filter data dan SQL Query Command tiap proses ETL berbeda-beda. Berikut adalah masing-masing Filter data dan SQL Query Command :
1) Data Branch “select idBranch, branchName, description, address2 from m_Branch where idBranch = '004' or idBranch = '005' or idBranch = '016'”
Branch yang diambil secara acak untuk analisa data ada 3 cabang penjualan produk sepatu dan di filter berdasarkan idBranch. 2) Data Transaksi “select top 1000 dt_inv, no_inv, no_branch, no_model, no_color, no_size, buy_qty, price from p_invoiceline where dt_inv >= '2013-1-1' and dt_inv <= '2014-2-28' and no_branch = '004' and no_model != 'PAPERB' and no_model != 'SCHOLL' and no_model != 'MAJALAH' and no_model != 'VOUCHER' and left(no_model, 3) = 'CK1' order by NEWID()”
Data transaksi diambil secara acak dan berjumlah 1000 record per branch sehingga total data transaksi adalah 3000 record untuk 3 cabang. Data transaksi diambil dari Januari 2013 sampai Februari 2014. 3) Data Model “select distinct(no_model) from Transaksi”
Data model merupakan master dari produk yang diambil dari data transaksi secara acak sehingga dihasilkan data model sebanyak 2257 record. 4) Data Color “select distinct(no_color) from Transaksi” Data color merupakan master dari produk yang diambil dari data transaksi secara acak sehingga dihasilkan data model sebanyak 2257 record. 5) Data Size “select distinct(no_size) from Transaksi” Data color merupakan master dari produk yang diambil dari data transaksi secara acak sehingga dihasilkan data model sebanyak 2257 6) Data Pengiriman Barang Query 1 : “select top 500 p_loaderdelivery.dt_proof, p_loaderdelivery.no_proof, p_loaderdelivery.no_branch, p_loaderdelivery.type, p_loaderdelivery.outlet, p_loaderdeliveryline.no_model, p_loaderdeliveryline.no_color, p_loaderdeliveryline.no_size, p_loaderdeliveryline.qty from p_loaderdelivery inner join p_loaderdeliveryline on p_loaderdelivery.dt_proof = p_loaderdeliveryline.dt_proof and p_loaderdelivery.no_proof = p_loaderdeliveryline.no_proof and p_loaderdelivery.no_branch = p_loaderdeliveryline.no_branch and p_loaderdelivery.no_repeatorder = p_loaderdeliveryline.no_repeatorder where p_loaderdelivery.company = 'CK' and p_loaderdelivery.no_branch = '001' and p_loaderdelivery.dt_proof >= '2013-1-1' and p_loaderdelivery.dt_proof <= '2014-2-28' and p_loaderdelivery.outlet = '004' and p_loaderdelivery.type = 'RO' and no_model in (select no_model from dw_kurnia.dbo.Model) and no_color in (select no_color from dw_kurnia.dbo.Model) and no_size in (select no_size from dw_kurnia.dbo.Model) order by NEWID()”
Data pengiriman barang ada dua, yang pertama adalah data pengiriman barang dari warehouse pusat ke cabang. Data diambil secara acak dan diambil 500 record sehingga total data pengiriman barang dari pusat ke cabang adalah 1500 record. Query 2 : “select top 500 p_outletdelivery.dt_proof, p_outletdelivery.no_proof, p_outletdelivery.no_branch, p_outletdelivery.type, p_outletdelivery.outlet, p_outletdeliveryline.no_model, p_outletdeliveryline.no_color, p_outletdeliveryline.no_size, p_outletdeliveryline.qty from p_outletdelivery inner join p_outletdeliveryline on p_outletdelivery.dt_proof = p_outletdeliveryline.dt_proof and p_outletdelivery.no_proof = p_outletdeliveryline.no_proof and p_outletdelivery.no_branch = p_outletdeliveryline.no_branch where p_outletdelivery.company = 'CK' and p_outletdelivery.no_branch = '004' and p_outletdelivery.dt_proof >= '2013-1-1' and p_outletdelivery.dt_proof <= '2014-2-28' and p_outletdelivery.type != 'OC' and no_model in (select no_model from dw_kurnia.dbo.Model) and no_color in (select no_color from dw_kurnia.dbo.Model) and no_size in (select no_size from dw_kurnia.dbo.Model) order by NEWID()”
Data pengiriman barang yang kedua adalah data pengiriman barang dari outlet. Baik itu dari outlet ke pusat seperti barang retur atau pun pengiriman barang dari outlet ke outlet seperti konsolidasi. Data diambil secara acak dan diambil 500 record, sehingga jumlah total adalah 1500 record. 7) Data Penerimaan Barang Query 1 “select top 500 p_unloaderreceive.dt_proof, p_unloaderreceive.no_proof, p_unloaderreceive.no_branch, p_unloaderreceive.outlet, p_unloaderreceive.type, p_unloaderreceiveline.no_model, p_unloaderreceiveline.no_color, p_unloaderreceiveline.no_size, p_unloaderreceiveline.qty from p_unloaderreceive inner join p_unloaderreceiveline on p_unloaderreceive.dt_proof =
p_unloaderreceiveline.dt_proof and p_unloaderreceive.no_proof = p_unloaderreceiveline.no_proof and p_unloaderreceive.no_branch = p_unloaderreceiveline.no_branch where p_unloaderreceive.dt_proof >= '20131-1' and p_unloaderreceive.dt_proof <= '2014-2-28' and p_unloaderreceive.outlet = '004' and p_unloaderreceiveline.no_model in (select no_model from dw_kurnia.dbo.Model) and p_unloaderreceiveline.no_color in (select no_color from dw_kurnia.dbo.Model) and p_unloaderreceiveline.no_size in (select no_size from dw_kurnia.dbo.Model) order by NEWID()”
Data penerimaan barang ada dua, yang pertama adalah data penerimaan barang dari cabang ke pusat. Data diambil secara acak dan diambil 500 record sehingga total data penerimaan barang dari cabang ke pusat adalah 1500 record. Query 2 : “select top 500 p_outletreceive.dt_proof, p_outletreceive.no_proof, p_outletreceive.no_branch, p_outletreceive.outlet, p_outletreceive.type, p_outletreceiveline.no_model, p_outletreceiveline.no_color, p_outletreceiveline.no_size, p_outletreceiveline.qty from p_outletreceive inner join p_outletreceiveline on p_outletreceive.dt_proof = p_outletreceiveline.dt_proof and p_outletreceive.no_proof = p_outletreceiveline.no_proof and p_outletreceive.no_branch = p_outletreceiveline.no_branch where p_outletreceive.dt_proof >= '2013-1-1' and p_outletreceive.dt_proof <= '2014-2-28' and p_outletreceive.no_branch = '004' and p_outletreceive.type != 'SM' and p_outletreceive.type != 'DP' and p_outletreceive.type != 'OC' and type != 'UR' and p_outletreceiveline.no_model in (select no_model from dw_kurnia.dbo.Model) and p_outletreceiveline.no_color in (select no_color from dw_kurnia.dbo.Model) and p_outletreceiveline.no_size in (select no_size from dw_kurnia.dbo.Model) order by NEWID()”
Data penerimaan barang yang kedua adalah data penerimaan barang ke outlet. Baik itu dari warehouse pusat ke outlet seperti barang repeat order atau pun penerimaan barang dari outlet ke outlet seperti konsolidasi. Data diambil secara acak dan diambil 500 record, sehingga jumlah total adalah 1500 record.
8) Data In_Out_Stock Query 1 : “select dt_dlv, no_branch, outlet, no_model, no_color, no_size, qty_dlv from Pengiriman_Barang”
Data yang diperlukan untuk tabel in_out_stock ini adalah data gabungan dari tiga tabel yaitu tabel Transaksi, Pengiriman Barang dan Penerimaan Barang. Data digabung dan dikumpulkan ke dalam tabel in_out_stock untuk keperluan analisa dan dihasilkan report yang dibutuhkan. Data yang pertama adalah data pengiriman barang yang ada di tabel Pengiriman Barang akan ditransfer ke dalam tabel in_out_stock. Query 2 : “select dt_rcv, no_branch, outlet, no_model, no_color, no_size, rcv_qty from Penerimaan_Barang”
Data yang kedua adalah data penerimaan barang yang ada di tabel Penerimaan Barang akan di transfer ke dalam tabel in_out_stock. Query 3 : “select dt_inv, no_branch, no_model, no_color, no_size, buy_qty from Transaksi”
Data yang ketiga adalah data transaksi yang ada di tabel Transaksi akan ditransfer ke dalam tabel in_out_stock.
