Analytical List Report with Abap CDS on NW7.5

Introduction

This article is about the presentation and the construction of an analytical list page (ALP) created via Abap CDS views. I wrote this article because I wasn’t able to find  much detailed documentations or examples on this subject. This is all the more true since the base system used here is a version 7.5 of NetWeaver.

So, the important goal here is to show you that you can have a flavor of SAP Hana on a good old “on premise system”… if this one is at least on version 7.5 (7.4 very first version to be precise). Of course, you will have limitations compared to the HANA CDS views.

The analytical list page is a powerful Fiori component. The annotation framework combined with the ABAP CDS view gives the possibility to create awesome reports without almost no javascript line of code, real time analytics, new programming paradigm (code to data: main computations are performed by the data base layer), semantic layer on top of classical data base tables and views, …

The main feature of the report that stands out is…the visual filters! You can select on the chart the data part that you are interested in and the report refresh itself with the specific selected data filtered.

This is how the final report will look like:

Let’s begin… (the implementation)

Presentation of the example

The analytical report presented here will be based on a very basic scenario: a university wants to get an overview of his teachers. The teachers have as characteristics:

◉ a specific degree: associate, bachelor, master, doctoral

◉ a school area: the university is located in Brussels and teachers are working in different areas of the city

◉ the age of the professors is an important factor of analysis, it allows to anticipate the replacement of the professors leaving soon to the retirement

High level data model

We want to build a query that gives an idea about the amount of teachers that have specific attributes…Examples:

◉ the amount of teacher that have a masters’ degree

◉ the amount of teacher that work in the South district

◉ …

◉ => the main measure is then the amount (or “total of”) of teacher

In data warehousing terms (facts/dimensions/measures) we have thus this schema:

Star schema

From abap base…

From a database point of view, the example proposed above will be modeled as this:

Keep in mind this is not a correct implementation but a very simple one for the demo purpose. Two dimensions used here are indeed domain-based and the third one is an HR infotype table.

…to Core Data Services

Let’s develop the different views specified above. The views will be of course useful to query / analyse the measure(s) depending on an attribute of the related dimension but will serve also to get associated text of technical identifier and to provide value list of filters.

CDS view related to dimension “Person”

We need a view that will hold the data from table PA0002 (infotype “Personal data”). I use a little trick to get one row of data (since validity dates are part of the keys): one intermediate view that filter the row with the highest begin date:

CDS view related to dimension “School Area”

This view is specific since we will add specific annotations to generate value list information data but also text 

Improvement : use parameter or environment variable to deal with language field !

CDS view related to the analytical cube

The above query can be more easily parsed through this diagram: