Extraction Dalam Data Warehouse

ETL berasal dari kata Extract, Transform, and Load. ETL merupakan proses mendapatkan kembali dan merubah data dari source system dan meletakkannya pada data warehouse.

Ada beberapa prinsip dasar untuk memahami ketika ekstraksi data dari source system untuk tujuan mengisi data warehouse. Pertama, volume data yang diambil adalah besar, mungkin ratusan megabyte atau puluhan gigabyte. Sebuah sistem OLTP dirancang agar data yang diambil dalam bentuk potongan-potongan kecil, tidak dalam jumlah besar seperti ini, sehingga Anda harus berhati-hati untuk tidak memperlambat source system turun terlalu banyak. Proses ekstraksi dilakukan secepat mungkin, sekecil mungkin dan sejarang mungkin. Diharapkan perubahan yang terjadi seminimal mungkin pada source system.

Jenis-jenis transformasi data:

• Format dan standarisasi
• Konversi ke format nomor atau tanggal tertentu.
• Pemangkasan spasi
• Penyesuaian menjadi standar
• Penerjemahan ke bentuk lain
• Agregasi atau meringkas data pada tingkat yang lebih tinggi.

Dua prinsip penting lainnya adalah kebocoran (leakage) dan pemulihan (recoverability). Kebocoran terjadi ketika proses ETL berpikir itu telah di download semua data sepenuhnya dari source system tetapi dalam kenyataannya telah melewatkan beberapa catatan. Sebuah proses ETL yang baik seharusnya tidak ada kebocoran. Pemulihan berarti bahwa proses ETL harus kuat, sehingga dalam hal kegagalan, dapat pulih tanpa kehilangan atau kerusakan data.

ETL Approaches and Architecture

Ada beberapa pendekatan pada implementasi ETL. Pendekatan tradisional memungkinkan untuk menarik data dari source system, memasukkannya ke dalam area stage, dan kemudian mengubah dan memuatnya ke data warehouse. Selain menempatkan data dalam area stage, kadang-kadang server ETL melakukan transformasi dalam memori dan kemudian update data warehouse secara langsung (tidak ada staging). Area stage merupakan sebuah database fisik atau file. Menempatkan data ke dalam area stage berarti memasukkannya ke dalam database atau menuliskannya dalam file. Mengubah data dalam memori lebih cepat daripada menaruhnya pada disk pertama. Jika data cukup kecil, Anda dapat mengubah dalam memori, tetapi jika data yang besar, Anda harus meletakkannya pada disk pertama. Anda dapat menempatkan data ke dalam memori tergantung pada seberapa banyak memori server ETL yang dimiliki.

Alternatif ke dua pendekatan ETL adalah Extract, Load, dan Transform (ELT). Dalam pendekatan ELT, kita menarik data dari source system, me-load ke data warehouse, dan kemudian menerapkan transformasi dengan memperbaruidata warehouse. Dalam pendekatan ELT, pada dasarnya kita salin source system (OLTP) data ke dalam data warehouse dan mengubahnya di sana. Orang-orang biasanya mengambil pendekatan ETL jika mereka memiliki server ETL yang kuat dan perangkat lunak yang kuat yang kaya dengan segala macam transformasi dan kualitas data proses.

Orang-orang biasanya mengambil pendekatan ELT jika mereka memiliki sistem database data warehouse yang kuat, biasanya sistem database MPP. Massively Parallel Processing (MPP) adalah sekelompok server (disebut node), dan masing-masing node memiliki memori, prosesor dan disk sendiri. Sebuah sistem database MPP lebih kuat daripada sistem dengan disk bersama karena loading data yang terjadi secara paralel di beberapa node yang memiliki disk mereka sendiri.

Dalam hal ‘siapa’ yang menggerakkan data dari source system, kita bisa mengkategorikan metode ETL menjadi empat pendekatan:

• Sebuah proses ETL menarik data keluar dengan query database source system dengan teratur. Ini adalah pendekatan yang paling umum. ETL menghubungkan ke database source system, men-query data, dan membawa data keluar.
• Trigger dalam database source system mendorong perubahan data keluar. Sebuah Database Trigger adalah kumpulan pernyataan SQL yang mengeksekusi setiap kali ada insert, update, atau delete dalam tabel. Dengan menggunakan trigger, kita dapat menyimpan baris yang berubah dalam tabel lain.
• Sebuah proses dijadwalkan dalam source system mengekspor data secara teratur. Hal ini mirip dengan pendekatan pertama, tetapi program yang meng-query database bukan merupakan program ETL eksternal.
• Seorang pembaca log membaca file log database untuk mengidentifikasi perubahan data. Sebuah file log database berisi catatan transaksi yang dibuat untuk database Seorang pembaca log adalah program yang memahami format data dalam file log.

Dalam hal ‘di mana’ proses yang memindahkan data keluar dijalankan, kita dapat mengkategorikan ETL menjadi tiga pendekatan:

• Menjalankan proses ETL dalam server ETL yang terpisah yang berada di antara source system dan server data warehouse. Pendekatan ini memberikan kinerja tertinggi. ETL berjalan di server sendiri, sehingga tidak menggunakan sumber daya dari server data warehouse atau server source system sama sekali.
• Menjalankan proses ETL pada server data warehouse. Pendekatan ini dapat digunakan jika kita memiliki kapasitas cadangan di server data warehouse atau jika kita memiliki slot waktu ketika data warehouse tidak digunakan (di malam hari misalnya).
• Menjalankan proses ETL dalam server yang me-host source system. Pendekatan ini diimplementasikan ketika kita membutuhkan real-time data warehousing. Dengan kata lain, saat ini data dalam perubahan source system, perubahan itu disebarkan ke data warhouse. Hal ini dapat dicapai dengan menggunakan trigger database dalam source system.

