Building a GL Account Hierarchy for SAC Planning with CDS Views – Part 2 ( Building the Hierarchy )

Building a GL Account Hierarchy for SAC Planning with CDS Views – Part 2 ( Building the Hierarchy )
Estimated read time 27 min read

Table of Contents:

Overview on this Blog

Hi SAP Community,
This is part two of a three part blog series on How to Build a GL Account Hierarchy for SAC Planning with CDS Views.

Part1 – Loading the ID & DESC of the GLPart2 – Loading the hierarchyPart3 – Loading the Desc for the Hierarchy nodes 

In Part 1, we focused on loading the GL Account ID and Description into an SAC public dimension by selectively filtering GLs from the Financial Statement Version (FSV). This ensured that only the GL accounts relevant for our planning process were loaded, avoiding unnecessary noise in SAC input forms. If you haven’t read part1, I recommend you reading that first before this blog.

In this part, we will extend the same approach and focus on loading the GL hierarchy structure required for SAC Planning. This includes both leaf-level GL accounts and the header nodes that form the hierarchy.

 

Approach

Although SAP provides standard CDS hierarchy views(C_GLAccountHierarchyNode) , it is not feasible to consume a portion of the FSV with the available CDS View.

Instead of loading the entire GL hierarchy, we restrict ourselves to specific FSV branches relevant to the planning module (Sales, OPEX, CAPEX, etc.). The important assumption here is that the master nodes of the FSV do not change and the FSV is limited to a maximum of 8 Level of drill down , making them reliable anchors for hierarchy extraction.

The hierarchy is constructed by:

Identifying the leaf-level GL accountsTraversing upwards level by level until the root nodeCapturing all intermediate hierarchy nodes in the process

This traversal is implemented using multiple CDS views, Since CDS does not support recursion we would be iteratively joining and passing the parent of lower levels as the Children of next level until we reach the root. At the final step, all hierarchy levels are unioned into a single CDS view, which is then exposed via OData and consumed in the SAC public dimension. 

Don’t worry if it feels confusing at first, it will start to make lot more sense once we see the CDS architecture and Source code.

Technical Details and Source code

ZCDS_GLHIER_BACKBONE

This is the Base hierarchy view that combines HRRP_NODE with the standard CDS view (C_GLAccountHierarchyNode) to fetch the required FSV nodes and underlying hierarchy nodes. The reason we have joined HRRP_NODE is to obtain the node details. This CDS view will be propagated to the top level and be used as a foundation by higher CDS views.

@AbapCatalog.sqlViewName: ‘ZSQL_HIER’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘GL hierarchy Foundation’
@Analytics.dataExtraction.enabled: true
@OData.publish: true
define view ZCDS_GLHIER_BACKBONE as

select distinct from hrrp_node as a
inner join C_GLAccountHierarchyNode as b
on a.hryid = b.GLAccountHierarchy and a.nodevalue = b.GLAccount
{
key a.hryid,
key a.hrynode,
key a.parnode,
key a.nodevalue,
a.nodetype,
b.GLAccount,
b.HierarchyNode,
b.ParentNode,
b.NodeType as STD_nodetype
}
where (

(a.hryid=’FSV1′) and. — Replace with your relevant FSV for SAC Planning
(a.nodetype=’L’ or a.nodetype=’D’) and –Fetches line items containing Leaf or Directory nodes.

— Filters only the relevant Nodes needed for Planning.
— ParNode value may differ based on your FSV.
((a.parnode=’08’ or a.parnode=’09’or a.parnode=’017′ or a.parnode=’011′) //Sales (08->Turnover, 09->COS, 017-> Taxation, 011->Other income)
or (a.parnode=’013′ or a.parnode=’014′ or a.parnode=’016′) //OPEX (013->Selling & distribution cost, 014->General & Admin cost, 016-> Finance cost)
or (a.parnode=’012′) //Workforce (012->Indirect payroll cost)
or (a.parnode=’00ASSETS’ or a.parnode=’00LIABILITS’ or a.parnode=’028′) //CAPEX (00ASSETS->ASSETS, 00LIABILITS->LIABILITIES & EQUITY, 028->Depreciation)
or (a.parnode=’0117′)) //Inventory (0117-> Inventory)
)

