Defining Restricted Measures

Measures without context usually have no meaning. For example, the measure quantity sold is meaningless without context. What was sold? When was it sold? Where was it sold?

Context can be added to measures by including dimensions in the rows or columns of the analytical query result. In this case, you are relying on the dimension values being present in the rows or columns to add the context. But if a business user removes the dimensions, they can lose the context of the measure.

There is another way to add context to a measure that ensures the context stays with the measure even if dimensions are removed from the columns or rows.

To achieve this, we define restricted measures.

A restricted measure is an analytical query object that is defined by selecting a basic measure from a cube and applying filters from one or more dimension values to it.

For example: revenue (the basic measure) for fiscal year 2024 - South-East Region (the filters).

Once you have defined a restricted measure in the analytical query, it can be used in place of a basic measure. Because it carries its own predefined filters, a restricted measure is ready to use and does not rely on the business user adding dimensions to the result to give meaning to the measure. A business user could add additional filters to the analytical query, and these filters would affect the restricted measures.

For example, if our example restricted measure revenue for fiscal year 2024 - South-East Region appeared in an analytical query result, it would already have business meaning. But then a business user could apply the additional dimension product group to the analytical query result, and choose a filter value such as bicycles to identify the type of products sold. The result would be Revenue for fiscal year 2024 - South-East Region - Bicycles.

However, a business user cannot remove any predefined dimension filter values from the restricted measure.

Restricted measures provide a way for analytical query developers to ensure a measure always includes the essential dimension filters to provide meaning to a measure.

Defining restricted measures can also solve the problem where a base measure has absolutely no meaning until it has one or more filters applied. For example in financial reporting, we frequently use the amount measure. One of the essential dimensions that must always be applied to this measure is value type. Typical values of value type are actual and plan. If we fail to apply one of these filters to the measure amount, we would then include both plan and actual values in an aggregation. When we add actual and plan values to create a total, we have an invalid result. To avoid this, the analytical query developers should define individual restricted measures, such as actual amount and planned amount.

Restricted measures are especially helpful when you want to define multiple contexts for the same measure. For example, we could define a restricted measure as amount current period and another restricted measure as amount previous period. Then it is easy to define a formula that calculates the difference between these two restricted measures.

A restricted measure is defined in the analytical query based on a basic measure from the cube. You cannot create a restricted measure from another restricted measure.

Whereas a query filter is valid for an entire analytical query and applies to all measures, the filters used to define a restricted measure are valid only for that individual restricted measure.

A common use of restricted measures is to compare different time segments, or to compare plan versus actual. In these cases, the same measure is used in the definitions but different dimension values create new versions of the measure.

You define restricted measures by using the keyword case.

Let's look at an example of how we might define a restricted measure for First Quarter Amount.

12345678910
virtual MyVirtualCurrency : abap.cuky, 

@Semantics.amount.currencyCode: 'MyVirtualCurrency' 
case when 
( FiscalYear = '2024' ) and 
( FiscalPeriod = '001' or FiscalPeriod = '002' or FiscalPeriod = '003' ) 
then curr_to_decfloat_amount( AmountInCompanyCodeCurrency ) 
else null 
end as FirstQuarterAmount 

Keep in mind that different restrictions of the same dimension can only be combined using OR, and different dimensions can only be combined with AND.

Watch the video to learn how to implement restricted measures:

When you define filters in an analytical query, the filters apply to all measures. However, there are times when some measures defined in the analytical query should ignore the filters.

Let's look at an example:

First, we let's review the code that defines measures that ignore filters.

The code defines three measures: OccupiedSeats, OccupiedSeatsAll and OccupiedSeatsConnection. The first measure does not ignore filters. The other two measures ignore filters. Notice the annotation @AnalyticsDetails.query.ignoreFurtherFilter and the setting that defines which dimension(s) is ignored.

Below we see the result that displays the three measures:

The first measure OccupiedSeats applies the filters for each airline and each departure airport because those are the dimensions used in the rows. For example, we can see for airline AA and departure airport SFO, there are 693 available seats.

The second measure OccupiedSeatsAll ignores all filters, including those filters defined in the where clause of the analytical query definition. Our analytical query includes a where clause that restricts airlines to the range AA - LH. This measure displays the number of available seats for all airlines and for all departure airports. We see there are 9161 available seats.

The third measure OccupiedSeatsConnection ignores the filter for departure airport but retains all other filters. Notice how we specify the dimension(s) to be ignored for filtering. In our case, it means the where clause is retained so a filter is applied for all airlines between the values AA - LH. This measure displays the total number of available seats for each airline in the range AA-LH, regardless of departure airport. For airline AA, we see there are 1794 available seats.