Step by Step Hierarchies in S/4 HANA Analytics

I am going to show step by step procedure to display hierarchies in S/4 using analytical CDS views. I will use manger employee hierarchy as an example here.

To achieve this you need:

◉ A dimension view with an employee as key, and his manager as attribute, and optionally some time-dependency information; this view gets the @Hierarchy annotations.
◉ An analytical cube with the employee as dimension attribute, other attributes, and the relevant measures (actual/planned working hours etc).
◉ An analytical query that takes a manager as input and uses it as root of the hierarchy.
The following diagrams show high level architecture of these views and their relations, as well as an example of a simple representation.

The display would then show a tree structure of the employees below the manager, with sub-ordinate managers as hierarchy nodes.

Below are the steps to be followed to display S/4 hierarchies using CDS views:

1. Construction of Employee Manager Sample hierarchy:

Below is the sample code for constructing hierarchy:

@AbapCatalog.sqlViewName: 'ZEMH_PE'

define view ZEMH_P_Employee

  as

  select from t000

{

  key '00000001'                            as Employee,

      cast( 'Albert Ale' as abap.char(20))  as EmployeeName,

      cast( 'ALE'        as abap.char(12))  as UserID,

      cast( '' as abap.numc(8))             as Manager,

      cast( 'COST1' as abap.char( 10 ))     as CostCenter

}

where

  mandt = '000'

union all

select from t000

{

  '00000002'               as Employee,

  'Benjamin Baker'         as EmployeeName,

  'DENTZER'                as UserID,

  '00000001'               as Manager,

  'COST2'                  as CostCenter

}

where

  mandt = '000'

union all

select from t000

{

  '00000003'               as Employee,

  'Christian Cole'         as EmployeeName,

  'KRAUSEGER'              as UserID,

  '00000001'               as Manager,

  'COST3'                  as CostCenter

}

where

  mandt = '000'

union all

select from t000

{

  '00000004'              as Employee,

  'David Double'          as EmployeeName,

  'DOUBLE'                as UserID,

  '00000002'              as Manager,

  'COST2'                 as CostCenter

}

where

  mandt = '000'

union all

select from t000

{

  '00000005'             as Employee,

  'Elisabeth Elmer'      as EmployeeName,

  'ELMER'                as UserID,

  '00000002'             as Manager,

  'COST2'                as CostCenter

}

where

  mandt = '000'

union all

select from t000

{

  '00000006'            as Employee,

  'Frederik Flow'       as EmployeeName,

  'FLOW'                as UserID,

  '00000003'            as Manager,

  'COST3'               as CostCenter

}

where

  mandt = '000'

union all

select from t000

{

  '00000007'                 as Employee,

  'Gary Gallagher'           as EmployeeName,

  'GALLAGHER'                as UserID,

  '00000005'                 as Manager,

  'COST2'                    as CostCenter

}

where

  mandt = '000'

2. Dimension View Creation: Before Creating dimension view you need to know what is dimension view, View becomes dimension view if data category annotation @Analytics.dataCategory:#Dimension is specified. By specifying the dataCategory the developer can give directives and hints to an analytical engine how to interpret individual entities.

Dimension definition: A collection of similar data which, together with other such collections, forms the structure of a cube. Typical dimensions include time, product, and geography. Each dimension may be organized into a basic parent-child hierarchy or, if supported by the data source, a hierarchy of levels. For example, a geography dimension might include levels for continent, country, state, and city.

Below is the sample code for dimension view:

@EndUserText.label: 'Employee with Manager Hierarchy'

@AbapCatalog.sqlViewName: 'ZEMH_IE'

@Analytics.dataCategory: #DIMENSION

@ObjectModel.representativeKey: 'Employee'

@Hierarchy.parentChild: { name: 'Manager', 

  recurse : {

    parent:  [ 'Manager' ],

    child:   [ 'Employee' ]

} }

define view ZEMH_I_Employee

  as

  select from ZEMH_P_Employee

  association [0..1] to zemh_i_employee as _Manager on $projection.Manager = _Manager.Employee

{

      @ObjectModel.text.element:  [ 'EmployeeName' ]

      @EndUserText.label: 'Employee'

  key Employee,

      @Semantics.text: true

      @EndUserText.label: 'Employee Name'

      EmployeeName,

      Manager,

      _Manager,

      @EndUserText.label: 'Cost Center'

      CostCenter,

      UserID,

      'Employee' as NodeTypeElement

}

In Dimension View(ZEMH_I_Employee) employee is specified as key field and manager as attribute.

Annotation @ObjectModel.representativeKey represents the primary key of the of dimension view. This key is used as anchor for defining foreign key relationships.

Annotation @Hierarchy.parentChild specifies hiearchyname, on which parent and child fields recursion needs to be handled.

Annotation @ObjectModel.text.element is used for text arrangement for specific code text-pair, i.e link from code element to text element, here in our example code element is Employee attribute and text element is EmployeeName attribute of the view.

Annotation @EndUserText.label specifies label for each element which is shown on UI. Annotation @Semantics.text identifies a human-readable text (which is not necessarily language-dependent).

if you observe the code there is self association(view name ZEMH_I_Employee and association name is same) with cardinality [1 : 0..1] pointing to the parent node of the node is written which is mandatory in the dimension view which is used for recursion of Manger and employee.

Testing Dimension View: Tcode- RSRTS_ODP_DIS

Select ODP Context as ABAP Core Data Services and ODP Name as Dimension SQL View name ZEMH_IE as shown below and execute

Then you will see the dimension view structure, click on standard query button as below to test the output in query monitor

Result is shown as below:

Every analytical view whether it is a dimension, fact, cube or query view attributes has row, column and free characteristics. You can test by moving the attributes from  free to row by clicking on down arrow as shown above.

if you find empty values move the attributes free to row then you will get the result.

3. Cube View Creation: Cube view indicates a star schema. Usually it is used for a “View Entity” with a “Fact Entity”(View) in the center and “Dimension Entities”(View) around. View becomes cube view if annotation @Analytics.dataCategory: #CUBE is specified.

Fact Entity(view) indicates that the entity represents transactional data. Usually it contains the measures. So first we have to create a fact view which has measures for employee such as actual and planned working hours.

Below is the sample code for fact view:

@AbapCatalog.sqlViewName: 'ZEMH_PEH'

define view ZEMH_P_EmployeeHours

  as

  select from t000

{

  key '00000001'                       as Employee,

      cast( 8 as abap.dec( 7, 2 ))     as PlannedHours,

      cast( 6 as abap.dec( 7, 2 ))     as ActualHours

}

where

  mandt = '000'

union all select from t000 { '00000002' as Employee, 8 as PlannedHours, 8.5 as ActualHours } where mandt = '000'

union all select from t000 { '00000003' as Employee, 8 as PlannedHours, 4 as ActualHours } where mandt = '000'

union all select from t000 { '00000004' as Employee, 8 as PlannedHours, 0 as ActualHours } where mandt = '000'

union all select from t000 { '00000005' as Employee, 8 as PlannedHours, 8.5 as ActualHours } where mandt = '000'

union all select from t000 { '00000006' as Employee, 8 as PlannedHours, 8 as ActualHours } where mandt = '000'

union all select from t000 { '00000007' as Employee, 8 as PlannedHours, 5 as ActualHours } where mandt = '000'

Below is the sample code for cube view:

@AbapCatalog.sqlViewName: 'ZEMH_IEH1'

@Analytics.dataCategory: #CUBE

define view ZEMH_I_EmployeeHours1

  as

  select from ZEMH_P_EmployeeHours

  association [0..1] to ZEMH_I_Employee as _Employee on $projection.Employee = _Employee.Employee

{

  key Employee,

      _Employee,

      @DefaultAggregation: #SUM

      @EndUserText.label: 'Planned Hours'

      PlannedHours,

      @DefaultAggregation: #SUM

      @EndUserText.label: 'Actual Hours'

      ActualHours,

      _Employee.CostCenter

}

In the above cube view “ZEMH_P_EmployeeHours” is fact view contains measures which is used as data source and it has association to “ZEMH_I_Employee” dimension view which is created in step 2. This association is mandatory which represents association from dimension view to cube view, anchors hierarchy to cube view.

Annotation @DefaultAggregation: #SUM determines aggregated measure which result summation of planned and actual hours for employee.

Testing Cube View: Tcode- RSRTS_ODP_DIS

Repeat the Testing Dimension View in step2 for testing cube view results. Results would like below:

Sum of actual and planned hours is displayed as above in the cube result.