Skip to Content
Technical Articles
Author's profile photo Masaaki Arai

Dynamic Cache for CDS View performance optimization in S/4HANA

Updated on 15 Feb 2021:

Add the way to create more flexible dynamic cache on DB layer.

Purpose of this blog

Performance optimization of analytical app is crucial in S/4HANA as it causes additional system load as well as performance of analytics itself. This is great impact on business users.

This blog is about the CDS View performance optimization with Dynamic Cache in S/4HANA. With the Dynamic Result Cache, as the aggregated cached data is accessed instead of data in the source table, thus the runtime could be optimized. The cached data is updated when the data in the source is updated. Dynamic Cache can be created for a table in S/4HANA.

This blog explains

  • The brief overview of Dynamic Result Cache
  • How to create “Dynamic Cache” as DDIC object and how it works in the test case including
    • The transaction to activate Dynamic Cache (S_DBCACHE_CONFIG)
    • Status view (M_DYNAMIC_RESULT_CACHE), e.g. how many times the cache was used.

The source of Dynamic Cache must be one table currently, so that the expected optimization comes from the reduction of the record number to be selected from one source table. But I still believe it should help optimizing the runtime for the CDS View in which complex calculations using the data in the source table only are included such as VDM of G/L Account Balance View (I_GLACCTBALANCECUBE), Stock Time series view (I_MATERIALSTOCKTIMESERIES) in which complex self join calculates balance value from delta value is included, and some Compatibility View like FALGLFLEXT.

Detail about Dynamic Cache is explained in SAP Note 2506811.

 

Table of the contents

Dynamic Result Cache

What is Dynamic Result Cache?

Dynamic Result Cache is the cache for one table for performance optimization in HANA. The access to the source table is redirected to the Dynamic Result Cache if the request can be filled with the cached data.

 

How faster?

In the test case of this blog, Database time is 1/8 shortened. Without Dynamic Cache, the DB time is 4 sec but it is 0.5 sec with Dynamic Cache. It also depends how much the data is aggregated and how the data is processed in the CDS View or the SELECT statement to access the table.

 

When available?

It is available as of SAP HANA 2.0 SPS 02. In S/4HANA 1809, Dynamic Cache has to be created in database layer. But as of S/4HANA 1909. As of S/4HANA 1909, ABAP Dictionary object Dynamic Cache is provided. But it is more flexible to create in database layer.

For ACDOCA (Universal Journal Actual table), how to create dynamic cache as HANA artifact or as ABAP dictionary object Dynamic Cache.

 

Limitations

  • Query Execution Frequency: A side-effect of the version garbage collection process is that it may clear data from the cache.
  • Supported aggregation types: SUM, COUNT, AVE, MIN, MAX, but DELETE or UPDATE operations for the source table invalidates the cache including MIN or MAX.
  • Single column tables only (Join is considered to be supported in the future).
  • Total memory size for the cache and the memory size for one cache are limited with the parameters (Total memory size (total_size) = 10000MB, the memory size for one cache (max_cache_entry_size) = 1000MB by default). See SAP help for further detail. It is generally expected not to have too large cache, so having several smaller caches having different fields would be one option.
  • Dynamic Cache might not be used for partitioned table if the Dynamic Cache definition contains a filter on a partition criterion if or range restriction which could prune some of partitions, because SQL optimizer works after the partition pruning
    • Case1:
      • Dynamic Cache: “create DC1 as (select a, b, sum(k) from TAB where c < 10 group by a, b) with dynamic cache”
      • SELECT statement: “select a, b, sum(k) from TAB where c < 10 group by a, b”
    • Case2:
      • AG_TAB is partitioned by _dataaging, it’s aging table
      • create DC2 as (select a, b, _dataaging, sum(k) from AG_TAB group by a, b, _dataaging) with dynamic cache;
      • SELECT statement: “select a, b, sum(k) from AG_TAB group by a, b with RANGE_RESTRICTION(‘CURRENT’)”
        • If “RANGE_RESTRICTION(‘CURRENT’)” is not included in the statement, or if the source is CDS View and it includes the annotation “DataAging.noAgingRestriction:true” (to access aged data).

Information about Dynamic Result Cache

  • In S/4HANA 1909, Dynamic Cache “FINS_DC_ACDOCA_GL” for ACDOCA is provided by default. See the SAP Note 2713569 for further detail.
  • SAP Note 2506811 FAQ: SAP HANA Dynamic Result Cache.
  • SAP help on Dynamic Result Cache

 

Test on Dynamic Cache for ACDOCA

The test scenario overview

Dynamic Cache for ACDOCA is created and the CDS View includes the table ACDOCA.

The Access to the CDS View is redirected to the Dynamic Cache instead of source table.

Test environment:

SAP S/4HANA CAL instance

  • S/4HANA 1909
  • HANA2 Rev46

How to create Dynamic Cache View

Firstly, Dynamic Cache including BUDAT has to be created for ACDOCA to optimize the access to I_GLAcctBalanceCube, whose runtime tends to be long as balance value is calculated from past delta values and BUDAT is used as the key to join internally. Standard Dynamic Cache FINS_DC_ACDOCA_GL is not enough as BUDAT is not included.

 

Steps:

From the context menu of the package tree in ADT, select New > New ABAP Repository Object > Dictionary > Dynamic Cache

 

Enter Dynamic Cache name

 

 

Define as below

