S/4 Embedded Analytics - The Virtual Data Model

[celo_berger]

In this post, I will discuss the architecture of building a virtual data model (VDM) in S/4 HANA, using CDS Views (Core Data Services).

With the availability of the SAP HANA platform there has been a paradigm shift in the way business applications are developed at SAP. The rule-of-thumb is: Do as much as you can in the database to get the best performance.

CDS Views

To take advantage of SAP HANA for application development, SAP introduced a new data modeling infrastructure known as core data services. With CDS, data models are defined and consumed on the database rather than on the application server. CDS also offers capabilities beyond the traditional data modeling tools, including support for conceptual modeling and relationship definitions, built-in functions, and extensions

 



Technically, CDS is an enhancement of SQL which provides a Data Definition Language (DDL) for defining semantically rich database tables/views (CDS entities) and user-defined types in the database. Some of the enhancements are:

• Expressions used for calculations and queries in the data model


• Associations on a conceptual level, replacing joins with simple path expressions in queries


• Annotations to enrich the data models with additional (domain specific) metadata

 

Supported natively in both ABAP and SAP HANA, the data models are expressed in data definition language (DDL) and are defined as CDS views, which can be used in ABAP programs via Open SQL statements to enable access to the database. CDS provides a range of advantages for businesses and developers, including:

• Semantically rich data models
  CDS builds on the well-known entity relationship model and is declarative in nature, very close to conceptual thinking.


• Compatibility across any database platform
  CDS is generated into managed Open SQL views and is natively integrated into the SAP HANA layer. These views based on Open SQL are supported by all major database vendors


• Efficiency
  CDS offers a variety of highly efficient built-in functions — such as SQL operators, aggregations, and expressions — for creating views.


• Support for annotations
  The CDS syntax supports domain-specific annotations that can be easily evaluated by other components, such as the UI, analytics, and OData services.


• Support for conceptual associations
  CDS helps you define associations that serve as relationships between different views. Path expressions can be used to navigate along relations. Introducing an abstraction of foreign key relationships and joins, associations make navigation between entities consumable


• Extensibility.
  Customers can extend SAP-defined CDS views with fields that will be automatically added to the CDS view along with its usage hierarchy.

 

CDS Views for Embedded Analytics

Before HANA, querying large datasets in an ERP system could be time consuming, and degrade overall performance. Data Warehouses were used to create persisted data models using advanced modelling techniques to improve query performance. SAP HANA removes the performance issue in ERP out of the equation, allowing us to create Virtual Data Models (VDM) directly in ERP with incredible performance.

What is a VDM? A combination of semantically enriched CDS views that logically combine data from source ERP tables to create meaningful datasets that can be readily consumed in frontend tools



 

As the slide suggests, at a high level, the VDM consists of CDS Views reading data from tables in the DB, which are then read by other CDS views, without any persistency, happening in real time.

Annotations

Annotations help qualify the CDS Views, and provides semantics and meaning to fields within a CDS View

• They can be applied to the entire CDS View entity;


• They can be used to specify semantics to fields in the SELECT list;


• Are always preceded by the @ symbol.

Below is SAP's list of annotations:

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abencds_annotations_sap.htm

It can also be reached by following the path: ABAP - Keyword Documentation > ABAP - Dictionary > ABAP CDS in ABAP Dictionary > ABAP CDS - Syntax > ABAP CDS - Annotations

Below are a few of the key annotations that define entire CDS Views specific to VDMs:

