Power BI Modeline Neden Date Table Eklemeliyiz?

Power BI’da önemli yapı taşlarından bir tanesi de Date Table eklemektir. Konu ne olursa olsun hemen hemen her makalede, her webinarda mutlaka bir kez “Projelerinize Date Table ekleyerek başlayın” uyarısı ekliyorum. Nedir bu Date Table, Neden var olanla yetinemeyiz, modele harici bir Date table ekleriz? Bu makalede Direkt olarak Date table eklemeyi göstermek yerine, eklenmediğinde yaşanabilecek durumları göreceğiz. Böylece modelimizde varolmasının önemini anlayabileceğiz.

Date Table derken kastettiğimiz şey şudur, belirtilen iki tarih arasındaki her gün için mutlaka bir tarih kaydı içeren ve tarihlerin tekrar etmediği, özetle ardışık ve tekrarsız bir tarih sütunu barındıran tablodur. Power BI’da bir modelin olmazsa olmazıdır. Temelde kabaca şimdilik 4 sebep sayarak başlayabiliriz. Bu yazıda birini örneklendireceğim. Devamı diğer yazılarda gelecek.

1. Tarihe dair gerekli olan diğer gün/ay/yıl/haftanın günü/ yılın haftası vb. gibi bilgilerin Date Table’a eklenmesi, bu sütunların yılın her tarihi için yalnızca bir kez hesaplanması ve sadece Date Table satır sayısı kadar yer kaplaması anlamına gelir. Date Table olmadığında bu ek bilgi sütunlarını Fact Table’a (hareket tablolarına) ekleyenler oluyor ki bu hem refresh hızı hem de dosya boyutu açısından hiç sağlıklı bir yol değildir.
2. Farklı tablolardaki farklı grafik/değerlerin ortak bir slicer’dan tarihe göre çalışması için Shared Dimension olarak kullanırız. Date table olmadığında, farklı iki fact table’ı örneğin bütçe ve satışlar tablosunu birbirine tarih üzerinden bağlayanlar oluyor. Bu hem many to many ilişkiye sebep olur hem de bu iki tablo arasında ürün bazlı da ilişki kurulması gerektiğinde, Power BI aynı anda birden fazla aktif ilişkiye izin vermiyor. Shared Date Dimension burayı da kolaylıkla çözüyor.
3. Time Intelligence Fonksiyonları, tekrarsız ve eşsiz tarihlerden oluşan tabloları zemin olarak kabul ediyor. Yani Date Table Time Intelligence fonksiyonlarının olmazsa olmazı.
4. Satışlar Tablosu, üretim tablosu, denetim tablosu, ziyaret tablosu vb. tüm fact tablolar sadece üretim yapılmış tarihleri içerir. Oysaki bazen haftanın/ayın/yılın hangi tarihlerinde işlem olmadığını da görmemiz gerekir. Bu durumda grafiğinizin temel değerinin tüm günleri içermesi gerekir. Bu da yine Date Table’dır.

Dediğimiz gibi tek bir tablo üzerinden basitçe bir rapor hazırlıyor olsanız dahi Date table gereklidir. Bu maddeyi açıklarken tek tablo üzerinde çalışmaya başlayan amatör bir geliştirici olduğumuzu düşünelim. Bakalım nerelerde Tarih tablosuna yönelmemiz gerekir.

Tablolarınızdaki her tarih formatlı sütun için, modele otomatik olarak gizli şekilde Yıl/Çeyrek/Ay/Gün sütunları eklenir!

Bizim manuel bir tarih tablosu ekleme nedenlerimizi açıklamadan önce otomatik Date Table özelliğinden bahsedelim.Power Bı Desktop uygulamasını açtığımızda bazı ayarlar default olarak açık gelir. Bunlardan bir tanesi otomatik Date Table’dır. File / Options & Settings / Options menüsünün Global bölümünün ve Current File bölümünün Time Intelligence alanındaki check işareti modele Otomatik Date Table ekleneceği anlamına gelir. Bilgisayarımızda oluşturulan tüm pbix dosyalarında Date Table eklenip eklenmeme durumunu Global bölümündeki seçenek, sadece o an açık mevcut pbix dosyasına eklenip eklenmeme durumunu ise Current File altındaki seçenek belirler.

Şimdi modelimizde bu ayar açık halde iken bir inceleyelim. Aşağıda bir satış tablomuz var. Tabloda tarihle ilgili olarak satışların tarihini gösteren Tarih sütunumuz var.

