Automating SAP Datasphere Task Chains with CLI Wrapping: FastAPI Integration with SAP Analytics Cloud 

Introduction 

When working with SAP Datasphere, triggering task chains is often a crucial part of automating data processes. SAP Datasphere provides the ability to start task chains using CLI (Command Line Interface) commands, which offer a powerful way to control these operations. However, one challenge we faced was that SAP Analytics Cloud (SAC) doesn’t natively support calling CLI commands directly. 

To overcome this limitation, we developed a custom solution: a FastAPI wrapper for the CLI commands, hosted on SAP Business Technology Platform (BTP). This API allows us to trigger SAP Datasphere task chains from SAC using a simple REST call. By utilizing SAC’s Multi Actions, we can now automate task chain execution seamlessly without leaving the SAC environment. 

The following architecture diagram illustrates the solution: 

  1. 1. SAP Analytics Cloud (SAC) initiates a Multi Action.
  2. 2. The Multi Action sends a REST call to the FastAPI CLI Wrapper hosted on SAP BTP.
  3. 3. The FastAPI wrapper processes the request and triggers the Task Chain in SAP Datasphere.

This integration provides a seamless way to automate processes between SAC and SAP Datasphere, unlocking powerful automation capabilities within a familiar interface. In this blog, we’ll walk through the steps to implement this solution and highlight the benefits it brings to your data workflows. 

CLI Setup 

As a first step we created an OAuth client in SAP Datasphere. 

And we also installed the datasphere CLI on our local machine.  

Hint: For the CLI there exist already some great blogs and documentation: 

Based on the following SAP Note https://me.sap.com/notes/3461484 we executed the login: 

datasphere config cache init –host https://<tenantname>.hanacloudservices.cloud.sap/ –authorization-url https://<tenant name>.authentication.sap.hana.ondemand.com/oauth/authorize –token-url https://<tenant name>/oauth/token –client-id „<client id>“ –client-secret „<client secret>“ 

After that you will be prompted to log on. Now you can display the secrets with the following command: 
datasphere config secrets show –host „<your_host>” 

This step is crucial as you’ll need to save the secrets.json file for later use. Currently, the CLI requires user interaction for the initial login process. By completing this step and saving the secrets.json, you’ll be able to automate future logins. The refresh_token validity, which is configured when creating the OAuth client, extends the duration before user re-authentication is needed. This allows for longer, uninterrupted use of the CLI without manual login. In the future, we hope that the log on process will no longer require user interaction, further streamlining automation workflows. 

In this local CLI command you can now already test the different CLI commands. 

API development and deployment 

