Excel'de milyonlarca satır içeren veri modelleri oluşturabilir ve ardından bu modellerde güçlü veri analizi gerçekleştirebilirsiniz. Veri modelleri, aynı çalışma kitabındaki herhangi bir sayıda PivotTable, grafik ve Power View görselleştirmesini desteklemek için Power Pivot eklentisiyle veya eklentisi olmadan oluşturulabilir.
Excel'de kolayca büyük veri modelleri oluşturabilmenize rağmen, oluşturmamak için birkaç neden vardır. İlk olarak, çok sayıda tablo ve sütun içeren büyük modeller çoğu analiz için fazlalıktır ve hantal bir Alan Listesi oluşturur. İkinci olarak, büyük modeller değerli bellek kullanır ve aynı sistem kaynaklarını paylaşan diğer uygulamaları ve raporları olumsuz etkiler. Son olarak, Microsoft 365 'de hem SharePoint Online hem de Excel Web App, Excel dosyasının boyutunu 10 MB ile sınırlar. Milyonlarca satır içeren çalışma kitabı veri modellerinde 10 MB sınırıyla oldukça hızlı bir şekilde karşılaşırsınız. Bkz. Veri Modeli belirtimi ve sınırları.
Bu makalede, daha kolay ve daha az bellek kullanan sıkı bir şekilde oluşturulmuş bir model oluşturmayı öğreneceksiniz. Verimli model tasarımında en iyi yöntemleri öğrenmek için zaman ayırdığınızda, excel'de, SharePoint Online'da, Office Web Apps Server'da Microsoft 365 veya SharePoint'te görüntülüyor olun, oluşturduğunuz ve kullandığınız herhangi bir model için yol açılır.
Ç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'ni indirin.
Bu makalenin başlıkları
Sıkıştırma oranları ve bellek içi analiz altyapısı
Excel'deki veri modelleri, verileri bellekte depolamak için bellek içi analiz altyapısını kullanır. Altyapı, depolama gereksinimlerini azaltmak için güçlü sıkıştırma teknikleri uygular ve sonuç kümesini özgün boyutunun bir parçası olana kadar küçültür.
Ortalama olarak, bir veri modelinin kaynak noktasındaki aynı verilerden 7 ile 10 kat daha küçük olmasını bekleyebilirsiniz. Örneğin, BIR SQL Server veritabanından 7 MB veri içeri aktarıyorsanız, Excel'deki veri modeli kolayca 1 MB veya daha az olabilir. Aslında elde edilen sıkıştırma derecesi öncelikle her sütundaki benzersiz değerlerin sayısına bağlıdır. Ne kadar benzersiz değerler olursa, bunları depolamak için o kadar fazla bellek gerekir.
Neden sıkıştırma ve benzersiz değerlerden bahsediyoruz? Bellek kullanımını en aza indiren verimli bir model oluşturmak, sıkıştırmayı en üst düzeye çıkarmaktan ibarettir ve bunu yapmanın en kolay yolu, özellikle de bu sütunların çok sayıda benzersiz değer içermesi durumunda gerçekten ihtiyacınız olmayan tüm sütunlardan kurtulmaktır.
Not: Tek tek sütunlar için depolama gereksinimlerindeki farklar çok büyük olabilir. Bazı durumlarda, çok sayıda benzersiz değer içeren bir sütun yerine az sayıda benzersiz değer içeren birden çok sütun olması daha iyidir. Datetime iyileştirmeleri bölümü bu tekniği ayrıntılı olarak kapsar.
Yetersiz bellek kullanımı için mevcut olmayan bir sütunu hiçbir şey yener
Bellek açısından en verimli sütun, ilk etapta hiç içeri aktarmadığınız sütundur. Verimli bir model oluşturmak istiyorsanız her sütuna bakın ve gerçekleştirmek istediğiniz analize katkıda bulunup bulunmadığını kendinize sorun. Yoksa veya emin değilseniz, dışarıda bırakın. Daha sonra ihtiyacınız olursa istediğiniz zaman yeni sütunlar ekleyebilirsiniz.
Her zaman dışlanması gereken iki sütun örneği
İlk örnek, veri ambarından kaynaklanan verilerle ilgilidir. Veri ambarında, ambardaki verileri yükleyen ve yenileyen ETL işlemlerinin yapıtlarını bulmak yaygındır. Veriler yüklendiğinde "tarih oluşturma", "güncelleştirme tarihi" ve "ETL çalıştırması" gibi sütunlar oluşturulur. Modelde bu sütunların hiçbiri gerekli değil ve verileri içeri aktardığınızda seçimi kaldırılmalıdır.
İkinci örnek, bir olgu tablosunu içeri aktarırken birincil anahtar sütununun atlanmasıdır.
Olgu tabloları da dahil olmak üzere birçok tablonun birincil anahtarları vardır. Müşteri, çalışan veya satış verileri içeren tablolar gibi çoğu tablo için, modelde ilişkiler oluşturmak için kullanabilmeniz için tablonun birincil anahtarını kullanmanız gerekir.
Olgu tabloları farklıdır. Olgu tablosunda, birincil anahtar her satırı benzersiz olarak tanımlamak için kullanılır. Normalleştirme amacıyla gerekli olsa da, yalnızca bu sütunların analiz için kullanılmasını veya tablo ilişkileri kurmasını istediğiniz bir veri modelinde daha az yararlıdır. Bu nedenle, bir olgu tablosundan içeri aktarırken birincil anahtarını eklemeyin. Olgu tablosundaki birincil anahtarlar modelde muazzam miktarda alan tüketir, ancak ilişki oluşturmak için kullanılamadıklarından hiçbir fayda sağlamaz.
Not: Veri ambarlarında ve çok boyutlu veritabanlarında çoğunlukla sayısal verilerden oluşan büyük tablolar genellikle "olgu tabloları" olarak adlandırılır. Olgu tabloları genellikle kurumsal birimlere, ürünlere, pazar segmentlerine, coğrafi bölgelere vb. hizalanmış satış ve maliyet veri noktaları gibi iş performansı veya işlem verilerini içerir. Olgu tablosundaki iş verilerini içeren veya diğer tablolarda depolanan verilere çapraz başvuru yapmak için kullanılabilecek tüm sütunlar, veri analizini desteklemek için modele eklenmelidir. Dışlamak istediğiniz sütun olgu tablosunun birincil anahtar sütunudur ve bu sütun yalnızca olgu tablosunda bulunan ve başka hiçbir yerde olmayan benzersiz değerlerden oluşur. Olgu tabloları çok büyük olduğundan, model verimliliğindeki en büyük kazançlardan bazıları olgu tablolarındaki satırların veya sütunların dışlanmasından türetilir.
Gereksiz sütunları dışlama
Verimli modeller yalnızca çalışma kitabınızda gerçekten ihtiyacınız olan sütunları içerir. Modele hangi sütunların dahil olduğunu denetlemek istiyorsanız, Verileri Excel'deki "Verileri İçeri Aktar" iletişim kutusu yerine içeri aktarmak için Power Pivot eklentisindeki Tablo İçeri Aktarma Sihirbazı'nı kullanmanız gerekir.
Tablo içeri aktarma Sihirbazı'nı başlattığınızda, hangi tabloların içeri aktarılacağını seçersiniz.
Her tablo için Önizleme & Filtre düğmesine tıklayabilir ve tablonun gerçekten ihtiyacınız olan bölümlerini seçebilirsiniz. Önce tüm sütunların işaretini kaldırmanızı ve ardından analiz için gerekli olup olmadıklarını göz önünde bulundurarak istediğiniz sütunları denetlemeye devam etmeniz önerilir.
Yalnızca gerekli satırları filtrelemeye ne dersin?
Şirket veritabanlarındaki ve veri ambarlarındaki birçok tablo, uzun süreler içinde birikmiş geçmiş verileri içerir. Ayrıca, ilgilendiğiniz tabloların işletmenin belirli analizleriniz için gerekli olmayan alanlarıyla ilgili bilgiler içerdiğini de fark edebilirsiniz.
Tablo İçeri Aktarma sihirbazını kullanarak geçmiş veya ilgisiz verileri filtreleyebilir ve böylece modelde çok fazla alan kaydedebilirsiniz. Aşağıdaki görüntüde, gerekmeyen geçmiş veriler hariç olmak üzere yalnızca geçerli yılın verilerini içeren satırları almak için bir tarih filtresi kullanılır.
Sütuna ihtiyacımız olursa ne olacak? Hala alan maliyetini düşürebilir miyiz?
Bir sütunu sıkıştırma için daha iyi bir aday yapmak için uygulayabileceğiniz birkaç teknik daha vardır. Sütunun sıkıştırmayı etkileyen tek özelliğinin benzersiz değerlerin sayısı olduğunu unutmayın. Bu bölümde, bazı sütunların benzersiz değer sayısını azaltmak için nasıl değiştirilebileceğini öğreneceksiniz.
Datetime sütunlarını değiştirme
Çoğu durumda, Datetime sütunları çok fazla yer kaplar. Neyse ki, bu veri türü için depolama gereksinimlerini azaltmanın çeşitli yolları vardır. Teknikler sütunu nasıl kullandığınıza ve SQL sorguları oluşturmadaki konfor düzeyinize bağlı olarak değişir.
Datetime sütunları bir tarih bölümü ve saat içerir. Bir sütuna ihtiyacınız olup olmadığını kendinize sorduğunuzda, aynı soruyu bir Datetime sütunu için birden çok kez sorun:
-
Zaman bölümüne ihtiyacım var mı?
-
Saat düzeyinde zaman bölümüne ihtiyacım var mı? tutanak? Saniye? Milisaniye?
-
Aralarındaki farkı hesaplamak veya verileri yıla, aya, çeyreğe vb. göre toplamak istediğim için birden çok Datetime sütunum mu var?
Bu soruların her birini nasıl yanıtladığınız, Datetime sütunuyla ilgilenme seçeneklerinizi belirler.
Bu çözümlerin tümü bir SQL sorgusunun değiştirilmesini gerektirir. Sorgu değişikliğini kolaylaştırmak için her tablodaki en az bir sütunu filtrelemeniz gerekir. Bir sütunu filtreleyerek, sorgu derlemesini kısaltılmış biçimden (SELECT *) tam sütun adları içeren ve değiştirilmesi çok daha kolay olan bir SELECT deyimiyle değiştirirsiniz.
Şimdi sizin için oluşturulan sorgulara göz atalım. Tablo Özellikleri iletişim kutusundan Sorgu düzenleyicisine geçebilir ve her tablo için geçerli SQL sorgusunu görebilirsiniz.
Tablo Özellikleri'nden Sorgu Düzenleyicisi'ni seçin.
Sorgu Düzenleyicisi, tabloyu doldurmak için kullanılan SQL sorgusunu gösterir. İçeri aktarma sırasında herhangi bir sütunu filtrelediyseniz, sorgunuz tam sütun adlarını içerir:
|
Buna karşılık, herhangi bir sütunun işaretini kaldırmadan veya herhangi bir filtre uygulamadan tabloyu tamamen içeri aktardıysanız, sorguyu "Seçim * kaynağı" olarak görürsünüz; bu da değiştirmek daha zor olacaktır:
|
SQL sorgusunu değiştirme
Artık sorguyu nasıl bulabileceğinizi bildiğinize göre modelinizin boyutunu daha da küçültmek için sorguyu değiştirebilirsiniz.
-
Para birimi veya ondalık veri içeren sütunlar için, ondalıklara ihtiyacınız yoksa, ondalıklardan kurtulmak için bu söz dizimini kullanın:
"SELECT ROUND([Decimal_column_name],0)... .”
Centlere ihtiyacınız varsa ancak cent kesirlerine ihtiyacınız yoksa, 0 değerini 2 olarak değiştirin. Negatif sayılar kullanıyorsanız, birimlere, onlarcaya, yüzlerceye vb. yuvarlayabilirsiniz.
-
dbo adlı bir Datetime sütununa sahipseniz. Bigtable' ı seçin. [Tarih Saati] ve Saat bölümüne ihtiyacınız yok, saatten kurtulmak için söz dizimini kullanın:
"SELECT CAST (dbo. Bigtable' ı seçin. [Tarih saat] tarih olarak) AS [Tarih saat]) "
-
dbo adlı bir Datetime sütununa sahipseniz. Bigtable' ı seçin. [Tarih Saati] ve hem Tarih hem de Saat bölümlerine ihtiyacınız varsa, sql sorgusunda tek bir Datetime sütunu yerine birden çok sütun kullanın:
"SELECT CAST (dbo. Bigtable' ı seçin. [Tarih Saat] tarih olarak ) AS [Tarih Saat],
datepart(ss, dbo. Bigtable' ı seçin. [Tarih Saat]) olarak [Tarih Saat Saatleri],
datepart(mi, dbo. Bigtable' ı seçin. [Tarih Saat]) [Tarih Saat Dakikaları] olarak,
datepart(ss, dbo). Bigtable' ı seçin. [Tarih Saat]) [Tarih Saat Saniyeleri] olarak,
datepart(ms, dbo. Bigtable' ı seçin. [Tarih Saat]) olarak [Tarih Saat Milisaniye]"
Her bölümü ayrı sütunlarda depolamak için gereken sayıda sütun kullanın.
-
Saat ve dakika gerekiyorsa ve bunları tek seferlik sütun olarak birlikte tercih ediyorsanız, söz dizimini kullanabilirsiniz:
Timefromparts(datepart(hh, dbo. Bigtable' ı seçin. [Tarih Saati]), datepart(mm, dbo. Bigtable' ı seçin. [Tarih Saat])) as [Date Time HourMinute]
-
[Başlangıç Saati] ve [Bitiş Saati] gibi iki tarih saat sütununuza sahipseniz ve gerçekten ihtiyacınız olan, [Süre] adlı bir sütun olarak saniye cinsinden aralarındaki zaman farkıysa, her iki sütunu da listeden kaldırın ve şunları ekleyin:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
ss yerine ms anahtar sözcüğünü kullanırsanız süreyi milisaniye cinsinden alırsınız
Sütunlar yerine DAX hesaplanan ölçülerini kullanma
Daha önce DAX ifade diliyle çalıştıysanız, hesaplanmış sütunların modeldeki başka bir sütuna göre yeni sütunlar türetmek için kullanıldığını, hesaplanmış ölçülerin modelde bir kez tanımlandığını, ancak yalnızca PivotTable'da veya başka bir raporda kullanıldığında değerlendirildiğini zaten biliyor olabilirsiniz.
Bellek tasarrufu tekniklerinden biri, normal veya hesaplanan sütunları hesaplanan ölçülerle değiştirmektir. Klasik örnek Birim Fiyat, Miktar ve Toplam'dır. Üçünü birden kullanıyorsanız, yalnızca iki tane koruyarak ve DAX kullanarak üçüncü olanı hesaplayarak alandan tasarruf edebilirsiniz.
Hangi 2 sütunu tutmanız gerekir?
Yukarıdaki örnekte Quantity ve Unit Price değerini koruyun. Bu ikisi Total değerinden daha az değere sahiptir. Toplam'ı hesaplamak için aşağıdaki gibi bir hesaplanmış ölçü ekleyin:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Hesaplanmış sütunlar, modelde yer kaplayan normal sütunlar gibidir. Buna karşılık, hesaplanan ölçüler anında hesaplanır ve yer kaplamaz.
Sonuç
Bu makalede, bellek açısından daha verimli bir model oluşturmanıza yardımcı olabilecek çeşitli yaklaşımlardan bahsettik. Veri modelinin dosya boyutunu ve bellek gereksinimlerini azaltmanın yolu, genel sütun ve satır sayısını ve her sütunda görünen benzersiz değerlerin sayısını azaltmaktır. Ele aldığımız bazı teknikler şunlardır:
-
Sütunları kaldırmak elbette alandan tasarruf etmenin en iyi yoludur. Gerçekten hangi sütunlara ihtiyacınız olduğunu belirleyin.
-
Bazen bir sütunu kaldırabilir ve tabloda hesaplanan ölçüyle değiştirebilirsiniz.
-
Tablodaki tüm satırlara ihtiyacınız olmayabilir. Tablo İçeri Aktarma Sihirbazı'nda satırları filtreleyebilirsiniz.
-
Genel olarak, tek bir sütunu birden çok ayrı parçaya ayırmak, sütundaki benzersiz değerlerin sayısını azaltmanın iyi bir yoludur. Parçaların her biri az sayıda benzersiz değere sahip olur ve birleştirilmiş toplam özgün birleşik sütundan daha küçük olur.
-
Çoğu durumda, raporlarınızda dilimleyici olarak kullanmak için ayrı parçalara da ihtiyacınız vardır. Uygun olduğunda, Saatler, Dakikalar ve Saniyeler gibi bölümlerden hiyerarşiler oluşturabilirsiniz.
-
Çoğu zaman, sütunlar da ihtiyacınız olandan daha fazla bilgi içerir. Örneğin, bir sütunun ondalıkları depoladuğunu, ancak tüm ondalıkları gizlemek için biçimlendirme uyguladığınızı varsayalım. Yuvarlama, sayısal bir sütunun boyutunu küçültmede çok etkili olabilir.
Artık çalışma kitabınızın boyutunu küçültmek için yapabileceklerinizi yaptığınıza göre, Çalışma Kitabı Boyutu İyileştiricisi'ni de çalıştırmayı göz önünde bulundurun. Excel çalışma kitabınızı inceler ve mümkünse daha fazla sıkıştırır. Çalışma Kitabı Boyutu İyileştiricisi'ni indirin.
İ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