Rapor görünümüne tarih sütununu getirdiğinizde Tarih hiyerarşik olarak bu 4 değere kırılıyor. Ancak yukarıdaki Görsel2’ye bir daha bakınız. Tabloda yalnızca Satış Tarih sütunu görünüyor, Year, Quarter, Month, Day sütun olarak görünmüyor.

Çünkü Power BI, tablolarımızdaki tarih sütunları için, modelimize eksta olarak bu 4 bilgiyi içeren gizli tarih tabloları ekliyor. Bunu tüm tarih sütunları için yapıyor! Bu hiyerarşi değerlerini yalnızca, rapor ekranına sürükleyip bıraktığımızda Görsel 3’teki gibi görebiliyoruz. Eğer Dax Studio ile Pbix dosyamızı açarsak, her Date veri tipi için ekstra tabloların eklendiğini görebiliriz.

İşin çok başında olanlar için rapordaki tarihlere, yıl, çeyrek, ay, gün kırılımlarının otomatik gelmesi güzel bir özellik. Hızlıca bu kırılımlarda rapor alabilir. Ancak projeye bu alanları kullanarak başlarsak proje geliştikçe, başta saydığım hatalar ortaya çıkacak. Ayrıca modelde gördüğü her tarih sütunu için bunu yapıyor olması da kötü; firmaların projelerini düşünelim, tarihle ilgili olarak üretim tarihi, teslimat tarihi, satış tarihi, fatura tarihi vb. birçok sütunlar oluyor. Her biri için de arka planda 4 sütun içeren birer tablo ekleniyor modele. Bu gizli eklenen tablolar yüzünden dosya boyutunuz siz farkında olmadan artıyor! Peki boyuta olumsuz etkisi dışında, nerde kusur kalıyor bakalım.

Örneğin raporda haftanın günlerine göre satış adetlerini gösterelim dersek?

Eğer modele kendiniz harici Date Table eklemediysek, mecburen satış tablosuna aşağıdaki gibi iki yeni hesaplanmış sütun eklememiz gerekecek. Bu sütun satış tarihi aynı tarih olsa bile, tablodaki her satır için tek tek hesaplanarak haftanın kaçıncı günü olduğunu bulacak. Burası Calculated Column olduğu için, her refreshte bu hesaplama her satır için yeniden yapılacak(itteration). Ayrıca bu sütun veri modelinize eklendiği için dosyada yer kaplayacak. Bu düzgün bir proje için doğru bir yaklaşım değildir,
bu yaklaşım ile ilerlersek, günün adı, yılın kaçıncı haftası, üretim tarihi üzerinden haftanın günü vb. gibi bilgiler gerekli oldukça tek tek sütun oluşturulmak gerekecek, her bir sütun zaten hayli yüklü olan satış tablonuza yük bindirmeye devam edecek.

Daha kötüsü ise, proje ilerledikçe ortaya çıkacak. Yukardaki örnekte bahsettiğimiz grafiği Date Table eklemeden, Fact Table’a eklediğimiz bu sütunla elde ettiğimizi düşünelim.

Satışlar tablosundaki haftanın kaçıncı günü sütunu ve yine satışlar tablosundan adet sütununu grafiğe bırakalım. Buraya kadar tamam gibi, grafik olmuş görünüyor. Fakat!

SORUN 1) Grafiğe dikkatlice baktığımızda 7.gün satış olmadığını görüyoruz. Satış yapılmamış günleri de görmek istersek, normalde eğer Axis’te tüm tarihleri içeren bir Table olsa idi, yukarıdaki görselde Axis bölümünden yeşil işaretlediğim yerden “Show Items with no data” dememiz yeterli olurdu, ama şuan bunu yapsak bile Axis’teki gün bilgisi satış tablosundan geldiği için ve orada sadece satış yapılmış veriler olduğu için, satış yapılmamış gün olan 7.günün grafikte gözükmesi mümkün değil!

SORUN 2) Şimdi de bir süre sonra bu grafik üzerinde üretim adetlerini de eklemek istediğimizi varsayalım. Üretim tabloasunu modele ekledikten sonra, Üretim Adedi değerini aynı grafiğe sürükledim. Ancak, Axis’teki “Haftanın Kaçıncı Günü” sütunu Satışlar tablosundan geldiği için ve üretim tablosu ile henüz bir ilişkisi olmadığı için, üretim adetleri haftanın günlerine göre filtrelenemiyor ve bu nedenl de tüm günlerde sütunlar toplam üretim adedini gösteriyor.

