Power BI dashboard

On this page

Introduction

To address challenges associated with using Power BI (PBI), SolarWinds developed customizable Power BI dashboard templates that can be easily integrated into your accounts. These templates cover most of the essential metrics, offering a ready-to-use solution.

Working with Power BI requires a certain level of expertise, and the platform may not be very user-friendly for those who are not familiar with it. Users may need to invest time in learning the tool to fully leverage its capabilities.

Overview

This topic provides an explanation for creators and stakeholders on how data is extracted from the SWSD custom connector and then transformed and loaded to Power BI.

The report template contains data for both tickets and assets.

Ticket overview	Asset management

Prerequisite

SWSD JSON web token. Instructions are included below.

Setup

Two options for setup are available. You can use either one or both.

• Set up the plug-and-play app
• Set up Power BI Desktop to customize dashboard

Set up the plug-and-play app

Step 1: Download and install

1. Navigate to the Microsoft AppSource website for the SolarWinds Service Desk Dashboard.

2. Click Get it now.

3. Provide the email address you use to sign into SWSD.

   The app installs. An example dashboard is visible, but it does not contain your current data.

Step 2: Enter your JSON web token and connect your data

1. Open the application and click Connect your data. You are prompted to enter a JSON Web Token.

   

2. Login to your SolarWinds Service Desk account.

3. Navigate to Setup > Users, and then click on your name to open your User details screen.

   • If you already have a JSON web token, click Show Token and then Copy.

     

   • If you don't already have a JSON web token, click Generate and then Copy.

4. Enter the token into Power BI.

Your connection is established and current data displays.

Set up Power BI Desktop to customize dashboard

Using Power BI desktop, you can modify the widgets or add information to them.

1. Ask your SolarWinds Service Desk Account Manager or Customer Service Manager for the template (pbix file).

2. Click Home > Transform Data > Data Source Settings.

   

3. Input your JSON web token generated from SWSD by clicking on data source SolarWinds Service Desk > Edit Permissions > Credentials > Edit.

   

4. Click Save and OK, and then refresh the data to get the latest using the SWSD JSON web token.

   

After you have your data, you can save the report template by clicking on File > Save.

KPI metrics

The following KPI metrics are required to be shown on the dashboard.

Data refresh

Data refresh occurs every day automatically. You can also set up your own data refresh schedule.

Set up data refresh schedule

After the report is published on the Power BI service (web version), do the following:

1. Go to the workspace, and then find your report and its corresponding semantic model.

   

2. Click the ellipsis(…) option in Semantic model, and then click Settings.

3. Scroll down until you see the Refresh section and click the right-facing arrow to expand Refresh

   

4. Click Configure a refresh schedule, and then configure the timing of your refresh (for example, time zone and time) and the recipients of refresh failure notifications.

   

5. Click Apply. The custom Refresh schedule is configured.

Data source

The current source of data used in the SolarWinds Service Desk Dashboard is:

Load the data to Power BI using the SolarWinds Connector

1. Click Get data, then click More.

2. In the search box, type SolarWinds Service Desk (Custom).

   

3. Click connect. Your data is pulled and loaded into your Power BI report.

Data processing

Additional tables, measurements, and columns are added after data loads. This allows the gathering of data needed for the required metrics.

Tables

Measurements

Assets Created = CALCULATE(COUNT(Assets[custom_id]), USERELATIONSHIP(Assets[created_at],DimDate[Date]))

Average Open Ticket Age = AVERAGEX(Incidents,'Incidents'[Ticket Age])

Broken Assets = 
VAR Broken_assets = CALCULATE(COUNT(Assets[custom_id]), FILTER(Assets, Assets[asset_state]="Broken"))
VAR Total_count = COUNT(Assets[custom_id])
RETURN
DIVIDE(Broken_assets, Total_count)

CSAT MOM = 
VAR
    _PREV =
        IF(NOT( ISBLANK( [CSAT SCORE] ) )
        , CALCULATE(
            [CSAT SCORE]
            ,PREVIOUSMONTH(DIMDATE[DATE])
        ))
RETURN
DIVIDE(([CSAT SCORE] - _PREV), _PREV)

CSAT Score = 
VAR CSATCount =
CALCULATE(COUNT(Incidents[number]), FILTER(Incidents, Incidents[customer_satisfied]= "Customer is satisfied"))
   
VAR AllCount =
CALCULATE(COUNT(Incidents[number]), FILTER(Incidents, Incidents[customer_satisfied]<> BLANK()))

Return
Divide(CSATCount,AllCount)

Operational Assets = 
VAR Operational_assets = CALCULATE(COUNT(Assets[custom_id]), FILTER(Assets, Assets[asset_state]="Operational"))
VAR Total_count = COUNT(Assets[custom_id])
RETURN
DIVIDE(Operational_assets, Total_count)

SLA MoM = 
VAR SLA_LM = CALCULATE([SLA Rate],PREVIOUSMONTH(DimDate[Date]))
RETURN
CALCULATE([SLA Rate] - SLA_LM)

