SAP Commissions – How to Connect Microsoft PowerBI

Power BI Desktop helps to visualize complex data with the help of inbuilt and custom visualizations. It allows integrating data from the various data sources and preparing visualization…

After the integration of the Python programming language in the Power BI, The capabilities of the Power BI desktop have increased immensely. We can do ETL Processing, create Machine learning models …

Why Use Python in Power BI?

Using Python in Power BI you can automate and optimize the tiring process, create amazing customized visualization, can create machine learning modules, and create the visualization based on the predicted result. the only way to consume data from SAP Commissions is using APIs since DB access is restricted to accessing the data models in the Production system. so you have an option to write our own logic in Python scripting.

Prerequisites

• Visual Studio Code or any other IDE’s ( To prepare python script in local system and test)
• Power BI Account
• SAP Commissions Tenant
• Python libraries

Overview

A high-level diagram showing how this works is shown below.

Get Data ->Python script’

Here is the script I am writing:

python libraries used.

pip install requests
pip install json
pip installbase64
pip install pandas

import requests
import json
import base64
import pandas as pd
from pandas.io.json import json_normalize


###################################################################
apidomain = "https://<tenantid>.callidusondemand.com/api/v2/"
resulttbl = "incentives?"
filter1 = "$filter=period/name eq 'January 2021'"
#filter2 = "and payee/payeeId eq '32341016'"
expand = "&expand=payee,position,period"
select = "&select=name,value,payee,position,period,businessUnits"
skip = "&skip=0&top=100"


###################################################################


headers = {
    'authorization': "Basic eW11dXXXXXXXXXXXXXXXXXXXXAMQ==",
    'cache-control': "no-cache",
    'Accept':"application/json"
    }
###################################################################
response = requests.request("GET", apidomain+resulttbl+filter1+expand+select+skip, headers=headers)
response.encoding = 'utf-8' 
abc =json.loads(response.text)
json_str = json.dumps(abc["incentives"])


df = pd.read_json(json_str)

df = df[['name','value','payee','position','period','businessUnits']]
#print(df)


### Nested value parsing###########

df1 = df['value']
df1 = pd.json_normalize(df1)
df1 = df1.rename(columns={"unitType.name": "zCurrency"})
df1 = df1[['value','zCurrency']]

df2 = df['payee']
df2 = pd.json_normalize(df2)
df2 = df2.rename(columns={"displayName": "Payee"})
df2 = df2['Payee']


df3 = df['position']
df3 = pd.json_normalize(df3)
df3 = df3.rename(columns={"displayName": "Position"})
df3 = df3['Position']

df4 = df['period']
df4 = pd.json_normalize(df4)
df4 = df4.rename(columns={"displayName": "Period"})
df4 = df4['Period']

df5 = df['businessUnits']
df5 = pd.DataFrame([y for x in df5.values.tolist() for y in x])
df5 = df5.rename(columns={"name": "BU"})
df5 = df5['BU']

##### Final Results ########
incentive = pd.concat([df2, df3, df4, df['name'], df1,df5], axis=1)
#print(abc)

Analyze and construct your own data model.. since JSON Parsing needs to be beautified.

Once the data model is ready, you can see the data in the right columns.

Now we have loaded the data into the Power BI desktop, let’s create some custom visualizations using Python scripting.

This is just an example to demonstrate and will not go in-depth in explaining how to create a dashboard or report.

Conclusion

Now that you know how to use your Python integration in Power BI using SAP Commission API’s, the possibilities to do things endless. from creating amazing dashboards to creating sales models.

I hope this article will help you and save a good amount of time.