ZCDS_GLHIER_GL_BASE:

This CDS View provides a clean GL-only child–parent mapping within the hierarchy, used as a reusable base for hierarchy traversal.

@AbapCatalog.sqlViewName: ‘ZSQL_GLBASE’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘For getting Plannable GLs’
define view ZCDS_GLHIER_GL_BASE as select distinct from C_GLAccountHierarchyNode as a

{
a.GLAccountHierarchy,
a.GLAccount,
a.HierarchyNode as Child,
a.ParentNode as Parent,
a.SACAccountType,
a.NodeType as STD_nodetype
}
where (

(a.GLAccountHierarchy=’FSV1′) –Replace with your FSV
and (a.NodeType=’N’ or a.NodeType=’R’)

)

ZCDS_GLHIER_LVL8_LEAF:

Using ZCDS_GLHIER_BACKBONE we will extract all the Leaf GLs and its immediate parent. Also while keeping track of the MasterParent.

@AbapCatalog.sqlViewName: ‘ZSQL_LVL8’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Leaf level 8 – Most granular’
define view ZCDS_GLHIER_LVL8_LEAF as select distinct from
ZCDS_GLHIER_BACKBONE as a {
a.parnode as MasterParent,
a.nodevalue as Child,
a.ParentNode as Parent
}

ZCDS_GLHIER_LVL7_PARENT:

@AbapCatalog.sqlViewName: ‘ZSQL_LVL7’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level7 – One step above leaf’
define view ZCDS_GLHIER_LVL7_PARENT as select distinct from

ZCDS_GLHIER_BACKBONE as a
{
a.hryid,
a.nodetype,
a.parnode as MasterParent,
a.ParentNode
}

The Code below this portion is simple and easy to follow along, The concept is simple.
Pass the parent of one level to the child of the higher level, that will give the parent child relationship all the way till the root level is reached.
 
ZCDS_GLHIER_LVL6_PARENT:

@AbapCatalog.sqlViewName: ‘ZSQL_LVL6’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level6 – Two step above leaf’
define view ZCDS_GLHIER_LVL6_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL7_PARENT as b on a.Child = b.ParentNode
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}

ZCDS_GLHIER_LVL5_PARENT:

@AbapCatalog.sqlViewName: ‘ZSQL_LVL5’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level5 – Three step above leaf’
define view ZCDS_GLHIER_LVL5_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL6_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}

ZCDS_GLHIER_LVL4_PARENT:

@AbapCatalog.sqlViewName: ‘ZSQL_LVL4’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level4 – Four step above leaf’
define view ZCDS_GLHIER_LVL4_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL5_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}

ZCDS_GLHIER_LVL3_PARENT:

@AbapCatalog.sqlViewName: ‘ZSQL_LVL3’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level3 – Five step above leaf’
define view ZCDS_GLHIER_LVL3_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL4_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}

ZCDS_GLHIER_LVL2_PARENT:

@AbapCatalog.sqlViewName: ‘ZSQL_LVL2’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level2 – Six step above leaf’
define view ZCDS_GLHIER_LVL2_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL3_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}

ZCDS_GLHIER_LVL1_PARENT:

@AbapCatalog.sqlViewName: ‘ZSQL_LVL1’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level1 – 7 step above leaf’
define view ZCDS_GLHIER_LVL1_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL2_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}

ZCDS_GLHIER_LVL0_ROOT:
After iterating the same procedure 8 times, we would have traversed 8 levels above the leaf which is also apparently the root node.
We have established 8 levels of iterations to achieve the maximum depth of 8levels of drill down, i.e, the maximum level of drill down possible in the S4/HANA FINANCE module for the creation of FSV.

@AbapCatalog.sqlViewName: ‘ZSQL_LVL0’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Root Level – 8 step above leaf’
define view ZCDS_GLHIER_LVL0_ROOT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL1_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}

ZCDS_GLHIER_FLAT_SAC:
At the final layer we would be unioning all the underlying levels ( Lvl0- root, lvl 1 to Lvl6 – Intermediate nodes, Lvl8 – Leaf).
The important thing here is to retain the MasterParent, which lets us identify which part of master branch the intermediate nodes and leaves are from.
 
If the source system has maintained the SACAccountType then well and good, if not you can maintain the SACAccountType like I did. (INC- income, EXP – expense, AST – Asset, LEQ – Liabilities, NFIN – NonFinancial). These will play crucial role in the aggregation, as GLs with AccountType EXP, LEQ will be subtracted rather than getting added. This will help SAC understand the signage and what to do next.

@AbapCatalog.sqlViewName: ‘ZSQL_TOP’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Union of all levels 1 to 8’
@Analytics.dataExtraction.enabled: true
@OData.publish: true
define view ZCDS_GLHIER_FLAT_SAC as select distinct from ZCDS_GLHIER_LVL8_LEAF as a
{
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Children’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL6_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL5_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}

union

select distinct from ZCDS_GLHIER_LVL4_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL3_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select from ZCDS_GLHIER_LVL2_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL1_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL0_ROOT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}

Leaf and header node Handling in SAC

The hierarchy loading is split into two logical parts:

Nodetype – Children (Leaf nodes): All Leaf GLs and it’s immediate parentNodetype – Node (Header nodes): All intermediate Nodes and their respective parents upto root.

We would be Loading the hierarchy in two steps in SAC as per the concept above. We will be using our ZCDS_GLHIER_FLAT_SAC cds view.

Query 2: Hierarchy for Leaf Level

Remember the MasterParent field we kept Intact? With the help of that we can choose whichever MasterNode we want to load into our public dimension. If you are loading the Hierarchy for the SALES GL Account dimension, just simply select the MasterNodes that point to Sales. In our Part1 blog, we loaded the GLs of nodes Turnover(08), CostofSales(09), OtherIncome(11) and Taxation(17), So we’ll do the same over here. 


In this query2, we will be loading only the leaf and its immediate hierarchy first. Hence we have selected Nodetype as ‘Children’. The below image will give you a clear picture on what is happening.

Since queries in public dimensions work on update method, the hierarchy & Account type property will get updated based on the above mapped information.

The sample result would be like this. 

ID

Description

Hierarchy

Account type

40010000

Revenue – Good (Export)

FSV1_019

INC

40010001

Revenue – Good (Service)

FSV1_019

INC

Query 3: Hierarchy for Header Level

Now that the Hierarchy for the leaf level is done, the remaining thing to concentrate now is to populate the nodes and its respective hierarchy details. The only difference being we just need to create another query with the same dimensions, but the important thing is to change the filter for Nodetype as ‘Node’.

This will fetch only the details from our intermediate levels. From Lvl6 to Root level.

In this query3, we will be loading only the nodes and its hierarchy, this continues all the way till the root. Hence we have selected Nodetype as ‘Node’. The below image will give you a complete picture on what is happening.

The sample result would be like this. 

ID

Description

Hierarchy

Account type

40010000

Revenue – Good (Export)

FSV1_019

INC

40010001

Revenue – Good (Service)

FSV1_019

INC

FSV1_011

 

FSV1_0FSV1

INC

FSV1_017

 

FSV1_0FSV1

INC

FSV1_018

 

FSV1_08

INC

FSV1_019

 

FSV1_08

INC

FSV1_020

 

FSV1_08

INC

FSV1_0FSV1

 

FSV1_

 

FSV1_

 

<root>

 

FSV1_08

 

FSV1_0FSV1

INC

FSV1_09

 

FSV1_0FSV1

INC

 

Conclusion

In this blog, we covered how to build a GL hierarchy for SAC Planning by selectively extracting FSV branches and traversing the hierarchy using multiple CDS views. This layered approach ensures a clean and future proof hierarchy in SAC. In the next part, we will focus on loading descriptions for hierarchy header nodes to complete the setup.

I hope this blog was helpful. This was my first time explaining something in detail. If you liked this level of explanation, do let me know. I’ll consider this feedback while writing my upcoming blogs. ✌️

 

​ Table of Contents:Overview on this BlogHi SAP Community,This is part two of a three part blog series on How to Build a GL Account Hierarchy for SAC Planning with CDS Views.Part1 – Loading the ID & DESC of the GLPart2 – Loading the hierarchyPart3 – Loading the Desc for the Hierarchy nodes In Part 1, we focused on loading the GL Account ID and Description into an SAC public dimension by selectively filtering GLs from the Financial Statement Version (FSV). This ensured that only the GL accounts relevant for our planning process were loaded, avoiding unnecessary noise in SAC input forms. If you haven’t read part1, I recommend you reading that first before this blog.In this part, we will extend the same approach and focus on loading the GL hierarchy structure required for SAC Planning. This includes both leaf-level GL accounts and the header nodes that form the hierarchy. ApproachAlthough SAP provides standard CDS hierarchy views(C_GLAccountHierarchyNode) , it is not feasible to consume a portion of the FSV with the available CDS View.Instead of loading the entire GL hierarchy, we restrict ourselves to specific FSV branches relevant to the planning module (Sales, OPEX, CAPEX, etc.). The important assumption here is that the master nodes of the FSV do not change and the FSV is limited to a maximum of 8 Level of drill down , making them reliable anchors for hierarchy extraction.The hierarchy is constructed by:Identifying the leaf-level GL accountsTraversing upwards level by level until the root nodeCapturing all intermediate hierarchy nodes in the processThis traversal is implemented using multiple CDS views, Since CDS does not support recursion we would be iteratively joining and passing the parent of lower levels as the Children of next level until we reach the root. At the final step, all hierarchy levels are unioned into a single CDS view, which is then exposed via OData and consumed in the SAC public dimension. Don’t worry if it feels confusing at first, it will start to make lot more sense once we see the CDS architecture and Source code.Technical Details and Source codeZCDS_GLHIER_BACKBONEThis is the Base hierarchy view that combines HRRP_NODE with the standard CDS view (C_GLAccountHierarchyNode) to fetch the required FSV nodes and underlying hierarchy nodes. The reason we have joined HRRP_NODE is to obtain the node details. This CDS view will be propagated to the top level and be used as a foundation by higher CDS views.@AbapCatalog.sqlViewName: ‘ZSQL_HIER’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘GL hierarchy Foundation’
@Analytics.dataExtraction.enabled: true
@OData.publish: true
define view ZCDS_GLHIER_BACKBONE as

select distinct from hrrp_node as a
inner join C_GLAccountHierarchyNode as b
on a.hryid = b.GLAccountHierarchy and a.nodevalue = b.GLAccount
{
key a.hryid,
key a.hrynode,
key a.parnode,
key a.nodevalue,
a.nodetype,
b.GLAccount,
b.HierarchyNode,
b.ParentNode,
b.NodeType as STD_nodetype
}
where (

(a.hryid=’FSV1′) and. — Replace with your relevant FSV for SAC Planning
(a.nodetype=’L’ or a.nodetype=’D’) and –Fetches line items containing Leaf or Directory nodes.

— Filters only the relevant Nodes needed for Planning.
— ParNode value may differ based on your FSV.
((a.parnode=’08’ or a.parnode=’09’or a.parnode=’017′ or a.parnode=’011′) //Sales (08->Turnover, 09->COS, 017-> Taxation, 011->Other income)
or (a.parnode=’013′ or a.parnode=’014′ or a.parnode=’016′) //OPEX (013->Selling & distribution cost, 014->General & Admin cost, 016-> Finance cost)
or (a.parnode=’012′) //Workforce (012->Indirect payroll cost)
or (a.parnode=’00ASSETS’ or a.parnode=’00LIABILITS’ or a.parnode=’028′) //CAPEX (00ASSETS->ASSETS, 00LIABILITS->LIABILITIES & EQUITY, 028->Depreciation)
or (a.parnode=’0117′)) //Inventory (0117-> Inventory)
)ZCDS_GLHIER_GL_BASE:This CDS View provides a clean GL-only child–parent mapping within the hierarchy, used as a reusable base for hierarchy traversal.@AbapCatalog.sqlViewName: ‘ZSQL_GLBASE’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘For getting Plannable GLs’
define view ZCDS_GLHIER_GL_BASE as select distinct from C_GLAccountHierarchyNode as a