SLA Rate = 
VAR Resolved_Count = COUNT(Incidents[resolved_at])
VAR Breached_SLA = COUNT(Incidents[sla_violations.sla_name])
RETURN
DIVIDE((Resolved_Count-Breached_SLA),Resolved_Count)

Tickets Created = CALCULATE(COUNT(Incidents[number]), USERELATIONSHIP(Incidents[Date Created],DimDate[Date]))

Tickets Resolved = COUNT(Incidents[Date Resolved])

Todays Date = TODAY()

TTR (Formatted) = 
VAR TTRDay = 
    INT([TTR Average]/( 24 * 60 * 60 )) 
VAR TTRHours = 
    MOD( INT([TTR Average] / (60 * 60)),24)
VAR TTRMin = 
    MOD( INT ([TTR Average] / 60), 60)
VAR TTRSec = 
    MOD( [TTR Average], 60)
RETURN
    TTRDay & "day(s)"
        & FORMAT( TTRHours, "#00" )
        & ":"
        & FORMAT( TTRMin, "#00" )
        & ":"
        & FORMAT( TTRSec, "#00" )

COMPUTATION TO GET THE AVERAGE TTR	TTR AVERAGE = AVERAGEX(INCIDENTS,INCIDENTS[SWSD TTR (SECOND)]) 

TTR MoM = 
VAR
    _prev =
        IF(NOT( ISBLANK( [TTR Average] ) )
        , CALCULATE(
            [TTR Average]
            ,PREVIOUSMONTH(DimDate[Date])
        ))
Return
CALCULATE([TTR Average] - _prev)

TTR MoM(Formatted) = 
VAR TTRDay = 
    INT([TTR MoM]/( 24 * 60 * 60 )) 
VAR TTRHours = 
    MOD( INT([TTR MoM] / (60 * 60)),24)
VAR TTRMin = 
    MOD( INT ([TTR MoM] / 60), 60)
VAR TTRSec = 
    MOD( [TTR MoM], 60)
RETURN
    TTRDay & "day(s)"
        & FORMAT( TTRHours, "#00" )
        & ":"
        & FORMAT( TTRMin, "#00" )
        & ":"
        & FORMAT( TTRSec, "#00" )

Columns

Date Created = Incidents[created_at].[Date]

Date Resolved = Incidents[resolved_at].[Date]

Incident - is_service_request = FALSE
Service Request – is_service_request = TRUE

Last Updated Age Bracket = 
SWITCH(
     TRUE(),
     'Incidents'[Ticket Updated Age]<=15, "0 - 15 days",
     'Incidents'[Ticket Updated Age]<=30, "16 - 30 days",
     'Incidents'[Ticket Updated Age]<=60, "31 - 60 days",
     'Incidents'[Ticket Updated Age]<=90, "61 - 90 days",
     "Over 90 days"
                )

Open Ticket Age Bracket = 
SWITCH(
     TRUE(),
     Incidents[Ticket Age]<=15, "0 - 15 days",
     Incidents[Ticket Age]<=30, "16 - 30 days",
     Incidents[Ticket Age]<=60, "31 - 60 days",
     Incidents[Ticket Age]<=90, "61 - 90 days",
     "Over 90 days"
                )

Closed
•	Closed
•	Resolved
Open = all state except the above

SWSD TTR (second) = IF(OR(ISBLANK(Incidents[closed_at]),ISBLANK(Incidents[resolved_at])),BLANK(), DATEDIFF(Incidents[created_at],Incidents[resolved_at],SECOND))

Ticket Age = DATEDIFF('Incidents'[created_at], TODAY(),DAY)

Ticket Updated Age = DATEDIFF(Incidents[updated_at].[Date], TODAY(),DAY)

Relationships

Dashboard

This section provides the information for piecing out and creating the dashboard template.

Ticket Overview

Demand pressure breakdown

This section provides an analysis of factors influencing the ticket demand pressure. It displays the trend of tickets created vs. resolved in a weekly manner and breaks down to categories.

Scorecard

This section displays KPI metrics from the overall team scorecard down to individual scorecards. This is mainly used to provide quick assessment of team/individual performance.

Open ticket management

This section displays a comprehensive list of open tickets, including key details such as ticket number, status, updates, priority, and assigned team members. It can be filtered by length of time since the ticket was created or updated.

Asset management

This section displays a high-level summary of all assets, including key information such as their asset status, asset tag, category, owner, and other related information. It can be filtered by their asset status and year or month created.

Known limitations

Known limitations of the SolarWinds Connector report template are:

• The custom connector can be refreshed once per day. If an error is encountered, contact your Customer Service Manager (CSM).
• The fields that are imported are limited. To request that more fields be added, submit a feature request in THWACK.
• Custom fields are not supported.
• Incremental refresh is not supported in the web service.
• Resolve groups are not implemented. Scorecard is currently by ticket analyst/assignee.
• Mobile layout is not available.

Related topics

Power BI Connector