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.
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.
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.
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
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.
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..