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.

1 33

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.

2 32

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.

3 32

Select default schema − Select the Semantics node → Choose the View Properties tab → In the Default Schema dropdown list, select the default schema.

4 30

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.

5 27

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.

So, this brings us to the end of blog. This Tecklearn ‘Usage of SQL Script in SAP Hana’ blog helps you with commonly asked questions if you are looking out for a job in SAP Hana and SAP Domain. If you wish to learn SAP Hana and build a career in SAP domain, then check out our interactive, SAP HANA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/sap-hana-training-certification/

SAP HANA Training

About the Course

SAP HANA is an in-memory computing application that is designed and developed to boost the business processes, deliver smart solutions, and simplify both hardware and software environments. Our Sap Hana Training course will help you understand and learn the fundamentals and will also felicitate on training hands-on for the better grasp on the course. Further, we have the highly qualified professionals who will train you about Sap Hana Studio, Modelling, Security features and its various other aspects. You will understand why SAP HANA is a fundamentally different database engine upon the completion of this SAP HANA course.

Why Should you take SAP HANA Training?

The average Sap Hana Consultant salary $165,750 per year or $85 per hour. (neuvoo.com).
SAP HANA is the highest growing technology; hence, there is no surprise in plenty of career opportunities in this field. Since it is one among the fastest-growing products in the history of SAP, it is considered by the industries as a ground-breaking key for in-memory databases.
SAP HANA currently has more than 6,500 customers globally.

What you will Learn in this Course?

Introduction to SAP HANA

Fundamentals of SAP HANA
Capabilities of SAP HANA
Limitations of SAP HANA

Key Features of SAP HANA

Key Features: High Performance functionalities In-Memory computing, Columnar store database, Data Compression and Massive Parallel Processing
Using SAP HANA for Non-SAP Applications

Architecture of SAP HANA

Detailed Architecture of SAP HANA Database
Concept of SAP HANA Landscapes and Scenarios

Overview of HANA Studio

SAP HANA System – Perspectives, Administration, Modelling, Development Plan
HANA Database SQL Basics and Database SQL Script
Types of statements and data types
Operators, expressions and basic query execution
Sub-queries, Types of Joins, Expressions and Loops
Catalog – Schema, Table, Views, Functions, Stored Procedures, Index, Synonyms, Sequences, Triggers

Data Provisioning

Data Provisioning with Flat File upload
Provisioning – SDA (Smart Data Access)
Joins Types in HANA

SAP HANA Modelling

Types of Models
Attribute Views, Joins and Using Filter Operations
Creating Restricted and Calculated Columns
Using Hierarchies
Analytic Views – Star Schema design and Multi-Dimensional Modelling
Variables and Input parameters

Calculation Views

Dimension Calculation View
Information View
SAP HANA Variables
Introduction to Input Parameters

SAP Project

Using HANA analytical view building of COPA (Controlling and Profitability Analysis) model
SAP HANA COPA for evaluation of market segments and classification of markets according to the products, customers or any combination of it

Dimension Calculation View

Dimension Calculation View – Star Join Calculation view
Using Projection, Join, Aggregation, Union and Rank

In-depth Modelling

Refactoring information models
Schema Mapping
Propagate to schematics and Show Lineage
Schema Mapping
Generating Time Data
Union Pruning
Using Time Travel
Migrating deprecated Information models
Using Currency Conversion
Web based Modelling Work bench

Analytic Privileges and Decision Tables

Classical Analytic Privileges
SQL Analytic Privileges
Dynamic analytic Privileges.
Turning Business Rules into Decision tables
Table Functions

SAP HANA Table Function

Query Optimizing Technique related to SAP HANA Tables
Web Based Modelling work bench

SAP HANA on Cloud

SAP Analytics with SAP Reporting environment SAP BOBJ – tools, WEBI, LUMIRA, DASHBOARD (integration between sap Hana and bob)

Advanced Topics Overview

SAP HANA Dynamic tiering
Delta Merge
SDI (Smart Data Integration)
SDA (Smart Data Access)

DATA Provisioning

SLT – SAP Landscape Transformation
BODS – Business Objects Data Services

Analytical Privileges

Classical XML Based Analytical Privileges
SQL Analytical Privileges

HANA Administration and Security

Hana Administration
Security in SAP HANA – User Management

Got a question for us? Please mention it in the comments section and we will get back to you.

555