Comparative Analysis with S/4 HANA Embedded Analytics

Comparative Analysis is a common requirement, for example, Y2Y. The challenge is to model CDS View in such a way that make data selection flexible and easy for both reporting period and reference period.

Below are two examples of comparative analysis in Query Browser. One for QTD and another one for YTD. QTD 2019 and FTD 2019 are compared respectively with QTD 2018 and FTD 2018. What user needs to enter is just rolling period name (Date Function) and CDS view calculates both Seat Occ Rate for selected period and Seats Occ Rate Ref for reference period (selected period offset by either 365 or 366 days).

The same examples of QTD and YTD comparative analysis in Smart Business:

CDS views are modeled in such a way that:

◈ There are two sets of measures one for reporting period and another one for reference period;

◈ Reporting period is selected by Date Function (rolling period) which is converted to date range selection by C_SglGregorianCalDateFunction

◈ Date Function parameter value help is assigned for ease of use;

◈ ZX_CalendarDate View Extension and ZI_CalendarDate Basic View are used to offset reporting period date range by either 365 or 366 days to get reference period date range

Following CDS views need to be created for the demo:

◈ ZI_DateFunction date function value help view

◈ ZX_CalendarDate calendar date extension view

◈ ZI_CalendarDate calendar date basic view

◈ ZSAPBC_Carr carrier basic view

◈ ZSAPBC_Region region basic view

◈ ZSAPBC_CarrText carrier text view

◈ ZSAPBC_CarrDimension carrier dimension view

◈ ZSAPBC_RegionHierarchy region hierarchy view

◈ ZSAPBC_RegionDimension region dimension view

◈ ZSAPBC_FlightFact flight fact table view

◈ ZSAPBC_FlightCube flight cube

◈ ZSAPBC_FlightQuery flight query

ZI_DateFunction Date Function Value Help View

@AbapCatalog.sqlViewName: 'ZIDATEFUNC'

@EndUserText.label: 'Date Function'

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.viewType: #BASIC

define view ZI_DateFunction as select from C_GregorianCalDateFunction( P_Language: $session.system_language )

{

  DateFunction

}

Note: I_CalendarDate view is extended with CalendarYearMinus1 field. This field will be used late on for leap year offset

ZX_CalendarDate Calendar Date View Extension

@AbapCatalog.sqlViewAppendName: 'ZXCALDATE'

@EndUserText.label: 'Date'

extend view I_CalendarDate with ZX_CalendarDate

  association [0..1] to I_CalendarYear as _CalendarYearMinus1 on $projection.CalendarYearMinus1 = _CalendarYearMinus1.CalendarYear 

{

  @ObjectModel.foreignKey.association: '_CalendarYearMinus1'

  case when calendaryear = '0000'  

       then calendaryear

       else cast(cast(cast(calendaryear as abap.int2) - 1 as abap.char( 12 ) ) as abap.numc( 4 ) ) end as CalendarYearMinus1,

  _CalendarYearMinus1 

}

ZI_CalendarDate Calendar Date Basic View

@AbapCatalog.sqlViewName: 'ZICALENDARDATE'

@EndUserText.label: 'Date'

define view ZI_CalendarDate as select from I_CalendarDate 

{

  key CalendarDate,

  case when ( ( _CalendarYear.IsLeapYear = 'X' and CalendarMonth = '02' and CalendarDay = '29' ) or 

              ( _CalendarYear.IsLeapYear = 'X' and CalendarMonth  > '02' ) )

       then dats_add_days( CalendarDate, -366, 'FAIL' ) 

       when ( ( _CalendarYearMinus1.IsLeapYear = 'X' and CalendarMonth = '02' and CalendarDay < '28' ) or

              ( _CalendarYearMinus1.IsLeapYear = 'X' and CalendarMonth = '01' ) )

       then dats_add_days( CalendarDate, -366, 'FAIL' )

       else dats_add_days( CalendarDate, -365, 'FAIL' ) end as CalendarDateMinus1Year,

  CalendarYear,

  CalendarQuarter,

  CalendarMonth,

  CalendarWeek,

  CalendarDay,

  YearMonth,

  YearQuarter,

  YearWeek,

  WeekDay        

}

Note: whenever offset cross February 29 of leap year then it is required to subtract 366 days instead of 365

ZSAPBC_Carr Carrier Basic View

@AbapCatalog.sqlViewName: 'ZCARR'

@AccessControl.authorizationCheck:#NOT_REQUIRED

@VDM.viewType: #BASIC 

@EndUserText.label: 'Airline'

define view ZSAPBC_Carr as select from scarr

{

  carrid as CarrID,   

  url as Url,

  currcode as CurrCode,

  cast(case 

  when carrid = 'LH' or carrid = 'AB' then 'Germany'

  when carrid = 'AA' or carrid = 'CO' or carrid = 'DL' or 

       carrid = 'NW' or carrid = 'WA' then 'US'

  when carrid = 'AC' then 'Canada'  

  when carrid = 'AF' then 'France'  

  when carrid = 'AZ' then 'Italy'

  when carrid = 'BA' then 'UK'

  when carrid = 'FJ' then 'Fiji'

  when carrid = 'NG' then 'Austria'

  when carrid = 'JL' then 'Japan'

  when carrid = 'QF' then 'Australia'

  when carrid = 'SA' then 'South Africa'

  when carrid = 'SQ' then 'Singapure'

  when carrid = 'SR' then 'Swirzerland'  

  when carrid = 'UA' then 'US'

  else 'Other'

  end as abap.char( 13 )) as Region    

}

ZSAPBC_Region Region Basic View

@AbapCatalog.sqlViewName: 'ZREG'

@AccessControl.authorizationCheck:#NOT_REQUIRED 

@EndUserText.label: 'Region'

@VDM.viewType: #BASIC

define view ZSAPBC_Region as select distinct from ZSAPBC_Carr {

  key Region,

  case 

  when Region = 'Germany' or Region = 'France' or Region = 'Italy' or

       Region = 'UK' or Region = 'Austria' or Region = 'Swirzerland' then 'Europe'

  when Region = 'US' or Region = 'Canada' then 'North America'

  

  when Region = 'South Africa' then 'Africa' 

  when Region = 'Fiji' or Region = 'Japan' or Region = 'Singapure' then 'Asia'

  else 'Other' 

  end as MainRegion  

}

where Region <> 'Australia'

union

select distinct from scarr {

key 'Europe' as Region,

    'World' as MainRegion

}

union

select distinct from scarr {

key 'North America' as Region,

    'World' as MainRegion

}

union

select distinct from scarr {

key 'Asia' as Region,

    'World' as MainRegion

}

union

select distinct from scarr {

key 'Australia' as Region,

    'World' as MainRegion

}

union

select distinct from scarr {

key 'Africa' as Region,

    'World' as MainRegion

}

union

select distinct from scarr {

key 'World' as Region,

    '' as MainRegion

}

ZSAPBC_CarrText Carrier Text View

@AbapCatalog.sqlViewName: 'ZCARRTEXT'

