Defining a Measure Structure

Structures are powerful query objects that help you to develop advanced query result layouts. There are two types of structure: measure structures and characteristic structures. We will first cover measure structures.

All measures in an analytical query belong to a measure structure. There is only be one measure structure in an analytical query, and that structure can be assigned either to the rows or column axis.

A measure structure is not explicitly defined in the CDS view, but is generated at run-time and includes all measures that you have defined in your query. The default axis assignment of the measure structure is derived from the axis assignment of the first measure defined in the analytical query. For example, if the first measure defined in the query is assigned to the column axis, then the measure structure is assigned to the column axis. The axis assignments for the other measures in the query, if they are defined, are ignored.

A measure structure behaves like a dimension. Each member of the measure structure would be the equivalent of a dimension value. Also, just like a dimension, a measure structure can be assigned to the row or column axis, and filters can be defined. The filter values for a measure structure are the individual measures within the structure. For example a measure structure might include the measures order quantity, delivery quantity and invoice quantity. You can decide which measures to include in the query result by applying filters on the measure structure.

The measure structure axis assignment can be swapped between column and row axis and vice-versa, by the business user using the front end tools.

Unlike a dimension, a measure structure cannot be assigned to the free axis in order to exclude a measure from the query result. To remove individual measures from the measure structure, you simply apply filters to the measure structure.

Even if there is only one measure defined in your analytical query, the measure will still belong to a measure structure. So this means every query that includes at least one measure will always include a measure structure. IF there are no measures defined in a query, then there will be no measure structure generated.

The members of a measure structure can include:

• basic measures
• restricted measures
• calculated measures

Watch the video to learn how to implement a measure structure in an analytical query.

It is possible to organize the measures of a structure into a hierarchy to simplify the query results. This is especially helpful if there are many measures in your analytical query.

For example, you could assign the calculated measure profit to the top level of the hierarchy and assign the operands of this calculation sales price and cost to the lower level of the hierarchy. If the hierarchy is collapsed, the business user would only see profit. Expanding the hierarchy to the lower level would reveal the sales price and cost. This is a great technique to initially hide measures that might not be interesting, whilst allowing the business user to decide if they would like to reveal the underlying measures by expanding the hierarchy to the next level.

To implement a measure hierarchy, you assign each measure in the structure to a parent measure. You do this by assigning the following annotation to the measure: @AnalyticsDetails.query.elementHierarchy.parent.

To simply the initial result, you can also choose to collapse the hierarchy by setting the parent measure to collapsed.. You do this by assigning following annotation to the parent measure:@AnalyticsDetails.query.elementHierarchy.initiallyCollapsed

Here is the code to implement the hierarchy within a measure structure:

12345678910111213141516
// Measures 
  @AnalyticsDetails.query.axis: #COLUMNS
  @Aggregation.default: #FORMULA
  @EndUserText.label: 'Difference Available Seats' 
  @AnalyticsDetails.query.elementHierarchy.initiallyCollapsed: true
  $projection.MaxSeatsCurrent - $projection.MaxSeatsPrevious as MaxSeatsDiff,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  @EndUserText.label: 'Available Seats Jul 2024' 
  @AnalyticsDetails.query.elementHierarchy.parent: 'MaxSeatsDiff'
  case when FlightYearMonth = '202407' then MaximumSeats else null end as MaxSeatsCurrent,
    
  @AnalyticsDetails.query.axis: #COLUMNS
  @EndUserText.label: 'Available Seats Jun 2024' 
  @AnalyticsDetails.query.elementHierarchy.parent: 'MaxSeatsDiff'
  case when FlightYearMonth = '202406' then MaximumSeats else null end as MaxSeatsPrevious,

Here we see three measures. Notice how the second and third measures have been assigned to the parent measure MaxSeatsDiff. The parent measure has been defined with the collapse annotation setting.

It is possible to assign parent measures to other parent measures, thus generating a multi-level hierarchy.

Always remember, in the analytical query definition, you are simply providing default settings that determine the initial view of the query result. In the front-end tools, the business user can decide if they want to display the measures as a flat list instead of the hierarchy that you have defined. Try to choose initial query settings that would be helpful to the business users.

Watch the video to learn how to implement a measure structure using a hierarchy.

Dimensions can also exist on the same axis as the measure structure. For example, you might include the dimension airline on the rows axis, with the measure structure that includes various measures. For each country in the rows, you would display the measures as shown below:

Here we see an example of a measure structure with a dimension on the same axis. In this case it is the row axis. You could also include the characteristic and measure structures on the column axis. You could also include additional dimensions on the opposite axis to generate a cross-tab layout.