This document is prepared based on HANA SPS6 revision 63.
jchesch beautifully explained how to do Transpose and its use cases in the document (How To: Dynamic Transposition in HANA) which involves an additional table. If our requirement is to do table transpose without creating any additional table then we can do this completely by modeling in HANA studio. There could be many ways for doing and this is just another way of doing it.
Once the table is available in HANA studio, modeling will be done based on HANA base table and output of the Information view will be the transposed data.
Based on comments, this document was modified on Jan 8, 2014 and 2 other approaches have been added. Special thanks to justin.molenaur2and krishna.tangudufor making this better.
Based on comment, this document was modified on May 2, 2016. Special thanks to pradeep.gupta for making this document more better than before.
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 :
- 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:
Now you are familiar with different approaches of doing table transpose
Thank You for your time.
- SAP Managed Tags:
78 Comments
