Power Pivot

Bir firmanın var olabilmesinin ilk şartı bir ürün üretmesi ya da bir ürün pazarlamasıdır. Bu ürün somut bir varlık olabileceği gibi hizmet gibi soyut bir kavram da olabilir. Kişi bilgisini satabilir; genellikle bu şekildeki firmalar danışmanlık adı altında faaliyet yaparlar.

Veri tabanı kavramını daha iyi anlayabilmek için birden çok ürün üreten ya da satan firmaların veri yapılarını inceleyeceğiz.

Northwind Veri Tabanı

Microsoft bir veri tabanında uygulanan ilişkisel veri yapısı hakkındaki kavramları keşfedebilmek, verileri yönetmek ve pratik deneyim kazanmak amacıyla MS Access ve SQL server için Northwind adında örnek bir veri tabanı yayınlamıştır. Veri tabanına konu Northwind Traders hayali bir kuruluştur.

Makaleye konu veri tabanı MS Access ile ilgili olanıdır. Amacımız Northwind veri tabanı yapısını incelerken, bu veri tabanındaki bilgilerden Excel’de nasıl yararlanacağımızı araştıracağız.

MS Access için Northwind veri tabanındaki tablolar aşağıdaki şekilde gösterilmiştir.

Ürünler

Bu tablolardan ilk olarak Ürünler tablosunu inceleyeceğiz.

Pazarlayacağınız ürünlerin en az bir adının ve satış fiyatının olması gerekmektedir. Bir beyaz kâğıda ürün adı ve fiyatı gibi 2 başlıklı bir liste hazırlamaya kalktığınızda ister istemez 3 sütunlu bir liste yapacaksınızdır. 3’üncü sütun nereden çıktı? Muhtemelen ilk sütun sıra numarasını yazacağınız alan olacaktır.

Veri tabanlarındaki her listede (tabloda) böyle bir sütunun (alan) bulunması, veri tabanı programları açısından zorunluluktur. Bu alanın özelliği listedeki her değerin benzersiz olmasıdır.

Ürün listesini genişletmek mümkündür. Ürünün alış fiyatını (maliyet tutarını) girmek isteyebilirsiniz. Ürünleri kategorilere ayırabilirsiniz (içecekler, tatlılar, çorbalar gibi).

Northwind Traders firmasındaki Ürünler tablosundaki alanlar ile veri türleri aşağıdaki şekilde gösterilmektedir.

Sağlayıcı olarak tanımlanan kavram, tedarikçi karşılığı olarak kullanılmıştır. İkinci satırdaki anahtar işareti No alan adının Birincil Anahtar, yani o alana aynı iki sayının girilemeyeceğini göstermektedir.

Aşağıdaki şekil ise Ürünler tablosunun veri sayfası görünümünü göstermektedir.

İlişkiler

Ürünler tablosunun ilişkilerini incelediğimizde Birincil Anahtar No alan adının 3 ayrı tablo ile ilişkilendirildiği görülmektedir.

İlişkilerin gösterildiği arayüzdeki pencerelerin yerleri, anlaşılmasını kolaylaştırmak açısından tarafımızdan değiştirilerek aşağıdaki görüntü alındı.

Ürünler penceresinin sağında ve solunda görülen 1 sayısına bağlı çizgileri takip ettiğimizde, ilişkilendirilen tabloların Satınalma Siparişi Ayrıntıları, Stok Hareketleri ve Sipariş Ayrıntıları tabloları olduğu anlaşılmaktadır.

Sorgular

Programlar (yazılımlar) veri tabanı tabloları arasındaki ilişkileri kullanarak, adına sorgu denilen yeni tablolar üretirler.

Bu sorgular aynen bir tablo gibi davranırlar, ancak sorgulara doğrudan veri giremezsiniz. Tablolara girilen veriler sorgulardaki verileri de etkiler.

Aşağıda Northwind Traders firmasının Tarihe Göre Ürün Satış Toplamı adlı sorgusunda kullanılan tabloların ilişkileri gösterilmektedir.

Siparişler tablosundaki Sipariş No (Birincil Anahtar), Sipariş Ayrıntıları tablosundaki Sipariş No ile, Ürünler tablosundaki No (Birincil Anahtar), yine Sipariş Ayrıntıları tablosundaki Ürün No ile ilişkilidir.

Bu 3 tablonun bir karışımı karşımıza sorgu olarak çıkmaktadır.

Bu basit sorguda 3 sütun bulunmaktadır. Ürünler tablosundan Ürün Adı, Siparişler tablosundan Spariş Tarihi ve Birim Fiyat ile Miktar’ın çarpımı ile oluşturulmuş Tutar sütunu…

Bu 3 sütunun oluşturduğu sorgu tablo gibi davransalar da aslında arka planda çalışan bir yazılımın sonucudur. Bu yazılım SQL (structured query language-yapısal sorgulama dili) olarak adlandırılır.

Tarihe göre Ürün Satışları Toplamı sorgusunun SQL söz dizimi şu şekildedir:

SELECT [Ürünler].[Ürün Adı], [Siparişler].[Sipariş Tarihi], Sum([Birim Fiyat]*[Miktar]) AS Tutar

