SAP HANA SQL scripting

[Former Member]

Hi,

I have few doubts on HANA SQL Scripting.

What is the main use of HANA SQL scripting ,stored procedure in HANA.

If want to do any data calculation, we can do them while data loading it self  using data services tool ( when source is NON- SAP) or in HANA modeling level using Calculation attribute view. when the Source system is SAP we can do them by writing ABAP code and configuring the include name at system table level, that ABAP code can get executed during execution.

Can you please clarify what is the main purpose of SQL scripting i.e data extraction, insertion , calculation, Procedure statement etc....

In what cases we can find the importance of usage.

Santhosh

[Ravi_Channe · Accepted solution]

Hi Santosh,

What you mentioned is right, but there are different thought processes. A tool like Data Services provides ETL (Extraction -> Transformation -> Loading).

Many a times, you may not want to perform T and just have EL (Extraction -> Loading). This is required so that you have exact data as source system in your DW landscape which. This helps in back-tracking and reconciliation of the data with the source system. If the data is "Transformed", then you may find difficulties in tracing it back to the source system data.

In such cases, the T happens in HANA with help of SQL scripts. You can then derive values, modify the data, cleanse it and load to another layer of objects.

In case of a scenario, when you need to perform a look up on an object which is already loaded to the database like need to check the dependency on Master data or derive some values from Master data for ex. If you need Transaction Header No, Transaction Created by and Creation Date for each of the line item to ensure that the line item data belongs to correct Header and have the created by and created date for audit purpose, then with the ETL tool like Data Services, it will have to make a call to the database to get the data and then perform the validation / derivation. In such scenarios, if the logic is more complex and highly data driven, you can opt to perform it WITHIN the database using SQL scripts.

Also as Raj mentioned, SQL scripts provide more than the ETL functionality like optimized data access path using CE functions and assist in the reporting part.

Regards,

Ravi