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

Cara Kerja Change Data Capture

untuk mengetahui cara Setup CDC klik disini ==> http://grahitanusantara.blogspot.com/2011/03/change-data-capture-cdc.html

sudah dijelaskan page sebelumnya bahwa Column _$operation akan menyimpan keterangan , apakah operasi delete,insert atau update. nah berikut adalah default value untuk pengcodean operasi DDL yang akan dicapture CDC. kali ini kita akan membahas bagaimana cdc mengcapture perubahan data pada tabel


Pertama kita lihat dulu kondisi awal dari table Person dan Mirrornya(cdc.HR_Person_CT)

USE BELAJARCDC
SELECT * FROM HR.Person
GO

USE BELAJARCDC
SELECT * FROM cdc.HR_Person_CT
GO

dapat kita lihat diatas bahwa tabel Person berisikan 3 rows sementara HR_Person_CT masih dalam keadaan virgin kosong  .

sekarang mari kita coba insert satu row pada table person Person

USE BELAJARCDC
GO
INSERT INTO HR.Person VALUES ('Rahwana','L',' Alengka','1981-09-29','Jl.pasak bumi 75',' Alengka')

setelah itu kita lihat kembali perubahan yang terjadi pada tabel Person dan HR_Person_CT

dapat kita lihat pada tabel HR_Person_CT ada penambahan row baru dengan kode pada kolom __$operation = 2 yang artinya adalah operasi Insert.

Sekarang kita coba untuk melakukan Update pada tabel Person

USE BELAJARCDC
GO
UPDATE HR.Person SET 
alamat = 'Jl.Mega Kuningan 10'
WHERE Id = '2011'

Hasilnya :

kalau kita lihat disini terjadi penambahan 2 row pada tabel HR_Person_CT yaitu pada row 2 dan 3. pada row ke 2 adalah menyimpan data sebelum terjadi perubahan (update) yang dikodekan dengan  __$operation = 3. dan pada row ke 3 adalah hasil dari update row itu sendiri yang dikodekan dengan __$operation = 4

Sekarang kita coba melakukan delete pada tabel Person

USE BELAJARCDC
GO
DELETE FROM HR.Person 
WHERE ID='2014'

Hasilnya :

Dapat kita lihat sekarang bahwa terdapat penambahan row pada tabel HR_Person_CT pada row ke 4 dan dapat kita lihat pada __$operation bernilai 1 yang artinya delete data

Change Data Capture (CDC)

Untuk Kesempatan kali ini kita akan belajar mewarnai membahas tentang CDC.
agar lebih mudah untuk memahami maka kita menggunakan Database sederhana untuk testing.
berikut Query untuk Create Database yang akan kita gunakan

CREATE DATABASE BELAJARCDC

Berikut Create Table yang akan kita gunakan

USE BELAJARCDC
GO
CREATE SCHEMA HR
GO
CREATE TABLE HR.Person
(
  id  int Identity (2011,1),
  nama Varchar (100),
  jenis_kelamin Char (1),
  tempat_lahir VARCHAR (50),
  tanggal_lahir Datetime,
  alamat VARCHAR (200),
  kota_domisili VARCHAR (50)
)

Dan berikut untuk Insert datanya

INSERT INTO [BELAJARCDC].[HR].[Person]
           ([nama]
           ,[jenis_kelamin]
           ,[tempat_lahir]
           ,[tanggal_lahir]
           ,[alamat]
           ,[kota_domisili])
     VALUES
          ('Werkudara','L','Jodipati','08-28-1986','JL.ayodya 299','Jodipati'),
          ('Arjuna','L','Hastina','03-11-1987','JL.pesanggrahan 88','Hastina'),
          ('Dewi Kunthi','P','Adirata','11-28-1989','JL.sendiko gusti 35','Adirata')
GO

Setup  CDC pada Database 

Check CDC yang sedang run pada Server dengan

USE master 
GO 
SELECT [name], database_id, is_cdc_enabled  
FROM sys.databases       
GO

disini kita akan mencoba mengaktifkan CDC pada BELAJARCDC

USE BELAJARCDC
GO 
EXEC sys.sp_cdc_enable_db 
GO

nah..sekarang CDC pasti sudah dalam kondisi active pada BELAJARCDC

Secara Otomatis akan tercreate beberapa table baru pada BELAJARCDC >> Tables >> System Tables

berikut adalah table baru yang akan muncul :



Akan muncul Schema baru (CDC) di BELAJARCDC >> Security >> Schemas

Setting CDC pada table tertentu

Periksa table dimana CDC sudah aktif

USE BELAJARCDC 
GO 
SELECT [name], is_tracked_by_cdc  
FROM sys.tables 
GO

Berikutnya kita perlu mengaktifkan pada table mana kita akan menggunakan CDC, pada kesempatan kali ini kita akan mencoba mengaktifkan CDC pada table Person

