Add SLA's to PowerON SCSM Power BI template


The PowerON SCSM Power BI Dashboard template is a great tool to use for analyzing data inside of System Center Service Manager. But it lacks one thing: Service Level Agreement info.

In this blog post, I am going to show you what I did to connect SLA information into the Power BI template

1) Download and set up the Power BI template from
https://gallery.technet.microsoft.com/PowerON-Power-BI-SCSM-e1c02a22
Follow their directions to get your information into the template, and save it to a report.

As you see above, I rearranged the visualizations, and added in some additional slicers. You can do this too, however it is not necessary.

2) Connect to your SCSM DataMart database as SQL Server Analysis Services database.
Enter the name of the SQL server, but don't specify the database
Expand the SystemCenterWorkItemsCube > Service Manager WorkItems Cube

Expand SLAInstanceStatus > ParentID. Select Level 03

Expand WorkItemDim and select WorkItemDim_Id and click OK

The problem that we face now is that the data is loaded into Power BI, but each ticket has a duplicate database entry for when it gets assigned a status. For an example, lets take a look at SR82481
As the status gets changed, the database entry does not get changed, it gets a new entry.
Let's manipulate our data so we can get around this.

3) Next we are going to add 4 new conditional columns. one for each type of status (Breached, Warning, Met, Active). If it didn’t fit the rule I left it blank

And I ended up with a table that looked like this:

I removed the SLAInstanceInformationCount column

Then I right clicked on the WorkItemDim_Id column, and clicked Unpivot Other Columns

It made my table look like this:

Then I clicked on the Attribute Column > Transform > Any Column > Pivot Column > Chose “Value” in the Values Column

It combined all my columns together, making it look like this:

To translate this to values I could use, I added in another conditional column

To make it look like this so that there are no duplicate work item IDs.

You can clean up, hide columns, and rename columns now.

Now you can add in visuals that leverage this new SLA information

One other thing that I had to do was that I needed to switch the ID fields in all the other visuals in the PowerON template from the "Incidents[ID]" and "Service Requests[ID]" to the ID in my SLA table. That way the SLA slicers in my report could slice the visuals correctly on SLAs.



References:

---------------------------------------------------------------------------------------------
Update: Sept 3, 2020
Update Rollup 2 for SCSM broke the above functionality of the report. Using some SQL magic, I was able to hook it back up again. 
1) Transform your data
2) Right Click on Incidents, and choose Advanced Editor
3) Paste this in place of the query that is in there:

let
 
Language = Language, 
FilterDays =Text.From(#"Filter Days"),     
Source = Sql.Database(#"SCSM DW SQL Server", #"SCSM DW SQL Database", [Query="

/* Variable for filtering number of days */
DECLARE @FilterByLastDays int =" & FilterDays & "
IF (@FilterByLastDays = '0')
   SET @FilterByLastDays = Convert(int, (Select MIN([CreatedDate]) FROM [IncidentDimvw] ))


/*
SQL Query
Incidents
*/


Declare @Language varchar(3) = '" & Language &"'

--Declare @Language varchar(3) = 'ENU'
--Declare @SupportUnits int = '30'

;WITH IRClassification as

(
SELECT 
IncidentClassificationId  Id, 
ParentId,
ID EnumID, 
d.DisplayName, 
Ordinal, 
1 AS Level, 
cast(':' + cast(IncidentClassificationId as varchar) + ':' as varchar (100)) AS Struc,
cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName
FROM dbo.IncidentClassification AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId
WHERE ParentId = 1
UNION ALL

SELECT 
IncidentClassificationId Id, 
t.ParentId,
t.id enumID, 
d.DisplayName, 
(i.Level+1)*1000 + t.Ordinal, i.Level+1, 
cast(i.Struc + cast(IncidentClassificationId as varchar)+ ':' as varchar(100)) AS Struc,
cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName
FROM dbo.IncidentClassification AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId
JOIN IRClassification i on i.Id = t.ParentId
),

Classifications As
(
SELECT 
ISNULL(Parent.Id,Child.Id) ParentId,
Child.ID ChildId,
Child.enumID,
ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Classification], 
Child.DisplayName Classification, 
SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Classification Path]
FROM IRClassification Child
LEFT OUTER JOIN IRClassification AS Parent ON Parent.ID = Child.ParentId
--ORDER BY Child.FullName
),

IRResolutionCategory as
(
SELECT 
IncidentResolutionCategoryId   Id, 
ParentId,
ID EnumID,  
d.DisplayName, 
Ordinal, 
1 AS Level, 
cast(':' + cast(IncidentResolutionCategoryId as varchar) + ':' as varchar (100)) AS Struc,
cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName
FROM dbo.IncidentResolutionCategory  AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId
WHERE ParentId = 1
UNION ALL

SELECT 
IncidentResolutionCategoryId  Id, 
t.ParentId,
t.id enumID,  
d.DisplayName AS SupportGroup, 
(i.Level+1)*1000 + t.Ordinal, i.Level+1, 
cast(i.Struc + cast(IncidentResolutionCategoryId as varchar)+ ':' as varchar(100)) AS Struc,
cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName
FROM dbo.IncidentResolutionCategory AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId
JOIN IRResolutionCategory i on i.Id = t.ParentId
),

