SAP Commissions : How to Clean your messy data ?

yogananda · ‎09-12-2021

In this article, you will come to know what kind of data you can perform to clean/remove from your SAP Commissions tenant using the RestAPI DELETE method through bulk operations to perform the cleansing.

To solve the common scenarios of messy data

As you are aware of Transactional Data that comes to SAP Commissions through the source system will be an imported record and can be removed through Pipeline Job and can be reset through Validate method with a batch name or period name.

But what about Organization data or Generic Classification data and Manually created through UI/Excel loader are loaded for testing purpose and that needs to be cleaned ?? so such heavy data will be a painful task for performing data cleaning.

Long story short, after being in the data Architect field for quite some time, I do feel the pain of doing data cleaning before dealing with data analysis, visualization, and with incorrect results generated from bad/testing data.

Garbage in, Garbage out

Admit it or not, data cleaning is not an easy task and most of the time it is time-consuming and tedious, yet this process is too important to be neglected. It's time-consuming to pick every 250 transactions and deleting them... Just think about if you have 5million data to do repeatedly doing and how long it takes ??.

Below screenshot will explain how to pick every 250 records and select delete based on your filter.

You’ll understand what I mean if you’ve been through the process. And this is exactly the reason why I wrote this article to help you perform data cleaning in a smoother way.

Benefits: Avoid below similar messages in your tenant when you perform data cleaning regularly

Identify first what to clean/remove the data from your tenant

Follow this documentation which is the Standard Process of Reset the data

Pipeline Job for Sales Transaction Imported data can only be performed through the Reset from Validate option and Purge (Stage tables).

Note :

Manual credits, deposits, and adjustments are never reset.

Below are not part of the Standard Process which you would need to perform through Admin Operations.

⚙️ Manually created Sales Transactions with (OriginType = Manual ) and Sales Order

⚙️ Manual Credits, Deposits, and Adjustments

⚙️ All Classification Data ( Products, Generic Classifier, PostalCodes & Customers)

⚙️ All Organization Data (Participants & Positions )

Techniques on what to clean and how.

I am doing this for the last several years when many of them ask me to do it and I do it independently without the need for database and Technical Support executives to perform from the backend...

Note: taking backup again is consuming the data and Approval to execute is time-consuming...

My technique for Data Cleaning is with Python and pandas libraries using Commissions RestAPI - DELETE Method.

SAP Commissions API Documentation

SAP Commissions Filtering Conditions

First things First

Identify the data which you need to remove and take a count of records before you perform for deletion.

In my below example: I have filtered the data which is manually created and filtered next on event Type which has a total of 65 records to delete.

Through using RestAPI of SalesTransaction with same filtering matching exact count from the above step. (65 records)

At the end of this article, I hope you’ll find the codes useful and that would make your data cleaning process more faster and efficient.

Let’s get started!

I will go with one delete method manual to see if the record is deleted and continue to run a bulk operation method using Python code to delete all the records continuously (perform loop)

Below script is just an example for my above use case, so Modify the code to your need

import requests
import json
import pandas as pd


###### Set your Envionment Variables & Modify to filter your data ###########
env = "https://<tenantid>.callidusondemand.com/api/v2/"
endpoint = "salesTransactions"
filtered = '?expand=eventType&$filter=originTypeId eq "manual" and eventType/eventTypeId eq "LicensesNF" '

size = "&skip=0&top=3"  ## only 3 records will be shown and make a change it to your need
headers = {
'authorization': "Basic eW9XXXXXXXXXXXXXXXXXXXXXXXXXXAMQ==",
'cache-control': "no-cache"
}

######## Get All SalesTransactions Seq into group ###############

response = requests.request("GET", env+endpoint+filtered+size, headers=headers)
response.encoding = 'utf-8'
response.text
transaction = json.loads(response.text)
json_str = json.dumps(transaction["salesTransactions"])

####### Parse the json object and element to get SalesTransaction###############
df = pd.read_json(json_str)
df = df['salesTransactionSeq']


######## Loop each salestransactionSeq to DELETE the record ##################

i = 0

for i in range(len(df)):
    response = requests.request("DELETE", env+endpoint+"("+str(df[i])+")", headers=headers)
    response.encoding = 'utf-8'
    if (response.status_code == 200):
        print(str(df[i]) + "---->" + response.text)
    else:
        print(str(df[i]) + "---->" + response.text)
        break
        #i = i+1

###### Break point is added - remove # to continuous loop ###########

Housekeeping Notes Before running the script

Ensure you run 2 or 3 records by updating the Size of records (line 9 in my code) and review

Ensure there must be no results associated with the transaction you want to delete.

Ensure first to run Salestransaction and then SalesOrder.

Ensure first to run Classifier assignment and then Generic Classifier (backward compatibility)

Ensure first to run Position and then Participant.