D2]

[Data Warehouse] [6/C2 & 6/D2]

[ Chapter 8] Ekstrak, Transform, Load (ETL) pada Data Warehouse

Dedy Alamsyah, S.Kom, M.Kom [NIDN : 0410047807]

Definisi ETL  Menurut Inmon (2005 : 390), ETL (Extract, Transform, Load) adalah proses melakukan pencarian data, mengintegrasikan, dan menempatkan data ke dalam sebuah data warehouse.  Menurut Kimball & Ross (2002 : 401), ETL adalah kumpulan proses untuk menyampaikan data dan operational source untuk data warehouse. Proses ini terdiri dari extracting, transforming, loading, dan beberapa proses yang dilakukan sebelum dipublikasikan ke dalam data warehouse

Struktur Data pada system ETL  Flat files  XML Data Sets  DTDs, XML Schemas, and XSLT  Relational Tables  Independent DBMS Working Tables  Third Normal Form Entity/Relation Models  Nonrelational Data Sources

Struktur data warehouse (Area depan & belakang)

Four Staging Steps

Keuntungan ETL Berbasis Tools (Software)  A quote from an ETL tool vendor: “The goal of a valuable tool is not to make trivial problems mundane, but to make impossible problems possible.”  Simpler, faster, cheaper development. The tool cost will make up for itself in projects large enough or sophisticated enough.  Technical people with broad business skills who are otherwise not professional programmers can use ETL tools effectively.  Many ETL tools have integrated metadata repositories that can synchronize metadata from source systems, target databases, and other BI tools.  Most ETL tools automatically generate metadata at every step of the process and enforce a consistent metadata-driven methodology that all developers must follow.  Most ETL tools have a comprehensive built-in scheduler aiding in documentation, ease of creation, and management change. The ETL tool should handle all of the complex dependency and error handling that might be required if things go wrong.

(2)  The metadata repository of most ETL tools can automatically produce data lineage (looking backward) and data dependency analysis (looking forward).  ETL tools have connectors prebuilt for most source and target systems. At a more technical level, ETL tools should be able to handle all sorts of complex data type conversions.  ETL tools typically offer in-line encryption and compression capabilities.  Most ETL tools deliver good performance even for very large data sets. Consider a tool if your ETL data volume is very large or if it will be in a couple of years.  An ETL tool can often manage complex load-balancing scenarios across servers, avoiding server deadlock.  Most ETL tools will perform an automatic change-impact analysis for downstream processes and applications that are affected by a proposed schema change.  An ETL-tool approach can be augmented with selected processing modules hand coded in an underlying programming language. For example, a custom CRC (cyclic redundancy checksum) algorithm could be introduced into an ETL vendor’s data flow if the vendorsupplied module did not have the right statistical performance. Or a custom seasonalization algorithm could be programmed as part of a data-quality step to determine if an observed value is reasonable.

Keuntungan ETL Berbasis Manual (Hand Code)  Automated unit testing tools are available in a hand-coded system but not with a tool-based approach. For example, the JUnit library (www.junit.org) is a highly regarded and well-supported tool for unit testing Java programs. There are similar packages for other languages. You can also use a scripting language, such as Tcl or Python, to set up test data, run an ETL process, and verify the results.  Automating the testing process through one of these methods will significantly improve the productivity of your QA staff and the quality of your deliverables.  Object-oriented programming techniques help you make all your transformations consistent for error reporting, validation, and metadata updates.  You can more directly manage metadata in hand-coded systems, although at the same time you must create all your own metadata interfaces.  A brief requirements analysis of an ETL system quickly points you toward file-based processing, not database-stored procedures. File-based processes are more direct. They’re simply coded, easily tested, and well understood.

(2)  Existing legacy routines should probably be left as-is.  In-house programmers may be available.  A tool-based approach will limit you to the tool vendor’s abilities and their unique scripting language. But you can develop a handcoded system in a common and well-known language. (In fairness, all the ETL tools allow escapes to standard programming languages in isolated modules.)  Hand-coded ETL provides unlimited flexibility, if that is indeed what you need. You can literally do anything you want. In many instances, a unique approach or a different language can provide a big advantage.

Keuntungan menggunakan teknologi ETL yang sudah teruji  Define once, apply many. Share and reuse business rules and structured routines, keeping your data consistent throughout the data warehouse.  Impact analysis. Determine which tables, columns, and processes are affected by proposed changes.  Metadata repository. Easily create, maintain, and publish data lineage; inherit business definitions from a datamodeling tool, and present capture metadata in your BI tool.

