Sunday 28 August 2022

Load Data from A Local File into HANA Cloud, Data Lake

Overview

Ever wondered how will you load data/file from your local machine into HANA Cloud, Data Lake without hesitation?  

Then you are at the right place. This blog will provide you a step-by-step guide as to how anyone can easily load data from their local machine to the HANA Cloud, Data Lake using Data Lake File store. 

Step-by-step process:

Firstly, one must provision a Data Lake instance from the SAP HANA Cloud Central through SAP BTP Cockpit. One can learn to do so by going through the following tutorial – Provision a Standalone Data Lake in SAP HANA Cloud | Tutorials for SAP Developers  

Friday 26 August 2022

Integrating SAP PaPM Cloud & SAP DWC

Since integration topics are currently the talk of the town when it comes to SAP Profitability and Performance Management Cloud (SAP PaPM Cloud), it would just make sense to let the community know that one of the most popular data warehousing service that is: SAP Data Warehouse Cloud (SAP DWC) could be easily integrated to your SAP PaPM Cloud Tenant. If you’re curious on how to do it, read on. 

At the end of this blog post, my goal is for you to be able to: 

◉ Consume a HANA Table from SAP PaPM Cloud’s underlying HANA Database into SAP DWC 
◉ Consume a View created from SAP DWC into SAP PaPM Cloud’s Modeling via Connections 

Let’s start!

Wednesday 24 August 2022

E-Mail Templates in S/4 HANA- Display table in Email Template

SAP has a very interesting feature in S/4 HANA (cloud and on premise both) – E-Mail Templates.

In this blog post, we will learn how to embed table with multiple records in SE80 email template.

Example:

◉ Requirement is to send an email by end of the month to all employees who has pending hours in his/her time sheet. Data is taken from Std HR tables and pending hours is calculated using formula for each employee for project on which he is assigned. Pending hours = Planned hours – (Approved + Submitted) hours.

Monday 22 August 2022

CDS Views – selection on date plus or minus a number of days or months

Problem

Need to be able to select data in a CDS view where the records selected are less than 12 months old. This needs a comparison of a date field in the view with the system date less 12 months.

The WHERE clause should look something like the following.

Where row_date >= DATS_ADD_MONTHS ($session.system_date,-12,'UNCHANGED')

The problem with this is that the CDS view SQL statement above is not permitted, giving the following error message on activation.

Friday 19 August 2022

Monitoring Table Size in SAP HANA

This is a second blogpost about RybaFish Charts tool, If you never heard about the tool – please check the introduction article.

The real power of RybaFish Charts is in custom KPIs. RybaFish supports three KPI types: regular, gantt and multiline. Today we are going to create regular KPI to track the memory consumption by a certain column store (CS) table:

SAP HANA, SAP HANA Exam Prep, SAP HANA Exam Certification, SAP HANA Prep, SAP HANA Preparations, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials
Table Size Monitoring

Monday 8 August 2022

Flatten Parent-Child Hierarchy into Level Hierarchy using HANA (2.0 & above) Hierarchy Functions in SQL

Knock knock! Anyone else also looking for handy illustrations of the hierarchy functions introduced with HANA 2.0? Well count me in then.

While trying hard not to write SQLs with recursive self joins to flatten a hierarchical data format presented in parent-child relationship, the hierarchy functions in HANA can be a saviour for sure. Let’s look into something easy to implement using pre-defined hierarchy functions available with HANA 2.0 & above.

As a starter, let’s assume we have a miniature article hierarchy structured as below:

Saturday 6 August 2022

Integration of SAP Ariba Sourcing with Qualtrics XM for Suppliers, HANA Cloud Database

In this blog, I will give you an overview of a solution to extract supplier data from a Sourcing event in SAP Ariba Sourcing, and save it in a mailing list in SAP Qualtrics XM for Suppliers, using BTP services.

Process Part 1

First, to extract the information from SAP Ariba Sourcing, I use the Operational Reporting for Sourcing (Synchronous) API to get events by date range, and also the Event Management API which returns supplier bid and invitation information from the sourcing events.

Then, I store the information I need in a SAP HANA Cloud database. I created 3 tables to store the information that I will send to SAP Qualtrics XM for Suppliers: RFx header information, Invitations, and Organizations contact data.

