Defining Basic Variables

Almost all analytical queries include filters. Filters ensure that the results are meaningful to a business user. Filters also help to reduce the wait-time for the results to appear. Filter values that never change are often hard-coded in the analytical query definition.

However, hard-coding filter values in an analytical query means that the analytical query cannot be reused in different scenarios. To avoid this problem, we can implement variables in our analytical queries.

This example shows how we generate a variable which must be filled with a single value before the analytical query can be executed.

The value help for the variable is derived from the foreign-key association of the field AirlineID that is defined in the cube.

Below we see the analytical query results. Notice that the variable appears as a user prompt for AirlineID.

By implementing a variable, the analytical query is reusable because the filter for airline is no longer hard-coded. Instead, the business user is able to select the filter value at run-time to evaluate any airline.

If a from and to value is required, you can use an annotation that defines the parameter as having an interval.

Here is the code to implement a simple variable using an interval:

Notice in the CDS syntax it might appear that in the where clause FlightYearMonth = $parameter.p_YearMonth selects only a single value. But in fact, at run-time the variable that is derived from this parameter requires a low and high value. At run-time, the value of field FlightYearMonth must be between the range provided by the variable.

As well as interval, there are other settings that control the behavior of variables:

• @AnalyticsDetails.variable.selectionType:
  • #SINGLE - Single value variable (this is the default selection type)
  • #INTERVAL - Interval variable
  • #RANGE - Select option variable (Including/Excluding, Contains Pattern)
  • #HIERARCHY_NODE - hierarchy node variable
• @AnalyticsDetails.variable.multipleSelections: true - multiple selections are possible
• @AnalyticsDetails.variable.mandatory: false - variable is optional
• @Consumption.hidden: true - variable doesn’t have a prompt at runtime, it must be filled with a default value or by derivation

Parameters are often used in analytical query filters but they can also be used in other places such as:

• WHERE
• CASE WHEN
• in formulas
• in binding parameters of FROM
• in filter of association
• In scalar function (for example, currency_conversion, calendar_operation)
• in selected annotations

Hierarchies that describe business entities can be large and complex. For example, consider an organizational hierarchy where all employees are included. In this case there would be many levels (job positions) and also many members (employees) assigned to each level of the hierarchy. It would be quite challenging to navigate.

Often, a business user is only interested in a section of a large hierarchy. Ideally, the business user should select the start node of the hierarchy at run-time so that the analytical query results would be filtered to the required hierarchy node, its sub-nodes and its members.

We can achieve this by implementing a variable with the selection type HIERARCHY_NODE.

Here is the code to implement this scenario:

1234567891011121314151617181920212223242526272829303132333435363738
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Parameter as hierarchy node'
@ObjectModel: {
  supportedCapabilities: [ #ANALYTICAL_QUERY ],
  modelingPattern: #ANALYTICAL_QUERY
}
define transient view entity /DMO/ANA_C_PAR_HIERNODE
  provider contract analytical_query
  with parameters
    P_HierarchyID : /dmo/ana_airport_hieid,
    @AnalyticsDetails.variable: {
      selectionType: #HIERARCHY_NODE,
      referenceElement: 'DepartureAirportID',
      hierarchyAssociation: '_DepartureAirportHier'
   }    
    P_DepartureAirportHierNode : /dmo/airport_from_id

  as projection on /DMO/ANA_I_FlightCube
  
{  
  _DepartureAirport._Hier( p_hierarchyID: $parameters.P_HierarchyID ) as _DepartureAirportHier,
  @Consumption.hidden: true
  _DepartureAirport.AirportID as dummyDepAirport,
  
  @AnalyticsDetails.query: {
    axis: #ROWS,
    displayHierarchy: #ON,
    hierarchyAssociation: '_DepartureAirportHier'
  }  
  @UI.textArrangement: #TEXT_ONLY  
  DepartureAirportID,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  MaximumSeats,
  @AnalyticsDetails.query.axis: #COLUMNS
  OccupiedSeats
}
where DepartureAirportID = $parameters.P_DepartureAirportHierNode

Watch the video to learn how we implement this code and then execute the query, so that we present the business user with a prompt to choose a hierarchy.