Integration with Power BI

Daniel White
2025-08-26T20:14:22Z

Overview

Learn how to set up Enterprise Service Management Platform integration with Power BI via ESMP API. Enterprise Service Management Platform serves as a source of data, while Power BI allows creating reports and dashboards based on the retrieved data. Furthermore, the Power BI reports can be integrated in ESMP.

ESMP Data Source Access

To use ESMP as a data source for Power BI reports, generate, copy and save an API token as described on Web Services tokens: Generate API Token page.

The generated API token is not stored in ESMP. It can be copied and saved only once right after its generation, so make sure you have saved your token in a secure way so that it can be reused in the next step.

Power BI reports

In this step, use the Advanced Editor in the Power BI to edit the PowerQuery and adjust the sample queries according to your environment and ESMP data source. You can use a Data Definition or a Data Query as a data source for the Power BI report.

Create a new data source in Power BI

Configure Power BI to fetch data from ESMP.

Adding data sources from ESMP is possible in the Power BI Desktop tool only.

In Power BI, click the Get Data menu and select Blank Query:

Edit the query provided below and adjust it according to the ESMP data source using Advanced Editor

Option 1: Execute Get Data API

This example retrieves and filters data from the Data Definition of the ESMP using Get list of Fragments API.

BaseUrl: your server URL;

APIToken: generated API token;

DataDefinition: a source of the data retrieved from ESMP, for instance, SPSActivityClassBase stores all data regarding Tickets, Tasks, Incidents, Change Requests, Service Requests, and Problems. See also Data Definition page;

Columns: columns of the specified Data Definition that are necessary for Power BI report. Add comma-separated values for the columns;

Where: additional data filtering step. A where condition for the data from the columns of the specified Data Definition, for instance UsedInTypeSPSActivityTypeGroupTicket is not null expression retrieves only Problems from the SPSActivityClassBaseData Definition;

The rest of the data can be used as-is, without changes. It includes:

Authorization endpoint

/api/data/fragments/ API endpoint that allows retrieving data from Data Definition. Use the pageNumber and page Size in a request when the expected respos exceeds the maximum limit (10000)

Power Query Example for Data Definition Data source:

Starting with version 12.1.1, the system introduces a limitation on the maximum number of records returned by Generic Data Services, such as /api/m42services/data/. For more details, refer to the Data Query Settings section.
If the amount of imported data exceeds the maximum limit (10,000 records by default), it is essential to implement paging to retrieve data efficiently.

