SQL Server Indonesia User Groups Community dirgantaragrahita
Welcome to SQL Server User Group Indonesia
Sign in • Join • Help | Sign In Live ID
Tuesday, June 07, 2011 10:34 PM

Mengetahui Definisi dalam suatu Database Object

USE AdventureWorks2008;
GO
-- Mengetahui isi Stored Procedure, Trigger, VIEW & Function
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id

a1

USE AdventureWorks2008;
GO
-- Menampilkan code definisi suatu user procedure
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE PROCEDURE%'

a2

USE AdventureWorks2008;
GO
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE FUNCTION%'

a3

USE AdventureWorks2008;
GO
-- T-SQL untuk mengetahui isi dan definisi VIEW pada database
-- Menampilkan semua view pada database dan definisinya
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE VIEW%'

a4

USE AdventureWorks2008;
GO
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE TRIGGER%'

a5

Posted by dirgantaragrahita with no comments
Tuesday, June 07, 2011 10:24 PM

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]).


1

2

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
3

4

Mari kita lihat hasilnya dengan menggunakan Data Profiler Viewer

5

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

 

 

6

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

 

7

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

 

8

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 ..yaituColumn 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
Posted by dirgantaragrahita with no comments
Tuesday, June 07, 2011 9:54 PM

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. 

 


Hasilnya akan seperti ini :

TableRowCount

Posted by dirgantaragrahita with no comments
Tuesday, June 07, 2011 9:50 PM

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;
3-31-2011 11-14-27 PM

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

3-31-2011 11-24-42 PM

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

 
Posted by dirgantaragrahita with no comments
Sunday, June 05, 2011 9:57 PM

Insert, Update ,Delete pada SQL VIEW menggunakan Instead Of trigger

Salam hangat sahabat2 pecinta SQL..dan salam kenal. dan terimakasih kepada bapak Narendra Wicaksono  yang telah membuatkan akses blog di community ini dan sekaligus bener2 bikin saya ngiler dengan foto2nya dengan sandra dewi hmmm.........

untuk postingan  yang pertama kali ini saya tidak membahas mengenai produk2 baru maupun fitur2 baru yang ada pada microsoft SQL baik 2008 R2 maupun CTP 2011, ya bisa kita bilang ini merupakan flash back atau reminder saja jika memang sudah pernah menggunakan fungsional pada materi ini.

Kebetulan (pada saat materi ini ditulis) saya didaulat untuk menyelesaikan project performance tuning dimana yang akan dioprek adalah database SQL 2000 Entreprise. Sedangkan issue yang dihadapi adalah performance baik batch processing maupun reporting yang dirasa sangat lambat. wah, tentunya ini menjadi suatu tantangan bagi saya, karena memang passion saya kebetulan ke masalah performance, meskipun dalam otak saya sql 2000 ini termasuk dalam legacy software...heheheh (jaman SQL 2008R2 gitu lohhh).

Analisa Penyebab Issue Muncul :

1. Penggunaan Temp tabel yang berlebihan pada query
2. Terlalu banyak recursive/ looping pada query (UDF maupun User Store Procedure)
3. Jumlah  row data pada table2 yang paling sering di akses sangat besar (lebih dari 20jt rows) sehingga scanning data sangat lama karena user yang mengakses juga banyak.
4. Penempatan index kurang tepat.

untuk kesempatan saat ini saya akan membahas cara solving pada problem no 3 (table yang terlalu besar sizenya)
Solusinya :

  -  memecah table yang paling besar menjadi beberapa table
  -  menggunakan Distributed Partition View
  -  menggunakan Instead Of Trigger sehingga tetap bisa melakukan Insert, Update , Delete pada View

pada SQL 2005 keatas kita dapat menggunakan Partition table , namun SQL 2000 belum ada fasilitas tersebut. dan juga kita tidak bisa begitu saja memecah serta membuat tabel baru karena     ini bisa menggangu fungsi aplikasi transaksional .



TO BE CONTINUE
Posted by dirgantaragrahita with no comments
Filed under:
More Posts