Create a Power BI Report From Data Loaded by Service Manager REST API

Last modified on 2023/01/25 16:36
Contents
HeaderNotes_AboutThisIntegration

IntegrationIntroduction

You can integrate third-party systems with Service Manager in many ways. The method you select will depend on business requirements, architectural and security constraints as well as the characteristics specific to each third-party application or service.

This document describes an integration process that has already been implemented in a customer context. 

To find out more about this integration (e.g. scripts to be used), please do not hesitate to contact your EasyVista contact, or your service provider and integrator.

HeaderNotes_CautionPerformances

   Caution: Extracting and refreshing high volumes of Service Manager data when running Power BI reports can severely impact the performances of the Service Manager platform.

  • If you work in an international organization with different service hours for each country, you should run Power BI reports on a dedicated Report server, rather than on the production environment. Should you need more details, please contact your EasyVista consultant.

Overview of the integration

This integration enables you to create a Power BI report using data retrieved by the Service Manager REST API.

You do this in two main steps:

  • Extract data from Service Manager in JSON format using the Execute an internal query method of the REST API.
  • Build a Power BI report using this data.
     
Integration Data Feed (direction) Authentication Type Integration Connector Options

Logo - EV Service Manager.png

Down arrow.png

Logo Icon - Power BI.png
  • Depending on the third-party service used (authentication managed by Microsoft Power BI)
  • Basic Authentication for the Service Manager REST API
Available versions
SaaS-based On-premises
Service Manager Check icon.png Check icon.png
Power BI Desktop Check icon.png N/A

Example

Create a Power BI report displaying the list of late incidents

  • The list of late incidents is displayed in List mode in Service Manager.
  • Data in JSON format is retrieved in Power BI Desktop using the Execute an internal query method of the Service Manager REST API.
  • The Power BI report is created.
     
Service Manager Power BI report

Example Service Manager - Late incidents.png

Example Power BI Desktop - Late incidents.png

Step-by-Step Integration Process

Prerequisites

1. Install Microsoft Power BI Desktop on your workstation.

     You can download it free of charge.

2. Retrieve the URL of the Service Manager report. It will be required when configuring the Power BI report (step 1).

Note: Your profile must be authorized to display the REST API URL menu.

  • Display the Service Manager screen for which you want to create a Power BI report.
  • Click Tools icon.png in the top banner and select the Rest API Url option.
    The URL of the active page is generated and copied to the clipboard as follows:
https:~/~/{your_server}}/api/v1/{your_account}/internalqueries?queryguid={query guid id}&filterguid={filter guid id}&viewguid={view guid id}
  • Paste the clipboard content in your text editor in order to store it temporarily. It will be required when configuring the Power BI report (step 1).
     

Step 1: Retrieve Service Manager data in Power BI.

1. Run Power BI Desktop on your workstation.

       Open url.png See:

2. Click Get data and select the Web data source.

          Power BI Desktop - Data source acquisition - Obtain data.png

3. Copy and paste the URL of the Service Manager report you stored in your text editor in prerequisite step. Next, click OK.

       Power BI Desktop - Data source acquisition - URL.png

4. In the Basic tab, specify the credentials for accessing the Service Manager REST API, i.e. user name and password, and click Connect.

          Power BI Desktop - Data source extraction - User information.png

  • The query associated with the REST API URL will be run.
  • Power Query Editor will open and data will be imported.
    • HREF refers to the URL run.
    • recordcount refers to the number of records returned by the query.
    • The records object is used to access the list of records.

       Power Query - Data source extraction - Connection information.png

5. Click List in the records object.
The list of Service Manager records will appear.
         Power Query - Data source extraction - Record list.png

Step 2: Create the Power BI report in Power Query Editor.

1. Select Convert > To Table to create a table using the list of records.
         Power Query - Data formatting - Convert to table.png

The table will be generated and will display one row for each record.
         Power Query - Data formatting - Create table.png

2. Click View fields icon.png to display the fields retrieved by the REST API and select the ones you want to display in the Power BI report.

Best Practice icon.png  Untick the Use original column name as prefix option if you only want to display labels in the report. If you tick this option, each field name will be preceded by the column name, Column1.

          Power Query - Data formatting - Select fields.png

The Power BI report will be generated using the selected columns.
         Power Query - Data formatting - View table.png

3. To find out how to format the data table, see the relevant Power BI documentation.

Step 3: Save the Power BI report.

1. Select File > Close & Apply in the menu.

  • Power Query Editor will close.
  • You will return to Power BI Desktop.
  • Data will be imported.Note: If you applied a filter to data, only the selected data will be imported.

2. Tick the fields to be displayed in the report in the order in which they should appear.

The report will appear.
         Power BI Desktop - View report.png

3. Select File > Save to save the report.
A PBIX file will be generated.

Links to other integrations

Tags:
Powered by XWiki © EasyVista 2022