Finally, I send all the information needed to a SAP Qualtrics XM for Suppliers mailing list, which will then handle automatically sending surveys to suppliers that participated in the Ariba sourcing events.

To send the information to SAP Qualtrics XM for Suppliers, I use the Create Mailing List API.

Integration

All this is orchestrated by the SAP Integration Suite, where I created 2 iFlows:

◉ The first iFlow is to get the information from the SAP Ariba APIs, and store it in the SAP HANA Cloud database.

SAP Ariba Sourcing, HANA Cloud Database, SAP HANA Exam, SAP HANA Career, SAP HANA Tutorial and Materials, SAP ABAP Skills, SAP ABAP Jobs
Get RFx Information from Ariba, and store it in SAP HANA Cloud

◉ The second iFlow is to get the information from the SAP HANA Cloud database, and send it to SAP Qualtrics XM for Suppliers via the Mailing List API.

SAP Ariba Sourcing, HANA Cloud Database, SAP HANA Exam, SAP HANA Career, SAP HANA Tutorial and Materials, SAP ABAP Skills, SAP ABAP Jobs
Get information from HANA Cloud and Send Contacts information to Qualtrics 

The SAP HANA Cloud database was created with a CAP application developed in Visual Studio Code.

Final Thoughts

By using SAP Ariba and Qualtrics APIs, as well as a couple of BTP services, integration between the two can be achieved in a very simple way with only a few steps.

Process Part 2


There are two methods to create SAP HANA Cloud artifacts:

HANA Database project

In this method we create database artifacts in a classic database schema using declarative SQL

For this exercise, I created the project in the SAP Business Application Studio as explained in the tutorial. I used these files to create the tables:

rfx.hdbtable

COLUMN TABLE rfx (
  id NVARCHAR(15) NOT NULL COMMENT 'Internal ID',
  title NVARCHAR(1024) COMMENT 'Title',
  created_at DATETIME COMMENT 'Created at',
  updated_at DATETIME COMMENT 'Updated at',
  event_type NVARCHAR(30) COMMENT 'Event Type',
  event_state NVARCHAR(30) COMMENT 'Event State',
  status NVARCHAR(30) COMMENT 'Event Status',
  PRIMARY KEY(id)
) COMMENT 'RFx Information'
 
rfx_invited_users.hdbtable

COLUMN TABLE rfx_invited_users (
  id NVARCHAR(15) NOT NULL COMMENT 'Internal ID',
  unique_name NVARCHAR(1024) NOT NULL COMMENT 'Contact Unique Name',
  full_name NVARCHAR(1024) COMMENT 'Full Name',
  first_name NVARCHAR(250) COMMENT 'First Name',
  last_name NVARCHAR(250) COMMENT 'Last Name',
  email NVARCHAR(250) COMMENT 'Email',
  phone NVARCHAR(30) COMMENT 'Phone',
  fax NVARCHAR(30) COMMENT 'Fax',
  awarded BOOLEAN COMMENT 'Awarded',
  PRIMARY KEY(id, unique_name)
) COMMENT 'Users invited to RFx'
 
rfx_organizations.hdbtable

COLUMN TABLE rfx_organizations (
  id NVARCHAR(15) NOT NULL COMMENT 'Internal ID',
  item INTEGER COMMENT 'Item No',
  org_id NVARCHAR(15) COMMENT 'Organization ID',
  name NVARCHAR(1024) COMMENT 'Name',
  address NVARCHAR(1024) COMMENT 'Address',
  city NVARCHAR(1024) COMMENT 'City',
  state NVARCHAR(1024) COMMENT 'State',
  postal_code NVARCHAR(10) COMMENT 'Postal Code',
  country NVARCHAR(100) COMMENT 'Country',
  contact_id NVARCHAR(1024) NOT NULL COMMENT 'Contact Unique Name',
  PRIMARY KEY(id, item)
) COMMENT 'RFx Organizations'

After creating all files, the project should look like this:

SAP Ariba Sourcing, HANA Cloud Database, SAP HANA Exam, SAP HANA Career, SAP HANA Tutorial and Materials, SAP ABAP Skills, SAP ABAP Jobs

After deployment, you should see all 3 tables in the SAP HANA Cloud database explorer:

SAP Ariba Sourcing, HANA Cloud Database, SAP HANA Exam, SAP HANA Career, SAP HANA Tutorial and Materials, SAP ABAP Skills, SAP ABAP Jobs

