Create an analytical model based on ABAP CDS views

SAP HANA combines OLAP and OLTP processing in a single in-memory database, transactional and analytical can live together in the same place and this means you have access to the exact information you need in real time.

With this new approach an interesting question emerges, since we have the transactional and analytical worlds combined in a single database, is there any difference in the development approach when we talk about data models constructed over ABAP CDS (Core Data Services)?

The answer is Yes and in this article I try to clarify these different concepts with a good example of how you should construct an Analytical model based on ABAP CDS views.

Transactional vs. Analytical

One of the biggest advantages of constructing data models with ABAP CDS is that you can design views thinking about both paradigms, depending on your requirements you can apply different development techniques focusing in one of the scenarios.

Analytical models are recommended for reporting purposes when you need to use advantage of aggregations to expose results across different areas (e.g. by time, by location, by responsible). These models are conceived over Facts and Dimensions and these views contain the basic data used to conduct detailed analyses and derive business values.

Imagine as an example a sales report which provides results based on customer, product, date and sales person. The Fact is the sales itself and it holds values that we can measure (e.g. number of sales and total amount of sales), the filters by customer, product, time and sales person are the Dimensions and these dimensions can have Attributes or Texts attached (e.g. customer name, address and product description) and when we connect all of them we have a Cube and consequently an analytical model ready for consumption.

On top of this analytical model we need to construct a Query adapting the data in the way we want to expose to the user. The cube must be constructed in a way it can be reused and consumed by several different Queries, for example, with the sales model above we can generate the following metrics in different queries:

• Sales by year quarter;
• Sales by products with cost above $100;
• Sales by customer located in a specific city;
• Average of sales amount per number of sales;
• Uplift on sales from prior year.

Each query will fulfill an specific purpose and it could be designed for different applications (e.g. Reports, KPIs, etc.).

Important Note: This article won’t focus extensively on Business Intelligence (BI) concepts, if you still have questions about the subject I advise you to perform an extra search with all the topics discussed so far.

• Dimensions
• Facts
• Attributes
• Texts
• Cubes
• Analytical Queries

The real focus of this article is actually to translate those BI concepts to the universe of ABAP CDS views and with this statement in mind we can proceed to our next topic.

Adapting Business Intelligence concepts in ABAP CDS

If you already worked with ABAP CDS in the past you definitely heard about annotations. One of the main purposes of Core Data Services is to allow the creation of semantically rich data models and annotations are the main component to support this task.

There are annotations related with different areas like configuration of UI applications, Enterprise Search, OData service publishing and Analytics. The official documentation and list of all annotations available can be seen in the link below:

• CDS Annotations

Since the focus of this article is to talk about analytic models we’re going to focus in two groups of annotations:

• Analytics and AnalyticsDetails

Analytics provide support to adapt ABAP CDS views and enable multidimensional data consumption taking advantage of data aggregation. AnalyticsDetails support the adaptation of analytical query layout with changes in the aggregation behavior, planning or formulas to calculate metrics.

The process to adapt CDS views is pretty simple, if you want to declare a Dimension, Fact, Aggregation Level or Cube you must include the following annotation in the header of your CDS view:

@Analytics.dataCategory: #VALUE

Replace #VALUE by one of the categories commented before:

• #CUBE
• #AGGREGATIONLEVEL
• #DIMENSION
• #FACT

Pay attention because Cubes must contain at least one measurable attribute, to define a field as a measure you need to place this annotation on the top of the field:

@DefaultAggregation: #SUM

Changes in the default aggregation are possible but the most common scenario is configured with a #SUM aggregation.

When we talk about a Query there is a slight difference in the process because you should include a different annotation in the header of your CDS view:

@Analytics.query: true

Queries must select data from cubes because of the aggregation pattern defined previously, if you try to consume data from a different view the system will return an error during the activation.

Now you should be able to identify the basic steps to create ABAP CDS analytical views but still without any idea of how you should connect all of these different views to create an analytical model. In the next section we still start to practice with a real development.

Creating an analytical model with ABAP CDS

As usual I like to explore SAP flight demo tables in my exercises and for this article I’m going to create a data model on top of the Single Flight Booking table (SBOOK). For the purpose of this demo a simple data model containing a few measures and dimensions is enough to demonstrate the concepts discussed so far. Let’s check the proposed data model in the card below:

There are a lot of options left to explore in SBOOK table but since I don’t want to over complicate the exercise I’m not going to explore its full potential. When you replicate this demo try to play around identifying more fields that could potentially add business value and feel free to attach them into the current data model.

With the analytical model planned we can finally start the development of ABAP CDS views. For the date dimension we don’t need to develop a custom view because the standard view I_CalendarDate provides support with association to all relevant attributes (e.g. year, quarter, month and week) but we need to implement the rest of the dimensions creating custom CDS views. Have a look in the source codes for each one of them below:

DIMENSION: Airline

@AbapCatalog.sqlViewName: 'ZDIMEAIRLINE'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Airline'

@Analytics.dataCategory: #DIMENSION

define view Z_Dimension_Airline
  as select from scarr
{
      @ObjectModel.text.element: [ 'AirlineName' ]
  key carrid   as Airline,
  
      @Semantics.text: true
      carrname as AirlineName,
      
      @Semantics.currencyCode: true
      currcode as Currency
} 

DIMENSION: Connection

@AbapCatalog.sqlViewName: 'ZDIMECONNECT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Connection'

@Analytics.dataCategory: #DIMENSION

@ObjectModel.representativeKey: 'FlightConnection'

define view Z_Dimension_Connection
  as select from spfli
  association [0..1] to Z_Dimension_Airline as _Airline on $projection.Airline = _Airline.Airline
{
      @ObjectModel.foreignKey.association: '_Airline'
  key carrid                    as Airline,

      @ObjectModel.text.element: [ 'Destination' ]
  key connid                    as FlightConnection,

      @Semantics.text: true
      concat(cityfrom,
        concat(' -> ', cityto)) as Destination,

      _Airline
} 

DIMENSION: Customer

@AbapCatalog.sqlViewName: 'ZDIMECUSTOMER'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Customer'

@Analytics.dataCategory: #DIMENSION

define view Z_Dimension_Customer
  as select from scustom
  association [0..1] to I_Country as _Country on $projection.Country = _Country.Country
{
      @ObjectModel.text.element: [ 'CustomerName' ]
  key id      as Customer,

      @Semantics.text: true
      name    as CustomerName,

      @ObjectModel.foreignKey.association: '_Country'
      @Semantics.address.country: true
      country as Country,

      @Semantics.address.city: true
      city    as City,
      
      _Country
} 

DIMENSION: Travel Agency

@AbapCatalog.sqlViewName: 'ZDIMETRVAGENCY'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Travel Agency'

@Analytics.dataCategory: #DIMENSION

define view Z_Dimension_TravelAgency
  as select from stravelag
  association [0..1] to I_Country as _Country on $projection.Country = _Country.Country
{
      @ObjectModel.text.element: [ 'TravelAgencyName' ]
  key agencynum as TravelAgency,

      @Semantics.text: true
      name      as TravelAgencyName,

      @ObjectModel.foreignKey.association: '_Country'
      @Semantics.address.country: true
      country   as Country,

      @Semantics.address.city: true
      city      as City,
      
      _Country
} 

Important Notes:

1. All dimensions must have an @Analytics.dataCategory: #DIMENSION classification in the header of the view.
2. Associations with texts and names are executed through annotation @ObjectModel.text.element.
3. Associations of external attributes are determined by foreign key using annotation @ObjectModel.foreignKey.association.
4. Dimensions with composite keys needs a definition of a single field as a representative key, this configuration is achieved through annotation @ObjectModel.representativeKey.
5. Annotation @Semantics helps to define text and address fields.

With all dimensions prepared we can move on to the development of our cube.

CUBE: Flight Bookings

@AbapCatalog.sqlViewName: 'ZCUBEFLIGHTBOOK'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Bookings'

@Analytics.dataCategory: #CUBE

