Why SQL scripting required in HANA. What is the limitation in modelling

[Former Member]

Why SQL scripting required in HANA. What is the limitation in modelling?

Can we do everything using in modelling, When we need scripting?

[KonradZaleski]

You will always find some requirement which you cannot cover by graphical views.

Here are some examples of operations which you can perform in SQL but not in graphical model:

• when you need to apply loop in your code - in SQL you can execute WHILE loop, but in graphical modeling it's not available
• when you need to apply more complex functions which are available only in SQL i.e WORKDAYS_BETWEEN() , regular expression related functions like SUBSTRING_REGEXPR and many others
• when you need to create custom function and apply it in your query
• when you need to apply complex join condition using different kind of operators other than "=" (like a.col1 >= b.col2 )
• when the logic is just too complex to create it in graphical model
• when you want to combine SQL with other programming language like R

I participated in multiple HANA related projects and there were always multiple cases when SQL scripting was required for creating views (mostly for applying loops and SQL functions which are not available in graphical views).

So I would say that SQL knowledge is significant for HANA Modeling.

[Former Member]

Thank you Konrad Załęski for your valuable time, can you mention one real time scenario from your experience

[KonradZaleski]

Here are some examples:

1. Report for BOM (Bill of Materials) and we wanted to display whole product structure (in this case WHILE loop was needed to retrieve all the materials for each BOM level), i.e.

Source data:

MATERIAL | PARENT
AAA      |
AAAA     | AAA   
AAAB     | AAA 
AAABA    | AAAB
AAABB    | AAAB

Desired output:

LEVEL | MATERIAL
0     | AAA
1     | AAAA
1     | AAAB
2     | AAABA
2     | AAABB

2. Report for calculating order processing time. Calculation should be based on workdays - weekends and country specific holidays should be skipped ( WORKDAYS_BETWEEN() SQL function ) ,i.e. compare number of days and working days between date 2002-12-23 and 2003-01-01 for US and Poland:

3. Report when multiple roles should be aggregated in single line (STRING_AGG SQL function)

Source data:

ID | Role
---------
1  | A
1  | B
1  | C
2  | A
2  | E

Desired output:

ID | Role
-------
1  | A, B, C
2  | A, E

Please vote/mark it as an answer if that helped to your question.

[SergioG_TX]

it is recommended to do the graphical modeling, however, there may be scenarios that require sql scripting. it is not required but almost every scenario can be accomplished w the modeling tool. you will need scripting to simplify some scenarios, one that comes to mind is to do a ranking function - it is easier and shorter doing it in sqlscripting but you can also accomplish the same with graphical models. Sometimes the SQL engine has better performance however.

as far as debugging and maintaining code, i would recommend graphical just because of syntax validation. there may be a bunch of other reasons, but these are from personal experience. maybe others can add to my short answer

[Former Member]

Thank you Sergio Guerrero, can you share one real time scenario from your experience