Keuntungan menggunakan teknologi ETL yang sudah teruji (2)  Incremental aggregation. Dynamically update summary tables by applying only new and changed data without the need to rebuild aggregates with each load process.  Managed batch loading. Reduce shell scripts and enable conditional loading, load statistics, automated e-mail notification, and so on.  Simpler connectivity to a wide variety of complex sources such as SAP and mainframes.  Parallel pipe-lined multithreaded operation.  Vendor experience, including success with dimensional models and a proven track record of supporting data warehouses.

Penjelasan ETL  Jadi, ETL atau extract transform loading adalah fase pemrosesan data dari sumber data masuk ke dalam data warehouse.  Tujuan ETL adalah mengumpulkan, menyaring dan mengilah dan menggabungkan data yang relevan dari berbagai sumber untuk disimpan ke dalam suatu data warehouse. ETL juga digunakan untuk mengintegrasikan data dengan sistem yang sudah ada sebelumnya.  Hasil dari proses ETL adalah data yang memenuhi kriteria data warehouse seperti data historis, terpadu, terangkum, statis dan memiliki struktur yang dirancang untuk keperluan proses analisis

Kesulitan pada proses extraction Beberapa kesulitan jenis kesulitan yang seringkali dijumpai dalam proses ETL (Inmon, 2005):  Sistem sumber yang sangat beragam dan berbeda.  Sumber sistem dari berbagai platform dan sistem operasi yang berbeda.  Sumber sistem yang berjalan pada teknologi database yang sudah outof-date.  Data historis pada perubahan nilai tidak disimpan dalam sistem sumber operasional, karena informasi historis penting dalam data warehouse.  Kualitas data meragukan di banyak sistem sumber lama.  Sumber struktur sistem terus berubah dari waktu ke waktu  Kurangnya konsistensi antara sistem sumber. Sebagai contoh, data gaji dapat direpresentasikan sebagai gaji bulanan, gaji mingguan, dan gaji dua -bulan sekali dalam sistem penggajian sumber yang berbeda.  Kurangnya sarana untuk menyelesaikan ketidaksesuaian yang menjadi masalah dalam inkonsistensi.  Banyak jenis data representasi yang samar dan ambigu.

Kegiatan yang membentuk proses ETL:  Menggabungkan sumber data beberapa struktur ke dalam baris tunggal dalam database target.  Membagi satu sumber struktur data ke dalam beberapa struktur untuk dimasukkan ke beberapa baris dalam database target.  Membaca data dari kamus data dan katalog dari sistem sumber.  Membaca data dari berbagai struktur file termasuk file flat, file indeks, dan database sistem warisan (legacy).  Memuat rincian untuk mengisi tabel fakta atomik.  Meng-agregasikan untuk mengisi tabel ringkasan fakta.  Mengubah data dari satu format dalam platform sumber ke format lain dalam platform target.  Mengolah nilai target dari field input (contoh: usia dari tanggal lahir).  Mengubah nilai-nilai samar terhadap nilai-nilai yang bermakna bagi pengguna (contoh: 1 dan 2 untuk pria dan wanita).

Ekstrak Langkah pertama dari ETL adalah proses penarikan data dari satu atau lebis sitem operasional sebagai sumber data (bisa diambil dari sistem OLTP, tapi bisa juga dari external database). Pada hakekatnya, proses extrasi adalah proses penguraian dan pembersihan data yang diekstrak untuk mendapatkan suatu pola atau struktur data yang diinginkan.

Logic Extraction Metode yang digunakan untuk melakukan proses ekstrasi secara logic (logical exctraction) ada 2, yaitu : 1. Full Extraction,  Proses ekstraksi dilakukan dengan mengambil seluruh data dari source system yang diperlukan 2. Increment Extraction  Proses ekstraksi hanya pada data yang berubah atau belum ada pada target sistem pada periode tertentu

Physical extraction  Mekanisme ekstraksi secara fisik (physical extraction) dibagi menjadi dua jenis, yaitu : 1. Online extraction  Data di ekstrak dari source system ke target system secara langsung. Proses ekstraksi dilakukan dengan cara langsung connect ke source system untuk mengakses source table. 2. Offline Extraction  Data tidak di ekstrak secara langsung dari source system, namun berada diluar source system. Data yang akan di ekstrak sudah mempunyai struktur table dan struktur data yang diharapkan sudah sesuai dengan data warehouse. Misalnya flat file.

Beberapa hal yang perlu diperhatikan dalam tahap ekstraksi (Inmon, 2005):  Identifikasi Sumber - mengidentifikasi aplikasi sumber dan struktur sumber.  Metode ekstraksi - untuk setiap sumber data, menentukan apakah proses ekstraksi bersifat manual atau Tool-based.  Frekuensi ekstraksi - untuk setiap sumber data, menetapkan seberapa sering ekstraksi data harus dilakukan: harian, mingguan, kuartalan, dan sebagainya.  Window time - untuk setiap sumber data, menunjukkan jendela waktu untuk proses ekstraksi.  Job sequencing - menentukan apakah awal dari satu pekerjaan dalam aliran pekerjaan ekstraksi harus menunggu sampai pekerjaan sebelumnya telah selesai dengan sukses.  Exception handling - menentukan bagaimana menangani catatan masukan yang tidak dapat diekstraksi.

