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

[former_member184768 · 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

[former_member264321]

This message was moderated.

[rindia]

Dear Santhosh,

SQLScript is a set of SQL extensions for SAP HANA database which allow developers to push data intensive logic into the database.

It should be used in cases where other modeling constructs of HANA such as Attribute views or Analytic views are not sufficient.

It is not good idea to do data calculation before data loading using BODS / SLT / DXC based on the current business need as in future one does not know how the business need changes.

That's why better to load the data in HANA and based on the requirements we can do modeling.

SQLScript is highly famous for its CE plan operators and using this data logic can be processed with great speeds.

CE plan operator does the data transformation functionality internally and used in definition of functions. It is an alternate to using SQL statements.

One example is:

with_tax = CE_PROJECTION(:product, [“CID”, “CNAME”, “OID”, “SALES”,

                  CE_CALC('”SALES” * :vat_rate', decimal(10,2)) AS “SALES_VAT”],

                   ''”CNAME” = ':cname''');

Is equivalent to

with_tax2 = SELECT cid, cname, oid, sales, sales * :vat_rate AS sales_vat

                       FROM :product

                       WHERE cname = ':cname';

Still want to know more then please refer to http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

Regards

Raj