General Consideration

Tipe data source :

• Database (ADO.NET, ODBC, OLEDB, JDBC dan lainnya)
• File System (structured, semistructure, unstructured)
• Queue
• Service
• Emails

Extracting Relational Database

Metode dari ekstraksi data pada sebuah database yang berrelasi :

• Whole table every time: Kami menggunakan whole table every time pada tabel kecil, seperti tabel dengan tiga integer atau kolom varchar (10) yang terdiri dari beberapa baris. Alasan yang lebih umum adalah karena tidak ada cap atau identitas kolom yang bisa kita gunakan untuk mengetahui baris mana yang diperbarui sejak terakhir di ekstrak.
• Incremental extract: Tabel transaksi organisasi besar merupakan tabel besar, yang berisi ratusan ribu baris atau bahkan ratusan juta baris (atau lebih). Ini bisa memakan waktu berhari-hari untuk mengekstrak seluruh tabel, yang merupakan operasi yang sangat intensif dan menurunkan kinerja transaksi pada aplikasi front-end karena hambatan basis data. Ini bukan pilihan yang layak (karena waktu yang dibutuhkan untuk mengekstrak), jadi kita perlu menemukan cara untuk mengekstrak data secara bertahap.
• Fixed range: Jika tidak memungkinkan untuk mengekstrak seluruh tabel karena tabel terlalu besar dan tidak mungkin untuk melakukan incremental extraction, misalnya, karena tidak ada kolom timestamp atau kolomtimestamp tidak dapat diandalkan, karena tidak ada yang dapat diandalkan kolom incremental identity, dan karena tidak mungkin untuk menginstal trigger dalam source system-ada satu pendekatan yang lebih bisa kita lakukan.
• Testing Data Leaks: Jika kita melakukan keduanya incremental extraction atau period range extraction, penting bahwa kita menguji kebocoran data. Katakanlah kita pikir kita telah mengekstrak semua perubahan dalam source system ke data warehouse kami. Kita perlu membangun ETL tambahan atau fixed-range dan menjalankannya setiap hari (atau empat kali sehari atau frekuensi lain). Setelah beberapa minggu, kita membandingkan jumlah baris antara source system dan data warehouse.

Extracting File System

Jenis yang paling umum dari file yang bertindak sebagai sumber dalam proses ETL adalah flat file. Dua contoh flat file, file posisi tetap dan file pipa-delimited. Flat file umum, karena mereka memberikan kinerja terbaik. Mengimpor atau mengekspor dari flat file adalah pilihan tercepat, dibandingkan dengan mengimpor dari jenis file lainnya (XML, misalnya). SQL Server bulk insert dan bulk copy utility (BCP) bekerja dengan flat file. Bulk insert adalah perintah SQL untuk memuat data dari file ke dalam tabel SQL Server. Bulk insert dijalankan dari dalam query SQL Server Editor seperti perintah Transact SQL.

Beberapa hal yang perlu Anda pertimbangkan ketika mengimpor dari flat file:

• Sepakat dengan administrator source system tentang struktur sistem file. Pedoman: lebih berguna untuk menggunakan nama file dinamis sehingga kita dapat meninggalkan n hari terakhir ekstrak file pada disk sebelum menghapus mereka.
• Pastikan Anda memiliki akses ke lokasi yang telah disepakati. Pedoman: Mulai meningkatkan perubahan permintaan untuk memiliki akses read-write ke file lokasi sesegera mungkin. Lakukan ini untuk lingkungan pengembangan, QA, dan produksi.
• Sepakati proses. Pedoman: Tentukan periode arsip berdasarkan prosedur cadangan. Memodifikasi rutin ekstraksi untuk memasukkan pergerakan file diekstrak ke direktori arsip dan menghapus file arsip tua dari periode arsip.
• Sepakati penanganan kegagalan. Pedoman: Prinsip utama penanganan kegagalan adalah bahwa proses ETL harus dapat diulang kembali tanpa menimbulkan masalah.
• Sepakati frekuensi bahwa source system menjalankan ekspor mereka. Pedoman: frekuensi pemuatan ini tergantung pada seberapa sering mereka ekspor dan seberapa sering kebutuhan bisnis untuk melihat data.
• Sepakati format file. Pedoman: tidak baik untuk menggunakan tab, koma, titik dua, backslashes, atau titik koma.
• Sepakati kolom mana yang akan diekspor oleh source system dan dalam rangka apa. Pedoman: Untuk mempermudah, pertimbangkan urutan, memilih dan ekspor kolom hanya diperlukan.

Mengekstrak Data Menggunakan SSIS

Ekstraksi Source Data ke Stage Database meggunakan SSIS

• Buat database source system dan sebuah user login.
• Buat sebuah projek SSIS baru.
• Buat data source untuk Jade beserta stage-nya
• Buat sebuah Data Flow
• Periksa Data pada source system
• Buat data flow asal
• Buat data flow tujuan
• Petakan kolom asal ke tujuan
• Eksekusi SSIS package.

Memorizing Last Extraction Timestamp

Memorizing Last Extraction Timestamp berarti kita menghafalkan timestamp terakhir dari data yang diekstrak sehingga pada ekstraksi selanjutnya kita dapat memulai pada poin tersebut.

Posted in: