Nested JSON to SAP HANA Tables with SAP Integration Suite

In this blog post, I will demonstrate how to send data to SAP HANA Cloud using the Integration Suite. Additionally, I will explain how to handle nested JSON data and distribute it across multiple tables utilizing parallel multicast and mapping functions.

Problem Statement:

We have exposed an API endpoint through which we push data in JSON format and in response we get the insert count in particular tables. The input data contains user details and role details in nested form. We are supposed to insert the user details in User Table whereas in the User-Role mapping table for each role associated with a user, we ensure the creation of a corresponding entry, linking the user's details with their roles. Our requirement is to process the JSON data via CPI and populate these two tables.

How to Capture Multi-valued Attributes in HANA Table?

SAP HANA is well known for its proficient Architecture along with hardware and software optimizations. With hardware optimization SAP HANA database allows the developer/user to specify whether a table is to be stored column-wise or row-wise.

As an extension to the existing features of column table in HANA, user can now define the columns in a way to make it store multi-values or Array of values.

This document helps in understanding of how to define and work with Multi-Valued Attributes/Columns.

To understand the same let us consider a simple example of storing personal details of an Employee in a single table.

Step 1 : Create a column table that helps to store Employee ID, Employee Firstname, Employee Lastname and Employee Phone details. Former 3 details in the table are considered to have Single Value for each Employee, where as Phone details can be a Multi-Valued column for each Employee. That means, each Employee can have more than one phone details. Thus to make sure the table structure suffice the need to store multiple phone details for each Employee in same column of the table, we must define 'Phone' column in the table as Multi-Valued or Array column as shown below:

CREATE COLUMN TABLE Employee (

      ID int PRIMARY KEY,

      Firstname VARCHAR(20),

      Lastname VARCHAR(20),

      Phone VARCHAR(15) ARRAY --WITHOUT DUPLICATES

)

'Without Duplicates' condition implies that storage of same phone number more than once in the array list is not allowed.