Thursday, 20 April 2017

Unpivot Data In HANA Using a Graphical Calculation View

UNPIVOT data is a common requirement especially when we try to covert MS SQL query into SAP HANA data model. I ran into one such requirement, we had ETL data from MS SQL server to SAP HANA Enterprise and while converting one MS SQL model into HANA model I came across a Select query in MS SQL server where they were using UNPIVOT in Select statement.

I am using a simple example to explain how we can UNPIVOT data in HANA using Graphical Calculation View.

Here is the requirement:

Initial Data Set in HANA:

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Output: UNPIVOT Data Set:

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Pre-Requisite

Table in HANA, for this example I have created a student table in HANA:

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Process

Create a graphical calculation view. For this use case I have created a Dimension type Graphical Calculation view:

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Add 3 projection nodes and add Student table to each of these projections. In this example we need to UNPIVOT 3 columns (ENGLISH, PHYSICS, MATHS) hence I have used 3 projection nodes, however if you have 2 columns to UNPIVOT then you need to use 2 projection nodes, in short Projection nodes = No. of columns you want to UNPIVOT.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Add a Union Node and connect all three projections to Union Node.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Now in mapping section of UNION node do the following:
  • Click on Auto Map by Name

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

  • Remove all the Target fields except STUDENT_ID


SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Create two new Target columns Subject and Marks

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

In this step we will convert Column names ENGLISH, MATHS, and PHYSICS into column value.

Click on Manage Mapping of SUBJECT field and add constant values ‘English’, ‘Maths’, ‘Physics’ corresponding to three projection nodes. In MS SQL, UNPIVOT statement automatically converts Column header into Column values however in HANA we need to create constant values to achieve the same functionality.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Now we need to map marks/data of ENGLISH, MATHS and PHYSICS to newly created Marks target field.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Once UNION node mapping is done, connect UNION node to Projection node, add fields to output and activate the view:

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide

Here is the output of the View

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guide