Power Pivot ile Excel'de Veri Modeli Kurma Rehberi

Power Pivot nedir, hangi sorunu çözer?

Power Pivot, Excel'in içinde çalışan bir veri modelleme katmanıdır. Sıradan bir pivot tablo tek bir tablodan beslenir. Satış kayıtları bir sayfada, müşteri bilgileri başka bir sayfada, ürün kataloğu bir başkasında duruyorsa klasik yöntem DÜŞEYARA ile bu bilgileri satış tablosunun yanına kopyalamaktır. Tablo büyüdükçe dosya ağırlaşır, formüller yavaşlar ve bir kolonun yanlış çekilmesi tüm raporu bozar.

Power Pivot bu işi tersine çevirir. Tabloları olduğu gibi bırakır, aralarında ilişki tanımlar ve raporu bu ilişkiler üzerinden üretir. Böylece tek bir pivot tabloda satış tablosundaki tutarı, müşteri tablosundaki şehri ve ürün tablosundaki kategoriyi yan yana kullanabilirsiniz. Model, Excel dosyasının içinde sıkıştırılmış ve sütun odaklı biçimde saklandığı için sayfa satır sınırını aşan büyüklükteki veriler de makul sürede özetlenir.

Bu özelliğin Excel'in her sürümünde ve her lisansında bulunmadığını bilmek gerekir. Kendi kurulumunuzda Power Pivot sekmesinin görünüp görünmediğini, görünmüyorsa eklentinin etkinleştirilip etkinleştirilemediğini bilgi işlem ekibinizle kontrol edin.

Modele girmeden önce tabloları hazırlamak

Veri modelinin kalitesi, kurmaya başlamadan önce verinin ne kadar düzenli olduğuna bağlıdır. Her tablonun ilk satırı başlık olmalı, birleştirilmiş hücre ve boş ara satır bulunmamalı, her sütunda tek tür veri durmalıdır. Tarih sütununda yarısı metin yarısı gerçek tarih olan bir kolon ilişki kurarken sizi en çok yoracak hatalardan biridir.

Tabloları hazırlarken şu ayrımı gözetin: • Olay tabloları: satış, sipariş, hareket, bilet gibi her satırı bir olayı anlatan ve zamanla uzayan tablolar. • Boyut tabloları: müşteri, ürün, bölge, takvim gibi her satırı bir varlığı anlatan, benzersiz anahtar içeren tablolar.

Boyut tablolarında anahtar sütunu tekrar etmemelidir. Ürün kodu bir ürün tablosunda yalnızca bir kez geçmelidir. Aksi halde ilişki kurulamaz ya da rapor sayıları şişer. Kaynak veriyi Excel'e getirirken Power Query ile temizlemek, kodları kırpmak, tür dönüşümlerini yapmak ve sonucu doğrudan veri modeline yüklemek en sağlam yoldur.

Tabloları modele eklemek ve ilişki kurmak

Tabloyu modele eklemenin yaygın yolu, tablo üzerindeyken Power Pivot sekmesinden modele ekle komutunu kullanmak ya da Power Query'den yüklerken yalnızca bağlantı oluştur ve veri modeline ekle seçeneğini işaretlemektir. Hücreleri tek tek değil, adlandırılmış Excel tabloları olarak eklemek, kaynak büyüdüğünde modelin kendiliğinden yenilenmesini sağlar.

Tablolar modelde olunca diyagram görünümüne geçin. Burada her tablo bir kutu olarak görünür ve ortak anahtar sütunları fare ile sürüklenerek bağlanır. Mantık şudur: boyut tablosundaki benzersiz anahtar, olay tablosundaki karşılığına bağlanır. Müşteri kodu müşteri tablosunda tek, satış tablosunda çok kez geçer; bu bire çok ilişkisidir ve filtre müşteri tablosundan satış tablosuna doğru akar.

Çoğu ihtiyaç için tek yönlü filtreleme yeterlidir. Çift yönlü filtre belirsiz yollar yaratıp rakamların hangi yoldan geldiğini anlaşılmaz hale getirebilir, bu yüzden yalnızca gerçek bir nedeniniz olduğunda açın. Modelin düzgün bir şekil alması için merkezde olay tablosu, çevresinde boyut tabloları duracak biçimde yıldız şemasını hedefleyin. Boyutlar arası zincirleme bağlantıyı mümkün olduğunca azaltın.

Takvim tablosu neden modelin ayrılmaz parçasıdır?

Zamana göre karşılaştırma yapacaksanız, olay tablosundaki tarih sütununu doğrudan pivot tabloya atmak yerine ayrı bir takvim tablosu kurun. Takvim tablosu kesintisiz tarihlerden oluşur ve yıl, ay, ay adı, çeyrek, hafta gibi türetilmiş sütunlar taşır. Olay tablosundaki tarih bu tabloya bağlanır.

Takvim tablosu olmadan geçen yılın aynı dönemiyle kıyaslama, yılbaşından bugüne toplam ya da hareketli ortalama gibi zaman zekâsı hesapları ya çalışmaz ya da tutarsız sonuç verir. Çünkü bu hesaplar tarihlerin eksiksiz ve sıralı olmasını bekler. Satış olmayan günler olay tablosunda hiç yer almaz, takvim tablosunda ise yer alır. Power Pivot'ta takvim tablosunu tarih tablosu olarak işaretlemek, bu hesapların doğru çalışmasını sağlar. Ay adlarını doğru sıralamak için ay adı sütununu ay numarası sütununa göre sıralatmayı unutmayın; aksi halde aylar alfabetik dizilir.

