How to connect SAP Sales Cloud V2 to SAP Datasphere.
SAP Sales Cloud V2 provides Rest APIs only. You can find them here in the Business Accelerator Hub: https://api.sap.com/package/SAPSalesServiceCloudV2/rest
You can use these Rest APIs and generate output with your own environment within Business Accelerator Hub. You have to user Business User Credentials and the user name has to have at least six characters.
For the following example, I did that with the Opportunities. The output is a nested JSON. Why is that an issue?
You have one opportunity with 4 Business Partners (each take a different role). Another Opportunity only has 2. This issue goes on with involved parties and items and many more attributes. Back with Sales Cloud V1 (C4C/Cloud for Customers) you were able to extract the collections. Now you will have issues with extracting the information needed. Therefore, you have to recreate those collections, you used to have.
You will have several options to do that, for example with SAP Integration Suite. You can select the JDBC scenario. I still recommend doing that, but if you don’t have the option to use SAP Integration Suite, you can launch a python script.
In my example I did run it locally, but if I would do it for a customer, I would just launch it on Cloud Foundry (within SAP BTP). I will provide a blog on how to do that very soon.
This code calls the Rest API, flattens it, extracts the collections needed for this case and writes the tables back into the HANA under SAP Datasphere. I will use those tables to build views upon them. The tables will update once the code is being executed again. Adjust the code to call for Leads, Accounts etc. This is a workaround until SAP provides the APIs, we used to have in Sales Cloud V1 or if the customer does not have SAP Integration Suite. There are still a lot of other options to work around.
import requests
from requests.auth import HTTPBasicAuth
import pandas as pd
from pandas import json_normalize
from hana_ml.dataframe import create_dataframe_from_pandas
from hana_ml.dataframe import ConnectionContext
def fetch_data_from_api(url, username, password):
try:
# Define headers for the request, including additional config headers
headers = {
'config_authType': 'Basic',
'config_packageName': 'SAPSalesServiceCloudV2',
'config_actualUrl': 'https://myxxxx.de1.test.crm.cloud.sap',
'config_urlPattern': 'https://{hostname}',
'config_apiName': 'SalesSvcCloudV2_xxxxxx',
'Accept': 'application/json'
}
# Send a GET request to the API endpoint with Basic Auth and headers
response = requests.get(url, headers=headers, auth=HTTPBasicAuth(username, password))
# Raise an HTTPError if the HTTP request returned an unsuccessful status code
response.raise_for_status()
# Parse the JSON response
data = response.json()
return data
except requests.exceptions.HTTPError as http_err:
print(f"HTTP error occurred: {http_err}")
except Exception as err:
print(f"Other error occurred: {err}")
def extract_main_opportunity_data(data):
# Extract the main opportunity data and convert it to a DataFrame
opportunities = data.get('value', [])
df_opportunities = json_normalize(opportunities)
return df_opportunities
def extract_involved_parties(data):
# Extract involved parties from each opportunity
opportunities = data.get('value', [])
involved_parties_list = []
for opportunity in opportunities:
involved_parties = opportunity.get('involvedParties', [])
for party in involved_parties:
party['opportunity_id'] = opportunity.get('id') # Link party to opportunity
involved_parties_list.append(party)
df_involved_parties = pd.DataFrame(involved_parties_list)
return df_involved_parties
def extract_items(data):
# Placeholder for extracting items if they exist in the JSON structure
# Similar to extracting involved parties
items_list = []
for opportunity in data.get('value', []):
items = opportunity.get('items', []) # Adjust the key as per actual data
for item in items:
item['opportunity_id'] = opportunity.get('id') # Link item to opportunity
items_list.append(item)
df_items = pd.DataFrame(items_list)
return df_items
def clean_dataframe(df):
# Convert all columns to strings to avoid type issues
df = df.astype(str)
# Replace problematic characters or values if necessary
df = df.replace({r'\r\n': ' ', r'\r': ' ', r'\n': ' '}, regex=True)
return df
def write_dataframe_to_hana(dataframe, table_name, conn_context):
# Clean the dataframe to ensure compatibility
dataframe = clean_dataframe(dataframe)
hana_df = create_dataframe_from_pandas(conn_context, dataframe, table_name, force=True)
return hana_df
# URL of the REST API endpoint
api_url = 'https://myXXXXXXXX.de1.test.crm.cloud.sap/sap/c4c/api/v1/opportunity-service/opportunities'
# Your credentials for Basic Auth
username = 'xxxxxxxx'
password = 'xxxxxxxx'
# Fetch data from the API
data = fetch_data_from_api(api_url, username, password)
# Extract and convert JSON data to DataFrames
if data is not None:
df_opportunities = extract_main_opportunity_data(data)
df_involved_parties = extract_involved_parties(data)
df_items = extract_items(data) # If items exist
# SAP HANA Cloud connection details
host = xxxxxxxxxxxx.hana.prod-eu10.hanacloud.ondemand.com'
port = '443'
user = 'XXXXXXX’
password = 'xxxxxxxxxxxxx'
# Establish connection
conn_context = ConnectionContext(address=host, port=port, user=user, password=password)
# Write dataframes to SAP HANA Cloud
write_dataframe_to_hana(df_opportunities, 'OPPORTUNITIES', conn_context)
write_dataframe_to_hana(df_involved_parties, 'INVOLVED_PARTIES', conn_context)
write_dataframe_to_hana(df_items, 'ITEMS', conn_context)
# Close the connection
conn_context.close()
else:
print("No data retrieved from the API.")
