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: 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 @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: ◈ Display (Key or Text) and axis control (Free, Columns or Rows) of dimensions. ◈ Exception aggregation behavior. ◈ 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 already provide support to ABAP CDS analytical queries, some examples: ◈ SAP BusinessObjects Web Intelligence (WebI) ◈ SAP Analysis for Microsoft Office ◈ SAP Lumira