let
    BaseUrl = "https://andromeda.dwp.imagoverum.com/m42Services",
    APIToken = "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1bmlxdWVfbmFtZSI6Im5lb24ucWEubWF0cml4NDIuZGVcXGluc3RhbGxhZG1pbiIsIm5hbWVpZCI6Im5lb24ucWEubWF0cml4NDIuZGVcXGluc3RhbGxhZG1pbiIsIlVzZXJGcmFnbWVudElkIjoiZjIxZDMwMzAtMGVjYy1lYTExLTZlYWQtMDA1MDU2OTA3ZWQ3IiwiaXNzIjoiaHR0cHM6Ly9zdHMubWF0cml4NDIuY29tIiwiYXVkIjoidXJuOm1hdHJpeDQyQXBpIiwiZXhwIjoyMTQ3NDgzNjA5LCJuYmYiOjE2MDYxNjM3Mjl9.6XmZZqn_0-ZxY1h_MnameIuW78VpJC0dPK0RZVOtZ_I",
    AuthEndpoint = "/api/ApiToken/GenerateAccessTokenFromApiToken/",
    APIAuthHeader = "Bearer " & APIToken,
    body="{}",
    AuthResponce = Json.Document(Text.FromBinary(Web.Contents(BaseUrl & AuthEndpoint, [Headers=[#"Content-Type"="application/json", Authorization=APIAuthHeader], Content = Text.ToBinary(body)]))),
    token = AuthResponce[RawToken],
    DataDefinition = "SPSActivityClassBase",
    Columns = Uri.EscapeDataString("ID,TicketNumber,Subject,CreatedDate,T(SPSCommonClassBase).State.DisplayString as Status,Impact.DisplayString as Impact,Urgency.DisplayString as Urgency, Category.Name as Category, RecipientRole.T(SPSSecurityClassRole).Name as Role"),
    Where = Uri.EscapeDataString("UsedInTypeSPSActivityTypeGroupTicket is not null"),
    AuthHeader = "Bearer " & token,

    GetPageData = (pageNumber as number) =>
    let

        Endpoint = "/api/data/fragments/" & DataDefinition & "?Sort=ID ASC&pageSize=10000&pageNumber=" & Text.From(pageNumber) & "&where=" & Where & "&columns=" & Columns,
        Source = Json.Document(Web.Contents(BaseUrl, [RelativePath=Endpoint, Headers=[Authorization=AuthHeader]]))
    in
        Source,

    GetAllData = List.Generate(
        () => [PageNumber = 0, Data = GetPageData(0)], // Initialization
        each List.NonNullCount([Data]) > 0, // Continue while there is data
        each [PageNumber = [PageNumber] + 1,
              Data = GetPageData([PageNumber] + 1)], // Fetch the next page
        each [Data] // Output the data for each iteration
    ),


    //CombinedData  = Table.FromList(GetAllData, Splitter.SplitByNothing(), {"Data"})
    CombinedData  = Table.FromList(GetAllData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        // Convert lists in Column1 to tables
    ConvertListsToTables = Table.TransformColumns(CombinedData, {
        "Column1",
        each Table.FromList(_, Splitter.SplitByNothing(), {"Record"})
    }),
    // Expand nested tables and their fields
    ExpandedData = Table.ExpandTableColumn(ConvertListsToTables, "Column1", {"Record"}),

    // Expand individual fields in the records
    FinalTable = Table.ExpandRecordColumn(ExpandedData, "Record", List.Distinct(List.Combine(List.Transform(Table.Column(ExpandedData, "Record"),each Record.FieldNames(_)))))
in
    FinalTable

As a result:

1. No syntax errors have been detected message allows you to save the query and proceed;

2. The data and columns retrieved by the edited Power Query may look as provided on the example;

3. The data from the columns can be used in the Power BI charts and reports:

Option 2: Execute Data Query based on SQL data source

This example retrieves data from the Data Query of the ESMP.

It is recommended to use Data Queries based on SQL data source when you want to do the calculation in the ESMP application and just retrieve results to Power BI report.

Starting with version 12.1.1, the execution time for SQL-based Data Queries is governed by the "SQL Data Query Timeout" setting.

If the default timeout is insufficient to retrieve the required data, you can address the issue by:

1. Increasing the timeout value (applicable to On-Premise systems).

2. Modifying the Power BI query to use generic data queries with paging (refer to Option 1).

Create a Data Query of SQL type and define statement that returns data in a prepared format, as shown in the example:

To use Data Query as the data source in Power BI you will need a Data Query ID. For this, go to Administration application → User Interface → Data Queries → click on the necessary Data Query to open Preview Page → click Export action.

The Data Query ID example from the exported XML file may look as follows:

In the Advanced Editor of the Power BI, modify the following query according to your data source:

BaseUrl: your server URL;

APIToken: generated API token;

DataQueryID: a source of the data retrieved from ESMP. See also Data Query page;

The rest of the data can be used as-is, without changes. It includes:

Authorization endpoint

/api/dataquery/ API endpoint that allows retrieving data from Data Query;

Power Query Example for Data Query based on SQL data source:

let
    BaseUrl = "https://andromeda.dwp.imagoverum.com/m42Services",
    APIToken = "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1bmlxdWVfbmFtZSI6Im5lb24ucWEubWF0cml4NDIuZGVcXGluc3RhbGxhZG1pbiIsIm5hbWVpZCI6Im5lb24ucWEubWF0cml4NDIuZGVcXGluc3RhbGxhZG1pbiIsIlVzZXJGcmFnbWVudElkIjoiZjIxZDMwMzAtMGVjYy1lYTExLTZlYWQtMDA1MDU2OTA3ZWQ3IiwiaXNzIjoiaHR0cHM6Ly9zdHMubWF0cml4NDIuY29tIiwiYXVkIjoidXJuOm1hdHJpeDQyQXBpIiwiZXhwIjoyMTQ3NDgzNjA5LCJuYmYiOjE2MDYxNjM3Mjl9.6XmZZqn_0-ZxY1h_MnameIuW78VpJC0dPK0RZVOtZ_I",
    DataQueryID = "97a12231-3b2e-eb11-72ad-005056907ed7",
    AuthEndpoint = "/api/ApiToken/GenerateAccessTokenFromApiToken/",
    APIAuthHeader = "Bearer " & APIToken,
    AuthResponce = Json.Document(Text.FromBinary(Web.Contents(BaseUrl & AuthEndpoint, [Headers=[#"Content-Type"="application/json", Authorization=APIAuthHeader], Content = Text.ToBinary(body)]))),
    token = AuthResponce[RawToken],
    AuthHeader = "Bearer " & token,
    Endpoint = "api/dataquery/" & DataQueryID,
    body="{}",
    Source = Json.Document(Web.Contents(BaseUrl, [RelativePath=Endpoint, Headers=[Authorization=AuthHeader]])),
    AsTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumns = Table.ExpandRecordColumn(AsTable, "Column1", List.Distinct(List.Combine(List.Transform(Table.Column(AsTable, "Column1"),each Record.FieldNames(_)))))
in
   ExpandedColumns

The query example and retrieved data in Power BI:

Build Power BI report with data from ESMP instance

After data sources have been added to Power BI, you can build a report in Power BI (web or desktop version) according to your taste by adding charts and configuring them.

You can find more information about designing Power BI reports here.

Troubleshooting

Let's imagine the case when you are working on adding a data source to Power BI that uses Get Data API.

When configuring Columns to be retrieved from ESMP you have made a mistake, by adding a column name that does not exist:

As a result, Power Query Editor will show a mistake in the following format:

As ESMP Get Data API will not show the exact mistake directly in Power Query Editor due to security reasons, it is required to check ESMP logs.

You can download ESMP logs by using the action Download Server Logs on the Administration home page:

API logs can be found in Matrix42.Services.logs saying:

"update4u.SPS.DataLayer.Query.ExpressionParserException: Class: SPSActivityClassBase does not contain attribute CreatedDatesx!"

Power BI Reports integration in ESMP

To integrate the Power BI report in ESMP:

1. In Power BI, when the report is done, click File → Publish;

2. Go to the Report page in Power BI, click Share → Embed report → Website or portal:

3. Use the suggested link to embed the report contend to ESMP.

Report display in ESMP

The following options are possible:

1. External link: create a Navigation Item that opens the Power BI report as an external link. For more details see Navigation Item configuration page.

2. Landing Page: create a Navigation Item that opens a Landing Page. In the Layout Designer, edit the Landing page with the following options:

Add an IFrame control;

Set the URL of the Power BI report;

Set Height and Width to 100%;

Set Flex option to Flex:

Additionally, adjust the Container parameters for responsive report display in ESMP.

Set Horizontal Alignment to Justify

Set Vertical Alignment to Stretch

Report as a Landing Page example in ESMP: