Optimized ABAP CDS Modelling Approach – Extended Star Schema

Problem Statement:

Custom report in Embedded analytics which is based on ACDOCA table is consuming huge memory and report ends up with error as below. While checking in ST22 error message indicates report execution exceeds the allocated memory size which was 106GB.

Error Message

Report execution got terminates so couldn’t able to get bottleneck statements through Plan-viz. Custom model leverages 95% of standard CDS views which had been built on top of ACDOCA table but still memory consumption was extremely high.

Reporting Requirement:

Report should show GL ending balances, GL balances, slice and dice can happen with couple of user specific custom fields and different dimensions from ACDOCA table. Report should be capable to show data in hierarchal format by GL Account or Cost Center or Profit Center hierarchies.

Standard CDS views don’t have Cost center & Profit center hierarchies hence enhancement for the same have been performed.

Existing Design:

Built consumption view on top of standard CDS view I_GLAcctBalanceCube, this standard CDS is being integrated with 7-8 layers of other standard CDS views with different functional logics.

Current ABAP CDS View model based on standard CDS views

Solution:

To overcome the above said problem statement, developed custom ABAP CDS views as per below approach. Instead of keeping dimensions, facts, units, attributes in same view leverage the concept of extended star schema in ABAP CDS view design as below.

Existing CDS view model had almost around 10 CDS view layers whereas in below approach same solution can be achieved in 4 CDS views.

Star Schema based ABAP CDS View Modelling

Report should show cumulative ending balance till that month so separate UNION is being used to get cumulative ending balance till that month. Now fact view is aggregated based on defined key fields.

Code Sample from dimension basic view

Code sample from fact basic view

For each period UNION perform period masking as below so that cumulative ending balance will get aggregated based on GL for the same period otherwise report will show same GL in different periods.

UNION

Now integrate both dimension and fact basic views in composite view with required joins. As we maintained same key granularity in both dimension/fact basic views so INNER join is being used in composite view to merge the data into single record.

Dimension and Fact view join

Used the integrated composite view in consumption view with required selection prompts.

Parameterized all views with Ledger, Company Code and Fiscal Year so that required records will be fetched from ACDOCA table and processed in consumption CDS view..

Performance throughput:

Selection criteria: Ledger-0L, Fiscal Year- 2020, FiscalYearPeriod – 001.2020 to 005.2020

Data volume in ACDOCA table for above selection – 0.95 Million

While executing the report, existing CDS view model which leverage standard CDS views consumed 80GB memory.

Performance trace

New approach provides 20x times performance throughput compared to other approach.

Implemented this approach in couple of other CDS view based reports which is showing promising performance throughput compared to other approach.

Conclusion: As per performance throughput & observations, recommended to use  extended star-schema based ABAP CDS view development while building reports/apps in Embedded Analytics.