One of the questions I get a lot around reporting for SCSM 2012 when using Power BI is how they can pull the Affected User for a Work Item. This sounds straight forward, but it’s really not due to the fact that all of the entities in SCSM are relationship based. Having the Work Item doesn’t mean you have the Affected User right there in front of you.
Today, I’m going to show you how to make that relationship via the Data Warehouse and Power BI to show who your most ‘needy’ users are.
The first thing we need to do is make sure we have the latest version of Power BI and open it up. Create a new Power BI project and then click ‘Get Data’. Select your DWDataMart database.
Once connected, we want to select all of the tables we will required. Make sure when selecting the tables you are choosing the ‘vw’ tables, as these are the processed ‘views’.
Here is a list of the tables I selected for this example:
Though for this particular example, you will really only require the WorkItemDimvw, the WorkItemAffectedUserFactvw, and the UserDimvw. Once all of these queries are set, right click on the WorkItemDimvw and ‘Reference’ it.
This will make a ‘reference’ copy of the WorkItem table, rather than duplicating the data. This reduces our overhead when processing the report. Following this lets rename the new reference table to ‘MyWorkItemDim’.
From this view, click the ‘Merge Queries’ option in the Home tab. Then select the WorkItemDimKey from this table, and the same column from the WorkItemAffectedUserFactvw.
This will create a new column, click the split arrows to see the dialogue below, uncheck select all and then select WorkItemAffectedUser_UserDimKey and click OK.
This will expand the previous column and leave us only with the Affected User dim key. From here we are going to click the ‘Merge Queries’ option again.
This time we are going to select the UserDimKey from the MyWorkItem table, and merge it with the UserDimKey from the UserDimvw table. This will create our relationship to the Affected User object.
Expand this new column just like we did the previous column we created.
This time select the ‘DisplayName’ column (or any identifying column you prefer) and expand it out.
This gives us our Affected User column showing us the display names. From here we will rename the column.
Remove the UserDimkey column that we no longer need to show.
And finally, we will click the ‘Close & Apply’ in the upper left to save our queries and close out the query editor. Now we will see our tables on the right hand side of our report. From here we can expand the ‘MyWorkItemDim’ query and select the AffectedUser and Id columns.
This will create a ‘table’ chart for us with the information selected.
Drag the ‘Id’ column from the Axis or Legend area down into the Value area. This will convert it to a ‘count’. Also, switch the chart type to a bar chart.
This gives us our Bar Chart, with all of the ticket count for each Affected User.
Now lets make this a little more effective, by modifying the chart information. Expand the AffectedUser filter and click ‘Advanced filtering’
Set the filter to exclude any tickets that don’t have an Affected User set (this is optional).
Now switch over to the Formatting view (the paintbrush icon) and move the legend to the right to make it more readable.
You can also turn on data labels if you want to see the count on the chart bars.
This finishes up our bar chart, but you will notice that if you have a large number of affected users, the chart is really squished so lets change to a chart time more conducive to this. I chose the Funnel chart, because I am most interested in my top ‘problem’ children.
I can then click the ellipses on the chart and sort it by Count of Id to put the largest number on the top of the funnel.
And that’s it! Now we have a chart that can be refreshed just by hitting ‘Refresh’ on the Home ribbon and see all the latest data. This will show us the WorkItem count for each affected user straight out of the data warehouse.
From here, we can make this even more useful by including the status as a filter and filtering out any closed tickets, or using the same methods described above we could bring in our Assigned Users and see which analyst have more open tickets, or who is closing the most tickets in a certain time period.
Hope this helps!