Skip to Content
Technical Articles
Author's profile photo Muruga MuthuKrishnan

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%20ABAP%20CDS%20View%20model%20based%20on%20standard%20CDS%20views

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%20Schema%20based%20ABAP%20CDS%20View%20Modelling

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%20Sample%20from%20dimension%20basic%20view

Code Sample from dimension basic view

Code%20sample%20from%20fact%20basic%20view

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

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%20and%20Fact%20view%20join

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%20trace

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.

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Swaminathan Ganapathy
      Swaminathan Ganapathy

      Good one Muruga. Thought process of using the Extended Star schema concept during the data processing  is excellent.

      Author's profile photo Mayank Jaiswal
      Mayank Jaiswal

      Nice blog Muruga, but it would be great if you could give one example to show how cummulation till that period is working.

      Best Regards,

      Mayank

      Author's profile photo Muruga MuthuKrishnan
      Muruga MuthuKrishnan
      Blog Post Author

      Thanks Mayank, will try to incorporate the example.

      Author's profile photo Amol Jaiswal
      Amol Jaiswal

      Nice blog Muruga. This approach can be useful in resolving performance issues related to high memory consumption CDS view.