SQL Server Integration Services (Data Profiling Task)

Seringkali pada saat membuat masalah SSIS ETL package, seringkali saya menemui issue munculnya data jadi-jadian error atau karakter aneh2, entah itu menggandung pesan truncation , data dislocation, row dispotition atau constraint yang menolak null value. Akal2an yang saya lakukan adalah membuat suatu store procedure yang membaca semua data pada column table yang menggandung nilai null atau karakter2 tertentu diluar huruf, angka dan beberapa tanda baca (sok ide mode ON). Lebih parah lagi saya perlu check satu2 dan menyamakan Column pada Tabel Source dengan Tabel destination. Mungkin kalau tabel dan data yang terlibat cuman sedikit, tentunya tidak akan menjadi masalah...namun apabila tabel2 yang terlibat mempunyai karakteristik banyak column , banyak rows data tentunya akan berujung frustasi (kenyataan). pernah saya berurusan dengan  table dengan jumlah column lebih dari 300 dan rownya lebih dari 500 juta dan sizenya 126 Giga... (yang pernah berurusan dengan polisi table BSEG pada SAP pasti tau)tentunya akan berujung dengan senyum kecut menangis semalam (audi Mode ON). Bisa saja diakali dengan meredirect row2 data kotor kedalam table lain, namun kenyataanya akan membuat effort untuk pembuatan ETL package lebih susah dan lebih lama.

Dan Syukur Alhamdullillah Puji Tuhan..... Pada SQL 2008 ini sudah ada component yang namanya Data Profiling Task........

Fungsi dari Data Profiling Task, yaitu untuk menganalisis data dalam database SQL Server untuk menentukan apakah ada potensi masalah dengan data.


Dengan menggunakan Data Profiling Task, kita dapat menggenerate hasil analisis data yang akan disimpan dalam bentuk XML yang kita sebut disini data profile report, dan kemudian melihat hasil dari profiling tersebut kita dapat menggunakan Data Profiler Viewer yang seharusnya ikut terinstallpada saat  menginstal SSIS. Secara default Data Profiler task dapat ditemui di C:\Program Files\Microsoft SQL Server\100\DTS\Binn dengan nama  DataProfileViewer.exe

HOW TO USE ??????



Untuk dapat mengetahui profil data, kita bisa menggunakan Data Profiling Task pada Control Flow yang terdapat pada toolbox==> Control flow  kemudian pilih  satu atau beberapa type  profiling (coba saya jelaskan di bawah) dalam Data Profiling Task Editor (pada bagian [Profile Request Page]).

Data profiling task memiliki  8 profile types yaitu :

• Candidate Key 
• Column Length Distrib
• Column Null Ratio
• Column Pattern 
• Column Statistics 
• Column Value Distrib
• Functional Dependency 
• Value Inclusion 

Candidate Key Profilling type :    alkisah pada jaman penjajahan jepang, nenek mengajarkan pada saya , menurut hasil risetnya selama puluhan tahun bahwa untuk mencari candidate  key (unique) pada suatu table, saya harus melakukan Count Distinct column pada tabel tersebut. kalau hasilnya sama dengan jumlah rowcount total tabel tersebut, maka dapat dipastikan bahwa column tersebut dapat dijadikan candidate key.

         

Namun ...sungguh durjana luaarrr biasa, kini dengan adanya Profiling Task ini, cukup dengan klak klik kita sudah dapat menemukan column2 mana pada suatu table yang unique dengan mudah dan hampir effortless. dan terang saja.. teori dari nenek saya sudah terbukti tidak efektif lagi..ohh soo sad (Semoga nenek saya tenang dan bahagia di SURGA ..AMINN)

Pada Profiling tipe ini, akan membantu kita untuk mencari column pada tabel yang bisa dijadikan candidate key untuk kemudian jadi primary key. cara kerjanya adalah mencari column2 yang unique untuk kemudian bisa dijadikan primary key

Type Data 
Type data yang dapat dihandle oleh profiling tipe ini adalah

• Tipe data integer: bit, tinyint, smallint, int, dan bigint
• Tipe data karakter: char, nchar, varchar, dan nvarchar
• Tanggal dan waktu tipe data: datetime, smalldatetime, timestamp, tanggal, waktu, datetime2, dan datetimeoffset

Profiler type ini mencari Duplicate values in a potential key column untuk membantu identifikasi apakah column2 kandidat tersebut masih redundant— untuk memberikan gambaran berikut akan saya coba tampilkan profiling Candidate key pada tabel Member dalam database tester saya yang kebetulan memiliki 478894 row

Mari kita lihat hasilnya dengan menggunakan Data Profiler Viewer

Dapat kita lihat pada Kandidat Key Profile (pada gambar dinomori dengan angka 1), bahwa ada 2 kandidat yaitu barcode dan code. namun barcode memiliki key strenght 97 % sementara code memiliki 100 %, dari sini sudah dapat kita pastikan bahwa code lah yang lebih cocok sebagai unique key karena 100% Unique

Kalau kita klik  barcode pada Key colomn, maka secara otomatis  akan muncul list di Key Violation, berisi tentang karakter2 apa saja atau barcode mana yang membuat barcode tidak unique

Dapat kita lihat pada gambar diatas, column barcode memiliki 9890 row yang berisi spasi atau kosong, 676 row berisi '.' dan banyak barcode yang sama digunakan pada beberapa row. apabila ingin tahu detil datanya tinggal di klik pada salah satu row pada key violation, misalnya pada row yang berisi spasi...secara otomotis semua row yang memiki barcode bernilai spasi akan di list pada Kandidat Key Profile tab

Berarti kesimpulan yang dapat kita ambil, yang cocok untuk unique key adalah column 'code' karena memiliki 100% key strenght dan tidak ada key violation

------------------------------------------------------------------------------------------------------------

Column Length Distrib
"Warning : trucation in colomn..blablablaaa..... the source collumn and destination collumn table length doesn't macth" . Yap, benar sekali, kira2 seperti itulah pesan yang akan muncul , apabila panjang collumn (length) pada destination lebih sedikit ketimbang panjang column dari data source.

Untuk mengantisipasi kejadian tersebut dari awal, kita bisa menggunakan Profiler type ini ..yaitu Column Length Distrib   yang fungsinya menghasilkan informasi tentang banyak character pada column table.

Type Data : 

type data yang dapat dihandle oleh profiler ini adalah :

• Character data types: char,nchar, varchar, and nvarchar