Pivot ve Unpivot Component

6. December 2010

Hepimiz elbet bir gün Pivot yapmanın ya da unpivot yapmanın zorunluluğuna şahit olmuşuzdur. Union All ile başlayan maceramız sub query'ler ile devam etmiş, nihayetinde T-SQL koduyla cursor üzerinden binbir takla atarak kendi pivot, unpivot stored procedure'ımızı yazmış ve/veya SQL içerisinde gelen PIVOT ve UNPIVOT komutlarını kullanmaya kadar devam etmiştir. Tüm bu badireleri atlatmak yerine SSIS içerisinde bulunan pivot ve unoivot conponent'leri kullanarak da ilerleyebileceğimizi anlatmaya çalışacağım bu yazıda.

 

Öncelikle hiç Pivot ve Unpivot mantığını bilmeyenlere basit bir girizgah yaparak başlayalım.

Pivot:

İlk Durum:

Satış Tablosu:

UrunIsmi

Ay

SatisMiktari

Ayakkabı

1

100

Ayakkabı

2

50

Bot

1

20

Bot

2

40

 

İstenen:

UrunIsmi

  1. AY
  1. Ay

Ayakkabı

100

50

Bot

20

40

 

İlk durumdan ikinci duruma bir tabloyu getirmek için Pivot işlemi uygulamak gereklidir. Tabloyu transpoze ederek ayları kolonlara getirme işlemine Pivot işlei denir.

 

Bunun için SQL yazmak istersek aşağı yukarı şöyle bir query yazmak gerekir:

UNION ALL Metodu:

SELECT UrunIsmi, SUM([1.Ay]) as [1.Ay], SUM([2.Ay]) as [2.Ay] FROM (

SELECT UrunIsmi,SUM(SatisMiktari) as [1.Ay], 0 as [2.Ay] FRoM SatislarTablosu

GROUP BY UrunIsmi

WHERE Ay=1

UNION ALL

SELECT UrunIsmi, 0 as [1.Ay], SUM(SatisMiktari) as [2.Ay] FRoM SatislarTablosu

GROUP BY UrunIsmi

WHERE Ay=2

UNION ALL

 

...

...

 

) as UnionQ

GROUP BY UrunIsmi

 

PIVOT Metodu:

SELECT UrunIsmi,

[1],

[2]

FROM (

SELECT * FROM SatislarTablosu

) As DATA

PIVOT

(Sum(SatisMiktari))

FOR Ay IN ([1],[2],[3]......[12])

AS PivotQ

 

Ancak gördüğünüz gibi bu işlemleri her seferinde yapmak hem sql'i hem de sizi yorar. Bunun alternatifi olarak bugün SSIS Metodunu inceleyeceğiz.

SSIS Metodu:

Örneklemeyi kolaylaştırmak adına AdventureWorks demo veritabanındna faydalanalım.

Burada Sales.SalesOrderHeader tablosundan OrderDate'in ayını, Sales.SalesOrderDetail tablosundan miktarı ve ürünü, Production.Product tablosundan da ürünün ismini alalım aşağıdaki query ile:

SELECT p.Name as ProductName , MONTH(soh.OrderDate) as OrderMonth, SUM(sod.LineTotal) as OrderTotal FROM Sales.SalesOrderHeader soh

LEFT JOIN Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID

LEFT JOIN Production.Product p on sod.ProductID=p.ProductID

GROUP BY p.Name,MONTH(soh.OrderDate)

 

Bu Query bize aşağıdakine benzer bir sonuç döndürecektir:

 

Biz bunu SSIS yardımıyla aşağıdaki sonuca benzer hale getireceğiz:

 

Bunun için dilerseniz hemen paketimizi yaratıp bir adet dataflow ekleyelim. Ardından dataflow içerisine bir adet OLEDB Source, bir adet pivot component ve bir adet de oleDB Destination ekleyelim ve birbirine bağlayalım.

OLEDB Source içerisine girelim ve yazmış olduğumuz Query'i yapıştıralım.

 

Ardından Pivot Component içerisine girelim ve özelliklerini düzenlemeye başlayalım.

Input columns sekmesinde kullanacağımız alanları seçelim:

Inpu and Output Properties ekmesinde ise Pivot işlemi için gerekli olan ayarlamaları yapacağız:

Bu noktada Defualt Input kolonlarınun pivoting için kullanım tiplerini belirlememiz gerekiyor.

Olası kullanım tipleri:

  • Hiç Kullanılmaz.
  • Sol tarafta görüntülenir.
  • Kolonlarda görüntülenir.
  • Ölçü birimi olarak kullanılır.

