Wednesday, 3 August 2016

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)

Modelling: Column to Row Transpose using Matrix in HANA

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

Modelling: Column to Row Transpose using Matrix in HANA

Solution Options:


We'll use matrix transpose to achieve this result.

Modelling: Column to Row Transpose using Matrix in HANA

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

Modelling: Column to Row Transpose using Matrix in HANA

2. Insert the dataset as below to DATA_TRANSFORM table

Modelling: Column to Row Transpose using Matrix in HANA

3. Build the calculation view as below.

Modelling: Column to Row Transpose using Matrix in HANA

  • 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.

Modelling: Column to Row Transpose using Matrix in HANA

Modelling: Column to Row Transpose using Matrix in HANA

Result:

Now execute the following Query for the desired output

Modelling: Column to Row Transpose using Matrix in HANA