Ölçü yazmak: DAX'a ilk adım

Modelin gücü, sütun ekleyerek değil ölçü tanımlayarak ortaya çıkar. Ölçü, pivot tablo hangi filtre bağlamında olursa olsun o bağlama göre yeniden hesaplanan bir formüldür. Ölçüler DAX adı verilen formül dilinde yazılır. Sözdizimi Excel formüllerine benzer ama hücre adresleri yerine tablo ve sütun adlarıyla çalışır.

İlk ölçünüz muhtemelen toplam satış tutarı olacaktır. Bunu satış tablosundaki tutar sütununun toplamı olarak tanımlarsınız. Ardından kâr marjı gibi başka ölçülerin oranını alan türev ölçüler kurarsınız. Ölçüyü başka ölçüye dayandırmak, bir mantığı tek yerde tutmanızı sağlar; tanım değiştiğinde yalnızca bir formülü düzeltirsiniz.

Hesaplanmış sütun ile ölçü arasındaki farkı bilin. Hesaplanmış sütun satır satır hesaplanır ve modelde yer kaplar. Ölçü ise yalnızca raporda sorgulandığında, ilgili filtreyle hesaplanır. Toplanacak, ortalaması alınacak, oranlanacak her şey için ölçü kullanın; hesaplanmış sütunu ancak kategori etiketi gibi satır düzeyinde bir özellik gerektiğinde tercih edin.

Bir sonraki aşama filtre bağlamını değiştiren formülleri öğrenmektir: belirli bir bölgenin satışı, belirli bir ürün grubunun payı ya da tüm müşteriler içindeki oran gibi sorular bu aşamada çözülür.

Pivot tabloyu veri modelinden beslemek

Model hazır olunca pivot tablo oluştururken kaynak olarak bu çalışma kitabının veri modelini seçin. Alan listesinde artık yalnızca tek bir tablo değil, modeldeki tüm tablolar ve ölçüleriniz görünür. Satırlara müşteri tablosundan şehir, sütunlara takvim tablosundan çeyrek, değerlere satış ölçüsü sürüklendiğinde ilişkiler sayesinde doğru sonuç oluşur.

Bu aşamada şu kolaylıklar öne çıkar. Dilimleyiciler farklı tablolardan gelen alanları aynı anda filtreleyebilir ve birden fazla pivot tabloya bağlanabilir. Aynı model üzerinde kurulan çeşitli pivot tablolar ve grafikler bir panoya toplandığında tek bir dilimleyici bütün raporu yönetir. Ayrıca kaynak veri güncellendiğinde yalnızca yenile komutu yeterlidir.

Birlikte bakmanızda yarar olan konular: Excel'de pivot tablo ile özetleme, Power Query ile veri hazırlama ve panonun görsel kurgusu. Model bu işlerin ortak zeminidir.

Sık yapılan hatalar ve nasıl önlenir?

En sık görülen hata, ilişki kurulamayan ya da yanlış kurulmuş anahtar sütunlardır. Ürün kodu bir tabloda metin, diğerinde sayı olarak tutuluyorsa ilişki oluşmaz. Çözüm kaynağa dönüp türü eşitlemektir. Anahtar sütunda boşluk, büyük küçük harf farkı ve gizli karakter bulunması da aynı sonucu verir; Power Query'de kırpma ve temizleme adımları bunu önler.

Sık görülen bir başka hata, her şeyi tek geniş tabloda toplayıp Power Pivot'u yalnızca pivot tablo aracı olarak kullanmaktır. Bu durumda modelin esas faydası, yani boyutlarla olay tablosunu ayırmak, kaybolur. Bir diğer hata hesaplanmış sütunları gereksiz çoğaltmaktır; dosya şişer ve yenileme yavaşlar.

Bir diğer tuzak, kullanılmayan sütunları modelde bırakmaktır. Raporda hiç kullanmayacağınız açıklama, not ya da ayrıntılı kimlik sütunlarını modele hiç yüklemeyin. Son olarak ölçülerin adlandırması: Toplam Satış, Satış Adedi gibi açık adlar verin; raporu başkası açtığında formülü okumadan anlamalıdır.

Bir sonuca güvenmeden önce küçük bir kontrol yapın: ölçünün genel toplamını kaynak tablodan elle hesaplayın, farklıysa ilişkiyi ya da filtre yönünü gözden geçirin.

Öğrenme yolu ve Mindset programlarında çalışma

Sağlıklı bir sıra şöyledir: önce klasik pivot tabloda rahat olun, sonra Power Query ile veri temizlemeyi öğrenin, ardından Power Pivot'ta ilişki ve ölçüye geçin. Bu sırayı atlamak, hatayı nerede aradığınızı bilmemenize yol açar.

Mindset'in Excel Power Query ve Power Pivot eğitimi, müfredatında Veri Modeline Giriş ve Power Pivot, DAX Temelleri Power Pivot İçin ve Pivot Tablo Power Pivot ile Entegrasyon modüllerini ayrı başlıklar olarak ele alır; Power Query temelleri ve ileri teknikler de aynı programın parçasıdır. Kurumsal iş akışı ve otomasyon modülü, kurduğunuz modeli düzenli raporlamaya dönüştürmeye odaklanır.