{
a.GLAccountHierarchy,
a.GLAccount,
a.HierarchyNode as Child,
a.ParentNode as Parent,
a.SACAccountType,
a.NodeType as STD_nodetype
}
where (

(a.GLAccountHierarchy=’FSV1′) –Replace with your FSV
and (a.NodeType=’N’ or a.NodeType=’R’)

)ZCDS_GLHIER_LVL8_LEAF:Using ZCDS_GLHIER_BACKBONE we will extract all the Leaf GLs and its immediate parent. Also while keeping track of the MasterParent.@AbapCatalog.sqlViewName: ‘ZSQL_LVL8’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Leaf level 8 – Most granular’
define view ZCDS_GLHIER_LVL8_LEAF as select distinct from
ZCDS_GLHIER_BACKBONE as a {
a.parnode as MasterParent,
a.nodevalue as Child,
a.ParentNode as Parent
}ZCDS_GLHIER_LVL7_PARENT:@AbapCatalog.sqlViewName: ‘ZSQL_LVL7’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level7 – One step above leaf’
define view ZCDS_GLHIER_LVL7_PARENT as select distinct from

ZCDS_GLHIER_BACKBONE as a
{
a.hryid,
a.nodetype,
a.parnode as MasterParent,
a.ParentNode
}The Code below this portion is simple and easy to follow along, The concept is simple.Pass the parent of one level to the child of the higher level, that will give the parent child relationship all the way till the root level is reached. ZCDS_GLHIER_LVL6_PARENT:@AbapCatalog.sqlViewName: ‘ZSQL_LVL6’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level6 – Two step above leaf’
define view ZCDS_GLHIER_LVL6_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL7_PARENT as b on a.Child = b.ParentNode
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}ZCDS_GLHIER_LVL5_PARENT:@AbapCatalog.sqlViewName: ‘ZSQL_LVL5’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level5 – Three step above leaf’
define view ZCDS_GLHIER_LVL5_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL6_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}ZCDS_GLHIER_LVL4_PARENT:@AbapCatalog.sqlViewName: ‘ZSQL_LVL4’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level4 – Four step above leaf’
define view ZCDS_GLHIER_LVL4_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL5_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}ZCDS_GLHIER_LVL3_PARENT:@AbapCatalog.sqlViewName: ‘ZSQL_LVL3’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level3 – Five step above leaf’
define view ZCDS_GLHIER_LVL3_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL4_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}ZCDS_GLHIER_LVL2_PARENT:@AbapCatalog.sqlViewName: ‘ZSQL_LVL2’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level2 – Six step above leaf’
define view ZCDS_GLHIER_LVL2_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL3_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}ZCDS_GLHIER_LVL1_PARENT:@AbapCatalog.sqlViewName: ‘ZSQL_LVL1’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Parent Level1 – 7 step above leaf’
define view ZCDS_GLHIER_LVL1_PARENT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL2_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}ZCDS_GLHIER_LVL0_ROOT:After iterating the same procedure 8 times, we would have traversed 8 levels above the leaf which is also apparently the root node.We have established 8 levels of iterations to achieve the maximum depth of 8levels of drill down, i.e, the maximum level of drill down possible in the S4/HANA FINANCE module for the creation of FSV.@AbapCatalog.sqlViewName: ‘ZSQL_LVL0’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Root Level – 8 step above leaf’
define view ZCDS_GLHIER_LVL0_ROOT as select distinct from
ZCDS_GLHIER_GL_BASE as a inner join ZCDS_GLHIER_LVL1_PARENT as b on a.Child = b.Parent
{
a.GLAccountHierarchy,
b.MasterParent,
a.Child,
a.Parent
}ZCDS_GLHIER_FLAT_SAC:At the final layer we would be unioning all the underlying levels ( Lvl0- root, lvl 1 to Lvl6 – Intermediate nodes, Lvl8 – Leaf).The important thing here is to retain the MasterParent, which lets us identify which part of master branch the intermediate nodes and leaves are from. If the source system has maintained the SACAccountType then well and good, if not you can maintain the SACAccountType like I did. (INC- income, EXP – expense, AST – Asset, LEQ – Liabilities, NFIN – NonFinancial). These will play crucial role in the aggregation, as GLs with AccountType EXP, LEQ will be subtracted rather than getting added. This will help SAC understand the signage and what to do next.@AbapCatalog.sqlViewName: ‘ZSQL_TOP’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Union of all levels 1 to 8’
@Analytics.dataExtraction.enabled: true
@OData.publish: true
define view ZCDS_GLHIER_FLAT_SAC as select distinct from ZCDS_GLHIER_LVL8_LEAF as a
{
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Children’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL6_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL5_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}

