Table Transpose in SAP HANA Modeling

Approach 1:

• Analytic view will be built on each base table column which needs transposition.
• In this case 6 columns need transposition, hence 6 Analytic views will be created.
• Calculated Column (VALUE) is created in each Analytic view which derives the value of a particular month in a year.
• Create Calculation View based on Analytic Views created above and join them together using Union with Constant Value.
• No need to create Calculated Column (MONTH) in each Analytic view as this can be derived in Calculation View to improve performance.

Approach 2:

• 1 general Analytic view will be created instead of several Analytic views in which selected attributes and measures will be selected.
• In this case we select 6 measures M_JAN, M_FEB, M_MAR, M_APR, M_MAY, M_JUN in addition to common attributes.
• Create Calculation View based on general Analytic View created above and join them together using Union with Constant Value.
• Calculated Column (VALUE) is created in each Projection node which derives the value of a particular month in a year. 

Approach 3:

• No Analytic view will be created instead base table will be used directly.
• Create Calculation View based on direct base table in each projection node.
• Here also 6 projection nodes will be used.
• Calculated Column (VALUE) is created in each Projection node which derives the value of a particular month in a year. 

------------------------------------------------------------------------------------
Approach 4 (Recommended):
With single SQLScript calculation view, the table can be easily transposed.
This is the most easiest way and better as compared to other approaches.
------------------------------------------------------------------------------------
DDL used for workaround is given below:
------------------------------------------------------------------------------------
CREATE COLUMN TABLE TEST.ACTUALS (
     ID INTEGER NOT NULL,
     NAME VARCHAR (20) NOT NULL,
     YEAR VARCHAR (4),
     M_JAN INTEGER,
     M_FEB INTEGER,
     M_MAR INTEGER,
     M_APR INTEGER,
     M_MAY INTEGER,
     M_JUN INTEGER,
     PRIMARY KEY (ID));
INSERT INTO TEST.ACTUALS VALUES (1,'NAME1','2012',101,102,103,104,105,106);
INSERT INTO TEST.ACTUALS VALUES (2,'NAME2','2012',111,112,113,114,115,116);
INSERT INTO TEST.ACTUALS VALUES (3,'NAME3','2012',121,122,123,124,125,126);
INSERT INTO TEST.ACTUALS VALUES (4,'NAME4','2012',131,132,133,134,135,136);
INSERT INTO TEST.ACTUALS VALUES (5,'NAME5','2012',141,142,143,144,145,146);
INSERT INTO TEST.ACTUALS VALUES (6,'NAME6','2013',201,202,203,204,205,206);
INSERT INTO TEST.ACTUALS VALUES (7,'NAME7','2013',211,212,213,214,215,216);
INSERT INTO TEST.ACTUALS VALUES (8,'NAME8','2013',221,222,223,224,225,226);
INSERT INTO TEST.ACTUALS VALUES (9,'NAME9','2013',231,232,233,234,235,236);
INSERT INTO TEST.ACTUALS VALUES (10,'NAME10','2013',241,242,243,244,245,246);
------------------------------------------------------------------------------------
The data in the table is:

Transposed data:

Implementation steps for Approach 1:

• Analytic view will be built on each base table column which needs transposition.
• In this case 6 columns need transposition, hence 6 Analytic views will be created.
• Calculated Column (VALUE) is created in each Analytic view which derives the value of a particular month in a year.
• Create Calculation View based on Analytic Views created above and join them together using Union with Constant Value.
• No need to create Calculated Column (MONTH) in each Analytic view as this can be derived in Calculation View to improve performance. 

Now let us see this in action.

Let’s start with building Analytic view (AN_M_JAN) based on column M_JAN and in the Data foundation select the attributes ID, NAME, YEAR which will be common in all Analytic views and only month M_JAN and skip other columns as shown below.

In the Logical Join, create Calculated Column (VALUE) and hard-code the value with the name same as base table column name (“M_JAN”) and validate the syntax as shown below.

In the Semantics, hide the attribute M_JAN as it is not required in the output as shown below.

Now Validate and Activate the Analytic view and do data preview. You will see only the values corresponding to M_JAN only.

