SAP ABAP Central: 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.

SAP ABAP Development, SAP ABAP CDS, SAP ABAP Certifications, SAP ABAP Tutorials and Materials

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:

Fact	Flights Bookings
	Measures	Total number of bookings Total price of bookings Total weight of luggage
Dimension	Date Airline Connection Customer Travel Agency

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