Skip to Content
Technical Articles
Author's profile photo Yogananda Muthaiah

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.

Thanks, for reading it till the end. 🙏


Hope you find that helpful! Let me know your thoughts on this in the comments section.
Don’t forget to share this article with your friends or colleagues.
Feel free to connect with me on any of the platforms below! 🚀

Yogananda Muthaiah |Twitter | LinkedIn | GitHub

Assigned tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda

      Hi Yoga,

      Thanks for writing the blog. Just a quick doubt. In python script, you are reading the data from commissions using api or reading data from a csv file stored in your github account?

       

      Best Regards, Ajit

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      HI Ajit Kumar Panda

      Python script is calling Commissions RestAPI for Payments. Github link is commented out which was used for my testing in local environment.

      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda

      okay Got it now.

      Thanks again for the blog. 🙂

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks, Ajit Kumar Panda for reviewing the blog!

       

      Author's profile photo Dan Christenson
      Dan Christenson

      Great subject! - can you add a part II that fills in some of the gaps on the Python scripts and libraries used to build that data model?  Really interesting approach to pull data into Power BI.

      Thanks, Dan

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      HI Dan Christenson

      I have added python libraries used in the example above.  thanks for reviewing!

      Author's profile photo Dan Christenson
      Dan Christenson

      Thanks for including the sample code - very helpful!

      Author's profile photo Kameshwar Nukala
      Kameshwar Nukala

      Really a good Post

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks, Kameshwar Nukala !