ResolutionCategory AS
(
SELECT 
ISNULL(Parent.Id,Child.Id) ParentId,
Child.ID ChildId,
Child.enumID,
ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Incident Resolution Category], 
Child.DisplayName [Incident Resolution Category] , 
SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Incident Resolution Category  Path]
FROM IRResolutionCategory Child
LEFT OUTER JOIN IRResolutionCategory AS Parent ON Parent.ID = Child.ParentId
),

IRTierQueues as
(
SELECT 
IncidentTierQueuesId   Id, 
ParentId,
ID EnumID,  
d.DisplayName, 
Ordinal, 
1 AS Level, 
cast(':' + cast(IncidentTierQueuesId as varchar) + ':' as varchar (100)) AS Struc,
cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName
FROM dbo.IncidentTierQueuesvw  AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id
WHERE ParentId = 1
UNION ALL

SELECT 
IncidentTierQueuesId  Id, 
t.ParentId,
t.id enumID,  
d.DisplayName AS SupportGroup, 
(i.Level+1)*1000 + t.Ordinal, i.Level+1, 
cast(i.Struc + cast(IncidentTierQueuesId as varchar)+ ':' as varchar(100)) AS Struc,
cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName
FROM dbo.IncidentTierQueuesvw AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id
JOIN IRTierQueues i on i.Id = t.ParentId
),

TierQueues AS
(
SELECT 
ISNULL(Parent.Id,Child.Id) ParentId,
Child.ID ChildId,
Child.enumID,
ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Tier Queue], 
Child.DisplayName [Tier Queue] , 
SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Tier Queue  Path]
FROM IRTierQueues Child
LEFT OUTER JOIN IRTierQueues AS Parent ON Parent.ID = Child.ParentId
),

IRSource AS
(
SELECT 
ID EnumID,  
d.DisplayName,
Ordinal
FROM dbo.IncidentSourcevw  AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id
),

IRStatus AS
(
SELECT 
ID EnumID,  
d.DisplayName,
Ordinal
FROM dbo.IncidentStatusvw  AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id
),

IRImpact AS
(
SELECT 
ID EnumID,  
d.DisplayName,
Ordinal
FROM dbo.IncidentImpactvw  AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id
),

IRUrgency AS
(
SELECT 
ID EnumID,  
d.DisplayName,
Ordinal
FROM dbo.IncidentUrgencyvw  AS t
INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id
)



--------------------------- Incident Query Below ---------------------------------------
Select distinct 
I.ID,
I.Title,
I.Description, 
'Created Date' = DATEADD(HOUR,-6,I.CreatedDate),
'Resoved Date' = DATEADD(HOUR,-6,I.ResolvedDate),
'Created Time' = CAST(DATEPART(HOUR, DATEADD(HOUR,-6,I.CreatedDate)) AS char(2)) + ':' + CAST(DATEPART(MINUTE, DATEADD(HOUR,-6,I.CreatedDate))/30*30 AS char(2)),
'Resolved Time' = CAST(DATEPART(HOUR, DATEADD(HOUR,-6,I.ResolvedDate)) AS char(2)) + ':' + CAST(DATEPART(MINUTE, DATEADD(HOUR,-6,I.ResolvedDate))/30*30 AS char(2)),
/*
CASE
WHEN SUM(TimeInMinutes) IS NULL THEN 0
ELSE SUM(TimeInMinutes)
END AS 'Support Minutes',

CASE 
WHEN SUM(TimeInMinutes) > @SupportUnits THEN ROUND(SUM(TimeInMinutes)/@SupportUnits,0)
WHEN SUM(TimeInMinutes) between 5 and @SupportUnits THEN 1
ELSE 0
END as SupportUnits,
*/
iclf.[Parent Classification],
slaConfig.DisplayName as 'SLA Type',
sla.IsBreached as 'Breached',
sla.StartDate as 'SLA Start Date',
sla.TargetEndDate as 'SLA Target',

CASE
WHEN iclf.Classification IS NULL THEN 'Unclassified'
ELSE iclf.Classification
END AS 'Classification',

iclf.[Full Classification Path],
iclv.IncidentClassificationValue AS 'Incident Classification',

Afuu.Company 'Affected User Company',
Afuu.Department 'Affected User Department',
Afuu.Office 'Affected User Office',  

CASE
WHEN Afuu.DisplayName IS NULL THEN 'Unassigned'
ELSE Afuu.DisplayName
END AS 'Affected User',


