Power BI Connector

On this page

Introduction

The Service Desk Power BI Connector lets you incrementally import incidents and different asset records from Service Desk to Microsoft Power BI. The import provides raw data on the topics most relevant to your organization. You can then review and analyze the data by viewing it in a variety of formats, for example, in tables, graphs, and charts.Some customers will also find the Power BI dashboard useful.

SolarWinds owns the connector and is a member of the Microsoft Power Query Connector Certification Program. If you have questions regarding the content of this article or want to request a change to it, provide feedback at the bottom of the page.

Connect to Service Desk

Current version of Power BI Connector: V113

Instructions

1. In Power BI Desktop, select Get Data from Home. Select SolarWinds Service Desk, and then select Connect.

   

2. Sign in with the JSON Web Token you generated as described in the Prerequisites, and then click Connect to verify your access to Service Desk.

   If you have an ESM account, the JSON token you use for the connector must be generated by a Service Provider Administrator.

   

3. In the Navigator dialog box, select the table you want to import, and then click Load.

   

After completion of the steps above, you can see your chosen models load under Data view. The records you just imported were updated or created in Service Desk during last month.

Additional steps for previous users of the BETA connector

If you previously used the BETA Service Desk Power BI Connector to create reports, and you already have the model name Incidents and reports based on it, and you wish to connect those reports with the new out-of-the-box version:

1. Go to the Incidents model, and right-click Edit query. A Power Query Editor opens.

   

2. Select the Incidents query and open Advanced Editor.

   

   You see the following lines:

   Source = SolarWindsServiceDesk.Contents(),

   Incidents1 = Source{[Name="Incidents"]}[Data]

3. Replace those lines with the following lines:

   Source = SolarWindsServiceDesk.ContentsV113(null, null, null),

   #"All Objects" = Source{[Name="All Objects"]}[Data],

   Incidents1 = #"All Objects"{[Name="Incidents"]}[Data]

4. Click Done.

5. Click Close & Apply.

The reports display data from the past month instead of the last two years. Going forward, those reports are connected to the new Service Desk Power BI Connector.

Set the Date range by using RangeStart and RangeEnd parameters

1. On the Home menu bar, click Transform Data. A Power Query Editor opens.

2. From the Power Query Editor toolbar, select Home > Manage Parameters > New Parameter.

3. Create two new DateTime parameters named RangeEnd and RangeStart.

   From the DateTime parameters, the connector ignores the Time, and uses only the Date. The new parameters set the filtering range of the pulled data, based on the updated_at column. This reduces download time and filters your data based on the range you set. The updated_at column contains the last time the record was updated in Service Desk in the Server TimeZone. This is not the same as what is shown in the UI; there it is based on the Account TimeZone setting. Because of this difference, when filtering the index, you might see a gap between the information there and what you see in a Power BI report. The filtering range provided by adding the parameters is record.updated_at >= RangeStart(Date) and record.updated_at < RangeEnd(Date). Notice the difference between less than RangeEnd and larger than and equals RangeStart.

   Set enough time between the parameters to allow you to pull sufficient records for your reports, but not too large. Select a range that won't cause you to wait a long time for every refresh. See Microsoft's instructions: create-parameters.

   Example

   To pull records that were updated during a one-month period between Nov 10, 2023 and Dec 10, 2023 (not included), you would use:

   RangeStart = 11/10/2023 12:00 AM

   RangeEnd = 12/10/2023 12:00 AM

   The filtering range provided by adding those parameters is record.updated_at >= Nov 10, 2023 and record.updated_at < Dec 10, 2023.

   In this example, records that were updated by Dec 10, 2023 will not be pulled.

   

4. For each query you wish to set with Date range, open Advanced Editor.

   The following line displays:

   Source = SolarWindsServiceDesk.ContentsV113(null, null, null),

5. Replace the line with the following:

   Source = SolarWindsServiceDesk.ContentsV113(RangeStart, RangeEnd, null),

   The third parameter relates to custom fields.