Now you can use JDBC (for example) to access the tables in the SAP HANA Cloud database.

Multi-target application project, CAP and CDS

In this method we create a multi-target application and use CAP and CDS to generate the SAP HANA database tables, as well as the OData services to access the database.

I used these files for the CDS artifacs:

schema.cds

namespace com.aribaxm.service;

type InternalId : String(15);
type SDate : DateTime;
type XLText : String(2050);
type LText : String(1024);
type SText : String(30);
type MText : String(250);

entity Rfx {
    key id           : InternalId;
        title        : LText;
        createdAt    : SDate;
        updatedAt    : SDate;
        eventType    : SText;
        eventState   : SText;
        status       : SText;
}

entity RfxInvitedUsers {
    key id         : InternalId;
    key uniqueName : LText;
    fullName       : LText;
    firstName      : MText;
    lastName       : MText;
    email          : MText;
    phone          : SText;
    fax            : SText;
    awarded        : Boolean;
}

entity RfxOrganizations {
    key id      : InternalId;
    key item    : Integer;
    orgId       : SText;
    name        : LText;
    address     : LText;
    city        : LText;
    state       : LText;
    postalCode  : SText;
    country     : MText;
    contactId   : LText;
}
 
service.cds

using com.aribaxm.service as aribaxm from '../db/schema';

service CatalogService @(path:'/api/v1') {
    entity Rfx as projection on aribaxm.Rfx;
    entity RfxInvitedUsers  as projection on aribaxm.RfxInvitedUsers;
    entity RfxOrganizations as projection on aribaxm.RfxOrganizations;
}

server.js

"use strict";

const cds = require("@sap/cds");
const cors = require("cors");
//const proxy = require("@sap/cds-odata-v2-adapter-proxy");

cds.on("bootstrap", app => app.use(cors()));

module.exports = cds.server;

The mta.yaml should look something like this:

---
_schema-version: '3.1'
ID: AribaXM
version: 1.0.0
parameters:
  enable-parallel-deployments: true
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm install --production
        - npx -p @sap/cds-dk cds build --production

modules:
  - name: aribaxm-srv
    type: nodejs
    path: gen/srv
    parameters:
      buildpack: nodejs_buildpack
    build-parameters:
      builder: npm-ci
    provides:
      - name: srv-api # required by consumers of CAP services (e.g. approuter)
        properties:
          srv-url: ${default-url}
    requires:
      - name: aribaxm-db

  - name: aribaxm-db-deployer
    type: hdb
    path: gen/db
    parameters:
      buildpack: nodejs_buildpack
    requires:
      - name: aribaxm-db

resources:
  - name: aribaxm-db
    type: com.sap.xs.hdi-container
    parameters:
      service: hana # or 'hanatrial' on trial landscapes
      service-plan: hdi-shared
    properties:
      hdi-service-name: ${service-name}
 
And the package.json should look something like this:

{
  "name": "aribaxm",
  "version": "1.0.0",
  "description": "A simple CAP project.",
  "repository": "<Add your repository here>",
  "license": "UNLICENSED",
  "private": true,
  "dependencies": {
    "@sap/cds": "^5",
    "express": "^4",
    "@sap/hana-client": "^2",
    "cors": "^2"
  },
  "devDependencies": {
    "sqlite3": "^5",
    "@sap/hdi-deploy": "^4"
  },
  "engines": {
    "node": "^16"
  },
  "scripts": {
    "start": "cds run"
  },
  "eslintConfig": {
    "extends": "eslint:recommended",
    "env": {
      "es2020": true,
      "node": true,
      "jest": true,
      "mocha": true
    },
    "globals": {
      "SELECT": true,
      "INSERT": true,
      "UPDATE": true,
      "DELETE": true,
      "CREATE": true,
      "DROP": true,
      "CDL": true,
      "CQL": true,
      "CXL": true,
      "cds": true
    },
    "rules": {
      "no-console": "off",
      "require-atomic-updates": "off"
    }
  },
  "cds": {
    "requires": {
        "db": {
            "kind": "hana"
        }
    },
    "hana": {
        "deploy-format": "hdbtable"
    }
  }
}
 
After creating all files, the project should look like this:

SAP Ariba Sourcing, HANA Cloud Database, SAP HANA Exam, SAP HANA Career, SAP HANA Tutorial and Materials, SAP ABAP Skills, SAP ABAP Jobs

