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
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:
No comments:
Post a Comment