Technical Articles
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…
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! 🚀
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
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.
okay Got it now.
Thanks again for the blog. 🙂
Thanks, Ajit Kumar Panda for reviewing the blog!
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
HI Dan Christenson
I have added python libraries used in the example above. thanks for reviewing!
Thanks for including the sample code - very helpful!
Really a good Post
Thanks, Kameshwar Nukala !
Hi Yogananda Muthaiah ,
Thanks for the blog. Its very informative.
In your High Level Diagram, you have shown the reps can access the reports from Commissions UI.
Could you please also let few ways to do so?
Regards
Deep
Thanks Deep Lal Sharma !