Purpose of this blog:
This blog intends to encourage you to enjoy
high productivity of custom object development by using predefined Virtual Data Model (VDM) in S/4HANA by providing the sample of Custom CDS Views based on predefined VDM enhanced with Extend View. By using predefined VDM, you may not have to create custom CDS Views for all entities. It is always the case that predefined content is totally reusable but it is necessary to add some fields, which is possible with
Extend View.
The example Consumption View in this blog is Analytic Query supposed to be used in Fiori Multidimensional Reporting, SAC and BusinessObjects, but predefined VDM and custom CDS View can be used for OData based Fiori app, ABAP program, data source to BW, etc.
But before considering Extend View, please check if the fields can be extended with
Custom Fields and Logic, which could add fields with graphical Fiori app.
The source code in this blog works on S/4HANA 1709.
2020/9/19 added:
It would take long time, e.g. 2 hours, to activate Extend VIew for the CDS View which is used in many other view. In the case, firstly, it is expected to check if extension would be possible with Custom Fields and Logic as explained later. Even if it is not possible, then it might be better to create another new custom CDS view to add the fields. e.g. I have added a custom field in EKKO (Purchase doc header table) and want to add it in the custom CDS view using I_PurchaseOrder as a source. In the case, instead of creating Extend View for I_PurchaseOrder, Join EKKO again in the Custom CDS VIew. In the case, the activation time for the custom CDS view should be much shorter, and in the case, the join of EKKO would not happen twice as internally select statement is optimized. (Self-join-optimization). (Use the same definition for @ClientHandling.algorithm to make it work.).
This blog should help how to deal with long activation time.
2020/9/27
Use
metadata data extension for the VDM which includes @Metadata.allowExtensions:true to change UI, Consumption, EndUserText annotations.
2021/2/6
2023/5/16
By adding the following annotation, activation time can be shortened as checks are skipped, but it is not officially supported. For test purpose, it could be used.
My motivation to write the blog
Predefined VDM is one of the key in S/4HANA arechitecture, although it is not emphasized as is the object underneath, but in my understanding, not so many customers enjoy it. I think one of the main reason should be that how to use Predefined VDM might not be clear for them, and some would think it be useless as they have custom fields in the table or want to add the field from other table but it would be impossible to add field to Predefined VDM. To overcome those concerns, I provide the example code to use Predefined VDM including Extend View with which fields can be added to standard Predefined VDM.
Target audience:
This blog is for the ones who want to create report and analytical apps. It is
not only for analytics experts but for all functional experts as it is expected to create report based on CDS View, instead of traditional functions like ALV or Report Painter, which has been used by functional experts. See SAP Note
2579584 and
2349297.
It is also for
developers as CDS View can be used not only for analytical apps, but it can be used in normal ABAP program.
Summary:
- Concrete samples of the predefined VDMs are explained to understand what exactly the predefined VDM is.
- Samples of custom CDS View using predefined VDMs and Extend View to add fields to predefined VDM are provided, as the best way to understand is to see the source. The predefined VDM of Billing Document Item is associated to Sales Order Item in the sample.
- In the sample Extend View, a CDS Wiew is associated, and a field is added from the view.
- Considerations to use Extend View and limitations are included.
- In the sample of Consumption View, some advanced settings are included (hoping it would help creating sophisticated Analytic Query, although it is not the main topic of this blog).
- Add value help to a parameter using association.
- Set start/end date as consumption filtering value automatically using lookup entry used in Date Function.
- Exception aggregations are used to avoid value duplication when joining sales order and billing document.
- How to deal with “Not released” predefined VDM is argued. “Not released” VDM is subject to change after version up of the system. But in my personal view, it should be better to use it than creating custom CDS Views for all business entities.
Predefined VDM and Extend View:
Predefined VDM:
Predefined VDM is the predefined CDS Views for business entities or for applications in S/4HANA. There are mainly 3 types of predefined VDM in S/4HANA.
- Consumption View: The view consumed by application (Tech name: C_*). e.g. Journal Entry Analyzer (C_GLLINEITEMSQ0001). It is created from Interface View in general.
- Interface View: The view is the foundation of Consumption View (Tech name: I_*). Interface View for business entities in S/4HANA is called Basic View, e.g. GL Account Line Item (I_GLAccountLineItem), Profit Center (I_ProfitCenter). Interface view created from Basic View is called Composite View, e.g. G/L Account Balance Cube (I_GLAcctBalanceCube).
- Private View: The view used as a part to create Interface View or Consumption View (Tech name: P_*). It is not mandatory to create.
See the document linked in
this blog in detail about CDS View and VDM.
(In BW context, Consumption View is like BW Query and Interface View is like InfoProvider or InfoObject.)
You can find the predefined VDMs in S/4HANA with
Fiori View Browser.
Example of predefined VDM:
- Transaction Data View: Sales Order
- Consumption View: C_SalesOrderItemQry (Analytic Query – executable as BW Query)
- Interface View:I_SalesOrderItemCube
- Interface View: I_SalesDocumentItemAnalytics
- Interface View: I_SalesDocumentItem (Use table VBAK, VBAP, VBKD, VEDA as sources)
- Master Data View:
- Interface View (Simple): I_CompanyCode
- Interface View (Complex): I_ProfitCenter
List of main predefined View:
Component |
Status |
CDS View |
Text |
SD |
Released |
I_SALESORDERITEMCUBE |
Analytics - Incoming Sales Order Cube |
SD |
Released |
I_BILLINGDOCUMENTITEMCUBE |
Analytics - Sales Volume Cube |
MM |
Unreleased |
I_PURCHASEORDERITEM |
Purchase Order Item |
MM |
Unreleased |
C_PURDOCLISTINVOICEDETAILS |
Invoice Details |
MM |
Unreleased |
I_PURCHASEREQUISITIONITEM |
Purchase Requisition Item |
MM |
Released |
I_MATERIALSTOCKTIMESERIES |
Material stock for periods |
MM |
Released |
I_GOODSMOVEMENTCUBE |
Analytical Cube for Goods Movements |
FI |
Released |
I_GLACCTBALANCECUBE |
G/L Account Balance Cube |
FI |
Released |
I_GLACCOUNTLINEITEMCUBE |
G/L Line Items Cube |
FI |
Released |
I_PROFITCENTER |
Profit Center |
FI |
Released |
I_COSTCENTER |
Cost Center |
FI |
Released |
I_GLACCOUNTINCHARTOFACCOUNTS |
G/L Account In Chart Of Accounts |
LO |
Released |
I_CUSTOMER |
Customer |
LO |
Released |
I_SUPPLIER |
Supplier |
MM |
Released |
I_PLANT |
Plant |
MM |
Released |
I_STORAGELOCATION |
Storage Location |
MM |
Unreleased |
I_MATERIAL |
Material |
BC |
Released |
I_CURRENCY |
Currency |
BC |
Released |
I_UNITOFMEASURE |
Unit of Measure |
CA |
Unreleased |
I_CALENDARDATE |
Date |
FI |
Released |
I_FISCALCALENDARDATE |
Fiscal Calendar Date |
- Release statuses is at S/4HANA 1709 FPS00.
Extend View:
Extend View is used to add fields to predefined VDM without changing predefined VDM itself. It is possible to associate the view and add it in the source view, and it is also possible to use the association for foreign key association on the added field. It is like APPEND for table in ABAP Dictionary. See
SAP Help ,
Wiki or
blog in detail.
Consideration to use Extend View:
Enhancement by Extend View is in a sense like modification, so would cause the following issues when upgrading the system.
- Conflict of the field name: conflict of the field name would happen if new fields are added on the predefined VDM in the newer version but the field names has already been used in the Extend View, e.g. Segment is added with Extend View to the source predefined VDM, but in the newer version of the predefined VDM, Segment is added by default. To avoid it, it is recommended to rename the fields added with Extend View with ZZ~.
- Field name change of the source view: fields in the source (select ~ from <Source>) can be added using Extend View. But if the source view is unreleased (see below), the field name might be changed (or the filed would be deleted) in the newer version. In this case, it cannot be avoided to adjust the Extend View and relevant CDS Views.
For those reasons,
it is recommended to add fields using Key User Extensibility Tools “Custom Fields and Logic” to add fields as much as possible.
Other limitations in Extend View:
- [ min .. *] cannot be used in Extend View. See the blog for [min..*] association.
- The field of the association cannot be added when path expression is used in ON condition of the association, _FiscalYearCalenderDate.FiscalYear cannot be added when _FiscalYearCalenderDate is associated in the Extend View and SDIA._ControllingArea.FiscalYearVariant is used in the ON condition.
- It is not possible to set annotation only to existing field.
- For doing that ANNOTATE VIEW is used. But it is available only for the predefined VDM in which Metadata.allowExtensions is set to true.
Example of Custom CDS View using Predefined VDM and Extend View
There are some ways to use predefined VDM as below.
In the example of this blog, Interface View of predefined VDM I_SalesOrderItemCube is enhanced by Extend View, and Another Custom CDS View is created from it, and Consumption View is created on top.
- The Interface View I_SalesOrderItemCube is enhanced by Extend View ZE_SalesOrderItemCube to add FiscalYearVariant from I_ControllingArea associated in I_SalesOrderItemCube.
- Custom Interface View is created on top of predefind VDM and I_ProfitCenter, I_BillingDocumentItemBasic and I_FiscalCalendarDate are associated in it, and fields and associations are added from those views.
- Consumption View ZC_Sales is created from I_SalesOrderItemCube.
Extend View: ZE_SalesOrderItemCube
@AbapCatalog.sqlViewAppendName: 'ZESSOITMCB'
@EndUserText.label: 'Extend SalesOrderItemCube'
extend view I_SalesOrderItemCube with ZE_SalesOrderItemCube
association [0..1] to I_FiscalYearVariant as _FiscalYearVariant
on $projection.ZZFiscalYearVariant = _FiscalYearVariant.FiscalYearVariant
{
@ObjectModel.foreignKey.association: '_FiscalYearVariant'
SDIA._ControllingArea.FiscalYearVariant as ZZFiscalYearVariant,
--expose
_FiscalYearVariant
} |
Detail:
- Add fields from associated Views: FiscalYearVariant is added from I_ControllingArea associated in the source I_SalesOrderItemCube, and master view I_FiscalYearVariant is associated and it is set as the foreign key association on the added field FiscalYearVariant.
- Renamed as ZZ~: All fields added in the Extend View are renamed to ZZ~. it is to avoid the possible conflict of the name when upgrading the system. Now I_SalesOrderItemCube doesn’t have the field FiscalYearVariant so it is possible to add the field named “FiscalYearVariant” with Extend View. However, if the field FiscalYearVariant is added in the next version of I_SalesOrderItemCube, the conflict would happen.
- Field and foreign key association added from associated View: The field Segment is added from I_ProfitCenter, and _Segment (_ProfitCenter. _ProfitCenter) is also added from I_ProfitCenter, and is used in the foreign key association on the field. Although I_Segment has already been used in the foreign key association in I_ProfitCenter, foreign key association setting is necessary again in the Extend View (Transaction Interface View).
- Not possible to use Path Expression for ON condition for the association: I tried to associate _FiscalCalendarDate.FiscalYear but found it is not possible. It is because _ControllingArea. FiscalYearVariant cannot be used for ON condition for association to add fields from the associated view, although it is possible to add association only, e.g. _FiscalCalendarDate.FiscalYear cannot be added, but _FiscalCalendarDate can be added. For doing that, the only way is to add only association in Extend View and add the field from the associated view in custom CDS View created from the source view extended by Extend View.
Interface View: ZI_SALESBILL:
@AbapCatalog.sqlViewName: 'ZISALESBILL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'SalesOrder+BillingDoc'
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
define view ZI_SALESBILL
with parameters
P_ExchangeRateType : kurst,
P_DisplayCurrency : vdm_v_display_currency
as select from I_SalesOrderItemCube
( P_ExchangeRateType: $parameters.P_ExchangeRateType, P_DisplayCurrency: $parameters.P_DisplayCurrency )
association [0..1] to I_ProfitCenter as _ProfitCenter
on $projection.ControllingArea = _ProfitCenter.ControllingArea
and $projection.ProfitCenter = _ProfitCenter.ProfitCenter
association [0..*] to I_BillingDocumentItemBasic as _BillingDocumentItemBasic
on $projection.SalesOrder = _BillingDocumentItemBasic.SalesDocument
and $projection.SalesOrderItem = _BillingDocumentItemBasic.SalesDocumentItem
association [0..1] to I_FiscalCalendarDate as _FiscalCalendarDate
on $projection.ZZFiscalYearVariant = _FiscalCalendarDate.FiscalYearVariant
and $projection.SalesOrderDate = _FiscalCalendarDate.CalendarDate
{
SalesOrder,
SalesOrderItem,
SalesOrderType,
SalesOrderDate,
SalesOrganization,
DistributionChannel,
OrganizationDivision,
ControllingArea,
SalesOrderDateYearMonth,
DisplayCurrency,
@ObjectModel.foreignKey.association: '_ProfitCenter'
ProfitCenter,
ZZFiscalYearVariant,
NetAmountInDisplayCurrency,
@ObjectModel.foreignKey.association: '_Segment'
_ProfitCenter.Segment,
_BillingDocumentItemBasic.BillingDocument,
_BillingDocumentItemBasic.BillingDocumentItem,
_FiscalCalendarDate.FiscalYear as ZZFiscalYear,
_FiscalCalendarDate.FiscalPeriod as ZZFiscalPeriod,
_FiscalCalendarDate.FiscalYearQuarter,
/* Associations */
//I_SalesOrderItemCube
_ControllingArea,
_DistributionChannel,
_FiscalYearVariant,
_OrganizationDivision,
_SalesOrder,
_SalesOrderType,
_SalesOrganization,
_ScheduleLine,
_ProfitCenter,
_ProfitCenter._Segment
} |
Detail:
- Add fields from associated Views: In this Interface View, I_ProfitCenter are associated and Segment are added from it, and this view is set as Foreign Key association on ProfitCenter. BillingDocument and BillingDocumentItem are added from associated I_BillingDocumentItemBasic, and FiscalYear, FiscalPeriod, and FiscalQuarter are added from associated I_FiscalCalendarDate.
Most of them could be added with Extend View and it might be possible to go without creating custom Interface View. However, it is created in this sample as it might be better to avoid using Extend View too much because of the considerations and limitations as mentioned above.
Consumption View: ZC_Sales
@AbapCatalog.sqlViewName: 'ZCSALES'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Sample Sales'
@Analytics.query: true
define view ZC_Sales
with parameters
@AnalyticsDetails.query.variableSequence: 20
@Consumption.defaultValue: 'EUR'
P_DisplayCurrency : vdm_v_display_currency,
@AnalyticsDetails.query.variableSequence: 30
@Consumption: { valueHelp: '_ExchangeRateType.ExchangeRateType', defaultValue: 'M'}
P_ExchangeRateType : kurst,
@Consumption.hidden: true
@Environment.systemField: #SYSTEM_DATE
P_KeyDate : sydate
as select from ZI_SALESBILL
( P_ExchangeRateType: $parameters.P_ExchangeRateType, P_DisplayCurrency: $parameters.P_DisplayCurrency )
association [1] to I_ExchangeRateType as _ExchangeRateType on _ExchangeRateType.ExchangeRateType <> ''
{
SalesOrder,
SalesOrderItem,
@AnalyticsDetails.query.totals: #SHOW
SalesOrderType,
@AnalyticsDetails.query.variableSequence : 10
@Consumption: {
filter: {selectionType: #INTERVAL},
derivation: {
lookupEntity: 'C_SglGregorianCalDateFunction',
resultElement: 'DateFunctionStartDate', binding: [
{ targetParameter: 'P_DateFunction', type: #CONSTANT, value : 'YEARTODATE' },
{ targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE' } ],
resultElementHigh: 'DateFunctionEndDate' }
}
SalesOrderDate,
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.display: #TEXT
@AnalyticsDetails.query.totals: #SHOW
SalesOrganization,
@AnalyticsDetails.query.display: #TEXT
@AnalyticsDetails.query.totals: #SHOW
DistributionChannel,
@AnalyticsDetails.query.display: #TEXT
@AnalyticsDetails.query.totals: #SHOW
OrganizationDivision,
@AnalyticsDetails.query.display: #TEXT
@AnalyticsDetails.query.totals: #SHOW
ControllingArea,
@EndUserText.label: 'Calender YearMonth'
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.totals: #SHOW
SalesOrderDateYearMonth,
DisplayCurrency,
ZZFiscalYearVariant,
BillingDocument,
@EndUserText.label: 'Billing Document Item'
BillingDocumentItem,
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.display: #TEXT
@AnalyticsDetails.query.totals: #SHOW
ProfitCenter,
@AnalyticsDetails.query.display: #TEXT
@AnalyticsDetails.query.totals: #SHOW
Segment,
ZZFiscalYear,
ZZFiscalPeriod,
FiscalYearQuarter,
@Consumption.hidden: true
@DefaultAggregation: #FORMULA
@AnalyticsDetails: {
exceptionAggregationSteps: [{ exceptionAggregationBehavior : #MAX,
exceptionAggregationElements: ['BillingDocument','BillingDocumentItem']
}]
}
@AnalyticsDetails.query.formula : 'NetAmountInDisplayCurrency'
1 as SalesOrderAmtTmp,
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: 'SalesOdrAmt_D'
@DefaultAggregation: #FORMULA
@AnalyticsDetails: {
exceptionAggregationSteps: [{ exceptionAggregationBehavior : #SUM,
exceptionAggregationElements: ['SalesOrder','SalesOrderItem']
}],query.decimals: 0
}
@AnalyticsDetails.query.formula : '$projection.SalesOrderAmtTmp'
1 as SalesOrderAmt
} |
Results of the Consumption View:
Detail:
- Analytic Query: This Consumption View is Analytic Query, which works as the source of Fiori Multidimensional Reporting. It can be used with SAC and BusinessObjects.
- Value help for the parameter: I_ExchangeRateType is associated practically without on condition (dummy association) to use it for the value help for the parameter P_ExchangeRateType. See the blog in detail.
- Derive start and end date automatically using lookup entry for Date Function: The start and end date are derived automatically from C_SglGregorianCalDateFunction as Lookup entry. C_SglGregorianCalDateFunction is the CDS View used in Date Function. As YEARTODATE is set as Date Function parameter Value, when running the view on 8/19/2018, 1/1/2018 is derived as start date and 8/19/2018 as end date.
- Exception Aggregation to avoid value duplicate value: The record of I_SalesOrderItemCube would be duplicated when JOIN happens as relationship between I_SalesOrderItemCube and I_BillingDocumentItemBasic is 1 : N. To avoid it, 2 calculated measures including Exception Aggregation are created (SalesOrderAmtTmp and SalesOrderAmt). In SalesOrderAmtTmp, ZBillingDocument and ZBillingDocumentItem are used as Exception Aggregation Element, and Aggregation is set to MAX, so the aggregation of ZBillingDocument and ZBillingDocumentItem is MAX, not SUM. In SalesOrderAmt, ZSalesOrder and ZSalesOrderItem are used as Exception Aggregation Element, and Aggregation is set to SUM, SalesOrderAmtTmp is aggregated with SUM. (For this reason, [0..*] in the association to I_BillingDocumentItemBasic in Interface View (Extend View) is no problem in this case).
When drilled down with Billing Document, SalesOdrAmt_D for Sales Order 388 is displayed twice.
Even when Billing Document is removed and records are aggregated, SalesOdrAmt_D for Sales Order 388 is displayed as expected without duplication.
As high granular attributes ZBillingDocument, ZBillingDocumentItem, and ZSalesOrder, ZSalesOrderItem are used in Exception Aggregations, they have to be included in the select list of internal SELECT statement. It would lead to longer runtime as larger amount of data would have to be retrieved.
“Not released” predefined VDM:
Predefined VDM has the status of validity (Released/Not Released). In the unreleased predefined VDM, the design is subject to change in the future release and current design is not guaranteed by SAP, e.g. field name and the keys would be changed, fields would be removed, the view would be removed. It is like unreleased Function Module. In addition, Only the released CDS Views can be used for Key User Extensibility Tools Custom CDS View and Custom Analytical Queries.
See
the blog in detail.
Hope you enjoy high productivity by using predefined VDM and Extend View!