Question Re: SAP Datasphere ROW ENGINE issues in Technology Q&A

SAP Datasphere ROW ENGINE issues

albertosimeoni — Tue, 25 Jul 2023 16:57:26 GMT

Hello,

sometimes for SIMPLE calculations in graphical views we experience OOM issues.

How this happens:

consider this calculated column:

TO_DATE( TO_VARCHAR( SALESDOCUMENTDATE, 'YYYY') || '0101', 'YYYYMMDD').

This will trigger a temporary ROW STORE materialization visible in explain plan.

if I remove the calculated column : no ROW SEARCH step appear:

This do not affect too much performances in case of few columns many rows.

In case of many columns and few rows ( like 300+ columns available in SD standard BI Content made by SAP ).

We experience a x100 factor in memory usage during View persistency, and partitioning do not help too much.

So for ~ 4 Million rows times ~ 300 columns we hit over 20GB of memory during execution.

While the persisted View account only for 200MB.

This do not ends here,

if the views are used by third party BI tools (with 1 daily full import to the other BI tool, so a "select *" statement is issued ),

Just adding a simple calculation as shown above will trigger OOM issues.

This issues are not visible during modelling and MAY involve colleagues from other teams (sys admins), to analyze in hana cockpit / SQL console. This is a real big issues as in big projects 30% of the time is spent in this kind of optimization ( that we can call "bug hunting" ).

I have 2 questions:

1) When Row Engine will be completely removed in favour of ESX ?

2) Is there a way to know which function available in graphical view editor will trigger a row store temporary materialization ? ( can someone add a sort of "black pirate flag" to spot this ? ).

Best Regards,

Alberto

Re: SAP Datasphere ROW ENGINE issues

Dominik7727 — Wed, 26 Jul 2023 14:50:04 GMT

HI Alberto,

Are you added calculated column at very bottom of your view?

Have tried to force engine with hints?

https://help.sap.com/docs/SAP_HANA_PLATFORM/9de0171a6027400bb3b9bee385222eff/a1664ad72db542d9a519d1e195a02d37.html

Kind Regards,

Dominik

Re: SAP Datasphere ROW ENGINE issues

albertosimeoni — Wed, 26 Jul 2023 15:18:51 GMT

Hello Dominik,

the HANA cloud database in my case is managed by the application (SAP Datasphere),

So I have not a user/login with privileges to change configuration parameters.

The procedure to persist a view is triggered and managed by the application, so I can not enter an execution hint on it.

Best Regards,

Alberto

Re: SAP Datasphere ROW ENGINE issues

Dominik7727 — Thu, 27 Jul 2023 07:05:55 GMT

Beauty of cloud solutions.

Re: SAP Datasphere ROW ENGINE issues

albertosimeoni — Mon, 31 Jul 2023 10:12:49 GMT

We partially solved problems of third party BI Client Using

"WITH HINT(USE_HEX_PLAN)"

Thanks

Re: SAP Datasphere ROW ENGINE issues

Dominik7727 — Mon, 31 Jul 2023 11:43:29 GMT

Great to hear.