Implementing Dynamic Transpose in a alternate way in HANA

I this bog I will discuss about the Implementing Dynamic Transpose in a alternate way in HANA which will take easy steps and consume less memory while execution.

As we know that the process are converting column data to row wise and row data to column wise called transpose.Normal way or classical way when we do transpose it is much complex and time and memory consuming in HANA.

The new way we have implementing will have easy steps and consume less memory while execution.

Implementing Dynamic Transpose in a alternate way in HANA :

There are two types of known transpose methods in HANA. They are as below:

• Data may need to be combined from multiple fact tables where one is “horizontally” structured and another is “vertically” structured. A common structure is required in order to combine them  in a shared data model.

• “Vertical” data structures are much easier to work with for BI data modeling and reporting. (Less fields are required in the data model, and slicing/dicing/sorting on dimension values make much more sense than awkward display of different measure columns)

Here we will we check what is the steps  traditional approach  of transpose and how we will implement of new approach which will more effective,easy and less memory an time consuming .

• Traditional/Classical approach of Horizontal Transpose:

• Create an “Identity Matrix” table with any required DIMENSION columns and, optionally, and required SORT_ORDER. The matrix table will be look like this

• Create constant value calculated columns in each table (in implementation these will be projection nodes in a Calculation View).

• Cross Join the two tables by joining on constant value columns.

• Create output measure (i.e. AMOUNT) as a calculated column.

• Query required columns (aggregate AMOUNT, group by DIMENSION, sort by SORT_ORDER)

• Alternate approach of horizontal Transpose :

From column to row switch can be done easily by creating a ‘case’ statement in Projection                  node  which is consuming the existing table. Let us see the below steps:

1. In the  implementation Suppose we have a base table like this:

2. In this   transpose implementation we will move the column to row wise. By this all the                     values of each “Account Code”(s) will appear in a single column row by row and hence                       transposing the data from column to row.

3. We have implemented a calculated column (AMOUNT) in a projection by using the ‘case’              statement on the top node.

The case statement is given below –

4. Now, when we are pulling the “AMOUNT” column along with the “ACCOUNT_GRP_CODE”            all the values from different columns are coming into different rows in a single column.

This way of implementation is easy, less time consuming and less memory consuming while                executing.