Son dönemde beni en çok heyecanlandıran projelerimden biri Dynamics 365 F&O üzerine. Daha önce AX üzerinde birkaç proje yapmıştım, o projelerde birçok tablo önüme hazır gelmişti. Bu sefer hepsini kendim oluşturmak istedim. Dynamics F&O Ürün tablolarını Power BI ‘da özellikle de PowerQuery kullanarak oluşturmak, F&O ‘daki kategorilerle ilgili tabloların yapısını iyi anlamayı gerektiriyor.
Detaylara geçmeden önce, F&O ‘dan veriyi nasıl çektiğimizden de bahsedeyim kısaca. Daha önce ODATA, entity oluşturma, BYOD (Bring Your Own Database) gibi yöntemler vardı. Bu sefer birkaç ay önce çıkan “Export to Azure Data Lake” entegrasyonunu kullandık. Tek kelime ile özetlemek gerekirse: Şahane bir entegrasyon! Tabloları doğrudan bir ADLS Gen2 ‘ya bağlayabiliyorsunuz, F&O ‘da oluşan data neredeyse anında datalake’e geliyor. Üstelik insert, update, delete kabiliyetiyle birlikte, yani F&O tarafında veri değişirse ya da silinirse bu da datalake’e yansıyor! 350 tablo sınırı var ama satıştan finansa tüm kapsamları Power BI ‘da yapsanız bile en fazla 60-70 tabloya ihtiyacınız olur. Aynı entegrasyonu Dynamics Business Central için de bekliyorum!
Yazı İçeriği
Ürünler (F&O’daki adıyla Items) tablosunu oluşturmak için lazım olan tablolar:
- EcoResCategoryHierarchy
- EcoResCategory
- EcoResProductCategory
- EcoResProduct
- EcoResProductTranslation
- InventTable
Bu tabloların yapısını açmak gerekirse:
InventTable
Bu tablo Item tablosu, sistemde tanımlanmış ne kadar ürün, hammadde vs varsa hepsi bu tabloda. Satış-Satınalma-Stok hareketleriyle ilgili fact tablolarının (CustInvoiceTrans gibi) tamamı ITEM ID seviyesinde.
Bu tablo ITEM’ların master tablosu ve ITEM ID ‘ler fact tablolarında geçiyor ama bu tablodaki ITEM ID sütunu tekil değerler içermeyebilir. Resimde olduğu gibi aynı ITEM ID birden fazla DATAAREAID altında tanımlanmış olabilir. DATAAREAID bir nevi şirket kodu sütunu. İki şirketiniz var, aynı ITEM ID her iki şirkette de olabilir.
Bu tablonun orijinal anahtarı aslında REC ID sütunu ama hiçbir işimize yaramıyor modelleme açısından, çünkü fact tablolarıyla hiçbir bağlantısını kuramıyorsunuz!
Bu tabloda ITEMID + DATAAREADID sütunlarını birleştirerek bir anahtar elde etmek gerekiyor. Çünkü fact tablolarındaki satırlarda da ITEM ID + DATAAREAID kombinasyonu var. Aynı anahtar mantığını fact tablolarında da yaparsanız, bu tabloyla direkt bu sütun üzerinden bağlayabilirsiniz.
EcoResProduct
Item’ların bir üst seviyesindeki hiyerarşi Products. Bu tablo Product master tablosu ve anahtarı REC ID sütünu. InventTable’daki Product sütunu bu sütunla eşleşiyor. Bir Product’ın altında birden fazla ITEM ID olabilir!
Bu tablodan bize lazım olan tek şey REC ID.
Product’ların ismi bu tabloda değil, EcoResProductTranslation tablosunda. Ve bir Product’ın birden fazla dilde kaydı olabilir!
Product isimlerini getirmenin birden fazla yolu var ve ihtiyacınızın ne olduğuna göre seçeceğimiz yöntem değişebilir. Eğer çok dilli bir rapor seti hazırlayacaksanız, EcoResProduct ile EcoResProductTranslation tablolarını REC ID-PRODUCT sütunları üzerinden one-to-many birleştirebilirsiniz model tarafında. Ya da Power Query tarafında yapalım derseniz, translation tablosundaki LANGUAGE ID ‘yi pivota alıp, PRODUCT-REC ID üzerinden merge edebilirsiniz.
Pivot yöntemini yapalım: LANGUAGEID sütununu seç, Pivot’u tıkla, NAME alanınındaki değerleri getir, aggregation yapma!
EcoResProduct tablosunu da bu tabloyla merge edip lazım olan dilleri getirebiliriz.
Ara not: Translation tablosunu merge için kullandık, model tarafına aktarmaya gerek yok! “Enable Load” opsiyonunu kaldırmamız doğru olur!
EcoResProductCategory
Bu tablo hangi Product’ın hangi kategorilere ait olduğunu gösteren bir tablo. Aynı zamanda hiyerarşi bilgisi de var bu tabloda. Bunu özellikle belirtiyorum çünkü normalde bir Product bir Category’nin altında olur genelde. Ama F&O ‘da kategorilerin de kendi içlerinde hiyerarşileri var. Dolayısıyla bir product, farklı hiyerarşilerdeki kategorilerle birden fazla defa eşleşebilir.
Örnek resimdeki kayıtlar böyle mesela.
EcoResCategory
Bu tablo kategorileri tanımlayan, hangi hiyerarşiye bağlı olduğunu gösteren bir tablo. Anahtar sütunu REC ID, EcoResProductCategory tablosundaki CATEGORY ile eşleşiyor.
Bu tablodaki her kategorinin bir parent’ı, yani üst kategorisi de var! En alt seviyedeki kategorinin bir üstü, onun bir üstü vs de varsa hepsi burada!
EcoResCategoryHierarchy
Bu tablo, en üstteki hiyerarşilerin tanımlandığı tablo. Anahtar sütunu REC ID, EcoResCategory tablosundaki CATEGORYHIERARCHY ile eşleşiyor.
Tablolar Arası ilişkiler
Aradaki ilişkileri yukarıda tabloları açıklarken yazmıştım ama bir kez daha özetleyip toplu halde görelim:
- EcoResCategoryHierarchy.RECID → EcoResCategory.CATEGORYHIERARCHY
- EcoResCategory.RECID → EcoResProductCategory.CATEGORY
- EcoResProductCategory.PRODUCT → EcoResProduct.RECID
- EcoResProduct.RECID → InventTable.PRODUCT
Model neredeyse! tamam gibi. Basit bir de metrik yazalım.
Ürün Sayısı = DISTINCTCOUNT('InventTable'[Item Anahtar] )
Hiyerarşi-Kategori-Product-Item sütunlarıyla bir matrise düşürelim!
Tüm hiyerarşi, kategori, product karşısında aynı rakam var! Çünkü hiyerarşi-kategoriyle başlayan filtreleri InventTable tablosuna taşıyamıyoruz! Eğer EcoResProductCategory ile EcoResProduct tablosu arasındaki ilişkiyi çift yönlü yapacak olursak bu sorun düzelecektir!
Model daha henüz bitmedi!
Kategori – Parent Kategori
EcoResCategory tablosu, her bir kategoriyi ve varsa bunun parent kategorisini tanımlıyor.
Kategorilerin de kendi içlerinde hiyerarşileri var. Bu EcoResCategoryHierarchy tablosundaki hiyerarşi değil bu, kategorilerin kendi aralarındaki hiyerarşileri.
Bu tür ID-Parent ID yapısındaki tablolarda hiyerarşileri bulmanın en kolay yöntemlerinden biri PATH-PATHITEM fonksiyonlarını kullanmak.
Kategori tablosundaki bu hiyerarşileri bulmak için bir hesaplanmış sütun ekleyelim PATH fonksiyonu ile:
Hiyerarşi=PATH('EcoResCategory'[RECID] ,EcoResCategory[ParentCategory] )
Hata verdi! Neye bozulduğunu da söylüyor ama : )
F&O ‘nun kategori yapısında en üstte “0” kategorisi var. ParentCategory’de geçiyor. Ama “0” RECID li bir satır yok bu tabloda! PATH-PATHITEM grubu da -by design- en alttan başladığında en üstteki kategoriye kadar her bir tanımı bulmak zorunda.
Eğer EcoResCategory tablosuna, RECID si 0 olan ve parent kategorisi olmayan bir satırı eklersek, bu sorunu çözmüş oluruz.
Manuel veri girişi için en uygun yapı “Enter Data” deyip tablo yaratmak.
DAX ile bu tabloları birleştirebilirz ama en uygun yer -bana sorarsanız- PowerQuery tarafı. Orijinal EcoResCategory tablosuna bu satırı da append ile eklediğimizde hiyerarşileri sorunsuz buluyor.
Artık kategorilerin kendi içlerindeki hiyearşilerini PATHITEM ile bulabiliriz.
Kaç seviye olduğunu da PATHLENGHT ile bulabiliriz.
Seviye Sayısı = PATHLENGTH('EcoResCategory'[Hiyerarşi])
Seviye-1 =
LOOKUPVALUE(
'EcoResCategory'[Name] ,
EcoResCategory[RECID] ,
PATHITEM(EcoResCategory[Hiyerarşi] , 2, INTEGER)
)
Seviye-2 =
LOOKUPVALUE(
'EcoResCategory'[Name] ,
EcoResCategory[RECID] ,
PATHITEM(EcoResCategory[Hiyerarşi] , 3, INTEGER)
)
… hepsini yazmaya gerek yok!
Seviye-5 =
LOOKUPVALUE(
'EcoResCategory'[Name] ,
EcoResCategory[RECID] ,
PATHITEM(EcoResCategory[Hiyerarşi] , 6, INTEGER)
)
Kategoriler tablosu artık tamamen hazır!
Metriklerinizi yazıp, son kullanıcının görmemesinin iyi olacağı tablo/sütunları da gizlerseniz “Item” tablomuz bu! Satınalma-stok-satış vs hepsi için kullanabiliriz.