Power Pivot ve DAX fonksiyonlarına giriş. Power Pivot’ta Kullanılan temel DAX Fonksiyonların ‘=RELATED()’
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. Adından da anlaşılacağı üzere, raporlar ve analizler herkesin bildiği Excel’in Pivot Table özelliği ile yapılı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.
Power Pivot ile Excel’de Pivot Tablo özelliğini kullanarak yapılamayan analizleri de mümkün kılar. Sebebi ise, sahip olduğu DAX fonksiyonlarıdır. DAX ( Data Analysis Expressions) veriler üzerinde hesaplamalar, işlemler gerçekleştirilip, veri modeline sorulan soruları çok hızlı bir şekilde cevaplayan sorgulama dilidir. Yani DAX bir formül değil, programlama dilidir. Normal bir Veri Modeline dönüştürülmüş veri setinden Pivot Tablo ile analiz gerçekleştirilmek istendiği zaman hesaplanmış alan ya da öğe oluşturulamaz. Ancak Power Pivot ile DAX fonksiyonları kullanılarak, Veri Modelinde istenildiği zaman bu hesaplamalar gerçekleştirilebilir, merak edilen her soru bu fonksiyonlar ile cevaplandırılabilir.
Veri Modeli Nedir?
İ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 da Veri Modeli denir.
Şekil-1 İlişkili tablolardan oluşan bir veri modeli.
Excel’de oluşturulan bir satırlık kayıt bile kendi başına aslında tablodur ve Veri Modelinin en basit halidir. Eğer bu kayıtlar birden fazla tablo oluşturur ve tablolar –yüksek ihtimalle- belirli alanlar üzerinden birbirlerine bağlanırsa ilişkili (Related) hale gelirler. Tablolar arası ilişki, görsel olarak bu tabloları birbirilerine bağlayan çizgileri le ifade edilir.
İlişki Türleri
1. Bire çok (One to Many): En sık kullanılan ilişki çeşididir. Bir tablodaki her kayıt, bir başka tabloda birden fazla kayıt ile eşleşebilir. Yukarıdaki veri modelinden örnek vermek gerekirse, D_urun tablosu, F_Satis tablosu ile ilişkilidir. D_urun tablosundaki her ürün, benzersiz bir alan ile temsil edilmektedir, “Ürün Kod”. D_urun tablosunda bulunan bir ürün, F_Satis tablosunda birden fazla kayıtla eşleşebilir, yani bir ürün, birden fazla kez satılabilir. F_Satis Tablosu, D_urun Tablosundaki “ürün kod” alanını kullanarak satılan ürünün detaylarına erişebilir.
2. Bire Bir (One to One): Bir diğer ilişki çeşidi olan bire bir ilişkide, bir tablodaki bir alan, diğer bir tablodaki yalnızca bir kayıt ile eşleşebilir. Bu gibi durumlarda, bu iki tablo birleştirilir ve tek bir tablo haline getirilir.
3. Çoka çok (Many to Many): Bu ilişki türünde ise, tablolardaki kayıtlar, karşılıklı olarak diğer tablolardaki birden fazla kayıtla eşleşebilir. En sık kullanıldığı durumlar banka sistemleridir. Kredi çeşitlerinin bulunduğu tablo( ihtiyaç kredisi, ev kredisi vb.) ile müşteri tablosu ilişkili olabilir. Bir kredi türü birden fazla müşteriye verilebileceği gibi, aynı müşterinin birden fazla kredisi bulunabilir.
Power Pivot’ta DAX fonksiyonlarını kullanabilmek için Veri tabanı Yöneticisi ya da uzmanı olunması zorunluluğu yoktur. Ancak kullanılan Veri Modeli hakkında bilgi sahibi ya da Veri Modeline hâkim olunması avantaj sağlayacaktır.
VLOOKUP vs. RELATED
Normal Excel kullanıcıları için hayati öneme sahip, bir tablodaki veriyi bir başka tabloya, bu iki tablo arasındaki ortak alanı kullanarak getirmek için VLOOKUP (Düşeyara) fonksiyonu kullanılır.
Şekil-2: Excel’de Vlookup fonksiyonunun kullanımı.
Excel sayfasında ürün bilgilerinin (ürün adı ve fiyatı) ve satış bilgilerinin (Satış Tarihi, Satış Temsilcisi, Ürün adı, Adedi ve Getirisi) tutulduğu iki tablo olsun. Satış tablosunda, ilgili ürünün satışından elde edilen gelirin hesaplanabilmesi açısından Satış Tablosuna, Ürün tablosundan ürünün fiyatının getirilmesi gerekir. Bu işlem için de iki tablo arasında var olan bir ortak alan belirlenmelidir ki, bu örnek için ürünün adıdır.
Excel’de benzersiz kayıtların tutulduğu tablolar Lookup Table (Arama Tablosu), bu tablolardan belirli alanların fonksiyon ile getirildiği tablolar ise Transaction Table (İşlem Tablosu) olarak adlandırılır.
VLOOKUP fonksiyonu, iki tablodaki ortak alan olan ürün adını, Lookup tablosunda arar, eşleşen bir kayıt bulursa, o satırdaki istenilen alanı (Fiyat) getirir. Ve bunu her satır için tekrar eder. Evet, her satır için. Excel’de fonksiyonlar satır bazlı çalışır. İlgili satırdaki referans olarak verilen ortak alanı, Lookup tablosunda satır satır arar ve eşleştiği noktada, kaydı bulduğunda da değeri döndürür. Küçük ya da orta ölçekli tablolarda çalışıldığı zaman herhangi bir performans kaybı yaşatmadan istenildiği gibi sonuç verir. Ancak veri tablolarının büyüdüğü, satır ve sütun sayısının arttığı (birkaç yüz bin) Excel kitaplarında dosya boyutu büyümeye, fonksiyonun performansı yavaşlamaya başlar.
Excel’deki bu performans kaybını önlemek, dosya boyutlarının büyümesinin önüne geçmek, hatta Excel’e yüklenemeyecek kadar satır sayısına sahip verileri Power Pivot’a aktarıp burada işleyip, istenilen raporların ya da verilerin görüntülenmesini sağlamak mümkün ve gayet kolaydır.
Ancak Power Pivot’ta VLOOKUP fonksiyonu bulunmamaktır. Ya da VLOOKUP fonksiyonu ile oluşturulmuş bir Transaction Table, Power Pivot’a aktarıldığı zaman fonksiyonun döndürdüğü değerler gözükmez. Çünkü VLOOKUP olmadığı için, fonksiyonun döndürdüğü değerler görüntülenemez. Excel’de VLOOKUP fonksiyonunun yaptığı işi en temel DAX fonksiyonu olan RELATED gerçekleştirir.
Not: Excel’deki tabloların Power Pivot’a nasıl aktarılıp, ilişkilendirildiğini buradaki makaleden ayrıntılı bir biçimde öğrenebilirsiniz.
=RELATED(ColumnName)
Şekil-3: İlişkilendirilmiş Veri Modeli.
Ürün bilgilerinin, Temsilci Bilgilerinin, Bölge ve Satış bilgilerinin bulunduğu tabloların, Tablo formatına çevrilip, Power Pivot’a aktarıldıktan sonra ilişkilendirilmiş halinin yandaki gibi olduğunu düşünelim.
Şekil-4 : F_Satis tablosundaki veri seti örneği.
Excel’de VLOOKUP fonksiyonu kullanarak, ürünün fiyatını getirip, Adet değeri ile çarpma işlemini Power Pivot’ta gerçekleştirmek için; F_Satis tablosuna, ilişkili olduğunu Ürün tablosundan ürünün fiyatının getirilmesi ve Adet değeri ile çarpılması gerekir.
Bir tablodaki alanı, ilişkili olduğu başka bir tabloya getirmek için kullanılan fonksiyon =RELATED() dır. Excel’deki VLOOKUP’dan farklı olarak, tek bir parametre ile çalışır. “ColumnName“, ilişkili olduğu tablodaki, getirilmek istenen verinin bulunduğu sütun adıdır. Adet sütunun yanında bulunan Add Column sütununa “Gelir” yazılıp tabloda yeni bir sütun oluşturulur.
Not: Power Pivot’un 2016 versiyonu ile gelen yeniliği sayesinde, RELATED fonksiyonu çağırıldıktan sonra, çağrıldığı tablo ile ilişkili olan tabloları listeler. Böylece ya veri modelini hafızada tutmaya ya da sürekli tablo görünüme geçip alanları bağlı olduğu tabloları takip etme zorunluluğu ortadan kaldırılmıştır.
Formül çubuğuna da aşağıdaki gibi “=RELATED(” yazıldıktan sonra ilişkili olduğu tablolardan hangi sütundaki veri çağrılmak isteniyorsa (Bu örnek için, D_Urun tablosundaki Fiyat sütunu) tablo adı ile birlikte sütun adı seçilip “8 ” ENTER’a basılır.
Şekil-5: F_Satis tablosunun ilişkili olduğu tabloların listelenişi.
Şekil-6: Related fonksiyonu çalıştırıldıktan sonra görünüm.
RELATED(ColumnName) fonksiyonun çalışma biçimi
Şekil-7: F_Satis tablosu ile D_Urun tablosunun ilişkisi
Yukarıdaki şekilde görüldüğü gibi, iki tablo Ürün Kod sütunu üzerinden birbirleri ile ilişkilidir. RELATED(D_URUN[Fiyat]) DAX fonksiyonun yaptığı iş şudur: F_Satis tablosundaki her satır kayıt için, Ürün Kod sütununda gördüğü değeri, D_urun Tablosunda arar, eşleştiği satırdaki [fiyat] sütununda bulunan değeri getirir.
F_Satis Tablosunda Gelir sütununa getirilen veriyi, yine F_Satis tablosundaki Adet sütununda bulunan değer ile çarparak her satıştan elde edilen gelir hesaplanır.
Şekil-8: Gelir Sütununun son hali
DAX fonksiyonları ile bir hesaplanmış sütun ya da alan oluşturulurken, her zaman için TabloAdı[SütunAdı] şeklinde seçim yapılması gerekir. Aynı tabloda iken [SütunAdı] seçimi yeterli olsa da, DAX fonksiyonlarının kullanım şeklinin dışına çıkmamakta fayda olacaktır.
Son olarak, Hesaplanmış sütuna bir para birimi formatı vermek için, Power Pivot’ta home sekmesinde, Number Formating alanından istenilen sembol seçilir.
Şekil-9: Gelir Sütunun son durumu.
#PowerPivot
#Excel2016
#BusinessIntelligence
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
