Leverage SAP Analytics Cloud File Server and Python to Automate Data Integration for External API

[Wu-Dongxue]

SAP Analytics Cloud (SAC) hasn't provided REST API connectivity. This blog can be treated as a Proof-of-Concept. It addresses the gay of importing data from external REST API and scheduling data updates based on REST API data source. The main configuration flow is to: convert the payload into a CSV file by Python > schedule the CSV file-generating process by Python and Task Scheduler > store the CSV file in the file server by Python > schedule import CSV file into SAC from the file server via SAC model data management.

Alternatively, SAP BTP Cloud Platform Integration also provides the capability to convert REST API into OData API.  It requires knowledge of customizing the entity in the edmx file.

In this Blog, I will take api/v1/audit/activities/exportActivities APIs for SAP Analytics Cloud as an example.

Step 1. Store data as CSV files from an API at regular intervals

import requests
import requests.auth
from datetime import datetime
import time
import sys
import csv
print(sys.executable)

def fetch_data():
    client = 'sb-<mockup>-65' #replace with the real client_id
    secret = '49-<mockup>p='   #replace with the real client_secret
    token_url = 'https://<mockup>/oauth/token' #replace with the real token_URL
    tenant_url = 'https://<mockup>'  #replace with real tenant_URL

    sess = requests.Session()
    basic_auth = requests.auth.HTTPBasicAuth(client, secret)

    resp = sess.get(token_url, params={'grant_type': 'client_credentials'}, auth=basic_auth)
    print('Step 1: Status', resp.status_code) 

    bearer_token = resp.json().get('access_token')

    resp = sess.get(
        f'{tenant_url}/ResourceRequestService/v1/ResourceRequests',
        headers={
            'Authorization': f'Bearer {bearer_token}',
            'x-sap-sac-custom-auth': 'true'
            'x-csrf-token': 'fetch', #add/remove headers as per application required
        }
    )

    print('Step 2: Status', resp.status_code)  

    if resp.status_code == 200:
        textList = [resp.text]
        textListFormatted = [i.replace('"', '') for i in textList]
        filename = 'sapTestFile.csv'
        with open(filename, 'w', encoding='UTF8') as f: #adapt to the filePath and fileName as expected, and data in the file gets updated every n seconds
            sapwriter = csv.writer(f)
            sapwriter.writerow(textListFormatted)
        print('Step 3: Data fetched and saved successfully')
    else:
        print(f'Step 3: Error {resp.status_code} - Unable to fetch data')

def main():
    while True:
        fetch_data()
        time.sleep(60)  # Sleep for 60 seconds, address the time as needed

if __name__ == "__main__":
    main()

Step 2. Schedule Python script on Windows Task Scheduler

After generating CSV file successfully based on the script in Step 1, then please refer to Execute Python Script on Schedule – Windows Task Scheduler to schedule the script in the background. Linus and Mac operation systems also have similar methods.

Step 3. Build import connection from file server to SAC

Refer to Allow Data Import and Model Export with a File Server and How to Configure File Server for importing data and exporting a model in SAP Analytics Cloud

The result can be expected:

The data in the existing file is overwritten every 60 seconds based on the last API call.

The file can be imported into SAC by scheduling via the file server.

-----------------------------------------------------###################--------------------------------------------------

Other user cases of the Python file: 

1. Archive the activity log from SAP Analytics Cloud automatically
2. Integrate 3rd party data sources that provide REST API endpoint