Saturday, 5 November 2022

SAP Analytics Clouds Stories with S/4HANA Live CDS – Time Variance

Purpose


This blog explains the typical issues developers have trying enable Time Variance charts in SAP Analytics Cloud (SAC) based on CDS-Views and how to solve this by adding additional fields and annotations to the Cube and Query

Baseline CDS View:


Every CDS which is built to do reporting in SAC, will have at least one date field, and in most dashboards the users want to see a time-based chart for their measures.

Surely it would be great to use the Date-Variance feature of SAC for this – but how?

In order to make it simple and reproducable, lets take a very simple CDS-Cube:

@EndUserText.label: 'Date Cube'
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@ObjectModel.usageType:{
  serviceQuality: #D,
  sizeCategory: #XL,
  dataClass: #MIXED
}
@Analytics: { dataCategory: #CUBE }

define view entity Ztmp_Date_Cube
  as select from I_CalendarDate
{
  CalendarDate,
  @DefaultAggregation: #SUM
  @EndUserText.label: 'Day Counter'
  1 as DayCounter
}

And the corresponding CDS-Query for it:

@EndUserText.label: 'Date Query'
@VDM.viewType: #CONSUMPTION
@Analytics: { query: true }
@ObjectModel: { usageType.sizeCategory: #XXL,
                usageType.dataClass:  #MIXED,
                usageType.serviceQuality: #D }
@Metadata: { allowExtensions:true }
@OData.publish: true

define view entity Ztmp_Date_Query
  as select from Ztmp_Date_Cube
{
  CalendarDate,
  DayCounter
}

When we run this Query in any Analytical Application (RSRT, BICS WD Grid, Fiori Design Studio, Analysis for Office) we can see, that all virtual time hierarchies defined in RSRHIERARCHYVIRT
are visible and can be selected

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

and the results are shown in the choosen hierarchy:

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

and of course also in SAC we can choose the Hierarchy

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

But when we try to add a Time Variance:

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

instead of this option

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

we only see this:

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

even though all other date functionalities are working, including the dynamic time filter:

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

Now one might think this happens, because an annotation on the date field is missing,
so you try to add either of there annotations:

@Semantics.calendar.dayOfYear: true
  
@Semantics.businessDate.at: true
  
@Semantics.systemDate.createdAt: true

but unfortunately none of these will solve the problem.

So the developers try adding all additional hierarchy levels to the Cube itself

.
.
  @Semantics.calendar.dayOfYear: true
  CalendarDate,
  @Semantics.calendar.year: true
  CalendarYear,
  @Semantics.calendar.quarter: true
  CalendarQuarter,
  @Semantics.calendar.month: true
  CalendarMonth,
  @Semantics.calendar.week: true
  CalendarWeek,
  @Semantics.calendar.dayOfMonth: true
  CalendarDay,
  @Semantics.calendar.yearMonth: true
  YearMonth,
  @Semantics.calendar.yearQuarter: true
  YearQuarter,
  @Semantics.calendar.yearWeek: true
  YearWeek,
.
.

which is a good starting point, but SAC still doesn’t know,
that all these fields belong to the Date-Field.

Resolution:

There is one last annotation missing that does exactly that,
even though it is not used in any standard SAP-CDS-View (S/4HANA 2021)
and you’ll find almost no documentation for it, apart of this BI-Wiki

@ObjectModel.value.derivedFrom: ['CalendarDate']

which says

There is no dependency between different time fields by default

So now we can finalize our CDS-Cube:

define view entity Ztmp_Date_Cube
  as select from I_CalendarDate
{
  @Semantics.calendar.dayOfYear: true
  CalendarDate,
  @Semantics.calendar.year: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  CalendarYear,
  @Semantics.calendar.quarter: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  CalendarQuarter,
  @Semantics.calendar.month: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  CalendarMonth,
  @Semantics.calendar.week: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  CalendarWeek,
  @Semantics.calendar.dayOfMonth: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  CalendarDay,
  @Semantics.calendar.yearMonth: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  YearMonth,
  @Semantics.calendar.yearQuarter: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  YearQuarter,
  @Semantics.calendar.yearWeek: true
  @ObjectModel.value.derivedFrom: ['CalendarDate']
  YearWeek,
  @DefaultAggregation: #SUM
  @EndUserText.label: 'Day Counter'
  1 as DayCounter
}

As well as the CDS-Query:

define view entity Ztmp_Date_Query
  as select from Ztmp_Date_Cube
{
  CalendarDate,
  CalendarYear,
  CalendarQuarter,
  CalendarMonth,
  CalendarWeek,
  CalendarDay,
  YearMonth,
  YearQuarter,
  YearWeek,
  DayCounter
}

And back to SAP Analytics Cloud, you’ll see that something changed, and SAC already recommends comparisions:

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

And finally in the variance panel you can choose the date and create a dynamic comparison

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

And our chart is done:

SAP Analytics Clouds, S/4HANA Live CDS, SAP ABAP Career, SAP ABAP Skills, SAP ABAP Jobs, SAP ABAP Tutorial and Materials, SAP ABAP Cloud, SAP ABAP Prep, SAP ABAP Preparation

No comments:

Post a Comment