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

[christian_willi]

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 diagram illustrates the solution:

1. SAP Analytics Cloud (SAC) initiates a Multi Action.
2. The Multi Action sends a REST call to the FastAPI CLI Wrapper hosted on SAP BTP.
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:

• Blogs: https://community.sap.com/t5/technology-blogs-by-sap/sap-datasphere-cli-command-line-interface-for-s...
• CLI Documentation: https://help.sap.com/docs/SAP_DATASPHERE/d0ecd6f297ac40249072a44df0549c1a/3f9a42ccde6b4b6aba121e2aab...

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 @ash_G/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. secrets.json: Contains the previously extracted information about the authentication
2. config.json: Contains the Datasphere host URL, which the application uses to interact with the Datasphere environment.
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. Login to Cloud Foundry:
   1. Open the terminal in BAS.
   2. Use the command: cf login -a “<API Endpoint>” and follow the instructions to log in. Please see below where to find your Cloud Foundry endpoin
      
      

1. Deploy the Application
   1. use cf push in the app's directory.
      

1. Check the application
   1. 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.

[Martin_Kuma]

Hi Christian! awesome step-by-step guidance; on a silver plate :). Thanks. Martin

Manual login / hurdle to full automation. You mean that every 30 days you have to update the secrets-file.json, rt please? The token will be invalidated, but else the OAuth works fine, or did you find issues there please? 

[CasCriel]

Dear Christian,

Very interesting blog, thanks for that.

However, I was thinking there is possibility that the CLI was also using an API endpoint. There is a DSP API endpoint available for triggering task chains from external applications. This would mean triggering it from a Multi Action API step in SAC (authenticate with OAuth2.0), should be possible?

You can browse the available endpoints with the URL below :

I tested this with Postman, it works there (3rd party could be SAC but I did not test this :)).

Kind regards!

Cas

[JulianJuraske]

Hello @christian_willi 

thanks for the nice blog with the detailed explanations.
BTW I'm also waiting on a official Solution provided by SAP for the problem of triggering DSP Taskchain outside of DSP. (e.g. from SAC, or in the BW at the End of a BW Process Chain via an ABAP Programm(REST API Call), or any other Source System finishing a batch Dataload )

Is the CLI on the Local(User) Machine not also a Limitation or did I miss something and the CLI is only neccessary for the setup? ( but how would the BTP App reach DSP than? ) 
In your Scenario does the DSP have to be in the same BTP as the created BAS-APP? 

Did you check this Blog, there is no CLI required to trigger a DSP Chain (other Usecase than your's):
https://community.sap.com/t5/technology-blogs-by-members/how-to-run-sap-dwc-task-chain-from-sap-btp-...
I think this is the same approach @CasCriel is talking about in his comment, that the CLI is not required because you can trigger it via the DSP API Endpoint.

Kind regards
Julian

[christian_willi]

Hi @Martin_Kuma ,

yes exactly, based on the setup of the OAuth Client and the refresh token you need to update the secrets manually. I did not find any other issues so far.

Regards

Christian

[christian_willi]

Hi @CasCriel ,

yes I think this could be possible, but would need to be tested. Do you need to fetch an CSRF Toke  like it is necessary in the SCIM API (https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/1ca8c4a9467f43df9ae6d4ed37...)? I think something like this could make it challenging.

Regards

Christian

[christian_willi]

Hi @JulianJuraske,

I agree, it would be great to have an official solution provided.

The CLI on the local machine is necessary for the initial setup and for retrieving the secrets.json when the refresh token expires. In my case, the are two separate BTP accounts.

Thank you for pointing out the blog. I believe this approach is quite similar to what @CasCriel  mentioned, and it seems like a potential solution as well!

Regards

Christian

[klorn_pallier]

Anyone else get this error?  My Redirect URI is correct:CLI ErrorRedirect URI

[christian_willi]

Hi @klorn_pallier ,

can you share which commands you are executing?

Regards

Christian

[klorn_pallier]

Hi @christian_willi

It's:

[christian_willi]

Hi @klorn_pallier ,

I cannot see the authorization-url. Is it not visible in the screenshot or missing?

Did you already try to perform the steps mentioned in the mentioned note: https://me.sap.com/notes/3461484

Regards

Christian

[klorn_pallier]

Yes sorry I accidently covered the auth url and I've tried the notes but the same message:

[christian_willi]

Hi @klorn_pallier ,

where are you running this CLI command? Does a window open to login to your Datasphere tenant?

Otherwise I found also these link very helpful for dealing with issues when working with the CLI:

https://community.sap.com/t5/technology-blogs-by-sap/faq-amp-troubleshooting-guide-for-sap-dataspher...

Regards

Christian

[DobrovolskayaTanya]

Hi @christian_willi 

thanks for this blog. I've faced the problem testing on Postman. I provide DS space ID and technical name of chain

According to log in BTP there is problem with login in DS 

I use the most recent secrets.json from CLI in BTP app. I can get secret information via /get_secrets API

Could you please advise where the problem is and how I can solve it.

Thanks in advance

[christian_willi]

Hi @DobrovolskayaTanya ,

I would check the following points:

• Did you update the secrets via the update_secrets endpoint?
• Can you run the login command or any other command in your local CLI?
• How did you setup the OAuth client?

Regards

Christian

[DobrovolskayaTanya]

thanks @christian_willi  for reply

• Did you update the secrets via the update_secrets endpoint?-> Initially I have update secrets.json  in BAS app and pushed it again. Additionally, new secrets is updated via API successfully.
• Can you run the login command or any other command in your local CLI?-> Yes. I can log in CLI using this Auth client credentials

• How did you setup the OAuth client

Unfortunatelly, the issue is still there. Do you have any ideas what to check as well?

[DobrovolskayaTanya]

@christian_willi additionally I have removed from manifest.yaml  snippet

 services:

    - python_dest_service

as during deployment there was an error " Service instance 'python_dest_service' not found.

without this code the deployment went successfully.

May it be the reason of problem with secret.json credentials?

If yes, please advise how  this python_dest_service can be set up  for application.

[christian_willi]

Hi @DobrovolskayaTanya 

the service destination can be created with the following command:
cf create-service destination lite python_dest_service

During a recent deployment I also had the issue with "datasphere not found". The issue was that the CLI command was not found or the path not set correctly. For a quick fix I added in the execution of the CLI commands the following path:

Hope this helps!

Regards

Christian

[Randeep]

Hi Christian! 

Thank you for this blog. Very well written.

Can you pls give some leads and share similar options if available to trigger the Task Chain of DS from PaPM Cloud (Profitability and Performance Management) application?

SAP Profitability and Performance Management , 

Regards,

Randeep