Integration with Power BI

Last updated
Save as PDF

Overview

Learn how to set up Digital Workspace Platform integration with Power BI via DWP API. Digital Workspace 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 DWP.

DWP Data Source Access

To use DWP 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 DWP. 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 DWP 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 DWP.

Adding data sources from DWP 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 DWP data source using Advanced Editor

Option 1: Execute Get Data API

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

BaseUrl: your server URL;
APIToken: generated API token;
DataDefinition: a source of the data retrieved from DWP, 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;

Power Query Example for Data Definition Data source:

let
    BaseUrl = "https://andromeda.dwp.imagoverum.com/m42Services",
    APIToken = "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1bmlxdWVfbmFtZSI6Im5lb24ucWEubWF0cml4NDIuZGVcXGluc3RhbGxhZG1pbiIsIm5hbWVpZCI6Im5lb24ucWEubWF0cml4NDIuZGVcXGluc3RhbGxhZG1pbiIsIlVzZXJGcmFnbWVudElkIjoiZjIxZDMwMzAtMGVjYy1lYTExLTZlYWQtMDA1MDU2OTA3ZWQ3IiwiaXNzIjoiaHR0cHM6Ly9zdHMubWF0cml4NDIuY29tIiwiYXVkIjoidXJuOm1hdHJpeDQyQXBpIiwiZXhwIjoyMTQ3NDgzNjA5LCJuYmYiOjE2MDYxNjM3Mjl9.6XmZZqn_0-ZxY1h_MnameIuW78VpJC0dPK0RZVOtZ_I",
    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"),
    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/data/fragments/" & DataDefinition & "?where=" & Where & "&columns=" & Columns,
    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

As a result:

No syntax errors have been detected message allows you to save the query and proceed;
The data and columns retrieved by the edited Power Query may look as provided on the example;
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 DWP.

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

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 DWP. 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 DWP 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 DWP 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 DWP Get Data API will not show the exact mistake directly in Power Query Editor due to security reasons, it is required to check DWP logs.

You can download DWP 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 DWP

To integrate the Power BI report in DWP:

In Power BI, when the report is done, click File → Publish;
Go to the Report page in Power BI, click Share → Embed report → Website or portal:
Use the suggested link to embed the report contend to DWP.

Report display in DWP

The following options are possible:

External link: create a Navigation Item that opens the Power BI report as an external link. For more details see Navigation Item configuration page.
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 DWP.
  - Set Horizontal Alignment to Justify
  - Set Vertical Alignment to Stretch

Report as a Landing Page example in DWP: