Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
A database definition (DDL) statement (CREATE, ALTER, or DROP).
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes −

Generating some derived column values automatically
Enforcing referential integrity
Event logging and storing information on table access
Auditing
Synchronous replication of tables
Imposing security authorizations
Preventing invalid transactions

SAP HANA – SQL Synonym

SQL Synonyms is an alias for a table or a Schema object in a database. They are used to protect client applications from the changes made to name or location of an object.

Synonyms permit applications to function irrespective of user who owns the table and which database holds the table or object.

Create Synonym statement is used create a Synonym for a table, view, package, procedure, objects, etc.

Example

There is a table Customer of efashion, located on a Server1. To access this from Server2, a client application would have to use name as Server1.efashion.Customer. Now we change the location of Customer table the client application would have to be modified to reflect the change.

To address these we can create a synonym of Customer table Cust_Table on Server2 for the table on Server1. So now client application has to use the single-part name Cust_Table to reference this table. Now, if the location of this table changes, you will have to modify the synonym to point to the new location of the table.

As there is no ALTER SYNONYM statement, you have to drop the synonym Cust_Table and then re-create the synonym with the same name and point the synonym to the new location of Customer table.

Public Synonyms

Public Synonyms are owned by PUBLIC schema in a database. Public synonyms can be referenced by all users in the database. They are created by the application owner for the tables and other objects such as procedures and packages so the users of the application can see the objects.

Syntax

CREATE PUBLIC SYNONYM Cust_table for efashion.Customer;

To create a PUBLIC Synonym, you have to use keyword PUBLIC as shown.

Private Synonyms

Private Synonyms are used in a database schema to hide the true name of a table, procedure, view or any other database object.

Private synonyms can be referenced only by the schema that owns the table or object.

Syntax

CREATE SYNONYM Cust_table FOR efashion.Customer;

Drop a Synonym

Synonyms can be dropped using DROP Synonym command. If you are dropping a public Synonym, you have to use the keyword public in the drop statement.

Syntax

DROP PUBLIC Synonym Cust_table;

DROP Synonym Cust_table;

SAP HANA – SQL Explain Plans

SQL explain plans are used to generate detail explanation of SQL statements. They are used to evaluate execution plan that SAP HANA database follows to execute the SQL statements.

The results of explain plan are stored into EXPLAIN_PLAN_TABLE for evaluation. To use Explain Plan, passed SQL query must be a data manipulation language (DML).

Common DML Statements

SELECT − retrieve data from the a database
INSERT − insert data into a table
UPDATE − updates existing data within a table

SQL Explain Plans cannot be used with DDL and DCL SQL statements.

EXPLAIN PLAN TABLE in database

EXPLAIN PLAN_TABLE in database consists of multiple columns. Few common column names − OPERATOR_NAME, OPERATOR_ID, PARENT_OPERATOR_ID, LEVEL and POSITION, etc.

COLUMN SEARCH value tells the starting position of column engine operators.

ROW SEARCH value tells the starting position of row engine operators.

To create an EXPLAIN PLAN STATEMENT for a SQL query

EXPLAIN PLAN SET STATEMENT_NAME = ‘statement_name’ FOR <SQL DML statement>

To see values in EXPLAIN PLAN TABLE

SELECT Operator_Name, Operator_ID

FROM explain_plan_table

WHERE statement_name = 'statement_name';

To delete a statement in EXPLAIN PLAN TABLE

DELETE FROM explain_plan_table WHERE statement_name = 'TPC-H Q10';

SAP HANA – SQL Data Profiling

SQL Data Profiling task is used to understand and analyze data from multiple data sources. It is used to remove incorrect, incomplete data and prevent data quality problems before they are loaded in Data warehouse.

Here are the benefits of SQL Data Profiling tasks −

It helps is analyzing source data more effectively.
It helps in understanding the source data better.
It remove incorrect, incomplete data and improve data quality before it is loaded into Data warehouse.
It is used with Extraction, Transformation and Loading task.

The Data Profiling task checks profiles that helps to understand a data source and identify problems in the data that has to be fixed.

You can use the Data Profiling task inside an Integration Services package to profile data that is stored in SQL Server and to identify potential problems with data quality.

Note − Data Profiling Task works only with SQL Server data sources and does not support any other file based or third party data sources.

Access Requirement

To run a package contains Data Profiling task, user account must have read/write permissions with CREATE TABLE permissions on the tempdb database.

Data Profiler Viewer

Data Profile Viewer is used to review the profiler output. The Data Profile Viewer also supports drilldown capability to help you understand data quality issues that are identified in the profile output. This drill down capability sends live queries to the original data source.

Data Profiling Task Setup and Reviewing

Setting up the Data Profiling Task

It involves execution of a package that contains Data Profiling task to compute the profiles. The task saves the output in XML format to a file or a package variable.

Reviewing the Profiles

To view the data profiles, send the output to a file and then use the Data Profile Viewer. This viewer is a stand-alone utility that displays the profile output in both summary and detail format with optional drilldown capability.

Data Profiling − Configuration Options

The Data Profiling task has these convenient configuration options −

Wildcard columns

While configuring a profile request, the task accepts ‘*’ wildcard in place of a column name. This simplifies the configuration and makes it easier to discover the characteristics of unfamiliar data. When the task runs, the task profiles every column that has an appropriate data type.

Quick Profile

You can select Quick Profile to configure the task quickly. A Quick Profile profiles a table or view by using all the default profiles and settings.

The Data Profiling Task can compute eight different data profiles. Five of these profiles can check individual columns and the remaining three analyze- multiple columns or relationships between columns.

Data Profiling − Task Outputs

The Data Profiling task outputs the selected profiles into XML format that is structured like DataProfile.xsd schema.

You can save a local copy of the schema and view the local copy of the schema in Microsoft Visual Studio or another schema editor, in an XML editor or in a text editor such as Notepad.

This Tecklearn ‘SQL Triggers, Synonym and Data Profiling 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.

816