Display Time Hierarchies in SAP Analytics Cloud based on ABAP CDS Views from S/4HANA

Display%20Time%20Hierarchies%20in%20SAP%20Analytics%20Cloud%20based%20on%20ABAP%20CDS%20Views%20from%20S/4HANA%20%28Source%3A%20SAP%29

Display Time Hierarchies in SAP Analytics Cloud based on ABAP CDS Views from S/4HANA (Source: SAP)

Introduction

Time hierarchies are an integral part of modern dashboards. For an effective comparison of financial data, a drill down to different hierarchy levels (such as quarters and months) is needed in charts as well as in filters.

In this blog post, I will outline how custom ABAP CDS views need to be adapted so that a time hierarchy on a date field can be correctly displayed in business intelligence front-end tools such as SAP Analytics Cloud.

The remainder of this post is organized as follows: In the following chapter, the preconditions for the successful implementation of the scenario are presented. In the first step of the procedure, it is presented how the CDS views must be extended in order to show time hierarchies. Then, in the second step, it is presented how virtual time hierarchies are set up in S/4HANA. In the third step of the procedure, it is presented how the time hierarchies can be displayed in a widget in the SAP Analytics Cloud. Finally, a conclusion is drawn.

Prerequisite

In my scenario a S/4HANA system is connected to the SAP Analytics Cloud tenant via a live connection. A detailed guide on connecting a S/4HANA system to the SAP Analytics Cloud can be found here. In order to consume ABAP CDS views in the SAP Analytics Cloud, an ABAP CDS analytical query must be created. For more information, see this detailed blog post by Felipe de Mello Rodrigues. In our use case we have a sample CDS View of the type Cube with one measure (Standard Price) and two dimensions (Material Number, Date of Last Price Change). On top we have a CDS View of type Query. We want to consume the Query view within the SAP Analytics Cloud and use a time hierarchy on top of the dimension “Date of Last Price Change”.

Step 1 – Associate ABAP CDS View with Calendar Date View

In the first step of the procedure, the custom view must be linked to the standard view I_CalendarDate. For this purpose, an association is created and the field CalendarDate from I_CalendarDate is associated in the join condition with the date field of the CDS view for which we want to display the time hierarchy. The data type of the date field must be DATS or transformed to this format.

The CalendarDate field will be included in the CDS Cube View. For this purpose, an annotation of type Semantics must also be set for the date.  The Semantics annotations are listed on this website.

The following is the sample coding of the CDS view of type Analytical Cube “ZSAC_TIMEHIER_CUBE_01“. The time dimension with the time hierarchy is the caldate_last_price_change field. As semantics type for the field caldate_last_price_change the annotation @Semantics.businessDate.at was used. Material_number is another dimension of the view. The field Standard_Price represents a measure.

