Product Information
Overview of S/4HANA Cloud reporting capabilities utilizing custom CDS views and eSAC – Part 1
Purpose
This blog provides an overview of CDS views/ Analytics from the S/4HANA Cloud perspective and is designed to enable Customers and Partners to navigate their first experience designing custom analytical reports using the standard tools available in SAP S/4HANA Cloud, essentials edition. We showcase a simple use case from beginning to end for the perspective of a Key User, which results in the generation of a custom dashboard.
View Browser
The View Browser App, which can be accessed via the Fiori launchpad, is a useful tool SAP provides to search for available CDS views. Utilizing the built-in search bar, key users can search for a variety of views by using categories like Descriptions (ex. Sales order), Application Components (ex. SD), or tables used in building the CDS views (ex. VBAK).
When opening the view browser app, don’t be intimidated by the total number of views that are available to be viewed in the app. I recommend making use of the pre-set view categories “Released Views” which displays all views available for data modelling by key users.
Let’s walk through some of the details of views available to key users in the View Browser App, using the following example. As we work through the needs of the Key User in our example, we’ll also review some additional functionalities of the View Browser App, which, though not directly related to our example, are still important to note.
Our (fictional) Key User is Keith Urban, a Sales Analyst for a major (fictional) US based consumer goods company. Keith’s company wants to gain insight as to the impact his employer’s periodic promotional events have on the company’s. More specifically, Keith has been asked to produce reports showing sales revenue by customer, at the conclusion of each of these promotional events.
To get started, Keith starts searching the View Browser App for available views related to Sales Orders. To further narrow his search, he can utilize some of the artifacts shown in list views, such as View Type and Data Category. The screen capture below illustrates one such search. Keith is searching by keyword “sales order” in the hopes to obtain results that contain pre-delivered analytical views which he can use to structure his reporting.
Types of CDS views
Basic:
A basic view is created on top of DDIC tables/views, which means only a basic view is a view which interacts with database for fetching data. The fetching of data directly from database results as core data for other type of view types without any data redundancies.
Composite:
A composite view is created on top of basic views, which means composite views doesn’t interact with database directly for data, but instead thorough dimension result set of basic views. The multiple basic views can be used with join / association for fetching data and doing calculations (eg:. using built-in-expressions and functions) on retrieved data to consume in different analytics tools using consumption view.
Consumption:
A consumption view is created on top of composite view, which means consumption view doesn’t interact with DDIC tables/views or basic views. The consumption view is written on top of composite view which provides factual and cube data. This view is used to expose the data to different analytics tools (such as HTML, SAP BusinessObjects, Analysis for Excel, SAP Lumira, etc..) which helps to create reports and different visualization for presenting Business data.
Data Category
Dimension:
This value indicates that the entity represents master data. Such a view can be used for replication and for queries.
Fact:
This value indicates that the entity represents transactional data (center of star schema). Usually it contains the measures. Typically, these views are necessary for replication, therefore, they should not be joined with master data views.
Cube:
The #CUBE value (like #FACT) also represents factual data, but #CUBE does not have to be without redundancy. This means joins with master data are possible. Queries are usually built on top of #CUBE, where data is replicated from facts.
Aggregation Level:
This value indicates a projection. For this kind of view, the analytic manager offers write-back functionality (planning functionality). Views in this category have to select from a view with dataCategory = #CUBE, which supports the annotation Analytics.writeBack.className. No associations are allowed, and elements cannot be renamed.
Keith decides to select the Sales Order Cube and clicks on the entry in the list above. This brings him to the Detailed View:
If you have had previous experience with an SAP on-premise system, the contents of this screen capture may be familiar to you. It shows a list of all exposed fields, and the information is presented similarly to how it would be shown in the on-premise transaction SE11. By reviewing this list, a key user can determine if a view contains all the fields they require for reporting. If not all desired fields are available in one CDS view, key users have the option to create Custom CDS views. We will cover this step later in this blog.
Keith found a predelivery CDS view with a data category Cube he is can use to start building his reporting, however he needs to customize the CDS views to ensure that he meets his company’s reporting requirements. Let’s see how Keith works to create this custom CDS view.
Custom CDS view
When creating a new Custom CDS View, Keith must choose one of four possible scenarios:
Analytical Cube: A cube represents a multi-dimensional data model consisting of attributes derived from dimensions and measure. Please click on the following link to review modeling rules to consider when selecting this scenario. https://help.sap.com/viewer/0f69f8fb28ac4bf48d2b57b9637e81fa/2008.500/en-US/fe9b4b4587a84611b94c844c344fb099.html
Analytical Dimension: Dimension consist of a unique set of master data values (attributes) that identify and categorize a data set. They represent the edges of the cube. To derive meaning from the attributes in a cube, a value must be qualified by at least one attribute of each dimension defined in the cube. If a Sales cube has the dimension Product, Sales Number and Time, the value Sales Number (22.500$) can only be interpreted in a meaningful way, if Product (TV) and time (2019) are also qualified.
External API: By choosing this scenario, a custom CDS view will be enabled for external consumption, the view will be exposed as OData API. CDS views created with this scenario cannot be used as part of other custom CDS views or queries. Please refer to the following help page for further details about this scenario. https://help.sap.com/viewer/0f69f8fb28ac4bf48d2b57b9637e81fa/2008.500/en-US/a88a4619-ed2c-4cf7-8db7-99968833b73f.html
Standard CDS View: Choose this scenario if you want to create a new custom CDS view based on a released SAP VDM view or another existing custom CDS view.
He selects create main screen to generate a new custom CDS view. In the pop-up window he enters the name of that new view and selects the scenario as analytical cube, then hits create.
Next, Keith needs to select a primary data source. In this case he’s selected the same data source he identified in the View Browser App, “I_SalesOrderCube.”
The first customization Keith does to his new view is to create an additional parameter. The parameter he creates is a date, to be able to enter the day he wants to start tracking the sales numbers.
He then moves on to select several fields from the original data source as elements to be used in the newly created custom CDS view. Keith also creates 3 calculated fields. These fields are used to define periods (30, 60 and 90 days from the “from date” defined as parameter) and will help him to group all sales made in these periods. Since this new custom CDS view is a analytical cube, he needs to remember that he selects at least one measure from the data source.
Keith used the following formulas to create the calculated fields:
DATS_ADD_DAYS($parameters.P_FromDate,30,’UNCHANGED’)
DATS_ADD_DAYS($parameters.P_FromDate,60,’UNCHANGED’)
DATS_ADD_DAYS($parameters.P_FromDate,90,’UNCHANGED’)
Keith now needs to create a seconds CDS view in which he uses the one he just created as primary data source. This time however, he is adding associated data sources (I_SalesDocument) in order to connect the periods, he previously defined with the sales volume generated in these periods.
He uses the same CDS view (I_SalesDocument) multiple times as an associated data source, but the individual join condition considers only one of the three previously defined periods (30, 60 and 90 days)
30-day join condition
60-day join condition
90-day join condition
When selecting the fields from each data source, he makes sure to edit some of the field labels when adding them to simplify reading the final output.
As a final step, Keith is adding the previously added parameters as filters to this CDS view. This will make sure that only sales orders which were created on or after the “From Date” are selected.
Custom Analytical Query
Once this CDS view is published, he can continue to use his CDS view as data source when creating a query in the app “Custom Analytical Queries”
Keith is selecting all fields from the CDS view to be used in the Query.
His final step when creating the query is to arrange the fields for the query output, before he publishes the newly created query.
Thanks for Sharing!