Yeni Excel 2013 veya sonraki bir modelde, milyonlarca satır içeren veri modelleri oluşturabilir ve bu modellere karşı güçlü veri çözümlemeleri gerçekleştirebilirsiniz. Aynı çalışma kitabındaki herhangi bir sayıda PivotTable Power Pivot grafiği ve görselleştirmeyi desteklemek için veri modelleri Power View eklentiyle veya eklenti olmadan oluşturulabilir.
Not: Bu makalede, Excel 2013'teki veri modelleri açıklanır. Bununla birlikte, Excel 2013'te kullanıma sunulan veri modelleme ve Power Pivot özelliklerinin aynıları Excel 2016 için de geçerlidir. Bu sürümler arasında çok az fark Excel.
Büyük veri modellerini aynı Excel, ancak oluşturmamanın çeşitli nedenleri vardır. İlk olarak, çok sayıda tablo ve sütun içeren büyük modeller çoğu analiz için aşırı kalabalık olur ve zahmetli bir Alan Listesi oluşturur. İkinci, büyük modeller değerli belleği kullanır ve bu da aynı sistem kaynaklarını kullanan diğer uygulamaları ve raporları olumsuz etkiler. Son olarak, Microsoft 365, SharePoint Online ve Excel Web App'te bir Excel dosyanın boyutunu 10 MB ile sınırlandırır. Milyonlarca satır içeren çalışma kitabı veri modellerinde, 10 MB sınırına hemen ulaşabilirsiniz. Bkz. Veri Modeli belirtimi ve sınırlamaları.
Bu makalede, birlikte daha kolay çalışmak ve daha az bellek kullanan, sıkı bir şekilde oluşturulmuş bir modelin nasıl inşa edilmiş olduğunu öğrenirsiniz. Verimli model tasarımında en iyi yöntemleri öğrenmek için zaman almak, 2013'te, Excel 2013'te, Microsoft 365 SharePoint Online'da, bir Office Web Apps Sunucusunda veya SharePoint 2013'te görüntülerken, sizin oluşturmanız ve kullanıyor olun her modele zaman yardımcı olur.
Çalışma Kitabı Boyutu Ayarlayıcısı'nı çalıştırmayı da düşünebilirsiniz. Excel çalışma kitabınızı inceler ve mümkünse daha fazla sıkıştırır. Çalışma Kitabı Boyutu İyileştiricisi'ne tıklayın.
Bu makalenin başlıkları
Sıkıştırma oranları ve bellek içinde çözümleme altyapısı
Aşağıdaki veri Excel bellekte depolamak için bellek çözümleme altyapısını kullanır. Altyapı, depolama gereksinimlerini azaltmak için güçlü sıkıştırma tekniklerini uygulamaktadır; sonuç kümesi özgün boyutunun çok küçük bir parçasına gelene kadar küçültür.
Ortalama olarak, bir veri modelinin ilk noktasında aynı veriden 7 - 10 kat daha küçük olmasını beklebilirsiniz. Örneğin, bir SQL Server veritabanından 7 MB'lık verileri içeri aktarıyorsanız, Excel veya daha küçük bir veri modeli olabilir. Gerçekten elde edilen sıkıştırma derecesi, öncelikle her sütundaki benzersiz değerlerin sayısına bağlıdır. Benzersiz değerler ne kadar fazlasa, bunları depolamak için o kadar çok bellek gerekir.
Sıkıştırma ve benzersiz değerler hakkında neden konuşuyoruz? Bellek kullanımını en aza indiren verimli bir model oluşturmanın en kolay yolu sıkıştırma en yüksek düzeye çıkarmaktır ve bunu yapmanın en kolay yolu aslında aslında ihtiyacınız olmayan tüm sütunları, özellikle de bu sütunlar çok sayıda benzersiz değer içeren sütunları çıkarmaktır.
Not: Tek tek sütunların depolama gereksinimleri arasındaki farklar çok büyük olabilir. Bazı durumlarda, benzersiz değerleri yüksek sayıda benzersiz değer içeren bir sütun yerine, düşük sayıda benzersiz değer içeren birden çok sütunun olması daha iyi olabilir. Datetime iyileştirmeleri bölümünde bu teknik ayrıntılı olarak ele alınmaktadır.
Yetersiz bellek kullanımı için var olmayan bir sütunda hiçbir şey yok
En verimli bellek etkin sütunu, ilk yerinde hiç içe aktarmadınız sütun olur. Verimli bir model oluşturmak için, her sütuna bakın ve yapmak istediğiniz çözümlemeye katkıda bulunarak katkıda bulunıp katkıda bulun olmadığını kendinize sorun. Yoksa veya emin değilsanız, bu sizin için önemli değil. Gerekirse daha sonra istediğiniz zaman yeni sütunlar ekleyebilirsiniz.
Her zaman hariç tutulacak iki sütun örneği
İlk örnek, veri ambarından gelen verilerle ilgilidir. Veri ambarında, depodaki verileri yükleyin ve yenileyin olan ETL işlemlerinin yapıtlarını bulmak yaygındır. Veri yüklendiğinde "tarih oluştur", "güncelleştirme tarihi" ve "ETL çalıştırma" gibi sütunlar oluşturulur. Modelde bu sütunların hiçbiri gerekli değildir ve verileri içeri aktarıldığında seçilmeleri gerekir.
İkinci örnekte, bir bilgi tablosu içeri aktarıldı sırada birincil anahtar sütununu yoklama işlemi yer alır.
Bilgi tabloları da dahil olmak üzere birçok tabloda birincil anahtarlar vardır. Müşteri, çalışan veya satış verileri içeren tablolar gibi çoğu tablo için, tablonun birincil anahtarını kullanmak gerekir; böylece modelde ilişkiler oluşturmak için onu kullanabilirsiniz.
Bilgi tabloları farklıdır. Bir bilgi tablosunda birincil anahtar, her satırı benzersiz bir şekilde tanımlamak için kullanılır. Normalleştirme amacıyla gerekli olduğu gibi, veri modelinde yalnızca bu sütunların çözümleme amacıyla veya tablo ilişkisi kurmak için kullanılmaktadır. Bu nedenle, bir gerçek tablosundan içeri aktarma işlemi için birincil anahtarı dahil değildir. Bir bilgi tablosunda birincil anahtarlar modelde inanılmaz miktarda alan tüketir, ancak ilişkiler oluşturmak için kullanılamazlar.
Not: Veri depolarında ve çok boyutlu veritabanlarında, çoğunlukla sayısal verilerden oluşan büyük tablolar genellikle "bilgi tabloları" olarak adlandırılır. Bilgi tabloları tipik olarak, birleştirilmiş ve kurumsal birimler, ürünler, pazar segmentleri, coğrafi bölgeler gibi özelliklerle uyumlu olan satış ve maliyet verileri gibi iş performansı veya işlem verilerini içerir. İş verileri içeren veya başka tablolarda depolanan verilere çapraz başvuru için gerektirilen tüm sütunlar, veri çözümlemeyi desteklemek için modele ek kullanılmalıdır. Dışarıda tutmak istediğiniz sütun, yalnızca bilgi tablosunda bulunan ve başka hiçbir yerde olmayan benzersiz değerlerden oluşan, bilgi tablosu birincil anahtar sütunudur. Bilgi tabloları çok büyük olduğundan, model verimliliğinde en büyük kazançlardan bazıları bilgi tablolarının satırları veya sütunları hariç tutarak türetilen.
Gereksiz sütunları dışlama
Verimli modeller yalnızca çalışma kitabınızda gerçekten ihtiyacınız olacak sütunları içerir. Modele hangi sütunların dahil olduğunu denetim altına almak için, Power Pivot eklentisinde "Verileri İçeri Aktar" iletişim kutusu yerine verileri içeri aktarmanız için Power Pivot eklentisinde Tablo İçeri Aktarma Sihirbazı'nı Excel.
Tablo İçeri Aktarma Sihirbazı'nı başlatırken, hangi tabloların içeri aktarılacaklarını seçersiniz.
Her tablo için, Önizleme & düğmesine tıklayabilirsiniz ve tablonun gerçekten ihtiyacınız olan bölümlerini seçin. Öncelikle tüm sütunların işaretini kaldırın ve ardından çözümleme için gerekli olup olmadığını dikkate alarak istediğiniz sütunları denetlemeye devam edin.
Peki ya yalnızca gerekli satırları filtrelemek?
Şirket veritabanlarında ve veri depolarında yer alan birçok tablo, uzun süreler içinde birikimli geçmiş verileri içerir. Buna ek olarak, ilgilendiğiniz tabloların işletmeyle ilgili kendi özel çözümlemeniz için gerekli değildir alanlarıyla ilgili bilgiler içerdiğini de bulabilirsiniz.
Tablo İçeri Aktarma sihirbazını kullanarak geçmiş verileri veya ilgili olmayan verileri filtrenin dışında bir hale böylece modelde çok fazla yer tasarrufu sabilirsiniz. Aşağıdaki resimde, yalnızca geçerli yıldaki verileri içeren satırları (gerek olmayacak geçmiş verileri hariç) almak için tarih filtresi kullanılmıştır.
Sütuna ihtiyacımız olursa ne olur; alan maliyetini yine de azaltır miyiz?
Sütunu sıkıştırma için daha iyi bir aday yapmak için uygulayabilecek birkaç teknik daha vardır. Sütunun sıkıştırmayı etkileyen tek özelliğin benzersiz değerlerin sayısı olduğunu unutmayın. Bu bölümde, benzersiz değerlerin sayısını azaltmak için bazı sütunların nasıl değiştirilil olduğunu öğrenirsiniz.
Tarih Saat sütunlarını değiştirme
Birçok durumda, Tarih saat sütunları çok fazla yer alır. Neyse ki, bu veri türünün depolama gereksinimlerini azaltmanın çeşitli yolları vardır. Teknikler sütunu nasıl kullandığınıza ve daha rahat sorgular oluşturmak için SQL değişir.
Tarih saat sütunları bir tarih bölümü ve bir saat içerir. Bir sütuna ihtiyacınız olup olmadığını kendinize sorunca, Tarihsatır sütunu için aynı soruyu birden çok kez sorun:
-
Zaman parçasına ihtiyacım var mı?
-
Saat bölümü saat düzeyinde bana gerekiyor mu? dakikalar mı? saniye? mili saniye cinsinden mi?
-
Aralarındaki farkı hesaplamak veya verileri yıl, ay, üç ay gibi verilere göre toplamak istediğimden, birden çok TarihSatır sütunum var mı?
Bu sorulardan her birini nasıl yanıtlayabilirsiniz? Datetime sütunuyla ilgilenme seçenekleriniz belirler.
Bu çözümlerin hepsi, sorguda değişiklik SQL gerektirir. Sorgu değişikliğini kolaylaştırmak için, her tabloda en az bir sütuna filtre uygulamalısınız. Sütunu filtreleerek, sorgu yapısında değişiklik yapmak çok daha kolay olan, kısaltılmış bir biçimden (SELECT *) tam sütun adlarını içeren bir SELECT deyimine dönüştürmeniz gerekir.
Sizin için oluşturulan sorgulara bir göz at bakalım. Tablo Özellikleri iletişim kutusunda Sorgu düzenleyicisine geçebilirsiniz ve her tablonun geçerli SQL sorgusunu görüntüebilirsiniz.
Tablo Özellikleri'nde Sorgu Düzenleyicisi'ni seçin.
Sorgu Düzenleyicisi, tabloyu SQL için kullanılan sorguyu gösterir. İçeri aktarma sırasında herhangi bir sütuna filtre uygulamadıysanız, sorgunuz tam sütun adlarını içerir:
|
Buna karşılık, tabloyu tamamen içe aktardıysanız, hiçbir sütunun işaretini kaldırmış veya herhangi bir filtre uygulamamışsa, sorguyu "Select * from" olarak görüntülersiniz ve bu değiştirmek daha zor olur:
|
Varsayılan sorguyu SQL değiştirme
Artık sorguyu nasıl bula ilgili olduğunu biliyorsunuz, modelinizin boyutunu daha da azaltmak için sorguyu değiştirebilirsiniz.
-
Para birimi veya ondalık veri içeren sütunlar için, ondalık sayılara ihtiyacınız yoksa, bu söz dizimlerini kullanarak ondalık değerlerden kurtulun:
"YUVARLA SEÇ([Decimal_column_name],0)... .”
Kuruşa ihtiyacınız varsa ancak kuruş kesirlerini değil, 0'ın yerine 2'ye değiştirin. Negatif sayılar kullanırsanız, birimleri, onları, yüzlerce vb. yuvarlayabilirsiniz.
-
dbo adlı bir Datetime sütununu seçin. Büyüktam. [Tarih Saati] ile Saat bölümü kullanmak zorunda değilken, zaman söz dizimi kullanarak zamanları ortadan kaldırabilirsiniz:
"SELECT CAST (dbo. Büyüktam. [Tarih saat] tarih) AS [Tarih saati]) "
-
dbo adlı bir Datetime sütununu seçin. Büyüktam. [Tarih Saat] ile birlikte hem Tarih hem de Saat bölümlerine ihtiyacınız varsa, Tarih SQL sütunu yerine birden çok sütun kullanın:
"SELECT CAST (dbo. Büyüktam. [Tarih Saat] tarih olarak ) AS [Tarih Saati],
datepart(hh, dbo. Büyüktam. [Tarih Saati]) [Tarih Saat Saat] olarak,
datepart(mi, dbo. Büyüktam. [Tarih Saati]) olarak [Tarih Saat Dakikaları],
datepart(ss, dbo. Büyüktam. [Tarih Saati]) olarak [Tarih Saat Saniye],
datepart(ms, dbo. Büyüktam. [Tarih Saati]) as [Tarih Saati Milisaniye]"
Her bölümü ayrı sütunlarda depolamak için gereken sayıda sütun kullanın.
-
Saat ve dakikaya ihtiyacınız varsa ve bunları tek seferlik sütun olarak birlikte kullanmayı tercih ediyorsanız, şu söz dizimini kullanabilirsiniz:
Timefromparts(datepart(hh, dbo. Büyüktam. [Tarih Saati]), datepart(mm, dbo. Büyüktam. [Tarih Saati])) as [Date Time HourMinute]
-
[Başlangıç Saati] ve [Bitiş Saati] gibi iki tarihsatır sütununuz varsa ve aslında size gereken, [Süre] adlı bir sütun olarak saniyeler içinde bu sütunlar arasındaki zaman farkıdır, listeden her iki sütunu da kaldırın ve şunları ekleyin:
"datediff(ss,[Başlangıç Tarihi],[Bitiş Tarihi]) as [Süre]"
SS yerine ms anahtar sözcüğünü kullanırsanız, süre mili saniye cinsinden elde edersiniz
Sütunlar yerine DAX hesaplanan ölçülerini kullanma
DAHA önce DAX ifade diliyle çalıştıysanız, modelin diğer bazı sütunlarını temel alan yeni sütunları türerken, hesaplanan sütunların modelde tek bir kez tanımlandığı, ancak yalnızca PivotTable veya başka bir raporda kullanıldıklarında değerlendirilen hesaplanan sütunların önceden biliyor olabileceğiniz oldu.
Bellek tasarrufu tekniklerinden biri, normal veya hesaplanan sütunları hesaplanan ölçülerle değiştirmektir. Klasik örnek Birim Fiyatı, Miktar ve Toplam'dır. Bunların üçünü de kullanıyorsanız, yalnızca ikisini koruyarak ve daX kullanarak üçüncü hesabı hesaparak yer tasarrufu sabilirsiniz.
Hangi 2 sütunu tutmalı?
Yukarıdaki örnekte Miktar ve Birim Fiyat'a bakın. Bu ikisi Toplam'dan daha az değere sahiptir. Toplamı hesaplamak için şöyle bir hesaplanan ölçü ekleyin:
"Toplam Satışlar:=toplam('Satış Tablosu','Satış Tablosu'[Birim Fiyat]*'Satış Tablosu'[Miktar])"
Hesaplanan sütunlar, modelde yer alan normal sütunlara benzer. Buna karşılık, hesaplanan ölçüler o anda hesaplanır ve yer almayan bir durum olur.
Sonuç
Bu makalede, daha verimli bellek sağlayan bir model oluşturmanıza yardımcı olacak çeşitli yaklaşımlar hakkında konuştuk. Veri modelinin dosya boyutunu ve bellek gereksinimlerini azaltmanın yolu, tüm sütun ve satır sayısını ve her sütunda görünen benzersiz değer sayısını azaltmaktır. İşte ele allarımız gereken bazı teknikler:
-
Sütunları kaldırmak kuşkusuz yer tasarrufu sağlamanın en iyi yoludur. Gerçekten hangi sütunlara ihtiyacınız olduğuna karar verin.
-
Bazen bir sütunu kaldırabilir ve tablodaki hesaplanan ölçümle değiştirebilirsiniz.
-
Bir tablodaki tüm satırlara ihtiyacınız olabilir. Tablo İçeri Aktarma Sihirbazı'nda satırları filtreleebilirsiniz.
-
Genelde, tek bir sütunu birden çok farklı parçalara ayırarak sütundaki benzersiz değerlerin sayısını azaltmak için iyi bir yol sağlar. Parçaların her biri az sayıda benzersiz değere sahip olur ve birleştirilmiş toplam özgün birleştirilmiş sütundan daha küçük olur.
-
Birçok durumda, raporlarınız içinde dilimleyici olarak kullanmak için ayrı parçalara da ihtiyacınız vardır. Uygun olduğunda, Saat, Dakika ve Saniye gibi bölümlerin hiyerarşilerini oluşturabilirsiniz.
-
Sütunlarda çoğu zaman, ihtiyacınız olandan daha fazla bilgi bulunur. Örneğin, bir sütunda ondalıkların depolu olduğunu, ancak tüm ondalık sayıları gizlemek için biçimlendirme uygulandığını varsayalım. Yuvarlama, sayısal bir sütunun boyutunu azaltmada çok etkili olabilir.
Artık çalışma kitabınızı boyutunu azaltmak için neler yapsanız da çalışma Kitabı Boyutu İyileştiricisi'ne göz önünde bulundurabilirsiniz. Excel çalışma kitabınızı inceler ve mümkünse daha fazla sıkıştırır. Çalışma Kitabı Boyutu İyileştiricisi'ne tıklayın.
İlgili bağlantılar
Veri Modeli belirtimi ve sınırlamaları
Çalışma Kitabı Boyutu İyileştiricisi
Power Pivot: Excel'de güçlü veri çözümlemesi ve veri modelleme