İşte en sık duyduğumuz, “benim bazı slicerlarım bazı grafiklerdeki değerleri değiştiremiyor, benim grafiğimde her iki değer aynı anda gösteremiyor” sorunu doğdu bile!

Burada veri modelleme eğitiminden bir hatırlatma yapmamız gerekiyor. İlişkileri hatırlayalım. Satış ve Üretim tablosu arasında bir ilişki yok. O halde, nasıl satışlar tablosundaki bir değere göre(haftanın günü sütununa göre), Üretim tablosunu filtreleyebilsin ki değil mi? Doğru yolu söylemeden önce bir hatalı ilerleme biçimini daha gösterelim. Çoğu amatör kullanıcı, üretim tablosu ile Satış tablosunu satış tarihi ve üretim tarihi üzerinden bağlıyor.

Bu durumda, rapordaki grafik çalışmış gibi görünse de ters giden şeyler var.

Şimdi dikkat edelim lütfen, X aksisin değerleri satışlar tablosundan geliyor. Yani hangi tarihte/hangi günlerde satışlar varsa satışlar tablosundan yalnızca o değerler geliyor. Dolayısı ile üzerine getirdiğiniz değerler, aksiste görünen değerlere göre filtrelenecek, yani üretim tablosunda yalnızca o tarihe dair filtre uygulanacak ve sadece o tarihlerdeki değerler gelecek. Grafikte de görüyoruz ki, Haftanın 7. Gününde satış yapılmamış, o nedenle X aksiste yok, peki ya üretim yapıldı ise?

Kontrol etmek için üretim tablosuna da haftanın kaçıncı günü formülünü ekleyip aşağıdaki şekilde rapora bırakıyorum. İşte 7.gündeki üretim adedi 94! 94 Adetlik üretimi raporlarımızda gözden kaçıracaktik neredeyse. Bu mantıkla yapılmış projelerde gerçekleşen %’ler gibi önemli bilgiler de hatalı hesaplanmış olacak.

Ne Yapmalıyız?

Raporlarımızda filtreleyici görevindeki verilerin; Slicer’lar, Tablolardaki veriler, diğer tablolardaki veriler vb. hepsinin filtrelemesi gereken kayda ait tüm değerleri kapsıyor olması gerekir. Yani burada Tarihi Üretim ya da Satış Tablosundaki tarihten almamalı, tüm tarihleri içeren meşhur Date Table’dan almalıydık. Böylece hem satışların doğru adetlerini, hem de üretimin doğru adetlerini görebiliriz. Ayrıca Satış ve Üretimi Ürün bazında da kıyaslamak istersek, modele bir de ürünler Dimension tablosu ekleyip, Satışlar ve Üretim tablosunu tıpkı Date gibi, bu ara dimension üzerinden ilişkilendirin. Aşağıdaki modelde Satış ve üretimi direkt birbirine bağlamaya kalkarsak, döngüye sebep oluruz. Esasen bu bir “Shared Dimension” konusu. “Shared Dimension” mantığını anlattığım videoyu buradan izleyebilir ya da buradan makaleyi okuyabilirsiniz.

Bu gibi durumlarda Harici Date Table eklemeyi düşünmeyen kullanıcılar, iki veriyi ayrı grafiklerde göstermek gibi yollara gidilebiliyor. Bu hem rapor görünümü için istenmeyen kötü kullanıcı deneyimlerine sebep olur, hem de her iki tabloya sürekli olarak tarihle ilgili yeni hesaplanmış sütunlar eklemek zorunda kalmış oluruz. Dahası da, daha ileri konularda çok önemli bazı hesaplamaları yapamaz hale geliriz. Görüyorsunuz ki modelde bir tarih tablosu olmadığında sorunlar ardışık geliyor ve büyüyor.

O halde şöyle özetleyebiliriz. Power BI modelimiz tek tablodan oluşsa bile Date Table kullanmak gereklidir. Date table’ın içerisinde bir de tarihlerle ilgili yıl, hafta, gün, haftanın günü gibi vb. değerleri de eklediğimizde işte o zaman tamam oluyor. Haftanın ya da ayların gün adlarının hem bizim hem de Power BI Engine açısından minimum eforla sıralanmasında da çok işimize yarıyor. Sort By Colum olarak adlandırılan bu özelliği buradaki makaleden okuyabilirsiniz.

