SCSM DWDatamart – Get Recent one-to-many Relationship Data

By | 03 January 2017

Bu makalemde sizlere raporlama ile ilgili önemli bir durumdan bahsedeceğim. SCSM mimarisine rapor yapanlar için oldukça önemli bir makale olacağını düşünüyorum.

Bilindiği üzre SCSM mimarisinde default olarak gelen raporlar çok zengin içeriklere sahip değiller. Bu yüzden bir çok SCSM yöneticisi istediği raporlar için custom rapor yapmayı tercih eder. SCSM SQL reporting service altyapısını kullandığı için, bu konuda bilgi sahibi olan kişiler kolaylıkla istenilen raporları yapabilir.

SCSM mimarisinde her şey class’lara ait objelerdir. (Incident,Service Request,Windows User,Windows Computer vs.) Bu class’lar birbirleri ile relationship’lik kurarlar. Örneğin incident class’ı ile user calss’ı birbirleri ile relationshiplik kurarlar. Relationshiplik kurdukları class’lara göre isim alırlar. (Affected User,Assigned To User,Created By User,Resolved By User vs) Class’ların birbiri ile relationship kurmaları bazen one-to-many (Incident and AssignedToUser) bazen ise many-to-many (Incident ve Affected Configuration Item) olabilir.

One-to-many relationsip’lerde one olarak ifade edilen taraf yeni bir obje ile relationship’lik kurması gerektiğinde mevcut olanı silip yok eder. Örnek olarak her Incident’a bir affected user atanabilir. Aynı incident yeni bir kişiye atanacağı zaman eski kişi ile olan bağlantının silinmesi gerekir.

Şimdi asıl konuya gelelim. Service manager data warehouse mimarisinde DWDatamart içerisinde relationshiplik ile ilgili kayıtlar Fact tablolarında tutulur. Örneğin herhangibir workitem’ın atandığı tüm kullanıcılara ait bilgiler WorkitemAssignedToUserFact tablosunda her biri ayrı satır olacak şekilde tutulur. Bu bazen istenilen bazense istenmeyen sonuçlar doğurabilir. Örneğin bir workitem’a yaşam döngüsü boyunca hangi kullanıcıların atandığı raporlanmak istenirse bu mimari oldukça faydalı olacaktır. Buna karşın ilgilenilen kişi workitem üzerinde en son atanmış kullanıcı ise (Genel olarak workitem’ı çözümleyen kişi) yada one-to-many relationship kurmuş başka bir class’a ait son relationship kurmuş obje ise raporlama yaparken göz önünde bulundurulması gereken bazı durumlar mevucttur.

Örneğin WorkItemAssignedToUserFact tablosu incelendiğinde tablo üzerinde DeletedDate isimli bir kolon görünecektir. Bu kolon date/time formatındadır ve workitem ile assignedToUser arasındaki relationship’lik sona erdiğinde buraya tarih atmaktadır. Bu sebeple WorkItem üzerindeki aktif relationship her zaman NULL değerine sahiptir. Yani NULL değeri workitem üzerindeki en son atanmış kullanıcıyı göstermektedir. Şimdi buraya kadar her şey normal. Asıl sıkıntı bundan sonra başlıyor J

Workitem’lar belirlenen sürelerde groom olmaları gerekir. Grooming ile ilgili ayrıntılı bilgiyi aşağıdaki makalelerimde bulabilirsiniz.

Grooming – Part 1

Grooming – Part 2

Grooming – Part 3

Grooming işlemi olduğunda workitem’a ait relationshiplikler silinir ve DeletedDate kolonundaki NULL ifadesi yerine date/time değeri atanır. Tabi böyle bir durum olduğunda OOB olarak gelen default raporlar ve bunlardan türetilmiş custom raporlar üzerinde relationship kurulumuş objeler görünmezler. Örneğin rapor üzerindeki AssignedToUser bilgisinin boş olması gibi.

Hal böyle olunca rapor artık AssignedToUser bilgisi, DalatedDate kolonu NULL olmadığı için görüntülenemeyecektir.

Sorun anlaşıldığında göre işin çözüm aşamasına geçebiliriz. Çözüm aşamasında yapılması gereken iş aslında basit. DalatedDate’i NULL olan bir kayıt olmadığına göre, karşılaştırma yapılacak değer atanan DeletedDate tarihleridir. Böylece en son Assign edilmiş kullanıcı bu yöntem ile kolayca belirlenebilir.

İşlem yapılırken artık deletedDate kolonu ile ilgilenilmediği için Coalesce fonksiyonu kullanlarak Null değerler o anki date bilgisi ile değiştirilmelidir. Bu işlemi yaparken de GetDate() metodu ile geçerli zaman elde edilip deletedDate kolonuna atanmalıdır. Bu işlemin ardından Max() fonksiyonu kullanılarak her bir workitem için en son atanmış date değeri elde edilir.

 

Aşağıdaki query ile DWDatamart içerisindeki enson atanmış kullanıcılar kolaylıkla get edilebilir.

SELECT WI.Id AS [WorkItem ID], AssignedInfo.DisplayName AS [Assigned To User] FROM WorkItemDimvw WI LEFT JOIN (Select Max(COALESCE(DeletedDate,getdate())) as [max deleted date],WorkItemDimKey from WorkItemAssignedToUserFactvw assignedFact group by WorkItemDimKey) assignedfact on wi.WorkItemDimKey = assignedfact.WorkItemDimKey LEFT JOIN WorkItemAssignedToUserFactvw assignedfact2 on WI.WorkItemDimKey = assignedfact2.WorkItemDimKey AND Coalesce(assignedfact2.DeletedDate,GETDATE()) = assignedfact.[max deleted date] LEFT JOIN UserDimvw AssignedInfo on AssignedFact2.WorkItemAssignedToUser_UserDimKey = AssignedInfo.UserDimKey where WI.Id IS NOT NULL

Umarım bu işlem yapacağınız custom raporlarda işinize yarar.

Bir sonraki makalede görüşmek üzere.

Kaynak: https://blogs.technet.microsoft.com/scsmfromthefield/2016/10/26/get-the-most-recent-assigned-to-user-or-any-other-relationship-with-one-to-many-cardinality/

Fırat

 

Leave a Reply