define dynamic cache ZD_ACDOCA10 on acdoca
{
  rldnr,
  rbukrs,
  gjahr,
  ryear,
  poper,
  fiscyearper,
  periv,
  budat,
  ktopl,
  racct,
  kokrs,
  prctr,
  segment,
  rbusa,
  rfarea,
  rcntr,
  rrcty,
  bstat,
  rtcur,
  rwcur,
  rhcur,
  rkcur,
  rocur,
  rvcur,
  rbcur,
  rccur,
  rdcur,
  recur,
  rfcur,
  rgcur,
  runit,
  rvunit,
  drcrk,
  sum( tsl ),
  sum( wsl ),
  sum( hsl ),
  sum( ksl ),
  sum( osl ),
  sum( vsl ),
  sum( bsl ),
  sum( csl ),
  sum( dsl ),
  sum( esl ),
  sum( fsl ),
  sum( gsl ),
  sum( msl ),
  sum( vmsl )     
}
creation configurable default off
  • AAs BUDAT is included, the cache size would be larger, so you would have to remove some fields like PRCTR, or have to adjust the parameter (total_size/max_cache_entry_size)
    • As mentioned in the “Limitations”, too large cache would not help improving the performance at the moment.
  • The Dynamic Cache which includes “creation configurable default off” can be set to activated or deactivated with the transaction “S_DBCACHE_CONFIG”. The Dynamic Cache without this setting is always active.
  • This custom Dynamic Cache is almost the same as the standard Dynamic Cache “FINS_DC_ACDOCA_GL” but BUDAT (Posting Date) is added to be used for G/L Balance View (I_GLACCTBALANCECUBE).

Steps of the scenario

  1. Before activating the Dynamic Cache
    1. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT)
    2. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE”
  2. After Activating the Dynamic Cache
    1. Activate the Dynamic Cache with Tcd S_DBCACHE_CONFIG
    2. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” which stored data in the Dynamic Cache
    3. Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT)
    4. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” which accesses the Dynamic Cache.
    5. Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT)
  3. Deactivate the Dynamic Cache again
    1. Deactivate the Dynamic Cache with Tcd S_DBCACHE_CONFIG
    2. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT)
    3. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” without the Dynamic Cache

Detail of steps

1. Before activating the Dynamic Cache

1-1. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT)

Run Tcd DBACOCKPIT and select Diagnostics > SQL Editor, and run the following query.

select * from m_dynamic_result_cache

Result

 

1-2. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE”

Run the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” (= running the interface View I_GLACCTBALANCECUBE directly) with Tcd RSRT pushing “Execute and Debug” and flagging

Enter the Posting date from/to as below and run

Result (in the following screen shot, it is drilled down with G/L Account).

Back (F3) and the Statistic is displayed.

DB Time: 4.4 sec (9000 Data Manager)

Selected records: 6298824 (9011 DBSEL)

2. After Activating the Dynamic Cache

2-1. Activate the Dynamic Cache with Tcd S_DBCACHE_CONFIG

Search with Cache name (Dynamic Cache name) or Data Source (Table). In the following case, the data source is set to be ACDOCA.

The Dynamic Caches for ACDOCA are listed.

Select the created Dynamic Cache “ZD_ACDOCA10” and push “Toggle DB Status”.

Now “Is Configurable DB Status” is changed and the Dynamic Cache is activated.

 

2-2. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” for which the data is stored in the Dynamic Cache.

Run the same query with Tcd RSRT in the same way as 1.

DB Time: 1.8 sec (9000 Data Manager)

Selected records: 6298824 (9011 DBSEL)

 

2-3. Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT)

Access m_dynamic_result_cache with tcd DBACOCKPIT.

New Cache is created.

 

2-4. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” which accesses the Dynamic Cache.

Access m_dynamic_result_cache with tcd DBACOCKPIT.

DB Time: 0.5 sec (9000 Data Manager)

 

2.5 Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT)

 

3. Deactivate the Dynamic Cache again

3-1. Deactivate the Dynamic Cache with Tcd S_DBCACHE_CONFIG

3-2. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT)

3-3. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” without the Dynamic Cache

DB Time: 4.3 sec (9000 Data Manager)

How to create Dynamic Cache as HANA artifact

Dynamic Cache can be created as HANA artifact before S/4HANA 1909. it is guided in SAP Note 2713569 in which the sample source code is attached. In Dynamic Cache as HANA artifact, more flexible calculations are available like UNION, CASE.

 

Thanks for reading!

 

Assigned tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mayank Jaiswal
      Mayank Jaiswal

      As usual Great Blog Masaaki!! but can you throw some highlights on the below questions -

      a) How do you check the dynamic cache for any CDS view (will it be a check on the base tables).

      b) Since we do not call out dynamic cache anywhere in the cube views “2CIFIGLBALCUBE/!2CIFIGLBALCUBE" how you know that Dynamic Cache ZD_ACDOCA10 will be called. (my guess is system automatically checks for the selected fields in the output if Dynamic Cache exists then it uses it)

      c) Also how it works when you have CDS view which involved multiple tables.

      Regards,

      Mayank Jaiswal

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      Hello Manank,

      See M_DYNAMIC_RESULT_CACHE to find dynamic cache and how many it is used.

      Dynamic cache is only for single table at the moment.

      regards, Masa

      Author's profile photo Shuang Zhang
      Shuang Zhang

      Hi Masaaki,

      Great post!

      Is there any way to cache string functions like "Concat", "Left", etc.?

      Thanks,

      Shuang