54
BAB 3 ANALISIS DAN PERANCANGAN APPLICAT ION TUNING PLAN 3.1 Analisis Perusahaan 3.1.1
Sejarah PT.Bank X PT.Bank X berdiri pada tanggal 2 Oktober 1998 sebagai bagian dari
program restrukturisasi perbankan yang dilaksanakan oleh pemerintah Indonesia. Pada bulan Juli 1999, empat bank milik pemerintah yaitu Bank Bumi Daya, Bank Dagang Negara, Bank Ekspor Impor Indonesia dan Bank Pembangunan Indonesia, bergabung menjadi PT.Bank X. Keempat bank tersebut telah turut membentuk riwayat perkembangan perbankan di Indonesia di mana sejarahnya berawal pada lebih dari 140 tahun yang lalu. Kini, PT.Bank X menjadi penerus suatu tradisi layanan jasa perbankan dan keuangan yang telah berpengalaman selama lebih dari 140 tahun. M asing-masin g dari empat bank bergabung telah memainkan peranan yang penting dalam pembangunan ekonomi. Setelah selesainya proses merger, PT.Bank X kemudian memulai proses konsolidasi. Diantaranya menutup 194 kantor cabang yang overlap dan mengurangi jumlah pegawai dari 26.000 menjadi 17.620. Selanjutnya diikuti dengan peluncuran single brand di seluruh jaringan melalui iklan dan promosi. Salah satu pencapaian penting adalah penggantian secara menyeluruh platform teknologi PT.Bank X. PT.Bank X mewarisi sembilan sistem perbankan dari keempat legacy bank. Setelah investasi awal untuk konsolidasi sistem yang
54
55 berbeda tersebut, PT.Bank X mulai melaksanakan program penggantian platform yang berlangsung selama tiga tahun dengan investasi USD 200 juta, di mana program pengganti tersebut difokuskan untuk kegiatan consumer banking. Pada saat ini, infrastruktur teknologi informasi PT.Bank X sudah mampu memfasilitasi straight through processing dan interface yang seragam untuk nasabah. Nasabah corporate PT.Bank X merupakan penggerak utama perekonomian Indonesia. Berdasarkan sektor usaha, nasabah PT.Bank X bergerak di bidan g usaha yang sangat beragam khususnya makanan dan minuman, pertanian, konstruksi, kimia dan tekstil. Persetujuan kredit dan pengawasan dilaksanakan dengan prinsip ‘four eyes,’ dimana persetujuan kredit dipisahkan dari kegiatan pemasaran dan business unit PT.Bank X. Sejak didirikan, PT.Bank X terus bertekad untuk membentuk tim manajemen yang handal dan profesional serta bekerja berdasarkan prinsip good corporate governance, pengawasan dan kepatuhan yang sesuai standar internasional. PT.Bank X disupervisi oleh dewan komisaris yang terdiri dari orang-orang yang menonjol di komunitas keuangan yang ditunjuk oleh pemegan g saham termasuk M enteri Negara BUM N. Tingkatan tertinggi dari manajemen eksekutif adalah Direksi, yang diketuai oleh Direktur Utama. Direksi PT.Bank X terdiri dari para bankir yang berasal dari legacy bank dan juga para bankir profesional dari bank lain. Sebagai bagian dari penerapan GCG, PT.Bank X membentuk Compliance Group, Internal Audit dan Corporate Secretary, dan juga dari waktu ke waktu diperiksa oleh Bank Indonesia dan Badan Pemeriks a Keuangan (BPK), serta diaudit setiap tahun nya oleh auditor independen. Komitmen PT.Bank X terhadap GCG ini telah mendapat pengakuan berupa 55
56 Corporate Governance Award dari majalah Asia Money untuk kategori Bes t Overall for Corporate Governance in Indonesia dan Best for Disclosure and Transparency. Dengan total aktiva yang saat ini telah tumbuh mencapai lebih dari Rp300 triliun, atas kerja keras 21 ribu lebih karyawan yang tersebar di 956 kantor caban g dalam negeri dan 6 cabang luar negeri termasuk perwakilannya, PT.Bank X telah bertekad untuk memberikan pelayanan terbaik dan menyediakan solusi keuangan yang menyeluruh di bidang investment banking, perbankan syariah serta bank assurance bagi nasabah perusahaan swasta maupun milik negara, komersial, usaha kecil dan mikro serta nasabah consumer. Tekad yang diwujudkan dalam kerja keras ini telah diakui melalui pemberian peringkat pertama Banking Service Excellence Award 2007 dari majalah Infobank dan M RI.
56
57
3.1.2
Visi & Misi PT.Bank X Visi
:
Misi :
Bank Terpercaya Pilihan Anda Berorientasi pada pemenuhan kebutuhan pasar
M engembangkan sumber daya profesional
M emberi keuntungan yang maksimal bagi stakeholder
M elaksanakan manajemen terbuka
Peduli terhadap kepentingan masyarakat dan lingkungan
Tagline : Terdepan, Terpercaya, Tumbuh Bersama Anda
3.1.3
Nilai Budaya dan Perilaku Utama Insan PT.Bank X Nilai-nilai budaya yang kuat berakar di seluruh jajaran organisasi akan
menjadi landasan utama dalam memacu kinerja prima dan berkesinambungan. Untuk mewujudkan visi “ Bank Terpercaya Pilihan M asyarakat “ dan misi sebagaimana tersebut di atas merupakan suatu perjalanan panjang yang harus ditempuh dalam suatu koridor dan pedoman yang disepakati bersama dalam PT.Bank X. Nilai-nilai budaya dan perilaku utama insan PT.Bank X merupakan suatu tatanan dan kerangka acuan yang akan menyamakan gerak dan langkah PT.Bank X sehingga seluruh jajaran organisasi secara konsisten akan bertindak sesuai dengan prinsip-prinsip yang telah kita yakini bersama untuk mencapai tujuan yang telah ditetapkan bersama. 57
58 5 Nilai Budaya PT.Bank X Nilai-nilai adalah serangkaian prinsip yang diyakini sebagai panduan moral dalam berprilaku, bertindak dan mengambil keputusan. Nilai-nilai inilah yang akan memandu dalam menentukan baik atau buruk dan benar atau salahnya suatu tindakan. Sebagai panduan moral, nilai-nilai ini telah didefinisikan secara jelas supaya memberikan petunjuk yang nyata dalam kehidupan insan PT.Bank X. Kelima nilai budaya yang akan dijadikan pedoman perilaku seluruh insan PT.Bank X adalah sebagai berikut : 1) KEPERCAYAAN (TRUST)
M embangun keyakinan dan prasangka baik di antara stakeholders dalam hubungan yang tulus dan terbuka berdasarkan kehandalan. 2) INT EGRIT AS (INTEGRITY)
Setiap saat berpikir, berkata dan berperilaku terpuji, menjaga martabat serta menjunjung tinggi kode etik profesi. 3) PROFESIONALISME (PROFESSIONALISM)
Berkomitmen untuk bekerja tuntas dan akurat atas dasar kompetensi terbaik dengan penuh tanggung jawab. 4) FOKUS PADA PELANGGAN (CUSTOMER FOCUS)
Senantiasa menjadikan pelanggan sebagai mitra utama yang saling menguntungkan untuk tumbuh secara berkesinambungan. 5) KESEMPURNAAN (EXELLENCE)
M engembangkan dan melakukan perbaikan di segala bidang untuk mendapatkan nilai tambah dan hasil yang terbaik secara terus menerus. 58
59
10 Perilaku Utama Insan PT.Bank X Agar nilai-nilai budaya yang telah dirumuskan dapat dihayati dan diamalkan oleh seluruh insan PT.Bank X, dan akhirnya menjadi budaya yang menjadi ciri khas, membedakan insan PT.Bank X dari organisasi lain, maka nilai-nilai budaya tersebut harus diterjemahkan ke dalam bentuk perilaku yang nyata. Penerapan nilai-nilai budaya secara konsisten dan terus menerus dalam setiap perilaku dan pengambilan keputusan akan mewujudkan budaya PT.Bank X yang kokoh, yang secara positif akan menghasilkan kinerja prima. Telah ditetapkan 10 (sepuluh) perilaku utama yang dilandasi oleh nilai-nilai budaya PT.Bank X yang akan menjadi panduan bagi seluruh insan PT.Bank X.
Nilai
Pe rilaku Utama
Ke percayaan Integritas Profesionalisme Fokus Pada Pelanggan
Kesempurnaan
Saling menghargai dan bekerjasama
Jujur, tulus dan terbuka
Disiplin dan konsisten
Berpikir, berkata dan bertindak terpuji
Kompeten dan bertanggung jawab
Memberikan solusi dan hasil terbaik
Inovatif, proaktif dan cepat tanggap
Mengutamakan pelayanan dan kepuasan pelanggan
Orientasi pada nilai tambah dan perbaikan terus menerus
Peduli lingkungan
Tabel 0-1 Sepuluh Perilaku Utama Insan PT.Bank X
59
60
Gambar 0-1 Jumlah Aset PT .Bank X per 31 Desember 2007
60
62
3.1.4 3.1.4 Struktur Organisasi PT.Bank X
Gambar 0-2 Struktur Organisasi PT .Bank X
61
64
Struktur Organisasi IT – Bussiness Solusitions & Application Services
Gambar 0-3 Struktur Organisasi IT-BSA Group PT.Bank X
Struktur Organisasi Consumer Loan Group
Gambar 0-4 Struktur Organisasi Consumer Loan Group PT.Bank X
65
3.1.5
Tugas dan Wewenang A. IT – BS A ( IT – Bussiness Solutions & Application Services) IT – BSA merupakan group bagian dari direktorat IT di PT.Bank X yang dipimpin oleh seorang Group Head. IT – BSA menangani sisi development dari aplikasi dan sistem-sistem yang ada. IT – BSA terdiri dari 7 department yaitu : 1. Department IT – IMS IT-IM S department merupakan singkatan dari IT - Information Management System Department. Department ini terdiri dari 3 bagian, yaitu :
DWH & BI Tugas: a. M engidentifikasi
masalah
yang
berkaitan
dengan
permasalahan di bidang Data warehouse dan Bussiness Intelligence System b. M engupayakan
solusi
yang
berkaitan
dengan
permasalahan di bidang Data warehouse dan Bussiness Intelligence System. c. M engembangkan
data
warehouse
dan
mendukung system pengambilan keputusan d. Maintenance data warehouse dan BI system.
BI
dalam
66
Wewenang: a. M enentukan solusi berkaitan dengan masalah data warehouse dan Business Intelligence System
PM S, M IS, EIS Tugas: a. M engidentifikasi
masalah
yang
berkaitan
dengan
permasalahan di bidang Perfomance, Management, Expert Information System b. M engupayakan
solusi
yang
berkaitan
dengan
permasalahan di bidang Perfomance, Management, Expert Information System. c. M engembangkan Information
Perfomance, Systemdalam
Management, mendukung
Expert kegiatan
operasional. d. Maintenance
Perfomance,
Management,
Expert
Information System.
Wewenang: a. M enentukan solusi berkaitan dengan masalah Perfomance, Management, Expert Information System b. M embuat perencanaan
pengembangan Perfomance,
Management, Expert Information System
67
Regularly Reporting Tugas: a. M embuat laporan keuangan untuk dilaporkan ke Bank Indonesia b. M embuat auditing,
laporan-laporan yang dibutuhkan laporan
ke vendor,
laporan
untuk
ke pihak
managerial, dan laporan-laporan lain yang dibutuhkan.
Wewenang: a. M emvalidasi laporan yang dihasilkan b. M engeluarkan laporan keuangan kepada publik
2. Department IT – RMS IT–RMS
department
merupakan
singkatan
dari
IT-Risk
Management System Department. Tugas: a. M engidentifikasi resiko IT yang ada b. M erancang aplikasi untuk membantu risk management c. Maintenance ERM (Enterprise Risk Management) Wewenang: a. M enentukan
solusi
berkaitan
dengan
masalah
Management System b. M erancang solusi terhadap resiko yang teridentifikasi
Risk
68 3. Department IT – ACR IT–ACR department merupakan singkatan dari IT - Asset and Customer Relationship Solution Department. Tugas: a. M engembangkan
CRM
(Customer
Relationship
Management) b. M engelola data-data asset PT. Bank X yang berupa pinjaman nasabah. Wewenang: a. M enentukan solusi berkaitan dengan masalah Asset and Customer Relationship Solution Department
4. Department IT – WOB IT–WOB department merupakan singkatan dari IT – WholeSale & Overseas Branches Solutions Department. Tugas: a. M engimplementasikan sistem-sistem yang akan digunakan pada kantor cabang yang terintegrasi dengan kantor pusat PT.Bank X, terutama kantor cabang di luar negeri, seperti Singapore, Dili (India). b. M enangani masalah-masalah yang berkaitan dengan sistem yang telah diimplementasikan pada kantor cabang di luar negeri.
69 Wewenang: a. M enentukan solusi berkaitan dengan masalah WholeSale & Overseas Branches Solutions Department b. M embuat
perencanaan
pengembangan
WholeSale
&
Overseas Branches Solutions Department
5. Department IT – ECS IT – ECS department merupakan singkatan dari IT – Electronic Channel & Credit Card Solution Department. Tugas: a. M engembangkan aplikasi online transaction, seperti mbanking, e-banking, dan credit card b. Maintenance aplikasi online transaction, seperti m-banking, e-banking, dan credit card Wewenang: a. M enentukan solusi berkaitan dengan masalah Electronic Channel & Credit Card Solution Department b. M embuat perencanaan pengembangan Electronic Channel & Credit Card Solution Department
70
6. Department IT – LTS IT – LTS department merupakan singkatan dari IT – Liability & Transaction Solution Department. Department ini bertugas dengan segala sesuatu yang menjadi tanggung jawab PT.Bank X (Liability of PT.Bank X), misalnya : Tabungan
dan
Corporate
Management
System.
Corporate
Management System memungkinkan suatu perusahaan yang merupakan nasabah PT.Bank X dapat mengakses dan mengatur data-data perbankan perusahaan itu sendiri secara online tanpa harus datang ke PT.Bank X. Wewenang: a. M enentukan solusi berkaitan dengan masalah Liability & Transaction Solution Department 7. Department IT – CCS IT – CCS department merupakan singkatan dari IT – Corporate Center Solution Department. Department ini bertugas untuk mengatasi permasalahan dan mengatur segala aktivitas yang berkaitan dengan Corporate. M isalnya : pada bagian HRD (Human Resource Development). Wewenang: a. M enentukan solusi berkaitan dengan masalah Corporate Center Solution Department
71 B. Consumer Loan Group Consumer Loan merupakan group yang
menangani pinjaman-
pinjaman dari para nasabah PT.Bank X.
Consumer Loan Group terdiri dari 8 department yaitu : 1. Personal Loan Department Personal Loan menangani hal-hal yang berkaitan dengan produk :
Kredit Tanpa Agunan /Jaminan Kredit Tanpa Agunan adalah kredit yang akan diberikan kepada nasabah PT.Bank X tanpa harus meyerahkan agunan/jaminan kepada PT.Bank X. Kredit tanpa agunan tersebut dapat digunakan untuk kepentingan pendidikan, payroll (gaji karyawan), regular (kepentingan lainnya).
Kredit M itrakarya Kredit M itrakarya adalah layanan kredit yang ditujukan bagi perusahaan-perusahaan yang telah bekerja sama dengan PT.Bank X. Kredit tersebut dapat digunakan untuk kepentingan KPR (Kredit Pemilikan Rumah), KPM (Kredit Pemilikan M obil), dan KTA (Kredit tanpa agunan). Kredit M itrakarya ini akan memberikan perhitungan suku bunga khusus, berbeda dengan suku bunga personal. PT.Bank X akan memberikan limit budget kredit untuk satu perusahaan.
72 Job desk dari Personal Loan Department adalah : ¾ M erancang
strategi
pemasaran
agar
masyarakat
yang
membutuhkan dana secara cepat, akan meminjam dana melalui Kredit Tanpa Agunan PT.Bank X. ¾ M enganalisis, merancang dan mengembangkan suatu produk yang sesuai dengan kebutuhan pasar, agar PT.Bank X selalu menjadi Bank terpercaya pilihan masyarakat. Wewenang Personal Loan Department adalah : M enjalin kerja sama dengan perusahaan-perusahaan yang ingin meminjam dana melalui Kredit M itrakarya PT.Bank X. 2. AutomotiveDepartment Automotive Department menangani hal-hal yang berkaitan dengan Kredit Pemilikan M obil (KPM ) dengan agunan/jaminan.Berikut adalah job desk dari Automotive Department : ¾ M erancang strategi agar masyarakat membeli mobil melalui Kredit Pemilikan M obil (KPM ) PT.Bank X dan membuat ketentuan-ketentuan
yang berlaku
untuk
mengajukan
aplikasi KPM PT.Bank X.
Wewenang Automotive Department adalah : M enentukan suku bunga untuk Kredit Pemilikan M obil (KPM ) PT.Bank X serta menentukan jenis agunan yang harus diserahkan pada PT.Bank X sebagai jaminan kredit.
73 3. Mortgage Department Mortgage Department menangani hal-hal yang berkaitan dengan :
Kredit Pemilikan Rumah (KPR) dengan agunan Kredit Pemilikan Rumah (KPR) dengan agunan adalah kredit yang diperuntukkan bagi nasabah yang ingin membeli rumah baru dari developer maupun rumah second (rumah yang pernah dihuni sebelumnya). Agar dapat menggunakan Kredit Pemilikan Rumah (KPR) ini, nasabah harus memberikan agunan yang dapat berupa akte tanah, surat kepemilikan mobil, dan lain-lain.
Kredit M ultiguna Kredit M ultiguna adalah jenis kredit yang dapat digunakan untuk
keperluan
konsumtif,
misalnya
: pendidikan,
kesehatan dan lain-lain. Job desk dari Mortgage Department adalah : ¾ M erancang strategi bisnis agar masyarakat membeli rumah secara kredit melalui KPR PT.Bank X. ¾ M erancang strategi bisnis
agar
masyarakat
memilih
PT.Bank X untuk kredit keperluan konsumtif mereka.
Wewenang Mortgage Department adalah : menentukan bentuk promosi untuk menarik consumer agar berminat pada produk yang ditawarkan oleh Consumer Loan Group.
74 Pada Mortgage Department ini, terdapat divisi promotion and marketing yang bertugas untuk melakukan promosi mengenai produk-produk Consumer Loan Group, seperti : KPM , KPR, KTA, Kredit M itrakarya dan Kredit M ultiguna. Divisi tersebut dapat melakukan promosi melalui berbagai media cetak (koran, majalah, tabloid), elektronik (TV, radio, internet), atau pameran.
4. Developer / Broker Relationship Department Developer / Broker Relationship Department bertugas untuk : ¾ M enjalin kerja sama dengan para developer real estate / perumahan dan para broker (lembaga perantara penjualbelian rumah, misalnya : Ray White, Century 21). ¾ M enilai apakah suatu developer/broker yang akan menjalin hubungan kerja sama dengan PT.Bank X merupakan perusahaan yang berkualitas dan bertanggung jawab. Kerja sama yang dijalin antara PT.Bank X dengan pihak developer adalah mengenai pembelian rumah baru langsung dari developer.
Dengan
adanya
kerja
sama,
diharapkan
dapat
memudahkan transaksi yang terjadi antara nasabah PT.Bank X dengan pihak developernya sendiri.
75
Kerja sama yang dijalin antara Bank M adiri dengan pihak broker adalah mengenai pengurusan atau proses penjual-belian rumah baik yang baru atau pun second. Dengan adanya kerja sama dengan pihak broker, diharapkan dapat memudahkan transaksi yang terjadi antara nasabah PT.Bank X dengan pihak brokernya sendiri.
Wewenang Developer / Broker Relationship Department adalah : menentukan developer/broker mana yang layak dan tidak layak untuk diberikan plafon kredit.
5. Sales Management Department Sales Management Department bertugas untuk melakukan penjualan aplikasi-aplikasi kredit Consumer Loan Group, yakni : Kredit Pemilikan M obil (KPM ), Kredit Pemilikan Rumah (KPR), Kredit Tanpa Agunan (KTA), Kredit M itrakarya dan Kredit M ultiguna. Petugas yang menjalankan tugas tersebut biasanya merupakan outsourcing yang di-hire oleh PT.Bank X untuk menawarkan aplikasi-aplikasi kredit yang ada kepada masyarakat.
Wewenang Sales Management Department adalah : melakukan penjualan aplikasi-aplikasi kredit consumer loan dengan berbagai cara misalnya dengan bazaar, promosi-promosi, dan lain-lain.
76
6. Jakarta Operation Department Jakarta Operation Department merupakan department yang menangani proses pengajuan kredit untuk wilayah Jakarta. Deparment ini bertugas untuk melakukan : ¾ Processing pengajuan kredit, yang dimulai dari permberian informasi dan keterangan mengenai jenis kredit yang dibutuhkan nasabah, ketentuan-ketentuan yang berlaku, cara pembayaran. ¾ Interview nasabah untuk memperoleh data-data pribadi nasabah (misalnya : alamat rumah, pekerjaan, gaji) ¾ Setelah nasabah mengajukan nominal kredit, nasabah akan menerima SPPK (Surat Penawaran Pemberian Kredit) yang berisi limit kredit yang disetujui PT.Bank X untuk nasabah tersebut. Jika nasabah bersedia menerima limit kredit yang diberikan,
maka
selanjutnya
semua
data
mengenai
pengajuan kredit oleh nasabah tersebut diserahkan kepada Jakarta Disbursement Department.
Wewenang
Jakarta
Operation
Department
adalah
:
menyatakan status layak tidaknya sebuah pengajuan kredit dan menentukan besarnya total nominal kredit di wilayah Jakarta.
77
7. Jakarta Disbursement Department Setelah nasabah menyetujui limit kredit yang dicantumkan oleh PT.Bank X pada SPPK, maka Jakarta Disbursement Department bertugas untuk mencairkan dana tersebut. Dana tersebut akan disalurkan langsung kepada pihak-pihak yang terkait, misalnya untuk KPR, dana disalurkan kepada developer. Wewenang Jakarta
Disbursement Department adalah :
menimbang kembali jumlah nominal kredit yang diberikan pada nasabah sesuai dengan yang tertera pada SPPK, dan berhak menolak pencairan dana tersebut jika mendapati hal-hal yang dicurigai.
8. Regional Operation Department Regional Operation Department merupakan department yang menangani processing pengajuan kredit hingga pencairan dana untuk wilayah di luar Jakarta. Rincian tugas Deparment ini adalah: ¾ Processing pengajuan aplikasi kredit, yang dimulai dari permberian informasi dan keterangan mengenai jenis kredit yang
dibutuhkan
nasabah,
ketentuan-ketentuan
yang
berlaku. ¾ Interview nasabah untuk memperoleh data-data pribadi nasabah (misalnya : alamat rumah, pekerjaan, gaji)
78 ¾ Setelah nasabah mengajukan nominal kredit, nasabah akan menerima SPPK (Surat Penawaran Pemberian Kredit), yang berisi limit kredit yang disetujui PT.Bank X untuk nasabah tersebut.
Wewenang
Regional
Operation
Department
adalah
:
menyatakan status layak tidaknya sebuah pengajuan kredit dan menentukan besarnya total nominal kredit di luar wilayah Jakarta.
Perbedaan antara Regional Operation Department dengan Jakarta Operation Department adalah: • Wilayah yang ditanganinya, Regional Operation Department menangani wilayah selain Jakarta sedangkan Jakarta Operation Department hanya menangani wilayah Jakarta. • Pada Jakarta Operation Department, jika nasabah bersedia menerima limit
kredit yang diberikan,
maka data-data
pengajuan aplikasi kredit nasabah tersebut harus diserahkan ke department
lain
yang khusus
untuk
mencairkan
dana.
Sedangkan Regional Operation Department dapat langsung mencairkan dana tersebut tanpa harus menyerahkan data-data pengajuan aplikasi kredit nasabah tersebut ke department lain yang khusus untuk mencairkan dana.
79
3.2 Analisis Application Tuning Plan 3.2.1
Pembentukan Analytical Report Consumer Loan Group
Gambar 0-5 Proses Pembentukan Analytical Report
Proses untuk menghasilkan analytical report pada Consumer Loan Group PT.Bank X dapat dijelaskan sebagai berikut :
Ketika seseorang ingin mengajukan permohonan kredit ke PT.Bank X, ia harus menjalani operational procedure terlebih dahulu. Di mana operational procedure yang dimaksud adalah mengisi form pengajuan kredit. Setelah itu, customer service (CS) akan memasukkan data orang tersebut ke dalam sistem dan data akan diproses sekitar 5-14 hari kerja.
Gambar 0-5 Operational Procedure Pengajuan Kredit
80
Selama permohonan kredit belum di-approve, data tersebut ada di dalam server LOS (Loan Origination System). LOS adalah sebuah database server yang menyimpan informasi calon peminjam PT.Bank X (bisa nasabah, bisa juga non-nasabah PT.Bank X).
Suatu aplikasi pengajuan kredit akan mengalami proses scoring, yakni proses penilaian kelayakan kredit mengenai apakah orang yang mengajukan kredit tersebut akan mampu melunasi kredit tepat waktu dan tanpa masalah. Data-data pribadi yang diisi pada form pengajuan kredit akan menjadi bahan pertimbangan proses scoring. M isalnya : dari total gaji dan pengeluaran per bulan, Consumer Loan akan memprediksikan kemampuan seseorang dalam melunasi kredit.
Jika suatu aplikasi lolos dari proses scoring, dalam arti dinyatakan layak untuk diberikan kredit, maka datanya akan masuk ke dalam server eM AS (Enhancement Mandiri Advanced System). Server eM AS adalah Core Banking System dari PT.Bank X. Database server inilah yang setiap harinya menyimpan data transaksi setiap nasabah PT.Bank X.
Gambar 0-6 Proses Scoring Aplikasi kredit
81
Data dari eM AS dimasukkan ke data warehouse dengan metode Backup dan Restore karena data yang terdapat dalam eM AS terlalu besar, sedangkan bandwidth yang disediakan hanya 1 M bps maka proses tersebut membutuhkan waktu yang cukup lama jika melakukan ETL. Kemudian dilakukan proses ETL dari LOS dan data warehouse ke data mart server dengan proses ETL. Proses ini memakan waktu 12 jam.
Proses yang terjadi di dalam data mart server adalah pembentukan beberapa fact table dari data mart yang kemudian dengan bantuan Analytical Reporting Tools, dihasilkanlah analytical report. Proses ini memakan waktu sekitar lima hingga tujuh jam. Lamanya waktu yang dibutuhkan untuk melakukan proses ini dikarenakan penggunaan codecode yang belum optimal dalam proses pengolahan datanya, misalnya : terdapat penggunaan where clause untuk menggabungkan beberapa tabel, adanya multiple join in one statement, banyaknya penggunaan data type convertion serta belum adanya index terhadap kolom-kolom yang sering diakses serta partitioning untuk data-data yang melebihi 2 GB.
Gambar 0-7 Proses Pembentukan Analytical Report dari Data Mart Server
82 3.2.2
Identifikasi Hardware dan Software Untuk mendukung seluruh kegiatan operasional, Consumer Loan Group
PT.Bank X memiliki spesifikasi hardware dan software sebagai berikut : a. eMAS ( Enhancement Mandiri Advance System ) eM AS merupakan Core Banking PT.Bank X. Di dalamnya terdapat seluruh data-data PT.Bank X, termasuk didalamnya data transaksi dan data nasabah PT.Bank X yang pengajuan kreditnya telah diterima.
Hardware : Hardware
Spesifikasi
Tipe Server
I Series 840
Jumlah CPU
24
RAM
96 GB
Tipe Hardisk
DASD
Kapasitas Hardisk
12.5 T B
Konektivitas
T CP/IP + SDLC
T abel 0-2 Spesifikasi Hardware Server eMAS
Software : Software
Spesifikasi
Database
DB2/400
Ve rsi
6
Language
RPG dan COBOL
Operating System
OS400
T abel 0-3 Spesifikasi Software Server eMAS
83 b. LOS Server ( Loan Origination System ) LOS merupakan server yang berisikan data-data orang yang sedang yang mengajukan kredit (statusnya masih processing, belum di-approve).
Hardware Hardware
Spesifikasi
Tipe Server RAM
Blade Server BL25P Quad Core (4 Processor), Hewlett Packard Proliant 4 GB
Tipe Hardisk
SCSI Dual Ultra 320
Kapasitas Hardisk
250 GB Connect to SAN
Konektivitas
NIC 100/1000 MBPS T abel 0-4 Spesifikasi Hardware LOS
Software Software
Spesifikasi
Ve rsi
2000
Database
SQL Server
Operating System
Windows Server 2000 Advance Server SP4
T abel 0-5 Spesifikasi Software LOS
84
c. Data warehouse Software & Hardware Configuration
Spesifikasi
Database
DB2/400
Tipe Server
IBM 1740
Jumlah CPU
5
RAM
4 GB
Operating System
OS/400
Tipe Hardisk
DASD
Kapasitas Hardisk
2.8 T B
T abel 0-6 Spesifikasi Data Warehouse
d. Data Mart Server Consumer Loan Software & Hardware Configuration
Spesifikasi
Database
Oracle 11g
Reporting Tool
Business Object 6.0
Tipe Server
Intel Xeon 3 Gh for 32 bit
Jumlah CPU
4
RAM
4 GB
Operating System
Windows Server 2000
Tipe Hardisk
SAN
Kapasitas Hardisk
50 GB
T abel 0-7 Spesifikasi Data Mart Server Consumer Loan
85 3.2.3
Analisis Proses Pembentukan Data Mart
Gambar 0-8 Proses Pembentukan Data Mart Consumer Loan
1.Data transaksi harian dari database server eM AS dimasukkan ke data warehouse dengan metode Backup dan Restore. Alasan mengapa harus backup dan restore adalah karena data yang terdapat dalam eM AS terlalu besar, sedangkan bandwidth yang disediakan hanya 1 M bps maka proses tersebut membutuhkan waktu yang cukup lama jika melakukan ETL. 2.Setelah itu dilakukan ETL (Extract, Transform, Load)
di data
warehouse dan LOS server sehingga membentuk data mart yang dibutuhkan oleh consumer loan.
Keterangan Nama Deskripsi
Spesifikasi Metode Pengambilan Data Periode Pengambilan Data
Source System 1 Data Warehouse Menyimpan transaksi harian seluruh nasabah PT.Bank X, termasuk salah satunya data Consumer Loan Group Lihat Tabel 3-6 ETL Harian
Source System 2 LOS Menyimpan detil data calon peminjam dana pada PT.Bank X. Lihat Tabel 3-4, 3-5 ETL Harian
T abel 0-8 Data Source Pembentuk Data Mart Consumer Loan
87
Data Source Pembentuk Datamart Consumer Loan Group : 1. Data dari eM AS (Enhancement Mandiri Advance System) a. Host Application Detail
Nama Tabel Warehouse Nama Tabel Sumber
Frekuensi Jenis Checksum Field Prediksi Jumlah Record
D_ APPLN_HOST _DTL LNAPPF, LNSP3K, LNSP3H Tabel ini menyediakan hubungan antara nomor aplikasi yang disetujui pada eMAS dengan nomor aplikasi calon peminjam dana pada LOS. Data ini dibutuhkan untuk menghasilkan nomor aplikasi baru yang telah disetujui ketika detil dikirimkan ke eMAS. Harian Format DB2 Full (Insert and Update) Delimiter Character NA AFFAMT Primary Key AFAPNO 300.000 Pertambahan Record per Bulan 10%
Penyimpanan Data
1 hari pada waktu data tersebut disimpan dalam warehouse
Deskripsi
Mapping :
No 1 2 3 4 5 6 7 8 9
Tabel LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF
SOURCE SYSTEM Field Deskripsi AFAPNO Application Number AFCPNO Facility Ref No AFFCDE Facility Code AFSEQ Facility Sequence AFAPLY Amount Applied AFFAMT Facility Limit AFEND6 Date Entered AFAPD6 Application Date AFFPMT Final Payment Amount
DATA TYPE Tipe Panjang A 20 A 20 A 4 N 10 N 17,2 N 17,2 D 10 D 10 N 17,2
ORACLE SYSTEM Field Deskripsi HOST _APPLN_NO Host Application Number LOS_APPLN_NO LOS Application Number FACILITY_CD Facility Code FACILITY_SEQ Facility Sequence APPLIED_AMT Amount Applied APPROVED_AMT Facility Limit ENTRY_DATE Date Entered APPLN_DATE Application Date Final Payment Amount FINAL_PMT_AMT
84
88
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNAPPF LNSP3K
AFTERM AFT COD AFPURP AFOFD6 AFARD6 AFAPR1 AFAPR2 AFOFFR AFLMST AFSTAT AFMODE AFRATE AFRAT# AFPFLR AFPCEL AFCUR AFCIF# AFSDC6 LNVDT6 AFAPR6 AFAPR7 AFOAD6 AFBR# AFINST AFARD6 AFAPPR AFORGM AFOBAL AFLTYP SPSP3K
Term Term Code Purpose Date Of Offer Date Approved Approved By 1 Approved By 2 Officer Limit Status Facility Status Mode Of Interest Interest Rate Prime Rate# Prime Rate Floor Prime Rate Ceiling Currency Type Facility CIF No Dt sent to decisn center Last changed ddmmyy Approved Date 2 Approved Date 2 Date Offer Accepted Branch No. Payment Amount Date Approved Approved By Original Amount O/S Balance Product Type SP3K No.
N A A D D A A A A A A N N N N A A D D D D D N N D A N N A A
5 1 5 10 10 10 10 10 2 1 1 11,9 5,0 11,9 11,9 19, 0 19, 0 10 10 10 10 10 5 17,2 10 10 17,2 17,2 10 19
TERM Term TERM_CD Term Code LOAN_PURPOSE Purpose OFFER_DATE Date Of Offer APPROVED_DATE Date Approved BU_APPROVAL_BY Approved By 1 CRM_APPROVAL_BY Approved By 2 OFFICER_CD Officer LIMIT_STATUS Limit Status FACILITY_ST ATUS Facility Status MOD_INT Mode Of Interest INT_RATE Interest Rate PRIM_RATE Prime Rate# PRIM_RATE_FL Prime Rate Floor PRIM_RATE_CL Prime Rate Ceiling CURR_TYPE Currency Type FACILITY_CIF_NO Facility CIF No DEC_SENT_DATE Dt sent to decisn center LAST _CHG_DT Last changed DDMMYY BU_APPROVAL_DATE Approved Date 2 CRM_APPROVAL_DATE Approved Date 2 OFFER_ACCEPT_DATE Date Offer Accepted BRANCH_NO Branch No. PMT_AMT Payment Amount APPROVAL_DATE Date Approved APPROVED_BY Approved By ORG_AMT Original Amount OS_BAL O/S Balance PROD_TYPE Product Type SP3K_NO SP3K No.
85
89
40 41 42 43 44 45 46 47 48 49 50 51 53 54 55 56 57
LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K LNSP3K
SPPRVB SPBNT R SPBAPR SPHAKT SPASJW SPANGS SPPRAR SPDNUM SPABKR SPOTHS SPASKD SPMATR SPDRAC SPBLOK SPUGMK SPAKDR SPASBK
Provisi Bank Biaya Notaris-PK/AJB Biaya Appraisal Biaya SKMHT /APHT/SHT Biaya Asuransi Jiwa Angsuran Bulan Pertama Performed Pra Realisasi Insurance Code Fire Insurance Code Biaya Other Charges Biaya Asuransi Kendara Materai Fee Customer Giro/SA A/C Saldo T abungan Yg Diblok Uang Muka Kendara Insurance Code Biaya Asuransi Kebakaran
N N N N N N A A A N N N N N N A N
17,2 17,2 17,2 17,2 17,2 17,2 1 10 10 17,2 17,2 17,2 19,0 17,2 17,2 10 17,2
PROV_BANK NOTARY_FEE APPRAISAL_FEE SKMHT_FEE LIFE_INS_FEE FIRST_MTH_PMT PRA_REALISASI_FLAG INSURANCE_CD FIRE_INSURANCE_CD ADMINIST RATION_FEE AUTO_INS_FEE ST AMP_FEE CUST_GIRO BLOCK_ACC_BAL DOWN_PMT AUTO_INS_CD HOME_INS_FEE
Provisi Bank Biaya Notaris-PK/AJB Biaya Appraisal Biaya SKMHT /APHT/SHT Biaya Asuransi Jiwa Angsuran Bulan Pertama Performed Pra Realisasi Insurance Code Fire Insurance Code Biaya Other Charges Biaya Asuransi Kendara Materai Fee Customer Giro/SA A/C Saldo T abungan Yg Diblok Uang Muka Kendara Insurance Code Biaya Asuransi Kebakaran
T abel 0-9 T abel Mapping D_ APPLN_HOST_DT L
JOINER : LNAPPF Left Outer Join LNSP3K Left Outer Join LNSP3H ON LNAPPF.AFAPNO = LNSP3K.SPAPNO LNAPPF.AFFCDES = LNSP3K.SPFCDE LNAPPF.AFSEQ = LNSP3K.SPFSEQ LNSP3K.SPSP3K = LNSP3H.STSP3K FILTER : WHERE AFAPNO IN (SELECT AANO FROM LNMAST WHERE LONGRP =1) NB: Host Application Number akan diidentifikasi bersama-sama dengan Facility Code dan Facility Sequence, contoh: Primary Key untuk mengidentifikasi Host Application Number adalah Host Application Number + Facility Code + Facility Sequence . 86
90
b. Account Detail Nama Tabel Warehouse Nama Tabel Sumber Deskripsi Frekuensi Jenis Checksum Field Prediksi Jumlah Record
D_ACCOUNT_DTL LNAPPF, LNMAST, LNBICD, LNSP3K, LNPKNO, LNSPKF, LNSPKH Tabel ini akan berisi detil informasi seputar Loan Accounts Harian Format Full (Insert and Update) Delimiter Character ORGAMT Primary Key 300.000 Pertambahan Record per Bulan
Penyimpanan Data
1 hari pada waktu data tersebut disimpan dalam warehouse
DB2 NA ACCT NO 10%
Mapping:
No 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SOURCE SYSTEM Tabel Field Deskripsi LNMAST BR# Branch Number LNMAST ACCT NO Account Number LNMAST ACT YPE Account Type LNMAST AANO A/A No LNMAST CIFNO CIF Number LNMAST ST ATUS Status LNMAST TYPE Loan Type LNMAST ORGAMT Original Balance LNMAST AMTREL Amount Release LNMAST CBAL Current Balance LNMAST MIPREM Misc Ins Premium LNMAST OT HCHG Other Charges LNMAST MISC$ Misc. Cost LNMAST PAIDLC Paid Late Charges LNMAST MIREB Misc Ins Rebate LNMAST PMTAMT Payment Amount
DATA TYPE Tipe Panjang A 5 N 19 A 1 A 20 N 19 N 1 N 10 N 17,2 N 17,2 N 17,2 N 15,2 N 15,2 N 15,2 N 15,2 N 15,2 N 17,2
ORACLE SYSTEM Field BRANCH_NO ACCT_NO ACCT_TYPE HOST _APPLN_NO CUST_NO ST ATUS_CD LOAN_TYPE ORIG_BAL RELEASE_AMT CURR_BAL MISC_INSURANCE_PREMIUM OT H_CHARGES MISC_COST PAID_LATE_CHARGES MISC_INSURANCE_REBATE PAYMENT_AMT
Deskripsi Branch Number Account Number Account Type Host Application Number Customer Number Status Loan Type Original Balance Amount Release Current Balance Miscellanies Ins Premium Other Charges Misc. Cost Paid Late Charges Miscellanies Ins Rebate Instalment Amount
87
91
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
LNMAST CHGDT Chg off Date LNMAST POFFDT Payoff Date LNMAST RATE Loan interest rate LNMAST TERM Loan Term LNMAST TMCODE Loan Term Code LNMAST FREQ Payment Frequency LNMAST ORGDT6 Original Loan Date LNMAST DLRNO Dealer/ Participation # LNMAST MTDPMR MTD Payment Received LNMAST FCODE Facility Code LNMAST FSEQ Facility Sequence LNMAST BK# Bank Number LNMAST RBR# Reporting Branch LNMAST SNAME Short Name LNMAST LNACN1 Account Name 1 LNMAST LNACN2 Account Name 2 LNMAST OFFCR Officer LNMAST YSAST S Previous Status LNMAST OPNBAL Yearly Opening Bal LNMAST SCHREL Amt to be released LNMAST LSTPMT Last Pymt Amount LNMAST LYWOFF Last Year Write Off LNMAST DRLIMT Drawing Limit LNMAST REDBAL Reduced Balance LNMAST ORGMD6 Original Maturity Date LNMAST FRELDT 1st Release Date LNMAST FULLDT Full Rel. Date LNMAST NPLDT NPL Date LNMAST CPD10 Times Past Due 10-29 LNMAST CPD30 Times Past Due 30-59
D D N N A N D A N A N N N A A A A N N N N N N N N N D D D N
10 10 11,9 5 1 3 10 10 17,2 4 10 2 5 20 40 40 10 1 17,2 17,2 17,2 17,2 17,2 17,2 6 10 10 10 3 3
CHARGEOFF_DATE PAY_OFF_DATE INT_RATE LOAN_TERM LOAN_TERM_CD PAYMENT_FREQUENCY ORIG_LOAN_DATE DEVELOPER_CD MTD_PAYMENT_RCVD FACILITY_CD FACILITY_SEQ BANK_NO REPORTING_BRANCH SHORT _NAME ACCT_NAME1 ACCT_NAME2 OFFICER PREVIOUS_STATUS YEARLY_OPENING_BAL AMT_TO_BE_RELEASED LAST _PAYMENT_AMT LAST _YEAR_WRITE_OFF DRAWING_LIMIT REDUCED_BAL ORIG_MATURITY_DATE FIRST_RELEASE_DATE FULL_RELASE_DATE NPL_DATE TIMES_DPD_10_29 TIMES_DPD_30_59
Chg off Date Payoff Date Loan interest rate Loan Term Loan Term Code Payment Frequency Original Loan Date Developer Code MTD Payment Amount Facility Code Facility Sequence Bank Number Reporting Branch Short Name Account Name 1 Account Name 2 Officer Previous Status Yearly Opening Bal Amt to be released Last Payment Amount Last Year Write Off Drawing Limit Reduced Balance Original Maturity Date 1st Release Date Full Rel. Date NPL Date Times Past Due 10-29 Times Past Due 30-59
88
92
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
LNMAST CPD60 LNMAST CPD90 LNMAST YT DINT LNMAST YT DPRN LNMAST YT DLC LNMAST LPD10 LNMAST LPD30 LNMAST LPD60 LNMAST LPD90 LNMAST DATOP6 LNMAST GROUP LNMAST CURT YP LNMAST ACBAL LNMAST ACCINT LNMAST CURPO LNMAST FNLPMT LNMAST PRTPMT LNMAST BILPRN LNMAST BILINT LNMAST BILLC LNMAST BILOC LNMAST YSCBAL LNMAST LMOSBL LNMAST NSPDT6 LNMAST NPDAY LNMAST POFFD6 LNMAST MATDT LNMAST FPDT LNMAST LPDT LNMAST NPDT
Times Past Due 60-89 Times Past Due 90-119 Interest Paid YTD Principal Paid YTD Late chgs Paid YTD L-T-D Past Due 10-29 L-T-D Past Due 30-59 L-T-D Past Due 60-89 L-T-D Past Due 90-119 Date account opened Group Code Currency Type Accrual Balance ACCRUED INTEREST Current Month Payoff Final Payment Partial Pymt Amt Billed Prin Amt Billed Int Amt Billed Late Chgs Billed Other Chgs Yesterday Balance Previous Mth O/S Next Sched Pymt Date Pymt Day of Month Payoff Date Maturity Date 1st Pymt Date Last Pymt Date Next Pymt Due Date
N N N N N N N N N D N A N N N N N N N N N N N D N D D D D D
3 3 17,2 17,2 17,2 3 3 3 3 10 3 4 17,2 17,5 17,2 17,2 17,2 17,2 17,2 15,2 15,2 15,2 17,2 10 2 10 10 10 10 10
TIMES_DPD_60_89 TIMES_DPD_90_119 INT_PAID_YTD PRINCIPAL_PAID_YTD LATE_CHARGES_PAID_YTD LT_DPD_10_29 LT_DPD_30_59 LT_DPD_60_89 LT_DPD_90_119 DATE_OPENED GROUP_CD CURR_TYPE ACCR_BAL ACCR_INT CURR_MTH_PAYOFF FINAL_PMT PARTIAL_PMT BILLED_PRN_AMT BILLED_INT_AMT BILLED_LATE_CHGS BILLED_OTH_CHGS YEST ERDAY_BAL PREV_MTH_OS NEXT_SCHD_PMT_DATE PMT_DAY_MTH PAYOFF_ DATE MATURITY_ DATE FIRST_PMT_ DATE LAST _PMT_ DATE NEXT_PMT_ DATE
Times Past Due 60-89 Times Past Due 90-119 Interest Paid YTD Principal Paid YTD Late charges Paid YTD L-T-D Past Due 10-29 L-T-D Past Due 30-59 L-T-D Past Due 60-89 L-T-D Past Due 90-119 Date Account Opened Group Code Currency Type Accrual Balance ACCRUED INTEREST Current Month Payoff Final Payment Partial Pymt Amt Billed Prin Amt Billed Int Amt Billed Late Chgs Billed Other Chgs Yesterday Balance Previous Mth O/S Next Sched Pymt Date Pymt Day of Month Payoff Date Maturity Date 1st Pymt Date Last Pymt Date Next Pymt Due Date
89
93
77 LNMAST PRATE# 78 LNMAST PFLOOR 79 LNMAST PCEIL 80 LNMAST FRCODE 81 LNMAST ALT SCH 82 LNMAST INTPRE 83 LNMAST LCPRE 84 LNMAST EXTPRE 85 LNMAST INTLTD 86 LNMAST PURCOD 87 LNMAST NPLST A 88 LNMAST ST SDT6 89 LNMAST ALTRAT 90 LNMAST YSACBL 91 LNMAST CARCD 92 LNMAST FUDRW 93 LNMAST FUDPC 94 LNMAST FUDAR 95 LNMAST MTDIDB 96 LNMAST MTDLDB 97 LNMAST MTDODB 98 LNMAST MTDPRN 99 LNMAST MTDINT 100 LNMAST MTDLC 101 LNMAST MTDMSC 102 LNMAST MTDOTH 103 LNMAST YT DPMR 104 LNMAST YT DOTH 105 LNMAST LTDPMR 106 LNMAST LTDIDB
Prime Rate Number Prime Rate Floor Prime Rate Ceiling Payment Freq Code Alternate Pymt Sched Int Paid Last Year LC paid Last Year Times extended Last Yr Interest Paid L-T-D Purpose Code NPL Status Status Date Alternate Rate Yesterdays accrual bal Classified Acct.code Undrawn Limit % Of Total Undrawn Undrawn % If Above Lmt MTD accr int debit MTD late chg debit MTD other chg debit MTD principal paid MTD int paid MTD LC paid MTD misc chg paid MTD other chg paid YT D payms received YT D other chg paid LTD payms received LTD accr int debit
N N N A A N N N N A A D A N A N N N N N N N N N N N N N N N
5 11,9 11,9 1 1 17,2 15,2 3 17,2 5 1 10 1 17,2 2 17,2 11,9 11,9 17,2 17,2 17,2 17,2 17,2 17,2 17,2 17,2 17,2 17,2 17,2 17,2
PRIME_RT_NO PRIME_RATE_FL PRIME_RATE_CL PMT_FREQ_CD ALT_PMT_SCHD INT_PAID_LASTYEAR LC_PAID_ LAST YEAR TIME_EXT_ LAST YEAR LTD_INT_PAID PURPOSE_CD NPL_ST ATUS ST ATUS_DATE ALT_RATE YST_ACCR_BAL CLASS_ACCT_CD UNDRAWN_LMT PERCENT_UNDRAWN PERCENT_UNDRAWN_ABV_LMT MTD_ACCR_INT_DBT MTD_LT_CHG_DBT MTD_OTH_CHG_DBT MTD_PRINCIPAL_PAID MTD_INT_PAID MTD_LC_PAID MTD_MISC_CHG_PAID MTD_OTH_CHG_PAID YT D_PMT_RCVD YT D_OTH_CHG_PAID LTD_PMT_RECVD LTD_ACCR_INT_DBT
Prime Rate Number Prime Rate Floor Prime Rate Ceiling Payment Freq Code Alternate Pymt Sched Int Paid Last Year LC paid Last Year Times extended Last Yr Interest Paid L-T-D Purpose Code NPL Status Status Date Alternate Rate Yesterdays accrual bal Classified Acct.code Undrawn Limit % Of Total Undrawn Undrawn % If Above Lmt MTD accr int debit MTD late chg debit MTD other chg debit MTD principal paid MTD int paid MTD LC paid MTD misc chg paid MTD other chg paid YT D payms received YT D other chg paid LTD payms received LTD accr int debit
90
94
107 LNMAST LTDLDB LTD late chg debit 108 LNMAST LTDODB LTD other chg debit 109 LNMAST LTDPRN LTD principal paid 110 LNMAST LTDLC LTD LC paid 111 LNMAST LTDOTH LTD other chg paid 112 LNMAST OPMTAM Original payment amount 113 LNMAST DEPTCD Department Code 114 LNMAST INT SUP Total Int-In-Susp 115 LNPKNO ACTPKN PK NO 116 LNPKNO ACT DA6 PK DATE 6 117 LNBICD BIKOLE KOLEKTIBILITAS 118 LNBICD BISEKT SEKT OR EKONOMI 119 LNBICD ACT YPE Account Type 120 LNBICD BISIFA Sifat Kredit 121 LNBICD BIPENG Jenis Penggunaan 122 LNBICD BILOK1 Lokasi 1 123 LNBICD BIKUK2 KUK/NONKUK (II) 124 LNBICD BIPEMI Pemilik / Debitur 125 LNBICD BIGUNA Orientasi Penggunaan 126 LNBICD BISBMC Sandi Penyebab Macet 127 LNBICD BIT GM7 Tanggal Macet 128 LNBICD BIPROJ Nilai Proyek 129 LNBICD BILOKJ Sandi Lokasi Proyek 130 LNBICD BIADDR Alamat Proyek 131 LNBICD BIREAC Reason Code 132 LNAPPF AFCPNO Facility Ref No 133 Blacklist Flag 134 LNSPKF SPSP3K SP3K No. 135 LNSPKF SPPRVB Provisi Bank 136 LNSPKF SPBNT R Biaya Notaris-PK/AJB
N N N N N N A N A D N N A N N N N N N N D N N A A A A A N N
17,2 17,2 17,2 17,2 17,2 17,2 5 17,2 35 10 1 4 1 2 2 4 2 3 1 1 10 17,2 4 40 2 20 20 29 17,2 17,2
LTD_LATE_CHG_DBT LTD_OTH_CHG_DBT LTD_PRINCIPAL_PAID LTD_LC_PAID LTD_OTH_CHG_PAID ORIG_PMT_AMT BUC INT_IN_SUSPENSE PK_NO PK_DATE KOLEKTIBILITAS SECTOR_ECONOMY ACC_TYPE CREDIT_TYPE BI_CREDIT_PURPOSE BI_LOCATION BI_KUK_NONUK BI_DEBITOR BI_ORIENTATION BI_DEFAULT_CD BI_DEFAULT_DATE BI_PROJ_VALUE BI_PROJ_LOC_CD BI_PROJ_ADDR BI_REASON_CD LOS_APPLN_NO BLACKLIST_FLAG SP3K_NO PROV_BANK NOTARY_FEE
LTD late chg debit LTD other chg debit LTD principal paid LTD LC paid LTD other chg paid Original payment amount Business Unit Code Total Interest in Suspense PK NO PK DATE 6 KOLEKTIBILITAS SEKT OR EKONOMI Account Type Sifat Kredit Jenis Penggunaan Lokasi 1 KUK/NONKUK (II) Pemilik / Debitur Orientasi Penggunaan Sandi Penyebab Macet Tanggal Macet Nilai Proyek Sandi Lokasi Proyek Alamat Proyek Reason Code Facility Ref No Blacklist Flag SP3K No. Provisi Bank Biaya Notaris-PK/AJB
91
95
137 LNSPKF 138 LNSPKF 139 LNSPKF 140 LNSPKF 141 LNSPKF 142 LNSPKF 143 LNSPKF 144 LNSPKF 145 LNSPKF 146 LNSPKF 147 LNSPKF 148 LNSPKF 149 LNSPKF 150 LNSPKF 151 LNSPKF 152 LNSPKF 153 LNSPKF 154 LNSPKH
SPBAPR SPHAKT SPASJW SPANGS SPPRAR SPDNUM SPABKR SPOTHS SPASKD SPMATR SPDRAC SPBLOK SPUGMK SPAKDR SPASBK SPOCIF SPADAC ST AMT
Biaya Appraisal Biaya SKMHT /APHT/SHT Biaya Asuransi Jiwa Angsuran Bulan Pertama Performed Pra Realisasi Insurance Code Fire Insurance Code Biaya Other Charges Biaya Asuransi Kendara Materai Fee Customer Giro/SA A/C Saldo T abungan Yg Diblok Uang Muka Kendara Insurance Code Biaya Asuransi Kebakaran Old CIF Alih Debitur Old CIF A/C No Transaction Amount
N N N N A A A N N N N N N A N N N N
17,2 17,2 17,2 17,2 137 138 148 17,2 17,2 17,2 19,0 17,2 17,2 10 17,2 19,0 19,0 17,2
APPRAISAL_FEE SKMHT_FEE LIFE_INS_FEE FIRST_PMT PRA_REALISASI_FLAG INSURANCE_CD FIRE_INSURANCE_CD ADMINIST RATION_FEE AUTO_INS_FEE ST AMP_FEE CUST_GIRO BLOCK_ACC_BAL DOWN_PMT AUTO_INS_CD HOME_INS_FEE OLD_CIF_OTH OLD_CIF_ACC_NO TRANS_AMT
Biaya Appraisal Biaya SKMHT /APHT/SHT Biaya Asuransi Jiwa Angsuran Bulan Pertama Performed Pra Realisasi Insurance Code Fire Insurance Code Biaya Other Charges Biaya Asuransi Kendara Materai Fee Customer Giro/SA A/C Saldo T abungan Yg Diblok Uang Muka Kendara Insurance Code Biaya Asuransi Kebakaran Old CIF Alih Debitur Old CIF A/C No Transaction Amount
T abel 0-10 Tabel Mapping D_ACCOUNT_DTL Field 133 : IF LNMAST .ACCTNO = CFBLST.CBACNO THEN BLACKLIST_FLAG = 1 JOINER : LNMAST FJ LNBICD FJ LNPKNO LOJ LNSPKF LOJ LNSP3H ON LNMAST .ACCTNO = LNPKNO.ACCTNO, LNMAST .ACCTNO = LNBICD.ACCTNO, LNMAST .ACCTNO = LNASPKF.SPACCT, LNMAST .ACCTNO = LNSPKH.LNACCT, LNSPKF.SPSP3K = LNSPKH.ST SP3K FILTER : LNMAST .LONGRP = '1 '
92
96
c. Account Day Past Due Information Nama Tabel Warehouse Nama Tabel Sumber Deskripsi Frekuensi Jenis Checksum Field Prediksi Jumlah Record
D_ACCT_DPD_ENH LNPDUE Tabel ini berisi detil dari account yang bermasalah dengan cicilan kredit Harian Format Full (Insert and Update) Delimiter Character PDDAYS Primary Key 30.000 Pertambahan Record per Bulan
Penyimpanan Data
1 hari pada waktu data tersebut disimpan dalam warehouse
DB2 NA ACCT NO 10%
Mapping : No 1 2 3 4 5 6 7 8 9 10 11 12
Tabel LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE LNPDUE
SOURCE SYSTEM Field Deskripsi ACCT NO Account Number PDDAYS Days Past Due INDDAY Interest Days Past Due BR# Branch Number OFFCR Officer NPDT6 Next Pymt Due Date PDIPRN Principal Due PDIINT Interest Due PDILC Late chgs Due PDIOTH Other chg Due PDIMSC Misc cost Due PDITOT Total Amt Due
DATA TYPE Tipe Panjang N 19 N 5 N 5 A 5 A 10 D 10 N 17,2 N 17,2 N 15,2 N 15,2 N 15,2 N 17,2
ORACLE SYSTEM Field ACCT_NO DPD INT_DPD BRANCH_NO OFFICER_CD NXT_PMT_DUE_DATE PRINCIPAL_DUE INT_DUE LT_CHGS_DUE OT H_CHGS_DUE MISC_COST_DUE TOT_AMT_DUE
Deskripsi Account Number Days Past Due Interest Days Past Due Branch Number Officer Next Pymt Due Date Principal Due Interest Due Late chgs Due Other chg Due Misc cost Due Total Amt Due
T abel 0-11 Tabel Mapping D_ACCT_DPD_ENH FILTER : WHERE LNPDUE.ACCTNO IN (SELECT ACCTNO FROM LNMAST WHERE LONGRP = 1)
93
97
d. Customer Host Detail Nama Tabel Warehouse Nama Tabel Sumber Deskripsi Frekuensi Jenis Checksum Field Prediksi Jumlah Record Penyimpanan Data
D_CUST_HOST _DTL CFMAST , CFZEMP Tabel ini berisi data detil semua customer pada eMAS. Harian Format Full (Insert and Update) Delimiter Character CFECUR Primary Key 300.000 Pertambahan Record per Bulan 1 hari pada waktu data tersebut disimpan dalam warehouse
DB2 NA CFCIF# 10%
Mapping : No 1 2 3 4 5 6 7 8 9 10 11 12
Tabel CFMAST CFMAST CFMAST CFMAST CFMAST CFMAST CFMAST CFMAST CFMAST CFMAST CFMAST CFZEMP
SOURCE SYSTEM Field Deskripsi CFBNKN Bank number CFBRNN Branch number CFCIF# Customer number CFSNME Short name CFRESD Resident code CFSSNO ID number CFBIRD Birth/incorporation date CFSEX Sex code CFBUST Business type CFIEX6 ID Expiry date CFYBIP Birth/Incorporation place CFECUR Current Salary
DATA TYPE Tipe Panjang N 2 N 5 N 19 A 20 A 1 A 40 D 10 A 1 A 4 D 10 A 30 N 17,2
ORACLE SYSTEM Field Deskripsi BANK_NO Bank number BRANCH_CD Branch number CUST_NO Customer number SHORT _NAME Short name RESIDENT_CD Resident code ID_NO ID number BIRT H_DATE Birth/incorporation date SEX Sex code BUS_T YPE Business type ID_EXP_DT ID Expiry date BIRT H_PLACE Birth/Incorporation place CURR_SAL Current Salary
T abel 0-12 Tabel Mapping D_CUST _HOST_DT L JOINER : CFMAST LOJ CFZEMP CFMAST .CFCIF# = CFZEMP.CFCIF#
98
FILTER :
WHERE CFCIF# IN (SELECT CIFNO FROM LNMAST WHERE LONGRP =1)
e. Customer Address Detail 94
Nama Tabel Warehouse Nama Tabel Sumber Deskripsi Frekuensi Jenis Checksum Field Prediksi Jumlah Record
D_CUST_ADDR_DTL CFADDR Tabel ini berisi data detil dari alamat customer Harian Format Full (Insert and Update) Delimiter Character CFAYRS Primary Key 1.200.000 Pertambahan Record per Bulan
Penyimpanan Data
1 hari pada waktu data tersebut disimpan dalam warehouse
DB2 NA CFCIF# 10%
Mapping :
No 1 2 3 4 5 6 7 8 9
Tabel CFADDR CFADDR CFADDR CFADDR CFADDR CFADDR CFADDR CFADDR CFADDR
SOURCE SYSTEM Field Deskripsi CFCIF# Customer number CFZIP Postal code CFASEQ Address Seq No CFNA2 Address line 1 CFNA3 Address line 2 CFNA4 Address line 3 CFNA5 Address line 4 CFAPTY Address Property Type Code CFAYRS Period there
DATA TYPE Tipe Panjang A 19 N 9 N 3 A 40 A 40 A 40 A 40 A 5 N 3
Field CUST_NO POSTAL_CD ADDR_SEQ_NO
ORACLE SYSTEM Deskripsi Customer number Postal code Address Seq No
CUST_ADDRESS
Customer Host Address
PROPERTY_TYPE_CD Address Property Type Code LONG_OF_ST AY Period there
T abel 0-13 Tabel Mapping D_CUST _ADDR_DTL FILTER :
WHERE CFCIF# IN (SELECT CIFNO FROM LNMAST WHERE LONGRP =1)
95
99
2. Data dari LOS (Loan Origination System) a. Application LOS Detail Nama Tabel Warehouse Nama Tabel Sumber Deskripsi Frekuensi Jenis Checksum Field Prediksi Jumlah Record
D_APPLN_LOS_DTL APP Tabel ini berisi detil tentang aplikasi di LOS Harian Format Full (Insert and Update) Delimiter Character AP_ASSOSIATEBANK Primary Key 300.000 Pertambahan Record per Bulan
Penyimpanan Data
1 hari pada waktu data tersebut disimpan dalam warehouse
SQL Server NA AP_REGNO 10%
Mapping : SOURCE SYSTEM
DATA TYPE ORACLE SYSTEM No Tabel Field Deskripsi Tipe Panjang Field Deskripsi 1 APP AP_AGENOFRCODE Agency Sales Officer ID A 10 SALES_EXEC_ID Agency Sales Officer ID 2 APP AP_APPRRCVDATE Appraisal Received Date D 10 APPRAISAL_END_DATE Appraisal Received Date 3 APP AP_APPRSENDDATE Appraisal Send Date D 10 APPRAISAL_START_DATE Appraisal Send Date 4 APP AP_BICHECKINGDATE BI Checking Date D 10 BI_CHECK_DATE BI Checking Date 5 APP AP_BOOKINGDATE Booking Date D 10 DISBMT_END_DATE Booking Date 6 APP AP_CMPRVWDATE D 10 DOC_COLL_SPPK_END_DATE Document collection – SPPK date 7 APP AP_DECSDATE Decision Date D 10 APPR_END_DATE Decision Date 8 APP AP_DECSSTA Decision Status A 20 FINAL_APPLN_ST ATUS Decision Status 9 APP AP_INPTBODATE Tanggal Initial Kel.Dokumen D 10 DT BO_ST ART_DATE Tanggal Initial Kel.Dokumen 10 APP AP_INVRCVDATE Investigation Received Date D 10 INVST_END_DATE Investigation Received Date 11 APP AP_INVSENDDATE Investigation Send Date D 10 INVST_ST ART_DATE Investigation Send Date 12 APP AP_NAKDATE Nota Analisa Kredit Date D 10 APPR_START_DATE Nota Analisa Kredit Date 13 APP AP_RCVBYLDDATE Received Loan Disbursement Date D 10 SEND_LD_END_DATE Received Loan Disbursement Date 14 APP AP_RECVDATE Receive Date D 10 APPLN_RCVD_DATE Receive Date 15 APP AP_REGIONAL_ID Regional_ID A 10 REGIONAL_ID Regional_ID 16 APP AP_REGNO Application No. A 20 APPLN_NO Application No.
96
100
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP
AP_SCORINGDATE Scoring Date AP_SNDBYLPDATE Send By Loan Processing Date AP_SPPKCONFDATE SPPK Confirmation Date AP_SPPKDATE SPPK Date AP_TBODATE Tanggal Kelengkapan Dokumen AP_VERASGDAT E Verification assign date AP_VERRCVDATE Verification Received Date AP_VERSENDDATE Verification Send Date BRANCH_CODE Branch CH_CODE Channels CU_MITRACOMP Company Code (Mitrakarya) CU_REF Cust Ref # RJ_CODE Reject Code SA_CODE Source Code AP_AGENCYCODE Agency ID AP_AOCODE CSO AP_APPENTRYDATE Application Entry Date AP_APPRAISAL Appraisal Flag AP_APPREMARK Application Remarks AP_APPROVALBY Approval By AP_APPROVALDATE Approval Date AP_APRDEV Approval Deviation Flag AP_APRDEVBY Approval Deviation By AP_BICHECKING BI Checking AP_BIST A BI Status AP_BOOKINGBY Booking By AP_BOOKINGST A Booking Status AP_CONFIRMBOOKING Confirmation Booking AP_DTENTRYBY Data Entry By AP_INVENT RYDATE Investigation Entry Date AP_LASTTRACK Last Track
D D D D D D D D A A A A A A A A D A A A D A A A A A A A A D A
10 10 10 10 10 10 10 10 5 3 10 20 10 10 10 20 10 1 16 20 10 1 20 5 10 20 1 1 20 10 20
FS_LS_END_DATE Scoring Date SEND_LD_ST ART_DATE Send Loan Processing Date DOC_COLL_SPPK_START_DATE SPPK Confirmation Date SPPK_END_DATE SPPK Date DT BO_END_DATE Tanggal Kelengkapan Dokumen VER_ASG_DATE Verification assign date VERFN_END_DATE Verification Received Date VERFN_ST ART_DATE Verification Send Date BRANCH_CD Branch CHANNEL_CODE Channels COMPANY_CD Company Code (Mitrakarya) CUST_NO Cust Ref # REJ_REASON_CD Reject Code BRANCH_NO Source Code AGENCY_CD Agency ID AO_CD CSO APPLN_ENTRY_DATE Application Entry Date APPRAISAL_FLAG Appraisal Flag APPLN_REMARK Application Remarks APPROVAL_BY Approval By APPROVAL_DATE Approval Date APROVAL_DEV_FLAG Approval Deviation Flag APROVAL_DEV_BY Approval Deviation By BI_CHECKING BI Checking BI_ST A BI Status BOOKING_BY Booking By BOOKING_ST ATUS Booking Status CONFIRM_BOOKING Confirmation Booking DATA_ENT RY_BY Data Entry By INV_ENTRY_DATE Investigation Entry Date LAST _TRACK Last Track
97
101
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP APP
AP_LASTTRBY Last Track By AP_LASTTRDATE Last Track Date AP_NAIKATAS Naik atas AP_NAKBY Nota Analisa Kredit By AP_NOTARY_RCVDATE Notary Received Date AP_NOTARY_SNDDATE Notary Send Date AP_ORCODE Override Reason AP_ORGMKTNAME Marketing Org. Name AP_ORGOFRNAME Officer Org. Name AP_OVERRIDE Override Status AP_OVERRIDEBY Override By AP_OVERRIDEDATE Override Date AP_RCVBYLDBY Received Loan Disbursement By AP_SCORINGBY Scoring By AP_SPPKBY SPPK By AP_SPPKCONFBY SPPK Confirmation By AP_TBOBY Kelengkapan Dokumen By PR_CODE Program Code ST ATUS_BL Black list Status AP_DTENTRYDATE T gl Data Entry AP_INVDATE Investigation Date AP_NOTARYDATE Notary Date AP_REJECTBY Reject By AP_REJECTDATE Reject Date AP_SCORE Scoring Flag AP_SCOREBY Scoring By AP_SCOREDATE Scoring Date AP_SIGNDATE Sign Date
A D A A D D A A A A A D A A A A A A A D D D A D A A D D
20 10 1 20 10 10 10 50 80 1 20 10 20 20 20 20 20 20 1 10 10 10 20 10 1 25 10 10
LAST _TRACK_BY LAST _TRACK_DATE NAIK_ATAS NAK_BY NOTARY_RCVD_DATE NOTARY_SEND_DATE OVERRIDE_REASON_CD ORG_MARKT_NAME ORG_OFR_NAME OVERRIDE_STATUS OVERRIDE_BY OVERRIDE_DATE RCVD_BY_LD_BY SCORING_BY SPPK_BY SPPK_CONFIRM_BY TBO_BY PRG_CODE BLACKLIST_ STATUS DATE_ENT RY_DATE INV_DATE NOTARY_DATE REJECT_BY REJECT_DATE SCORING_FLAG SCORING_BY SCOR ING_DATE SIGN_DATE
Last Track By Last Track Date Naik Atas Nota Analisa Kredit By Notary Received Date Notary Send Date Override Reason Marketing Org. Name Officer Org. Name Override Status Override By Override Date Received Loan Disbursement By Scoring By SPPK By SPPK Confirmation By Kelengkapan Dokumen By Program Code Black list Status T gl Data Entry Investigation Date Notary Date Reject By Reject Date Scoring Flag Scoring By Scoring Date Sign Date
T abel 0-14 Tabel Mapping D_APPLN_LOS_DTL
98
102
b.
Customer LOS Detail Nama Tabel Warehouse Nama Tabel Sumber Deskripsi Frekuensi Jenis Checksum Field Prediksi Jumlah Record
D_CUST_LOS_DTL CUST Tabel ini berisi detil tentang customer pada LOS Harian Format Full (Insert and Update) Delimiter Character CU_DEINCOME Primary Key 300.000 Pertambahan Record per Bulan
Penyimpanan Data
1 hari pada waktu data tersebut disimpan dalam warehouse
SQL Server NA CU_REF 10%
Mapping:
No 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SOURCE SYSTEM DATA TYPE ORACLE SYSTEM Tabel Field Field Deskripsi Tipe Panjang Deskripsi CUST CU_REF Cust Ref # A 20 CUST_NO Cust Ref # CUST CU_KTPZIPCODE City A 7 POSTAL_CD City CUST CU_OFINCMMTOT Total Income N 17,2 TOTAL_INCOME Total Income CUST CU_CHILD # Of Dependent N 10 NO_OF_CHILD # Of Dependent CUST CU_HMCITY City A 25 HOME_CITY_CD City CUST CP_CODE Company Ownership A 10 COMP_OWN_CD Company Ownership CUST CUST_TYPE Cust Type A 10 CUST_TYPE Cust Type CUST CU_CIF Customer CIF A 30 CUST_CIF_NO Customer CIF CUST CU_DEINCOME Data Entry Income N 22,2 DATA_ENT RY_INCOME Data Entry Income CUST CU_DEOTHINC Data Entry Other Income N 17,2 DATA_ENT RY_OTH_INC Data Entry Other Income CUST CU_DESPINCOME Data Entry Spouse Income N 17,2 DATA_ENT RY_SP_INCOME Data Entry Spouse Income CUST CU_DESPOTHINC Data Entry Spouse Other Income N 17,2 DATA_ENT RY_SP_OTH_INC Data Entry Spouse Other Income CUST CU_EMAIL Email A 50 EMAIL_ADDR Email CUST CU_EMADDR1 Emergency Address 1 A 80 Emergency Address CUST CU_EMADDR2 Emergency Address 2 A 80 EMG_ADDR CUST CU_EMADDR3 Emergency Address 3 A 80
99
103
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
CUST CU_EMCITY Emergency Address City CUST CU_EMHPNUM Emergency Address HP CUST CU_EMHMPHNAREA Emergency Address Phone Area CUST CU_EMHMPHNNUM Emergency Address Phone No CUST RELIGI_CODE Agama CUST CU_EMRELSHIP Emergency Contact Relationship CUST CU_EMNMFIRST Emergency First Name CUST CU_EMNMLAST Emergency Last Name CUST CU_EMNMMID Emergency Middle Name CUST JOB_TYPE_ID Employment Type CUST CU_OFINCNETMM Income Netto CUST CU_KTPNO KTP # CUST CU_KTPADDR1 KTP Address1 CUST CU_KTPADDR2 KTP Address2 CUST CU_KTPADDR3 KTP Address3 CUST CU_KTPDATE KTP Issuance Date CUST CU_KTPEXPDATE KTP Validity CUST CU_OFPRENAME Office Previous Name CUST CU_OFINCOTH Other Income CUST CU_OTHERLOAN Pinjaman Lain CUST CU_POB place of birth CUST CU_OFPRELIFEYY Previous Year Of Service CUST CU_HMRT RT CUST CU_HMRW RW CUST CU_SAMEADDR Same Address Flag CUST CU_SPCOMPTYPE Spouse Company Type CUST CU_SPOFINCNETMM Spouse Net Income per Month CUST CU_SPOWNSHIP Spouse-Company Ownership CUST CU_SPBORNDATE Spouse-Date Of Birth CUST CU_SPEDCODE Spouse-Education CUST CU_SPJOB_TYPE Spouse-Employment Type
A A A A A A A A A A N A A A A D D A N N A A A A A A N N D A A
20 15 4 20 1 10 30 30 30 3 17,2 50 50 50 50 10 10 30 17,2 17,2 50 4 5 5 1 10 17,2 17,2 10 10 10
EMG_CITY EMG_HP_NO
Emergency Address City Emergency Address HP
EMG_HM_PHONE_NO
Emergency Address Phone No
RELIGION_CD EMG_RELSHIP
Agama Emergency Contact Relationship
EMG_NAME
Emergency Name
EMPLOYMENT_TYPE INC_NET KTP_NO
Employment Type Income Netto KTP #
KTP_ADDRESS
KTP Address
KTP_ISSUE_DATE KTP Issuance Date KTP_EXP_DATE KTP Validity PREV_OFF_NAME Office Previous Name OT HER_INCOME Other Income OT HER_LOAN Pinjaman Lain BIRT H_PLACE place of birth PREV_LIFE_OF_SERV_YEAR Previous Year Of Service RT RT RW RW SAME_ADDR_FLAG Same Address Flag SP_COMP_TYPE Spouse Company Type SP_INC_NET_MM Spouse Net Income /Month SP_COMP_OWNSHIP Spouse-Company Ownership SP_DOB Spouse-Date Of Birth SP_EDU_CD Spouse-Education Spouse-Employment Type SP_JOB_TYPE
100
104
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
CUST CU_SPJABATAN Spouse-Jabatan CUST CU_SPJOB Spouse-Job CUST CU_JOININCOME Spouse-Join Income CUST CU_SPKTPNUM Spouse-KTP # CUST CU_SPKTPADDR1 Spouse-KTP Address1 CUST CU_SPKTPADDR2 Spouse-KTP Address2 CUST CU_SPKTPADDR3 Spouse-KTP Address3 CUST CU_SPKTPCITY Spouse-KTP City CUST CU_SPKTPEXP Spouse-KTP Expiry Date CUST CU_SPKTPDATE Spouse-KTP Issue Date CUST CU_SPKTPZIPCODE Spouse-KTP Kode Pos CUST CU_SPNMFIRST Spouse-First Name CUST CU_SPNMLAST Spouse-Last Name CUST CU_SPNMMID Spouse-Mid Name CUST CU_OFCOMPTYPE Spouse-Office Company Type CUST CU_SPOFINCOTH Spouse-Other Income CUST CU_SPOFPOS Spouse-Position CUST CU_SPOFINCMMTOT Spouse-Total Income CUST CU_SPOFTOTLIFE Spouse-Total Services CUST CU_OFOUTINCTOT Total Expenses CUST CU_KTPCITY Zip Code CUST CU_BORNDATE Date Of Birth CUST JT_CODE Job T ittle CUST BT _CODE Business Type CUST CU_OFNAME Company Name CUST CT _CODE Company Type CUST ED_CODE Education CUST HM_CODE Home Status CUST CU_OFOPRYY Lama Usaha (year) CUST CU_HMLIFEYY Length Of Stay (year) CUST MR_CODE Marital
A A A A A A A A D D A A A A A N A N A N A D A A A A A A N N A
10 30 1 50 30 30 30 50 10 10 7 30 30 30 20 17,2 10 17,2 4 17,2 30 10 10 10 100 10 10 10 10 10 10
SP_JABATAN SP_JOB JOIN_INCOME_FLAG SP_KTP_NO
Spouse-Jabatan Spouse-Job Spouse-Join Income Spouse-KTP #
SP_KTP_ADDR
Spouse-KTP Address
SP_KTP_CITY SP_KTP_EXP_DATE SP_KTP_ISSUE_DATE SP_KTP_ZIP_CD
Spouse-KTP City Spouse-KTP Expiry Date Spouse-KTP Issue Date Spouse-KTP Kode Pos
SP_NAME
Spouse Name
SP_OFF_COMP_TYPE SP_OTH_INCOME SP_POSIT ION SP_TOTAL_INCOME SP_LIFE_SERVICES TOTAL_EXPENSES KTP_CITY DOB OCC_CD BT _CD COMP_NAME COMP_CD EDU_CD HOME_CD OFOPRYY HMLIFEYY MR_CD
Spouse Office CompanyType Spouse-Other Income Spouse-Position Spouse-Total Income Spouse-Total Services Total Expenses Zip Code Date Of Birth Job T ittle Business Type Company Name Company Type Education Home Status Lama Usaha (year) Length Of Stay (year) Marital
101
105
79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
CUST CU_OFPOS Position CUST CU_SEX Sex CUST CU_OFTOTLIFE Total Services CUST CU_HMADDR1 Corespondence Address1 CUST CU_HMADDR2 Corespondence Address2 CUST CU_HMADDR3 Corespondence Address3 CUST CU_NMFIRST First Name CUST CU_NMLAST Last Name CUST CU_NMMID Mid Name CUST CU_HPNUM Mobile Phone CUST CU_OFADDR1 Office Address1 CUST CU_OFADDR2 Office Address2 CUST CU_OFADDR3 Office Address3 CUST CU_OFCITY Office City CUST CU_OFPHNNUM Office Phone # CUST CU_OFPHAREA Office Phone Area # CUST CU_OFPOSCODE Office Pos Code CUST CU_HMPHNNUM Phone # CUST CU_HMPHNAREA Phone area CUST CU_HMPOSCODE Zip Code CUST CU_SISAINC Sisa Income CUST CU_SPOFINCGRSMM Spouse Gross Income /Month CUST CU_SPOFINCNETYY Spouse Net Income /Annum CUST CU_SPDEPT Spouse-Department Name CUST CU_SPMOBILE Spouse-Mobilephone # CUST CU_SPOFPHNNUM Spouse-Office Phone # CUST CU_SPOFPHNAREA Spouse-Office Phone Area # CUST CU_SPOFLIFEMM Spouse-Year Of Service (month) CUST CU_OFLIFEMM Year Of Service (month)
A A N A A A A A A A A A A A A A A A A A N N N A A A A A A
30 1 10 100 100 100 35 35 35 15 100 100 100 25 15 4 7 15 4 7 17,2 17,2 17,2 30 30 15 4 3 4
OFPOS SEX OFTOTLIFE
Position Sex Total Services
HOME_ADDRESS
Corespondence Address
CUST_NAME
Customer name
MOBILEPHONE_NO
Mobile phone No
OFFICE_ADDRESS
Office Address
OFFICE_CITY OF_PHONE_NO OFFICE_PHONE OF_POST AL_CD
Office City Office Phone No Office Phone area Office Pos Code
HOME_PHONE_NO
Home Phone No
HOME_POSTAL_CD SISA_INCOME SP_INCOME_GRS_MM SP_INCOME_NET_YY SPDEPT SP_MOBILEPHONE
Zip Code Sisa Income Spouse Gross Income /Month Spouse Net Income /Annum Spouse-Department Name Spouse-Mobilephone No
SP_OF_PH0NENO
Spouse-Office Phone No
SP_LIFE_OF_SERVICE_MM Spouse-Year Of Service (month) LIFE_OF_SERVICE_MM Year Of Service (month)
T abel 0-15 Tabel Mapping D_CUST _LOS_DT L
102
106
c. Customer Summary Detail Nama Tabel Warehouse Nama Tabel Sumber Deskripsi Frekuensi Jenis Checksum Field Prediksi Jumlah Record Penyimpanan Data
D_CUST_SUMMARY MANDIRI_CUST_SUMMARY, MANDIRI_SCORING_LIST Tabel ini berisi detil dari rangkuman aplikasi customer Harian Format Full (Insert and Update) Delimiter Character INCOME Primary Key 600.000 Pertambahan Record per Bulan 1 hari pada waktu data tersebut disimpan dalam warehouse
SQL Server NA AP_REGNO 10%
Mapping : No 1 2 4 5 7 8 9 10 11 13 14 15 17 18
Tabel
SOURCE SYSTEM Field
Deskripsi MANDIRI_CUST_SUMMARY AP_REGNO Application # MANDIRI_CUST_SUMMARY CALCULATE_RESULT Calculate Result MANDIRI_CUST_SUMMARY INCOME Income Limit Credit MANDIRI_CUST_SUMMARY LIMIT_CREDIT MANDIRI_CUST_SUMMARY LIMIT_TENOR Limit Tenor Applied Loan Amount MANDIRI_CUST_SUMMARY LOAN_AMOUNT MANDIRI_CUST_SUMMARY LTV Loan to Value Product Code MANDIRI_CUST_SUMMARY PRODUCTID MANDIRI_CUST_SUMMARY COL_VALUE Colateral Value Final Scoring MANDIRI_SCORING_LIST FINAL_SCORE MANDIRI_SCORING_LIST PROCESS_DATE Processing Date Limit Credit MANDIRI_SCORING_LIST LIMIT_CREDIT MANDIRI_SCORING_LIST LIMIT_TENOR Limit Tenor Finish Status MANDIRI_SCORING_LIST FINISH_STATUS
DATA TYPE Tipe Panjang A 20 A 30 N 22,2 N 17,2 A 10 N 17,2 N 17,2 A 8 N 17,2 N 17,2 D 7 N 8 A 5 A 1
ORACLE SYSTEM Field Deskripsi APPLN_NO Application # Calculate Result SCORE INCOME Income SUMMARY_LIMIT_CREDIT Limit Credit SUMMARY_LIMIT_TENOR Limit Tenor SUMMARY_LOAN_AMOUNT Applied Loan Amount LTV Loan to Value Product Code PROD_PRG_CD COLLTRL_VALUE Colateral Value Final Scoring FINAL_APPLN_SCORE SCORING_PROCESS_DATE Processing Date Limit Credit SCORING_LIMIT_CREDIT SCORING_LIMIT_TENOR Limit Tenor SCORING_FINISH_STATUS Finish Status
T abel 0-16 Tabel Mapping D_CUST _SUMMARY JOINER: MANDIRI_SCORING_LIST.AP_REGNO = MANDIRI_CUST_SUMMARY.AP_REGNO
103
107 Keterangan : 1. Tipe Data a. A = varchar2 b. N = number c. D = date (format tanggal disesuaikan dengan kebutuhan, tetapi format standar yang dipakai ‘DD/MM/YY’)
2. Waktu Pengambilan Untuk setiap tabel yang ada di data warehouse akan ditambahkan informasi waktu pengambilan dengan informasi sebagai berikut: a. File Creation Date Adalah field yang berisi tanggal pembuatan data warehouse dari source system. b. Record from Date Adalah field yang berisi tanggal data yang diambil. Sebagai contoh : apabila data yang diambil itu data bulanan, maka isi dari record from date adalah tanggal pada awal bulan tersebut. c. Record to date Adalah field yang berisi tanggal terakhir data diambil. Sebagai contoh, apabila data yang diambil itu data bulanan, maka isi dari record to date adalah tanggal pada akhir bulan tersebut. Tujuan pencatatan waktu pengambilan adalah untuk integritas data. NB: Record to Date dapat berbeda dengan File Creation Date.
108 Sebagai contoh, jika data yang diambil dari source system adalah data tanggal 01/01/2007 sampai 31/01/2007, dan program untuk menjalankan pengambilan data ini adalah tanggal 05/02/2007 maka File creation date
= 05/02/2007
Record from date
= 01/01/2007
Record to date
= 31/01/2007
3. Checksum Checksum ini dibuat dengan tujuan untuk memastikan bahwa data yang dimasukkan ke dalam warehouse sudah sesuai dengan data pada source system. Checksum ini secara sederhana dapat didefinisikan dengan penjumlahan nilainilai yang ada pada checksum field. Checksum field adalah field dari source system yang ditentukan untuk dijadikan acuan. Sebagai contoh, pada tabel D_ACCT_DTL, record dan field yang disediakan sebagai berikut : Record No 1 2 3 4 5
Annual Income 1000 2000 0 -500
Pada contoh di atas, hasil dari checksum adalah 2500. Jika field itu berisi null, maka field itu dianggap 0. Jika field itu berisi nilai - (negatif) maka nilai negatif tersebut juga ikut ditambahkan dengan tidak mengubah nilai negatif itu.
109
3.2.4
Analisis Proses Pembentukan Fact Table Data mart pada sistem yang berjalan hanya menyimpan data posisi,
sehingga dibuatlah sebuah tabel bernama P_ACCT_DTL dengan struktur penyimpanan data mart history menggunakan logika bucket-bucket (block column per bulan) yang menyimpan data bulanan sebanyak 12 bucket. Cara pengisian data ke setiap bucket tiap bulannya adalah : 1. Awalnya, bucket-bucket pada tabel P_ACCT_DTL akan digeser terlebih dahulu dengan data lama yang ada pada bucket pertama, akan digeser ke bucket kedua, bucket kedua ke bucket ketiga dan begitu seterusnya, meskipun terdapat bucket yang belum berisi data.
Jadi setiap data pada
bulan baru yang masuk akan terjadi 12 kali pergeseran data, jika proses penggeseran tersebut belum pernah dijalankan dengan sukses pada bulan tersebut 2. Jika data customer A belum pernah terdapat dalam sistem, maka data tersebut akan di-INSERT ke dalam tabel P_ACCT_DTL pada 1 baris baru di bucket pertama. Jika pada bulan yang sama customer melakukan transaksi, maka data pada bucket bulan tersebut akan di-update. 3. Setiap data pada bulan baru akan selalu berada pada bucket yang pertama. Alasan mengapa data yang terbaru selalu dimasukkan ke dalam bucket pertama adalah karena data yang digunakan untuk menghasilkan analytical report bulanan adalah data yang terbaru sehingga dari sisi scripting, akan lebih mudah apabila data yang terbaru selalu diletakkan pada bucket pertama.
110 Proses pengisian data pada P_ACCT_DTL: Bulan 01
Bucket 1
Atribut Account Mr. A No rek Mr. B No rek Mr. C No rek Mr. D No rek Mr. E No rek
Account Bulan 01
: xxxxx
Rp 6.000.000
: xxxxx
Rp 18.000.000
: xxxxx
Rp 89.000.000
: xxxxx
Rp 24.000.000
: xxxxx
Rp 45.000.000
Bulan 02
Bucket 2
Bucket 3
Dst..
Account Bulan
Account Bulan
Dst…
Account Bulan 01
Account Bulan
Dst…
Account Bulan 01
Dst…
Account bulan 01,dst digeser ke bucket berikutny a, pada saat data baru akan masuk.
Atribut Account Mr. A No rek : xxxxx
Account Bulan 02 Rp 4.000.000
Rp 6.000.000
Rp 15.000.000
Rp 18.000.000
Rp 56.000.000
Rp 89.000.000
Mr. D No rek : xxxxx
Rp 19.000.000
Rp 24.000.000
Mr. E No rek : xxxxx
Rp34.000.000
Rp 45.000.000
Mr. B No rek : xxxxx Mr. C No rek : xxxxx
Bulan 03
Account bulan 01,02,dst digeser ke buc ket berikutny a, pada saat data baru akan masuk.
Atribut Account Mr. A No rek : xxxxx Mr. B No rek : xxxxx Mr. C No rek : xxxxx Mr. D No rek : xxxxx Mr. E No rek : xxxxx
Account Bulan 03 Rp 2.000.000
Account Bulan 02 Rp 4.000.000
Rp 13.000.000
Rp 15.000.000
Rp 45.000.000
Rp 56.000.000
Rp14.000.000
Rp 19.000.000
Rp 25.000.000
Rp34.000.000
Rp 6.000.000 Rp 18.000.000 Rp 89.000.000 Rp 24.000.000 Rp 45.000.000
111
Sehingga hasil shifting data ke P_ACCT_DTL lama pada bulan 12: Atribut Account Mr. A No rek Mr. B No rek Mr. C No rek Mr. D No rek Mr. E No rek
: xxxxx : xxxxx : xxxxx : xxxxx : xxxxx
Account Bulan 12
…
Account Bulan 02
Account Bulan 01
Rp 2.000.000
…
Rp 4.000.000
Rp 6.000.000
Rp 3.000.000
…
Rp 15.000.000
Rp 18.000.000
Rp 13.000.000
…
Rp 56.000.000
Rp 89.000.000
Rp 9.000.000
…
Rp 19.000.000
Rp 24.000.000
Rp 2.000.000
…
Rp34.000.000
Rp 45.000.000
Proses pengisian record-record data pada P_ACCT_DTL menggunakan sebuah PL/SQL procedure yaitu DBP_PRE_PROCESS. Pada procedure DBP_PRE_PROCESS ini dilakukan proses insert record-record baru (jika ada account baru yang melakukan peminjaman) dan proses update record-record (jika nasabah membayar cicilan).
113
Berikut gambar mengenai proses pembentukan P_ACCT_DTL: Source pembentuk P_ACCT_DTL
Procedure DBP_PRE_PROCESS
Proses yang terjadi : SHIFT_RELEASE_AMT();
P_ACCT_DTL
INS_FROM_LOAN () ; UPD_FROM_LOAN() ;
T abel
UPD_FROM_ACCT_DPD () ; CLEANUP_P ROD (); REGION_CLEANUP (); DISBURSEMENTDATE_CLEANUP (); UPD_LOS_APPLN () ; UPD_FROM_HOST_APP LN () ; UPD_FROM_LOAN_CLEANUP (); CLEANACCTREGION ();
Gambar 0-9 Proses Pembentukan P_ACCT_DT L
109
115 Setelah P_ACCT_DTL berisi data, dibentuklah fact table dengan menjalankan procedure yang menggabungkan data-data tersebut dan diolah oleh Business Object untuk membuat analytical report. Setiap fact table yang terbentuk akan menyediakan data untuk pembentukan sebuah analytical report. Berikut daftar dari fact table yang digunakan untuk pembentukan analytical report: 1. VW_M IS_KPI1_ENH Fact table ini menyediakan data untuk pembentukan KPI report. Fact table ini
diisi
oleh
procedure
DBP_M IS_KPI_ENH.
Berikut
pembentukan fact table VW_M IS_KPI1_ENH:
Gambar 0-10 Proses Pembentukan MIS_KPI_ENH
proses
116
2. VW_M IS_AVG_TURNAROUND_TIM E_ENH Fact table ini menyediakan data yang digunakan untuk pembentukan Detail Average Turnaround Time report. Pembentukan data pada fact table ini menggunakan PL/SQL procedure DBP_M IS_AVG_TURNAROUND_ENH.
Proses pembentukan fact table M IS_AVG_TURNAROUND_TIM E_ENH:
Gambar 0-11 Proses Pembentukan MIS_AVG_T URNAROUND_ENH
117
3. M IS_LOAN_LINE_ANLS_ENH Fact table ini menyediakan data yang digunakan untuk pembentukan Loan Line Analysis report. Pembentukan data pada fact table ini menggunakan PL/SQL procedure DBP_M IS_LOAN_LINE_ANLS_ENH. Berikut gambar proses pembentukan fact table M IS_LOAN_LINE_ANLS_ENH:
Gambar 0-12 Proses Pembentukan MIS_LOAN_LINE_ANALYSIS_ENH
118
4. M IS_M ITRA_LOAN_LINE_ANLS_ENH Fact table ini menyediakan data yang digunakan untuk pembentukan Mitra Karya Loan Line Analysis report. Pembentukan data fact table ini menggunakan
procedure
DBP_M IS_MTR_LOAN_LINE_ANLS_ENH.
Berikut proses pembentukan M IS_M ITRA_LOAN_LINE_ANLS_ENH :
Gambar 0-13 Proses Pembentukan MIS_MIT RA_LOAN_LINE_ANLS_ENH
119
5. M IS_PROFIT_LOSS_ALL Fact table ini menyediakan data yang digunakan untuk pembentukan Profit and Loss report. Pembentukan data pada fact table ini menggunakan PL/SQL procedure DBP_M IS_PROFIT_LOSS_ALL. Berikut gambar proses pembentukan fact table M IS_PROFIT_LOSS_ALL:
Gambar 0-14 Proses T erbentuknya MIS_PROFIT_LOSS_ALL
120 3.2.5 Analisis Kebutuhan Data dan Informasi Berdasarkan penelitian yang dilakukan, data dan informasi yang diperlukan dalam kegiatan operasional pada Consumer Loan Group PT.Bank X, adalah: 1. Analytical Report mengenai KPI (Key Performance of Index) Report ini berisi tentang tingkat kinerja Consumer Loan setiap hari. Dimension : Indicator, Product, Product Program, Region, CLBC/CLBO + Jakarta Indicator
Current P revious MTD Date day
Same date, prior month
MTD as of last day prior month
YTD
Avg TA time (AIP -SPPK) Avg TA time (DTBO-SPPK) Net Outstanding Initial Approval Rate Final Approval Rate Number of SP PK SPP K Amount
T abel 0-17 Template KPI Report
Keterangan Dimensi : - Current Date : Hari pada saat pembuatan report tersebut. - Previous Day : Hari sebelum pembuatan report tersebut. - MTD : Month To Date (dari tanggal diawal bulan hingga tanggal pada saat report tersebut dibuat, misalnya hari pembuatan report adalah tanggal 15 M aret 2008, maka M TD nya adalah dari tanggal 1 hingga 15 M aret 2008). - Same date, prior month : Tanggal yang sama pada bulan sebelumnya. - MTD as of last day prior month : Dari tanggal diawal bulan hingga tanggal pada saat report tersebut dibuat satu bulan sebelumnya (misalnya : report dibuat tanggal 15 M aret 2008, maka MTD as of last day prior month adalah tanggal 1 Febuari 2008 – 15 Febuari 2008). - YTD : Year To Date (dari tanggal 1 januari hingga tanggal pada saat report tersebut dibuat pada tahun tersebut, misalnya report dibuat tanggal 15 maret 2008, maka YTD adalah dari tanggal 1 januari 2008 hingga 15 M aret 2008).
121 No
Istilah Avg TA time (AIP-SPPK)
Waktu rata-rata yang dibutuhkan dari proses aplikasi kredit diterima secara prinsip (belum mengalami proses scoring) hingga SPPK (Surat Penawaran Pemberian Kredit) dikeluarkan.
Avg TA time (DTBO-SPPK)
Waktu rata-rata yang dibutuhkan dari saat DTBO (Data To Be Obtained)/semua data nasabah telah terkumpul hingga SPPK (Surat Penawaran Pemberian Kredit) dikeluarkan.
Net Outstanding
Sisa saldo terakhir yang masih harus dibayar oleh nasabah kepada PT.Bank X di luar suku bunga.
Initial Approval Rate
Perbandingan jumlah aplikasi pengajuan kredit yang di-approve (diterima oleh PT.Bank X) awal dari total aplikasi yang masuk.
Final Approval Rate
Perbandingan jumlah aplikasi yang di-approve setelah melewati proses scoring lebih lanjut dari total aplikasi yang masuk.
Number of SPPK
Jumlah SPPK (Surat Penawaran Pangjuan Kredit) yang dikeluarkan hingga saat itu.
SPPK Amount
Jumlah dana yang dicantumkan dalam SPPK (Surat Penawaran Pangajuan Kredit) yang dikeluarkan hingga saat itu.
1
2
3
4
5
6
7
Deskripsi
T abel 0-18 Tabel Istilah KPI Report
122
Gambar 0-15 Star Schema KPI Report
2. Star schema KPI report menggambarkan lama waktu yang dibutuhkan untuk pemrosesan suatu product_program (misalnya : untuk mengajukan kredit KPR membutuhkan waktu berapa lama) serta jumlah dana dan aplikasi yang telah diapproved.
123 2. Analytical Report Mengenai Detailed Averaged Turn Around Time Report ini berisi informasi mengenai waktu rata-rata yang dibutuhkan dari processing aplikasi kredit hingga pencairan dana. Dimension : Product, Region, CLBC/CLBO + Jakarta Activity
Daily Turn Around Time
MTD Turn Around Time
YTD Turn Around Time
AIP-Doc Collection Doc Collection - DTBO Total - Sales DTBO - Appraisal DTBO - Investigation DTBO - Verification DTBO – IDIBI Sub Total - Verif ication Final score and limit setting Final Score - Approval SPP K/Send to loan disbursement Total - Processing Original DTBO - Loan Disbursement Others* Original DTBO - Loan Disbursement New Houses SPP K - Cancel SPP K – Disbursement Others Total - Disbursement SPP K - Disbursement New House G rand Total
T abel 0-19 Template Detailed Averaged Turn Around Time Report
Keterangan : •
Daily Turnaround Time : Jumlah waktu rata-rata (dalam satuan days) yang diperlukan untuk pembuatan report tersebut setiap harinya.
•
MTD Turnaround Time : Jumlah waktu rata-rata (dalam satuan days) yang diperlukan untuk pembuatan report tersebut dari tanggal 1 bulan tersebut hingga tanggal pembuatan report. M isalnya : report dibuat pada tanggal 23 M aret 2008, maka M TD : 1 - 23 M aret 2008
•
YTD Turnaround Time : Jumlah waktu rata-rata (dalam satuan days) yang diperlukan untuk pembuatan report tersebut dari tanggal 1 Januari tahun tersebut hingga tanggal pembuatan report. M isalnya : report dibuat pada tanggal 23 M aret 2008, maka YTD : 1 Januari - 23 M aret 2008
124
No
Istilah Deskripsi
1
AIP-Doc Collection
Waktu yang dibutuhkan dari proses AIP (Approval In Principal) / Scoring awal hingga pengumpulan persyaratan (KTP, KK, slip gaji, dan lain-lain).
2
Doc Collection – DTBO
Waktu yang dibutuhkan dari proses pengumpulan data-data persyaratan hingga DT BO (Data To Be Obtained) / persyaratan lengkap dikumpulkan.
3
DTBO – Appraisal
Waktu yang dibutuhkan dari proses DT BO (Data To Be Obtained) / persyaratan lengkap dikumpulkan hingga scoring jaminan apakah sepadan dengan dana kredit yang dikeluarkan oleh PT .Bank X.
4
DTBO – Investigation
Waktu yang dibutuhkan dari proses DT BO hingga proses investigasi selesai dilakukan. Di mana investigasi yang dimaksud adalah : pengecekan kebenaran data-data nasabah, misalnya : apakah benar alamat rumah dan alamat kantor sesuai dengan yang dicantumkan nasabah.
5
DTBO – Verification
Waktu yang dibutuhkan dari proses DT BO hingga verifikasi. Verifikasi adalah proses pengecekan benarkah gaji nya per-bulan sekian rupiah (cek ke bagian HRD perusahaan tempat klien bekerja).
6
DTBO – IDIBI
Waktu yang diperlukan dari proses DT BO hingga IDIBI (pengecekan status nasabah ke Bank Indonesia, apakah nasabah tersebut masuk dalam daftar black list karena pernah tidak membayar tunggakan kredit lain)
125
7
8
9
Final score and limit setting
Waktu yang dibutuhkan dari Final Scoring hingga menentukan besarnya dana yang akan diberikan kepada nasabah untuk kredit.
Final Score – Approval
Waktu yang dibutuhkan dari proses final scoring (pengecekan mengenai kelayakan seorang nasabah yang akan diberikan dana kredit) hingga proses approval (aplikasi pengajuan kredit nasabah tersebut diterima)
SPPK / Send to loan disbursement
Waktu yang dibutuhkan dari proses dikeluarkannya SPPK hingga pengiriman permohonan pencairan dana ke bagian pencairan dana.
Total – Processing
Jumlah aplikasi pengajuan kredit yang diproses hingga saat itu.
Original DTBO –
Waktu yang dibutuhkan dari proses DTBO hingga pencairan dana kredit selain KPR (Kredit Pemilikan Rumah), misalnya KPM (Kredit Pemilikan M obil), KTA (Kredit Tanpa Agunan).
10
11
Loan Disbursement Others
12
Original DTBO – Loan Disbursement New Houses
Waktu yang dibutuhkan dari proses DTBO hingga pencairan dana untuk kredit KPR dari developer.
13
SPPK -Cancel
Waktu antara dikeluarkannya SPPK hingga di-cancel oleh nasabah.
14
SPPK – Disbursement Others
Waktu dari SPPK hingga pencairan dana untuk kredit selain KPR.
15
Total - Disbursement
Jumlah aplikasi dicairkan.
kredit
yang
telah
126
16
SPPK Disbursement house
– New
Waktu dari SPPK hingga pencairan dana untuk kredit KPR.
T abel 0-20 Tabel Istilah Detailed Averaged Turn Around Time Report
Gambar 0-16 Star Schema Detail Average Turn Around Time
Star schema Detailed Average Turn Around Time report menggambarkan lama waktu yang dibutuhkan untuk pemrosesan suatu product_program (misalnya : untuk mengajukan kredit butuh berapa lama dari pengumpulan persyaratan hingga appraisal)
127
3. Analytical Report mengenai Finance a. Loan Line Analysis Report Report ini beris i informasi dana yang disediakan untuk suatu main company hingga limit yang telah digunakan oleh perusahaan tersebut. Period
: Monthly
Dimension : Product, Branch, Company, Product Program, CLPC + Jakarta Product
Company
Total Guarantor Line - Opening Balance
Realized
Total Amount Disbursed / Released
No Of Application Disbursed
Remaining Line
Actual Remaining Line
T abel 0-21 Template Loan Line Analysis Report No
Istilah
Deskripsi
1
Product
Jenis Kredit yang diambil nasabah : misalnya KPR, Mitrakarya, dan lain-lain.
2
Company
Nama perusahaan yang meminjam dana
3
Total Guarantor Line - Opening Balance
Total limit kredit yang diberikan kepada suatu perusahaan.
4
Realized
Jumlah dana yang telah di-approve untuk dicairkan.
5
Total Amount Disbursed/Released
Jumlah dana yang telah dicairkan
6
No of Applications Disbursed
Jumlah aplikasi yang telah dikeluarkan
7
Remaining Line
Sisa limit dana yang masih dapat diberikan oleh PT.Bank X untuk perusahaan tersebut. Didapat dari T otal limit kredit – limit yang di-approve
8
Actual Remaining Line
Sisa jumlah dana yang masih dapat dicairkan oleh PT .Bank X untuk perusahaan tersebut. Didapat dari Total limit kredit – jumlah dana yang telah dicairkan.
T abel 0-22 Tabel Istilah Loan Line Analysis Report
128
Gambar 0-17 Star Schema Loan Line Analysis Report
Star schema Loan Line Analysis report menggambarkan besarnya pinjaman, sisa kredit yang masih harus dibayarkan, jenis kredit yang diambil oleh suatu perusahaan. Serta perusahaan tersebut mengajukan kredit di cabang PT.Bank X mana.
129
b. Mitrakarya Loan Line Analysis Report Report ini berisi informasi tentang dana yang disediakan untuk pinjaman sebuah main company, cabang perusahaan (Sub Company) hingga limit yang telah digunakan oleh perusahaan tersebut. Period
: Monthly
Dimension : Main Company, Sub Company, DPD, CLBC/CLBO + Jakarta
T abel 0-23 Template Mitrakarya Loan Line Analysis Report No
Istilah
Deskripsi
1
Main Company Name
Nama perusahaan induk
2
Sub Company Name
Nama anak perusahaan
3
Total Guarantor Line – Open Balanced
Total limit kredit (plafon yang diberikan untuk setiap perusahaan)
4
Total Limit Approved
Total limit kredit yang disetujui
5
Total Application Approved
Jumlah aplikasi kredit yang disetujui
6
Remaining Line
Total limit kredit – limit approved
7
Total Amount Disbursed
Jumlah total dana yang telah dicairkan
8
No of Application Disbursed
Jumlah aplikasi kredit yang telah dicairkan dananya.
9
Actual Remaining Line
Total limit kredit – Amount disbursed
10
Kolektibilitas
Status pembayaran kredit, misal kolektibilitas 1 untuk kredit lancar.
11
Baki debit
Sisa tagihan yang harus dibayar
Tabel 0-24 Tabel Istilah Mitrakarya Loan Line Analysis Report
130
Gambar 0-18 Star Schema Mitrakarya Loan Line Analysis Report
Star schema Mitrakarya Loan Line Analysis report menggambarkan besarnya pinjaman dan sisa kredit yang masih harus dibayarkan oleh main company dan sub company yang telah bekerjasama dengan PT.Bank X melalui kredit M itrakarya.
131 c. Profit and Lost Report Report ini mengenai summary laba rugi per-bulan. Report ini berisi informasi mengenai pengeluaran yang digunakan untuk promosi, gaji karyawan, serta pemasukan dari bunga pinjaman dan lain-lain. P & L Report Period
: Monthly
Dimension : Summary
Period
Interest Income Interest Expense Net Interest Income Provision & Commission Insurance Fees Admin Fees Other Fees Net Revenue Expenses Marketing Premises Salaries Other Expenses Allocated Cost Total Operating Expenses PPAP Profit / (Loss)
SEPTEMBER 2008
132 T abel 0-25 Template P & L Report
No
Istilah
Deskripsi
1
Interest Income
Pendapatan dari bunga kredit
2
Interest Expense
Pengeluaran untuk bunga deposito
3
Net Interest Income
Pendapatan bersih dari bunga kredit
4
Insurance Fees
Pendapatan dari biaya asuransi
5
Admin Fees
Pendapatan dari biaya administrasi
6
Other Fees
Pendapatan dari biaya lain-lain
7
Net Revenue
Pendapatan bersih
8
Expenses
Pengeluaran
9
Marketing
Biaya marketing (promosi)
10
Premises
Biaya premises
11
Salaries
Gaji karyawan
12
Other Expenses
Pengeluaran lainnya (selain operational), mis : biaya software,dll
13
Allocated Cost
Dana yang dialokasikan untuk keperluan tertentu
14
Total Operating Expenses
Jumlah pengeluaran untuk kegiatan operasional
15
PPAP
Penyisihan Penghapusan Aktiva Produktif. Untuk setiap kredit yang tidak lancar, PT .Bank X harus menyisihkan dana 10% dari dana kredit tersebut kepada Bank Indonesia.
16
Profit/ (Loss)
Untuk menandakan status keuangan pada bulan tersebut, apakah profit (untung) atau loss (rugi), dan berapa besar keuntungan atau kerugian pada bulan tersebut. JIka bernilai positif berarti keuntungan, sedangkan minus menandakan kerugian.
T abel 0-26 Tabel Istilah Profit and Lost Report
133
Gambar 0-19 Star Schema P & L Report
Star schema P & L report menggambarkan besarnya keuntungan atau kerugian dari semua cabang PT.Bank X, besarnya pendapatan dan keuntungan dari penjualan produk. Semua informasi ini dapat dilihat berdasarkan cabang, jenis produk dan year_month.
134
Berikut merupakan tabel yang menunjukkan kebutuhan informasi yang
Personal Loan Department Automotive Department Mortgage Department Developer / Broker Relationship Department Sales Management Department Jakarta Operation Department Jakarta Disbursement Department Regional Operation Department
X
X
Profit & Loss
Mitra Loan Line Analysis
KPI
User
Loan Line Analysis
Report
Detailed Avg TA
dibutuhkan oleh pihak managerial Consumer Loan Group :
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
T abel 0-27 Matriks Kebutuhan Informasi User
Dari tabel matriks di atas dapat kita lihat bahwa kebutuhan setiap departemen akan report KPI, Detailed Averege Turn Around Time (Detailed Avg TA), Loan Line Analyst. Mitra Loan Line dan Profit & Loss (P & L). Tanda X pada tiap kolom menandakan bahwa departemen tersebut membutuhkan report tersebut.
135
3.2.5
Analisis Permasalahan Yang Dihadapi Berdasarkan analisis dan wawancara yang dilakukan, beberapa masalah
yang dihadapi oleh Consumer Loan Group PT.Bank X, antara lain: 1. Untuk membentuk analytical report dibutuhkan history data sedangkan perancangan data warehouse Consumer Loan PT.Bank X berisi data posisi per-hari. 2. Proses pembentukan fact table dari data mart membutuhkan waktu yang lama yaitu sekitar 5 hingga 7 jam. 3. Untuk menghasilkan analytical report menggunakan Business Object membutuhkan waktu yang lama, sehingga user lebih memilih untuk langsung query ke data mart. Oleh karena itu, user (Consumer Loan Group) harus memiliki keahlian di bidang SQL code. 3.2.6
Usulan Pemecahan Masalah Berdasarkan masalah yang dihadapi Consumer Loan Group PT.Bank X,
maka pemecahan masalah yang diusulkan adalah : 1. M elakukan SQL tuning pada procedure-procedure yang belum optimal. M isalnya : mengganti where
clause
menjadi join
clause untuk
menggabungkan beberapa tabel, menggunakan ROWID untuk mengakses data, menggunakan temp table untuk menggabungkan beberapa statement yang dipergunakan berulang kali. 2. M embuat partisi pada tabel yang mengandung lebih dari 2 GB data. 3. M emberi index terhadap data yang sering diakses.
136
3.3 Perancangan Application Tuning Plan Application Tuning Plan Pengolahan Data Mart Consumer Loan yang kami rancang adalah SQL tuning procedure yang digunakan untuk mempopulasi data pada data mart history, indexing field-field yang sering diakses, dan partitioning tabel yang mengandung data lebih dari 2 GB . 3.3.1 SQL Tuning 3.3.1.1 Restrukturisasi P_ACCT_DTL_ENH Pada perancangan sistem yang lama, tabel P_ACCT_DTL menggunakan sistem bucket-bucket. Proses tersebut dapat menyebabkan penurunan kinerja dari database dan juga pemborosan resource sehingga kami merancang suatu sistem baru untuk menyimpan data mart. Pada perancangan sistem yang baru, kami tidak menggunakan 12 bucket untuk menyimpan history data setiap bulannya. Akan tetapi, hanya menggunakan 1 bucket dalam partisi bulanan untuk menyimpan history data. Struktur tabel P_ACCT_DTL_ENH : Atribut Account Mr. A No rek : xxxxx Mr. B No rek : xxxxx Mr. A No rek : xxxxx Mr. B No rek : xxxxx Mr. A No rek : xxxxx Mr. B No rek : xxxxx Mr. A No rek : xxxxx Mr. B No rek : xxxxx
Account Status
Month_idx
Rp 6.000.000
01
Rp 18.000.000
01
Rp 4.000.000
02
Rp 15.000.000
02
…
…
…
…
Rp 6.000.000
12
Rp 6.000.000
12
Data selalu di-insert ke row baru, berbeda dengan P_ACCT_DTL lama yang selalu menggeser dat a lama ke bucket sebelumnya jika ada data baru yang akan masuk.
137 Keuntungan yang diperoleh dari perubahan ini adalah : 1. M empercepat pengaksesan data pada tabel P_ACCT_DTL_ENH yang berisi history data karena adanya range partitioning berdasarkan month_idx dan subpartition berupa hash partitioning
berdasarkan
acct_no. 2. M enghilangkan proses update yang menggunakan logika perpindahan data ke bucket berikutnya setiap bulan. Jadi, perubahan ini membuat procedure SHIFT_ P_ACCT_DTL dapat dihilangkan.
Berikut procedure shifting P_ACCT_DTL code yang kami hilangkan : PROCEDURE SHIFT_P_ACCT_DTL( i_cnt_cd IN VARCHAR2, i_indate IN DATE, o_status OUT VARCHAR2, o_severity OUT VARCHAR2, o_remarks OUT VARCHAR2 ) IS v_process_id v_tot v_count v_start_time v_seq_no v_rec_commit v_ctr_loop v_mod_cnt v_initial_cnt v_final_cnt v_excep
PLS_INTEGER PLS_INTEGER PLS_INTEGER DATE PLS_INTEGER PLS_INTEGER PLS_INTEGER PLS_INTEGER PLS_INTEGER PLS_INTEGER EXCEPTION ;
:=Bmconstant.P_ACCT_DTL_SHIFTING; := 0 ; := 0; := SYSDATE ; := 0 ; := 50000 ; := 0; := 0; := 1; := v_rec_commit;
TYPE ty_rowid IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER; obj_rowid ty_rowid; BEGIN o_status := Bmconstant.SCHD_STATUS_SUCC; o_severity := Bmconstant.SCHD_SEVRTY_NIL; o_remarks := 'SUCCESS'; Bmglobal.Process_name := 'SHIFT_P_ACCOUNT_DTL' ;
138
SELECT s_process_no.NEXTVAL INTO v_seq_no FROM DUAL; SELECT INTO FROM WHERE AND AND
COUNT(*) v_count PROCESS_STATUS process_id TRUNC(process_date,'MONTH') PROCESS_STATUS
= v_process_id = TRUNC(i_indate,'MONTH') = Bmconstant.SUCCESS;
IF (v_count > 0) THEN o_remarks := 'Shifting process already run for this month : ' || i_indate ; RAISE v_excep ; END IF ; Bmglobal.proclog('Shifting of P_ACCT_DTL Started for the date : ' || i_indate); SELECT ROWID BULK COLLECT INTO obj_rowid FROM P_ACCT_DTL; v_ctr_loop := TRUNC(obj_rowid.COUNT/v_rec_commit); v_mod_cnt := MOD(obj_rowid.COUNT,v_rec_commit); Bmglobal.Proclog('Total Records to be processed for Shifting : ' || obj_rowid.COUNT); IF v_mod_cnt > 0 THEN v_ctr_loop := v_ctr_loop + 1; END IF; IF v_final_cnt > obj_rowid.COUNT THEN v_final_cnt := obj_rowid.COUNT; END IF; FOR ctr_cnt IN 1..v_ctr_loop LOOP FORALL ctr IN v_initial_cnt..v_final_cnt UPDATE P_ACCT_DTL a SET acct_status12 = acct_status11, acct_sub_status12 = acct_sub_status11, ltd_release_amt12 = ltd_release_amt11, curr_bal12 = curr_bal11, inst_amt12 = inst_amt11, chargeoff_date12 = chargeoff_date11, payment_amt12 = payment_amt11, int_rate12 = int_rate11, amt_dpd12 = amt_dpd11, int_dpd12 = int_dpd11, ytd_int_amt12 = ytd_int_amt11, ytd_prin_amt12 = ytd_prin_amt11,
139
acct_status11 acct_sub_status11 ltd_release_amt11 curr_bal11 inst_amt11 chargeoff_date11 payment_amt11 int_rate11 amt_dpd11 int_dpd11 ytd_int_amt11 ytd_prin_amt11
= = = = = = = = = = = =
acct_status10, acct_sub_status10, ltd_release_amt10, curr_bal10, inst_amt10, chargeoff_date10, payment_amt10, int_rate10, amt_dpd10, int_dpd10, ytd_int_amt10, ytd_prin_amt10,
acct_status10 acct_sub_status10 ltd_release_amt10 curr_bal10 inst_amt10 chargeoff_date10 payment_amt10 int_rate10 amt_dpd10 int_dpd10 ytd_int_amt10 ytd_prin_amt10
= = = = = = = = = = = =
acct_status9, acct_sub_status9, ltd_release_amt9, curr_bal9, inst_amt9, chargeoff_date9, payment_amt9, int_rate9, amt_dpd9, int_dpd9, ytd_int_amt9, ytd_prin_amt9,
acct_status9 acct_sub_status9 ltd_release_amt9 curr_bal9 inst_amt9 chargeoff_date9 payment_amt9 int_rate9 amt_dpd9 int_dpd9 ytd_int_amt9 ytd_prin_amt9
= = = = = = = = = = = =
acct_status8, acct_sub_status8, ltd_release_amt8, curr_bal8, inst_amt8, chargeoff_date8, payment_amt8, int_rate8, amt_dpd8, int_dpd8, ytd_int_amt8, ytd_prin_amt8,
acct_status8 acct_sub_status8 ltd_release_amt8 curr_bal8 inst_amt8 chargeoff_date8 payment_amt8 int_rate8 amt_dpd8 int_dpd8 ytd_int_amt8 ytd_prin_amt8
= = = = = = = = = = = =
acct_status7, acct_sub_status7, ltd_release_amt7, curr_bal7, inst_amt7, chargeoff_date7, payment_amt7, int_rate7, amt_dpd7, int_dpd7, ytd_int_amt7, ytd_prin_amt7,
140
acct_status7 acct_sub_status7 ltd_release_amt7 curr_bal7 inst_amt7 chargeoff_date7 payment_amt7 int_rate7 amt_dpd7 int_dpd7 ytd_int_amt7 ytd_prin_amt7
= = = = = = = = = = = =
acct_status6, acct_sub_status6, ltd_release_amt6, curr_bal6, inst_amt6, chargeoff_date6, payment_amt6, int_rate6, amt_dpd6, int_dpd6, ytd_int_amt6, ytd_prin_amt6,
acct_status6 acct_sub_status6 ltd_release_amt6 curr_bal6 inst_amt6 chargeoff_date6 payment_amt6 int_rate6 amt_dpd6 int_dpd6 ytd_int_amt6 ytd_prin_amt6
= = = = = = = = = = = =
acct_status5, acct_sub_status5, ltd_release_amt5, curr_bal5, inst_amt5, chargeoff_date5, payment_amt5, int_rate5, amt_dpd5, int_dpd5, ytd_int_amt5, ytd_prin_amt5,
acct_status5 acct_sub_status5 ltd_release_amt5 curr_bal5 inst_amt5 chargeoff_date5 payment_amt5 int_rate5 amt_dpd5 int_dpd5 ytd_int_amt5 ytd_prin_amt5
= = = = = = = = = = = =
acct_status4, acct_sub_status4, ltd_release_amt4, curr_bal4, inst_amt4, chargeoff_date4, payment_amt4, int_rate4, amt_dpd4, int_dpd4, ytd_int_amt4, ytd_prin_amt4,
acct_status4 acct_sub_status4 ltd_release_amt4 curr_bal4 inst_amt4 chargeoff_date4 payment_amt4 int_rate4 amt_dpd4 int_dpd4 ytd_int_amt4 ytd_prin_amt4
= = = = = = = = = = = =
acct_status3, acct_sub_status3, ltd_release_amt3, curr_bal3, inst_amt3, chargeoff_date3, payment_amt3, int_rate3, amt_dpd3, int_dpd3, ytd_int_amt3, ytd_prin_amt3,
141
acct_status3 acct_sub_status3 ltd_release_amt3 curr_bal3 inst_amt3 chargeoff_date3 payment_amt3 int_rate3 amt_dpd3 int_dpd3 ytd_int_amt3 ytd_prin_amt3
= = = = = = = = = = = =
acct_status2, acct_sub_status2, ltd_release_amt2, curr_bal2, inst_amt2, chargeoff_date2, payment_amt2, int_rate2, amt_dpd2, int_dpd2, ytd_int_amt2, ytd_prin_amt2,
acct_status2 acct_sub_status2 ltd_release_amt2 curr_bal2 inst_amt2 chargeoff_date2 payment_amt2 int_rate2 amt_dpd2 int_dpd2 ytd_int_amt2 ytd_prin_amt2
= = = = = = = = = = = =
acct_status1, acct_sub_status1, ltd_release_amt1, curr_bal1, inst_amt1, chargeoff_date1, payment_amt1, int_rate1, amt_dpd1, int_dpd1, ytd_int_amt1, ytd_prin_amt1,
acct_status1 acct_sub_status1 ltd_release_amt1 curr_bal1 inst_amt1 chargeoff_date1 payment_amt1 int_rate1 amt_dpd1 int_dpd1 ytd_int_amt1 ytd_prin_amt1 WHERE ROWID
= = = = = = = = = = = = =
acct_status1, acct_sub_status1, ltd_release_amt1, 0, inst_amt1, chargeoff_date1, 0, int_rate1, amt_dpd1, int_dpd1, ytd_int_amt1, ytd_prin_amt1 obj_rowid(ctr);
Bmglobal.Proclog('Processed ' || v_final_cnt || ' Records '); v_initial_cnt := v_final_cnt + 1; v_final_cnt := v_final_cnt + v_rec_commit; v_tot := v_tot + SQL%ROWCOUNT; IF v_final_cnt > obj_rowid.COUNT THEN v_final_cnt := obj_rowid.COUNT; END IF; COMMIT; END LOOP;
142
Bmglobal.proclog('Shifting of P_ACCT_DTL Completed Successfully'); Bmglobal.proclog('Rebuilding P_ACCT_DTL Started'); BEGIN EXECUTE IMMEDIATE 'ALTER INDEX PK_P_ACCT_DTL REBUILD PCTFREE 0'; EXCEPTION WHEN OTHERS THEN NULL; END; Bmglobal.proclog('Rebuilding P_ACCT_DTL Completed'); Bmglobal.proclog('Analyzing P_ACCT_DTL Started'); BEGIN EXECUTE IMMEDIATE 'ANALYZE TABLE P_ACCT_DTL ESTIMATE STATISTICS'; EXCEPTION WHEN OTHERS THEN NULL; END; Bmglobal.proclog('Analyzing P_ACCT_DTL Completed'); Bmglobal.Insert_Proc_Status(v_process_id, 5, Bmconstant.SUCCESS, 0, v_tot, o_remarks, v_seq_no, i_indate, NULL, v_start_time, SYSDATE); COMMIT; EXCEPTION WHEN v_excep THEN o_status := Bmconstant.SCHD_STATUS_SUCC; o_severity := Bmconstant.SCHD_SEVRTY_NIL; ROLLBACK;
Bmglobal.Insert_Proc_Status( v_process_id, 5, Bmconstant.SUCCESS, 0, v_tot, o_remarks, v_seq_no,i_indate, NULL, v_start_time, SYSDATE); Bmglobal.proclog('Shifting of P_ACCT_DTL already run for the month and will not run for the date : ' || i_indate); WHEN OTHERS THEN o_status := Bmconstant.SCHD_STATUS_FAIL; o_severity := Bmconstant.SCHD_SEVRTY_HIGH; o_remarks := TO_CHAR(SQLCODE) || SQLERRM; ROLLBACK; Bmglobal.Insert_Proc_Status(v_process_id, 5, Bmconstant.FAIL, 0, v_tot, o_remarks, v_seq_no,i_indate, NULL, v_start_time, SYSDATE); Bmglobal.proclog('Shifting of P_ACCT_DTL Failed for the date : ' || i_indate); Bmglobal.errorlog(SQLCODE,' '); END shift_p_acct_dtl;
*) script berwarna merah, merupakan code yang bertugas memindahkan data ke bucket -bucket berikutnya
144
Berikut perbandingan proses pembentukan P_ACCT_DTL yang lama dengan P_ACCT_DTL_ENH (P_ACCT_DTL yang baru) : Source pembentuk P_ACCT_DTL
Procedure DBP_PRE_PROCESS
Proses yang terjadi : SHIFT_RELEASE_AMT();
P_ACCT_DTL
INS_FROM_LOAN () ;
T abel
UPD_FROM_LOAN() ; UPD_FROM_ACCT_DPD () ; CLEANUP_PROD (); REGION_CLEANUP (); DISB URSEMENTDATE_CLEANUP(); UPD_LOS_APPLN () ; UPD_FROM_HOST_APP LN () ; UPD_FROM_LOAN_CLEANUP(); CLEANACCTREGION ();
*)
P roses berwarna merah merupakan proses yang dihilangkan dari procedure DBP_P RE_P ROCESS lama
Gambar 0-20 Proses Pembentukan P_ACCT_DT L Yang Lama
138
145
Source pembentuk P_ACCT_DTL_ ENH
Procedure DBP_PRE_PROCESS
Proses yang terjadi : SHIFT_RELEASE_AMT();
P_ACCT_DTL_ENH
INS_FROM_LOAN (); UPD_FROM_ACCT_DPD () ;
T abel
REGION_CLEANUP (); UPD_LOS_APPLN () ; REFRESH_TEMP_APPLN_DTL_ENH UPD_FROM_HOST_APP LN () ; CLEANACCTREGION ();
*)
P roses berwarna merah merupakan proses tambahan pada procedure DBP_P RE_P ROCESS baru
Gambar 0-21 Proses Pembentukan P_ACCT _DTL_ENH (P_ACCT_DTL Baru)
139
147 3.3.1.2 Where vs Join clause Setelah menganalisis SQL code yang digunakan dalam procedureprocedure yang digunakan pada sistem yang sedang berjalan, kami menemukan beberapa code yang masih dapat dioptimasi, salah satunya adalah untuk menggabungkan beberapa tabel, procedure lama masih menggunakan where clause seperti yang tampak pada kotak dibawah ini : SELECT COUNT (*) FROM D_APPLN_LOS_DTL A, REF_BRANCH B, REF_HUB C WHERE
A.BRANCH_CD
= B.BRANCH_CD
AND
B.HUB_CD
= C.HUB_CD;
Untuk mengoptimasi code tersebut, kami mengganti code yang menggabungkan beberapa tabel tersebut dengan menggunakan join clause, seperti yang tampak pada kotak dibawah ini :
SELECT COUNT(*) FROM
D_APPLN_LOS_DTL A JOIN REF_BRANCH B
ON
A.BRANCH_CD
ON
B.HUB_CD
Berikut
= B.BRANCH_CD JOIN REF_HUB C = C.HUB_CD;
perbandingan
proses
penggabungan
data
dengan
menggunakan where clause biasa dengan menggunakan join clause :
148
Dengan Where Clause Select a.dept_id, name, salary,a. job_id, dept_name, job_title from employees a, department b, jobs c Where a.dept_id = b.dept_id and a.job_id = c.job_id and job_title not like ‘%Admin%’; and dept_name = ‘Finance’ and salary > 5000 De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
table employee
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
table department
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
table jobs
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
Proses terus berulang untuk setiap data employees, sehingga hasil penggabungannya :
149
Data dari tabel Employees
De pt_id
10
30
40
Name Salary
Joe
Lina
Anna
2000
6000
3500
Job_id
Data dari tabel Department
Data dari tabel Jobs
De pt_id
De pt_name
10
Finance
20
IT
30
HRD
40
Marketing
10
Finance
20
IT
30
HRD
40
Marketing
10
Finance
20
IT
30
HRD
40
Marketing
Job_id J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04
J04
J01
J03
Job_title Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk
150
De pt_id
10
20
Name Salary
Nur
Ben
4500
7500
Job_id
De pt_id
De pt_name
10
Finance
20
IT
30
HRD
40
Marketing
10
Finance
20
IT
30
HRD
40
Marketing
J01
J02
Job_id J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04
Job_title Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk
Setelah proses penggabungan ketiga tabel selesai, diperoleh 80 baris (5 baris tabel employees x 4 baris tabel department x 4 baris tabel jobs). Langkah selanjutnya adalah filter data menurut kondisi yang telah ditentukan code : kondisi yang pertama adalah where a.dept_id = b.dept_id, maka hasil nya adalah :
151
De pt_id 10
Name Salary Joe
2000
Job_id
De pt_id 10
De pt_name
J04
30
Finance 30
Lina
6000
J01
40
HRD 40
Anna
3500
J03
30
Marketing 30
Nur
4900
J01
10
HRD 10
Ben
7500
J03
Finance
Job_id J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04
Job_title Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk
Setelah proses filter pertama selesai, langkah selanjutnya adalah filter data menurut where a.job_id = c.job_id, maka hasil nya adalah :
De pt_id 10 30
Name Salary Joe 2000 6000 Lina
Job_id J04
De pt_id 10
De pt_name Finance
Job_id J04
Job_title Clerk
40
Anna
3500
J01 J03
30
J01
Admin
40
HRD Marketing
J03
Sales
30
Nur
4900
J01
30
HRD
J01
Admin
10
Ben
7500
J02
10
Finance
J02
Manager
Selanjutnya, karena pada select statement hanya meminta a.dept_id dan a.job_id, maka dept_id dan job_id yang dimunculkan hanya satu.
De pt_id 10 30 40 30
Name Salary Joe 2000 Lina 6000 Anna 3500 Nur 4900
Job_id J04 J01 J03 J01
De pt_name Finance HRD Marketing HRD
Job_title Clerk Admin Sales Admin
152 10
Ben
7500
J02
Finance
Manager
Selanjutnya adalah melakukan filter berdasarkan Job_title not like ‘%Admin%’, jika true diberi tanda (✔), jika tidak akan diberi tanda (✖ ). De pt_id 10 30 40 30 10
Name Salary Joe 2000 Lina 6000 Anna 3500 Nur 4900 Ben 7500
Job_id J04 J01 J03 J01 J02
De pt_name Finance HRD Marketing HRD Finance
Job_title Cle rk Admin Sales Admin Manage r
✔ ✖ ✔ ✖ ✔
Selanjutnya adalah melakukan filter data berdasarkan Dept_name = ‘Finance’, jika true diberi tanda (✔), jika tidak akan diberi tanda (✖ ).
De pt_id 10 40 10
Name Joe Anna Ben
Salary 2000 3500 7500
Job_id J04 J03 J02
De pt_name Finance Marketing Finance
Job_title Clerk Sales Manager
✔ ✖ ✔
Langkah berikutnya adalah melakukan filter data berdasarkan S alary > 5000, jika true akan diberi tanda (✔) dan jika tidak akan diberi tanda (✖ ). De pt_id 10 10
Name Salary Joe 2000 Ben 7500
Job_id J04 J02
De pt_name Finance Finance
Job_title Clerk Manager
Setelah semua filter terpenuhi, didapat data yang dicari : De pt_id 10
Name Ben
Salary 7500
Job_id J02
De pt_name Finance
Job_title Manager
✖ ✔
153
Dengan Join Clause Select a.dept_id, name, salary, a.job_id, dept_name, job_title from employees a join department b on a.dept_id = b.dept_id join jobs c on a.job_id = c.job_id where job_title not like ‘%Admin%’; and dept_name = ‘Finance’ and salary > 5000; De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
table employee
De pt_name Finance IT HRD Marketing
table department
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
table jobs
Penggabungan tabel menggunakan join clause sedikit berbeda dengan where clause pada proses penggabungannya. Jika dengan where clause kita langsung menggabungkan semua tabel sekaligus, dengan join clause kita akan menggabungkan secara bertahap per-dua tabel. Hal ini akan mempercepat proses karena mengurangi cross product yang dihasilkan, berikut prosesnya :
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
154
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
De pt_id
Name
Salary
Job_id
10
Joe
2000
J04
20
Lina
6000
J01
40
Anna
3500
J03
30
Nur
4900
J01
10
Ben
7500
J02
De pt_id 10 20 30 40 10 20 30 40 10 20 30 40 10 20 30 40 10 20 30 40
De pt_name Finance IT HRD Marketing Finance IT HRD Marketing Finance IT HRD Marketing Finance IT HRD Marketing Finance IT HRD Marketing
Kemudian filter data berdasarkan : on a.dept_id = b.dept_id De pt_id 10
Salary 2000 6000 3500
Job_id J04 J01 J03
De pt_id 10
20 40
Name Joe Lina Anna
20 40
De pt_name Finance IT Marketing
30 10
Nur Ben
4900 7500
J01 J02
30 10
HRD Finance
155 Karena pada select statement hanya meminta a.dept_id maka kita dapat menghilangkan salah satunya, setelah itu gabungkan dengan tabel ketiga yaitu tabel Jobs De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Job_id J04 J01 J03 J01 J02
De pt_name Finance IT Marketing HRD Finance
JOIN
Job_id J01 J02 J03 J04
Tabel setelah menghilangkan duplikat field
Job_title Admin Manager Sales Clerk
Tabel Jobs
De pt_id
Name
Salary
Job_id
De pt_name
10
Joe
2000
J04
Finance
20
Lina
6000
J01
IT
40
Anna
3500
J03
Marketing
30
Nur
4900
J01
HRD
10
Ben
7500
J02
Finance
Job_id J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04 J01 J02 J03 J04
Job_title Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk Admin Manager Sales Clerk
Langkah selanjutnya adalah filter data menurut where a.job_id = c.job_id De pt_id 10 30 40
Name Joe Lina Anna
Salary 2000 6000
Job_id J04
De pt_name Finance
3500
J01 J03
HRD Marketing
30 10
Nur Ben
4900 7500
J01 J02
HRD Finance
Job_id J04 J01 J03
Job_title Clerk Admin Sales
J01 J02
Admin Manager
156 Karena pada select statement hanya ada a.job_id , kita dapat menghilangkan salah satunya. De pt_id 10 30 40 30 10
Name Salary Joe 2000 Lina 6000 Anna 3500 Nur 4900 Ben 7500
Job_id J04 J01 J03 J01 J02
De pt_name Finance HRD Marketing HRD Finance
Job_title Clerk Admin Sales Admin Manager
Selanjutnya adalah melakukan filter data berdasarkan Job_title not like ‘%Admin%’, jika true diberi tanda (✔), jika tidak diberi tanda (✖ ). De pt_id 10 30 40 30 10
Name Salary Joe 2000 Lina 6000 Anna 3500 Nur 4900 Ben 7500
Job_id J04 J01 J03 J01 J02
De pt_name Finance HRD Marketing HRD Finance
Job_title Cle rk Admin Sales Admin Manage r
✔ ✖ ✔ ✖ ✔
Selanjutnya adalah melakukan filter data berdasarkan Dept_name = ‘Finance’, jika true diberi tanda (✔), jika tidak akan diberi tanda (✖ ). De pt_id 10 40 10
Name Salary Joe 2000 Anna 3500 Ben 7500
Job_id J04 J03 J02
De pt_name Finance Marketing Finance
Job_title Clerk Sales Manager
✔ ✖ ✔
Selanjutnya adalah melakukan filter data berdasarkan S alary > 5000, sehingga jika true diberi tanda (✔) dan jika tidak akan diberi tanda (✖ ). De pt_id 10 10
Name Salary Joe 2000 Ben 7500
Job_id J04 J02
De pt_name Finance Finance
Job_title Clerk Manager
Setelah semua filter terpenuhi, diperoleh data yang dicari : De pt_id 10
Name Ben
Salary 7500
Job_id J02
De pt_name Finance
Job_title Manager
✖ ✔
157 Dapat terlihat dari kedua proses penggabungan dengan menggunakan dua metode yang berbeda, penggabungan menggunakan where clause memerlukan waktu yang lebih lama dibandingkan dengan code yang menggunakan join clause. Secara garis besar perbedaannya ada pada : ¾ Pada Where Clause De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Tabel Employees (5 baris)
Job_id J04 J01 J03 J01 J02
x
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
Tabel Department (4 baris) x
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
Tabel Jobs (4 baris)
80 baris ¾ Pada Join Clause De pt_id 10 20 40 30 10
Name Joe Lina Anna Nur Ben
Salary 2000 6000 3500 4900 7500
Tabel Employees (5 baris)
Job_id J04 J01 J03 J01 J02
x
De pt_id 10 20 30 40
De pt_name Finance IT HRD Marketing
Tabel Department (4 baris)
Job_id J01 J02 J03 J04
Job_title Admin Manager Sales Clerk
Tabel Jobs
Setelah filter berdasarkan primary key (5 baris)
x
20 baris
(4
baris)
158 3.3.1.3 Join Multiple Table Dalam script yang kompleks, seringkali ditemukan penggabungan beberapa tabel. Penggabungan beberapa tabel sekaligus ini dapat menurunkan performance. Setelah kami teliti terdapat beberapa jenis penggabungan tabel yang sering diimplementasikan. Join Multiple Table in one statement FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET (SALES_EXEC_ID, APPLIED_LOAN_AMT, SPPK_LOAN_AMT, PRG_CODE, OCC_CD, EMPLOYMENT_TYPE, REGION_CD, BRANCH_CD, HUB_CD, DEVELOPER_CD, CHANNEL_CD, POSTAL_CD, DOB, TOTAL_INCOME, FINAL_APPLN_SCORE, COMPANY_CD, COMBRANCH_CODE ) = ( SELECT NVL(DALD.SALES_EXEC_ID,'99'), NVL(DAHD.APPLIED_AMT,0), NVL(DAHD.APPROVED_AMT,0), NVL(DALD.PRG_CODE,'99'), NVL(DCLD.EMPLOYMENT_TYPE,'99'), NVL(DALD.REGIONAL_ID,'99'), NVL(DALD.BRANCH_CD,'99'), NVL(RB.HUB_CD,'99'), NVL(ACCD.DEVELOPER_CD,'99'), NVL(DALD.CHANNEL_CODE,'99'), NVL(DCAD.POSTAL_CD,'99'), DCLD.DOB, NVL(DCLD.TOTAL_INCOME,0), DCS.FINAL_APPLN_SCORE, NVL(DALD.COMPANY_CD,'99'), NVL(DALD.COMBRANCH_CODE,'99') FROM D_APPLN_LOS_DTL DALD LEFT OUTER JOIN D_APPLN_HOST_DTL DAHD ON DALD.APPLN_NO = DAHD.LOS_APPLN_NO LEFT OUTER JOIN D_CUST_LOS_DTL DCLD ON DALD.CUST_NO = DCLD.CUST_NO LEFT OUTER JOIN D_ACCOUNT_DTL ACCD ON DALD.APPLN_NO = ACCD.LOS_APPLN_NO LEFT OUTER JOIN D_CUST_ADDR_DTL DCAD ON DALD.CUST_NO = DCAD.CUST_NO LEFT OUTER JOIN D_CUST_SUMMARY DCS ON DALD.APPLN_NO = DCS.APPLN_NO LEFT OUTER JOIN REF_BRANCH RB ON DALD.BRANCH_CD = RB.BRANCH_CD WHERE DAHD.ROWID = OBJ_DROWID(CTR)) WHERE ROWID = OBJ_PROWID(CTR); FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET (SALES_EXEC_ID, APPLIED_LOAN_AMT, SPPK_LOAN_AMT, OCC_CD, EMPLOYMENT_TYPE, BRANCH_CD, HUB_CD, DEVELOPER_CD, CHANNEL_CD, POSTAL_CD, DOB, TOTAL_INCOME, FINAL_APPLN_SCORE, COMPANY_CD) = ( SELECT NVL(DALD.SALES_EXEC_ID,'99'), NVL(DAHD.APPLIED_AMT,0), NVL(DAHD.APPROVED_AMT,0), NVL(DCLD.OCC_CD,'99'), NVL(DCLD.EMPLOYMENT_TYPE,'99'), NVL(DALD.BRANCH_CD,'99'), NVL(RB.HUB_CD,'99'), NVL(ACCD.DEVELOPER_CD,'99'), NVL(DALD.CHANNEL_CODE,'99'),NVL(DCAD.POSTAL_CD,'99'), DCLD.DOB, NVL(DCLD.TOTAL_INCOME,0), DCS.FINAL_APPLN_SCORE, NVL(DALD.COMPANY_CD,'99') FROM D_APPLN_LOS_DTL DALD LEFT OUTER JOIN D_APPLN_HOST_DTL DAHD ON DALD.APPLN_NO = DAHD.LOS_APPLN_NO LEFT OUTER JOIN D_CUST_LOS_DTL DCLD ON DALD.CUST_NO = DCLD.CUST_NO LEFT OUTER JOIN D_ACCOUNT_DTL ACCD ON DALD.APPLN_NO = ACCD.LOS_APPLN_NO LEFT OUTER JOIN D_CUST_ADDR_DTL DCAD ON DALD.CUST_NO = DCAD.CUST_NO LEFT OUTER JOIN D_CUST_SUMMARY DCS ON DALD.APPLN_NO = DCS.APPLN_NO LEFT OUTER JOIN REF_BRANCH RB ON DALD.BRANCH_CD = RB.BRANCH_CD WHERE DAHD.ROWID = OBJ_LDROWID(CTR)) WHERE ROWID = OBJ_LPROWID(CTR);
Dari script dapat dilihat bahwa terjadi penggabungan beberapa tabel dalam satu statement, hal ini akan memperlambat performa.
159
Join Multiple Table using temp table Dari contoh Join Multiple table in one statement di atas, dapat kita lihat bahwa terdapat beberapa code yang sama, hal ini dapat kita atasi dengan menyimpan code yang sama tersebut ke dalam satu temp table, kemudian baru dipanggil dari temp table tersebut.
CREATE TABLE TEMP_APPLN_DTL_ENH AS SELECT DISTINCT DAHD.HOST_APPLN_NO, DAHD.LOS_APPLN_NO, DAHD.FACILITY_CD, DAHD.FACILITY_SEQ, DAHD.RECORD_TO_DATE, NVL(DAHD.APPLIED_AMT,0) APPLIED_AMT, NVL(DAHD.APPROVED_AMT,0) APPROVED_AMT, NVL(DAD.DEVELOPER_CD,'99') DEVELOPER_CD, NVL(DCAD.POSTAL_CD,'99') POSTAL_CD, NVL(DALD.SALES_EXEC_ID,'99') SALES_EXEC_ID, NVL(DALD.PRG_CODE,'99') PRG_CODE, NVL(DALD.REGIONAL_ID,'99') REGIONAL_ID, NVL(DALD.BRANCH_CD,'99') BRANCH_CD, NVL(DALD.CHANNEL_CODE,'99') CHANNEL_CODE, NVL(DALD.COMPANY_CD,'99') COMPANY_CD, NVL(DALD.COMBRANCH_CODE,'99') COMBRANCH_CODE, NVL(DCLD.OCC_CD,'99') OCC_CD, NVL(DCLD.EMPLOYMENT_TYPE,'99') EMPLOYMENT_TYPE, DCLD.DOB, NVL(DCLD.TOTAL_INCOME,0) TOTAL_INCOME,DCS.FINAL_APPLN_SCORE, NVL(RB.HUB_CD,'99') HUB_CD FROM D_APPLN_HOST_DTL DAHD JOIN D_ACCOUNT_DTL DAD ON DAHD.FACILITY_CD NOT LIKE 'P%' AND DAHD.HOST_APPLN_NO = DAD.HOST_APPLN_NO AND DAHD.FACILITY_CD = DAD.FACILITY_CD AND DAHD.FACILITY_SEQ = DAD.FACILITY_SEQ JOIN D_CUST_ADDR_DTL DCAD ON DAD.CIF_NO = DCAD.CUST_NO AND DCAD.ADDR_SEQ_NO = ( SELECT MAX(B.ADDR_SEQ_NO) FROM D_CUST_ADDR_DTL B WHERE B.CUST_NO = DCAD.CUST_NO ) LEFT OUTER JOIN D_APPLN_LOS_DTL DALD ON DAD.LOS_APPLN_NO = DALD.APPLN_NO LEFT OUTER JOIN D_CUST_LOS_DTL DCLD ON DALD.CUST_NO = DCLD.CUST_NO LEFT OUTER JOIN D_CUST_SUMMARY DCS ON DALD.APPLN_NO = DCS.APPLN_NO LEFT OUTER JOIN REF_BRANCH RB ON DALD.BRANCH_CD = RB.BRANCH_CD;
FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FOR ALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET (SALES_EXEC_ID, APPLIED_LOAN_AMT, SPPK_LOAN _AMT, PRG_CODE, OCC_CD, EMPLOYMENT_TYPE, REGION_CD, BRANCH_CD, HUB_CD, DEVELOPER_CD, CHANNEL_CD, POSTAL_CD, DOB, TOTAL_INCOME, FINAL_APPLN_SCORE, COMPAN Y_CD, COMBRANCH_CODE) = ( SELECT SALES_EXEC _ID, APPLIED _AMT, APPROVED_AMT, PRG_CODE, OCC_CD, EMPLOYMENT_TYPE, REGIONAL_ID, BRANCH_CD, HUB_CD, DEVELOPER_CD, CHANNEL_CODE, POSTAL_CD, DOB, TOT AL_INCOME, FINAL_APPLN_SCORE, COMPANY_CD, COMBRANCH_CODE FROM TEMP_APPLN_DTL_ENH DAD WHERE DAD.ROWID = OBJ _DROWID(CTR)) WHERE ROWID = OBJ _PROWID(CTR);
160
3.3.1.4 Cursor dan ROWID Berdasarkan ranking access path tercepat (lihat hal 42), ROWID merupakan cara akses tercepat, sehingga kami menggunakan cursor untuk menunjuk ROWID agar dapat digunakan untuk proses DM L. Berikut contoh query yang menggunakan cursor dan ROWID : PROCEDURE INS_FROM_LOAN IS CURSOR INS_ACCT IS SELECT DAD.ROWID FROM D_ACCOUNT_DTL DAD WHERE DAD.RECORD_TO_DATE = I_INDATE AND NOT EXISTS (SELECT 'X' FROM P_ACCT_DTL_ENH PAD WHERE PAD.ACCT_NO = DAD.ACCT_NO AND PAD.MONTH_IDX = TO_CHAR(I_INDATE,'MM')); OPEN INS_ACCT; FETCH INS_ACCT BULK COLLECT INTO OBJ_DROWID ; CLOSE INS_ACCT; V_CTR_LOOP := TRUNC(OBJ_DROWID.COUNT/V_REC_COMMIT); FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT INSERT INTO P_ACCT_DTL_ENH( ACCT_NO, CUST_NO, LOS_APPLN_NO, HOST_APPLN_NO, FACILITY_CD, FACILITY_SEQ, LOAN_TYPE, ORIG_BAL, VINTAGE, ACCT_OPEN_DATE, LOAN_TENURE, ORIG_LOAN_DATE, PAYMENT_FREQUENCY, SALES_EXEC_ID, PRG_CODE, EMPLOYMENT_TYPE, OCC_CD, REGION_CD, BRANCH_CD, HUB_CD, DEVELOPER_CD, CHANNEL_CD, POSTAL_CD, TOTAL_INCOME, CREATED_USER, CREATED_DTM, AMT_DPD1, INT_DPD1, PROCESS_SEQ_NO, MONTH_IDX) SELECT ACCT_NO, CIF_NO, HOST_APPLN_NO, HOST_APPLN_NO, FACILITY_CD, FACILITY_SEQ, LOAN_TYPE, ORIG_BAL, TO_NUMBER(TO_CHAR(DATE_OPENED,'RRRRMM')), DATE_OPENED, LOAN_TERM, ORIG_LOAN_DATE, PAYMENT_FREQUENCY, '99', '99', '99', '99', '99','99', '99', '99','99', '99', 0,UPPER(USER), SYSDATE, 0, 0, PROCESS_SEQ_NO, TO_CHAR(RECORD_TO_DATE,'MM') FROM D_ACCOUNT_DTL WHERE ROWID = OBJ_DROWID(CTR);
161
3.3.1.5 Konversi Tipe Data Yang dimaksud dengan konversi tipe data adalah mengubah tipe data suatu field. M isalnya field emp_id tipe datanya number, namun anda ingin menggabungkan emp_id dengan namanya yang bertipe varchar2 maka anda harus meng-konversi data emp_id agar bertipe varchar2 sehingga dapat digabung dengan emp_name.
Select ‘ Name of employee with ID : ‘ || emp_id || ‘ is ’ || emp_name As ‘employees detail’ From employees ;
Sehingga akan menghasilkan output seperti : EMPLOYEE DETAIL ======================================== Name of employee with ID : E001 is John Travolta Name of employee with ID : E002 is Paris Hilton Name of employee with ID : E003 is Britney Spears Name of employee with ID : E004 is Craig David
Bentuk code di atas merupakan contoh konversi data secara implicit. Oracle dengan sendirinya akan mengonversi data emp_id yang bertipe number menjadi varchar2. Sedangkan konversi data secara explicit, kita harus menggunakan fungsi konversi yang telah disediakan oracle seperti : to_char untuk mengonversi data bertipe number atau date menjadi varchar2, to_number untuk mengonversi data bertipe char atau varchar2 menjadi number, to_date untuk mengonversi data bertipe varchar2 menjadi date.
char atau
162 Contoh code yang menggunakan konversi data secara explicit : SELECT last_name, TO_CHAR(hire_date, 'DD Month YYYY') AS HIREDATE FROM employees;
Dimana hasilnya akan seperti : LAST_NAME ============== John Travolta Paris Hilton Britney Spears Craig David
HIREDATE ================ 10 January 2004 23 February 2004 05 January 2004 15 June 2004
Pada perancangan SQL tuning dengan tujuan untuk menghasilkan code yang lebih optimal, kami meminimalisasi penggunaan konversi data secara explicit, karena dapat menurunkan performance. Sehingga, kami mengubah beberapa code yang
menggunakan
konversi data secara explicit menjadi implicit seperti dibawah ini : SELECT COUNT(*) FROM D_ACCOUNT_DTL DAD WHERE TO_DATE(DAD.RECORD_TO_DATE,'DD-MON-YY') '14-MAY-08'; M enjadi : SELECT COUNT(*) FROM D_ACCOUNT_DTL DAD WHERE DAD.RECORD_TO_DATE = '14-MAY-08';
=
163
3.3.2
Indexing Seperti yang telah dijelaskan pada landasan teori, indexing akan
mempercepat performance dari suatu query. Berikut contoh proses indexing :
Key
<= 100
Key
>= 100
(min) … 50 201 … (max) 101 … 200
51 .. 100
50, rowid
51, rowid
200, rowid
201, rowid
40, rowid
60, rowid
190, rowid
210, rowid
… 100, rowid
… 101, rowid
… (max)
… (min)
Blok paling bawah di struktur tree pada gambar di atas disebut sebagai leaf nodes, dan berisi index key dan rowid. Index key adalah data pada kolom ID sesuai dengan yang telah didefinisikan sedangkan rowid adalah physical address yang menunjukkan pada blok mana record tersebut berada. Blok yang berada di atas leaf nodes disebut sebagai branch block.
Sebagai contoh : untuk mencari data dengan nilai 52 menggunakan index maka oracle akan mulai mencari data dari block paling atas dan menuju branch blok sebelah kiri yang menunjukkan nilai lebih kecil sama dengan 100. Pada branch block kedua, oracle menuju block dengan nilai antara 51 dan 100, sampai akhirnya menemukan leaf node yang berisi index key dengan nilai 52, dan di dalamnya terdapat rowid letak record data sebenarnya di dalam tabel. Dari leaf node ini, oracle akan mendapatkan record yang diminta. Cara pencarian data seperti ini disebut sebagai INDEX SCAN.
164 Berikut contoh code indexing pada salah satu tabel : CREATE TABLE D_ACCOUNT_DTL( BRANCH_NO ACCT_NO ACCT_TYPE HOST_APPLN_NO CIF_NO STATUS_CD LOAN_TYPE ORIG_BAL RELEASE_AMT CURR_BAL MISC_INSURANCE_PREMIUM OTH_CHARGES MISC_COST PAID_LATE_CHARGES MISC_INSURANCE_REBATE RECORD_TO_DATE CREATED_USER CREATED_DTM UPDATED_USER UPDATED_DTM PROCESS_SEQ_NO )
NUMBER(5), NUMBER(19) NOT NULL, VARCHAR2(1 BYTE), VARCHAR2(20 BYTE), NUMBER(19), NUMBER(1), CHAR(10 BYTE), NUMBER(17,2), NUMBER(17,2), NUMBER(17,2), NUMBER(15,2), NUMBER(15,2), NUMBER(15,2), NUMBER(15,2), NUMBER(15,2), DATE, VARCHAR2(12 BYTE), DATE VARCHAR2(12 BYTE), DATE, NUMBER(12)
CREATE INDEX IDX_RECORD_TO_DATE ON D_ACCOUNT_DTL(RECORD_TO_DATE); CREATE INDEX IDX_LOS_APPLN_NO ON D_ACCOUNT_DTL(LOS_APPLN_NO); CREATE UNIQUE INDEX PK_D_ACCOUNT_DTL ON D_ACCOUNT_DTL(ACCT_NO);
Pada perancangan ini, data yang diberi index adalah kolom dalam suatu tabel yang mengandung banyak data dan kolom tersebut sering diakses baik dalam select statement maupun dalam conditional statement (where clause). Contoh : kolom record_to_date dalam tabel D_ACCOUNT_DTL. Kolom tersebut sering diakses untuk membentuk analytical report harian maupun bulanan seperti : KPI, AVG TA dan P & L report. Index yang diimplementasikan adalah B*Tree index karena kolom yang diberi index sering menggunakan DM L statement (insert, update,delete), sehingga tidak cocok jika menggunakan bitmap index.
165 3.3.3
Partitioning Partisi pada tabel digunakan untuk membagi tabel yang mempunyai record
data yang sangat besar menjadi segmen-segmen kecil yang terpisah.Tabel yang diberi partisi adalah tabel yang berisi data lebih besar dari 2 GB dan data yang menunjukkan waktu (misalnya kolom year_month dan month_idx). Setelah dipartisi, data secara keseluruhan akan terpecah menjadi bagian-bagian yang lebih kecil dikelompokkan berdasarkan key partition-nya. Berikut contoh hasil partitioning terhadap data yang dipartisi berdasarkan month (bulan). Month 01 01 02 03 03 01 03
Product KPR KPM KT A KT A KPR Mitrakarya House
Month 01
02 03
Product KPR KPM Mitrakarya KT A KT A KPR House
Sehingga ketika akan mengakses data pada bulan 03, maka select, update, delete statement dapat mengakses atau memanipulasi data per-bagian tanpa perlu mengakses data pada tabel secara keseluruhan. Month 01
02 03
Product KPR KPM Mitrakarya KT A KT A KPR House
Month 03
Product KT A KPR House
Tabel yang diberi partisi adalah tabel yang berisi data lebih dari 2 GB dan data yang menunjukkan waktu (misalnya : kolom year_month dan month_idx). Jenis partisi yang diimplementasikan adalah : range, hash dan interval partition. Jenis partisi yang diimplementasikan tersebut disesuaikan dengan jenis data yang terdapat dalam tabel-tabel milik Consumer Loan Group.
166
Berikut contoh code untuk membuat partition by range terhadap kolom month_idx CREATE TABLE P_ACCT_DTL_ENH ( ACCT_NO CUST_NO LOS_APPLN_NO FACILITY_CD FACILITY_SEQ SALES_EXEC_ID APPLIED_LOAN_AMT SPPK_LOAN_AMT HOST_APPLN_NO ACCT_OPEN_DATE VINTAGE PRG_CODE EMPLOYMENT_TYPE OCC_CD REGION_CD BRANCH_CD HUB_CD DEVELOPER_CD CHANNEL_CD POSTAL_CD DOB TOTAL_INCOME FINAL_APPLN_SCORE GENDER_CD LOAN_TYPE ORIG_BAL LOAN_TENURE ORIG_LOAN_DATE PAYMENT_FREQUENC ACCT_STATUS1 ACCT_SUB_STATUS1 LTD_RELEASE_AMT1 CURR_BAL1 YTD_INT_AMT1 YTD_PRIN_AMT1 INST_AMT1 CHARGEOFF_DATE1 PAYMENT_AMT1 INT_RATE1 AMT_DPD1 INT_DPD1 CREATED_USER CREATED_DTM PROCESS_SEQ_NO PREV_DAY_RELEASE_AMT COMPANY_CD MONTH_IDX COMBRANCH_CODE )
NUMBER(19) NUMBER(19) VARCHAR2(20) VARCHAR2(4), NUMBER(10), VARCHAR2(12), NUMBER(17,2), NUMBER(17,2), VARCHAR2(20) DATE, NUMBER(6), VARCHAR2(10), VARCHAR2(6), VARCHAR2(5), VARCHAR2(6), VARCHAR2(5), VARCHAR2(10), VARCHAR2(10), VARCHAR2(6), VARCHAR2(9), DATE, NUMBER(20,2), NUMBER(8,2), VARCHAR2(1), VARCHAR2(10), NUMBER(17,2), NUMBER(4), DATE, NUMBER(3), NUMBER(2), NUMBER(2), NUMBER(17,2), NUMBER(17,2), NUMBER(22,2), NUMBER(22,2), NUMBER(17,2), DATE, NUMBER(17,2), NUMBER(6,2), NUMBER(5), NUMBER(5), VARCHAR2(12), DATE, NUMBER(12), NUMBER(22,2), VARCHAR2(10), VARCHAR(2) VARCHAR2(10)
NOT NULL, NOT NULL, NOT NULL,
NOT NULL,
NOT NULL,
PARTITION BY RANGE (MONTH_IDX) SUBPARTITION BY HASH (ACCT_NO) SUBPARTITIONS 4 ( PARTITION P_JAN VALUES LESS THAN ('02'), PARTITION P_FEB VALUES LESS THAN ('03'), PARTITION P_MAR VALUES LESS THAN ('04'), PARTITION P_APR VALUES LESS THAN ('05'), PARTITION P_MAY VALUES LESS THAN ('06'), PARTITION P_JUN VALUES LESS THAN ('07'), PARTITION P_JUL VALUES LESS THAN ('08'), PARTITION P_AUG VALUES LESS THAN ('09'), PARTITION P_SEP VALUES LESS THAN ('10'), PARTITION P_OCT VALUES LESS THAN ('11'), PARTITION P_NOV VALUES LESS THAN ('12'), PARTITION P_DEC VALUES LESS THAN ('13') );
