- SAP HANA Tutorial
- SAP HANA - Home
- SAP HANA Introduction
- SAP HANA - Overview
- In-Memory Computing Engine
- SAP HANA - Studio
- Studio Administration View
- SAP HANA - System Monitor
- SAP HANA - Information Modeler
- SAP HANA - Core Architecture
- SAP HANA Modeling
- SAP HANA - Modeling
- SAP HANA - Data Warehouse
- SAP HANA - Tables
- SAP HANA - Packages
- SAP HANA - Attribute View
- SAP HANA - Analytic View
- SAP HANA - Calculation View
- SAP HANA - Analytic Privileges
- SAP HANA - Information Composer
- SAP HANA - Export and Import
- SAP HANA Reporting
- SAP HANA - Reporting View
- Bi 4.0 Connectivity to HANA Views
- SAP HANA - Crystal Reports
- SAP HANA - Excel Integration
- SAP HANA Security
- SAP HANA - Security Overview
- User Administration & Management
- SAP HANA - Authentications
- SAP HANA - Authorization methods
- SAP HANA - License Management
- SAP HANA - Auditing
- SAP HANA Data Replication
- SAP HANA - Data Replication Overview
- SAP HANA - ETL Based Replication
- SAP HANA - Log Based Replication
- SAP HANA - DXC Method
- SAP HANA - CTL Method
- SAP HANA - MDX Provider
- SAP HANA Monitoring
- SAP HANA - Monitoring and Alerting
- SAP HANA - Persistent Layer
- SAP HANA - Backup & Recovery
- SAP HANA - High Availability
- SAP HANA - Log Configuration
- SAP HANA SQL
- SAP HANA - SQL Overview
- SAP HANA - Data Types
- SAP HANA - SQL Operators
- SAP HANA - SQL Functions
- SAP HANA - SQL Expressions
- SAP HANA - SQL Stored Procedures
- SAP HANA - SQL Sequences
- SAP HANA - SQL Triggers
- SAP HANA - SQL Synonym
- SAP HANA - SQL Explain Plans
- SAP HANA - SQL Data Profiling
- SAP HANA - SQL Script
- SAP HANA Useful Resources
- SAP HANA - Questions and Answers
- SAP HANA - Quick Guide
- SAP HANA - Useful Resources
- SAP HANA - Discussion
SAP HANA - SQL Script
Set of SQL statements for HANA database which allows developer to pass complex logic into database is called SQL Script. SQL Script is known as collections of SQL extensions. These extension are Data Extensions, Function Extensions, and Procedure Extension.
SQL Script supports stored Functions and Procedures and that allows pushing complex parts of Application logic to database.
Main benefit of using SQL Script is to allow the execution of complex calculations inside SAP HANA database. Using SQL Scripts in place of single query enables Functions to return multiple values. Complex SQL functions can be further decomposed into smaller functions. SQL Script provides control logic that is not available in single SQL statement.
SQL Scripts are used to achieve performance optimization in HANA by executing scripts at DB layer −
By Executing SQL scripts at database layer, it eliminates need to transfer large amount of data from database to application.
Calculations are executed at database layer to get benefits of HANA database like column operations, parallel processing of queries, etc.
Integration with Information Modeler
While using SQL scripts in Information Modeler, below given are applied to Procedures −
- Input parameters can be of scalar or table type.
- Output parameters must be of table types.
- Table types required for the signature are generated automatically.
SQL Scripts with Calculation Views
SQL script are used to create script based Calculation views. Type SQL statements against existing raw tables or column store. Define output structure, activation of view creates table type as per structure.
How to create a Calculation View with SQL Script?
Launch SAP HANA studio. Expand the content node → Select a package where you want to create the new Calculation view. Right Click → New Calculation View End of the navigation path → Provide name and description.
Select calculation view type → from Type dropdown list, select SQL Script → Set Parameter Case Sensitive to True or False based on how you require the naming convention for the output parameters of the calculation view → Choose Finish.
Select default schema − Select the Semantics node → Choose the View Properties tab → In the Default Schema dropdown list, select the default schema.
Choose SQL Script node in the Semantics node → Define the output structure. In the output pane, choose Create Target. Add the required output parameters and specify its length and type.
To add multiple columns that are part of existing information views or catalog tables or table functions to the output structure of script-based calculation views −
In the Output pane, choose Start of the navigation path New Next navigation step Add Columns from End of the navigation path → Name of the object that contains the columns you want to add to the output → Select one or more objects from the dropdown list → Choose Next.
In the Source pane, choose the columns that you want to add to the output → To add selective columns to the output, then select those columns and choose Add. To add all columns of an object to the output, then select the object and choose Add → Finish.
Activate the script-based calculation view − In the SAP HANA Modeler perspective − Save and Activate - to activate the current view and redeploy the affected objects if an active version of the affected object exists. Otherwise, only the current view is activated.
Save and activate all − to activate the current view along with the required and affected objects.
In the SAP HANA Development perspective − In Project Explorer view, select the required object. In the context menu, select Start of the navigation path Team Next navigation step Activate End of the navigation path.
SQL Scripting in HANA Information Modeler is used to create complex Calculation Views, which are not possible to create using GUI option.