Defining a Characteristic Structure

In addition to a measure structure, a second type of structure is supported. This structure is known as a characteristic structure.

A characteristic structure includes one or more members which are defined by applying restrictions to dimensions. For example, a simple member would be June which is a simple restriction on the dimension month. But a member can also be defined with multiple single values or intervals. For example, Jan, Feb, March or Jan - June.

A characteristic structure member can also be defined using multiple dimensions. For example, combining the dimensions month and country to define the member June / France.

Measures cannot be defined as members of a characteristic structure.

To assign an member to the characteristic structure, you use annotation @AnalyticsDetails.query: { onCharacteristicStructure: true }

Whilst a restricted measure is defined using CASE WHEN <filters on dimensions> THEN <measure from cube> ELSE NULL END, a member of a characteristic structure is defined using CASE WHEN <filters on dimensions> THEN 1 ELSE NULL END.

Filters on dimensions in a characteristic structure follow the same rules as filters in a restricted measure. For example, filters on different dimensions have to be combined with AND.

Here is the code extract that focuses on the implementation of a characteristic structure:

12345678910111213
// Characteristic Structure
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @EndUserText.label: 'Jul 2024'
  case when FlightYearMonth = '202407' then 1 else null end as Jul2024,
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @EndUserText.label: 'Jun 2024'  
  case when FlightYearMonth = '202406' then 1 else null end as Jun2024

Notice that we have defined two members in our characteristic structure, and the characteristic structure is assigned to the rows axis.

When we display the result we see the characteristic structure appears on the rows axis with the two members listed in the sequence defined in the query. The measure structure has been assigned to the columns axis, but this is not shown in the code extract.

When the two structures are assigned to different axis, we generate a cross-tab, also known as a grid. The grid comprises cells that are defined where the rows and columns intersect. With the grid layout we have more options for defining advanced queries. In the next lesson we will learn how we work with cells in the grid.

Individual dimensions can also be included in queries that have a characteristic structure. The dimensions can be assigned to the same axis as the characteristic structure, or the opposing axis.

Watch this video to learn how to combine dimensions with a characteristic structure:

It is also possible to arrange the characteristic members hierarchically using the same syntax used for the measure structure.

As well as defining members by applying restrictions to dimensions, you can also define calculations on members of a characteristic structure. The operands of the calculation must be members from the same structure, for example: Summer Sales = June + July. You cannot use a measure as an operand of the formula.

A calculation in the characteristic structure follows the same syntax as a calculation in the measure structure.

Here is the code extract to implement a calculation in a characteristic structure:

123456789101112131415161718192021
 // Characteristic Structure
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @EndUserText.label: 'Jul 2024'
  case when FlightYearMonth = '202407' then 1 else null end as Jul2024,
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @EndUserText.label: 'Jun 2024'  
  case when FlightYearMonth = '202406' then 1 else null end as Jun2024,
   
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @Aggregation.default: #FORMULA
  @EndUserText.label: 'Difference' 
  $projection.Jul2024 - $projection.Jun2024 as MonthDiff

Here we see the result of the query:

Notice how a calculation in the characteristic structure is applied to all members of the measure structure. In our case we see the calculated characteristic member Difference is applied to all three measures.

Since Maximum Capacity is a measure and Difference is a formula, the value of intersection cell Maximum Capacity / Difference is the value of the cell Maximum Capacity / Jul 2024 minus the value of the cell Maximum Capacity / Jun 2024. This corresponds to the formula defined for the element Difference on the characteristic structure. So this is 5,460 minus 6,155 to give the result -695.

If an intersection cell in the grid is defined by a formula on the measure structure and also a formula on the characteristic structure, as we see in the example above with the intersection cell Difference / Utilization, the calculation comes from the formula of the measure structure. In our case, the column Utilization is calculated as Occupied Seats divided by Maximum Capacity multiplied by 100. So this is -67 divided by -695 multiplied by 100 to give the result 9.64. The formula of our characteristic structure is ignored.

However, if the formula of the characteristic structure should be used and the formula of the measure structure should be ignored, you can control this using special annotations described in the next section.

The previous example showed us that there are times when we should resolve collisions of formulas.

The annotation @AnalyticsDetails.query.collisionHandling.formula: #THIS can be assigned.

As described above, the value of the cell Utilization / Difference is calculated using the formula of element Utilization.

Here is the code that now includes an additional formula.

1234567891011121314151617181920212223242526272829303132
// Characteristic Structure
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @EndUserText.label: 'Jul 2024'
  case when FlightYearMonth = '202407' then 1 else null end as Jul2024,
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @EndUserText.label: 'Jun 2024'  
  case when FlightYearMonth = '202406' then 1 else null end as Jun2024,
   
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true
  }
  @Aggregation.default: #FORMULA
  @EndUserText.label: 'Difference' 
  $projection.Jul2024 - $projection.Jun2024 as MonthDiff, 
  
  @AnalyticsDetails.query: {
    axis: #ROWS,
    onCharacteristicStructure: true,
    collisionHandling: {
      formula: #THIS      
    }
  }
  @Aggregation.default: #FORMULA
  @EndUserText.label: 'Difference prio' 
  $projection.Jul2024 - $projection.Jun2024 as MonthDiffPrio

Notice that the second formula Difference prio is a copy of the original formula, but includes the setting collisionHandling: with the value #THIS that ensures the formula definition is used from the characteristic structure, instead of the settings from the measure structure.

This means we are now using the formula for cell Utilization / Difference prio which is the difference between Utilization / Jul 2024 and Utilization / Jun 2024, since here the formula defined by element Difference prio is applied.

Here is the result with the two formulas in the characteristic structure generating different results in the intersection cell where formulas collide.

Notice the first formula Difference is calculated horizontally using the measure structure definition. The second formula Difference Prio is calculated vertically using the characteristic structure definition. Now we see 73.30 minus 66.11 to give 7.19.

Both are mathematically correct values but you need to decide which is the one you require.

Apart from calculations, there are also other settings that might collide and need to be resolved

The complete list of settings that can collide, include:

• Formula definitions
• Number of decimals - @AnalyticsDetails.query.decimals
• Scaling - @AnalyticsDetails.query.scaling
• Semantic Object - @Consumption.SemanticObject

By default, the setting of the element in the measure structure is used.

However, this default direction can be overwritten using the annotations:

• @AnalyticsDetails.query.collisionHandling.formula
• @AnalyticsDetails.query.collisionHandling.decimals
• @AnalyticsDetails.query.collisionHandling.scaling
• @AnalyticsDetails.query.collisionHandling.semanticObject

These annotations can be used for elements on both structures. The annotations can be set to #THIS, #CONCURRENT, #DEFAULT. #DEFAULT is equal to not using this annotation.

The impact of these settings on a structure element is as follows:

M means that the property of the element of the measure structure is applied, and C means that the property of the characteristic structure is applied.