Bazı iş zekası uygulamalarında, fact tablosuyla tarih tablosunu ilişkilendirirken “date” tipinde bir tarih sütunu kullanımı yerine “integer” tipinde bir tarih sütunu kullanımı önerilir. Gerekçe olarak integer tipindeki sütünların date tipindeki sütunlara göre daha hızlı çalıştığı ifade edilir. Bu durum Power BI için geçerli değil!
Tarih (date) yerine integer tipinde tarih anahtarı (datekey) sütunu da kullanabilirsiniz ilişki kurarken elbette ama bunun performansa hiçbir katkısı yok. Artı, ilişki sütunu olarak kullanıldığında bu iki sütun tipinin çalışma şekilleri birbirinden farklıdır! Bu farkları bilerek kullandığımız sürece hangi tipi kullanacağınız size kalmış, ama benim önerim her zaman “date” tipinde sütun kullanmak.
İkisi arasındaki farklara geçmeden önce benzer bir durumu da kısaca ekleyeyim: Bazen çektiğimiz veride GUID diye tabir edilen, çok uzun alfa-nümerik değerler içeren sütunlar olur, bazı ERP’ler bazı anahtar sütunları bu şekilde tutuyor. Bu GUID tipi değerleri modele yapay-sentetik bir integer olarak (surrogate key ) almanın da model performansına eğer import yöntemini kullanıyorsanız bir katkısı yok! Buradaki anahtar detay “import yöntemini kullanıyorsanız”.
Eğer direct query ile bağlanıyorsanız GUID değerler yerine integer surrogate key’ler yaratmak daha avantajlı olabilir. Bu tamamen SQL tarafındaki tabloları/sütunları nasıl optimize ettiğinizle ilgili. GUID yerine integer surrogate key yaratmak -eğer bu işlemi veri kaynağının üzerinde yaptıysanız- modelin refresh süresini azaltabilir yalnız, bu da GUID sütunların SQL tarafında indeksli olup olmamasıyla ilgili. Surrogate key’i Power Query’de yapıyorsanız bu refresh sürenizi arttırabilir. Ama veriyi bir kez import ettikten sonra bu anahtar sütunlar GUID olmuş ya da integer olmuş performans açısından farketmiyor. Pbix dosyanızın büyüklüğü bir miktar artabilir ama o da devasa farklılıkta olmaz.
Date veya DateKey tipi sütunlar üzerinden ilişki kurduğumuzda ne değişiyor! Aradaki farklar ne?
Her zaman olduğu gibi modelimizde bir tarih tablosu var fakat henüz “Mark as Date Table” olarak işaretlenmemiş! Yani modele tarih tablosu olarak bu tabloyu kullanacağız dememişiz henüz.
İki tablo arasındaki ilişki “date” tipindeki tarih sütunu üzerinden kurulu şu an. Kümülatif satışları ve aylık değişimleri hesaplayan basit metrikler ekliyorum matrise.
Satışlar = SUMX( 'Satışlar' , 'Satışlar'[Fiyat] * 'Satışlar'[Miktar] )
YTD =
CALCULATE(
[Satışlar] ,
DATESYTD( 'Tarih'[Tarih] )
)
MoM % =
VAR __OncekiAySatislar =
CALCULATE(
[Satışlar],
DATEADD('Tarih'[Tarih], -1, MONTH)
)
RETURN
DIVIDE([Satışlar] - __OncekiAySatislar, __OncekiAySatislar)
Olması gerektiği gibi çalışıyorlar!
Tarih Anahtarı Üzerinden İlişki
Tarih tablosuyla satışlar tablosu arasındaki ilişkiyi bu sefer Tarih Anahtar (DateKey) sütunu üzerinden kuruyorum.
Biraz önce gayet güzel sorunsuz çalışan YTD ve MoM % metrikleri bu sefer başka bir şey hesaplıyorlar! Formüller aynı, hiçbir değişiklik yok, ama rakamlar beklediğimiz rakamlar değil!
Ta ki, Tarih Anahtarı üzerinden ilişkilendirdiğimiz tarih tablosunu “Mark as Date Table” deyip modele tanıtana kadar!
Her şey normale döndü! Neden!
Bu durumu anlamak için YTD formülünü bir time intelligence fonksiyonu olan DATESYTD yerine diğer standart DAX fonksiyonları ile yazacağım.
** Time intelligence fonksiyonları genelde hayatımızı kolaylaştırır, kullanımı basittir, yazar geçersiniz, ama eğer bir developer iseniz, DAX bilginizi ilerletiyorsanız, mutlaka karşınıza custom bir kod yazmanızı gerektirecek zaman/period karşılaştırma durumu çıkar. Örneğin hafta karşılaştırması bu durumlardan biridir.
İki tablo arasındaki ilişkiyi tekrar date tipindeki Tarih sütünuna çeviriyorum!
Eğer DATESYTD ile birebir aynı etkiyi elde etmek istersek aşağıdaki gibi bir custom DAX formülü yazabiliriz.
YTD DAX =
VAR _SonTarih = MAX( 'Tarih'[Tarih] )
VAR _SatirdakiYil = YEAR(_SonTarih )
VAR _YTDTarihListesi =
FILTER(
ALL( 'Tarih') ,
'Tarih'[Tarih] <= _SonTarih &&
YEAR( 'Tarih'[Tarih] ) = _SatirdakiYil
)
RETURN
CALCULATE(
[Satışlar] ,
_YTDTarihListesi
)
Formülün DATESYTD ile yazılan formüle göre uzun olması kompleks olduğu anlamına gelmez! Sadece daha uzun : )
- Matrisin satırında gözülen ay/yıl neyse bunun en son tarihini bul, _SonTarih değişkenine ata.
- _SonTarih ‘in yılını bul.
- Tarih tablosundaki satırların tamamını ( ALL( ‘Tarih’ ) dediğimiz için) bu değerlerle karşılaştır, matris satırında gözüken ayın / yılın son tarihine kadar olan tüm tarihleri döndür.
- Bu dönen tarih listesi üzerinden satışları hesapla.
DATESYTD doğası gereği en dip total satırında son yıla ait kümülatif rakamı döndürüyor burada, eğer Total satırında tüm yılların toplamını görmek istersek benzer mantıkta başka bir custom formül yazabiliriz!
YTD DAX -2 =
CALCULATE(
[Satışlar] ,
FILTER(
ALL('Tarih') ,
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
) ,
VALUES( 'Tarih'[Yıl] )
)
- Tarih tablosundaki satırların tamamını satırda gözüken ayın/yılın son günüyle karşılaştır.
- Bu bulduğun “son gün” değerine kadar olan tarihleri döndür.
- VALUES ( ‘Tarih]Yıl] ) ? Ama yıl değişirse bunu dikkate alacaksın! ( 2007 altındaki bütün aylar için yıl 2007, dolayısıyla 2008’e geçtiğinde kümülatif resetleniyor, Total satırında ise hem 2007 hem 2008 var, dolayısıyla her iki yılın tarihlerini döndürdüğünden dip toplamda matematik olarak her iki yılın toplamını alıyor.
- MAX (Tarih … kısmını bir önceki örnekte olduğu gibi değişkene de atayabilirdik, bu sefer değişken olmadan yazdım. Değişken kullanımıyla ilgili detaylar için buradan.
“Mark as Date Table” ‘ı kaldırıyorum tekrar, yani artık resmi bir tarih tablomuz yok diyorum modele ve son yazdığımız formülde ufak bir değişiklik yapıyorum. Tarih tablosu satışlar ile halen “Tarih” sütunu üzerinden ilişkili.
YTD DAX - Tarih Sütunu İlişkisi =
CALCULATE(
[Satışlar] ,
FILTER(
ALL('Tarih'[Tarih]) ,
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
) ,
VALUES( 'Tarih'[Yıl] )
)
Tüm Tarih tablosu yerine ‘Tarih'[Tarih] sütununu ALL parantezi içine aldık. Ve matrise düşürüyorum.
Aynı rakamları gösteriyor!
Yani Tarih tablosunun tamamını ALL parantezine almak ile Tarih tablosundaki sadece Tarih sütununu ALL parantezine almak arasında bir fark yok bu örnekte. Oysa olmalıydı! ALL ( ‘Tarih'[Tarih] ) dediğimizde, sadece ve sadece Tarih sütunu üzerindeki filtreleri kaldırırız, aynı tablodan gelen, misal yıl ay filtreleri varsa buna riayet etmesi gerekirdi ama etmiyor!
Bunun sebebi, date tipindeki bir sütun üzerinden, “Mark as Date Table” demesek bile bir ilişki kurarsak, DAX’ın otomatik olarak internal bir şekilde bu tarih sütununun bulunduğu tablonun tamamını ALL parantezine alması!
Yani yukarıdaki kodun internal çevriminde ALL (‘Tarih’) var! Çünkü ilişki kurduğumuz sütun tipi date!
…
Devam edelim. “Mark as Date Table” hala seçili değil ve tarih ile satışlar tablosu arasındaki ilişkiyi bu sefer Tarih Anahtar sütununa çeviriyorum!
Formülü aynen kopyalıyorum ve YTD metriğiyle birlikte matrise düşürüyorum!
YTD DAX - Tarih Anahtar Sütunu İlişkisi =
CALCULATE(
[Satışlar] ,
FILTER(
ALL('Tarih'[Tarih]) ,
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
) ,
VALUES( 'Tarih'[Yıl] )
)
YTD yazının başında da gördüğümüz gibi datekey üzerinden ilişki olduğunda ve Mark as Date Table işaretli olmadığında çalışmıyordu. Yazdığımız son custom formül de istediğimiz gibi çalışmıyor. Çünkü şu an sadece Tarih[Tarih] sütünunun tüm değerleri üzerinde çalışıyoruz, bu sütun üzerindeki filtreleri kaldırıyoruz, ama tarih tablosundan gelen yıl ay gibi filtreler olursa bunlara riayet edeceğiz.
Örneklendirerek açalım: Misal 2007 Nisan rakamı hem YTD de hem custom formülde niye 478,694 çıkıyor da kümülatif çıkmıyor!
- FILTER(ALL(‘Tarih'[Tarih]) , ‘Tarih'[Tarih] <= MAX( ‘Tarih'[Tarih] ), elimizde 1.01.2007’den 30.04.2007’ye kadar olan tarihlerin listesi var. Dikkat sadece tarih listesi var, yıl ay değil!
- VALUES ( ‘Tarih'[Yıl ) Nisan 2007 hücresi için 2007.
- Nisan 2007 hücresindeki filter context‘te ay olarak Nisan, yıl olarak 2007 var.
- Bu 3 kümeyi AND ile birleştirelim: (1.01.2007 – 30.04.2007 ) AND 2007 AND Nisan. Elimizde kalan set 1 Nisan 2007 – 30 Nisan 2007.
- CALCULATE ‘te bu tarih aralığındaki satışları buldu!
DATESYTD kullandığımız YTD formülü de aynı mantıkla, 1.01.2007-30.04.2007 tarihlerini filter context’ten gelen Nisan ve 2007 ile birleştirince sadece Nisan 2007 tarihleri elinde kaldığından Nisan 2007 satışlarını gösteriyor.
Kendi içinde tutarlılar!
Eğer YTD’nin ve yazdığımız custom formülün kümülatif çalışmasını istiyorsak, bunlara ALL (‘Tarih’ ) filtresini de vermemiz lazım! Çünkü şu an aradaki ilişki datekey üzerinden, tablo mark as date table olarak işaretlenmemiş, dolayısıyla DAX otomatik olarak yazdığımız kodlara ALL ( Tarih ) eklemiyor! Eğer mark as date table ‘ı işaretlersek veya datekey sütunu yerine tekrar date sütununu ilişkide kullanırsak internal olarak ekleyecek!
Bu haliyle matristeki kümülatif metriklerinin doğru çalışması için ya her iki formüle de ALL (Tarih) ekleyeceğiz …
ve metrikler istediğimiz gibi çalışmaya başlayacak !
ya da tabloyu “Mark as Date Table” olarak işaretleyeceğiz ve metrikler ALL (Tarih) dememize gerek kalmadan istediğimiz gibi çalışmaya başlayacak. Çünkü bu kısmı DAX bizim adımıza internal olarak ekleyecek!
Buraya kadar olan kısmı özetlememe ve önerilerimi yazmama müsade edin:
- Tarih tablosu istisnasız her modelin vazgeçilmez tablosudur. Her modelde mutlaka olması gerekir.
- Fact tablosuyla tarih tablosu arasında “date” tipindeki tarih sütunu üzerinden ilişki kurun. Hayatınız kolaylaşır. Tüm time intelligence fonksiyonları sorunsuz çalışır.
- Custom zaman karşılaştırma formülü yazmanız gerekirse ALL ( Tarih ) demenize gerek yoktur. ALL( Tarih[Tarih] ) de çalışır. DAX bunu internal olarak yazdığımız formüle zaten ekler.
- Her durumda tarih tablosunu “Mark as date table” diyerek modelinize tanıtın. Bunu yaptığımızda Power BI opsiyonlarındaki “Auto Date Time” açık olsa bile, arka tarafta görmediğimiz internal tarih tabloları modelden uçar, dolayısıyla dosya büyüklüğünüz azalır.
- Tarih tablosundaki tarih sütununuz sadece date olsun, datetime değil! Fact tablonuzdaki tarih sütunu veya sütunlarında time kısmı da varsa, bu sütünu date ve time olarak ayırın. Ve fact tablosundaki bu date sütunu ile tarih tablosunu ilişkilendirin.
- Tarih tablosu, fact tablolarındaki ilgili yıllara ait 365 günün tamamını eksiksiz şekilde içermek zorundadır. Nisan 2022 deyiz diye tarih tablomuz Nisan 2022 sonuna kadar olan tarihleri içerse olur mu? Hayır olmaz! İlgili yıllara ait 365 günün tamamı olmak zorunda!
- DateKey tipinde bir sütun üzerinden ilişki kurmakta ısrarcıysanız, tarih tablonuzu modele “mark as date table” diyerek tanıtın. Eğer tanıtmazsanız yazdığınız tüm time intelligence formüllerine ALL ( Tarih ) eklemeyi unutmayın.
DAX’ın date tipindeki sütunlar üzerinden ilişki kurulduğunda veya mark as date table dediğimizde internal olarak eklediği bu ALL ( Tarih tablosu ) durumu, modelimizde birden fazla tarih tablosu olduğunda daha da çok önem kazanacak. Bunu da çoklu tarih tablolarıyla ilgili role playing dimensions yazısına bırakalım.
Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!