After deployment, you should see all 3 tables in the SAP HANA Cloud Database Explorer:

SAP Ariba Sourcing, HANA Cloud Database, SAP HANA Exam, SAP HANA Career, SAP HANA Tutorial and Materials, SAP ABAP Skills, SAP ABAP Jobs

And these 2 applications in the BTP space:

SAP Ariba Sourcing, HANA Cloud Database, SAP HANA Exam, SAP HANA Career, SAP HANA Tutorial and Materials, SAP ABAP Skills, SAP ABAP Jobs

Now you can use OData to access the database. In this exercise I didn’t added access control, so you should use your BTP user to execute the OData services from Postman and check the access.

Final Thoughts

Now you have 2 methods of creating SAP HANA Cloud database artifacts, both from the SAP Business Application Studio or Visual Studio, so you can access the database from JDBC or OData services.

Friday 5 August 2022

Processing of Prepayments with SAP S/4HANA Accruals Management

For sure you have already heard about the SAP S/4HANA Accruals Management?

Starting with S4 HANA OP 2021, the Accrual Management provides a new functionality to process deferrals. So far, the processing of prepayments is a typical and manual activity for the Finance users. With this new functionality Finance can achieve efficiency gains and it’s quite easy to implement as well. And you can also use the deferrals’ part even if you didn’t implement the accruals’ part yet.

In this blog I will show you how to set up the SAP S/4HANA Accruals Management for deferrals in order to optimize this process.

Thursday 4 August 2022

App Extensibility for Create Sales Orders – Automatic Extraction: Custom Proposal for Sales Order Request Fields (Using BAdI)

In the Create Sales Orders – Automatic Extraction app, the system starts data extraction and data proposal for sales order requests immediately after your purchase order files are uploaded. If SAP pre-delivered proposal rules do not satisfy your business needs, key users can create custom logic to implement your own proposal rules.

Here is an example procedure. In your custom logic, you want the system to set the sales area to 0001/01/01 if the company code is 0001, and set the request delivery date to the current date plus seven days if this date is initial. In case ML can not extract the date from file or date is not exist in the file.

Wednesday 3 August 2022

How to Display Situations in Your Custom Apps with the Extended Framework of Situation Handling

Situation Handling in SAP S/4HANA and SAP S/4HANA Cloud detects exceptional circumstances and displays them to the right users.

I’m the lead UI developer within the Situation Handling framework for end-user facing apps and I’m excited to present a new feature in the Situation Handling extended framework to you. Together with the team from SAP Fiori elements, we introduce a new, simplified way of displaying situations right in your business apps. You can enable a great user experience for your end users with low effort. You should read through this tutorial-style article which explains the details and points you to further resources that you can take as a blueprint for your successful implementation.

With the extended framework of Situation Handling, situations can be displayed in apps based on SAP Fiori elements for OData version 4. You can do this without any front-end development, just by extending the app’s OData service.

Tuesday 2 August 2022

Currency conversion in BW/4HANA, Enterprise HANA Modelling

Most the business model cost center or profit center are located in different country and with different currency profit and cost is generated. At the end of the year finance team try to calculate total cost or profit against a target currency (USD,EUR or different one) which is the headquarter of the company located to generate laser and balance sheet. In that scenario we need to perform currency conversion to generate analytics report. This blog I am going to discuss about the currency conversion step for different scenarios i.e. BW/4HANA, Enterprise HANA Modelling  SAP Analytics Cloud

1. Currency conversion in BW/4HANA:

In our scenario, A finance analyst wants all the profits generated in the Belgium and France plant (in EURO) need to converted into the USD which is the currency of the organization head office to generate the Laser posting.

Monday 1 August 2022

Backup and Recovery for the SAP HANA (BTP)

SAP HANA (HANA Cloud, HAAS ..) offers comprehensive functionality to safeguard your database i.e. SAP HANA offers automatic Backup to back up your database and ensure that it can be recovered speedily and with maximum business continuity even in cases of emergency. The recovery point objective (RPO) is no more than 15 minutes.

A full backup of all SAP HANA Cloud instances is taken automatically once per day for last 14 days

These Backups are encrypted using the capabilities of the SAP Business Technology Platform. The retention time for backups is 14 days. This means that an instance can be recovered for 14 days.