define view Z_Cube_FlightBookings
  as select from sbook
  association [0..1] to I_CalendarDate           as _CalendarDate on  $projection.FlightDate = _CalendarDate.CalendarDate
  association [0..1] to Z_Dimension_Airline      as _Airline      on  $projection.Airline = _Airline.Airline
  association [0..1] to Z_Dimension_Connection   as _Connection   on  $projection.Airline          = _Connection.Airline
                                                                  and $projection.FlightConnection = _Connection.FlightConnection
  association [0..1] to Z_Dimension_Customer     as _Customer     on  $projection.Customer = _Customer.Customer
  association [0..1] to Z_Dimension_TravelAgency as _TravelAgency on  $projection.TravelAgency = _TravelAgency.TravelAgency
{
  /** DIMENSIONS **/

  @EndUserText.label: 'Airline'
  @ObjectModel.foreignKey.association: '_Airline'
  carrid                 as Airline,

  @EndUserText.label: 'Connection'
  @ObjectModel.foreignKey.association: '_Connection'
  connid                 as FlightConnection,

  @EndUserText.label: 'Flight Date'
  @ObjectModel.foreignKey.association: '_CalendarDate'
  fldate                 as FlightDate,

  @EndUserText.label: 'Book No.'
  bookid                 as BookNumber,

  @EndUserText.label: 'Customer'
  @ObjectModel.foreignKey.association: '_Customer'
  customid               as Customer,

  @EndUserText.label: 'Travel Agency'
  @ObjectModel.foreignKey.association: '_TravelAgency'
  agencynum              as TravelAgency,

  @EndUserText.label: 'Flight Year'
  _CalendarDate.CalendarYear,

  @EndUserText.label: 'Flight Month'
  _CalendarDate.CalendarMonth,

  @EndUserText.label: 'Customer Country'
  @ObjectModel.foreignKey.association: '_CustomerCountry'
  _Customer.Country      as CustomerCountry,

  @EndUserText.label: 'Customer City'
  _Customer.City         as CustomerCity,

  @EndUserText.label: 'Travel Agency Country'
  @ObjectModel.foreignKey.association: '_TravelAgencyCountry'
  _TravelAgency.Country  as TravelAgencyCountry,

  @EndUserText.label: 'Travel Agency Customer City'
  _TravelAgency.City     as TravelAgencyCity,

  /** MEASURES **/

  @EndUserText.label: 'Total of Bookings'
  @DefaultAggregation: #SUM
  1                      as TotalOfBookings,

  @EndUserText.label: 'Weight of Luggage'
  @DefaultAggregation: #SUM
  @Semantics.quantity.unitOfMeasure: 'WeightUOM'
  luggweight             as WeightOfLuggage,

  @EndUserText.label: 'Weight Unit'
  @Semantics.unitOfMeasure: true
  wunit                  as WeightUOM,

  @EndUserText.label: 'Booking Price'
  @DefaultAggregation: #SUM
  @Semantics.amount.currencyCode: 'Currency'
  forcuram               as BookingPrice,

  @EndUserText.label: 'Currency'
  @Semantics.currencyCode: true
  forcurkey              as Currency,

  // Associations
  _Airline,
  _CalendarDate,
  _CalendarDate._CalendarMonth,
  _CalendarDate._CalendarYear,
  _Connection,
  _Customer,
  _Customer._Country     as _CustomerCountry,
  _TravelAgency,
  _TravelAgency._Country as _TravelAgencyCountry
} 

Important Notes:

1. It’s not mandatory to construct a Fact view to consume it from inside of a Cube, you can expose the table who holds the measures directly in the cube to avoid an extra view and consequently an unnecessary level. Based on the documentation, fact views cannot have joins or association and they must hold only measurable values, if you want to connect your dimensions in the same view you should definitely use a cube instead of a fact.
2. All cubes must have an @Analytics.dataCategory: #CUBE classification in the header of the view.
3. Associations of external attributes are determined by foreign key using annotation @ObjectModel.foreignKey.association.
4. @DefaultAggregation annotation should be place before the fields determined as measures.
5. Annotation @Semantics helps to define text, currency, quantity and address fields.
6. All the associations are exposed in the bottom of the view to provide access to Attributes and Texts during the query consumption.

Before we proceed to the query development let’s have a quick look in the transaction RSRTS_ODP_DIS (Transient Provider Preview). This transaction is used to review associations, texts and hierarchies placed inside of the analytical data model providing a detailed analysis for each one of the attributes.

You can use this transaction to review any kind of analytical views but the focus here is to review the cube only. Copy the name defined in the annotation @AbapCatalog.sqlViewName, place it in ODP Name parameter and execute the program.

In the right section of the screen we can see two types of icons, the left one  confirms that we have a valid dimension associated with the attribute (based on a Foreign Key), the right one confirms that we have a text association with this field.

All the attributes were validated (by foreign key and text) and since everything looks fine we can move on to the query development.

QUERY: Flight Bookings

@AbapCatalog.sqlViewName: 'ZQUERYFLIGHTBOOK'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Bookings'

@Analytics.query: true
@VDM.viewType: #CONSUMPTION