Create second analytic view AN_M_FEB based on column M_FEB and the process will be the same as created above for M_JAN. In the data foundation make sure that you select month M_FEB not M_JAN.

Date preview for AN_M_FEB corresponds to M_FEB only.

Similarly create other 4 Analytic views AN_M_MAR, AN_M_APR, AN_M_MAY, AN_M_JUN.

Create Calculation View (CA_ACTUALS_MONTH). From the scenario panel, drag and drop the "Projection" node and add the Analytic view in it. Do not select M_JAN column as the Calculated column VALUE used instead. Similarly add the Projection node for other Analytic vies. Totally 6 Projection nodes are required for each Analytic view.

Now add the "Union" node above the six "Projection" node and join them. In details section click "Auto Map by Name". The only attribute missing in the output is "Month".  In Target(s) under Details section, click on create target as MONTH with datatype as VARCHAR and size as 3 which contains 3 letter month names (eg. JAN, FEb, MAR, etc.)

Right click on MONTH and choose "Manage Mappings" and enter the value for constant for Source model accordingly.

The final Calculation view would be like:

Save and Validate, Activate, and Do the data preview:

which is our desired output of the view with data transposed 

But what about the performance?

Total number of records the information view contains:

To check if the filters are pushed down to the Analytic search, you need to find the “BWPopSearch” operation and check the details on the node in the visual plan. Please refer to awesome document by Ravindra Channe explaining "Projection Filter push down in Calculation View" which in turn points to the Great Lars Breddemann blog "Show me the timelines, baby!"

Let us apply filter for the year 2012.

SELECT NAME, YEAR, MONTH, VALUE FROM "_SYS_BIC"."MDM/CA_ACTUALS_VALUE" WHERE YEAR = '2012';

The Analytic search when expanded will show:

Though the table size is small in our case, Irrespective of table size, the filter is pushed down and fetching only the required records from the base table which helps in improving performance 

Implementation steps for Approach 2:

• 1 general Analytic view will be created instead of several Analytic views in which selected attributes and measures will be selected.
• In this case we select 6 measures M_JAN, M_FEB, M_MAR, M_APR, M_MAY, M_JUN in addition to common attributes.
• Create Calculation View based on general Analytic View created above and join them together using Union with Constant Value.
• Calculated Column (VALUE) is created in each Projection node which derives the value of a particular month in a year.

Let us see this in action.

Create general Analytic view with no calculated columns, simple and straight forward as shown below:

Create Calculation view. Drag and drop the Projection node and add general Analytic view, select the measure M_JAN only in addition to common attributes. Create Calculated column VALUE as shown below:

Now add 5 more projection nodes with same Analytic view adding to it. Create Calculated Column VALUE in each projection node corresponding to respective month M_FEB M_MAR, etc. 

Now add Union node above these projections and the rest of the process is already seen in  Approach1.

Implementation steps for Approach 3:

• No Analytic view will be created instead base table will be used directly.
• Create Calculation View based on direct base table in each projection node.
• Here also 6 projection nodes will be used.
• Calculated Column (VALUE) is created in each Projection node which derives the value of a particular month in a year.

------------------------------------------------------------------------------------

Implementation steps for Approach 4: (recommended)

Create the SQLScript as below:

BEGIN

  var_out = 

       SELECT ID, NAME, YEAR, 'JAN' as "MONTH", M_JAN as "VALUE" from TEST.ACTUALS

       UNION

       SELECT ID, NAME, YEAR, 'FEB' as "MONTH", M_FEB as "VALUE" from TEST.ACTUALS

       UNION

       SELECT ID, NAME, YEAR, 'MAR' as "MONTH", M_MAR as "VALUE" from TEST.ACTUALS

       UNION

       SELECT ID, NAME, YEAR, 'APR' as "MONTH", M_APR as "VALUE" from TEST.ACTUALS

       UNION

       SELECT ID, NAME, YEAR, 'MAY' as "MONTH", M_MAY as "VALUE" from TEST.ACTUALS

       UNION

       SELECT ID, NAME, YEAR, 'JUN' as "MONTH", M_JUN as "VALUE" from TEST.ACTUALS

  ;

END

Output:

Isn't it simple as compared to other approaches? yes it is.

Now you are familiar with different approaches of doing table transpose

Source: scn.sap.com