Modelling: Column to Row Transpose using Matrix in HANA

Background:

In almost every project, some form of data transformation is required. Most of these transformations are some combination of Aggregation, Projection, Union or Join steps and these transformation can be easily done in the HANA models.

Where possible, It may be a good idea to push the complex transformation to the data acquisition layer using ETL tools like data services.

In certain scenarios, we may need additional data transformation on top of transformed data (say. you want to transform the output of an Analytic or Calculation view), in this scenario ETL tool can't be used.

Problem Statement:

There are two types of transformation

• Row to Column Transpose
• Column to Row Transpose 

1. Row to Column Transpose: This can be easily done using Restricted Measure (available in Analytic View) and Calculated Measures (available in both Analytic View and Calculation View)

2. Column to Row Transpose:

Let's start with an example. The following is output of Analytic View (AN_DATA)

We'd like to transform the dataset (from column to row) as shown below

Solution Options:

We'll use matrix transpose to achieve this result.

1. Create Matrix table DATA_TRANSFORM as below. 3 columns for 3 measures in our source data and an extra column "dummy".

2. Insert the dataset as below to DATA_TRANSFORM table

3. Build the calculation view as below.

• Create a Projection (proj_DATA) on the Analytic View (AN_DATA). Create a calculated column "DUMMY" with value 1
• Create a Projection (proj_MATRIX) on the DATA_TRANSFORM table
• Join proj_DATA and proj_MATRIX on the DUMMY field. Create two calculated column AMT_TYPE and AMT as shown below
• Expose the DOCID, COUNTRY, AMT_TYPE and AMT in the Aggregation node.

Result:

Now execute the following Query for the desired output