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:



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

0 comments:

Post a Comment