For space requirements this blog has been split in 4 parts:
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:
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:
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:
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:
Below is the sample code for cube view:
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.
| 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:
@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.
- SAP Managed Tags:
6 Comments