asuu.Company 'Assigned User Company',
asuu.Department 'Assigned User Department',
asuu.Office 'Assigned User Office',  


CASE
WHEN asuu.DisplayName IS NULL THEN 'Unassigned'
ELSE asuu.DisplayName
END AS 'Assigned User',

CASE
WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName = 'Active' THEN 'Unresolved'
WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName = 'Closed' THEN 'Unspecified Resolution'
WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName IS NULL THEN 'Unresolved'
ELSE ircv.[Incident Resolution Category]
END AS 'Incident Resolution Category',

CASE
WHEN irtq.[Parent Tier Queue] IS NULL THEN 'Unassigned'
ELSE irtq.[Parent Tier Queue]
END AS 'Parent Tier Queue',

CASE
WHEN irtq.[Tier Queue] IS NULL THEN 'Unassigned'
ELSE irtq.[Tier Queue]
END AS 'Tier Queue',



CASE
WHEN iscv.DisplayName IS NULL THEN 'Unassigned'
ELSE iscv.DisplayName
END AS 'Source',

CASE
WHEN isv.DisplayName IS NULL THEN 'New'
ELSE isv.DisplayName
END AS 'Status',


iriv.DisplayName 'Impact',

iruv.DisplayName 'Urgency'

From IncidentDimvw I

-- Work Item
left outer join WorkItemDim W on I.EntityDimKey = W.EntityDimKey AND W.IsDeleted = 0

--Billable Time
--left outer join WorkItemHasBillableTimeFactvw BFact on W.WorkItemDimKey = BFact.WorkItemDimKey and Bfact.DeletedDate is Null
--left outer join BillableTimeDimvw B on BFact.WorkItemHasBillableTime_BillableTimeDimKey = B.BillableTimeDimKey

-- Affected User
left outer join WorkItemAffectedUserFactvw afu on W.WorkItemDimKey = afu.WorkItemDimKey AND  afu.DeletedDate IS NULL
left outer join UserDim afuu on afu.WorkItemAffectedUser_UserDimKey = afuu.UserDimKey


-- Assigned to User
left outer join WorkItemAssignedToUserFactvw asu on W.WorkItemDimKey = asu.WorkItemDimKey AND asu.DeletedDate IS NULL
left outer join UserDim asuu on asu.WorkItemAssignedToUser_UserDimKey = asuu.UserDimKey

-- SLA
left outer join SLAInstanceInformationFactvw sla on W.WorkItemDimKey = sla.WorkItemDimKey
left outer join SLAConfigurationDim slaConfig on sla.SLAInstanceStatusId = slaConfig.SLAConfigurationDimKey

-- Status List
left outer join IRStatus isv on I.Status = isv.EnumID

-- Source List
left outer join IRSource iscv on I.Source = iscv.EnumID

-- Impact List
left outer join IRImpact iriv on I.Impact = iriv.EnumID

-- Urgency List
left outer join IRUrgency iruv on I.Urgency = iruv.EnumID

--Classification List
left outer join IncidentClassification iclv ON iclv.IncidentClassificationId = I.Classification_IncidentClassificationId
left outer join Classifications iclf on iclv.IncidentClassificationId = iclf.ChildId

--Resolution Category List
left outer join ResolutionCategory ircv on I.ResolutionCategory = ircv.EnumID 

--Tier Queue List
left outer join TierQueues irtq on I.TierQueue = irtq.EnumID 



Where I.IsDeleted <> 1 and I.CreatedDate between GETDATE()-@FilterByLastDays and GETDATE() 
Group by I.ID, 
Afuu.DisplayName, Afuu.Company, Afuu.Department, Afuu.Office, 
asuu.DisplayName, asuu.Company, asuu.Department, asuu.Office, 
i.IsDeleted, W.IsDeleted,
I.Classification_IncidentClassificationId,
I.CreatedDate,
I.ResolvedDate, 
iscv.DisplayName,
isv.DisplayName, 
iriv.DisplayName,
iruv.Displayname,
I.Title, I.Description,  I.Classification, I.Classification_IncidentClassificationId,i.ResolutionCategory,
slaConfig.DisplayName,sla.IsBreached, sla.StartDate, sla.TargetEndDate
,iclf.[Parent Classification], iclf.Classification, iclf.[Full Classification Path]
,ircv.[Incident Resolution Category]
,irtq.[Parent Tier Queue], irtq.[Tier Queue]
,iclv.IncidentCLassificationValue
Order by I.ID ASC

/* End SQL Query */
"]),
    #"Parsed Time1" = Table.TransformColumns(Source,{{"Created Time", each Time.From(DateTimeZone.From(_)), type time}}),
    #"Sorted Rows" = Table.Sort(#"Parsed Time1",{{"Created Date", Order.Ascending}})
in
    #"Parsed Time1" 

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.
    Blogger Comment

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Very useful post. This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. Really its great article. Keep it up. inventory template

    ReplyDelete