Modele Date Table Nasıl eklenir?

Hemen hemen her projede gerekli olan alanları da içeren Date tablosunu modelinize eklemek için Aşağıdaki kodu, bir alttaki ekranda gösterilen Home Sekmesi / New Table düğmesi ile açılan formül çubuğuna yere yapıştırabilirsiniz.

Date = ADDCOLUMNS ( // aşağıdaki ilk satırda oluşan tarih sütununa diğer satırdaki yıl, ay vb. bilgileri sütun olarak ekler.

CALENDAR (DATE(2019;1;1); DATE(2021;12;31)); // 2019 yılından 2021 yılına kadar tüm tarihlerden oluşan tablo oluşturur. Burada Calendar(Min(tablodaki tarih);max(tablodaki tarih))yazılarak kendi fact tablonuzun en küçük ve en büyük değerlerinden oluşan bir tablo elde edilmesi daha iyidir)

“DateAsInteger”; FORMAT ( [Date]; “YYYYMMDD” ); // yıl ay gün numaraları bitişik, sayısal

“Year”; YEAR ( [Date] ); // Yıl

“Monthnumber”; FORMAT ( [Date]; “MM” ); // Ay numarası

“YearMonthnumber”; FORMAT ( [Date]; “YYYY/MM” ); // Yıl 4 hane ve ay numarası

“YearMonthShort”; FORMAT ( [Date]; “YYYY/mmm” ); // Yıl 4 hane ve ay kısa adı

“MonthNameShort”; FORMAT ( [Date]; “mmm” ); // Ay kısa adı

“MonthNameLong”; FORMAT ( [Date]; “mmmm” ); //Ay uzun adı

“DayOfWeekNumber”; WEEKDAY ( [Date];2 ); // Haftanın Günü

“WeekNumber”; WEEKNUM ( [Date];2 ); // Yılın Kaçıncı Haftası

“DayOfWeek”; FORMAT ( [Date]; “dddd” ); // Gün uzun adı

“DayOfWeekShort”; FORMAT ( [Date]; “ddd” ); //Gün kısa adı

“Quarter”; “Q” & FORMAT ( [Date]; “Q” ); // Quarter – Çeyrek

“YearQuarter”; FORMAT ( [Date]; “YYYY” ) & “/Q” & FORMAT ( [Date]; “Q” ) )

Ardından, Model bölümüne geçip aşağıdaki alanlar arasında ilişkileri oluşturunuz

Date Tablomuz olduğunda, Date tablosu ile elimizdeki Fact tablomuz(Satışlar, Üretim) arasında Date üzerinden ilişki kurarak, yüksek sayıda kayıt içeren Fact tablomuza daha fazla yük bindirmeden, Fact tablomuzdaki her tarihin diğer tüm bilgilerine Date table sayesinde ulaşmış oluruz. Şimdi yeniden grafiğe döndüğümüzde istediğimiz gibi 7.gün için 94 üretim adedini gösteriyor olur!

Not: ilişkinin çalışması için, ilişkilendirdiğiniz tüm tarihlerin aynı veri tipinde olması gerektiğini unutmayınız. Hepsi Tarih olmalı, eğer biri Date & Time ise ilişki çalışmaz. Böyle bir durumda ya formatı Date e dönüştürülür ya da saatler önemli ise, Power Query’de tek tuşla ilgili sütundan veri tipi “Date” olarak yeni bir tane çoğaltılır.

Date Table ekledikten sonra küçük bir adım daha var, henüz etkilerini görmeyeceğiz, bir sonraki makalede Date Table’ı Time Intelligence konusu ile birlikte işlerken farkedeceğiniz bir özellik ama yine de şimdiden o özelliği de aktif hale getirelim. Henüz Power BI dosyanız, uygulamaya eklediğimiz Date Table’ı Tarih Tablosu olarak görmedi, kontrol etmedi. Bunu sağlamak için aşağıdaki adımları takip ediniz.

Şimdilik son olarak, makalenin en başında bahsettiğimiz Auto Date’in açık olup olmadığını kontrol etmekte fayda var. Açıksa kapatalım ki, tarihler için gereksiz gizli tablolar eklenmesin modelimize.

Makalenin başında saydığım gibi, bu önemli özelliği atlamamanız için daha bir çok hayati sebep var. Siz siz olun Date table’sız başlamayın.

Sevgi ve sağlıcakla kalın