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


About this integration

IntegrationIntroduction

You can integrate third-party systems with Product name - ev itsm.png 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 contact the Logo - EasyVista.png Consulting & Professional Services team, the Logo - EasyVista.png Support team, or your service provider and integrator.
 

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.
  • Consequently, you should schedule the execution of Power BI reports when users do not need to access Service Manager, i.e. at night or during weekends. Open url.png Should you need more information, see How to configure Power BI report scheduled refresh
  • 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

       Open url.png See:

Prerequisites

  • You must have the URL of the Service Manager REST API as this is used to retrieve the list of records in JSON format.

Click to see the procedure.

ProcedureObtainURL

How to quickly obtain Service Manager reporting data in JSON format?

  • Open a screen in List mode.
  • Click Tools icon.png in the top banner.
  • 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={....}&filterguid={....}&viewguid={....}

Create a Power BI report using data retrieved by the Service Manager

 REST API

Step 1: Retrieve Service Manager data in Power BI.

1. Run Power BI Desktop on your workstation.

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 REST API. This is used to retrieve the list of records in JSON format. 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:

Shortcuts

Powered by XWiki © EasyVista 2022