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.

Step 2 : Let us now insert the details of Employee in to the above created column table :

INSERT INTO Employee (ID, Firstname, Lastname, Phone)

VALUES (1, 'Hans', 'Peters', ARRAY('111-546-2758', '435-756-9847', '662-758-9283'))

Above insert statement helps to store the single values in first three columns of the table and array of values into the last column of the table.

Step 3 : We can differently insert value to array list by selecting data from already existing column table using a nested select query.

INSERT INTO Employee (ID, Firstname, Lastname, Phone)

VALUES (2, 'Harry', 'Potter', ARRAY(select '1245-1223-223' from dummy))

Step 4 : We can now induce a temporary phone number by preforming concatenation operation between array column and an array value :

SELECT ID, ARRAY('123-456-4242') || Phone FROM Employee

Step 5 : Let us now go ahead and retrieve the selected phone details from existing array of Phone numbers.

SELECT ID, TRIM_ARRAY(Phone, 2) FROM Employee

TRIM_ARRAY function helps to remove the specified length of undesired values at the end of the array. Here it Trims last 2 Phone number details in the array list and thus provides first phone number from the Phone column of the table for all Employees.

Step 6 : When user wishes to access the specific phone number  of an Employee, we can get that member value in the array by specifying its ordinal position as shown in the below SQL:

SELECT ID, MEMBER_AT(Phone, 1) FROM Employee

This helps to get the first phone number details in the array list of each Employee, for employees without phone numbers null value will be returned.

Step 7: Conditional retrieval of value from the Array list can be done by using CASE statement as shown below:

SELECT     

      CASE WHEN CARDINALITY(Phone) >= 3 THEN MEMBER_AT(Phone, 1)  ELSE NULL    END

FROM Employee;

Based on the cardinality of array column either the member value is retrieved or  null value is returned.

Step 8 :  Let us rotate the phone number details in array list by 90 degree so that the values in the array are split to  row values of a column of a table by using UNNEST function as shown below :

SELECT DISTINCT Phones.Number FROM UNNEST(Employee.Phone) AS Phones (Number);

Above set of operations are supported for Numeric and Non-Numeric Array Columns in the table.

Here by,  we are done with the creation, storage and usage of Multi-Valued Attributes in HANA Table.

Hope the provided information is useful. Any suggestion and feedback for improvement will be much appreciated.

Source: scn.sap.com