Annotation	Description
VDM.viewType	Defines the type of a VDM view
Analytics.dataCategory	Analytic queries can be defined on top of CDS views.  By specifying the data category, the developer can provide directives and hints, telling the analytic manager how to interpret individual entities for example
Analytics.dataExtraction.enabled	Application developers can use this annotation to mark views that are suitable for data replication (for example, delta capabilities must be provided for mass data)
Analytics.query	By tagging the CDS view, the developer can specify which views will be exposed to the analytic manager. This type of view will be interpreted as an analytic query by the analytic manager.
ObjectModel.dataCategory	Defines the category of data (#TEXT or #HIERARCHY)
ObjectModel.representativekey	Most specific element (field or managed association) of the primary key (indicated by the keyword KEY) that represents the entity which the view is based on
AccessControl.authorizationCheck	Enables row level authorization on a specific CDS view

 

Because I'm also a BW developer, I've taken some of the most important annotations for VDMs and compared them to BW objects:

Annotation: @VDM.viewType

Value	Description	BW Equivalent
#BASIC	Views that form the core data basis without data redundancies. This is your basic SELECT from a physical table in the database	This would be equivalent to an ADSO, where your raw data is present, with some ETL
#COMPOSITE	Views that provide data derived and/or composed from the BASIC views.	This would be equivalent to a Composite Provider, which is the virtual layer which allows for joins and unions
#CONSUMPTION	Views that serve for specific application purposes and may be defined based upon public interface (for example, BASIC and COMPOSITE) views.	This would be equivalent to a BW Query, where we specify a particular layout, variables, RKFs and CKFs, totals, etc.

Annotation: @analytics.dataCategory

Value	Description	BW Equivalent
#DIMENSION	Views representing master data should be annotated with ObjectModel.dataCategory: #DIMENSION	Equivalent to Infoobject Attributes
#FACT	This value indicates that the entity represents transactional data (center of star schema). Usually it contains the measures. Typically, these views are necessary for replication, therefore, they should not be joined with master data views.	Equivalent to an ADSO loading from a single datasource, without any master data links
#CUBE	The #CUBE value (like #FACT) also represents factual data, but #CUBE does not have to be without redundancy. This means joins with master data are possible. Queries are usually built on top of #CUBE, where data is replicated from facts	Equivalent to an ADSO loading from a single/multiple datasources, with master data linkage to attributes/texts/hiererachies

Annotation: @analytics.dataExtraction.enabled

Value	Description	BW Equivalent
#TRUE	Application developers can use this annotation to mark views that are suitable for data replication (for example, delta capabilities must be provided for mass data).	N/A
#FALSE	This view is not suitable for data replication	N/A

Annotation: @analytics.query

Value	Description	BW Equivalent
#TRUE	By tagging the CDS view, the developer can specify which views will be exposed to the analytic manager. This type of view will be interpreted as an analytic query by the analytic manager. This will display the consumption view in the frontend tools such as BOBJ, when searching for it in the application folder	BW or BEx Query. This view will be enabled to be run in RSRT in the backend
#FALSE	The query view will not be exposed to the analytic manager.	N/A

Annotation: @ObjectModel.dataCategory

Value	Description	BW Equivalent
#TEXT	Indicates that the annotated entity represents texts. Usually one key element is of type language. NOTE: Within the VDM a text view is always language-dependent.	Equivalent to infoobject Texts/descriptions
#HIERARCHY	Indicates that the entity represents the hierarchy-related data. This could be a header information or structure information.	Equivalent to infoobject Hierarchy

Annotation: @ObjectModel.representativekey

• Most specific element (field or managed association) of the primary key (indicated by the keyword KEY) that represents the entity which the view is based on. This element shall be used as the anchor for defining foreign key relationships (except for text views): The foreign key field corresponding to the representative key represents the entity. As such it can be called representative foreign key element. The foreign key association is defined on the representative foreign key element. The name of the representative key typically equals the name of the entity represented by the view.


• For non-text views it is the key element for which the view serves as a value list/check table. For text views (@ObjectModel.dataCategory: #TEXT) it identifies the key element to which the text fields relate to.


• The representative key element has to be modelled explicitly even if there is only one primary key field (no implicit derivation).


• A view may only become a target of a foreign key association if it has a representative key element (exception: language dependent text views may not be used as targets of foreign key relationships

Annotation: @AccessControl.authorizationCheck

Value	Description	BW Equivalent
#NOT_REQUIRED	A Data Control Language (row level security object for CDS Views) does NOT exist for the CDS View therefore no security is enforced for the CDS View, and all data is displayed	N/A
#CHECK	A Data Control Language (row level security object for CDS Views) EXISTS for the CDS View and row level security is to be enforced for the CDS View	Somewhat equivalent to BW authorizations
#NOT_ALLOWED	A Data Control Language (row level security object for CDS Views) exists for the CDS View and row level security is NOT to be enforced for the CDS View	N/A

 

Now that we've seen the main CDS View annotations, we'll look at a more detailed architecture of the VDM:



Notice how the concepts are similar to that of a BW environment. We have texts and dimension CDS Views. We can build these once and re-utilize them across any number of transactional CDS views.

Think of this as building a material dimension and text views. You only need to build this once, as the base tables won't change (MARA and MAKT). But for every transactional model built (Sales, Deliveries, COPA, Inventory, etc.) where you require material description or attributes, you can re-utilize that dimension and text view.

Below are more details regarding each of the CDS Views mentioned above:

TEXT 

 

@AbapCatalog.sqlViewName: 'ZBTMATERIAL'

@ObjectModel.dataCategory: #TEXT

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

@AccessControl.authorizationCheck: #NOT_REQUIRED

@VDM.viewType: #BASIC

@EndUserText.label: 'Material Text'

@ObjectModel.representativeKey: 'Material'



define view Zbt_Material as 

  select from makt 

        { 

          @ObjectModel.text.element:  [ 'MaterialName' ] 

          key makt.matnr as Material, 

          @Semantics.language: true

          key makt.spras as Language,

          @Semantics.text: true 

              makt.maktx as MaterialName}





where makt.spras = $session.system_language

 

DIMENSION



 

@AbapCatalog.sqlViewName: 'ZBDMATERIAL'

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

@VDM.viewType: #BASIC

@AbapCatalog.compiler.compareFilter: true

@EndUserText.label: 'Material Attributes'

@ObjectModel.representativeKey: 'Material'

@AccessControl.authorizationCheck: #NOT_REQUIRED



define view Zbd_Material as select from mara 



association [0..1] to Zbt_Material           as _Text           on $projection.Material      = _Text.Material

association [0..1] to Zbd_MaterialType       as _MaterialType   on $projection.MaterialType  = _MaterialType.MaterialType

association [0..1] to Zbd_MaterialGroup      as _MaterialGroup  on $projection.MaterialGroup = _MaterialGroup.MaterialGroup

association [0..1] to I_UnitOfMeasure        as _BaseUnit       on $projection.MaterialBaseUnit = _BaseUnit.UnitOfMeasure

association [0..1] to I_UnitOfMeasure        as _WeightUnit     on $projection.MaterialWeightUnit = _WeightUnit.UnitOfMeasure

association [0..1] to Zbt_Storage_Conditions as _StorCond       on $projection.StorageCondition = _StorCond.StorageCond



     { @EndUserText.label: 'Material'

       @ObjectModel.text.association: '_Text'

          key mara.matnr as Material, _Text,

          

       @ObjectModel.foreignKey.association: '_MaterialType'

       @EndUserText.label: 'Material Type'  

              mara.mtart as MaterialType, _MaterialType,

       @ObjectModel.foreignKey.association: '_MaterialGroup'

       @EndUserText.label: 'Material Group'        

              mara.matkl as MaterialGroup, _MaterialGroup,

       @EndUserText.label: 'Base Unit of Measure'  

       @Semantics.unitOfMeasure: true

       @ObjectModel.foreignKey.association: '_BaseUnit'

              mara.meins as MaterialBaseUnit, _BaseUnit,                

       @EndUserText.label: 'Gross Weight'  

       @Semantics.quantity.unitOfMeasure: 'MaterialWeightUnit'              

       @DefaultAggregation: #NONE

              mara.brgew as MaterialGrossWeight,

       @EndUserText.label: 'Net Weight'  

       @Semantics.quantity.unitOfMeasure: 'MaterialWeightUnit'

       @DefaultAggregation: #NONE

                     mara.ntgew as MaterialNetWeight,              

       @EndUserText.label: 'Weight Unit'

       @Semantics.unitOfMeasure: true

       @ObjectModel.foreignKey.association: '_WeightUnit'

              mara.gewei as MaterialWeightUnit, _WeightUnit,

                            

              mara.mfrnr as MaterialManufacturerNumber,

              mara.mfrpn as MaterialManufacturerPartNumber,

       @EndUserText.label: 'Storage Condition'

       @ObjectModel.text.association: '_StorCond'              

              mara.raube as StorageCondition, _StorCond,

       @EndUserText.label: 'Product Hierarchy'       

              mara.prdha as ProductHierarchy

        

       

}

BASIC/FACT



Notice how we haven't applied any semantics (Annotations) to the fields in the select list, since this is the BASIC view, and we're creating the FACT of our model. Semantic annotations will be applied at the composite view.

Also note that a virtual data model can be comprised of multiple BASIC/FACT views depending on requirements.

@AbapCatalog.sqlViewName: 'ZBFACDOCAXX'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Universal Journal Entry, Basic'

@VDM.viewType: #BASIC

@Analytics.dataCategory: #FACT 

@Analytics.dataExtraction.enabled: true

    

define view ZBF_ACDOCA_XX as select from acdoca 



{

       rbukrs as CompCode,

       gjahr  as FiscalYear,

       poper  as Period,

       racct  as GLAccount,

       matnr  as Material,

       werks  as Plant,

       

// UOMs - Currencies

       runit  as UOM,

       rhcur  as CCCurr,

       

// Measures

       msl    as Quantity,

       hsl    as AmtCC              

}

 

 

COMPOSITE/CUBE

@AbapCatalog.sqlViewName: 'ZCCACDOCAXX'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Universal Journal Entry, Composite'

@VDM.viewType: #COMPOSITE

@Analytics.dataCategory: #CUBE

@Analytics.dataExtraction.enabled: true



define view ZCC_ACDOCA_XX as select from ZBF_ACDOCA_XX 



association [0..1] to I_Material    as _Mat      on $projection.Material = _Mat.Material

association [0..1] to I_Plant       as _Plant    on $projection.Plant    = _Plant.Plant

association [0..1] to I_CompanyCode as _CompCode on $projection.CompCode = _CompCode.CompanyCode



{

      @ObjectModel.foreignKey.association: '_CompCode'

      CompCode, _CompCode,

      FiscalYear, 

      Period, 

      GLAccount, 

      @ObjectModel.foreignKey.association: '_Mat'

      Material, _Mat,

      @ObjectModel.foreignKey.association: '_Plant'

      Plant, _Plant,

      //UOMs - Currencies

      @Semantics.unitOfMeasure: true

      @EndUserText.label: 'Base UOM'

      UOM, 

      @Semantics.currencyCode: true

      @EndUserText.label: 'Comp. Code Curr.'

      CCCurr, 

//Measures 

      @DefaultAggregation: #SUM

      @EndUserText.label: 'Quantity'

      @Semantics.quantity.unitOfMeasure: 'UOM'

      Quantity, 

      @DefaultAggregation: #SUM

      @EndUserText.label: 'Amount CC'

      @Semantics.amount.currencyCode: 'CCCurr'

      AmtCC

}

 

CONSUMPTION

@AbapCatalog.sqlViewName: 'ZCCACDOCAXXQ001'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Universal Journal Entry, Query'

@VDM.viewType: #CONSUMPTION

@Analytics.query: true 

define view ZCC_ACDOCA_XX_Q001 as select from ZCC_ACDOCA_XX 



{

    //ZCC_ACDOCA_XX 

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

    CompCode, 

    FiscalYear, 

    Period, 

    GLAccount, 

    Material, 

    @AnalyticsDetails.query.display: #KEY_TEXT

    @AnalyticsDetails.query.axis: #ROWS  

    Plant, 

  

//UOMs - Currencies  

    UOM, 

    CCCurr, 

    

//Measures    

    Quantity, 

    AmtCC 



}

ADVANTAGES

One of the main advantages of the VDM is that you build it once, and it can be consumed in a multitude of front end tools:

• BOBJ
  
  
  
  • WEBI
  
  
  • Analysis for Office
  
  
  • Crystal
  
  
  • OLAP
  
  
  
  


• Lumira Discovery (even though it's being discontinued in favor of Analytics Cloud)


• Analytics Cloud


• ALV Grid


• ODATA services


• Fiori

In addition to that, because the CDS View exists on the application layer, we can leverage the existing ABAP security model through PFCG. For CDS View security we use an artifact called the Data Control Language (DCL). For more information on DCL, please refer to my blog below:

https://blogs.sap.com/2017/05/22/cds-view-row-level-authorizations-with-data-control-language-dcl/

Once you learn how to model using CDS Views, the time to develop a VDM can be very fast, translating to delivering quick and efficient reporting solutions to customers.

PERFORMANCE

Regarding performance, the VDM is amazing for high volume high aggregation scenarios. I built a model at a client on Purchase Orders, going from the header down to the schedule line, with many master data joins. The schedule line table had about 250 Million records, across 8 years of data at the time.

When running a summarized report with no filters, with 3 measures on the columns and just the year on the drill down (8 rows, 3 columns, 24 data points), the report returned in 1-2 seconds!!!

Now, using the same data model above, when I ran a very detailed report, going down to the schedule line item, bringing many different attributes, with about 30 rows in the drilldown, for an entire month, that report took 10 minutes to run. Why? Because it was doing all the joins and calculations I had defined in the model in real time, and was very process intensive.

The scenario above would be a great candidate for a persisted solution in BW, where the ETL has taken care of any calculations and data joins. The data is persisted in an ADSO and when a report is run, it is simply selecting the data based on the criteria, without the need of any additional processing, with the expectation of performance in the sub second region.

CONCLUSION

So with this I hope I was able to provide information on the Virtual Data Model using CDS Views. I hope I was able to provide context regarding performance expectations, and that this is a great solution for real time operational reporting, but each reporting scenario must be addressed individually to assess the suitability of the solution.