© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
J. M. RhodesCreating Business Applications with Microsoft 365https://doi.org/10.1007/978-1-4842-8823-8_7

7. Power BI Case Study: Monitoring BMC Remedy Help Tickets

Jeffrey M. Rhodes1  
(1)
Colorado Springs, CO, USA
 

Power BI can also be used for non-Microsoft 365 content. This chapter is a case study on using Power BI to visualize data directly from a back-end database. While it takes some work in finding and massaging the data, the payoff can be huge.

A Remedy Dashboard

Remedy is an IT Service Management software that manages incidents, work orders, and other “Help Desk” items. An important task is monitoring ticket status, how long they have been open, whether they have been assigned, and other tasks. Before Power BI, this had been accomplished by exporting data from Remedy to Excel and then creating charts. Not only was this labor-intensive, but the data was stale by the time it was presented at a staff meeting. Figure 7-1 shows a sample of using Power BI instead. It covers data from the previous 90 days and shows the number of tickets by support group (section), the status, the organization (Mission Element) with the issue, who submitted the ticket,1 who the ticket was assigned to, etc.

A screenshot of power B I dashboard. On the left are 2 stacked bar graphs for incidents and work orders. On the right are 2 tables with ticket details and status.

Figure 7-1

Power BI dashboard showing Remedy ticket data

Configuring the Power BI Data Source

Remedy did not expose a data source that could be used by Power BI, so I went straight to its database (Microsoft SQL Server in our case). It took some effort to find the right data, but eventually discovered the BMC developers had created views to match the names of the relevant Remedy forms : CTM_People, HPD_HelpDesk, and WOI_WorkOrder . 2 Looking at the data, one immediate issue was that the dates were all numbers. A quick search found these were being stored in Unix or POSIX time, which the number of seconds since January 1, 1970. This is one of the many things we can address once we get to Power BI.

Within Power BI, we choose SQL Server database as the data source and then enter the Server, Database, and Credentials. 3 We add a query for each of our desired views.

The only reason we need CTM_People is to look up the first and last name of the support staff. As explained in a previous footnote, the mechanics of “Single Sign-On” via a Common Access Card (CAC) meant the login ID displayed within Remedy was a bunch of numbers. Remedy forms take care of this for the assignee of the ticket but not for the submitter of the ticket. We can use this CTM_People view to fix the issue. As shown in Figure 7-2, we set up a relationship between these tables and link the Submitter and Remedy_Login_ID columns. When it comes time to display who submitted the ticket, we grab the name from CTM_People in order to display the actual name.

A table titled manage relationships is visible. It has 3 columns: active, from, and to. A file from H P D help desk to C T M people is selected. Buttons for new, autodetect, edit, delete are visible below.

Figure 7-2

Relationships between the CTM_People and HPD_HelpDesk/WOI_Workgroups views

For both HPD_HelpDesk (which contains incidents) and WOI_WorkOrders (which contains work orders4), we create a custom SubmitDateCustom column using this formula:
SubmitDateCustom = #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, -6, 0, [Submit_Date])
This converts from the Unix date discussed earlier. The duration function takes account of the time zone so the time is Mountain rather than Coordinated Universal Time (UTC) , previously known as Greenwich Mean Time. As with previous examples, we create a DaysPassed column that shows the number of days since the SubmitDateCustom :
DaysPassed - DateTime.From(DateTime.LocalNow()) - [SubmitDateCustom]

This is particularly important here as we want to flag (and do special visualizations) for any ticket open longer than our service level agreement (such as seven days). The main other challenge was that the Status was stored as a numeric value. We duplicated the Status column and then Replaced Values with the corresponding text values (Assigned, In Progress, Completed, Resolved, etc.).5

One of my later tasks was to manage a particular support team that used this same system. This often involved submitting tickets to other support teams. It was useful to follow up on these tickets. Figure 7-3 shows an example of how this approach can be customized. We used the same queries as before but then filtered the data to ONLY show tickets submitted by the members of my particular support team. The report then showed exactly who is assigned to that ticket as well as its current status.

A screenshot of the power B I. On the left are 2 pie charts for incidents and work orders by section. On the right are 2 tables with ticket details.

Figure 7-3

Power BI dashboard showing the status of tickets submitted by a particular support team

This just scratches the surface of what this kind of approach can do. We can look at which organizations and users submit the most/least tickets and which support groups and technicians handle them. We could even integrate our survey data from previous chapters to show that data and flag any correlations.

Summary

In this chapter, we connected to BMC Remedy to visualize Help Ticket data directly from the back-end data source. It illustrates one of the most powerful aspects of the product, which is to transform raw data and skip the traditional process of spreadsheets, pivot tables, and the like.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.116.146