Intro

I am an Analytics & Insights Consultant at SAP with a passion for coding. In this case presentation I will show you how we built a Email Notification Solution within SAP Data Warehouse Cloud.

A special Thanks to @Hannes Keil who came up with this idea and helped implementing it.

Content

1. About this solution
2. Prerequisites
3. Setup SAP Data Warehouse Cloud Views
4. Notification Templates
5. Configuration: User Provided Service Instances
6. Deployment via SAP Business Application Studio
7. Scheduling
8. Testing & Messages

1. About this solution

As of beginning of Q1 2022 SAP Data Warehouse Cloud does not provide an email notification service for any kind of logs created by the SAP Data Warehouse Cloud system. For critical tasks businesses need immediate reaction and therefore the system must notify people in charge to react immediately.This solution is built to help SAP Data Warehouse Cloud customers to setup an email notification service for any kind of notification within their SAP Data Warehouse Cloud system.

2. Prerequisites

The solution is based on a node.js client which uses following dependencies:

• @sap/hana-client ^2.10.13
• express ^4.17.1
• node-json2html ^2.1.0
• nodemailer ^6.6.5
• nodemailer-smtp-transport ^2.7.4
• sap-cf-mailer 0.0.5

Please make sure if these can be used within your IT systems.

You also need a SAP Data Warehouse Cloud tenant and a SAP BTP tenant. Within the SAP BTP tenant you need to enable following services:

• Job Scheduling Service
• SAP Business Application Studio
• Authorization and Trust Management Service

3. Setup SAP Data Warehouse Cloud Views

You need to create the following tables & views:

A) Table Notification Tasks – SAP_TCT_NOTE_TASK_V_01 and/or Table Notification ANY – SAP_TCT_NOTE_ANY_V_01

These tables contain all the logs for which there should be a notification triggered. The tables should look like this:

• Notification_ID (String 100)
• Notification_Type (String 100)
• SPACE_ID (String 64)
• OBJECT_ID (String 256)
• EMAIL (String 256)
• LINK (String 1000)
• VAR_1 (String 5000)
• …
• VAR_5 (String 5000)

B) View Notification Union – SAP_TCT_NOTE_UNION_V_01

Union view of all Notification Tables. Structure:

• Notification_ID (String 100)
• Notification_Type (String 100)
• SPACE_ID (String 64)
• OBJECT_ID (String 256)
• STATUS (String 256)
• EMAIL (String 256)
• LINK (String 1000)
• VAR_1 (String 5000)
• …
• VAR_5 (String 5000)

C) Notification Status Table – SAP_TCT_NOTE_STATUS_O_01 (OpenSQL) and/or Notification Status Table – SAP_TCT_NOTE_STATUS_T_01

Notification status table. Structure:

• Notification_ID (String 100)
• Notification_Type (String 100)
• EMAILSENT (Boolean)
• LAST_CHANGE_AT (Timestamp)

D) View Notification Status Table Union – SAP_TCT_NOTE_STATUS_V_01

Union view of all Notification Tables. Structure:

• Notification_ID (String 100)
• Notification_Type (String 100)
• EMAILSENT (Boolean)
• LAST_CHANGE_AT (Timestamp)

E) Table Notification Type Template Table – SAP_TCT_NOTE_TEMPL_T_01

Table for templates for each notification type. Structure:

• Notification_Type (String 100)
• Template (String 5000)

F) Open Notifications View – SAP_TCT_NOTE_OPEN_V_01

Union view of all Notification Tables. Structure:

• Notification_ID (String 100)
• Notification_Type (String 100)
• SPACE_ID (String 64)
• OBJECT_ID (String 256)
• STATUS (String 256)
• EMAIL (String 256)
• LINK (String 1000)
• VAR_1 (String 5000)
• …
• VAR_5 (String 5000)
• EMAILSENT (Boolean)
• STATUS (String 60)
• LAST_CHANGE_AT (Timestamp)
• Template (String 5000)

4. Notification Templates

All Notification Templates are stored in the table SAP_TCT_NOTE_TEMPL_T_01. In this table you can store layouts for any kind of notification type such as templates for success messages, error messages, warning messages etc. Here is an example how a template can look like for a failed Task within SAP Data Warehouse Cloud:

{ "<>": "div", "style": "margin: 0; padding: 0; text-align: center;", "html": [ { "<>": "li", "style": "width: 80%; margin: 2% .35%; display: inline-flex; box-shadow: 0 2px 4px rgba(0,0,0, .2);", "html": [ { "<>": "ul", "style": "width: 100%; padding: 1% 2%; background: #fff; max-height: 220px; box-sizing: border-box;", "html": [ { "<>": "h3", "style": "text-align: center;", "html": "Failed Task" }, { "<>": "ul", "style": "list-style-type: disc; text-align: left;", "html": [ { "<>": "li", "html": "Where: ${VAR_4} ${OBJECT_ID} in ${SPACE_ID}" }, { "<>": "li", "html": "StartTime: ${VAR_2}" }, { "<>": "li", "html": "Activity: ${VAR_3}" }, { "<>": "li", "html": "Triggered: ${VAR_5}" }, { "<>": "li", "html": [ { "<>": "a", "href": "https://******.eu10.hcs.cloud.sap/dwaas-ui/index.html#/*******&/di/*****${LINK}", "html": "Integration Monitor" } ] } ] } ] } ] } ] }

Feel free to design the email templates as per your requirements.

5. Configuration: User Provided Service Instances

You need to create two User Provided Service Instances within your SAP BTP Space which will later be used by the notification solution to get e.g. credentials. Go to your target space where the App will also be deployed on. Go to the instances section and create a User Provided Service Instance.

First Instance “dwc-notification-secret”:

Picture1: User provided Service Instance 1

Second Instance “dwc-notification-mailconfig”:

Picture2: User provided Service Instance 2

After deploying the notification solution, we will bind these two service instances with the node.js app. That is why we need to come back to this section later again.

6. Deployment via SAP Business Application Studio

Open the SAP Business Application Studio Service within your Space. Go to or create a Dev Space (Basic tools and extensions + HTML5 Runner + MTA Tools are enough for the beginning) and clone the repository of the SAP Data Warehouse Cloud Notification Solution.

Picture3: SAP Business Application Studio Dev Space

Clone the notification solution repository.  After cloning the app, open the mta.yaml file in the parent directory and check if your instances created in the previous step were named exactly as described in this mta file.

Picture4: Notification solution repository structure

If everything is fine, build the mtar project & deploy the zipped mtar project to your target space.

Picture5: Deployment of mtar project

If successful, you will find a link to the running application in the output of the terminal.

Now we will open the service instance section again an see if the binding of the two User-Provided Service Instances which we created earlier to this running app was successful. When everything looks good, we now can test if the app works.

Open the link to the application and check if it is starting correctly. This is what you see, if everything works fine:

Picture6: Landing page of nodejs backend

After triggering /sendmail you see a success, or error message. All the types of outputs and their meaning you will find later in this document.

7. Scheduling

As the notification solution is running and we prepared the SAP Data Warehouse Cloud views and tables we can now schedule this running application.

Go to your subaccount on the SAP BTP and open the Service Marketplace. Search for the service “Authorization and Trust Management Service”. Create an instance and bind it to the newly deployed app.

Navigate to the Service Marketplace again. Search for the service “Job Scheduling Service” and create one. Give it some instance name + Parameter JSON: { “enable-xsuaa-support”: true } and go to the Instances and Subscription section after that where you will find the newly created service. Now click on this instance and bind this Job Scheduling instance to your app. Your instances section should look like this:

Picture7: Scheduling instance on SAP BTP

Now you can open the Job Scheduler dashboard by expanding the scheduler instance and clicking on the dots-menu on the top right. On the configurations page you can edit the execution timeout. The default is set to 3 hours.

8. Testing & Messages

You can test the app locally by cloning the notification solution on your local machine and running node app in the parent directory. This will serve the solution on your localhost:4004 and you can trigger the execution of the app from there. Please wait minimum 2 minutes after each execution, as any ongoing process could be aborted by a second trigger.

You will see different types of messages. In the following you find some explanation to each of them:

• ANY ERROR CODE => If you see any error code like 400, 5xx, etc. the app does not work properly. You may try to restart the app through the SAP BTP cockpit.
• Emails were sent (…) => This means everything worked fine and the app was successful in terms of sending notification emails to all the recipients.
• No Email Notification to send. => There may be no open notifications to send. If this is wrong, please start/trigger the app again.

In this technical show case you learned how to setup a notification app which consumes SAP Data Warehouse Cloud tables and views and executes notification actions based on incoming information. Furthermore you learned how to deploy this kind of solution and how to manage User Provided Service Instances on the SAP Business Technology Platform which can be used with any backend or frontend.

I am more than happy to receive any kind of feedback, suggestions, questions and ideas to improve this solution. Feel free to comment here on the comment section of this blog with your feedback and questions.