@Analytics: {dataCategory:  #TEXT, dataExtraction.enabled: true}

@AccessControl.authorizationCheck: #NOT_ALLOWED

@EndUserText.label: 'Carrier'

define view ZSAPBC_CarrText as select from scarr {

  key carrid as CarrId,

  @Semantics.text: true

  carrname as CarrierName    

}

ZSAPBC_CarrDimension Carrier Dimension View

@AbapCatalog.sqlViewName: 'ZCARRDIM'

@Analytics: {dataCategory: #DIMENSION, dataExtraction.enabled: true} 

@ObjectModel.representativeKey: 'Carrid'

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Carrier'

define view ZSAPBC_CarrDimension as select from ZSAPBC_Carr

 association [0..1] to ZSAPBC_CarrText as _Text on $projection.CarrID = _Text.CarrId

 {

  @ObjectModel.text.association: '_Text' 

  key CarrID,

  Url,

  CurrCode,

  @EndUserText.label: 'Region'

  Region,

  _Text

}

ZSAPBC_RegionHierarchy Region Hierarchy View

@AbapCatalog.sqlViewName: 'ZREGIONHIER'

@Analytics: { dataCategory: #HIERARCHY, dataExtraction.enabled: true }

@ObjectModel.representativeKey: 'Region'

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Region'

@Hierarchy.parentChild.name: 'REGION_GEO'

@Hierarchy.parentChild.label: 'Region Geography'

@Hierarchy.parentChild: 

{ recurse:          {   parent: 'ParentNode',   child:  'HierarchyNode'   } }

define view ZSAPBC_RegionHierarchy as select distinct from ZSAPBC_Region 

 association[0..1] to ZSAPBC_RegionDimension as _Dimension on $projection.HierarchyNode = _Dimension.Region

{

  @ObjectModel.foreignKey.association: '_Dimension'

  key Region as HierarchyNode,

  MainRegion as ParentNode,

  _Dimension

}

ZSAPBC_RegionDimension Region Dimension View

@AbapCatalog.sqlViewName: 'ZREGIONDIM'

@Analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }

@ObjectModel.representativeKey: 'Region'

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Region'

define view ZSAPBC_RegionDimension as select from ZSAPBC_Region

association[0..*] to ZSAPBC_RegionHierarchy as _Hierarchy on $projection.Region = _Hierarchy.HierarchyNode

{

  @ObjectModel.Hierarchy.association: '_Hierarchy' 

  key Region,

  @EndUserText.label: 'Main Region'

  MainRegion,

  _Hierarchy

}

ZSAPBC_FlightFact Flight Fact Table View

@AbapCatalog.sqlViewName: 'ZSFLIGHTFACT'

@AbapCatalog.compiler.compareFilter: true

@Analytics.dataCategory: #FACT

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: 'Flight'

define view ZSAPBC_FlightFact with parameters

    @Consumption.defaultValue: 'CAD' 

    P_DisplayCurrency : s_currcode  

    as select from sflight inner join ZSAPBC_Carr as scarr 

                                   on sflight.carrid = scarr.CarrID {

  key sflight.carrid as CarrId,

  key sflight.connid as ConnId,

  key sflight.fldate as FlightDate,

  scarr.Region as Region,  

  $parameters.P_DisplayCurrency as Currency,

  currency_conversion( 

    amount             => sflight.paymentsum,

    source_currency    => sflight.currency,

    target_currency    => $parameters.P_DisplayCurrency,

    exchange_rate_date => sflight.fldate,

    exchange_rate_type => 'M',

    error_handling     => 'SET_TO_NULL'              // otherwise data inconsistencies cause a dump     

  ) as Payment,

  cast('EA' as abap.unit(3) ) as Unit,

  seatsmax + seatsmax_b + seatsmax_f as SeatsMax,

  seatsocc + seatsocc_b + seatsocc_f as SeatsOcc    

}

ZSAPBC_FlightCube Flight Cube View

@AbapCatalog.sqlViewName: 'ZSFLIGHTCUBE'

@AbapCatalog.compiler.compareFilter: true

@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }

@AccessControl.authorizationCheck:#CHECK 

@EndUserText.label: 'Flight Cube'

define view ZSAPBC_FlightCube with parameters

  P_DisplayCurrency : s_currcode, 

  P_StartDate: dats,

  P_EndDate: dats       

  as select from ZSAPBC_FlightFact( P_DisplayCurrency:  $parameters.P_DisplayCurrency ) as flight inner join I_CalendarDate as Calendar on flight.FlightDate = Calendar.CalendarDate 

    association [1..1] to ZSAPBC_CarrDimension as _Carr on  $projection.CarrId  = _Carr.CarrID

    association [1..1] to ZSAPBC_RegionDimension as _Region on  $projection.Region  = _Region.Region

{

  key 'CURR' as PeriodType,

  @ObjectModel.foreignKey.association: '_Carr'  

  key flight.CarrId,

  key flight.ConnId,

  @EndUserText.label: 'Date'  

  key Calendar.CalendarDate as FlightDate,

  @EndUserText.label: 'Region'

  @ObjectModel.foreignKey.association: '_Region'

  flight.Region,

  @Semantics.calendar.yearWeek: true

  @EndUserText.label: 'Week'  

  Calendar.YearWeek as FlightWeek,  

  @Semantics.calendar.yearMonth: true

  @EndUserText.label: 'Month'  

  Calendar.YearMonth as FlightMonth,

  @Semantics.calendar.year: true

  @EndUserText.label: 'Year'

  Calendar.CalendarYear as  FlightYear,

  @EndUserText.label: 'Currency'  

  Currency,

  @Semantics.unitOfMeasure: true  

  @EndUserText.label: 'UOM'  

  flight.Unit,

  @Semantics.unitOfMeasure: true

  @EndUserText.label: '%'  

  cast( 'Z%' as abap.unit( 3 ) ) as PercentUnitOfMeasure,    

  @Semantics.amount.currencyCode: 'currency'

  @EndUserText.label: 'Payment'

  @DefaultAggregation: #SUM

  flight.Payment,

  @Semantics.quantity.unitOfMeasure: 'unit'

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Max'

  SeatsMax,

  @Semantics.quantity.unitOfMeasure: 'unit' 

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Occ'

  SeatsOcc,

  @Semantics.amount.currencyCode: 'currency'

  @EndUserText.label: 'Payment Ref'

  @DefaultAggregation: #SUM

  cast(0 as s_sum) as payment_ref,

  @Semantics.quantity.unitOfMeasure: 'unit'

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Max Ref'

  cast(0 as s_seatsmax) as SeatsMaxRef,

  @Semantics.quantity.unitOfMeasure: 'unit'

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Occ Ref' 

  cast(0 as s_seatsocc) as SeatsOccRef,

  @Semantics.quantity.unitOfMeasure: 'PercentUnitOfMeasure'

  @DefaultAggregation: #MAX

  @EndUserText.label: '100%'

  100 as HundredPercent, 

  _Carr,  

  _Region

 }

 where flight.FlightDate between $parameters.P_StartDate and $parameters.P_EndDate 

 union all

  select from ZSAPBC_FlightFact( P_DisplayCurrency:  $parameters.P_DisplayCurrency ) as flight

    inner join ZI_CalendarDate as Calendar on flight.FlightDate = Calendar.CalendarDateMinus1Year 

    association [1..1] to ZSAPBC_CarrDimension as _Carr on  $projection.CarrId  = _Carr.CarrID

    association [1..1] to ZSAPBC_RegionDimension as _Region on  $projection.Region  = _Region.Region

{

  key 'PREV' as PeriodType,

  @ObjectModel.foreignKey.association: '_Carr'  

  key flight.CarrId,

  key flight.ConnId,

  @EndUserText.label: 'Date'  

  key Calendar.CalendarDate as FlightDate,

  @EndUserText.label: 'Region'

  @ObjectModel.foreignKey.association: '_Region'

  flight.Region,

  @Semantics.calendar.yearWeek: true

  @EndUserText.label: 'Week'  

  Calendar.YearWeek as FlightWeek,  

  @Semantics.calendar.yearMonth: true

  @EndUserText.label: 'Month'  

  Calendar.YearMonth as FlightMonth,

  @Semantics.calendar.year: true

  @EndUserText.label: 'Year'

  Calendar.CalendarYear as  FlightYear,

  @EndUserText.label: 'Currency'  

  Currency,

  @Semantics.unitOfMeasure: true  

  @EndUserText.label: 'UOM'  

  flight.Unit,

  @Semantics.unitOfMeasure: true

  @EndUserText.label: '%'  

  cast( 'Z%' as abap.unit( 3 ) ) as PercentUnitOfMeasure,

  @Semantics.amount.currencyCode: 'currency'

  @EndUserText.label: 'Payment'

  @DefaultAggregation: #SUM

  cast(0 as s_sum) as payment,

  @Semantics.quantity.unitOfMeasure: 'unit'

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Max'

  cast(0 as s_seatsmax) as SeatsMax,

  @Semantics.quantity.unitOfMeasure: 'unit'

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Occ'

  cast(0 as s_seatsocc) as SeatsOcc,

  @Semantics.amount.currencyCode: 'currency'

  @EndUserText.label: 'Payment Ref'

  @DefaultAggregation: #SUM

  flight.Payment as payment_ref,

  @Semantics.quantity.unitOfMeasure: 'unit'

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Max Ref'

  flight.SeatsMax as SeatsMaxRef,

  @Semantics.quantity.unitOfMeasure: 'unit'

  @DefaultAggregation: #SUM  

  @EndUserText.label: 'Seats Occ Ref'

  flight.SeatsOcc as SeatsOccRef,

  @Semantics.quantity.unitOfMeasure: 'PercentUnitOfMeasure'

  @DefaultAggregation: #MAX

  @EndUserText.label: '100%'

  100 as HundredPercent,

  _Carr,  

  _Region

 }

 where Calendar.CalendarDate between $parameters.P_StartDate and $parameters.P_EndDate 

Note:

◈ Reporting period data is union with reference period data;

◈ Extended I_CalendarDate CDS View is used to offset reporting period date range by 365 days and such a way get reference period date range

ZSAPBC_FlightQuery Flight Query View

@AbapCatalog.sqlViewName: 'ZFLIGHTQUERY'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck:#CHECK

@VDM.viewType: #CONSUMPTION

@Analytics.query: true 

@OData.publish: true 

@EndUserText.label: 'Flight Query'

define view ZSAPBC_FlightQuery with parameters

  @EndUserText.label: 'Display Currency'     

  @Consumption.defaultValue: 'CAD'

  P_DisplayCurrency : s_currcode,

  @EndUserText.label: 'Date Function'                               

  @Consumption: { valueHelp: '_DateFunction.DateFunction', defaultValue: 'YEARTODATE' } 

  P_DateFunction : datefunctionid,

  @Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction', 

  resultElement: 'DateFunctionStartDate', binding: [ {

    targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },

     { targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE'  } ] 

  } 

  @Consumption.hidden: true

  P_StartDate: /srmsmc/puc_spend_valid_from ,

  @Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction', 

  resultElement: 'DateFunctionEndDate', binding: [ {

    targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },

     { targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE'  } ] 

  } 

  @Consumption.hidden: true 

    P_EndDate: /srmsmc/puc_spend_valid_to     

     as select from ZSAPBC_FlightCube( P_DisplayCurrency:  $parameters.P_DisplayCurrency, 

                                       P_StartDate: $parameters.P_StartDate,

                                       P_EndDate: $parameters.P_EndDate ) 

  association[1] to ZI_DateFunction as _DateFunction on _DateFunction.DateFunction <> ''  

{

  Region,

  @AnalyticsDetails.query.axis: #FREE    

  ConnId,          

  @AnalyticsDetails.query.axis: #FREE

  @Consumption.filter: {selectionType: #SINGLE, multipleSelections: true, mandatory: false }

  CarrId,

  @AnalyticsDetails.query.axis: #FREE

  FlightDate,

  @AnalyticsDetails.query.axis: #FREE

  FlightWeek,  

  @AnalyticsDetails.query.axis: #FREE

  FlightMonth,

  @AnalyticsDetails.query.axis: #FREE

  FlightYear,    

  @AnalyticsDetails.query.axis: #FREE

  Currency,

  @AnalyticsDetails.query.axis: #FREE

  Unit,    

  @AnalyticsDetails.query.axis: #FREE

  @AnalyticsDetails.query.hidden: true    

  Payment,    

  @AnalyticsDetails.query.axis: #FREE 

  @AnalyticsDetails.query.hidden: true    

  SeatsMax,

  @AnalyticsDetails.query.axis: #FREE

  @AnalyticsDetails.query.hidden: true    

  SeatsOcc,

  @AnalyticsDetails.query.axis: #FREE

  @AnalyticsDetails.query.hidden: true    

  payment_ref,

  @AnalyticsDetails.query.axis: #FREE

  @AnalyticsDetails.query.hidden: true    

  SeatsMaxRef,

  @AnalyticsDetails.query.axis: #FREE

  @AnalyticsDetails.query.hidden: true    

  SeatsOccRef, 

  @AnalyticsDetails.query.axis: #COLUMNS

  @AnalyticsDetails.query.decimals: 1

  @EndUserText.label: 'Seats Occ Rate'

  @AnalyticsDetails.query.formula: 'NDIV0( NODIM( SeatsOcc ) / NODIM( SeatsMax ) ) * HundredPercent' 

  0 as SeatsOccRate,

  @AnalyticsDetails.query.axis: #COLUMNS

  @AnalyticsDetails.query.decimals: 1

  @EndUserText.label: 'Seats Occ Rate Ref'

  @AnalyticsDetails.query.formula: 'NDIV0( NODIM( SeatsOccRef ) / NODIM( SeatsMaxRef ) ) * HundredPercent'

  0 as SeatsOccRateRef,    

  _DateFunction

}