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.
Expand the SystemCenterWorkItemsCube > Service Manager WorkItems Cube
Expand SLAInstanceStatus > ParentID. Select Level 03
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
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
I removed the SLAInstanceInformationCount column
Then I right clicked on the WorkItemDim_Id column, and clicked Unpivot Other Columns
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:
- https://gallery.technet.microsoft.com/PowerON-Power-BI-SCSM-e1c02a22
- https://community.powerbi.com/t5/Desktop/Combining-rows-based-on-unique-id-and-combining-information/m-p/29466#M9962
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"
This comment has been removed by the author.
ReplyDeleteVery 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
ReplyDeleteMua vé máy bay tại Aivivu, tham khảo
ReplyDeleteVé máy bay đi Mỹ
vé máy bay vietnam airline đi hồ chí minh
vé máy bay từ đà nẵng về hà nội
đi máy bay ra đà lạt
mua vé máy bay từ mỹ về việt nam hãng eva
taxi sân bay chiều về
combo bamboo airway đi quy nhơn
Designing Power BI Reports Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info.
ReplyDelete