Working with Hierarchy Views

Before we review the hierarchy view, let's take a moment to remind ourselves of the key terminology used when describing hierarchies that are used in analytics.

An example of a hierarchy that organizes airports by geography. The diagram is used to explain the terminology used when working with hierarchies

Root - the start node of the hierarchy (there might be multiple roots).
Node - a grouping of sub-nodes or leaves.
Parent - a node that is the first ascendant of another node or leaf. A node can have only one parent, but leaves can have multiple parents.
Child - a node or leaf that is an descendant of another node.
Leaf - a dimension value that is assigned to a node.
Siblings - leaves or nodes that are assigned to the same parent node

A node can be either dimension value-based or text-based. A dimension value-based node is represented by a value in a dimension. These nodes are based on well-known business entities such as countries, states or cities. A text-based node is not represented by a dimension value and is simply a node that is given a custom text to describe it. Text-based nodes are usually created to form a business-specific definition of a group of nodes or leaves where there is no existing business value. For example, a custom node called My Top Campaign Team which might include various team members.

A leaf always appears at the end of the hierarchy path, and is represented by a dimension value that describes the entire hierarchy. In our hierarchy Airport Hierarchy, the leaves are based on the values for the dimension airport.

There are five CDS views that are relevant to hierarchies:

Source View (mandatory) - contains a flat list of fields use in the hierarchy including all necessary associations.
Hierarchy View (mandatory) - reads the source view to build one or more hierarchys - there might be multiple hierarchy instances.
Hierarchy Directory View (optional) - describes each hierarchy instance with additional meta data such as last changed date.
Hierarchy Directory Text View (optional) - provides the text for each hierarchy instance so that business users know the purpose of the hierarchy.
Hierarchy Node View (optional) - provides the attributes and text for the hierarchy nodes that do not have a dedicated dimension.

Source View

Let's start with the mandatory source view that generates the base data of the hierarchy.

In the diagram below you see a hierarchy that organizes airports of the world. We have expanded the hierarchy to Hamburg Airport. Notice the rows of the source view that describe the parent-child relationships between the nodes of the hierarchy. You see how the NodeID and ParentNodeID are used to form a recursive parent-child relationship.

a data preview of the source records for a hierarchy and the corresponding hierarchy structure that appears in the query result.

Let's review the code used to generate the source view of the hierarchy.

Code Snippet

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Base View for Airport Hierarchy'

define view entity /DMO/ANA_P_AirportHierBase
  as select from /dmo/ana_ap_h

  association [0..1] to /DMO/ANA_I_Airport         as _Airport on  _Airport.AirportID = $projection.AirportID

  association [0..1] to /DMO/ANA_I_City            as _City    on  _City.country = $projection.Country
                                                               and _City.city    = $projection.City

  association [0..1] to I_Country                  as _Country on  _Country.Country = $projection.Country

  association [0..1] to /DMO/ANA_I_AirportHierDir  as _Dir     on  _Dir.HierarchyID = $projection.HierarchyID

  association [0..1] to /DMO/ANA_I_AirportHierNode as _Node    on  _Node.HierarchyID = $projection.HierarchyID
                                                               and _Node.NodeName    = $projection.NodeName

  association [0..1] to /DMO/ANA_P_AirportHierBase as _Parent  on  _Parent.HierarchyID = $projection.HierarchyID
                                                               and _Parent.NodeID      = $projection.ParentNodeID
{
      @ObjectModel.foreignKey.association: '_Dir'
  key hierarchy_id    as HierarchyID,
  key node_id         as NodeID,
      parent_node_id  as ParentNodeID,
      @ObjectModel.foreignKey.association: '_Airport'
      airport         as AirportID,
      @ObjectModel.foreignKey.association: '_City'
      city            as City,
      @ObjectModel.foreignKey.association: '_Country'
      country         as Country,
      @ObjectModel.foreignKey.association: '_Node'
      @EndUserText.label: 'Hierarchy Node'
      nodename        as NodeName,
      case when nodetype = 'AIRPORT'
           then 'AIRPORTID'
           else nodetype
      end             as NodeType,
      sequence_number as SequenceNumber,

      _Airport,
      _City,
      _Country,

      _Node,
      _Dir,
      _Parent
}

The most important aspects of the source view follow:

If the hierarchy view contains multiple hierarchies, an association to a hierarchy directory is needed. The fields in the ON condition must be key fields.
We define the parent > child relationship using an association _Parent. This relationship is recursive, so the target must be the source view - in other words, itself.
- The ON condition of the association must have the same number of fields on each side and they must be of the same type.
- The child item must be a key field(s), whereas the parent item is an attribute.
- All key fields must be included in the ON condition.
The example defines hierarchies on airports. The nodes in our hierarchy have different types: some are cities, others are countries, and some are just nodes with text. For all of these types, foreign key associations are needed. In the example these are _Airport, _City, _Country, and _Node. The target of one of these associations must be the dimension view which has a hierarchy association to the hierarchy view. These records will become the leaves of the hierarchy. In the example, this is _Airport.
One field is needed which is filled with the field name which should be used to interpret a record (here it is Nodetype ). If, for example a record should represent a leaf, then field Airport must be filled and field Nodetype must be filled with Airport. This field must be included in the hierarchy definition as nodetype.
All key fields must be either part of the directory association or the recursive association.

Hierarchy View

The next view is also mandatory and is the hierarchy view. This view generates the hierarchy structure by reading the source view. This view includes the important statement define hierarchy.

Associations cannot be defined in this view and that is one of the main reasons why we need the source view.

If the hierarchy view contains multiple hierarchies, then a hierarchy directory view is required.

Code Snippet

123456789101112131415161718192021222324252627282930313233343536373839
@ObjectModel: {
  supportedCapabilities: [ #ANALYTICAL_PARENT_CHILD_HIERARCHY_NODE ],
  modelingPattern: #ANALYTICAL_PARENT_CHILD_HIERARCHY_NODE
}
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Airport Hierarchy'
define hierarchy /DMO/ANA_I_AirportHier
  with parameters
    p_HierarchyID : /dmo/ana_airport_hieid
  as parent child hierarchy(
    source /DMO/ANA_P_AirportHierBase
    child to parent association _Parent
    directory _Dir filter by
      HierarchyID = $parameters.p_HierarchyID
    start where
      ParentNodeID is initial
    siblings order by
      SequenceNumber
    nodetype NodeType
  )

{
  key HierarchyID,
  key NodeID,
      ParentNodeID,
      AirportID,
      City,
      Country,
      NodeName,
      NodeType,

      _Dir,
      _Parent,
      _Airport,
      _City,
      _Country,
      _Node

}

The hierarchy view usually includes parameters which correspond to the key fields of the hierarchy directory. These parameters allow a business user to choose the hierarchy instance at run-time. There might be multiple instances of a hierarchy available to the business user, but they can only use one at-a-time in an analytical query.

In this view you can also define the root(s) of the hierarchy. To do this you define the start where and usually this is with the parent value is initial. This means all records without a parent become a root.

In this view you can also define the sequence of the siblings. Siblings are node or leaf values that occupy the same level of the hierarchy. For example, in what order should the countries (nodes) appear or in what order should the airports (leaves) appear. Notice siblings order by.

Hierarchy Directory

If a hierarchy directory is required, it should follow the rules of dimension views, but without defining attributes.

Here is the data preview of the hierarchy directory view below:

example of the hierarchy directory view data preview

Here is an example of the code for this hierarchy directory view:

Code Snippet

123456789101112131415161718
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Directory for Airport Hierarchies'
@Analytics.dataCategory: #DIMENSION
@ObjectModel.representativeKey: 'HierarchyID'
@ObjectModel: {
  supportedCapabilities: [ #ANALYTICAL_DIMENSION ],
  modelingPattern: #ANALYTICAL_DIMENSION
}
define view entity /DMO/ANA_I_AirportHierDir
  as select from /dmo/ana_ap_hd
{
      @ObjectModel.text.element: [ 'Name' ]
  key hierarchy_id    as HierarchyID,
      @Semantics.text: true
      name            as Name,
      @Semantics.systemDateTime.lastChangedAt: true
      last_changed_at as LastChangedAt
}

If available, this view should contain a field that describes when the hierarchy was last changed. When this field is a time-stamp, then annotation @Semantics.systemDateTime.lastChangedAt should be used.

If it is a field of type DATS or DATN then the annotation @Semantics.systemDate.lastChangedAt should be added. If such a field is available the Analytical Engine can buffer hierarchy instances. The buffer will be kept in sync with the help of the last-changed-at field.

Hierarchy Directory Text View

This view is only required if language dependent texts that define the hierarchy description are needed. Otherwise the text for the hierarchy is part of the hierarchy directory view. If this view is needed you must define a text association in the hierarchy directory view.

Hierarchy Node View

Now we come to the next view which is the hierarchy node view. This view is helpful to provide the text for the hierarchy nodes that are not based on dimension values. In our case, these are the nodes: WORLD, AFRICA, EUROPE, and so on. If this view were not available then we would rely on the names of the nodes and these names might not be meaningful to a business user. In our case, we are lucky as the node names are meaningful so we do not need text. If we needed additional attributes or language dependent texts, then we could define a text view. This node follows the pattern of the dimension view. The only special thing here is the hierarchy id is a key field.

The hierarchy node view is technically a dimension view.

Code Snippet

1234567891011121314151617181920
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Airport Hierarchy Nodes'
@Analytics.dataCategory: #DIMENSION
@ObjectModel.representativeKey: 'Nodename'
@ObjectModel: {
  supportedCapabilities: [ #ANALYTICAL_DIMENSION ],
  modelingPattern: #ANALYTICAL_DIMENSION
}
define view entity /DMO/ANA_I_AirportHierNode as select from /dmo/ana_ap_hn
  association to /DMO/ANA_I_AirportHierDir as _Dir on _Dir.HierarchyID = $projection.HierarchyID
{
       @ObjectModel.foreignKey.association: '_Dir'
   key hierarchy_id  as HierarchyID,
       @ObjectModel.text.element: [ 'NodeText' ]
   key nodename as NodeName,
       @Semantics.text: true
       nodetext as NodeText,
       
   _Dir
}

The hierarchy nodes that are based on dimension values get their text from the associations to each dimension view defined in the hierarchy view. In our case, we have dimension views for COUNTRY, CITY and AIRPORT.