Integrating SAP FSM with Microsoft PowerBI: Step-by-Step Guide

[awaes59]

Introduction

In today’s data-driven world, integrating different platforms to enhance business intelligence and data analysis capabilities is crucial.

This article provides a guide on integrating SAP Field Service Management (FSM) with PowerBI. Notably, there is no standard connector between these two platforms, necessitating a workaround for effective data utilization. Here, we'll explore how to overcome the authentication challenges using OAuth2 and subsequently fetch data into PowerBI.

1. Introduction to the Challenge

Integrating SAP FSM with PowerBI poses a significant challenge primarily due to the authentication process. SAP FSM requires OAuth2 authentication, which involves token generation not natively supported in PowerBI. This guide aims to address and provide a solution to this issue.

There are many connectors available through PowerBI, like SalesForce, IBM, S4, but no connection to FSM.

 

2. Token Retrieval

Step 1: Understanding OAuth2 Authentication

OAuth2 is an authorization framework that enables applications to obtain limited access to user accounts on an HTTP service. In the context of SAP FSM, it requires setting up an OAuth2 client in the SAP FSM system to generate credentials.

Please refer to the following SAP documentation on how to create these.

You are able to set-up the rights and company assigned to the Client ID you are creating.

Once the Client is created, save the clientSecret in a secured place. This will be the only time the key is shown, so make sure you store it.

Step 2: Scripting the Token Retrieval Function

Open a fresh blank PowerBI report. Go to Transform Data, and add a new "Blank Query".
Then navigate to the Advanced Editor 

Paste this code to create the token retrieval function.

()=>
let
 // Concatenates the ClientID & Client Secret and converts to base64
 authKey = "Basic " & Binary.ToText(Text.ToBinary("CLIENTID:CLIENTSECRET"),0),
 url = "https://eu.fsm.cloud.sap",
 // Uses the FSM POST OAuth2/token method to obtain a bearer token
 GetJson = Web.Contents(url,
     [RelativePath="/api/oauth2/v2/token",
         Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("grant_type=client_credentials") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),
 // Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " & AccessToken
in
    AccessTokenHeader

 Don't forget to replace the CLIENTID:CLIENTSECRET part with the values previously created.

Now, when you run this function, it should retrieve a bearer token.
If you encounter this warning : edit credentials, and select "Anonymous"

You should be able now to retrieve the token, and the hardest part is complete. 

Rename this function "GET Token"

3. Fetching Data from SAP FSM

Creating the Data Fetch Function

Like before, create a new blank query, and paste this code

(query as text, DTOs as text, companyName as text, tenantName as text) =>
let
Source = Json.Document(Web.Contents("https://eu.fsm.cloud.sap", 
[RelativePath="/api/query/v1?dtos=" & DTOs  & "&query="
& query & "&account=" & tenantName  & "&company=" & companyName & "&useExternalIds=true",
    Headers=[Authorization="" & #"GET Token"(), #"Content-Type"="application/json",#"x-client-ID"="cpi", #"x-client-version"="1.0"]])),
    data = Source[data],
    data1 = data
in
    data1

This function is calling SAP FSM Query API (documentation here) using the previouslys created function GET Token.
4 parameters are waited : 

• query: this is the query you want to make on FSM
• DTOs: this is the list of DTOs for the query
• companyName: this is the company you want to query on FSM
• companyTenant: this is the tenant of your FSM account

When saving, this should now look like this:

As you can see, this function can now be called as you wish. This will be the next step: invoke this query and create our first query. You can rename this "FSM Query".

4. Utilizing the Function

Here’s how you can use the above function to fetch and load data into PowerBI :

Enter the 4 parameters, and click on Invoke.

This will create a new query on the left side, with the result from FSM.

And by playing with PowerBI standard functionnality, you'll be able to expand, rename columns, add conditionnal logic; etc.

That's it! You now have all data in your PowerBI, and are able to query as much as you want the system.

Disclaimer

It's important to note that embedding credentials directly in your PowerBI files is not secure.
The client ID and client secret are visible in plain text, which poses a security risk.

SAP recommends using SAP Analytics Cloud for business intelligence to ensure better security and integration.