Skip to Content

For space requirements this blog has been split in 4 parts:

                       Link   Content
Hierarchy evaluation using CDS Table Function  Hierarchy evaluation using CDS Table Function in S/4
This part  Step 1 – Hierarchies in S/4 HANA  Analytics
Step 2 – Define the Analytical Query CDS View  Step 2 – Define the Analytical Query CDS View
 Step 3. Smart TreeTable UI for Hierarchies  Step 3 – Smart Tree Table UI for Hierarchies

 

In continuation to previous blog in this blog 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:  if you have hierarchy already in place you can skip this step otherwise you can check my previous blog for evaluating the hierarchy using CDS table function.

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:

  1. @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.

In the next blog define analytical query CDS Views I will show Analytical query consumption view which uses cube view and generates the hierarchy.

Your suggestions, feedback, comments on this blog are most welcome.

 

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Remya Raj

    Hi Pavan,

     

    Nice Blog! Very helpful indeed…

    Could you please give bit more details on annotation @ObjectModel.representativeKey? Like when is this mandatory and what is the use case?

     

    Thanks  & Regards,

    Remya

    (0) 
    1. Pavan Kumar Reddy Post author

      Hi Remya,

      Annotation @ObjectModel.representativeKey represents primary key of  Dimension view. In my case Employee is primary key which will be used as anchor for defining foreign key relationships. In cube view I have used dimension view as association(foreign key association) based on primary key, Employee is represented as representative foreign key(in cube view), for this field foreign key association is defined.

      When you use entity relationships base entity should have representative key(primary key) only then view will become target of a foreign key association. In Analytics hierarchical representation of entity are handled using representative foreign key field.

      Regards,

      Pavan

       

      (1) 

Leave a Reply