union

select distinct from ZCDS_GLHIER_LVL4_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL3_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select from ZCDS_GLHIER_LVL2_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL1_PARENT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}
union

select distinct from ZCDS_GLHIER_LVL0_ROOT as a {
key a.MasterParent,
key a.Parent,
key a.Child,
//a.SACAccountType,
case when a.Parent=” then ” else(case when (a.MasterParent=’08’ or a.MasterParent=’011′) then ‘INC’ else (case when (a.MasterParent=’00ASSETS’ or a.MasterParent=’0117′) then ‘AST’ else (case when a.MasterParent=’00LIABILITS’ then ‘LEQ’ else ‘EXP’ end) end) end) end as SACAccountType,
‘Node’ as Nodetype
}Leaf and header node Handling in SACThe hierarchy loading is split into two logical parts:Nodetype – Children (Leaf nodes): All Leaf GLs and it’s immediate parentNodetype – Node (Header nodes): All intermediate Nodes and their respective parents upto root.We would be Loading the hierarchy in two steps in SAC as per the concept above. We will be using our ZCDS_GLHIER_FLAT_SAC cds view.Query 2: Hierarchy for Leaf LevelRemember the MasterParent field we kept Intact? With the help of that we can choose whichever MasterNode we want to load into our public dimension. If you are loading the Hierarchy for the SALES GL Account dimension, just simply select the MasterNodes that point to Sales. In our Part1 blog, we loaded the GLs of nodes Turnover(08), CostofSales(09), OtherIncome(11) and Taxation(17), So we’ll do the same over here. In this query2, we will be loading only the leaf and its immediate hierarchy first. Hence we have selected Nodetype as ‘Children’. The below image will give you a clear picture on what is happening.Since queries in public dimensions work on update method, the hierarchy & Account type property will get updated based on the above mapped information.The sample result would be like this. IDDescriptionHierarchyAccount type40010000Revenue – Good (Export)FSV1_019INC40010001Revenue – Good (Service)FSV1_019INCQuery 3: Hierarchy for Header LevelNow that the Hierarchy for the leaf level is done, the remaining thing to concentrate now is to populate the nodes and its respective hierarchy details. The only difference being we just need to create another query with the same dimensions, but the important thing is to change the filter for Nodetype as ‘Node’.This will fetch only the details from our intermediate levels. From Lvl6 to Root level.In this query3, we will be loading only the nodes and its hierarchy, this continues all the way till the root. Hence we have selected Nodetype as ‘Node’. The below image will give you a complete picture on what is happening.The sample result would be like this. IDDescriptionHierarchyAccount type40010000Revenue – Good (Export)FSV1_019INC40010001Revenue – Good (Service)FSV1_019INCFSV1_011 FSV1_0FSV1INCFSV1_017 FSV1_0FSV1INCFSV1_018 FSV1_08INCFSV1_019 FSV1_08INCFSV1_020 FSV1_08INCFSV1_0FSV1 FSV1_ FSV1_ <root> FSV1_08 FSV1_0FSV1INCFSV1_09 FSV1_0FSV1INC ConclusionIn this blog, we covered how to build a GL hierarchy for SAC Planning by selectively extracting FSV branches and traversing the hierarchy using multiple CDS views. This layered approach ensures a clean and future proof hierarchy in SAC. In the next part, we will focus on loading descriptions for hierarchy header nodes to complete the setup.I hope this blog was helpful. This was my first time explaining something in detail. If you liked this level of explanation, do let me know. I’ll consider this feedback while writing my upcoming blogs. ✌️   Read More Technology Blog Posts by Members articles 

#SAP

#SAPTechnologyblog

Avatar for

You May Also Like

More From Author