For the API development we created a BTP trial account (https://developers.sap.com/tutorials/hcp-create-trial-account..html). Here we navigate to our Business Application Studio and create a new dev space: 

Here we select a “Full-Stack Application Using Productivity Tools” and the “Python-Tools”: 

All of our code is available in the following Git repository: https://github.com/zpartner/cli_wrapper. This repository can give you an overview of how the application is structured and developed. For secrets management, we have provided template files that need to be renamed before use. Be sure to update the necessary authentication details in these templates to match your environment. 

In the next section, the main points are described. 

Key Points of the FastAPI Application 

Authentication Handling: 

  • The API uses authenticate_request(request) from auth.py to authenticate all requests across the endpoints, ensuring security. 
  • Authentication is done at the start of every API route to verify the legitimacy of the requests. 

Endpoints: 

  • POST /: Simple authentication check. Returns a success message if authentication is successful. 
  • /hello: Supports both GET and POST requests. Responds with a „Hello, World“ message after authentication. 
  • POST /run_task_chain: Executes a task chain based on provided space and object parameters. Returns a log ID after successful execution. 
  • GET /get_logs: Retrieves logs for all runs of a specific task or task chain based on space and object. 
  • GET /get_log_details: Retrieves detailed logs for a specific log ID with optional information levels (status or details). 
  • GET /get_secrets/: Fetches the current secrets stored in secrets.json. 
  • POST /update_secrets/: Allows updating or creating the secrets.json file by receiving a payload and writing it to disk. 
  • GET /get_host/: Retrieves the current Datasphere host URL from the configuration. 
  • POST /update_host/: Updates the Datasphere host URL. 

Error Handling: 

  • Uses HTTPException to handle and relay specific errors back to the client. 
  • A general exception handler returns a 500 error for any unexpected issues. 

Task Chain Execution: 

  • The core functionality revolves around executing and retrieving logs for task chains, with endpoints like run_task_chain, get_logs, and get_log_details. 

Configuration Management: 

  • The API allows both getting and setting the Datasphere host using /get_host/ and /update_host/. 
  • Secrets management is handled via endpoints to get and update secrets in secrets.json. 

Key Points for Deployment (manifest.yaml) 

Buildpacks: 

  • The application requires both the Python and Node.js buildpacks. The python_buildpack is needed for the FastAPI app, while the nodejs_buildpack is necessary for npm to install and run the Datasphere CLI. 

Command: 

  • The command ensures that the necessary Node.js modules are installed, specifically @sap/datasphere-cli. 
  • It also verifies the installation by printing the version of the Datasphere CLI. 
  • Finally, the FastAPI server is started using uvicorn with –reload for hot-reloading, running on the default host and port specified by the cloud environment ($PORT). 

Environment Variables: 

  • NODE_ENV: Explicitly sets the NODE_ENV to development to prevent warnings. 

Secrets management 

The following .json files contain important information for our application: 

  1. 1. secrets.json: Contains the previously extracted information about the authentication.
  2. 2. config.json: Contains the Datasphere host URL, which the application uses to interact with the Datasphere environment.
  3. 3. credentials.json: Stores user credentials (username and password hash) for basic authentication within the application.

Deployment 

To deploy an application in SAP Business Application Studio (BAS) via Cloud Foundry (CF) on SAP BTP, follow these steps: 

  1. 1. Login to Cloud Foundry:
    • a) Open the terminal in BAS (Business Application Studio).
    • b) Use the command: cf login -a „<API Endpoint>“ and follow the instructions to log in.

    Please see below where to find your Cloud Foundry endpoint  

    1. 2. Deploy the Application:
      • a) use cf push in the app’s directory
    1. 2. Check the Application:
      • a) use cf apps to check the status of the application

    After deployment you can also take a look at the generated documentation: 


     

    Testing with Postman 

    With Postman we can now make the first tests to interact with the API: 

    Integration with SAC 

    To interact now with API endpoints, we need to create a connection first in SAC. We go to connections and create a new HTTP connection. Also add the credentials for the Basic Authentication. 

    In the next step we create a Multi Action with an API step 

    In a story we can now integrate this Multi Action and execute it: 

    Back in the Datasphere we can see now the running instance, triggered from the SAC story. 

    Conclusion 

    In this project, we successfully integrated SAP Datasphere task chain automation with SAP Analytics Cloud (SAC) by leveraging a FastAPI wrapper for CLI commands hosted on the SAP Business Technology Platform (BTP). This integration significantly streamlined the process of automating task chains, allowing users to trigger SAP Datasphere operations seamlessly from within SAC. The use of SAC’s Multi Actions feature combined with the FastAPI wrapper has enabled a more efficient and automated workflow, improving overall productivity. 

    Limitations 

    Despite the successful implementation, there are several limitations to consider: 

    • Manual Logon Process: The initial logon process for the CLI still requires manual user interaction. Although subsequent logins can be automated using saved credentials (via the secrets.json file), this requirement introduces a hurdle to full automation. 
    • Limited CLI Command Handling: The current implementation relies on a fixed set of CLI commands. Expanding the API to support additional commands may require further development and testing, especially when SAP Datasphere or SAC introduces new features. 

    Possible Challenges 

    • API Maintenance: Regular updates to SAP Datasphere, SAC, or the FastAPI framework may require frequent maintenance of the custom API to ensure compatibility and stability. 
    • Error Handling: While the API includes basic error handling, more comprehensive logging and troubleshooting mechanisms might be necessary to deal with complex errors or unexpected behaviors in production environments. 
    • No GET Requests in SAC: SAC’s current limitation of only supporting POST requests in Multi Actions means that no GET requests can be used. This restricts the ability to retrieve data or react dynamically based on the return of the API. As a result, the workflow is less flexible, as it cannot adapt based on the response from the API, which can limit the automation potential. 
    • Inability to Work with POST Response Body in SAC: SAC is also currently unable to process or handle the body of a POST response. This further limits the interaction between SAC and the API, as you cannot use the data returned from the API to make decisions or trigger additional actions. This constraint makes it challenging to build more advanced workflows that rely on data returned from the API after a POST request. 

    Outlook 

    Looking forward, we hope SAP introduces a native API that can handle task chain automation without requiring custom development. This would eliminate the need for workarounds like FastAPI wrappers and enhance the native capabilities of SAC. With these improvements, SAP Datasphere and SAC integration could become even more seamless, offering more powerful automation without the need for custom solutions.