Time-dependent attributes and language-dependent texts in ABAP CDS views

In the post post I would like to consider how to model time-dependent attributes and language dependent texts in ABAP CDS views.

1. Sales Model Tables

1.1. Table of sales transactions. It contains time characteristics, sales manager, customer and amount. Number of records is 133 848.

1.2. Sales managers description table contains language dependent texts. Number of records is 9.

1.3. Master data table with time-dependent bonus attribute. The bonus could be changed according to results demonstrated by sales manager. Bonus value is measured in percentage.

2. ABAP CDS views

2.1. Sales data transactional view:

@AbapCatalog.sqlViewName: 'XV_Sales'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Performance test'

@VDM.viewType: #BASIC

define view XV_I_Sales as select from sales002 as t1 {

  

  key t1.salesrep,

  key t1.customer,

  key t1.calyear,

  key t1.calmonth2,

   @DefaultAggregation: #SUM

   t1.amount

}

2.2. Sales managers texts view:

@AbapCatalog.sqlViewName: 'XV_IT_SALESREP'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Salesrep'

@VDM: { viewType: #BASIC }

@ObjectModel: { dataCategory: #TEXT }

define view XVITSALESREP as select from salesrep {

   @ObjectModel.text: {

       element: [ 'text' ]

   }

   key salesrep, 

   @Semantics: {language: true }

   key spras, 

   @Semantics: { text: true }   

   text

}

2.3. Sales managers dimension view with bonus attribute:

@AbapCatalog.sqlViewName: 'XV_IAT_SALESREP'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Time-Dependent Attributes'

@VDM: { viewType: #BASIC }

@Analytics.dataCategory: #DIMENSION

@ObjectModel.representativeKey: 'salesrep'

define view XvIatSalesrep as select from salesrepat

association[0..*] to XVITSALESREP on salesrepat.salesrep = XVITSALESREP.salesrep

 {  

   @ObjectModel: {

       text: {

          association: 'XVITSALESREP'

       }

   }  

    key salesrep,

    @Semantics.businessDate.to: true 

    key dateto,

    @Semantics.businessDate.from: true

    datefrom,

    bonus,

    XVITSALESREP

}

2.4. Sales cube view:

@AbapCatalog.sqlViewName: 'XV_C_Sales'

@AbapCatalog.compiler.compareFilter: true

@Analytics: {

    dataCategory: #CUBE

}

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Performance test consumption'

@VDM.viewType: #COMPOSITE

define view XVCSales as select from XV_I_Sales 

association[0..*] to XvIatSalesrep on XV_I_Sales.salesrep = XvIatSalesrep.salesrep

{

@ObjectModel: { foreignKey: {

    association: 'XvIatSalesrep'

}}

    key salesrep,

    key customer,

    key calyear,

    key calmonth2,

    @DefaultAggregation: #SUM

    amount,

    XvIatSalesrep 

}

2.5. Simple analytical query with sales managers and their bonus in rows and sales values by years in columns:

@AbapCatalog.sqlViewName: 'XV_Q_SalesA'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Performance test QUERY'

@VDM.viewType: #CONSUMPTION

@Analytics.query: true

define view XVQSALESA as select from XVCSales  

 {

  @AnalyticsDetails.query.axis: #ROWS

  @AnalyticsDetails.query.display: #KEY_TEXT

  salesrep,

   

  @AnalyticsDetails.query.axis: #ROWS

  XVCSales.XvIatSalesrep.bonus,

  

  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}

  @AnalyticsDetails.query.axis: #COLUMNS 

  calyear,

  

  @AnalyticsDetails.query.axis: #COLUMNS

  amount

}

3. Launch query in RSRT

3.1. Enter analytical query

3.2. Execute with the following debug options

3.3. Enter variables. Restrict year

3.4. Get generated SQL statements and find in code automatic restrictions by session language and system date.

/* Statement Preview (might be truncated) */

SELECT

 A1~CALYEAR AS K____256

, A1~SALESREP AS K____281

, COUNT( * ) AS Z____151_SUM

, SUM(  A1~AMOUNT  ) AS Z____259_SUM

FROM

XVCSALES AS A1

WHERE

   ( ( ( (

A1~CALYEAR

BETWEEN '2016'

AND '2017'

 ) ) ) )

GROUP BY

A1~CALYEAR

,A1~SALESREP

ORDER BY

 K____256

, K____281

%_HINTS 'NO_RESULT_CACHE'

Automatic filtration by session language:

SELECT SALESREP AS KEY_0001, TEXT AS TXTLG FROM XV_IT_SALESREP WHERE SPRAS = 'E' GROUP BY SALESREP, TEXT

Automatic filtration by system date:

SELECT SALESREP AS KEY_0001, BONUS AS ATR_0002 FROM XV_IAT_SALESREP WHERE DATEFROM <= '20170925' AND DATETO >= '20170925' GROUP BY SALESREP, BONUS

3.5. Result data set:

4. Manual enter of key date

4.1. To manually select key date we could use the following approach in analytical query:

@AbapCatalog.sqlViewName: 'XV_Q_SalesA'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Performance test QUERY'

@VDM.viewType: #CONSUMPTION

@Analytics.query: true

define view XVQSALESA 

  with parameters

    @Consumption.hidden: false

    @Environment.systemField: #SYSTEM_DATE 

    @EndUserText.label: 'Key Date'

    p_keydate: abap.dats

        

  as select from XVCSales  

 {

  @AnalyticsDetails.query.axis: #ROWS

  salesrep,

   

  @AnalyticsDetails.query.axis: #ROWS

  XVCSales.XvIatSalesrep[1:datefrom >= $parameters.p_keydate and dateto <= $parameters.p_keydate].bonus,

  

  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}

  @AnalyticsDetails.query.axis: #COLUMNS 

  calyear,

  

  @AnalyticsDetails.query.axis: #COLUMNS

  amount

}

4.2. Launch query in RSRT and enter variables values:

Notice new key date variable. It was filled by default by system date value, but I changed the value to ‘02.09.2016’. At selected date we have different values of bonus attribute.

4.3. Result data set:

Result

In the post we considered time-dependent master data and language-dependent texts in ABAP CDS views.