USE BELAJARCDC
GO 
EXEC sys.sp_cdc_enable_table 
@source_schema = N'HR', 
@source_name   = N'Person', 
@role_name     = NULL 

Pastikan bahwa SQL Job Agent dalam kondisi RUN
dan berikut apabila JOB CDC berhasil di Create

Dan akan secara otomatis tercreate Replikasi untuk table Person.Person pada BELAJARCDC >> Tables >> System Tables

Default-nya , semua kolom pada table cdc akan dibaca dan diproses dalam operasi cdc ini, dalam hal ini contohnya pada table Person yang telah kita buat. namun apabila yang akan ditrack hanya beberapa kolom (mempercepat performance) maka kita perlu merubah [cdc].[fn_cdc_get_all_changes_HR_Person] sesuai dengan kebutuhan akan kolom-kolom mana saja yang aka ditrack perubahanya.

gambar : column pada table mirror yang tercreate oleh cdc

Seperti yang bisa kita lihat bersama diatas bahwa setelah kita setup cdc pada suatu table , maka secara otomatis akan tercreate table baru (HR.Person_CT) sama persis seperti table yang akan ditrack oleh cdc. sedangkan table ini berfungsi untuk menyimpan data yang berubah. namun ada penambahan 5 kolom baru yaitu :

__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask

column yang paling penting disini adalah __$operation and __$update_mask.

Column _$operation akan menyimpan keterangan , apakah operasi delete,insert atau update. nah berikut adalah default value untuk pengcodean operasi DDL yang akan dicapture CDC.

Delete Statement = 1

Insert Statement = 2

Nilai sebelum diupdate = 3

Nilai setelah diupdate = 4

column _$update_mask akan disave dengan menggunakan bit datatype, yang akan menunjukan kolom mana yang diupdate .

Bagaimana Cara Kerja CDC ??? 

klik pada link ini untuk melanjutkan : http://grahitanusantara.blogspot.com/2011/04/cara-kerja-change-data-capture.html

Mengetahui Jumlah Kolom pada seluruh tabel pada database

Sebelum memulai merancang SSIS package untuk ETL, hal yang pertama perlu saya ketahui adalah Schema dan struktur database yang akan dijadikan source untuk ditarik datanya. Jumlah row, jumlah column dan size yang besar pada suatu table tentunya akan sangat mempengarui proses transfer data, yang dalam hal ini alokasi waktu dan kapan data tersebut bisa ditarik perlu dipertimbangkan dengan matang.

Andaikata kita menarik suatu table dengan size , jumlah row serta banyak column yang sangat besar dari OLTP (Database Transaksi) BANK pada saat busy work hour...sudah tentu akan mengganggu proses input data dan bisa-bisa dicomplain oleh user yang berada di cabang.

kali ini saya akan membahas dan memberikan contoh tentang cara mengetahui jumlah column pada table dalam database. (note : query ini diuji coba dengan menggunakan database AdventureWork2008RW, SQLSERVER2008R2 Database Engine)

pertama-tama kita perlu mengetahui column pada table dalam database
berikut query-nya

USE AdventureWorks2008R2
SELECT st.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
sc.name AS column_name
FROM sys.tables AS st
INNER JOIN sys.columns sc ON st.OBJECT_ID = sc.OBJECT_ID 
ORDER BY schema_name, table_name;

Setelah kita mengetahui nama table beserta column dan table schema-nya, maka yang kita lakukan disini adalah melakukan counting jumlah column dan di group berdasarkan nama table

with CTE_COLUMN_IN_TABLE
as
(SELECT st.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
sc.name AS column_name
FROM sys.tables AS st
INNER JOIN sys.columns sc ON st.OBJECT_ID = sc.OBJECT_ID 
--ORDER BY schema_name, table_name;
)
, CTE_TOTAL_COUNT_COLUMN as
(
  Select table_name,COUNT(column_name) as column_name FROM CTE_COLUMN_IN_TABLE Group by table_name
)
SELECT * FROM CTE_TOTAL_COUNT_COLUMN  Order By column_name DESC

dan hasilnya

dari sini sudah dapat diketahui bahwa table dengan column terbanyak adalah table SalesOrderHeader

Cara Mengetahui Jumlah Row pada database table(s)

Mengetahui jumlah row pada table sangat membantu dalam melakukan estimasi waktu yang diperlukan untuk penarikan data pada saat melakukan ETL. 

select "TABLE SCHEMA"= CONVERT(Varchar(20),s.name ),
       "TABLE NAME"= convert (varchar (50), o.name),
       "ROWS"=i.rows
        from sysobjects o, sysindexes i,sys.schemas s,sys.tables t
        where o.type = 'U'
        and o.id = i.id
        and i.indid in (0,1)
        and t.object_id = o.id
        and t.schema_id = s.schema_id
order by i.rows desc

Hasilnya akan seperti ini :