Business Intelligence and Analytics

Group Assignment 1

1. Deskripsi Data

Data yang digunakan dalam tugas kali ini berasal dari perusahaan di bidang asuransi dengan jumlah observasi sebanyak 30.045 beserta attribut-attribut dari beberapa table dan range data dari bulan January 2020 - Desember 2020. Berikut ini akan disajikan deskripsi dari attribut:

Berikut ini disajikan bentuk data yang akan digunakan pada tugas 1.

2. Entity Relationship Diagram

Pusat dari ERD ini adalah table transactions dengan primary key berupa nomor polis. Table transaction memiliki relation many to one dari beberapa table yaitu debitur, cabangbank, dan produk. Table transaction dan table cabang bank dihubungkan dengan attribut kode uker, Table transaction dan table debitur dihubungkan dengan attribut Nomor Rekening, dan Table transaction dan table Produk dihubungkan denga attribut kode Produk.

3. Bisnis Proses dan Enterprise Bus Matrix-nya

Bisnis proses yang ingin diketahui dari data yang digunakan ada 2. Pertama, kami ingin mengetahui berapa total produk terjual dan omset perbulan setiap kantor cabang, dengan tujuan melihat kantor cabang mana yang paling banyak menjual product dan memiliki omset terbesar dalam perbulan, sehingga kita bisa membuat strategi ke depanya dari insight ini seperti apakah harus menutup kantor cabang yang penjualan produk dan omset perbulan kecil. Kedua, usia debitur digunakan untuk membuat produk terbaru yang lebih cocok (melihat pangsa pasar).

3.1 Bisnis Proses

  1. Berapa total produk terjual perbulan untuk setiap kantor cabang = Periodic Snapshot
  2. Berapa total premi (omset) perbulan untuk setiap kantor cabang = Periodic Snapshot
  3. Berapa rata-rata jangka waktu bulan yang diambil berdasarkan usia kreditur = Periodic Snapshot
  4. Berapakah interval usia awal kreditur berdasarkan produk = Accumulating Snapshot
  5. Berapa total plafond (nilai pertanggungan) perbulan untuk setiap kantor cabang = Periodic Snapshot

3.2 Fakta

  1. Total produk terjual = Facts Additive
  2. Total Premi = Facts Additive
  3. Rata-rata jangka waktu bulan / Tenor = Facts Semi-Additive
  4. Interval usia awal asuransi = Non-Additive
  5. Total Plafond = Facts Additive

3.3 Enterprise Bus Matrix-nya

4. Star Schema

Berdasarkan bisnis proses dam erd diatas, kami menyimpulkan bahwa terdapat 1 star schema yang akan kami buat. star schema pertama digunakan untuk mengakomodir total produk terjual serta omset perbulan setiap cabang. star schema kedua digunakan untuk mengakomodir pembuatan produk terbaru yang sesuai dengan pasar saat ini

4.1 Star Schema untuk bisnis proses

5. ETL Process

Proses extact, transform dan load data yang dibutuhkan adalah mengolah data source yang berupa file excel 1 table dan dilakukan normalisasi sehingga mempermudah untuk menganalisa bisnis proses yang telah dibuat, berikut adalah langkah-langkah untuk proses ETL :

5.1 Data Normalisasi

1.Melakukan load data excel kedalam database guna mempermudah proses normalisasi menggunakan SQL Query. Database yang digunakan adalah SQL Server dengan menggunakan tools DBEaver untuk melakukan Query data, output dari proses ini adalah database data dengan table main_data

2.Selanjutnya adalah melakukan transform dan load data dari table main_data ke table-table yang telah dinormalisasi:

  • Table Debitur/Kreditur yang berisi data customer, proses ini menggunakan query dibawah ini dan menghasilkan data customer yang nantinya akan export menjadi file CSV

    query:

    output:

  • Table Cabang Bank, adalah table yang berisi kode_uker yang merupakan primary key dan nama kantor cabang, data didapat dari query dibabawah ini yang nantinya akan diexport menjadi file CSV

    query:

    output:

  • Table Produk adalah table yang berisi nama produk kredit asuransi, dikarenakan didalam data tidak terdapat kode_produk dan hanya terdapat nama produk maka dilakukan load data beserta indexing untuk memberi kode_produk tersebut. data didapat dari query dibabawah ini yang nantinya akan diexport menjadi file CSV

    query:

    output:

  • Table transaction, dimana data berupa data transaksi asuransi kredit. Pada saat load data dilakukan beberapa transform skala kecil untuk merubaha value pada atribut usia_awal_asuransi yang berupa [tahun_umur.bulan_umur] akan diubah menjadi tahun_umur saja, yang kedua adalah memberikan indexing produk sesuai dengan kode_produk yang telah didefine pada proses sebelumnya. data didapat dari query dibabawah ini yang nantinya akan diexport menjadi file CSV

    query:

    output:

5.2 Data Star Schema

Padan star schema yang telah dibuat maka diperlukan pemisahan dan indexing data guna memenuhi beberapa dimensi yang tidak dapat dipenuhi oleh data yang telah ada, sehingga diperlukan proses sebagai berikut:

  • Time Dimensi : Dilakukan pengolahan data tanggal_awal_asuransi table transaction yang berformat yyyy-dd-mm akan diubah menajadi dd-yyyy agar dapat memenuhi bisnis proses yang telah dibuat., data diperoleh dari query berikut.

    query:

    output:

  • Fact Table : Dari proses sebelumnya telah dilakukan transform data tanggal dan dijadikan dimensi waktu, maka pada fact table dibutuhkan relasi terhadap table dimensi tersebut, relasi dihasilkan berupa kolom id_time_dim. Proses dilakukan dengan menggunakan query berikut.

    query:

    output: