SCSM Dashboard

Since Service Manager lacks a native capability to create dashboards, like OpsMgr does, one often uses the Data Warehouse capabilities to provide relatively recent information, mainly to manager and director levels. In order to do that, a few things need to be in place:

A SQL view, to generate information for the reports

Connect a SQL Management Studio to the server hosting the DW databases:

image

Create a new View. You can close the Add Table window.

image

In the SQL are, paste the Service Request query below:

SELECT        dbo.WorkItemDimvw.Id, dbo.ServiceRequestDimvw.Title, dbo.UserDimvw.DisplayName AS [Assigned To], UserDimvw_1.DisplayName AS [Affected User],
                         dbo.ServiceRequestSupportGroupvw.ServiceRequestSupportGroupValue AS Queue, dbo.ServiceRequestDimvw.CreatedDate, dbo.SLAInstanceStatusvw.SLAInstanceStatusValue AS SLAStatus
FROM            dbo.WorkItemDimvw LEFT OUTER JOIN
                         dbo.SLAInstanceInformationFactvw LEFT OUTER JOIN
                         dbo.SLAInstanceStatusvw ON dbo.SLAInstanceInformationFactvw.SLAInstanceStatusId = dbo.SLAInstanceStatusvw.SLAInstanceStatusId ON
                         dbo.WorkItemDimvw.WorkItemDimKey = dbo.SLAInstanceInformationFactvw.WorkItemDimKey LEFT OUTER JOIN
                         dbo.UserDimvw AS UserDimvw_1 RIGHT OUTER JOIN
                         dbo.WorkItemAffectedUserFactvw ON UserDimvw_1.UserDimKey = dbo.WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey ON
                         dbo.WorkItemDimvw.WorkItemDimKey = dbo.WorkItemAffectedUserFactvw.WorkItemDimKey RIGHT OUTER JOIN
                         dbo.ServiceRequestSupportGroupvw INNER JOIN
                         dbo.ServiceRequestDimvw INNER JOIN
                         dbo.ServiceRequestStatusvw ON dbo.ServiceRequestDimvw.Status_ServiceRequestStatusId = dbo.ServiceRequestStatusvw.ServiceRequestStatusId ON
                         dbo.ServiceRequestSupportGroupvw.ServiceRequestSupportGroupId = dbo.ServiceRequestDimvw.SupportGroup_ServiceRequestSupportGroupId ON
                         dbo.WorkItemDimvw.EntityDimKey = dbo.ServiceRequestDimvw.EntityDimKey LEFT OUTER JOIN
                         dbo.UserDimvw RIGHT OUTER JOIN
                         dbo.WorkItemAssignedToUserFactvw ON dbo.UserDimvw.UserDimKey = dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey ON
                         dbo.WorkItemDimvw.WorkItemDimKey = dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey

Hit the image to execute. You should get something like:

image

Now save the view:

image

Refresh the Views and it should be there:

image

2. Create a new Report in SSRS.

Navigate to your SSRS url:

image

Navigate to the Service Manager folder and create a Dashboards folder:

image

In the Dashboards folder, click on Report Builder (it will install if you don’t have it yet). Create on New Report as below:

image

Some initial visual configuration:

image

Create a Data Source:

image

 

image

image

Notice you are now using your own credentials to connect. Eventually, you’ll need to store credentials just so all the users can connect (since most of them won’t be allowed to query the DW directly).

image

Now, let’s create the dataset based on the View we created on step1:

image

Click on Query Designer:

image

image

Your dataset should look like this:

image

Adding a table:

image

 

image

image

image

Run it and it should look something like this:

image

(I have a funny queue entry there, but yours should look good!)

 

I will come back with some other queries for Incidents and Activities, to compose a complete dashboard.

 

Hope this helps!