How to query CDS Hierarchy (created with Define Hierarchy) to select all siblings in a CDS

[MattHarding]

Very simple requirement - I'd like to find all work orders against a functional location, or any of it's descendants which is to be exposed as a CDS view.

So here's the recursive relationship of a Functional Location:

@AbapCatalog.sqlViewName: 'YIFLOCHIERREL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Test'
define view YI_FLOCHierarhcyRelationship as select from I_FunctionalLocation 
  association [0..1] to YI_FLOCHierarhcyRelationship as _Parent
  on  $projection.SuperiorFunctionalLocation = _Parent.FunctionalLocation
{
    key FunctionalLocation,
    SuperiorFunctionalLocation,
    _Parent
}

Now I thought I might be able to use Define Hierarchy to give me the required information - something like this:

define hierarchy YI_FLOCHierarhcy 
  as parent child hierarchy (
    source YI_FLOCHierarhcyRelationship
    child to parent association _Parent    
    siblings order by FunctionalLocation ascending
    orphans root
  )
{
    FunctionalLocation,
    SuperiorFunctionalLocation,
    $node.parent_id as ParentNode,
    $node.node_id as ChildNode,
    $node.hierarchy_is_orphan as HierarchyIsOrphan,
    $node.hierarchy_level as HierarchyLevel,
    $node.hierarchy_rank as HierarchyRank,
    $node.hierarchy_parent_rank as HierarchyParentRank,
    $node.hierarchy_tree_size as HierarchyTreeSize 
}

But then if I try use this, it's unclear from all the doco on how I get the descendents.

e.g. If I use the first CDS view in SQL, it's pretty straightforward for a given Functional Location to get all Descendants.

SELECT 
   FunctionalLocation
 FROM 
  HIERARCHY( source YI_FLOCHierarhcyRelationship 
  child to parent association _parent start where FunctionalLocation = '?0100000000000007264'
    siblings order by functionallocation )

But this SQL can't be put into a CDS view directly, so I'm trying to leverage the Hierarchy CDS view above instead.

Ideally, I'd like to create a CDS association that looks a little like this (making up stuff here) but have no idea of the format - If I query YI_FLOCHierarhcy directly, or via a CDS association, it just returns the 1st level of info.

define view yI_mwhtest as select from I_FunctionalLocation as _Lookup 
  association [0..*] to YI_FLOCHierarhcy as _Hierarchy start where FunctionalLocation = $Projection.FunctionalLocation
 {
   key _Lookup.FunctionalLocation,
   key _Hierarchy.FunctionalLocation,
   
}

If I could do this, I can then I can use this to create another CDS view with association to Work Orders so I can get all Work Orders for a given Functional Location and it's descendants.

Any ideas or examples in S/4HANA 1809 to help here?

Thanks,

Matt

BTW - Here's my temporary dodgy version which has the result I'm after:

@AbapCatalog.sqlViewName: 'YIDODGYHIER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'FLOC Hierarchy Query'
define view YI_DodgyFLOChiearchy as select from YI_FLOCHierarhcyRelationship as _Root {
    key FunctionalLocation,
    FunctionalLocation as LookupFunctionalLocation
} union all
  select from YI_FLOCHierarhcyRelationship as _Root 
    inner join YI_FLOCHierarhcyRelationship as _First on _Root.FunctionalLocation = _First.SuperiorFunctionalLocation {
  _First.FunctionalLocation,
  _Root.FunctionalLocation as LookupFunctionalLocation 
} union all
  select from YI_FLOCHierarhcyRelationship as _Root 
    inner join YI_FLOCHierarhcyRelationship as _First on _Root.FunctionalLocation = _First.SuperiorFunctionalLocation 
    inner join YI_FLOCHierarhcyRelationship as _Second on _First.FunctionalLocation = _Second.SuperiorFunctionalLocation {
  _Second.FunctionalLocation,
  _Root.FunctionalLocation as LookupFunctionalLocation 
} union all
  select from YI_FLOCHierarhcyRelationship as _Root 
    inner join YI_FLOCHierarhcyRelationship as _First on _Root.FunctionalLocation = _First.SuperiorFunctionalLocation 
    inner join YI_FLOCHierarhcyRelationship as _Second on _First.FunctionalLocation = _Second.SuperiorFunctionalLocation 
    inner join YI_FLOCHierarhcyRelationship as _Third on _Second.FunctionalLocation = _Third.SuperiorFunctionalLocation {
  _Third.FunctionalLocation,
  _Root.FunctionalLocation as LookupFunctionalLocation 
} union all
  select from YI_FLOCHierarhcyRelationship as _Root 
    inner join YI_FLOCHierarhcyRelationship as _First on _Root.FunctionalLocation = _First.SuperiorFunctionalLocation 
    inner join YI_FLOCHierarhcyRelationship as _Second on _First.FunctionalLocation = _Second.SuperiorFunctionalLocation 
    inner join YI_FLOCHierarhcyRelationship as _Third on _Second.FunctionalLocation = _Third.SuperiorFunctionalLocation 
    inner join YI_FLOCHierarhcyRelationship as _Fourth on _Third.FunctionalLocation = _Fourth.SuperiorFunctionalLocation {
  _Fourth.FunctionalLocation,
  _Root.FunctionalLocation as LookupFunctionalLocation 
}

[WRoeckelein · Accepted solution]

Hi 6b8e090ba50d45ec894fcae83b2f8a4a ,

for this hierarchy evaluations like HIERARCHY_DESCENDANTS exist. But they still can't be used in ABAP CDS but only in ABAP SQL or in SQLScript in CDS table functions. Performance of such a construct seems to be about factor 10 worse compared to your "dodgy" version based on my experiments, but they can handle arbitrary depths.

Do something like this

define table function yI_FL_Descendants with parameters

@Environment.systemField: #CLIENT

@Consumption.hidden: true

p_client: abap.clnt

returns {

key client : abap.clnt;

key FL : tplnr;

key descendantFL: tplnr;

hierarchy_level : abap.int4;

} implemented by YCL_FL_Descendants=>get_descendants;

METHOD get_descendants BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING YI_FLOCHierarhcy.

RETURN SELECT p_client as client, start_id as FL, node_id as descendantFL, hierarchy_level FROM HIERARCHY_DESCENDANTS ( SOURCE YI_FLOCHierarhcy START ( SELECT node_id AS start_id, hierachy_rank AS start_rank FROM YI_FLOCHierarhcy ) );

ENDMETHOD.

Regards,

Wolfgang

[MattHarding · Accepted solution]

Thanks Wolfgang - Next time I'll keep this in mind (and measure performance) - Note - I was able to get something working without SQL Script, but had to use parameters which I now know to avoid at all costs as it causes all sorts of limitations down the line (but does seem fast in doing it this way since this view is used in numerous places for an Overview Page):

define view Z_FI_CostCentreGroupCCs
  with parameters
    @EndUserText.label: 'Cost Centre Group'
    CostCentreGroup : subsetname
  as select from zi_fi_costcentrehierarchy ( CostCentreGroup:$parameters.CostCentreGroup ) as _CostCentreGroups

where the hierarchy looked like this

define hierarchy ZI_FI_CostCentreHierarchy 
with parameters 
    CostCentreGroup: subsetname 
  as parent child hierarchy (
    source ZI_FI_CostCentreHierNode
    child to parent association _Hierarchy
    start where Id = $parameters.CostCentreGroup   
    siblings order by Id
    multiple parents not allowed 
  )
{
    Id,
    Parent   
}

With underlying source CDS as:

define view ZI_FI_CostCentreHierNode as select from setnode   
association[0..1] to ZI_FI_CostCentreHierNode as _Hierarchy
  on _Hierarchy.Id = $projection.Parent
{
    key subsetname as Id,
    setname as Parent,
    _Hierarchy
} 
where setclass = '0101' // Cost Center Group
    and subclass = 'MyCompanyValue'    
group by subsetname, setname

[WRoeckelein · Accepted solution]

Yes, this is fast because it is only CDS views which can alltogether get evalutated inside the HANA engine and only the result is tranferred. With table functions there is back and forth between ABAP and HANA which is too much performance impact.

Nice trick on using the desired root node directly in the hierarchy CDS. Unfortunatly in my case I need the anchestors so I have to stay with my dodgy version because I can't do your trick for anchestors and the hierarchy evaluation in the table function is too slow and still no cache for ABAP CDS hierarchy.

Yes, when doing such stuff apparently you have always have to measure (and think carefully what to measure), I had too much surprices, I also sometimes take a look at the HANA query execution plans ...