BW/4HANA modeling scenarios

[mateusz_mikulski]

Hello,

we are using BW/4HANA and I am wondering which approach is better for modelling (data transformation, complex calculation):

1. Use only BW objects (Data Sources, Advanced Data Store Objects, Composite Provider) and add complex logic in ABAP (start / end / expert routine on transformations);
2. Use BW objects but add complex logic in HANA Calculation View & Table Functions – complicated data transformation and calculation would be done using SQL.

Most probably sometimes ABAP usage is necessary, but personally I prefer second option, where the flow is built as following:

When I compare ABAP routines and SQL views or table functions, in my opinion SQL objects are easier to write, to debug, to understand for support team and to document. When I think of performance, I bet HANA views would be faster, but probably in some cases ABAP routines are performing better.

Looking forward for some other opinions.

For reporting we are using Analysis for Office and 3^rd party tool.

SAP R3 is the source system, but business content can’t be used.

[susuang · Accepted solution]

Hello everyone!

At first I just want to mention that I’m not an expert or great SAP HANA developer, so please bear in mind that what I will write is only my personal opinion / feeling. What is more, I think there is no easy answer to this question – choose this or that, because we have to remember that it always depends on Scenario.

However, I think (and agree with Mateusz), that development done in SAP HANA is easier to understand by people which are coming from „outside of SAP world”. Nowadays it’s harder and harder to find programmers (or generally people with good SQL knowledge), but in my opinion there is much more people which know SQL rather than ABAP language.

So in terms of „easy to find someone” (maintenance for example) the winner is SAP HANA [SQL] *details on the bottom of my answer

On the other side, there is a question what is „easier to write” – SQL or ABAP. Again, the answer is – „it depends” 🙂 We all probably know how complicated for example SQL SELECT statement can be – lets take few correlated subqueries in one SELECT and bang! It could be very hard to debug. Developers can spend on just one SQL a lot of time to understand what is going on there ( or you’re hiring only superstars, so maybe couple of hours 😉 ). How about ABAP? So, this also could be „mission impossible” in many cases but it’s a programming language – you can debug it step by step, check values and find the root-cause of an issue.

In this case there is no clear winner – it depends what kind of people you’re hiring in fact.

Performance – hot topic(!) but again answer is the same 😉 You can write really good ABAP code, which will perform much better than SQL and Vice Versa.

Again, no clear winner here.

So what we should do? Hire really good Architect! Really – in my opinion this is the best answer. In other scenario – compare few solutions and choose the most appropriate. In the real life scenario ( 😉 ) – at least follow Best Practices in terms of naming convention, performance and so on. Some thoughts from top of my head (I’m „SQL guy” so will talk about SQL Best Practices):

• Try to avoid heavy UNION ALL statements
• If you’re using the same logic in a few places – try to use SQL Function (or table function). Then when you optimize one Function in terms of performance, it can affect many places.
• Use INT / BIGINT in Joins conditions – and here in fact it doesn’t matter if you’re using SAP HANA or other Databases. General rule is to avoid Varchar and and NVarchar in Joins, if it’s possible.
• If your goal is performance and not maintenance – try to use correlated subqueries. However, be careful with that, because it’s super easy to write correlated subquery which is not performing - do not use "sub-SELECT" just after SELECT statement - use it after FROM clause.
• If your goal is maintenance and not performance – try to use SQL Window Functions
• Try to read SQL Plans in SAP HANA – there will be a column “Execution Engine”. The more “Column” values you will find there, the better.
• Try to use Write-optimized Data Store Objects, if there is no update operation needed
• Use variables in Table Functions
• Try to use SAP HANA SQL Hints, to check if performance will be better

To sum up: I think it’s always a compromise between Performance, Complexity, Maintenance, Architects and Developers, but of course we all know that very well 🙂

Still, for me the winner is SQL in SAP HANA. For example because there is a lot of tutorials on SQL, not so many for ABAP. What is more, according to TIOBE Index for January 2019 (most popular languages), SQL is on 9th place, where ABAP is on 27th place (right after COBOL, I can't believe that!). So it really means something!

[mateusz_mikulski]

Thanks konrad for you thoughts. I understand how powerful AMDP scrips can be, but still I can see some downside:

1. AMDP is one script, so it is harder to build very well structured and clear solution (one AMDP script vs several HANA views or TFs);
2. You need to code inside AMDP, so it's not possible to use drag and drop interface and leverage graphical views (and for me graphical views are always the best option - you can easily debug them on each step, they are easy to enhance, to understand etc.);
3. AMDP script is good when you have BW object to BW object data flow (one main source). But when you are using more sources, usage of AMDP is not really consistent: in that case, in BW modelling, you can see AMDP transformation between two BW objects. But in fact, the transformation is between several different objects. To know that you need to open the script and read the code inside. Or have a good documentation.

It's good to have option to use AMDP transformations, but still, for me HANA artifacts are better. Of course we have some disadvantages (point 3 from your post is valid). Point 4 is something new for me as I was using HANA views as a part of composite providers many times. XSA upgrade it's not something I consider for the time being, but probably I should think about that beforehand.

Can you elaborate more about point 1? Why views are not good for data transformation?

[KonradZaleski]

Hi Mateusz.

Regarding my 1 point - this was only the conceptual statement that purpose of CV is reporting, I'm not saying that cannot be used for transformation 🙂
Basically if you have greenfield installation I strongly recommend to develop all the views in XSA since this is default framework for application development from SPS12 (see note: 2396214 ) and the development approach is quite different. Personally I experienced how big effort it is to convert XS classic views into XSA - for complex solution it is a lot of additional development required to cover all the requirements.
I was thinking about consuming HANA Views in AMDP but also because of XSA it is not a good idea:

[KonradZaleski]

Although for me it was much easier and more flexible to use calculation views for applying transfortmations, I'm not sure if it is a good approach.
I'm not an expert in SAP BW (especially in data loads), but here are my thoughts:

1. From my perspective Calculation Views are basically dedicated for reporting purposes not for transformations. AMDP are more appropriate here.
2. In AMDP scripts you can combine ABAP with SQL so the execution is pushed down to HANA level, therefore performance should be the same
3. I assume that if you change the name of CV or its location, you would need to manually adjust the tranformation
4. Currently HDI calculation views (XSA) cannot be used for BW Virtual Providers (See sap note: 2463312 ). So I assume that also they cannot be used directly as source of transformations.
5. When you move to XSA, views path most probably will change -> then see point 3.

Again - please don't treat my comment as a recommendation, because I'm really not experienced in that area, but maybe someone else could also refer/comment the points which I listed.

[MustafaBensan]

Hi Konrad,

Thanks for pointing out that HDI Calculation Views (XSA) are not supported for BW Virtual Providers. If I have understood this correctly, then the mixed modelling approach of combing native HANA with BW is only possible with XS Classic modelling. Yet according to SAP Note 2465027, XS Classic is being deprecated and will be removed from versions of SAP HANA after SAP HANA 2.0. This seems to imply that in future there will be no possibility to implement a mixed modelling approach unless SAP introduces HDI support for Virtual Providers.

What are your thoughts on this?

[KonradZaleski]

Hi Mustafa.
At the moment it seems that mixed scenarios are not supported when using XSA however as per Note 2463312

The consumption of HDI Calculation Views in Composite Providers or InfoObjects based on HANA model is supported:

- In BW/4HANA 1.0 (prerequisite: HANA 2.0 SP03) on pilot basis (please contact BW Product Management)

- In BW/4HANA 2.0 (official release planned)

We are currently evaluating when and how to support the HDI deployment of BW-generated HANA Calculation Views.

I guess it will start to be supported very soon.

[MustafaBensan]

Hi Mateusz,

I have been undertaking a similar review of BW architecture options and have come to the same conclusion that a mixed modelling approach which combines the features of native HANA and BW is preferable. I think you are spot on with your comment:

"When I compare ABAP routines and SQL views or table functions, in my opinion SQL objects are easier to write, to debug, to understand for support team and to document. When I think of performance, I bet HANA views would be faster, but probably in some cases ABAP routines are performing better."

For the above reasons, personally, I would only resort to ABAP routines if the requirement could not be met with native HANA. I also think it is easier to create a multi-layered Virtual Data Model with HANA views.

Regards,

Mustafa.

[mateusz_mikulski]

I just realized that there is one more disadvantage of using old-style ABAP routine (found in blog post😞

During execution of ABAP based transformation data’s are retrieved from database to application server for processing and transformed data are written back to database as shown in Figure 1. Data transfer happens twice in ABAP runtime, from database to application server and to database. This, along with row-by-row processing of field based calculations adds huge performance overheads to the data load process.

[former_member466150]

Hi Mateusz Mikulski,

Apart from "Data transfer happens twice in ABAP runtime, from database to application server and to database. This, along with row-by-row processing of field based calculations adds huge performance overheads to the data load process."

What are the other disadvantages of using ABAP routines in new BW/4HANA modelling concepts, can you please share your experiences(with examples if possible :-))?

Thanks and Regards,

HK

[mateusz_mikulski]

I think the most of disadvantages I mentioned in initial post:
When I compare ABAP routines and SQL views or table functions, in my opinion SQL objects are easier to write, to debug, to understand for support team and to document. When I think of performance, I bet HANA views would be faster, but probably in some cases ABAP routines are performing better.
Also very vital point was brought up by Damian in his post below:
Nowadays it’s harder and harder to find programmers (or generally people with good SQL knowledge), but in my opinion there is much more people which know SQL rather than ABAP language.