define view Z_Query_FlightBookings
  as select from Z_Cube_FlightBookings
{
    /** DIMENSIONS **/
    
    @AnalyticsDetails.query.display: #KEY_TEXT
    @AnalyticsDetails.query.axis: #FREE
    Airline, 
    @AnalyticsDetails.query.display: #KEY_TEXT
    @AnalyticsDetails.query.axis: #FREE
    FlightConnection, 
    @AnalyticsDetails.query.display: #KEY
    @AnalyticsDetails.query.axis: #FREE
    FlightDate, 
    @AnalyticsDetails.query.display: #KEY_TEXT
    @AnalyticsDetails.query.axis: #FREE
    Customer, 
    @AnalyticsDetails.query.display: #KEY_TEXT
    @AnalyticsDetails.query.axis: #FREE
    TravelAgency, 
    @AnalyticsDetails.query.display: #KEY
    @AnalyticsDetails.query.axis: #FREE
    CalendarYear,
    @AnalyticsDetails.query.display: #TEXT
    @AnalyticsDetails.query.axis: #FREE
    CalendarMonth,
    @AnalyticsDetails.query.display: #TEXT
    @AnalyticsDetails.query.axis: #FREE
    CustomerCountry,
    @AnalyticsDetails.query.display: #KEY
    @AnalyticsDetails.query.axis: #FREE
    CustomerCity,
    @AnalyticsDetails.query.display: #TEXT
    @AnalyticsDetails.query.axis: #FREE
    TravelAgencyCountry,
    @AnalyticsDetails.query.display: #KEY
    @AnalyticsDetails.query.axis: #FREE
    TravelAgencyCity,
    @AnalyticsDetails.query.display: #KEY
    @AnalyticsDetails.query.axis: #FREE
    Currency,
    @AnalyticsDetails.query.display: #KEY
    @AnalyticsDetails.query.axis: #FREE
    WeightUOM,
    
    /** MEASURES **/
    
    TotalOfBookings, 
    WeightOfLuggage,
    BookingPrice,
    
    @EndUserText.label: 'Average Weight Per Flight'
    @AnalyticsDetails.exceptionAggregationSteps.exceptionAggregationBehavior: #AVG
    @AnalyticsDetails.exceptionAggregationSteps.exceptionAggregationElements: [ 'Airline', 'FlightConnection', 'FlightDate' ]
    @AnalyticsDetails.query.formula: '$projection.WeightOfLuggage'
    @AnalyticsDetails.query.decimals: 0
    0 as AverageWeightPerFlight
} 

Important Notes:

1. All queries must have an @Analytics.query: true classification in the header of the view.
2. Annotation @AnalyticsDetails is used all over the query providing support for the following tasks:
   1. Display (Key or Text) and axis control (Free, Columns or Rows) of dimensions.
   2. Exception aggregation behavior.
   3. Query formulas.
3. All the three main measures were exposed in the bottom of the query but there is an extra metric based on an average aggregation behavior, the query formula will use the weight of luggage as measure and apply the aggregation to calculate the average during the run-time.

Since there is a calculated field in the query level there is no option to test the aggregation and formula using HANA Studio data preview, to achieve this functionality we need to test our query through a front-end application which supports this task.

Testing the Analytical Query

It is possible to execute tests directly through transaction RSRT (Query Monitor) but I would like to show you an alternative way using View Browser Fiori application available in the standard role SAP_BR_ANALYTICS_SPECIALIST (Analytics Specialist).

Search the query, select the record and click in the button Show Content.

The report screen shows all the 4 metrics (3 measures and 1 formula) and all of the dimensions in the left side available for selection. Check that Booking Price shows * as result, this happens because we have multiple currencies and the system cannot aggregate them without the Currency dimension exposed.

Let’s fix this issue moving Currency from Dimensions to Rows area. This is the expected outcome:

Now we can play with different dimensions checking the results across different areas, check some examples below:

By Airline:

By Customer Country:

By Year:

UI5 and BI front-end applications

Examples of UI5 applications which make use of ABAP CDS analytical queries:

• Smart Business Applications
  • KPI modeller
  • APF (Analysis Path Framework)
• Fiori Elements
  • Overview Pages (Analytical cards)
  • Analytical List Pages
• Custom applications with analytical UI controls
  • Analytical Tables
  • Charts

Most part of the SAP BI front-end applications (from the SAP BusinessObjects BI suite) already provide support to ABAP CDS analytical queries:

• SAP Web Intelligence (WebI)
• SAP Analysis for Microsoft Office
• SAP Lumira

So this is the end! Hope you enjoyed the content. 🙂

In my next post I’m going to move back to the Fiori Elements subject and reuse the data model created in this article to explore how we can work with an Analytical List Page.