@AbapCatalog.sqlViewName: 'ZSACTIMEHIERC01'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Test Cube with Time Hierarchy for Blog'
// Analytics Annotations for Cube
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
define view ZSAC_TIMEHIER_CUBE_01 as select from mbew
association [0..1] to I_CalendarDate as _DateLastPriceChange on $projection.date_last_price_change = _DateLastPriceChange.CalendarDate
{ /** Dimensions **/ @EndUserText.label: 'Materialnumber' key matnr as material_number, laepr as date_last_price_change, // Field with Time Hierarchy -- Semantics for Business Date @EndUserText.label: 'Last Price Change Date' @Semantics.businessDate.at: true _DateLastPriceChange.CalendarDate as caldate_last_price_change, /** Measures **/ @EndUserText.label: 'Standard Price' @DefaultAggregation: #SUM stprs as standard_price, /** Associations **/ _DateLastPriceChange }
where $projection.date_last_price_change != '00000000'

On top of the CDS view of type Cube an Analytical QueryZSAC_C_TIMEHIER_01” is set up. This is depicted in the following:

@AbapCatalog.sqlViewName: 'ZSACCTIMEHIER01'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Cons.View of Cube w/ Time Hier. for Blog'
@ClientHandling.algorithm: #SESSION_VARIABLE
// Analytics Annotations for Anal. Query
@Analytics.query: true
@VDM.viewType: #CONSUMPTION
@Analytics.dataExtraction.enabled: true define view ZSAC_C_TIMEHIER_01 as select from ZSAC_TIMEHIER_CUBE_01 { @AnalyticsDetails.query.display: #KEY_TEXT key material_number, @AnalyticsDetails.query.display: #KEY_TEXT key caldate_last_price_change, @AnalyticsDetails.query.axis: #COLUMNS standard_price }

See the Analytical Annotations that are necessary. To use the Query within Business Intelligence Frontends such as SAP Analytics Cloud you have to set the API State of the CDS View to released.

Step 2 – Generate Virtual Time Hierarchy

In S/4HANA, time hierarchies can be created based on different time characteristics (e.g. month, quarter, weekday) depending on the reporting need. Since these hierarchies are virtually generated, they are called Virtual Time Hierarchies. Using the transaction RSRHIERARCHYVIRT you can create and activate your own time hierarchies. Details on how to use the transaction are presented in this blog post.

Figure 1 shows that four time hierarchies are currently activated in the S/4HANA system for our example use case.

Figure%201%3A%20Virtual%20Time%20Hierarchies%20in%20S/4HANA%20%28Source%3A%20Own%20Image%29

Figure 1: Virtual Time Hierarchies in S/4HANA (Source: Own Image)

Step 3 – Create Analytics Cloud Widget with Time Hierarchy enabled

In order to utilize the CDS View in the SAP Analytics Cloud, a model must be created. For this purpose, “Live Data Model” is selected as the model type and the live connection to the S/4HANA system is taken as the connection. In the search, the Analytical Query can be picked (“2C” + <SQL View Name of the Query CDS View>). Figure 2 shows the search for the sample Query CDS view in the SAP Analytics Cloud.

Figure%202%3A%20Model%20Creation%20in%20SAP%20Analytics%20Cloud%20%28Source%3A%20Own%20Image%29

Figure 2: Model Creation in SAP Analytics Cloud (Source: Own Image)

After the data model has been created, a widget including a time hierarchy is created in an SAP Analytics Cloud Story based on the model. For this purpose, a widget is selected (e.g. bar chart) and the dimension prepared for the time hierarchy is selected as the dimension (in our example “Last Price Change Date”). If the settings have been made correctly, you will see a hierarchy icon next to the dimension. Here you can select the time hierarchy for the dimension (see figure 3).

Figure%203%3A%20Set%20Hierarchy%20in%20Widget%20for%20Time%20Dimension%20%28Source%3A%20Own%20Image%29

Figure 3: Set Hierarchy in Widget for Time Dimension (Source: Own Image)

After pressing the “Set Hierarchy” button, you can choose between the available virtual time hierarchies and a flat presentation. Figure 4 shows the four available time hierarchies from chapter 2.

Figure%204%3A%20Select%20between%20active%20Time%20Hierarchies%20%28Source%3A%20Own%20Image%29

Figure 4: Select between active Time Hierarchies (Source: Own Image)

Once a time hierarchy has been selected (e.g. Year-Quarter-Month-Day), it is possible to drill down within the time dimension within the Analytics Cloud widget (see Figure 5). The time hierarchy can also be used in filters.

Figure%205%3A%20Use%20Drill-Down%20in%20SAP%20Analytics%20Cloud%20Widget%20%28Source%3A%20Own%20Image%29

Figure 5: Use Drill-Down in SAP Analytics Cloud Widget (Source: Own Image)

Conclusion

This blog post showed how time hierarchies can be set up in SAP Analytics Cloud Dashboards using self-developed ABAP CDS Views in three simple steps. To achieve this, a CDS View of the analytical type “Cube” has to be associated with a Calendar Date View (I_CalendarDate) and a virtual time hierarchy has to be activated in S/4HANA. Finally, a model must be created in the SAP Analytics Cloud based on the CDS view and a widget with a time dimension generated based on it.

Thanks for reading! I hope you find this post helpful. For any questions or feedback just leave a comment below this post.

I would like to thank Thorsten Wuelpern, whose question within the SAP community inspired me to write this blog post.

Best wishes,

Tim

Find more information and related blog posts on the topic page for SAP Analytics Cloud.

If you have questions about SAP Analytics Cloud you can submit them in the Q&A area for SAP Analytics Cloud in the SAP Community.