Kurumsal IT-Teknoloji Eğitimleri-Eğitim Takvimi

Excel Tablolarını Power Pivot’a Aktarıp İlişkilendirmek

                                                                                                                                                                             

PowerPivot: 2010 Yılının Mayıs ayında Microsoft’un, SQL Server 2008 R2 ürünün bir parçası olarak sunuldu. İçerisinde Excel ve Sharepoint için ‘Excel PowerPivot’ ve ‘Sharepoint Powerpivot’ add-In( eklentileri ) bulunmaktaydı. SQL server 2012 versiyonu ise, Microsoft Excel 2010 için PowerPivot eklentisini SQL Server’den bağımsız hale getirdi ve PowerPivot kendi sürümlerini yayınlamaya başladı. Microsoft, 2013 versiyonunda PowerPivot’u kendi içerisinde dahil edip adını ‘Power Pivot’ olarak değiştirdi.

Microsoft Office 2010 versiyonu kullanıcıları, PowerPivot ya da Power Pivot eklentisini kullanmak için eklentiyi ücretsiz olarak Microsoft’un sitesinden indirmeleri gerekirken, Microsoft Office 2013 kullanıcılarının ürünü, File-> Options-> Add-Ins-> COM-Add-ins yoluyla aktif hale getirebiliyorlardı.

Microsoft Office 2016 versiyonunda ise Power Pivot Data sekmesinde, Data Tools bölümünde ‘Manage Data Model’ adıyla kendisine yer bulmuştur.

                                   

Power Pivot, Excel’de veri sınırlamasını ortadan kaldıran (1.048.576 satır), büyük verilerle çalışma imkânı sağlayan (milyonlarca satır), karmaşık ve anlamsız verilerin birbirleri ile ilişkilendirilerek anlamlı hale getirilmesini ve bu verileri analizler edip, raporlar oluşturulmasını sağlayan BI (Business Intelligence – İş Zekası) uygulamasıdır. Normal bir veri setinden farklı olarak, Veri Modelleme özelliği kullanılır. Veri Modelleme ile karmaşık veri setlerini birbirleri ile ilişkilendirerek veriler anlamlı hale getirilir, karmaşıklık ortadan kaldırılır ve raporların oluşturulması daha hızlı gerçekleştirilir.


1.    Verileri Power Pivot’a aktarmak 
Excel dosyalarındaki verileri Power Pivot’a aktarmanın birkaç farklı yöntemi bulunmaktadır. Aktarılacak veri setlerinin sayısına, yapısına, bulunduğu kaynağa, boyutuna göre uygun olan yol seçilebilir. 

Power Pivot’a aktarılmak istenen veri(ler) ilk olarak Excel’in Format As Table özelliği ile dinamik hale getirilir. Daha sonra dosya kaydedilip kapatılır.  Boş bir Excel Çalışma kitabı açıldıktan sonra, Data Sekmesinde, Data Tools alanında bulunan Manage Data Model’e tıklanır. Karşınıza gelen pencere, Power Pivot Editorü’dür.

Home Sekmesine, Get External Data alanında Power Pivot’a birçok farklı kaynaktan veri aktarımı yapmanızı sağlayan seçenekler mevcuttur. Bir Excel Çalışma Kitabından veriler aktarılmak istendiğinde, From Other Sources seçeneğine tıklanır. Karşınıza gelen pencere, Power Pivot’un tablo yükleme sihirbazıdır. Kaydırma çubuğu aşağı kaydırılarak Excel File seçeneğine tıklanıp, Next denir.

Friendly Connection Name alanına, bir bağlantı ismi verilir. Bu isim dilediğiniz bir isim olabilir. İkinci seçenek olan Excel’s File Path’ de ise veri alınacak Excel dosyasının yolu belirtilir. Use first row as column headers işaretlenir. Bu işlem, veri aldığınız Excel dosyasındaki tablonun başlıklarını sütun başlığı olarak kabul eder. Daha sonra Next’e basılır. 

Belirtilen yoldaki dosyada bulunan Excel sayfaları yukarıdaki gibi listelenir (İlgili kitapta bulunan tüm sayfalar). Pencerenin altındaki Finish butonuna basıldıktan sonra, veriler aktarılmaya başlar ve saniyeler içerisinde Excel’deki tablo, Power Pivot’a aktarılmış olur.

Table Import Wizard penceresini kapattığınızda verileri Power Pivot’a görebilirsiniz. 

b.    Bir Text Dosyasındaki Verileri Power Pivot’a aktarmak

Bir text dosyasında bulunan verileri Power Pivot’a aktarmak istediğinizde ise, boş bir Excel çalışma kitabı açılır. Önceki örnekte olduğu gibi veri alınacak kaynak seçilir.  Veri Text dosyasından alındığı için, text dosyasında veriler (sütunlar) birbirinden hangi özel karakterle ayrılmış ise ayraç olarak o karakter seçilir.

Daha sonra yine Finish denilerek veriler Power Pivot’a aktarılmış olur.

Görüldüğü üzere, Excel’de satır sınırlaması varken, Power Pivot’a milyonlarca satır veriyi saniyeler içerisinde import edebilirsiniz.

2.    Power Pivot’a aktarılan tabloları ilişkilendirmek

İster Excel’de olsun, ister diğer Veri Tabanı (Access, SQL, Oracle vb.) programlarında olsun, veriler tablolarda saklanır. Tablo, herhangi bir ürün ya da bir durum ile ilgili bilgilerin satır bazlı tutulduğu kayıtlardan oluşan yapılardır. Bu yapıların belirli alanlar üzerinden birbirlerine bağlanarak oluşturulan ilişkili tablo yapısına Veri Modeli denir. Power Pivot’ta Excel’de en sık kullanılan Vlookup Fonksiyonu yoktur. Eğer birden fazla tablo ile aynı anda çalışılması gerekiyorsa bu tabloların birbirleri ile ilişkilendirilmesi gerekir. 
İçerisinde Bölge, Ürün, Temsilci ve Satış bilgilerinin tutulduğu bir Excel çalışma kitabının Power Pivota aktarıldıktan sonra nasıl ilişkilendirildiği aşağıda açıklanmıştır.
Boş bir çalışma kitabı açıldıktan sonra, Data Sekmesindeki Data Tools bölümünde bulunan Manage Data Model ikonuna tıklanır. Power Pivot Editor’ü açıldıktan sonra Home Sekmesinde, Get External Data 

seçeneğinden yukarıda açıklandığı gibi veriler Power Pivot’a yüklenir. 

Daha sonra Power Pivot penceresinde, Home sekmesinde, View bölümünde Diagram View seçeneği ile tabloların ilişkilendirilebileceği görünüme geçilir. Bu Görünüm, Microsoft Access’de tablolar arası ilişki kurulan görünümle nerdeyse aynıdır. 

İlişkiler kurulmadan önce, tablolar arası bir ortak alan belirlenmesi gerekir. Excel’de VLOOKUP fonksiyonunu kullanırken, ilk parametre olan Lookup_Value çağrıldığı Transaction (İşlem) tablosundan seçilir ve tabloda tekrar edebilir. Ancak ikinci parametre olan Table_Array’da- almak istenilen verinin bulunduğu Lookup (Arama) tablosunda  - bu ortak alan tekrar edemez.

Bu kuraldan yola çıkarak benzersiz kayıtların olduğu tablodaki bir alan, başka bir tabloda tekrar eden bir durumda ise bu iki tablo ilişkilendirilebilir.

İlişkilendirmek için, benzersiz kayıtların bulunduğu tablodaki alanı tutup, tekrar eden kayıtların bulunduğu tablodaki aynı alanın üzerine sürükleyip bırakmak yeterlidir.

  • Bölge Tablosundaki Bölge Kod – Temsilci Tablosundaki Bölge Kod
  • Ürün Tablosundaki Ürün Kod – Satis Tablosundaki Ürün Kod

Temsilci Tablosundaki Satis Temsilcisi – Satis Tablosundaki Satis Temsilcisi ile sürükle bırak yöntemi ile ilişkilendirilmiştir

Power Pivot’un 2016 versiyonunda, ilişkilerin ne tür oldukları (One to many, many to one ya da many to many) bağlantı çizgileri üzerinde yönü ile birlikte gösterilir. Ayrıca herhangi bir bağlantı çizgisi üzerine gelindiğinde ise tabloların hangi alanlar üzerinden birbirine bağlandığı Power Pivot tarafından renklendirilerek belirtilir. 
Bu sayede, sadece milyonlarca satır veriler üzerinden raporlar hazırlamak için değil, VLOOKUP fonksiyonunu sürekli kullanarak farklı tablolardan alanları bir araya getirip raporlar oluşturulmak zorunda kalınıyorsa da Power Pivot ile bu raporlar hızlıca gerçekleştirilebilir.

KUBİLAY TAŞTUTAR

YAZILIM VE VERITABANI BIRIMI, KURUMSAL YAZILIM VE VERITABANI BIRIMI