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

[Jakob_Mainka]

Purpose of this blog:

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

and the results are shown in the choosen hierarchy:

and of course also in SAC we can choose the Hierarchy

But when we try to add a Time Variance:

instead of this option

we only see this:

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

 

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:

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

And our chart is done:

 

I hope this blog will help some of you, when creating SAC-Stories based on S/4HANA Live Models

 

Any feedback or comment is highly appreciated, and if you like, please follow me for more information regarding CDS and SAC.