Creating Your First Analytical Query

For every analytical query, there is one mandatory header-level annotation that is required:

• @AccessControl.authorizationCheck: #NOT_ALLOWEDThe annotation @AccessControl.authorizationCheck is required and the assigned value must always be set to #NOT_ALLOWED. This setting relates to the data which the user is allowed to see and is not related to which business user is allowed to execute the query. Data authorizations are controlled in the cube view that sits below the query. Therefore you need to disable data authorization checks in the analytical query.

The next header-level annotation is optional. If this annotation is missing, will not generate an activation error. However, you should always include this annotation to support the business user by providing a label for the query:

• @EndUserText.label: 'Hello CDS Analytics World!' The annotation @EndUserText.label: provides the label you want to give to your analytical query. The label you provide that will be exposed to the consuming application. Make sure this label is meaningful to the audience who will selecting the analytical query in the application.

There are two header-level annotations that are sometimes included, but again, these are optional. These annotations are used by some consuming applications as additional meta data to identify the purpose of the CDS view:

• @ObjectModel.supportedCapabilities: [#ANALYTICAL_QUERY]
• @ObjectModel.modelingPattern: #ANALYTICAL_QUERY

Following the header-level annotations, we come to the statement that defines the view:

1234
define transient view entity <name_of_view>
  provider contract analytical_query
  as projection on <cube_datasource>

The provider contract setting must have the value analytical query so that the special syntax and annotation related to analytical queries is allowed in the definition.

You then define the measures and dimensions inside curly brackets with optional annotations (which are covered later):

12345678910111213141516
{
// Dimensions
  @AnalyticsDetails.query: {axis: #ROWS, totals: #SHOW}
  AirlineID,

  @AnalyticsDetails.query: {axis: #ROWS, totals: #SHOW}
  ConnectionID,
   
// Measures  
  @AnalyticsDetails.query.axis: #COLUMNS
  MaximumSeats,

  @AnalyticsDetails.query.axis: #COLUMNS
  OccupiedSeats
  }

At the bottom of the analytical query definition we can set global filters that apply to the entire result. You can combine filters for the same dimension usually using OR and filters for different dimensions always using AND.

Here is an example where we use inclusive comparison:

12345
where
      AirlineID  = 'LH'
  OR  AirlineID  = 'BA'
  AND FlightYear = '2024'

Here is an example where we introduce an exclusive (<>) comparison:

12345
where
    (   FlightYear between '2000' and ' 2020'
    OR  FlightYear = '2025' )
AND FlightYear <> '2010'
   

Launch the video to learn how to create a simple analytical query and check the results.