“Hocam yüzdeler 100% olmuyor!” Aynı cümleyle birkaç kez karşılaşınca, yaklaşık 5 yıl önce yazdığım “referential integrity” konusunu, bu sefer ilişki tiplerini de katarak yeniden yazmak istedim. Tüm Power BI veri modellerinde, iki farklı ilişki tipi (relationship) vardır: İki tablo arasındaki bir ilişki, ya regular ilişkidir, ya da limited ilişkidir.
Yazı İçeriği
* Çoğu terimi bilerek ve isteyerek Türkçe -muhtemel- karşılıklarıyla yazmıyorum. Ne zaman kendimi Microsoft’un Türkçe dokümantasyon sitesinde bulsam, okuduğumu doğru anlayıp anlamadığımı teyit etmek için İngilizce versiyonunu da okuyorum. Özellikle DAX ve veri modeliyle ilgili Türkçe dokümantasyonun yarısı -hadi facia demeyelim- ama kötü. İki sebepten, düz Türkçe çeviri aynı anlamı ifade etmekte bazen yetersiz kalıyor. İkincisi, makine çevirisi olan çok kısım var, kendimi sıklıkla “burada ne demek istemiş olabilir acaba” diye düşünürken buluyorum. Relationship-İlişki tamam, primary key-birincil anahtar tamam, ama Regular-Limited ‘e Normal-Sınırlı mı demek lazım emin değilim. Çünkü aynı ilişki tiplerini Microsoft 2 sene önce Strong-Weak olarak tarifliyordu! O jargon tamamen kalktı, yerine Regular-Limited geldi. O dönem Güçlü-Zayıf diye çevrilen ilişki tipleri, şimdi Normal-Sınırlı oldu!
Bu ve bir sonraki yazının temel kaynaklarından biri, Microsoft Learn’deki bu sayfa. Türkçe versiyonu da burada. Hangisi sizin için daha uygunsa mutlaka göz atmanızı öneririm.
İlişki Tipini Biz mi Belirliyoruz?
Hayır!
Bir ilişkinin tipini, regular veya limited olarak belirlemek bizim seçebileceğimiz bir opsiyon değil! İlişkinin kardinalite tipine göre (1-∞, ∞-∞ gibi) , ilişkinin bağladığı iki tablonun bulunduğu veri kaynaklarına göre, bağlantı yöntemimize göre Power BI kendisi belirliyor! Tablolar farklı veri kaynaklarından veya aynı veri kaynağından geliyor olabilir, tabloları import etmiş olabiliriz veya direct query ile bağlanmış olabiliriz, aradaki ilişki 1-∞ ya da ∞-∞ olabilir, aynı modelde hem import hem de direct query tablolar olabilir (composite model) vs. Durumun ne olduğuna Power BI kendisi karar verecek!
Regular Relationship Ne Demek?
Power BI, bir ilişkinin “one” tarafındaki tablonun anahtar sütununda, sadece ve sadece tekil değerler olduğunu 100% garanti edebildiğinde bu ilişkiye “regular” ilişki diyor.
Peki 100% garanti edebilmek demek ne demek?
Tüm tabloların import edildiği, tablolar arası ilişkilerin 1-∞, ∞-1 veya 1-1 olduğu bir modeldeki tüm ilişkiler “regular relationship” ‘tir. Tablolar farklı veri kaynaklarından gelse de geçerli, gelmese de geçerli!
Aralarında ilişki olan tablolardan one taraftaki direct query ile oluştuysa, her iki tablonun da aynı veri kaynağından gelmesi gerekir. + İlişki tipi gene ya 1-1, 1-∞ veya ∞-1 olacak!
Dikkat! Yukarıda saydığım ilişkilerde ∞-∞ yok!
Regular Relationship ve Table Expansion
1-∞ tipindeki bir regular ilişkide, DAX ile hesap kitap yapılırken expanded tables konsepti, yani table expansion devreye girer!
Aynı SQL veri kaynağından import edilerek alınmış iki tane tablomuz var: Ürünler ve Satışlar tablosu.
Soldaki “Ürünler” master tablosundaki Ürün ID sütununda sadece tekil değerler var (blank, null, duplike yok) ve Power BI bunu görüyor, çünkü veriyi import ederek aldık! + Satışlar tablosundaki her bir Ürün ID’nin Ürünler tablosunda bir karşılığı var. Uyuşmayan, match etmeyen herhangi bir kayıt yok!
Bu iki tablo arasında 1-∞ ilişki kurduğumuzda oluşan ilişki regular bir ilişki ve “Satışlar” tablosunu refere eden yazdığımız tüm metrikler “Satışlar” tablosunun “expanded” halini, yani genişletilmiş halini bir virtual table olarak görecek!
Satışlar tablosunun expanded versiyonunda, satışlar tablosunun tüm sütunları + satışlar tablosunun “one” tarafında olan tüm tabloların ilgili sütunları var.
Yani Satışlar tablosunu one tarafındaki tüm tablolarla join’ledik ve bunu yaparken LEFT OUTER JOIN kullandık!
Power BI ‘ın gördüğü “expanded” satışlar tablosu bu:
Expansion, yani genişleme, her zaman ilgili “base” tablonun tüm orijinal sütunlarını + one tarafındaki tüm tabloların ilgili sütunlarını görür, arada kaç tane ilişki olursa olsun!
Eğer “Ürünler” tablosuna ek olarak, bir tane de “Ürün Özellikleri” diye bir tablo ekleseydik, bu tabloyu da “Ürünler” tablosuna 1-1 ilişkiyle bağlasaydık, aradaki ilişki gene “regular relationship” olacaktı. Fakat Ürünler tablosunun expanded versiyonunda, LEFT OUTER JOIN değil, FULL OUTER JOIN kullanacaktı Power BI.
Niye farklı? Referential Integrity denen bir şey var çünkü!
Referential Integrity Ne Demek?
“Integrity” ‘nin sözlük anlamı, bütünlük, tutarlılık!
İki tablo arasında “regular” bir ilişki yaratırken, Power BI one tarafındaki tablonun anahtar sütunundaki değerlerin 100% tekil olduğunu garanti eder demiştim.
Eğer verimizde aşağıdaki gibi bir durum varsa ne olacak?
Ürün ID 1,2,3 her iki tabloda da var, ama satışlar tablosunda olup da ürünler tablosunda geçmeyen 9,10 ve blank kayıtları var.
Tutarlı olabilmek için, yani referential integrity ‘yi sağlayabilmek için, satışlar tablosunda geçen ama ürünler tablosunda geçmeyen tüm Ürün ID değerleri için ürünler tablosunda fiktif bir blank satırı oluşturur! Ve eşleşmeyen bütün kayıtları bu “blank” satırıyla eşleştirir.
Bir matrise düşürdüğümüzde bu “blank” satırı görürüz!
Satışlar tablosunun expanded versiyonunda da bu blank satırı görünür!
Eğer modelimize DAX Studio ile bağlanırsak bu bütünlüğü bozan kayıtları görebiliriz!
* RI, referential integrity ‘nin ilk harfleri.
* 1-1 ilişki olması durumunda ürünler tablosunun expanded versiyonunda FULL OUTER JOIN kullanılmasının sebebi bu, eğer eşleşmeyen kayıtlar varsa, ilgili tablolarda bu fiktif satırı oluşturabilmek!
Yüzdeler 100% Yapmıyor!
Yapmayabilir! Çünkü bazı fonksiyonlar bu fiktif blank satırını görürken bazıları görmüyor!
Misal ALL(…) bu blank satırı görürken ALLNOBLANKROW (..) görmez.
VALUES ( Sütun Adı) görürken, DISTINCT (Sütun Adı) görmez.
+ Nasıl bir görsel oluşturduğunuzla da ilgili !
Bu metriği matrise düşürdünüz, yüzdeler 100% ‘e tamamlar.
Renk Yüzde Satışlar =
VAR _Satislar = SUM('Satışlar'[Tutar] )
VAR _TumSatislar = CALCULATE( SUM('Satışlar'[Tutar]) , ALL('Satışlar') )
VAR _Oran = DIVIDE(_Satislar , _TumSatislar )
RETURN
_Oran
Ama bu metriği düşürdünüz, yüzdeler 100% ‘e tamamlamaz!
Renkli Ürünler Yüzde =
DIVIDE(
COUNTROWS('Ürünler'), COUNTROWS(ALL('Ürünler'))
)
100% ‘e tamamlaması için blank satırını dikkate almayan bir versiyonunu yazmanız lazım!
Renkli Ürünler Yüzde NOBLANK =
DIVIDE(
COUNTROWS('Ürünler'), COUNTROWS(ALLNOBLANKROW('Ürünler'))
)
Örnekleri arttırmak, çeşitlendirmek mümkün! Hangi fonksiyon bu fiktif blank satırını dikkate alıyor, hangisi almıyor buna göre yazmanız lazım formülünüzü.
Blank Satırını Ne Yapalım?
Power BI, eğer regular bir ilişki oluşturduysa, bu eşleşmeyen kayıtlarla ilgili durumu size havale eder! Bu benim sorunum değil, senin sorunun! Veri kalitenle ilgili sorun var, nasıl çözüyorsan çöz! İdeal bir veri modelinde, fact tablosunda geçen tüm ID değerlerinin ilgili master tablolarda bir karşılığı olur. Eğer yoksa bu -muhtemelen- süreçlerinizle ilgili veya ETL aşamalarınızla ilgili bir soruna işaret eder!
Benim önerim, bu sorunu süreç-ETL tarafında çözmeniz yönünde.
Diğer taraftan şu durumla da sıklıkla karşılaşıyorum: Ürünler tablosundaki herhangi bir sütunu bir slicer’a düşürdüğünüzde bu blank kayıt gözüküyor!
Bu “blank” kaydı uçurmak için aradaki ilişkiyi bi-directional yapmak, ya da filtelerden “blank” değeri uçurmak -bana sorarsanız- doğru bir çözüm değil! Hatta tam tersi, gözünüzün önünde dursun, süreçlerinizle ilgili bir hataya işaret ediyor çünkü!
* Expanded tables ile ilgili yazdığım yazıya göz atmak isterseniz buradan.
Yazıdaki örneği indirebilirsiniz.