SAP Extensibility 101

Curious case of Foreign Key Association in ABAP CDS

Have you noticed that in SAP standard CDS views, the associations conditions are not quite correct and it may result in wrong output of data?

One example is the association condition for I_ProfitCenter in CDS view I_GLAccountLineItem. As you can see below, there are two association with I_ProfitCenter, one with validity periods considered and one that does not consider it.

If you check which association is used for foreign key association for the field Profit Center, it is the one without considering validty periods!

This is not a good idea because if you know how the profit center master data is setup, profit centers can be maintained with multiple validity periods, such as below. If validity period is not considered in join condition, that means mupltiple records of the same data will be fetched.

Let’s test how this affect the CDS view…

let’s create a CDS view to replicate the I_GLAccountLineItem with I_ProfitCenter association.

define view entity YTEST_ASSOCIATION
  as select from I_GLAccountLineItemRawData

  association [0..*] to I_ProfitCenter as _ProfitCenter on  $projection.ControllingArea = _ProfitCenter.ControllingArea
                                                        and $projection.ProfitCenter    = _ProfitCenter.ProfitCenter
{
  key I_GLAccountLineItemRawData.SourceLedger,
  key I_GLAccountLineItemRawData.CompanyCode,
  key I_GLAccountLineItemRawData.FiscalYear,
  key I_GLAccountLineItemRawData.AccountingDocument,
  key I_GLAccountLineItemRawData.LedgerGLLineItem,

      @ObjectModel.foreignKey.association: '_ControllingArea'
      I_GLAccountLineItemRawData.ControllingArea,

      @ObjectModel.foreignKey.association: '_ProfitCenter'
      I_GLAccountLineItemRawData.ProfitCenter,

      @ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
      I_GLAccountLineItemRawData.CompanyCodeCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
      I_GLAccountLineItemRawData.AmountInCompanyCodeCurrency,


      _ProfitCenter,
      _ControllingArea,
      _CompanyCodeCurrency


}

If you output the data with SE16H and the documents that are posted to ProfitCenter ‘TEST’, it does not seem to have multiple records fetched.

Will this not affect the output even though the join condition is incorrect?

Now by curiousity, let’s bring in actual fields from the I_ProfitCenter assocition.

define view entity YTEST_ASSOCIATION
  as select from I_GLAccountLineItemRawData

  association [0..*] to I_ProfitCenter as _ProfitCenter on  $projection.ControllingArea = _ProfitCenter.ControllingArea
                                                        and $projection.ProfitCenter    = _ProfitCenter.ProfitCenter
{
  key I_GLAccountLineItemRawData.SourceLedger,
  key I_GLAccountLineItemRawData.CompanyCode,
  key I_GLAccountLineItemRawData.FiscalYear,
  key I_GLAccountLineItemRawData.AccountingDocument,
  key I_GLAccountLineItemRawData.LedgerGLLineItem,

      @ObjectModel.foreignKey.association: '_ControllingArea'
      I_GLAccountLineItemRawData.ControllingArea,

      @ObjectModel.foreignKey.association: '_ProfitCenter'
      I_GLAccountLineItemRawData.ProfitCenter,
      
      _ProfitCenter.ValidityStartDate,

      @ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
      I_GLAccountLineItemRawData.CompanyCodeCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
      I_GLAccountLineItemRawData.AmountInCompanyCodeCurrency,


      _ProfitCenter,
      _ControllingArea,
      _CompanyCodeCurrency


}

As expected, it turned out if you actually add any fields from the association, the record will be duplicated. See that the item number is duplicated rersulting in duplicate of amount value.

Why wouldn’t it affect foreign key association?

Foreign key association is used, for example, to bring the hierarchy information. For ProfitCenter, the hierachy information is well defined inside I_ProfitCenter CDS view. By foreign key association, this hierarchy information can be leveraged in the analytical CDS views.(if you build cube and query on top of YTEST_ASSOCIATION).

So as long as the incorrectly joined assiction I_ProfitCenter is used for foreign key assoiaction and no fields are actually explicitly used in the CDS view YTEST_ASSOCIATION, there is no issue on the output(otherwise the standard CDS view I_GLAccountLineItem will have serious issue!).

For more information about how hierarchy is defined and shown in the ABAP CDS based analytical report, check out Custom hierarchy extension in SAP Fiori app based on CDS Query(step-by-step) – SAP Extensibility 101 or Hierarchies | SAP Help Portal