CDS VIEWS: ANALYTICS: A SIMPLIFIED MODEL
We are all well versed in the types of CDS Views used in SAP HANA. However, they are still evolving and adapting to the latest technology provided by HANA. This article focuses on an analytical angle of the CDS Views.
Before understanding the concept of analytics, it is very important to understand the concept of VDM.
What is VDM?
VDM or Virtual Data Model is a combination of semantically enriched CDS Views that logically combine database tables to create a meaningful data set. It is basically built to create a consumption model from any system or an environment.
There are three types of VDM used:
- BASIC - The basic CDS views or entities that extract data from the database tables and forms base of any reports or other CDS Views are classified as BASIC CDS Views.
- COMPOSITE - Views that are made by joining two or more than two Basic CDS Views are known as composite CDS Views. These views also serve aggregation level data.
- CONSUMPTION – The views that are exposed to the outer environment are known as consumption views. These are the topmost views which are a base of applying queries and creating metadata extensions with annotations. These views are also exposed as ODATA Services.
Classification of VDM
CDS View: As an Analytical Model
The world is shifting from transactional data to analytical data. Customers are more interested to view graphics, to view the records filtered on different dimensions. Analytical tools are dominating the market and thus CDS Views have adapted themselves with Analytical Queries and annotations!
Analytical models report the data on the basis of different areas for example Sales report by date, by customer, by person responsible etc. and the same report gets exposed in these different dimensions.
Keywords used in Analytical Model:
FACTS: Facts hold the values we can measure and are extracted based on the filters. They generally represent transactional data.
- DIMENSIONS: These are the measures or the master data like date, customer, and person responsible. This is the key factor to derive our analysis. They are generally the attributes of the master data.
- CUBE: When we connect all the FACT and DIMENSIONS, we come upon CUBES which represent the factual data. This is the view where the analytical queries are built upon.
- ANNOTATION: The Annotation that differentiates an Analytical model in CDS is @Analytics.dataCategory: #VALUE
#VALUE can be replaced by:
In the case of data category as CUBE, we place the annotation @DefaultAggregation: #SUM
QUERY: Queries select the data from a cube on which an aggregation is defined. The annotation @Analytics.query: true should be included in the CDS View to perform a query operation.
Based on the above data model, the below diagram is achieved till the consumption view.
I would like to quote a very simple example of a sales order. A sales order can be analyzed upon various filters like the creation date, material and order types etc.
Thus, if we talk only about sales order, it is a dimension on which we would like to carry out our analysis with attributes like date, material, order type etc. However, sales order when combined with the attribute Quantity becomes a Fact as then the entity of sales order along with its attributes and quantity would represent the transactional data. This forms the base of my analysis. The below CDS entity has thus the annotation @Analytics.dataCategory: #FACT
The fact that I created as an entity, I would like to expose it as a summation of quantity over the measures stated above. I would like to analyze the summation of quantity based on material, creation date and order type. Thus, I would create an aggregation based on the quantity.
The aggregation here is done on one quantity; we can have several aggregations done based on the filters.
Once, the aggregation is done, a CUBE is created on top of aggregation which holds the annotation @Analytics.dataCategory: #CUBE and @DefaultAggregation: #SUM. The annotation @DefaultAggregation can hold any type of aggregation. The analytics query is built on a cube.
A consumption view can be built on top of a Cube, on which Analytical queries can be created. With the annotation @Analytics.query: true, the CDS View becomes a query view and the view can be viewed on a query browser. That is, the analytics queries by the end user are automatically applied with the help of this annotation and no further calculation needs to be done. It also comes with the annotation @AnalyticsDetails.query.axis, this annotation is defined as defaulting an attribute to a row or a column in the query browser.
In the below example, I have extracted my consumption view which is built over a cube on which a query will be applied. With the annotation @Odata.publish: true, I can expose this view as an Odata Service also. Thus my simple analytical mode is ready in which I would be able to view the quantity (measure) of the sales orders based on Material, Created On and Order Type.
Where to analyze the Analytical Model we created?
As mentioned above, Analytical models can be analyzed in Query Browser.
The query browser is an SAP FIORI application that is used to search and browse the analytical queries. It displays the queries the user has access to and can be accessed through FIORI LAUNCHPAD. The role of the Query Browser is SAP_BR_EMPLOYEE. It provides a phenomenal operation of slice and dice in which the end result can be manipulated at runtime with the drag and drops of the dimensions. Let us analyze the same for our above created analytical model.
My analytical query is available in the query browser.
On clicking on the view name, it is directed to the below page in which Open for Analysis is to be clicked.
The below analytical image shows the different dimensions in the left corner that can be used for analyzing the measure Quantity.
The result at the right shows the summation of quantity based on the three dimensions. I can slice and dice the dimensions which will generate a runtime new analytical result.
Thus, in the below image I have dragged the Created_On and Order Type to the left corner and now my result is filtered on the summation of quantity material wise.
The above result can also be viewed with the help of a chart by clicking on Jump to Chart.
Thus, the above example illustrates a very simple approach towards the analytical model of the CDS Views which is of course the future roadmap.
Advantages of CDS with Analytics:
- With the annotation of Analytics query, the effort of viewing the result becomes simplified.
- Without this analytical approach, the developer had to create multiple CDS Views to view the end result.
- The analytical report is far exciting than the traditional ALVs which show only tabular displays with heavy coding.
- The calculations are carried out at the database layer and analytical annotation can be combined with several other powerful CDS annotations.
- The heavy coding of writing a Graph in UI5 is brought down to zero.
The analytical queries become weaker only in those reports in which some fields cannot be brought directly from the CDS and need Function modules or calculations from BAPIs. Thus, the reports that need straight forward calculations and analysis can only be categorized under Analytical Views.
Hope this article helps in replacing our traditional mindset of doing mere calculations on the tabular report with a much wider analytical approach!