Purpose 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 sample 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. 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. 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 as the foundation (Tech name: I_*).. The view as the general foundation for Consumption view. 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. (In BW context, Consumption View is like BW Query and Interface View is like InfoProvider or InfoObject.) 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. 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 ) 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. Sample of Custom CDS View using Predefined VDM and Extend View In the sample 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. ◈ 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. It is possible to check the status in Fiori View Browser or ABAP Development Tools(ADT). View Browser: ADT: After opening the predefined VDM, go Menu: Window > Show View > Properties, and select API Status. It is possible to set to be released. From the context menu of the CDS View in Project Explorer tree, select “Change API State..”. By checking the “Released” flag, it is released. After released, the CDS View can be used with Custom CDS View and Custom Analytical Queries. But it should be avoided to release predefined VDM as is regarded as modification. How to deal with the unreleased predefined VDM? At first, it would help to create Customer Incident to request SAP to release the predefined VDMs you want to use. For the predefined VDMs which are still not released, there are 2 ways to deal with the unreleased predefined VDMs: 1) not to use them but create custom CDS View instead, 2) use it at customers’ own responsibility. I discussed this topic with some developers, colleagues in service team and have following personal observations (not official announcement as SAP). ◈ 2) Use it as customers’ own risk is better. In option 1, efforts have to be paid to create custom CDS Views, but in option 2, efforts MIGHT have to be paid only for the predefined views which are changed in newer version when upgrading the system. Possibility to pay effort to some VDMs should be better than mandatory to pay effort for all views. ◈ In the experience of customers who used unreleased VDMs for custom CDS Views, they had to pay effort to adjust the model when upgrading S/4HANA to 1610 from 1511, but it was not so big effort. ◈ Interface View could be used, but better to avoid using Private View as much as possible. It is reasonably expected that Interface View, especially Basic View, should not be changed so drastically. I_Material is not released. But it is based on MARA and MAKT, and it is reasonably expected that MARA and MAKT should not be changed in the future release, so the predefined VDM based on it should not be changed so drastically. Private View is only a part to create Interface View, but internal design is subject to change in the newer version, and in fact some Private Views are changed drastically in the newer version. I think Consumption View is expected to be changed more often than Interface View, but not more often than Private view as it is application specific view. But again, it is personal opinion, not official statement.