Bizim örneğimizde Sol tarafta ürün isminin, kolonlarda ayların ve ölçü birimi olarak da Sipariş Miktarının kullanılmasını sağlayacağız.

Bunun için bizim Pivot Component içerisindeki input kolonlarımızı tasnif etmemiz gereklidir. Bu ayrımı ve kullanım biçimini belirlemek için o kolona ait Custom Properties içerisindeki Pivot Usage alanını kullanırız.

 

İlgili kolonu seçtikten sonra sağ tarafta açılan özellikler içerisinde PivotUsage özelliğini belirlememiz gereklidir. Peki bu pivot usage enum'ları nelerdir?

0 - Kullanılmaz.

1 - Sol tarafta görüntülenir.

2 - Kolonlarda görüntülenir.

3 - Ölçü birimidir.

 

Bizim örneğimizde ProductName'in Pivot Usage'ı 1, OrderMonth'un pivot Usage'ı 2, OrderTotal'ın pivot usage'ı ise 3 olacaktır.

Ardından bir sonraki yapmamız gereken Pivot Component'in Output kolonlarını ayarlamak olacaktır.

Output olarak ProductName ve 1. Aydan 12. Ay'a kadar kolonlar olacaktır.

Bunun için Pivot Defalt Output bölümüne yeni kolonlar eklememiz gereklidir.

Add colum diyerek tüm istediğimiz kolonlarımızı ekliyoruz.

Bu işlemden sonra ProductName output özelliklerine geçip ProductName Input kolonunun LineageID'sini yazıyoruz ilkin.

Benim paketimde bu LineageID 42

 

Ardından Pivot işlemlerini gerçekleştirmek adına January'den December'a kadar olan kolonların SourceColumn özelliğine de OrderTotal'ın LineageID'sini yazacağız. (Her birine tek tek)

Ek olarak January için PivotKeyValue özelliğine 1 (yani 1.Ay), February'nin PivotKeyValue özelliğine 2 (yani 2. Ay) yazarak 12 ayı tamamlıyoruz. (Dikkat: burada yazdığımız 1,2,3 değeri select'imiz içerisinde gelen değerlerdir. 1 yerine Ocak diye gelseydi PivotKeyValue içerisine Ocak yazacaktık.)

 

 

Ardından paketi çalıştırmadan önce bu bilgileri yazacağımız tabloyu SSIS yardımıyla oluşturalım .

 

Son olarak paketi çalıştırtığımızda ise verilerin doğru olarak geldiğini görebilmekteyiz.

 

 

UnPivot:

UnPivot işlemi ise pivot ile yaptığımız transpozasyonun tam tersini yapar. Yani veriyi yine eski haline getirir.

Bunun için az önce pivot ettiğimiz veriyi unpivot ederek örneğimize devam edebiliriz.

Hemen yeni bir dataflow ekleyip içerisine Oledb Source, Unpivot Component, Oledb Destination ekleyelim.

Oledb Source olarak az önce yarattığımız PivotedSales tablosunu select edelim.

Unpivot Component'in içerisine girerek ayarlamaları yaptıktan sonra aşağıdaki ekrandaki gibi birgörüntüye sahip olacağız:

Burada January..December aralığını seçeceğiz. ProductName'i de direkt geçireceğiz.

DestinationColumn pipeline içerisine OrderMonth olarak kendini ekleyecek ve değerlerini January için Ocak, February için Şubat ... olarak alacak ve ilgili toplamı da OrderTotal olarak yazacak.

Bu ayarlamaları yaptıktan sonra Oledb Destination içerisine girerek UnpivotedSales tablomuzu oluşturalım:

Ardından paketi çalıştıralm e UnpivotedSales tablosuna select atalım.

Nihayetinde değerlerin aşağıdaki gibi geldiğini göreceğiz:

 

Budur J

Dataflow Components ,

Comments (3) -

erdem erdem
erdem erdem
8/27/2012 11:36:46 PM #
mükemmel anlatım.teşekkürler.
erdem erdem
erdem erdem
8/28/2012 7:37:40 PM #
bu arada sanırım 2012 versiyonu ile bu işlemler değişmiş ve kolaylaşmış..yani bu karışıklığa artık gerek yokSmile
4/4/2013 12:23:41 AM #
teşekkür ederim, evet 2012 ile birlikte oldukça yeni güzel özellikler var.

Bkz: www.ssisnedir.com/.../...elen-Bazi-Yenilikler.aspx

Add comment




biuquote
  • Comment
  • Preview
Loading