SAP Commissions - How to Connect Microsoft PowerBI

[yogananda]

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! 🚀

yoganandamuthaiah |Twitter | LinkedIn | GitHub

FollowLikeRSS Feed

[Ajit_K_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

[DChristenson]

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

[yogananda]

HI ajitkpanda

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

[Ajit_K_Panda]

okay Got it now.

Thanks again for the blog. 🙂

[yogananda]

Thanks, ajitkpanda for reviewing the blog!

 

[yogananda]

HI danchristenson

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

[KameshwarNukala]

Really a good Post

[yogananda]

Thanks, kameshwar.nukala !

[DChristenson]

Thanks for including the sample code - very helpful!

[former_member694755]

Hi yoganandamuthaiah ,

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

[yogananda]

Thanks deepu0407 !