Hi all!
You as report developer or SAP Solution Architect probably faced with problem of choosing right tool to visualize business data with hierarchical aggregation of key figures. Fortunately there are several SAP tools and API which can do the task: ALV Tree for ABAP reports, Analysis for Office for BI Reports in MS Excel format, Web Intelligence for BI Reports in web format, HierarchyView.controller in SAP UI5 format and so on. The only thing you need is to feed either 2 columns with parent-child relationship between nodes or set of columns each corresponded to particular hierarchy level. Depending the tool you choosen.
So, generally it’s not a problem to obtain hierarchical aggregation in end-user reports.
But sometimes it’s not possible to use these tools due to their technology restrictions related to handle large amount of data (e.g. hundreds of thousands nodes). It’s quite rare case for reports with hierarchical aggregation but it could be happen. So if you are happy to use HANA2.0 database, I suppose built-in hierarchy functions to handle aggregation over hierarchy.
Lets consider simple table. DROP TABLE t_demo; CREATE COLUMN TABLE t_demo(node_id VARCHAR(2), parent_id varchar(2), type varchar(1), amount INTEGER ); insert into t_demo VALUES(‘RO’,null,null,0); insert into t_demo VALUES(‘A1’,’RO’,’a’,10); insert into t_demo VALUES(‘B1’,’A1’,’b’,10); insert into t_demo VALUES(‘C1’,’B1’,’a’,10); insert into t_demo VALUES(‘C2’,’B1’,’b’,10); insert into t_demo VALUES(‘B2’,’A1’,’c’,10); insert into t_demo VALUES(‘C3’,’B2’,’c’,10); insert into t_demo VALUES(‘D1’,’C3’,’b’,10); insert into t_demo VALUES(‘D2’,’C3’,’c’,10); insert into t_demo VALUES(‘C4’,’B2’,’a’,10); insert into t_demo VALUES(‘D3’,’C4’,’a’,10);
Following SAP HANA 2.0 SQL query… WITH h AS ( SELECT * FROM HIERARCHY ( SOURCE ( SELECT node_id, parent_id, amount FROM t_demo ORDER BY node_id ) ) ) SELECT hl, rpad(’ ’, hl,’.’) || group_node as gn, SUM(amount) AS sum_amount FROM HIERARCHY_DESCENDANTS( SOURCE h START ( SELECT ref_node, node_id AS group_node, hierarchy_rank AS start_rank, hierarchy_rank AS group_node_rank, hierarchy_level-1 AS hl FROM HIERARCHY_DESCENDANTS( SOURCE h START ( SELECT node_id AS ref_node, hierarchy_rank AS start_rank FROM h WHERE node_id IN ( ‘RO’ ) ) DISTANCE FROM 1 TO 4 ) ) ) GROUP BY ref_node, group_node, group_node_rank, hl ORDER BY group_node_rank;
…generates output
Column HL contains hierarchy level, column GN – node_id with corresponding indent. And the last column has aggregated value.
You can change the SQL clause DISTANCE FROM 1 TO 2 and got following output:
SUM_AMOUNT for .A1 contains sum posted to A1 and all its descendants.
So with great assist of SAP HANA 2.0 built-in hierarchy functions it’s possible to produce ready-to-export data set with aggregation over parent-child hierarchy.
Thank you for attention! http://bit.ly/2hu6Qxl #SAP #SAPCloud #AI