Contoh data dari sumber untuk di-ekstraksi (Sumber: Inmon, 2005)

Transform Proses pembersihan data yang telah diambil pada proses extract sehingga data itu sesuai dengan struktur data warehouse atau data mart. Hal-hal yang dapat dilakukan dalam tahap tranformasi :  Hanya memilih kolom tertentu saja untuk dimasukkan ke dalam data warehouse  Menerjemahkan nilai berupa kode (misal, database sumber menyimpan nilai 1 untuk pria 2 untuk wanita, tetapi data warehouse menyimpan M untuk pria dan F untuk wanita). Proses yang dilakukan disebut automated data cleansing, tidak ada pembersihan secara manual selama proses ETL.  Mengkodekan nilai-nilai ke dalam bentuk bebas (misal, memetakan “Male” , “T” dan “Mr” ke dalam “M”).  Melakukan perhitungan nilai-nilai baru (misal, sale_amount = qty*unit_price).  Menggabungkan data dari berbagai sumber bersama-sama  Membuat ringkasan dari sekumpulan baris data (misal, total penjualan untuk setiap bagian).  Kesulitan yang terjadi pada transformasi adalah data harus  digabungkan dari beberapa sistem terpisah. Oleh karena itu harus dibersikan sehingga konsisten dan harus diagregasi untuk mempercepat analisis.

Tugas Utama Transform  Seleksi: memilih salah satu catatan utuh atau bagian dari beberapa catatan dari sistem sumber  Memisahkan / menggabungkan data.  Melakukan Konversi untuk standarisasi antara ekstraksi data dari sistem sumber yang berbeda, dan untuk membuat fields yang dapat digunakan dan dimengerti oleh pengguna.  Summarization.  Pengayaan, dengan penataan dan penyederhanaan field individu untuk membuat field tersebut lebih berguna untuk lingkungan data warehouse.

Contoh

Load  Fase load merupakan tahapan yang berfungsi untuk memasukkan data ke dalam target akhir, yaitu ke dalam suatu data warehouse. Waktu dan jangkauan untuk mengganti atau menambah data tergantung pada perancangan data warehouse pada waktu menganalisa keperluan informasi.  Fase load berinteraksi dengan suatu database, constraint didefinisikan dalam skema database sebagai suatu trigger yang diaktifkan pada waktu melakukan load data (contohnya : uniqueness, referential, integrity, mandatory fields), yang juga berkontribusi untuk keseluruhan tampilan dan kualitas data dari proses ETL.

Beberapa Teknik Loading (Inmon:2005)  Load: Jika tabel target sudah ada, menghapus data yang ada dan menggunakan data dari file yang masuk. Jika table sudah kosong, menggunakan data dari file yang masuk.  Menambahkan (Append): Jika data sudah ada, menambahkan data yang masuk, tetap menggunakan data yang ada dalam tabel target. Ketika record yang masuk berupa duplikat, maka bisa ditolak atau diperbolehkan sebagai duplikat.  Destructive Merge: Jika primary key dari record yang masuk sesuai dengan kunci dari record yang ada, perbarui record target yang sesuai. Jika catatan yang masuk merupakan record baru, tambahkan record yang masuk.  Constructive Merge: Jika primary key dari record yang masuk sesuai dengan kunci dari record yang ada, tinggalkan record yang ada, tambahkan record yang masuk, dan tandai record tambahan sebagai superceding dari record yang lama.

Contoh Teknik Loading (Inmon:2005)

Proses Pendukung ETL  Pemeriksaan Quality Assurance

 Sudah dilakukan quality assurance -> testing

 Release /Publishing

 Ada perubahan -> informasi ke user

 Pemperbaruan

 Update -> Append -> managed load updates

 Querying

 Query/request data dari pengguna

 Data Tanggapan / Feeding in Reverse

 Hasil Upload deskripsi dimensi yg sudah dibersihkan

 Auditing

 Dapat diketahui asal muasal data -> update -> log

 Pengamanan

 Pengaturan hak akses terhadap dw atau data atau query

 Backing Up dan Pemulihan

 Pengaturan strategi backup dan restore data

Home Work 1. 2. 3. 4.

Print jawaban UTS masing-masing Install Software (MySQL, Pentaho) Buat model STAR Minggu depan praktek ETL (Ekstrak, Transform, Load)

Selesai

Ada pertanyaan ???