Excel'de, milyonlarca satır içeren veri modelleri oluşturabilir ve sonra bu modellerde güçlü veri çözümlemeleri gerçekleştirebilirsiniz. Veri modelleri, aynı çalışma kitabında 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 devasa veri modelleri oluşturabilseniz de, bunu yapmamanız için birkaç neden vardır. Birincisi, çok sayıda tablo ve sütun içeren büyük modeller çoğu çözümlemede aşırıya kaçar ve kullanışsız bir Alan Listesi oluşturur. İkincisi, büyük modeller değerli belleği kullanır ve bu da aynı sistem kaynaklarını paylaşan diğer uygulamaları ve raporları olumsuz etkiler. Son olarak, Microsoft 365'te hem SharePoint Online hem de Excel Web App, bir 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ını oldukça hızlı bir şekilde aşarsınız. Bkz. Veri Modeli belirtim ve sınırlamaları.
Bu makalede, üzerinde çalışması daha kolay olan ve daha az bellek kullanan, sıkı yapılanmış bir model oluşturmayı öğreneceksiniz. Verimli model tasarımındaki en iyi uygulamaları öğrenmek için zaman ayırmak, oluşturduğunuz ve kullandığınız herhangi bir model için (Excel'de, Microsoft 365 SharePoint Online'da, Office Web Apps Server'da veya SharePoint'te) görüntülediğiniz her model için ileride karşılığını verecektir.
Ç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 Ayarlayıcısı'nı indirin.
Bu makalede
Düşük bellek kullanımı için var olmayan bir sütunun yerini hiçbir şey tutamaz
Ya sütuna ihtiyacımız olursa; Yine de alan maliyetini azaltabilir miyiz?
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. Motor, depolama gereksinimlerini azaltmak için güçlü sıkıştırma teknikleri uygular ve bir sonuç kümesini orijinal boyutunun bir kısmına kadar küçültür.
Ortalama olarak, bir veri modelinin başlangıç noktasındaki aynı veriden 7 - 10 kat daha küçük olmasını bekleyebilirsiniz. Örneğin bir SQL ServerSQL Server veritabanından 7 MB'lık veriyi içeri aktarıyorsanız, Excel'deki veri modelinin boyutu kolayca 1 MB veya daha az olabilir. Gerçekte elde edilen sıkıştırma derecesi, öncelikle her sütundaki benzersiz değerlerin sayısına bağlıdır. Ne kadar çok benzersiz değer olursa, onları depolamak için o kadar fazla bellek gerekir.
Neden sıkıştırma ve benzersiz değerlerden bahsediyoruz? Çünkü bellek kullanımını en aza indiren verimli bir model oluşturmak tamamen sıkıştırmayı en üst düzeye çıkarma işlemidir ve bunu yapmanın en kolay yolu, özellikle de bu sütunlar çok sayıda benzersiz değer içeriyorsa, gerçekten ihtiyacınız olmayan sütunları kaldırmaktır.
Not
Tek tek sütunlar için depolama gereksinimlerindeki farklar çok büyük olabilir. Bazı durumlarda, benzersiz değer sayısı yüksek olan tek bir sütun yerine, benzersiz değer sayısı düşük olan birden çok sütun kullanmak daha iyidir. Tarih saat optimizasyonları ile ilgili bölüm bu tekniği ayrıntılı olarak ele alır.
Düşük bellek kullanımı için var olmayan bir sütunun yerini hiçbir şey tutamaz
Belleği en verimli kullanan sütun, daha önce hiç içeri aktarmadığınız sütundur. Verimli bir model oluşturmak istiyorsanız, her sütuna bakın ve yapmak istediğiniz analize katkıda bulunup bulunmadığını kendinize sorun. Desteklemiyorsa veya emin değilseniz dışarıda bırakın. Daha sonra gerek duyduğunuzda istediğiniz zaman yeni sütunlar ekleyebilirsiniz.
Her zaman dışlanması gereken iki sütun örneği
İlk örnek, bir veri ambarından kaynaklanan verilerle ilgilidir. Bir veri ambarında, ambardaki verileri yükleyen ve yenileyen ETL işlemlerinin yapıtlarını bulmak yaygındır. Veriler yüklendiğinde "oluşturma tarihi", "güncelleştirme tarihi" ve "ETL çalıştırma" gibi sütunlar oluşturulur. Modelde bu sütunların hiçbirine gerek yoktur ve verileri içeri aktarırken bu sütunların seçimi kaldırılmalıdır.
İkinci örnekte, olgu tablosunu içeri aktarırken birincil anahtar sütununun atlanması yer alır.
Bilgi tabloları da dahil olmak üzere birçok tablonun birincil anahtarları vardır. Müşteri, çalışan veya satış verilerini içeren tabloların çoğu için, modelde ilişkiler oluştururken bu tabloyu kullanabilmek için tablonun birincil anahtarını istersiniz.
Bilgi tabloları farklıdır. Bir olgu tablosunda, birincil anahtar her satırı benzersiz olarak tanımlamak için kullanılır. Normalleştirme amacıyla gerekli olsa da, çözümleme veya tablo ilişkileri kurmak amacıyla yalnızca bu sütunların kullanılmasını istediğiniz bir veri modelinde daha az kullanışlıdır. Bu nedenle, bir olgu tablosundan içe aktarırken, birincil anahtarını dahil etmeyin. Bir olgu tablosundaki birincil anahtarlar modelde çok büyük miktarda alan kaplar, ancak ilişki oluşturmak için kullanılamadıkları için hiçbir fayda sağlamazlar.
Not
Veri ambarlarında ve çok boyutlu veritabanlarında, çoğunlukla sayısal verilerden oluşan büyük tablolara genellikle "olgu tabloları" denir. Olgu tabloları tipik olarak, toplanmış ve kuruluş birimlerine, ürünlere, pazar segmentlerine, coğrafi bölgelere vb. hizalanmış satış ve maliyet veri noktaları gibi iş performansı veya işlem verilerini içerir. Bir olgu tablosunda iş verileri içeren veya diğer tablolarda depolanan verilere çapraz başvuru yapmak için kullanılabilen tüm sütunlar, veri çözümlemesini desteklemek için modele dahil edilmelidir. Dışarıda bırakmak istediğiniz sütun, olgu tablosunun yalnızca olgu tablosunda bulunan ve başka hiçbir yerde bulunmayan benzersiz değerlerden oluşan birincil anahtar sütunudur. Olgu tabloları çok büyük olduğundan, model verimliliğindeki en büyük kazanımlardan bazıları olgu tablolarından satırların veya sütunların dışlanmasından elde edilir.
Gereksiz sütunları dışlama
Verimli modeller yalnızca çalışma kitabınızda gerçekten gereksinim duyacağınız sütunları içerir. Modele hangi sütunların ekleneceğini denetlemek istiyorsanız, verileri içeri aktarmak için Excel'deki "Verileri İçeri Aktar" iletişim kutusu yerine Power Pivot eklentisindeki Tablo İçeri Aktarma Sihirbazı'nı kullanmanız gerekir.
Tablo İç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üğmesini tıklatabilir 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 sonra çözümleme için gerekli olup olmadıklarını düşündükten sonra istediğiniz sütunları denetlemeye devam etmenizi öneririz.
Yalnızca gerekli satırları filtrelemeye ne dersiniz?
Şirket veritabanlarında ve veri ambarlarında yer alan birçok tablo, uzun dönemler boyunca birikmiş geçmiş verilerini içerir. Buna ek olarak, ilgilendiğiniz tabloların, belirli çözümlemeleriniz için gerekli olmayan iş alanlarıyla ilgili bilgiler içerdiğini de fark edebilirsiniz.
Tablo İçeri Aktarma Sihirbazı'nı kullanarak, geçmiş veya ilişkisiz verilere filtre uygulayabilir ve böylece modelde çok fazla alan kazanabilirsiniz. Aşağıdaki resimde tarih filtresi, yalnızca geçerli yıla ait verileri içeren satırları getirmek için kullanılır; gerekmeyen geçmiş verileri dışarıda bırakır.
Ya sütuna ihtiyacımız olursa; Yine de alan maliyetini azaltabilir miyiz?
Sütunu sıkıştırma için daha iyi bir aday haline getirmek üzere uygulayabileceğiniz birkaç ek teknik 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, benzersiz değerlerin sayısını azaltmak için bazı sütunların nasıl değiştirilebileceğini öğreneceksiniz.
Tarihsaat sütunlarını değiştirme
Çoğu durumda, Tarih saat 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ştururken rahatlık düzeyinize bağlı olarak değişir.
Tarihsaat sütunları bir tarih bölümü ve bir saat içerir. Bir sütuna ihtiyacınız olup olmadığını kendinize sorduğunuzda, aynı soruyu Tarihsaat sütunu için birkaç kez sorun:
- Zaman kısmına ihtiyacım var mı?
- Saat düzeyinde saat kısmına ihtiyacım var mı? , dakika? , saniye? , milisaniye?
- Birden çok Datetime sütunum var mı, bunlar arasındaki farkı hesaplamak mı yoksa verileri yıla, aya, üç aylık döneme vb. göre toplamak mı?
Bu sorulardan her birine verdiğiniz yanıtlar, Tarih Saat sütunuyla ilgili seçeneklerinizi belirler.
Bu çözümlerin tümü SQL sorgusunun değiştirilmesini gerektirir. Sorgu değişikliğini kolaylaştırmak için, her tabloda en az bir sütunu filtrelemelisiniz. Sütunu filtreleyerek, sorgu yapısını kısaltılmış biçimden (SELECT *) değiştirmek, değiştirmesi çok daha kolay olan tam sütun adlarını içeren bir SELECT deyimine dönüştürebilirsiniz.
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.
From Table Properties, select Sorgu DüzenleyicisiSorgu Düzenleyicisi.
Sorgu DüzenleyicisiSorgu 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, hiçbir sütunun işaretini kaldırmadan veya herhangi bir filtre uygulamadan bir tabloyu bütünüyle içeri aktardıysanız, sorguyu "Seç * Kimden" olarak görürsünüz ve değiştirilmesi daha zor olur:
|
|---|
SQL sorgusunu değiştirme
Artık sorguyu nasıl bulacağınızı bildiğinize göre, modelinizin boyutunu daha da azaltmak için üzerinde değişiklik yapabilirsiniz.
- Para birimi veya ondalık veri içeren sütunlar için, ondalık sayılara ihtiyacınız yoksa, ondalık sayıları atmak için şu söz dizimini kullanın:
"YUVARLA([Decimal_column_name],0)... .”
Kuruşlara ihtiyacınız var ancak kesirlerine ihtiyacınız yoksa, 0'ı 2 ile değiştirin. Negatif sayılar kullanırsanız, birimlere, onlarca, yüzlere vb. yuvarlayabilirsiniz. - dbo adlı bir Datetime sütununuz varsa. Büyük tablo. [Tarih Saat] ve Saat bölümüne ihtiyacınız yoksa, saatten kurtulmak için söz dizimini kullanın:
"SELECT CAST (dbo. Büyük tablo. [Tarih saat] as date) AS [Tarih saat]) " - dbo adlı bir Datetime sütununuz varsa. Büyük tablo. [Tarih Saat] ve hem Tarih hem de Saat bölümlerine ihtiyacınız varsa, SQL sorgusunda tek Tarihsaat sütunu yerine birden çok sütun kullanın:
"SELECT CAST (dbo. Büyük tablo. [Date Time] as date ) AS [Date Time],
DatePart(hh, dbo. Büyük tablo. [Tarih Saat]) as [Date Time Hours],
DatePart(MI, dbo. Büyük tablo. [Tarih Saat]) as [Date Time Minutes],
DatePart(ss, dbo. Büyük tablo. [Tarih Saat]) [Tarih Saat Saniye] olarak,
DatePart(MS, dbo. Büyük tablo. [Tarih Saat]) as [Tarih Saat Milisaniye]"
Her parçayı ayrı sütunlarda depolamak için gerektiği kadar sütun kullanın. - Saat ve dakikaya ihtiyacınız varsa ve bunların birlikte tek bir zaman sütunu olarak kullanılmasını tercih ediyorsanız, şu söz dizimini kullanabilirsiniz:
Timefromparts(datepart(hh, dbo. Büyük tablo. [Tarih Saat]), datepart(mm, dbo. Büyük tablo. [Tarih Saat])) as [Tarih Saat SaatDakika] - [Başlangıç Saati] ve [Bitiş Saati] gibi iki tarihsaat sütununuz varsa ve gerçekten ihtiyacınız olan, [Süre] adlı bir sütun olarak saniyeler cinsinden aralarındaki zaman farkıysa, her iki sütunu da listeden kaldırın ve ekleyin:
"datediff(ss,[Başlangıç Tarihi],[Bitiş Tarihi]) as [Süre]"
ss yerine ms anahtar sözcüğünü kullanırsanız, süreyi milisaniye cinsinden alırsınız
Sütunlar yerine DAX hesaplanmış ölçülerini kullanma
DAX ifade diliyle daha önce çalıştıysanız, hesaplanan sütunların modeldeki diğer bazı sütunları temel alarak yeni sütunlar türetmek için kullanıldığını, hesaplanan ölçülerin ise modelde bir kez tanımlandığını, ancak yalnızca bir PivotTable'da veya başka bir raporda kullanıldığında hesaplandığını biliyor olabilirsiniz.
Bellek tasarrufu sağlayan tekniklerden biri de normal veya hesaplanmış sütunları hesaplanmış ölçülerle değiştirmektir. Klasik örnek Birim Fiyat, Miktar ve Toplam'dır. Üçüne de sahipseniz, yalnızca ikisini koruyarak ve üçüncüyü DAX kullanarak hesaplayarak yerden tasarruf edebilirsiniz.
Hangi 2 sütunu tutmalısınız?
Yukarıdaki örnekte Miktar ve Birim Fiyatı'nı koruyun. Bu ikisinin Toplam'dan daha az değeri vardır. Toplam'ı hesaplamak için, hesaplanandaki gibi bir ölçü ekleyin:
"TotalSales:=sumx('Satış Tablosu','Satış Tablosu'[Birim Fiyat]*'Satış Tablosu'[Miktar])"
Hesaplanan sütunlar, her ikisi de modelde yer kapladığından normal sütunlara benzer. Buna karşılık, hesaplanan ölçümler anında hesaplanır ve yer kaplamaz.
Sonuç
Bu yazıda, bellek açısından daha verimli bir model oluşturmanıza yardımcı olabilecek çeşitli yaklaşımlardan bahsettik. Veri modelinin dosya boyutu ve bellek gereksinimlerini azaltmanın yolu sütun ve satırların toplam sayısını ve her sütunda görünen benzersiz değerlerin sayısını azaltmaktır. İşte ele aldığımız bazı teknikler:
- Sütunları kaldırmak, kuşkusuz yerden tasarruf etmenin en iyi yoludur. Gerçekten ihtiyacınız olan sütunlara karar verin.
- Bazen tablodaki bir sütunu kaldırabilir ve onun yerine hesaplanmış bir ölçü koyabilirsiniz.
- Tablodaki tüm satırlara ihtiyacınız olmayabilir. Tablo İçeri Aktarma Sihirbazı'nda satırlara filtre uygulayabilirsiniz.
- Genel olarak, tek bir sütunu birden çok ayrı bölüme ayırmak, bir sütundaki benzersiz değerlerin sayısını azaltmak için iyi bir yoldur. Parçaların her biri az sayıda benzersiz değere sahip olacaktır ve birleştirilmiş toplam özgün birleştirilmiş sütundan daha küçük olacaktır.
- Çoğu durumda, raporlarınızda dilimleyici olarak kullanmak için ayrı bölümlere de ihtiyacınız olur. Uygun olduğunda, Saat, Dakika ve Saniye gibi bölümlerden hiyerarşiler oluşturabilirsiniz.
- Çoğu zaman, sütunlar gerekenden daha fazla bilgi içerir. Örneğin, bir sütunda ondalık sayıların depolandığını, ancak tüm ondalık değerleri gizlemek için biçimlendirme uyguladığınızı varsayalım. Yuvarlama, sayısal bir sütunun boyutunu küçültmede çok etkilidir.
Artık çalışma kitabınızın boyutunu küçültmek için yapabileceklerinizi yaptığınıza göre, Ç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 Ayarlayıcısı'nı indirin.
İlgili bağlantılar
Veri Modeli belirtimi ve sınırlamaları
Çalışma Kitabı Boyutu Ayarlayıcısı
Power Pivot: Excel'de güçlü veri çözümlemesi ve veri modelleme