6. Click Done.

7. Click Close & Apply.

8. Go to Data view.

Incremental Refresh

Incremental Refresh saves download time after your initial download is performed.

For each model you’ve downloaded, define the policy for Incremental refresh and real-time data as described below.

1. Set the option for archiving data to meet your reporting needs. Enter a number in the first field and use the dropdown to select a time frame.

2. Incrementally refresh data starting 1 Days before the refresh date. See define-policy for instructions.

   

Optional: Add custom fields to Power BI models

To include custom fields from Service Desk in your Power BI models:

1. Navigate to the Home tab.

2. On the Home menu bar, click Transform Data. A Power Query Editor opens.

3. From the Power Query Editor toolbar, select Home > Manage Parameters > New Parameter.

4. Create new parameter with the following:

   • Name: CustomFieldName

   • Type: Select Text from the dropdown menu

   • Suggested Values: Any value

   • Current value: Specify the custom fields you want to add to model.

     • Enter the custom field names exactly as they appear in Service Desk (be sure to use the custom field name; not its ID).

     • To add multiple custom fields, separate them with commas.

   

5. Click OK in the lower right.

6. In Power Query Editor select the Incidents query, then navigate to Home > Advanced Editor.

   • For those who set the RangeStart and RangeEnd parameters, the first line may appear as follows:

     Source = SolarWindsServiceDesk.ContentsV113(RangeStart, RangeEnd, null),

   • For those who have not set the RangeStart and RangeEnd parameters, the first line should appear as follows:

     Source = SolarWindsServiceDesk.ContentsV113(null, null, null),

7. Replace the third null parameter with parameter name you set in Step 4 of this process.

   Source = SolarWindsServiceDesk.ContentsV113(null, null, CustomFieldNames),

8. Click Done in the lower right.

9. From the Power Query Editor, navigate to File and select Close & Apply.

10. Using the example above, in the Data View expand Incidents model. See that in addition to the standard incident fields, you can also see the custom fields specified in the CustomFieldNames parameter. This allows you to integrate custom fields in your reports much more easily.

11. To add custom fields on other models (for example, Hardwares, Softwares, Other Assets, and others) repeat the entire process for each model individually.

    For models containing different custom fields, you should create a new parameter for each model (for example, for the model named mobiles, create a new parameter name MobileCustomFieldNames).

Publish reports

All customers publishing to the Power BI Service need to perform the steps below. This applies to both first-time publishers and those who have published in the past.

1. Publish the report to Power BI Service Workspace, based on the out-of-the-box connector.

2. Establish settings for each published semantic model in the Power BI Service.

   

3. For each semantic model, open the Data Source Credentials section and edit the credentials with the key and token you used when you established the connection with the connector in Desktop Power BI.

   

Your reports in Power BI Desktop and Power BI Service are now connected to and using the latest version of Service Desk Connector.

You can refresh each semantic model for the first time. The refresh downloads the archived data you defined in the incremental refresh policy (records from a maximum of 2 years ago).

SolarWinds suggests setting the first large download with a scheduled refresh. Optionally, you can run it manually by clicking Refresh the semantic model.

For instructions on configuring a scheduled refresh see scheduled-refresh.

After the first refresh, future refreshes go much more quickly and download only the new records from Service Desk.

Limitations and issues

Users should be aware of the following limitations and issues associated with accessing Service Desk:

• There is no limit on the number of users who can pull data, but if they are not using Incremental Refresh and try to import data from a range larger than one year, each user can refresh only once every 24 hours.

• The only way to pull data beyond two years is by using the Power BI Connector.

• The fields that are imported are limited. For additional fields please submit a feature request in THWACK. For feedback or support please reach SolarWinds support.

Credential error in the Navigator

If a credential error occurs in the Navigator, clear your recent data source settings.

1. In Power BI Desktop, select File > Data source settings.

   

2. Select the Service Desk data source, and then select Clear permissions.

3. Establish the connection to the Navigator again.

Related topics

Power BI dashboard