FROM Siparişler INNER JOIN (Ürünler INNER JOIN [Sipariş Ayrıntıları] ON [Ürünler].[No]=[Sipariş Ayrıntıları].[Ürün No]) ON [Siparişler].[Sipariş No]=[Sipariş Ayrıntıları].[Sipariş No]

GROUP BY [Ürünler].[Ürün Adı], [Siparişler].[Sipariş Tarihi], [Ürünler].[Ürün Adı]

ORDER BY Sum([Birim Fiyat]*[Miktar]) DESC , [Ürünler].[Ürün Adı];

Sorguları Anlamak

İşyerlerinde kullanılan bilgisayarlarda MS Excel varsa MS Access programı da yüklüdür. Fakat bu programın kullanılma oranı Excel ile mukayese edilemeyecek kadar azdır.

Sorguları daha iyi anlamak için de Access’teki tabloları Excel’e aktarıp, verileri orada çözmeye çalışacağız.

Aşağıdaki Excel görüntüsü Sipariş Ayrıntıları tablosunun bir kesitidir.

Sipariş No ve Ürün No alanları Siparişler ve Ürünler tablolarının birincil anahtarlarıdır. Renklendirmeler anlaşılması kolay olsun diye tarafımdan yapılmıştır. Ürünler ve Siparişler tablosunu da Excel’e aktardıktan sonra sayfalar şu şekilde oluştur:

Bu 3 tablodan 3 sütunlu bir sorguyu bildik Excel işlev ya da yöntemleri ile oluşturabilir miyiz?

Sipariş_Ayrıntıları sayfasının K1, L1 ve M1 hücrelerine, Access sorgusundaki alan adları yazıldı.

K2 hücresine; =DÜŞEYARA([@[Ürün No]];TblÜrünler[[No]:[Ürün Adı]];3;0),

L2 hücresine; =DÜŞEYARA([@[Sipariş No]];TblSiparişler[[Sipariş No]:[Sipariş Tarihi]];4;0),

M2 hücresine; =[@Miktar]*[@[Birim Fiyat]] formülleri yazıldı.

Tutar sütununa göre büyükten küçüğe sıralama yapıldı.

Excel’deki tablo sonuçları Access’teki sorgu sonuçları ile karşılaştırıldığında, soldaki şeklin son satırındaki 24 Mart 2006 tarihli Kahve ile ilk satırdaki aynı tarihli kahve sipariş tutarları toplamı, sorgudaki ilk satırdaki kahve sipariş tutarına eşittir.

Excel’de, Access’teki sonucun aynısına ulaşmak elbette mümkündür. Bu sonuca ulaşmak için yeni sayfa ekleyip oralara farklı formüller yazabilirsiniz. Bu formüllerden biri ETOPLA() olabilir. Fakat tablolar öyle karmaşık bir hal alır ki, tabloyu oluşturanlar bile bazen içinden çıkamayabilirler.

Power Pivot

Excel’de bir tablodaki verilerden Pivot tablo aracıyla istediğimiz şekilde raporlar oluşturabilmekteyiz.

2 ya da daha fazla tablonun olması halinde ise ETOPLA(), DÜŞEYARA() gibi işlevlerle diğer tablolardan elde edilen verileri tek tabloda toplayıp Pivot Tablo ile raporlar elde edilebiliyordu.

Excel’in Power Pivot özelliği ile tablolar arasında ilişkiler kurulabilmektedir.

Power Pivot sekmesi menülerde (şeritte) yer almıyorsa, Şeridi Özelleştir ile Power Pivot’u ekleyebilirsiniz. Tabloları aktarmak için, Power Pivot sekmesindeki Veri Modeline Ekle simgesini kullanabilirsiniz.

Tablolar Excel’e benzer bir yapıda görülmektedir.

Aşağıdaki şekil Power Pivot ile oluşturulmuş ilişkileri göstermektedir. Bu ilişki Access’teki ilişkinin aynısıdır.

İlişkileri oluşturduktan sonra Pivot Tablo oluşturmaya kalktığınızda Excel’deki PivotTable Alanları bölmesinde 3 tablonun yer aldığını görürsünüz.

TblÜrünler tablo adının solundaki > işaretine tıklayıp listeden Ürün Adı’nı seçin.

TblSiparişler tablosundan Sipariş Tarihi’ni seçin. Ürün adı ve Sipariş Tarihi Satırlar bölümünde yer almıyorsa, sürükleyerek Satırlar bölümüne taşıyın.

TblSipariş_Ayrıntıları tablosundaki Birim Fiyat ve Miktar alan adlarını Değerler bölümüne sürükleyip bırakın. Excel’deki Pivot Tablomuz aşağıdaki gibidir.

Access’teki sorguda Birim Fiyat ile Miktar’ın çarpımının oluşturduğu Tutar sütununu Excel’de yine bildik yöntemlerle ya da başka yöntemlerle oluşturmak mümkündür.

Power Pivot ile Hesaplama

Power Pivot’ta Excel hücrelerinde olduğu gibi formül yazılabilmektedir.

Excel’e dönüp Pivot Table alanlarındaki bölmeye eklenen Tutar alanını Pivot Tabloya eklediğinizde Access’teki sorgunun sonuçlarını elde etmiş olduk.

24 Mart 2006 tarihindeki Kahve siparişlerinin Access’teki sorguda olduğu gibi 14.950 TL olduğu görülmektedir.