HANA calculation view, temporal tables and processing data by rows

[Former Member]

Hi,

I have a question about using HANA sql script in calculation view for BW.

I'm trying to move the logic from application server to HANA DB. And I'm trying to find some objects to process data row-by-row like LOOP in internal tables in ABAP.

The task is to realize some complex logic - to read data from 3 DSO, to calculate some fields, check some conditions and delete some strings in the result and so on.

I've read most of the topics founded by search on "temporal tables in calculation view", "loop in calculation view" and so on. And i've made several experiments.

From them i understood that:

1. I can't use temporal tables in calculation view because calculation view is always read-only.
2. I can't make another procedure, use temporal tables and call it from my calculation vew because read-write procedure can not be called from read-only procedure. So I can't use  temporal tables at all.
3. I can use cursors but it is impossible to insert or delete some data in cursor.
4. I've found a blog Using SQLSCRIPT ARRAY as "Internal Table" to deal with complex logic about using separate array for every field of "internal table" which i need. But as for me, this variant is too difficult in debugging, understanding, supporting and so on.

I understand that using joins and unions is preferable variant, but I think that my task really could not be solved by joins and unions.

Before HANA on classic BW I used a "virtual cube" technology for such tasks. I can return to processing data in ABAP in virtual cube for current task.

But before doing this, I'd like to ask: have I understood correctly that the variant with arrays from that blog is the only one possible variant of realizing "internal tables" in calculation views?

If there are other variants, I'd be very glad to get some links to read, keywords, ideas and so on. Thanks.

[Former Member]

Still actual. Would be glad for any information or advice.

[lucas_oliveira]

Hello Evgeny,

SAP suggests avoiding using cursors with loops in the first place. By the way this is officially public here:

SAP HANA SQLScript Reference - SAP Library

Using plain SQL (avoiding row-by-row processing) is where we see the best performance (if that's what's driving you now). So you might want to check if your implementation can benefit from that instead.

If not, then why push it to HANA? Although we hear lots of fuzz about pushing logic to HANA, app server implementations are still necessary and will offer a better toolset to developers (generally speaking). Specially in row-by-row processing.

BRs,

Lucas de Oliveira

[Former Member]

Hello Lucas,

I had two main reasons to push it to HANA. The first one - in BW for processing data by ABAP in queries we should make a virtual cube or virtual characters/key figures. It is well-known but quite complicated technology. I hoped that pusing this logic to HANA would make the process easier. The second reason - to save time in sending data from DB to app.server. For example, in one of my task i should make a selection of data and than delete more than 90% of lines according to some conditions. If i check these conditions in app.server, all initial selection of data will be sended from DB. If i check them in DB, only result will be sended and the volume of data will be 10 times smaller.

The idea in your link is clear, but they selected several simple examples and show that it's better to use plain SQL instead of coursors. Of course it's better when it's possible. But in some task working only with plain SQL is very difficult or even impossible. It just looks a little strange - there is opporunity to work with temporal tables in HANA but it is closed for using in calculation views (and therefore in BW).

But anyway thanks a lot for your answer.